module Sequel::SQLite::DatabaseMethods

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.

Constants

AUTO_VACUUM
DATABASE_ERROR_REGEXPS
SYNCHRONOUS
TEMP_STORE
TRANSACTION_MODE

Attributes

current_timestamp_utc[RW]

Whether to keep CURRENT_TIMESTAMP and similar expressions in UTC. By default, the expressions are converted to localtime.

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

database_type() click to toggle source

SQLite uses the :sqlite database type.

   # File lib/sequel/adapters/shared/sqlite.rb
58 def database_type
59   :sqlite
60 end
foreign_key_list(table, opts=OPTS) click to toggle source

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

   # File lib/sequel/adapters/shared/sqlite.rb
69 def foreign_key_list(table, opts=OPTS)
70   m = output_identifier_meth
71   h = {}
72   _foreign_key_list_ds(table).each do |row|
73     if r = h[row[:id]]
74       r[:columns] << m.call(row[:from])
75       r[:key] << m.call(row[:to]) if r[:key]
76     else
77       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])}
78     end
79   end
80   h.values
81 end
freeze() click to toggle source
Calls superclass method
   # File lib/sequel/adapters/shared/sqlite.rb
83 def freeze
84   sqlite_version
85   use_timestamp_timezones?
86   super
87 end
indexes(table, opts=OPTS) click to toggle source

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

    # File lib/sequel/adapters/shared/sqlite.rb
 90 def indexes(table, opts=OPTS)
 91   m = output_identifier_meth
 92   im = input_identifier_meth
 93   indexes = {}
 94   table = table.value if table.is_a?(Sequel::SQL::Identifier)
 95   metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r|
 96     if opts[:only_autocreated]
 97       # If specifically asked for only autocreated indexes, then return those an only those
 98       next unless r[:name] =~ /\Asqlite_autoindex_/
 99     elsif r.has_key?(:origin)
100       # If origin is set, then only exclude primary key indexes and partial indexes
101       next if r[:origin] == 'pk'
102       next if r[:partial].to_i == 1
103     else
104       # When :origin key not present, assume any autoindex could be a primary key one and exclude it
105       next if r[:name] =~ /\Asqlite_autoindex_/
106     end
107 
108     indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1}
109   end
110   indexes.each do |k, v|
111     v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)}
112   end
113   indexes
114 end
set_integer_booleans() click to toggle source

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

   # File lib/sequel/adapters/shared/sqlite.rb
63 def set_integer_booleans
64   @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true
65 end
sqlite_version() click to toggle source

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

    # File lib/sequel/adapters/shared/sqlite.rb
118 def sqlite_version
119   return @sqlite_version if defined?(@sqlite_version)
120   @sqlite_version = begin
121     v = fetch('SELECT sqlite_version()').single_value
122     [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])}
123   rescue
124     0
125   end
126 end
support_without_rowid?() click to toggle source

SQLite 3.8.2+ supports the without rowid table constraint

    # File lib/sequel/adapters/shared/sqlite.rb
149 def support_without_rowid?
150   sqlite_version >= 30802
151 end
supports_create_table_if_not_exists?() click to toggle source

SQLite supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.

    # File lib/sequel/adapters/shared/sqlite.rb
129 def supports_create_table_if_not_exists?
130   sqlite_version >= 30300
131 end
supports_deferrable_foreign_key_constraints?() click to toggle source

SQLite 3.6.19+ supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/sqlite.rb
134 def supports_deferrable_foreign_key_constraints?
135   sqlite_version >= 30619
136 end
supports_partial_indexes?() click to toggle source

SQLite 3.8.0+ supports partial indexes.

    # File lib/sequel/adapters/shared/sqlite.rb
139 def supports_partial_indexes?
140   sqlite_version >= 30800
141 end
supports_savepoints?() click to toggle source

SQLite 3.6.8+ supports savepoints.

    # File lib/sequel/adapters/shared/sqlite.rb
