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