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
SQLite
uses the :sqlite database type.
# File lib/sequel/adapters/shared/sqlite.rb 58 def database_type 59 :sqlite 60 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 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
# File lib/sequel/adapters/shared/sqlite.rb 83 def freeze 84 sqlite_version 85 use_timestamp_timezones? 86 super 87 end
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] =~ /\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 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
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
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
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
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
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
SQLite
3.6.8+ supports savepoints.
# File lib/sequel/adapters/shared/sqlite.rb 144 def supports_savepoints? 145 sqlite_version >= 30608 146 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 168 def tables(opts=OPTS) 169 tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts) 170 end
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
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
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
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
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
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
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 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 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
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
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.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
# 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
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
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
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
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
Support creating STRICT AND/OR WITHOUT ROWID tables via :strict and :without_rowid options
# 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 else 361 super 362 end 363 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 366 def create_view_prefix_sql(name, options) 367 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 368 end
# File lib/sequel/adapters/shared/sqlite.rb 380 def database_error_regexps 381 DATABASE_ERROR_REGEXPS 382 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 385 def database_specific_error_class(exception, opts) 386 case sqlite_error_code(exception) 387 when 1299 388 NotNullConstraintViolation 389 when 1555, 2067, 2579 390 UniqueConstraintViolation 391 when 787 392 ForeignKeyConstraintViolation 393 when 275 394 CheckConstraintViolation 395 when 19 396 ConstraintViolation 397 when 517 398 SerializationFailure 399 else 400 super 401 end 402 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 405 def defined_columns_for(table) 406 cols = parse_pragma(table, OPTS) 407 cols.each do |c| 408 c[:default] = LiteralString.new(c[:default]) if c[:default] 409 c[:type] = c[:db_type] 410 end 411 cols 412 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 417 def duplicate_table(table, opts=OPTS) 418 remove_cached_schema(table) 419 def_columns = defined_columns_for(table) 420 old_columns = def_columns.map{|c| c[:name]} 421 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 422 423 yield def_columns if defined?(yield) 424 425 constraints = (opts[:constraints] || []).dup 426 pks = [] 427 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 428 if pks.length > 1 429 constraints << {:type=>:primary_key, :columns=>pks} 430 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 431 end 432 433 # If dropping a foreign key constraint, drop all foreign key constraints, 434 # as there is no way to determine which one to drop. 435 unless opts[:no_foreign_keys] 436 fks = foreign_key_list(table) 437 438 # If dropping a column, if there is a foreign key with that 439 # column, don't include it when building a copy of the table. 440 if ocp = opts[:old_columns_proc] 441 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 442 end 443 444 # Skip any foreign key columns where a constraint for those 445 # foreign keys is being dropped. 446 if sfkc = opts[:skip_foreign_key_columns] 447 fks.delete_if{|c| c[:columns] == sfkc} 448 end 449 450 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 451 end 452 453 # Determine unique constraints and make sure the new columns have them 454 unique_columns = [] 455 skip_indexes = [] 456 indexes(table, :only_autocreated=>true).each do |name, h| 457 skip_indexes << name 458 if h[:unique] && !opts[:no_unique] 459 if h[:columns].length == 1 460 unique_columns.concat(h[:columns]) 461 elsif h[:columns].map(&:to_s) != pks 462 constraints << {:type=>:unique, :columns=>h[:columns]} 463 end 464 end 465 end 466 unique_columns -= pks 467 unless unique_columns.empty? 468 unique_columns.map!{|c| quote_identifier(c)} 469 def_columns.each do |c| 470 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false 471 end 472 end 473 474 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 475 new_columns = old_columns.dup 476 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 477 478 qt = quote_schema_table(table) 479 bt = quote_identifier(backup_table_name(qt)) 480 a = [ 481 "ALTER TABLE #{qt} RENAME TO #{bt}", 482 "CREATE TABLE #{qt}(#{def_columns_str})", 483 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 484 "DROP TABLE #{bt}" 485 ] 486 indexes(table).each do |name, h| 487 next if skip_indexes.include?(name) 488 if (h[:columns].map(&:to_s) - new_columns).empty? 489 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 490 end 491 end 492 a 493 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 497 def on_delete_sql_to_sym(str) 498 case str 499 when 'RESTRICT' 500 :restrict 501 when 'CASCADE' 502 :cascade 503 when 'SET NULL' 504 :set_null 505 when 'SET DEFAULT' 506 :set_default 507 when 'NO ACTION' 508 :no_action 509 end 510 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 513 def parse_pragma(table_name, opts) 514 pks = 0 515 sch = _parse_pragma_ds(table_name, opts).map do |row| 516 if sqlite_version > 33100 517 # table_xinfo PRAGMA used, remove hidden columns 518 # that are not generated columns 519 if row[:generated] = (row.delete(:hidden) != 0) 520 row[:type] = row[:type].sub(' GENERATED ALWAYS', '') 521 end 522 end 523 524 row.delete(:cid) 525 row[:allow_null] = row.delete(:notnull).to_i == 0 526 row[:default] = row.delete(:dflt_value) 527 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 528 row[:db_type] = row.delete(:type) 529 if row[:primary_key] = row.delete(:pk).to_i > 0 530 pks += 1 531 # Guess that an integer primary key uses auto increment, 532 # since that is Sequel's default and SQLite does not provide 533 # a way to introspect whether it is actually autoincrementing. 534 row[:auto_increment] = row[:db_type].downcase == 'integer' 535 end 536 row[:type] = schema_column_type(row[:db_type]) 537 row 538 end 539 540 sch.compact! 541 542 if pks > 1 543 # SQLite does not allow use of auto increment for tables 544 # with composite primary keys, so remove auto_increment 545 # if composite primary keys are detected. 546 sch.each{|r| r.delete(:auto_increment)} 547 end 548 549 sch 550 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 554 def schema_parse_table(table_name, opts) 555 m = output_identifier_meth(opts[:dataset]) 556 parse_pragma(table_name, opts).map do |row| 557 [m.call(row.delete(:name)), row] 558 end 559 end
Don’t support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 562 def sqlite_error_code(exception) 563 nil 564 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 567 def tables_and_views(filter, opts) 568 m = output_identifier_meth 569 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 570 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 575 def type_literal_generic_bignum_symbol(column) 576 column[:auto_increment] ? :integer : super 577 end