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
A symbol signifying the value of the default transaction mode
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.
# File lib/sequel/adapters/shared/sqlite.rb, line 48 def database_type :sqlite 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.
# File lib/sequel/adapters/shared/sqlite.rb, line 59 def foreign_key_list(table, opts=OPTS) m = output_identifier_meth h = {} metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table)).each do |row| if r = h[row[:id]] r[:columns] << m.call(row[:from]) r[:key] << m.call(row[:to]) if r[:key] else 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])} end end h.values end
# File lib/sequel/adapters/shared/sqlite.rb, line 73 def freeze sqlite_version use_timestamp_timezones? super end
Use the index_list and index_info PRAGMAs to determine the indexes on the table.
# File lib/sequel/adapters/shared/sqlite.rb, line 80 def indexes(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth indexes = {} table = table.value if table.is_a?(Sequel::SQL::Identifier) metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r| if opts[:only_autocreated] # If specifically asked for only autocreated indexes, then return those an only those next unless r[:name] =~ /\Asqlite_autoindex_/ elsif r.has_key?(:origin) # If origin is set, then only exclude primary key indexes and partial indexes next if r[:origin] == 'pk' next if r[:partial].to_i == 1 else # When :origin key not present, assume any autoindex could be a primary key one and exclude it next if r[:name] =~ /\Asqlite_autoindex_/ end indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1} end indexes.each do |k, v| v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)} end indexes end
Set the #integer_booleans option using the passed in :integer_boolean option.
# File lib/sequel/adapters/shared/sqlite.rb, line 53 def set_integer_booleans @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true 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.
# File lib/sequel/adapters/shared/sqlite.rb, line 108 def sqlite_version return @sqlite_version if defined?(@sqlite_version) @sqlite_version = begin v = fetch('SELECT sqlite_version()').single_value [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])} rescue 0 end end
SQLite supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.
# File lib/sequel/adapters/shared/sqlite.rb, line 119 def supports_create_table_if_not_exists? sqlite_version >= 30300 end
SQLite 3.6.19+ supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/sqlite.rb, line 124 def supports_deferrable_foreign_key_constraints? sqlite_version >= 30619 end
SQLite 3.8.0+ supports partial indexes.
# File lib/sequel/adapters/shared/sqlite.rb, line 129 def supports_partial_indexes? sqlite_version >= 30800 end
SQLite 3.6.8+ supports savepoints.
# File lib/sequel/adapters/shared/sqlite.rb, line 134 def supports_savepoints? sqlite_version >= 30608 end
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, line 153 def tables(opts=OPTS) tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts) end
Set the default transaction mode.
# File lib/sequel/adapters/shared/sqlite.rb, line 39 def transaction_mode=(value) if TRANSACTION_MODE.include?(value) @transaction_mode = value else raise Error, "Invalid value for transaction_mode. Please specify one of :deferred, :immediate, :exclusive, nil" end end
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, line 145 def use_timestamp_timezones? defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false) end
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, line 161 def values(v) @default_dataset.clone(:values=>v) end
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, line 169 def views(opts=OPTS) tables_and_views({:type => 'view'}, opts) end
Private Instance Methods
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.
# File lib/sequel/adapters/shared/sqlite.rb, line 200 def alter_table_sql(table, op) case op[:op] when :add_index, :drop_index super when :add_column if op[:unique] || op[:primary_key] duplicate_table(table){|columns| columns.push(op)} else super end when :drop_column ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}} duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}} when :rename_column ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}} 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}} when :set_column_default duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}} when :set_column_null duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}} when :set_column_type duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}} when :drop_constraint case op[:type] when :primary_key duplicate_table(table){|columns| columns.each{|s| s[:primary_key] = s[:auto_increment] = nil}} when :foreign_key if op[:columns] duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) else duplicate_table(table, :no_foreign_keys=>true) end else duplicate_table(table) end when :add_constraint duplicate_table(table, :constraints=>[op]) when :add_constraints duplicate_table(table, :constraints=>op[:ops]) else raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" end end
Run all alter_table commands in a transaction. This is technically only needed for drop column.
# File lib/sequel/adapters/shared/sqlite.rb, line 177 def apply_alter_table(table, ops) fks = fetch("PRAGMA foreign_keys") run "PRAGMA foreign_keys = 0" if fks transaction do if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint} # If you are just doing constraints, apply all of them at the same time, # as otherwise all but the last one get lost. alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)} else # Run each operation separately, as later operations may depend on the # results of earlier operations. ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} end end remove_cached_schema(table) ensure run "PRAGMA foreign_keys = 1" if fks end
A name to use for the backup table
# File lib/sequel/adapters/shared/sqlite.rb, line 252 def backup_table_name(table, opts=OPTS) table = table.gsub('`', '') (opts[:times]||1000).times do |i| table_name = "#{table}_backup#{i}" return table_name unless table_exists?(table_name) end end
# File lib/sequel/adapters/shared/sqlite.rb, line 244 def begin_new_transaction(conn, opts) mode = opts[:mode] || @transaction_mode sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" log_connection_execute(conn, sql) set_transaction_isolation(conn, opts) end
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, line 262 def can_add_primary_key_constraint_on_nullable_columns? false end
Surround default with parens to appease SQLite
# File lib/sequel/adapters/shared/sqlite.rb, line 267 def column_definition_default_sql(sql, column) sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) end
Array of PRAGMA SQL statements based on the Database options that should be applied to new connections.
# File lib/sequel/adapters/shared/sqlite.rb, line 273 def connection_pragmas ps = [] v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| if v = opts[prag] raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) ps << "PRAGMA #{prag} = #{v}" end end ps end
SQLite support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb, line 289 def create_view_prefix_sql(name, options) create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) end
# File lib/sequel/adapters/shared/sqlite.rb, line 301 def database_error_regexps DATABASE_ERROR_REGEXPS end
Recognize SQLite error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb, line 306 def database_specific_error_class(exception, opts) case sqlite_error_code(exception) when 1299 NotNullConstraintViolation when 2067 UniqueConstraintViolation when 787 ForeignKeyConstraintViolation when 275 CheckConstraintViolation when 19 ConstraintViolation else super end end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb, line 324 def defined_columns_for(table) cols = parse_pragma(table, {}) cols.each do |c| c[:default] = LiteralString.new(c[:default]) if c[:default] c[:type] = c[:db_type] end cols end
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, line 336 def duplicate_table(table, opts=OPTS) remove_cached_schema(table) def_columns = defined_columns_for(table) old_columns = def_columns.map{|c| c[:name]} opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] yield def_columns if block_given? constraints = (opts[:constraints] || []).dup pks = [] def_columns.each{|c| pks << c[:name] if c[:primary_key]} if pks.length > 1 constraints << {:type=>:primary_key, :columns=>pks} def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} end # If dropping a foreign key constraint, drop all foreign key constraints, # as there is no way to determine which one to drop. unless opts[:no_foreign_keys] fks = foreign_key_list(table) # If dropping a column, if there is a foreign key with that # column, don't include it when building a copy of the table. if ocp = opts[:old_columns_proc] fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} end # Skip any foreign key columns where a constraint for those # foreign keys is being dropped. if sfkc = opts[:skip_foreign_key_columns] fks.delete_if{|c| c[:columns] == sfkc} end constraints.concat(fks.each{|h| h[:type] = :foreign_key}) end # Determine unique constraints and make sure the new columns have them unique_columns = [] skip_indexes = [] indexes(table, :only_autocreated=>true).each do |name, h| skip_indexes << name if h[:columns].length == 1 && h[:unique] unique_columns.concat(h[:columns]) end end unique_columns -= pks unless unique_columns.empty? unique_columns.map!{|c| quote_identifier(c)} def_columns.each do |c| c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) end end def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') new_columns = old_columns.dup opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] qt = quote_schema_table(table) bt = quote_identifier(backup_table_name(qt)) a = [ "ALTER TABLE #{qt} RENAME TO #{bt}", "CREATE TABLE #{qt}(#{def_columns_str})", "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", "DROP TABLE #{bt}" ] indexes(table).each do |name, h| next if skip_indexes.include?(name) if (h[:columns].map(&:to_s) - new_columns).empty? a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) end end a end
Does the reverse of on_delete_clause, eg. converts strings like +'SET
NULL'+ to symbols :set_null
.
# File lib/sequel/adapters/shared/sqlite.rb, line 412 def on_delete_sql_to_sym(str) case str when 'RESTRICT' :restrict when 'CASCADE' :cascade when 'SET NULL' :set_null when 'SET DEFAULT' :set_default when 'NO ACTION' :no_action end end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb, line 428 def parse_pragma(table_name, opts) pks = 0 sch = metadata_dataset.with_sql("PRAGMA table_info(?)", input_identifier_meth(opts[:dataset]).call(table_name)).map do |row| row.delete(:cid) row[:allow_null] = row.delete(:notnull).to_i == 0 row[:default] = row.delete(:dflt_value) row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' row[:db_type] = row.delete(:type) if row[:primary_key] = row.delete(:pk).to_i > 0 pks += 1 # Guess that an integer primary key uses auto increment, # since that is Sequel's default and SQLite does not provide # a way to introspect whether it is actually autoincrementing. row[:auto_increment] = row[:db_type].downcase == 'integer' end row[:type] = schema_column_type(row[:db_type]) row end if pks > 1 # SQLite does not allow use of auto increment for tables # with composite primary keys, so remove auto_increment # if composite primary keys are detected. sch.each{|r| r.delete(:auto_increment)} end sch end
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, line 459 def schema_parse_table(table_name, opts) m = output_identifier_meth(opts[:dataset]) parse_pragma(table_name, opts).map do |row| [m.call(row.delete(:name)), row] end end
Don't support SQLite error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb, line 467 def sqlite_error_code(exception) nil end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb, line 472 def tables_and_views(filter, opts) m = output_identifier_meth metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} end
SQLite only supports AUTOINCREMENT on integer columns, not bigint columns, so use integer instead of bigint for those columns.
# File lib/sequel/adapters/shared/sqlite.rb, line 480 def type_literal_generic_bignum_symbol(column) column[:auto_increment] ? :integer : super end