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

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
49 def database_type
50   :sqlite
51 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
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
freeze() click to toggle source
Calls superclass method
   # File lib/sequel/adapters/shared/sqlite.rb
74 def freeze
75   sqlite_version
76   use_timestamp_timezones?
77   super
78 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
 81 def indexes(table, opts=OPTS)
 82   m = output_identifier_meth
 83   im = input_identifier_meth
 84   indexes = {}
 85   table = table.value if table.is_a?(Sequel::SQL::Identifier)
 86   metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r|
 87     if opts[:only_autocreated]
 88       # If specifically asked for only autocreated indexes, then return those an only those
 89       next unless r[:name] =~ /\Asqlite_autoindex_/
 90     elsif r.has_key?(:origin)
 91       # If origin is set, then only exclude primary key indexes and partial indexes
 92       next if r[:origin] == 'pk'
 93       next if r[:partial].to_i == 1
 94     else
 95       # When :origin key not present, assume any autoindex could be a primary key one and exclude it
 96       next if r[:name] =~ /\Asqlite_autoindex_/
 97     end
 98 
 99     indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1}
100   end
101   indexes.each do |k, v|
102     v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)}
103   end
104   indexes
105 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
54 def set_integer_booleans
55   @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true
56 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
109 def sqlite_version
110   return @sqlite_version if defined?(@sqlite_version)
111   @sqlite_version = begin
112     v = fetch('SELECT sqlite_version()').single_value
113     [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])}
114   rescue
115     0
116   end
117 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
120 def supports_create_table_if_not_exists?
121   sqlite_version >= 30300
122 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
125 def supports_deferrable_foreign_key_constraints?
126   sqlite_version >= 30619
127 end
supports_partial_indexes?() click to toggle source

SQLite 3.8.0+ supports partial indexes.

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

SQLite 3.6.8+ supports savepoints.

    # File lib/sequel/adapters/shared/sqlite.rb
135 def supports_savepoints?
136   sqlite_version >= 30608
137 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
154 def tables(opts=OPTS)
155   tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts)
156 end
transaction_mode=(value) click to toggle source

Set the default transaction mode.

   # File lib/sequel/adapters/shared/sqlite.rb
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
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
146 def use_timestamp_timezones?
147   defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false)
148 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
162 def values(v)
163   @default_dataset.clone(:values=>v)
164 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
170 def views(opts=OPTS)
171   tables_and_views({:type => 'view'}, opts)
172 end