144 def supports_savepoints?
145   sqlite_version >= 30608
146 end
tables(opts=OPTS) click to toggle source

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

Options:

:server

Set the server to use.

    # File lib/sequel/adapters/shared/sqlite.rb
168 def tables(opts=OPTS)
169   tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts)
170 end
transaction_mode=(value) click to toggle source

Set the default transaction mode.

   # File lib/sequel/adapters/shared/sqlite.rb
49 def transaction_mode=(value)
50   if TRANSACTION_MODE.include?(value)
51     @transaction_mode = value
52   else
53     raise Error, "Invalid value for transaction_mode.  Please specify one of :deferred, :immediate, :exclusive, nil"
54   end
55 end
use_timestamp_timezones?() click to toggle source

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

    # File lib/sequel/adapters/shared/sqlite.rb
160 def use_timestamp_timezones?
161   defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false)
162 end
values(v) click to toggle source

Creates a dataset that uses the VALUES clause:

DB.values([[1, 2], [3, 4]])
# VALUES ((1, 2), (3, 4))
    # File lib/sequel/adapters/shared/sqlite.rb
176 def values(v)
177   raise Error, "Cannot provide an empty array for values" if v.empty?
178   @default_dataset.clone(:values=>v)
179 end
views(opts=OPTS) click to toggle source

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

Options:

:server

Set the server to use.

    # File lib/sequel/adapters/shared/sqlite.rb
185 def views(opts=OPTS)
186   tables_and_views({:type => 'view'}, opts)
187 end

Private Instance Methods

_foreign_key_list_ds(table) click to toggle source

Dataset used for parsing foreign key lists

    # File lib/sequel/adapters/shared/sqlite.rb
192 def _foreign_key_list_ds(table)
193   metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table))
194 end
_parse_pragma_ds(table_name, opts) click to toggle source

Dataset used for parsing schema

    # File lib/sequel/adapters/shared/sqlite.rb
197 def _parse_pragma_ds(table_name, opts)
198   metadata_dataset.with_sql("PRAGMA table_#{'x' if sqlite_version > 33100}info(?)", input_identifier_meth(opts[:dataset]).call(table_name))
199 end
alter_table_sql(table, op) click to toggle source

SQLite supports limited table modification. You can add a column or an index. Dropping columns is supported by copying the table into a temporary table, dropping the table, and creating a new table without the column inside of a transaction.

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
237 def alter_table_sql(table, op)
238   case op[:op]
239   when :add_index, :drop_index
240     super
241   when :add_column
242     if op[:unique] || op[:primary_key]
243       duplicate_table(table){|columns| columns.push(op)}
244     else
245       super
246     end
247   when :drop_column
248     if sqlite_version >= 33500
249       super
250     else
251       ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}}
252       duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}}
253     end
254   when :rename_column
255     if sqlite_version >= 32500
256       super
257     else
258       ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}}
259       duplicate_table(table, :new_columns_proc=>ncp){|columns| columns.each{|s| s[:name] = op[:new_name] if s[:name].to_s == op[:name].to_s}}
260     end
261   when :set_column_default
262     duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}}
263   when :set_column_null
264     duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}}
265   when :set_column_type
266     duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}}
267   when :drop_constraint
268     case op[:type]
269     when :primary_key
270       duplicate_table(table) do |columns|
271         columns.each do |s|
272           s[:unique] = false if s[:primary_key]
273           s[:primary_key] = s[:auto_increment] = nil
274         end
275       end
276     when :foreign_key
277       if op[:columns]
278         duplicate_table(table, :skip_foreign_key_columns=>op[:columns])
279       else
280         duplicate_table(table, :no_foreign_keys=>true)
281       end
282     when :unique
283       duplicate_table(table, :no_unique=>true)
284     else
285       duplicate_table(table)
286     end
287   when :add_constraint
288     duplicate_table(table, :constraints=>[op])
289   when :add_constraints
290     duplicate_table(table, :constraints=>op[:ops])
291   else
292     raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}"
293   end
294 end
apply_alter_table(table, ops) click to toggle source

