module Sequel::SQLite::DatabaseMethods

  1. 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.

Constants

AUTO_VACUUM = [:none, :full, :incremental].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, /\ASQLITE ERROR 3091/ => CheckConstraintViolation, /\A(SQLITE ERROR 275 \(CONSTRAINT_CHECK\) : )?CHECK constraint failed/ => CheckConstraintViolation, /\A(SQLITE ERROR 19 \(CONSTRAINT\) : )?constraint failed\z/ => ConstraintViolation, /\Acannot store [A-Z]+ value in [A-Z]+ column / => ConstraintViolation, /may not be NULL\z|NOT NULL constraint failed: .+\z/ => NotNullConstraintViolation, /\ASQLITE ERROR \d+ \(\) : CHECK constraint failed: / => CheckConstraintViolation }.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 }.freeze  

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()

SQLite uses the :sqlite database type.

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

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

[show source] 
   # 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()
[show source] 
   # 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)

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

[show source] 
    # 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()

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

[show source] 
   # 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()

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

[show source] 
    # 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?()

SQLite 3.8.2+ supports the without rowid table constraint

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

SQLite supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.

[show source] 
    # 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?()

SQLite 3.6.19+ supports deferrable foreign key constraints.

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

SQLite 3.8.0+ supports partial indexes.

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

SQLite 3.6.8+ supports savepoints.

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

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

Options:

:server

Set the server to use.

[show source] 
    # 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)

Set the default transaction mode.

[show source] 
   # 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?()

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

[show source] 
    # 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)

Creates a dataset that uses the VALUES clause:

DB.values([[1, 2], [3, 4]])
# VALUES ((1, 2), (3, 4))
[show source] 
    # 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)

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

Options:

:server

Set the server to use.

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