Private Instance Methods

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
212 def alter_table_sql(table, op)
213   case op[:op]
214   when :add_index, :drop_index
215     super
216   when :add_column
217     if op[:unique] || op[:primary_key]
218       duplicate_table(table){|columns| columns.push(op)}
219     else
220       super
221     end
222   when :drop_column
223     ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}}
224     duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}}
225   when :rename_column
226     if sqlite_version >= 32500
227       super
228     else
229       ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}}
230       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}}
231     end
232   when :set_column_default
233     duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}}
234   when :set_column_null
235     duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}}
236   when :set_column_type
237     duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}}
238   when :drop_constraint
239     case op[:type]
240     when :primary_key
241       duplicate_table(table){|columns| columns.each{|s| s[:primary_key] = s[:auto_increment] = nil}}
242     when :foreign_key
243       if op[:columns]
244         duplicate_table(table, :skip_foreign_key_columns=>op[:columns])
245       else
246         duplicate_table(table, :no_foreign_keys=>true)
247       end
248     else
249       duplicate_table(table)
250     end
251   when :add_constraint
252     duplicate_table(table, :constraints=>[op])
253   when :add_constraints
254     duplicate_table(table, :constraints=>op[:ops])
255   else
256     raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}"
257   end
258 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
178 def apply_alter_table(table, ops)
179   fks = fetch("PRAGMA foreign_keys")
180   if fks
181     run "PRAGMA foreign_keys = 0"
182     run "PRAGMA legacy_alter_table = 1" if sqlite_version >= 32600
183   end
184   transaction do 
185     if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint || op[:op] == :set_column_null}
186       null_ops, ops = ops.partition{|op| op[:op] == :set_column_null}
187 
188       # Apply NULL/NOT NULL ops first, since those should be purely idependent of the constraints.
189       null_ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}}
190 
191       # If you are just doing constraints, apply all of them at the same time,
192       # as otherwise all but the last one get lost.
193       alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)}
194     else
195       # Run each operation separately, as later operations may depend on the
196       # results of earlier operations.
197       ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}}
198     end
199   end
200   remove_cached_schema(table)
201 ensure
202   if fks
203     run "PRAGMA foreign_keys = 1"
204     run "PRAGMA legacy_alter_table = 0" if sqlite_version >= 32600
205   end
206 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
268 def backup_table_name(table, opts=OPTS)
269   table = table.gsub('`', '')
270   (opts[:times]||1000).times do |i|
271     table_name = "#{table}_backup#{i}"
272     return table_name unless table_exists?(table_name)
273   end
274 end
begin_new_transaction(conn, opts) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
260 def begin_new_transaction(conn, opts)
261   mode = opts[:mode] || @transaction_mode
262   sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil"
263   log_connection_execute(conn, sql)
264   set_transaction_isolation(conn, opts)
265 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
278 def can_add_primary_key_constraint_on_nullable_columns?
279   false
280 end
column_definition_default_sql(sql, column) click to toggle source

Surround default with parens to appease SQLite

    # File lib/sequel/adapters/shared/sqlite.rb
283 def column_definition_default_sql(sql, column)
284   sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default)
285 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
289 def connection_pragmas
290   ps = []
291   v = typecast_value_boolean(opts.fetch(:foreign_keys, 1))
292   ps << "PRAGMA foreign_keys = #{v ? 1 : 0}"
293   v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1))
294   ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}"
295   [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con|
296     if v = opts[prag]
297       raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym)
298       ps << "PRAGMA #{prag} = #{v}"
299     end
300   end
301   ps
302 end
create_view_prefix_sql(name, options) click to toggle source

SQLite support creating temporary views.

    # File lib/sequel/adapters/shared/sqlite.rb
