Module Sequel::SQLite::DatabaseMethods
In: lib/sequel/adapters/shared/sqlite.rb

No matter how you connect to SQLite, the following Database options can be used to set PRAGMAs on connections in a thread-safe manner: :auto_vacuum, :foreign_keys, :synchronous, and :temp_store.

Methods

Included Modules

UnmodifiedIdentifiers::DatabaseMethods

Constants

AUTO_VACUUM = [:none, :full, :incremental].freeze
SYNCHRONOUS = [:off, :normal, :full].freeze
TEMP_STORE = [:default, :file, :memory].freeze
TRANSACTION_MODE = { :deferred => "BEGIN DEFERRED TRANSACTION".freeze, :immediate => "BEGIN IMMEDIATE TRANSACTION".freeze, :exclusive => "BEGIN EXCLUSIVE TRANSACTION".freeze, nil => "BEGIN".freeze
DATABASE_ERROR_REGEXPS = { /(is|are) not unique\z|PRIMARY KEY must be unique\z|UNIQUE constraint failed: .+\z/ => UniqueConstraintViolation, /foreign key constraint failed\z/i => ForeignKeyConstraintViolation, /\ACHECK constraint failed/ => CheckConstraintViolation, /\A(SQLITE ERROR 19 \(CONSTRAINT\) : )?constraint failed\z/ => ConstraintViolation, /may not be NULL\z|NOT NULL constraint failed: .+\z/ => NotNullConstraintViolation, /\ASQLITE ERROR \d+ \(\) : CHECK constraint failed: / => CheckConstraintViolation

Attributes

integer_booleans  [RW]  Whether to use integers for booleans in the database. SQLite recommends booleans be stored as integers, but historically Sequel has used ‘t’/’f’.
transaction_mode  [R]  A symbol signifying the value of the default transaction mode
use_timestamp_timezones  [W]  Override the default setting for whether to use timezones in timestamps. It is set to false by default, as SQLite‘s date/time methods do not support timezones in timestamps.

Public Instance methods

SQLite uses the :sqlite database type.

[Source]

    # File lib/sequel/adapters/shared/sqlite.rb, line 49
49:       def database_type
50:         :sqlite
51:       end

Return the array of foreign key info hashes using the foreign_key_list PRAGMA, including information for the :on_update and :on_delete entries.

[Source]

    # File lib/sequel/adapters/shared/sqlite.rb, line 60
60:       def foreign_key_list(table, opts=OPTS)
61:         m = output_identifier_meth
62:         h = {}
63:         metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table)).each do |row|
64:           if r = h[row[:id]]
65:             r[:columns] << m.call(row[:from])
66:             r[:key] << m.call(row[:to]) if r[:key]
67:           else
68:             h[row[:id]] = {:columns=>[m.call(row[:from])], :table=>m.call(row[:table]), :key=>([m.call(row[:to])] if row[:to]), :on_update=>on_delete_sql_to_sym(row[:on_update]), :on_delete=>on_delete_sql_to_sym(row[:on_delete])}
69:           end
70:         end
71:         h.values
72:       end

[Source]

    # File lib/sequel/adapters/shared/sqlite.rb, line 74
74:       def freeze
75:         sqlite_version
76:         use_timestamp_timezones?
77:         super
78:       end

Use the index_list and index_info PRAGMAs to determine the indexes on the table.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 81
 81:       def indexes(table, opts=OPTS)
 82:         m = output_identifier_meth
 83:         im = input_identifier_meth
 84:         indexes = {}
 85:         metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r|
 86:           if opts[:only_autocreated]
 87:             # If specifically asked for only autocreated indexes, then return those an only those
 88:             next unless r[:name] =~ /\Asqlite_autoindex_/
 89:           elsif r.has_key?(:origin)
 90:             # If origin is set, then only exclude primary key indexes and partial indexes
 91:             next if r[:origin] == 'pk'
 92:             next if r[:partial].to_i == 1
 93:           else
 94:             # When :origin key not present, assume any autoindex could be a primary key one and exclude it
 95:             next if r[:name] =~ /\Asqlite_autoindex_/
 96:           end
 97: 
 98:           indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1}
 99:         end
100:         indexes.each do |k, v|
101:           v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)}
102:         end
103:         indexes
104:       end

Set the integer_booleans option using the passed in :integer_boolean option.

[Source]

    # File lib/sequel/adapters/shared/sqlite.rb, line 54
54:       def set_integer_booleans
55:         @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true
56:       end

The version of the server as an integer, where 3.6.19 = 30619. If the server version can‘t be determined, 0 is used.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 108
108:       def sqlite_version
109:         return @sqlite_version if defined?(@sqlite_version)
110:         @sqlite_version = begin
111:           v = fetch('SELECT sqlite_version()').single_value
112:           [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])}
113:         rescue
114:           0
115:         end
116:       end

SQLite supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 119
119:       def supports_create_table_if_not_exists?
120:         sqlite_version >= 30300
121:       end

SQLite 3.6.19+ supports deferrable foreign key constraints.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 124
124:       def supports_deferrable_foreign_key_constraints?
125:         sqlite_version >= 30619
126:       end

SQLite 3.8.0+ supports partial indexes.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 129
129:       def supports_partial_indexes?
130:         sqlite_version >= 30800
131:       end

SQLite 3.6.8+ supports savepoints.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 134
134:       def supports_savepoints?
135:         sqlite_version >= 30608
136:       end

Array of symbols specifying the table names in the current database.

Options:

:server :Set the server to use.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 153
153:       def tables(opts=OPTS)
154:         tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts)
155:       end

Set the default transaction mode.

[Source]

    # File lib/sequel/adapters/shared/sqlite.rb, line 40
40:       def transaction_mode=(value)
41:         if TRANSACTION_MODE.include?(value)
42:           @transaction_mode = value
43:         else
44:           raise Error, "Invalid value for transaction_mode.  Please specify one of :deferred, :immediate, :exclusive, nil"
45:         end
46:       end

SQLite supports timezones in timestamps, since it just stores them as strings, but it breaks the usage of SQLite‘s datetime functions.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 145
145:       def use_timestamp_timezones?
146:         defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false)
147:       end

Creates a dataset that uses the VALUES clause:

  DB.values([[1, 2], [3, 4]])
  # VALUES ((1, 2), (3, 4))

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 161
161:       def values(v)
162:         @default_dataset.clone(:values=>v)
163:       end

Array of symbols specifying the view names in the current database.

Options:

:server :Set the server to use.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 169
169:       def views(opts=OPTS)
170:         tables_and_views({:type => 'view'}, opts)
171:       end

[Validate]