Run all alter_table commands in a transaction. This is technically only needed for drop column.

    # File lib/sequel/adapters/shared/sqlite.rb
203 def apply_alter_table(table, ops)
204   fks = fetch("PRAGMA foreign_keys")
205   if fks
206     run "PRAGMA foreign_keys = 0"
207     run "PRAGMA legacy_alter_table = 1" if sqlite_version >= 32600
208   end
209   transaction do 
210     if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint || op[:op] == :set_column_null}
211       null_ops, ops = ops.partition{|op| op[:op] == :set_column_null}
212 
213       # Apply NULL/NOT NULL ops first, since those should be purely idependent of the constraints.
214       null_ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}}
215 
216       # If you are just doing constraints, apply all of them at the same time,
217       # as otherwise all but the last one get lost.
218       alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)}
219     else
220       # Run each operation separately, as later operations may depend on the
221       # results of earlier operations.
222       ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}}
223     end
224   end
225   remove_cached_schema(table)
226 ensure
227   if fks
228     run "PRAGMA foreign_keys = 1"
229     run "PRAGMA legacy_alter_table = 0" if sqlite_version >= 32600
230   end
231 end
backup_table_name(table, opts=OPTS) click to toggle source

A name to use for the backup table

    # File lib/sequel/adapters/shared/sqlite.rb
304 def backup_table_name(table, opts=OPTS)
305   table = table.gsub('`', '')
306   (opts[:times]||1000).times do |i|
307     table_name = "#{table}_backup#{i}"
308     return table_name unless table_exists?(table_name)
309   end
310 end
begin_new_transaction(conn, opts) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
296 def begin_new_transaction(conn, opts)
297   mode = opts[:mode] || @transaction_mode
298   sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil"
299   log_connection_execute(conn, sql)
300   set_transaction_isolation(conn, opts)
301 end
can_add_primary_key_constraint_on_nullable_columns?() click to toggle source

SQLite allows adding primary key constraints on NULLABLE columns, but then does not enforce NOT NULL for such columns, so force setting the columns NOT NULL.

    # File lib/sequel/adapters/shared/sqlite.rb
314 def can_add_primary_key_constraint_on_nullable_columns?
315   false
316 end
column_definition_default_sql(sql, column) click to toggle source

Surround default with parens to appease SQLite. Add support for GENERATED ALWAYS AS.

    # File lib/sequel/adapters/shared/sqlite.rb
319 def column_definition_default_sql(sql, column)
320   sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default)
321   if (generated = column[:generated_always_as])
322     if (generated_type = column[:generated_type]) && (generated_type == :stored || generated_type == :virtual)
323       generated_type = generated_type.to_s.upcase
324     end
325     sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{generated_type}"
326   end
327 end
column_schema_decimal_min_max_values(column)
column_schema_integer_min_max_values(column) click to toggle source

SQLite does not restrict the integer or decimal type to a specific range.

    # File lib/sequel/adapters/shared/sqlite.rb
330 def column_schema_integer_min_max_values(column)
331   nil
332 end
connection_pragmas() click to toggle source

Array of PRAGMA SQL statements based on the Database options that should be applied to new connections.

    # File lib/sequel/adapters/shared/sqlite.rb
337 def connection_pragmas
338   ps = []
339   v = typecast_value_boolean(opts.fetch(:foreign_keys, 1))
340   ps << "PRAGMA foreign_keys = #{v ? 1 : 0}"
341   v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1))
342   ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}"
343   [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con|
344     if v = opts[prag]
345       raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym)
346       ps << "PRAGMA #{prag} = #{v}"
347     end
348   end
349   ps
350 end
create_table_sql(name, generator, options) click to toggle source