305 def create_view_prefix_sql(name, options)
306   create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns])
307 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
317 def database_error_regexps
318   DATABASE_ERROR_REGEXPS
319 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
322 def database_specific_error_class(exception, opts)
323   case sqlite_error_code(exception)
324   when 1299
325     NotNullConstraintViolation
326   when 1555, 2067, 2579
327     UniqueConstraintViolation
328   when 787
329     ForeignKeyConstraintViolation
330   when 275
331     CheckConstraintViolation
332   when 19
333     ConstraintViolation
334   when 517
335     SerializationFailure
336   else
337     super
338   end
339 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
342 def defined_columns_for(table)
343   cols = parse_pragma(table, OPTS)
344   cols.each do |c|
345     c[:default] = LiteralString.new(c[:default]) if c[:default]
346     c[:type] = c[:db_type]
347   end
348   cols
349 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
354 def duplicate_table(table, opts=OPTS)
355   remove_cached_schema(table)
356   def_columns = defined_columns_for(table)
357   old_columns = def_columns.map{|c| c[:name]}
358   opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc]
359 
360   yield def_columns if block_given?
361 
362   constraints = (opts[:constraints] || []).dup
363   pks = []
364   def_columns.each{|c| pks << c[:name] if c[:primary_key]}
365   if pks.length > 1
366     constraints << {:type=>:primary_key, :columns=>pks}
367     def_columns.each{|c| c[:primary_key] = false if c[:primary_key]}
368   end
369 
370   # If dropping a foreign key constraint, drop all foreign key constraints,
371   # as there is no way to determine which one to drop.
372   unless opts[:no_foreign_keys]
373     fks = foreign_key_list(table)
374 
375     # If dropping a column, if there is a foreign key with that
376     # column, don't include it when building a copy of the table.
377     if ocp = opts[:old_columns_proc]
378       fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]}
379     end
380     
381     # Skip any foreign key columns where a constraint for those
382     # foreign keys is being dropped.
383     if sfkc = opts[:skip_foreign_key_columns]
384       fks.delete_if{|c| c[:columns] == sfkc}
385     end
386 
387     constraints.concat(fks.each{|h| h[:type] = :foreign_key})
388   end
389 
390   # Determine unique constraints and make sure the new columns have them
391   unique_columns = []
392   skip_indexes = []
393   indexes(table, :only_autocreated=>true).each do |name, h|
394     skip_indexes << name
395     if h[:columns].length == 1 && h[:unique]
396       unique_columns.concat(h[:columns])
397     end
398   end
399   unique_columns -= pks
400   unless unique_columns.empty?
401     unique_columns.map!{|c| quote_identifier(c)}
402     def_columns.each do |c|
403       c[:unique] = true if unique_columns.include?(quote_identifier(c[:name]))
404     end
405   end
406   
407   def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ')
408   new_columns = old_columns.dup
409   opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc]
410 
411   qt = quote_schema_table(table)
412   bt = quote_identifier(backup_table_name(qt))
413   a = [
414      "ALTER TABLE #{qt} RENAME TO #{bt}",
415      "CREATE TABLE #{qt}(#{def_columns_str})",
416      "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}",
417      "DROP TABLE #{bt}"
418   ]
419   indexes(table).each do |name, h|
420     next if skip_indexes.include?(name)
421     if (h[:columns].map(&:to_s) - new_columns).empty?
422       a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name))
423     end
424   end
425   a
426 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
430 def on_delete_sql_to_sym(str)
431   case str
432   when 'RESTRICT'
433     :restrict
434   when 'CASCADE'
435     :cascade
436   when 'SET NULL'
437     :set_null
438   when 'SET DEFAULT'
439     :set_default
440   when 'NO ACTION'
441     :no_action
442   end
443 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
446 def parse_pragma(table_name, opts)
447   pks = 0
448   sch = metadata_dataset.with_sql("PRAGMA table_info(?)", input_identifier_meth(opts[:dataset]).call(table_name)).map do |row|
449     row.delete(:cid)
450     row[:allow_null] = row.delete(:notnull).to_i == 0
451     row[:default] = row.delete(:dflt_value)
452     row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL'
453     row[:db_type] = row.delete(:type)
454     if row[:primary_key] = row.delete(:pk).to_i > 0
455       pks += 1
456       # Guess that an integer primary key uses auto increment,
457       # since that is Sequel's default and SQLite does not provide
458       # a way to introspect whether it is actually autoincrementing.
459       row[:auto_increment] = row[:db_type].downcase == 'integer'
460     end
461     row[:type] = schema_column_type(row[:db_type])
462     row
463   end
464 
465   if pks > 1
466     # SQLite does not allow use of auto increment for tables
467     # with composite primary keys, so remove auto_increment
468     # if composite primary keys are detected.
469     sch.each{|r| r.delete(:auto_increment)}
470   end
471 
472   sch
473 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
477 def schema_parse_table(table_name, opts)
478   m = output_identifier_meth(opts[:dataset])
479   parse_pragma(table_name, opts).map do |row|
480     [m.call(row.delete(:name)), row]
481   end
482 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
485 def sqlite_error_code(exception)
486   nil
487 end
tables_and_views(filter, opts) click to toggle source

Backbone of the tables and views support.

    # File lib/sequel/adapters/shared/sqlite.rb
490 def tables_and_views(filter, opts)
491   m = output_identifier_meth
492   metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])}
493 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
498 def type_literal_generic_bignum_symbol(column)
499   column[:auto_increment] ? :integer : super
500 end