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
Whether to keep CURRENT_TIMESTAMP and similar expressions in UTC. By default, the expressions are converted to localtime.
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
Source
# File lib/sequel/adapters/shared/sqlite.rb 58 def database_type 59 :sqlite 60 end
SQLite
uses the :sqlite database type.
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
Return the array of foreign key info hashes using the foreign_key_list
PRAGMA, including information for the :on_update and :on_delete entries.
Source
# File lib/sequel/adapters/shared/sqlite.rb 83 def freeze 84 sqlite_version 85 use_timestamp_timezones? 86 super 87 end
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
Use the index_list and index_info PRAGMAs to determine the indexes on the table.
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
Set the integer_booleans
option using the passed in :integer_boolean option.
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
The version of the server as an integer, where 3.6.19 = 30619. If the server version can’t be determined, 0 is used.
Source
# File lib/sequel/adapters/shared/sqlite.rb 149 def support_without_rowid? 150 sqlite_version >= 30802 151 end
SQLite
3.8.2+ supports the without rowid table constraint
Source
# File lib/sequel/adapters/shared/sqlite.rb 129 def supports_create_table_if_not_exists? 130 sqlite_version >= 30300 131 end
SQLite
supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.
Source
# File lib/sequel/adapters/shared/sqlite.rb 134 def supports_deferrable_foreign_key_constraints? 135 sqlite_version >= 30619 136 end
SQLite
3.6.19+ supports deferrable foreign key constraints.
Source
# File lib/sequel/adapters/shared/sqlite.rb 139 def supports_partial_indexes? 140 sqlite_version >= 30800 141 end
SQLite
3.8.0+ supports partial indexes.
Source
# File lib/sequel/adapters/shared/sqlite.rb 144 def supports_savepoints? 145 sqlite_version >= 30608 146 end
SQLite
3.6.8+ supports savepoints.
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
Array
of symbols specifying the table names in the current database.
Options:
- :server
-
Set the server to use.
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
Set the default transaction mode.
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
SQLite
supports timezones in timestamps, since it just stores them as strings, but it breaks the usage of SQLite’s datetime functions.
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
Creates a dataset that uses the VALUES clause:
DB.values([[1, 2], [3, 4]]) # VALUES ((1, 2), (3, 4))
Source
# File lib/sequel/adapters/shared/sqlite.rb 185 def views(opts=OPTS) 186 tables_and_views({:type => 'view'}, opts) 187 end
Array
of symbols specifying the view names in the current database.
Options:
- :server
-
Set the server to use.
Private Instance Methods
Source
# 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
Dataset
used for parsing foreign key lists
Source
# 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
Dataset
used for parsing schema
Source
# 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
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.
Source
# 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
Run all alter_table commands in a transaction. This is technically only needed for drop column.
Source
# 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
A name to use for the backup table
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
Source
# File lib/sequel/adapters/shared/sqlite.rb 314 def can_add_primary_key_constraint_on_nullable_columns? 315 false 316 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.
Source
# 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
Surround default with parens to appease SQLite
. Add support for GENERATED ALWAYS AS.
Source
# File lib/sequel/adapters/shared/sqlite.rb 330 def column_schema_integer_min_max_values(column) 331 nil 332 end
SQLite
does not restrict the integer or decimal type to a specific range.
Source
# 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
Array
of PRAGMA SQL
statements based on the Database
options that should be applied to new connections.
Source
# 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
Support creating STRICT AND/OR WITHOUT ROWID tables via :strict and :without_rowid options, and VIRTUAL tables with :using option.
Source
# 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
SQLite
support creating temporary views.
Source
# File lib/sequel/adapters/shared/sqlite.rb 382 def database_error_regexps 383 DATABASE_ERROR_REGEXPS 384 end
Source
# 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
Recognize SQLite
error codes if the exception provides access to them.
Source
# 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
The array of column schema hashes for the current columns in the table
Source
# 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
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.
Source
# 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
Does the reverse of on_delete_clause, eg. converts strings like +‘SET NULL’+ to symbols :set_null
.
Source
# 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
Parse the output of the table_info pragma
Source
# 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
supports schema parsing using the table_info PRAGMA, so parse the output of that into the format Sequel
expects.
Source
# File lib/sequel/adapters/shared/sqlite.rb 564 def sqlite_error_code(exception) 565 nil 566 end
Don’t support SQLite
error codes for exceptions by default.
Source
# 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
Backbone of the tables and views support.
Source
# File lib/sequel/adapters/shared/sqlite.rb 577 def type_literal_generic_bignum_symbol(column) 578 column[:auto_increment] ? :integer : super 579 end
SQLite
only supports AUTOINCREMENT on integer columns, not bigint columns, so use integer instead of bigint for those columns.