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 ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}} 243 duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}} 244 when :rename_column 245 if sqlite_version >= 32500 246 super 247 else 248 ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}} 249 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}} 250 end 251 when :set_column_default 252 duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}} 253 when :set_column_null 254 duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}} 255 when :set_column_type 256 duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}} 257 when :drop_constraint 258 case op[:type] 259 when :primary_key 260 duplicate_table(table) do |columns| 261 columns.each do |s| 262 s[:unique] = false if s[:primary_key] 263 s[:primary_key] = s[:auto_increment] = nil 264 end 265 end 266 when :foreign_key 267 if op[:columns] 268 duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) 269 else 270 duplicate_table(table, :no_foreign_keys=>true) 271 end 272 when :unique 273 duplicate_table(table, :no_unique=>true) 274 else 275 duplicate_table(table) 276 end 277 when :add_constraint 278 duplicate_table(table, :constraints=>[op]) 279 when :add_constraints 280 duplicate_table(table, :constraints=>op[:ops]) 281 else 282 raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" 283 end 284 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 294 def backup_table_name(table, opts=OPTS) 295 table = table.gsub('`', '') 296 (opts[:times]||1000).times do |i| 297 table_name = "#{table}_backup#{i}" 298 return table_name unless table_exists?(table_name) 299 end 300 end
# File lib/sequel/adapters/shared/sqlite.rb 286 def begin_new_transaction(conn, opts) 287 mode = opts[:mode] || @transaction_mode 288 sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" 289 log_connection_execute(conn, sql) 290 set_transaction_isolation(conn, opts) 291 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 304 def can_add_primary_key_constraint_on_nullable_columns? 305 false 306 end
Surround default with parens to appease SQLite
. Add support for GENERATED ALWAYS AS.
# File lib/sequel/adapters/shared/sqlite.rb 309 def column_definition_default_sql(sql, column) 310 sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) 311 if (generated = column[:generated_always_as]) 312 if (generated_type = column[:generated_type]) && (generated_type == :stored || generated_type == :virtual) 313 generated_type = generated_type.to_s.upcase 314 end 315 sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{generated_type}" 316 end 317 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 321 def connection_pragmas 322 ps = [] 323 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 324 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 325 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 326 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 327 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 328 if v = opts[prag] 329 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 330 ps << "PRAGMA #{prag} = #{v}" 331 end 332 end 333 ps 334 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 337 def create_view_prefix_sql(name, options) 338 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 339 end
# File lib/sequel/adapters/shared/sqlite.rb 349 def database_error_regexps 350 DATABASE_ERROR_REGEXPS 351 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 354 def database_specific_error_class(exception, opts) 355 case sqlite_error_code(exception) 356 when 1299 357 NotNullConstraintViolation 358 when 1555, 2067, 2579 359 UniqueConstraintViolation 360 when 787 361 ForeignKeyConstraintViolation 362 when 275 363 CheckConstraintViolation 364 when 19 365 ConstraintViolation 366 when 517 367 SerializationFailure 368 else 369 super 370 end 371 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 374 def defined_columns_for(table) 375 cols = parse_pragma(table, OPTS) 376 cols.each do |c| 377 c[:default] = LiteralString.new(c[:default]) if c[:default] 378 c[:type] = c[:db_type] 379 end 380 cols 381 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 386 def duplicate_table(table, opts=OPTS) 387 remove_cached_schema(table) 388 def_columns = defined_columns_for(table) 389 old_columns = def_columns.map{|c| c[:name]} 390 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 391 392 yield def_columns if block_given? 393 394 constraints = (opts[:constraints] || []).dup 395 pks = [] 396 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 397 if pks.length > 1 398 constraints << {:type=>:primary_key, :columns=>pks} 399 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 400 end 401 402 # If dropping a foreign key constraint, drop all foreign key constraints, 403 # as there is no way to determine which one to drop. 404 unless opts[:no_foreign_keys] 405 fks = foreign_key_list(table) 406 407 # If dropping a column, if there is a foreign key with that 408 # column, don't include it when building a copy of the table. 409 if ocp = opts[:old_columns_proc] 410 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 411 end 412 413 # Skip any foreign key columns where a constraint for those 414 # foreign keys is being dropped. 415 if sfkc = opts[:skip_foreign_key_columns] 416 fks.delete_if{|c| c[:columns] == sfkc} 417 end 418 419 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 420 end 421 422 # Determine unique constraints and make sure the new columns have them 423 unique_columns = [] 424 skip_indexes = [] 425 indexes(table, :only_autocreated=>true).each do |name, h| 426 skip_indexes << name 427 if h[:unique] 428 if h[:columns].length == 1 429 unique_columns.concat(h[:columns]) 430 elsif h[:columns].map(&:to_s) != pks && !opts[:no_unique] 431 constraints << {:type=>:unique, :columns=>h[:columns]} 432 end 433 end 434 end 435 unique_columns -= pks 436 unless unique_columns.empty? 437 unique_columns.map!{|c| quote_identifier(c)} 438 def_columns.each do |c| 439 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false 440 end 441 end 442 443 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 444 new_columns = old_columns.dup 445 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 446 447 qt = quote_schema_table(table) 448 bt = quote_identifier(backup_table_name(qt)) 449 a = [ 450 "ALTER TABLE #{qt} RENAME TO #{bt}", 451 "CREATE TABLE #{qt}(#{def_columns_str})", 452 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 453 "DROP TABLE #{bt}" 454 ] 455 indexes(table).each do |name, h| 456 next if skip_indexes.include?(name) 457 if (h[:columns].map(&:to_s) - new_columns).empty? 458 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 459 end 460 end 461 a 462 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 466 def on_delete_sql_to_sym(str) 467 case str 468 when 'RESTRICT' 469 :restrict 470 when 'CASCADE' 471 :cascade 472 when 'SET NULL' 473 :set_null 474 when 'SET DEFAULT' 475 :set_default 476 when 'NO ACTION' 477 :no_action 478 end 479 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 482 def parse_pragma(table_name, opts) 483 pks = 0 484 sch = _parse_pragma_ds(table_name, opts).map do |row| 485 if sqlite_version > 33100 486 # table_xinfo PRAGMA used, remove hidden columns 487 # that are not generated columns 488 if row[:generated] = (row.delete(:hidden) != 0) 489 next unless row[:type].end_with?(' GENERATED ALWAYS') 490 row[:type] = row[:type].sub(' GENERATED ALWAYS', '') 491 end 492 end 493 494 row.delete(:cid) 495 row[:allow_null] = row.delete(:notnull).to_i == 0 496 row[:default] = row.delete(:dflt_value) 497 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 498 row[:db_type] = row.delete(:type) 499 if row[:primary_key] = row.delete(:pk).to_i > 0 500 pks += 1 501 # Guess that an integer primary key uses auto increment, 502 # since that is Sequel's default and SQLite does not provide 503 # a way to introspect whether it is actually autoincrementing. 504 row[:auto_increment] = row[:db_type].downcase == 'integer' 505 end 506 row[:type] = schema_column_type(row[:db_type]) 507 row 508 end 509 510 sch.compact! 511 512 if pks > 1 513 # SQLite does not allow use of auto increment for tables 514 # with composite primary keys, so remove auto_increment 515 # if composite primary keys are detected. 516 sch.each{|r| r.delete(:auto_increment)} 517 end 518 519 sch 520 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 524 def schema_parse_table(table_name, opts) 525 m = output_identifier_meth(opts[:dataset]) 526 parse_pragma(table_name, opts).map do |row| 527 [m.call(row.delete(:name)), row] 528 end 529 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 532 def sqlite_error_code(exception) 533 nil 534 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 537 def tables_and_views(filter, opts) 538 m = output_identifier_meth 539 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 540 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 545 def type_literal_generic_bignum_symbol(column) 546 column[:auto_increment] ? :integer : super 547 end