Support creating STRICT AND/OR WITHOUT ROWID tables via :strict and :without_rowid options

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
353 def create_table_sql(name, generator, options)
354   if options[:strict] && options[:without_rowid]
355     "#{super} STRICT, WITHOUT ROWID"
356   elsif options[:strict]
357     "#{super} STRICT"
358   elsif options[:without_rowid]
359     "#{super} WITHOUT ROWID"
360   else
361     super
362   end
363 end
create_view_prefix_sql(name, options) click to toggle source

SQLite support creating temporary views.

    # File lib/sequel/adapters/shared/sqlite.rb
366 def create_view_prefix_sql(name, options)
367   create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns])
368 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
380 def database_error_regexps
381   DATABASE_ERROR_REGEXPS
382 end
database_specific_error_class(exception, opts) click to toggle source

Recognize SQLite error codes if the exception provides access to them.

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
385 def database_specific_error_class(exception, opts)
386   case sqlite_error_code(exception)
387   when 1299
388     NotNullConstraintViolation
389   when 1555, 2067, 2579
390     UniqueConstraintViolation
391   when 787
392     ForeignKeyConstraintViolation
393   when 275
394     CheckConstraintViolation
395   when 19
396     ConstraintViolation
397   when 517
398     SerializationFailure
399   else
400     super
401   end
402 end
defined_columns_for(table) click to toggle source

The array of column schema hashes for the current columns in the table

    # File lib/sequel/adapters/shared/sqlite.rb
405 def defined_columns_for(table)
406   cols = parse_pragma(table, OPTS)
407   cols.each do |c|
408     c[:default] = LiteralString.new(c[:default]) if c[:default]
409     c[:type] = c[:db_type]
410   end
411   cols
412 end
duplicate_table(table, opts=OPTS) { |def_columns| ... } click to toggle source

Duplicate an existing table by creating a new table, copying all records from the existing table into the new table, deleting the existing table and renaming the new table to the existing table’s name.

    # File lib/sequel/adapters/shared/sqlite.rb
417 def duplicate_table(table, opts=OPTS)
418   remove_cached_schema(table)
419   def_columns = defined_columns_for(table)
420   old_columns = def_columns.map{|c| c[:name]}
421   opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc]
422 
423   yield def_columns if defined?(yield)
424 
425   constraints = (opts[:constraints] || []).dup
426   pks = []
427   def_columns.each{|c| pks << c[:name] if c[:primary_key]}
428   if pks.length > 1
429     constraints << {:type=>:primary_key, :columns=>pks}
430     def_columns.each{|c| c[:primary_key] = false if c[:primary_key]}
431   end
432 
433   # If dropping a foreign key constraint, drop all foreign key constraints,
434   # as there is no way to determine which one to drop.
435   unless opts[:no_foreign_keys]
436     fks = foreign_key_list(table)
437 
438     # If dropping a column, if there is a foreign key with that
439     # column, don't include it when building a copy of the table.
440     if ocp = opts[:old_columns_proc]
441       fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]}
442     end
443     
444     # Skip any foreign key columns where a constraint for those
445     # foreign keys is being dropped.
446     if sfkc = opts[:skip_foreign_key_columns]
447       fks.delete_if{|c| c[:columns] == sfkc}
448     end
449 
450     constraints.concat(fks.each{|h| h[:type] = :foreign_key})
451   end
452 
453   # Determine unique constraints and make sure the new columns have them
454   unique_columns = []
455   skip_indexes = []
456   indexes(table, :only_autocreated=>true).each do |name, h|
457     skip_indexes << name
458     if h[:unique] && !opts[:no_unique]
459       if h[:columns].length == 1
460         unique_columns.concat(h[:columns])
461       elsif h[:columns].map(&:to_s) != pks
462         constraints << {:type=>:unique, :columns=>h[:columns]}
463       end
464     end
465   end
466   unique_columns -= pks
467   unless unique_columns.empty?
468     unique_columns.map!{|c| quote_identifier(c)}
469     def_columns.each do |c|
470       c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false
471     end
472   end
473   
474   def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ')
475   new_columns = old_columns.dup
476   opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc]
477 
478   qt = quote_schema_table(table)
479   bt = quote_identifier(backup_table_name(qt))
480   a = [
481      "ALTER TABLE #{qt} RENAME TO #{bt}",
482      "CREATE TABLE #{qt}(#{def_columns_str})",
483      "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}",
484      "DROP TABLE #{bt}"
485   ]
486   indexes(table).each do |name, h|
487     next if skip_indexes.include?(name)
488     if (h[:columns].map(&:to_s) - new_columns).empty?
489       a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name))
490     end
491   end
492   a
493 end
on_delete_sql_to_sym(str) click to toggle source

