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].start_with?('sqlite_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].start_with?('sqlite_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 && !indexes(table).any?{|_, h| h[:columns].include?(op[:name])}
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.delete('`')
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, and VIRTUAL tables with :using option.

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   elsif options[:using]
361     "CREATE VIRTUAL TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)} USING #{options[:using]}"
362   else
363     super
364   end
365 end
create_view_prefix_sql(name, options) click to toggle source

SQLite support creating temporary views.

    # File lib/sequel/adapters/shared/sqlite.rb
368 def create_view_prefix_sql(name, options)
369   create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns])
370 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
382 def database_error_regexps
383   DATABASE_ERROR_REGEXPS
384 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
387 def database_specific_error_class(exception, opts)
388   case sqlite_error_code(exception)
389   when 1299
390     NotNullConstraintViolation
391   when 1555, 2067, 2579
392     UniqueConstraintViolation
393   when 787
394     ForeignKeyConstraintViolation
395   when 275
396     CheckConstraintViolation
397   when 19
398     ConstraintViolation
399   when 517
400     SerializationFailure
401   else
402     super
403   end
404 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
407 def defined_columns_for(table)
408   cols = parse_pragma(table, OPTS)
409   cols.each do |c|
410     c[:default] = LiteralString.new(c[:default]) if c[:default]
411     c[:type] = c[:db_type]
412   end
413   cols
414 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
419 def duplicate_table(table, opts=OPTS)
420   remove_cached_schema(table)
421   def_columns = defined_columns_for(table)
422   old_columns = def_columns.map{|c| c[:name]}
423   opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc]
424 
425   yield def_columns if defined?(yield)
426 
427   constraints = (opts[:constraints] || []).dup
428   pks = []
429   def_columns.each{|c| pks << c[:name] if c[:primary_key]}
430   if pks.length > 1
431     constraints << {:type=>:primary_key, :columns=>pks}
432     def_columns.each{|c| c[:primary_key] = false if c[:primary_key]}
433   end
434 
435   # If dropping a foreign key constraint, drop all foreign key constraints,
436   # as there is no way to determine which one to drop.
437   unless opts[:no_foreign_keys]
438     fks = foreign_key_list(table)
439 
440     # If dropping a column, if there is a foreign key with that
441     # column, don't include it when building a copy of the table.
442     if ocp = opts[:old_columns_proc]
443       fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]}
444     end
445     
446     # Skip any foreign key columns where a constraint for those
447     # foreign keys is being dropped.
448     if sfkc = opts[:skip_foreign_key_columns]
449       fks.delete_if{|c| c[:columns] == sfkc}
450     end
451 
452     constraints.concat(fks.each{|h| h[:type] = :foreign_key})
453   end
454 
455   # Determine unique constraints and make sure the new columns have them
456   unique_columns = []
457   skip_indexes = []
458   indexes(table, :only_autocreated=>true).each do |name, h|
459     skip_indexes << name
460     if h[:unique] && !opts[:no_unique]
461       if h[:columns].length == 1
462         unique_columns.concat(h[:columns])
463       elsif h[:columns].map(&:to_s) != pks
464         constraints << {:type=>:unique, :columns=>h[:columns]}
465       end
466     end
467   end
468   unique_columns -= pks
469   unless unique_columns.empty?
470     unique_columns.map!{|c| quote_identifier(c)}
471     def_columns.each do |c|
472       c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false
473     end
474   end
475   
476   def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ')
477   new_columns = old_columns.dup
478   opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc]
479 
480   qt = quote_schema_table(table)
481   bt = quote_identifier(backup_table_name(qt))
482   a = [
483      "ALTER TABLE #{qt} RENAME TO #{bt}",
484      "CREATE TABLE #{qt}(#{def_columns_str})",
485      "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}",
486      "DROP TABLE #{bt}"
487   ]
488   indexes(table).each do |name, h|
489     next if skip_indexes.include?(name)
490     if (h[:columns].map(&:to_s) - new_columns).empty?
491       a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name))
492     end
493   end
494   a
495 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
499 def on_delete_sql_to_sym(str)
500   case str
501   when 'RESTRICT'
502     :restrict
503   when 'CASCADE'
504     :cascade
505   when 'SET NULL'
506     :set_null
507   when 'SET DEFAULT'
508     :set_default
509   when 'NO ACTION'
510     :no_action
511   end
512 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
515 def parse_pragma(table_name, opts)
516   pks = 0
517   sch = _parse_pragma_ds(table_name, opts).map do |row|
518     if sqlite_version > 33100
519       # table_xinfo PRAGMA used, remove hidden columns
520       # that are not generated columns
521       if row[:generated] = (row.delete(:hidden) != 0)
522         row[:type] = row[:type].sub(' GENERATED ALWAYS', '')
523       end
524     end
525 
526     row.delete(:cid)
527     row[:allow_null] = row.delete(:notnull).to_i == 0
528     row[:default] = row.delete(:dflt_value)
529     row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL'
530     row[:db_type] = row.delete(:type)
531     if row[:primary_key] = row.delete(:pk).to_i > 0
532       pks += 1
533       # Guess that an integer primary key uses auto increment,
534       # since that is Sequel's default and SQLite does not provide
535       # a way to introspect whether it is actually autoincrementing.
536       row[:auto_increment] = row[:db_type].downcase == 'integer'
537     end
538     row[:type] = schema_column_type(row[:db_type])
539     row
540   end
541 
542   sch.compact!
543 
544   if pks > 1
545     # SQLite does not allow use of auto increment for tables
546     # with composite primary keys, so remove auto_increment
547     # if composite primary keys are detected.
548     sch.each{|r| r.delete(:auto_increment)}
549   end
550 
551   sch
552 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
556 def schema_parse_table(table_name, opts)
557   m = output_identifier_meth(opts[:dataset])
558   parse_pragma(table_name, opts).map do |row|
559     [m.call(row.delete(:name)), row]
560   end
561 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
564 def sqlite_error_code(exception)
565   nil
566 end
tables_and_views(filter, opts) click to toggle source

Backbone of the tables and views support.

    # File lib/sequel/adapters/shared/sqlite.rb
569 def tables_and_views(filter, opts)
570   m = output_identifier_meth
571   metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])}
572 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
577 def type_literal_generic_bignum_symbol(column)
578   column[:auto_increment] ? :integer : super
579 end