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
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 163 def tables(opts=OPTS) 164 tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts) 165 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 155 def use_timestamp_timezones? 156 defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false) 157 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 171 def values(v) 172 raise Error, "Cannot provide an empty array for values" if v.empty? 173 @default_dataset.clone(:values=>v) 174 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 180 def views(opts=OPTS) 181 tables_and_views({:type => 'view'}, opts) 182 end
Private Instance Methods
Dataset
used for parsing foreign key lists
# File lib/sequel/adapters/shared/sqlite.rb 187 def _foreign_key_list_ds(table) 188 metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table)) 189 end
Dataset
used for parsing schema
# File lib/sequel/adapters/shared/sqlite.rb 192 def _parse_pragma_ds(table_name, opts) 193 metadata_dataset.with_sql("PRAGMA table_#{'x' if sqlite_version > 33100}info(?)", input_identifier_meth(opts[:dataset]).call(table_name)) 194 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 232 def alter_table_sql(table, op) 233 case op[:op] 234 when :add_index, :drop_index 235 super 236 when :add_column 237 if op[:unique] || op[:primary_key] 238 duplicate_table(table){|columns| columns.push(op)} 239 else 240 super 241 end 242 when :drop_column 243 if sqlite_version >= 33500 244 super 245 else 246 ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}} 247 duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}} 248 end 249 when :rename_column 250 if sqlite_version >= 32500 251 super 252 else 253 ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}} 254 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}} 255 end 256 when :set_column_default 257 duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}} 258 when :set_column_null 259 duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}} 260 when :set_column_type 261 duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}} 262 when :drop_constraint 263 case op[:type] 264 when :primary_key 265 duplicate_table(table) do |columns| 266 columns.each do |s| 267 s[:unique] = false if s[:primary_key] 268 s[:primary_key] = s[:auto_increment] = nil 269 end 270 end 271 when :foreign_key 272 if op[:columns] 273 duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) 274 else 275 duplicate_table(table, :no_foreign_keys=>true) 276 end 277 when :unique 278 duplicate_table(table, :no_unique=>true) 279 else 280 duplicate_table(table) 281 end 282 when :add_constraint 283 duplicate_table(table, :constraints=>[op]) 284 when :add_constraints 285 duplicate_table(table, :constraints=>op[:ops]) 286 else 287 raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" 288 end 289 end
Run all alter_table commands in a transaction. This is technically only needed for drop column.
# File lib/sequel/adapters/shared/sqlite.rb 198 def apply_alter_table(table, ops) 199 fks = fetch("PRAGMA foreign_keys") 200 if fks 201 run "PRAGMA foreign_keys = 0" 202 run "PRAGMA legacy_alter_table = 1" if sqlite_version >= 32600 203 end 204 transaction do 205 if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint || op[:op] == :set_column_null} 206 null_ops, ops = ops.partition{|op| op[:op] == :set_column_null} 207 208 # Apply NULL/NOT NULL ops first, since those should be purely idependent of the constraints. 209 null_ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 210 211 # If you are just doing constraints, apply all of them at the same time, 212 # as otherwise all but the last one get lost. 213 alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)} 214 else 215 # Run each operation separately, as later operations may depend on the 216 # results of earlier operations. 217 ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 218 end 219 end 220 remove_cached_schema(table) 221 ensure 222 if fks 223 run "PRAGMA foreign_keys = 1" 224 run "PRAGMA legacy_alter_table = 0" if sqlite_version >= 32600 225 end 226 end
A name to use for the backup table
# File lib/sequel/adapters/shared/sqlite.rb 299 def backup_table_name(table, opts=OPTS) 300 table = table.gsub('`', '') 301 (opts[:times]||1000).times do |i| 302 table_name = "#{table}_backup#{i}" 303 return table_name unless table_exists?(table_name) 304 end 305 end
# File lib/sequel/adapters/shared/sqlite.rb 291 def begin_new_transaction(conn, opts) 292 mode = opts[:mode] || @transaction_mode 293 sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" 294 log_connection_execute(conn, sql) 295 set_transaction_isolation(conn, opts) 296 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 309 def can_add_primary_key_constraint_on_nullable_columns? 310 false 311 end
Surround default with parens to appease SQLite
. Add support for GENERATED ALWAYS AS.
# File lib/sequel/adapters/shared/sqlite.rb 314 def column_definition_default_sql(sql, column) 315 sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) 316 if (generated = column[:generated_always_as]) 317 if (generated_type = column[:generated_type]) && (generated_type == :stored || generated_type == :virtual) 318 generated_type = generated_type.to_s.upcase 319 end 320 sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{generated_type}" 321 end 322 end
SQLite
does not restrict the integer or decimal type to a specific range.
# File lib/sequel/adapters/shared/sqlite.rb 325 def column_schema_integer_min_max_values(column) 326 nil 327 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 332 def connection_pragmas 333 ps = [] 334 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 335 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 336 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 337 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 338 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 339 if v = opts[prag] 340 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 341 ps << "PRAGMA #{prag} = #{v}" 342 end 343 end 344 ps 345 end
Support creating STRICT tables via :strict option
# File lib/sequel/adapters/shared/sqlite.rb 348 def create_table_sql(name, generator, options) 349 "#{super}#{' STRICT' if options[:strict]}" 350 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 353 def create_view_prefix_sql(name, options) 354 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 355 end
# File lib/sequel/adapters/shared/sqlite.rb 367 def database_error_regexps 368 DATABASE_ERROR_REGEXPS 369 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 372 def database_specific_error_class(exception, opts) 373 case sqlite_error_code(exception) 374 when 1299 375 NotNullConstraintViolation 376 when 1555, 2067, 2579 377 UniqueConstraintViolation 378 when 787 379 ForeignKeyConstraintViolation 380 when 275 381 CheckConstraintViolation 382 when 19 383 ConstraintViolation 384 when 517 385 SerializationFailure 386 else 387 super 388 end 389 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 392 def defined_columns_for(table) 393 cols = parse_pragma(table, OPTS) 394 cols.each do |c| 395 c[:default] = LiteralString.new(c[:default]) if c[:default] 396 c[:type] = c[:db_type] 397 end 398 cols 399 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 404 def duplicate_table(table, opts=OPTS) 405 remove_cached_schema(table) 406 def_columns = defined_columns_for(table) 407 old_columns = def_columns.map{|c| c[:name]} 408 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 409 410 yield def_columns if defined?(yield) 411 412 constraints = (opts[:constraints] || []).dup 413 pks = [] 414 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 415 if pks.length > 1 416 constraints << {:type=>:primary_key, :columns=>pks} 417 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 418 end 419 420 # If dropping a foreign key constraint, drop all foreign key constraints, 421 # as there is no way to determine which one to drop. 422 unless opts[:no_foreign_keys] 423 fks = foreign_key_list(table) 424 425 # If dropping a column, if there is a foreign key with that 426 # column, don't include it when building a copy of the table. 427 if ocp = opts[:old_columns_proc] 428 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 429 end 430 431 # Skip any foreign key columns where a constraint for those 432 # foreign keys is being dropped. 433 if sfkc = opts[:skip_foreign_key_columns] 434 fks.delete_if{|c| c[:columns] == sfkc} 435 end 436 437 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 438 end 439 440 # Determine unique constraints and make sure the new columns have them 441 unique_columns = [] 442 skip_indexes = [] 443 indexes(table, :only_autocreated=>true).each do |name, h| 444 skip_indexes << name 445 if h[:unique] && !opts[:no_unique] 446 if h[:columns].length == 1 447 unique_columns.concat(h[:columns]) 448 elsif h[:columns].map(&:to_s) != pks 449 constraints << {:type=>:unique, :columns=>h[:columns]} 450 end 451 end 452 end 453 unique_columns -= pks 454 unless unique_columns.empty? 455 unique_columns.map!{|c| quote_identifier(c)} 456 def_columns.each do |c| 457 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false 458 end 459 end 460 461 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 462 new_columns = old_columns.dup 463 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 464 465 qt = quote_schema_table(table) 466 bt = quote_identifier(backup_table_name(qt)) 467 a = [ 468 "ALTER TABLE #{qt} RENAME TO #{bt}", 469 "CREATE TABLE #{qt}(#{def_columns_str})", 470 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 471 "DROP TABLE #{bt}" 472 ] 473 indexes(table).each do |name, h| 474 next if skip_indexes.include?(name) 475 if (h[:columns].map(&:to_s) - new_columns).empty? 476 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 477 end 478 end 479 a 480 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 484 def on_delete_sql_to_sym(str) 485 case str 486 when 'RESTRICT' 487 :restrict 488 when 'CASCADE' 489 :cascade 490 when 'SET NULL' 491 :set_null 492 when 'SET DEFAULT' 493 :set_default 494 when 'NO ACTION' 495 :no_action 496 end 497 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 500 def parse_pragma(table_name, opts) 501 pks = 0 502 sch = _parse_pragma_ds(table_name, opts).map do |row| 503 if sqlite_version > 33100 504 # table_xinfo PRAGMA used, remove hidden columns 505 # that are not generated columns 506 if row[:generated] = (row.delete(:hidden) != 0) 507 row[:type] = row[:type].sub(' GENERATED ALWAYS', '') 508 end 509 end 510 511 row.delete(:cid) 512 row[:allow_null] = row.delete(:notnull).to_i == 0 513 row[:default] = row.delete(:dflt_value) 514 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 515 row[:db_type] = row.delete(:type) 516 if row[:primary_key] = row.delete(:pk).to_i > 0 517 pks += 1 518 # Guess that an integer primary key uses auto increment, 519 # since that is Sequel's default and SQLite does not provide 520 # a way to introspect whether it is actually autoincrementing. 521 row[:auto_increment] = row[:db_type].downcase == 'integer' 522 end 523 row[:type] = schema_column_type(row[:db_type]) 524 row 525 end 526 527 sch.compact! 528 529 if pks > 1 530 # SQLite does not allow use of auto increment for tables 531 # with composite primary keys, so remove auto_increment 532 # if composite primary keys are detected. 533 sch.each{|r| r.delete(:auto_increment)} 534 end 535 536 sch 537 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 541 def schema_parse_table(table_name, opts) 542 m = output_identifier_meth(opts[:dataset]) 543 parse_pragma(table_name, opts).map do |row| 544 [m.call(row.delete(:name)), row] 545 end 546 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 549 def sqlite_error_code(exception) 550 nil 551 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 554 def tables_and_views(filter, opts) 555 m = output_identifier_meth 556 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 557 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 562 def type_literal_generic_bignum_symbol(column) 563 column[:auto_increment] ? :integer : super 564 end