Does the reverse of on_delete_clause, eg. converts strings like +‘SET NULL’+ to symbols :set_null.

    # File lib/sequel/adapters/shared/sqlite.rb
497 def on_delete_sql_to_sym(str)
498   case str
499   when 'RESTRICT'
500     :restrict
501   when 'CASCADE'
502     :cascade
503   when 'SET NULL'
504     :set_null
505   when 'SET DEFAULT'
506     :set_default
507   when 'NO ACTION'
508     :no_action
509   end
510 end
parse_pragma(table_name, opts) click to toggle source

Parse the output of the table_info pragma

    # File lib/sequel/adapters/shared/sqlite.rb
513 def parse_pragma(table_name, opts)
514   pks = 0
515   sch = _parse_pragma_ds(table_name, opts).map do |row|
516     if sqlite_version > 33100
517       # table_xinfo PRAGMA used, remove hidden columns
518       # that are not generated columns
519       if row[:generated] = (row.delete(:hidden) != 0)
520         row[:type] = row[:type].sub(' GENERATED ALWAYS', '')
521       end
522     end
523 
524     row.delete(:cid)
525     row[:allow_null] = row.delete(:notnull).to_i == 0
526     row[:default] = row.delete(:dflt_value)
527     row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL'
528     row[:db_type] = row.delete(:type)
529     if row[:primary_key] = row.delete(:pk).to_i > 0
530       pks += 1
531       # Guess that an integer primary key uses auto increment,
532       # since that is Sequel's default and SQLite does not provide
533       # a way to introspect whether it is actually autoincrementing.
534       row[:auto_increment] = row[:db_type].downcase == 'integer'
535     end
536     row[:type] = schema_column_type(row[:db_type])
537     row
538   end
539 
540   sch.compact!
541 
542   if pks > 1
543     # SQLite does not allow use of auto increment for tables
544     # with composite primary keys, so remove auto_increment
545     # if composite primary keys are detected.
546     sch.each{|r| r.delete(:auto_increment)}
547   end
548 
549   sch
550 end
schema_parse_table(table_name, opts) click to toggle source

SQLite supports schema parsing using the table_info PRAGMA, so parse the output of that into the format Sequel expects.

    # File lib/sequel/adapters/shared/sqlite.rb
554 def schema_parse_table(table_name, opts)
555   m = output_identifier_meth(opts[:dataset])
556   parse_pragma(table_name, opts).map do |row|
557     [m.call(row.delete(:name)), row]
558   end
559 end
sqlite_error_code(exception) click to toggle source

Don’t support SQLite error codes for exceptions by default.

    # File lib/sequel/adapters/shared/sqlite.rb
562 def sqlite_error_code(exception)
563   nil
564 end
tables_and_views(filter, opts) click to toggle source

Backbone of the tables and views support.

    # File lib/sequel/adapters/shared/sqlite.rb
567 def tables_and_views(filter, opts)
568   m = output_identifier_meth
569   metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])}
570 end
type_literal_generic_bignum_symbol(column) click to toggle source

SQLite only supports AUTOINCREMENT on integer columns, not bigint columns, so use integer instead of bigint for those columns.

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
575 def type_literal_generic_bignum_symbol(column)
576   column[:auto_increment] ? :integer : super
577 end