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
Array
of PRAGMA SQL
statements based on the Database
options that should be applied to new connections.
# File lib/sequel/adapters/shared/sqlite.rb 325 def connection_pragmas 326 ps = [] 327 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 328 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 329 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 330 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 331 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 332 if v = opts[prag] 333 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 334 ps << "PRAGMA #{prag} = #{v}" 335 end 336 end 337 ps 338 end
Support creating STRICT tables via :strict option
# File lib/sequel/adapters/shared/sqlite.rb 341 def create_table_sql(name, generator, options) 342 "#{super}#{' STRICT' if options[:strict]}" 343 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 346 def create_view_prefix_sql(name, options) 347 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 348 end
# File lib/sequel/adapters/shared/sqlite.rb 359 def database_error_regexps 360 DATABASE_ERROR_REGEXPS 361 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 364 def database_specific_error_class(exception, opts) 365 case sqlite_error_code(exception) 366 when 1299 367 NotNullConstraintViolation 368 when 1555, 2067, 2579 369 UniqueConstraintViolation 370 when 787 371 ForeignKeyConstraintViolation 372 when 275 373 CheckConstraintViolation 374 when 19 375 ConstraintViolation 376 when 517 377 SerializationFailure 378 else 379 super 380 end 381 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 384 def defined_columns_for(table) 385 cols = parse_pragma(table, OPTS) 386 cols.each do |c| 387 c[:default] = LiteralString.new(c[:default]) if c[:default] 388 c[:type] = c[:db_type] 389 end 390 cols 391 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 396 def duplicate_table(table, opts=OPTS) 397 remove_cached_schema(table) 398 def_columns = defined_columns_for(table) 399 old_columns = def_columns.map{|c| c[:name]} 400 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 401 402 yield def_columns if defined?(yield) 403 404 constraints = (opts[:constraints] || []).dup 405 pks = [] 406 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 407 if pks.length > 1 408 constraints << {:type=>:primary_key, :columns=>pks} 409 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 410 end 411 412 # If dropping a foreign key constraint, drop all foreign key constraints, 413 # as there is no way to determine which one to drop. 414 unless opts[:no_foreign_keys] 415 fks = foreign_key_list(table) 416 417 # If dropping a column, if there is a foreign key with that 418 # column, don't include it when building a copy of the table. 419 if ocp = opts[:old_columns_proc] 420 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 421 end 422 423 # Skip any foreign key columns where a constraint for those 424 # foreign keys is being dropped. 425 if sfkc = opts[:skip_foreign_key_columns] 426 fks.delete_if{|c| c[:columns] == sfkc} 427 end 428 429 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 430 end 431 432 # Determine unique constraints and make sure the new columns have them 433 unique_columns = [] 434 skip_indexes = [] 435 indexes(table, :only_autocreated=>true).each do |name, h| 436 skip_indexes << name 437 if h[:unique] && !opts[:no_unique] 438 if h[:columns].length == 1 439 unique_columns.concat(h[:columns]) 440 elsif h[:columns].map(&:to_s) != pks 441 constraints << {:type=>:unique, :columns=>h[:columns]} 442 end 443 end 444 end 445 unique_columns -= pks 446 unless unique_columns.empty? 447 unique_columns.map!{|c| quote_identifier(c)} 448 def_columns.each do |c| 449 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false 450 end 451 end 452 453 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 454 new_columns = old_columns.dup 455 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 456 457 qt = quote_schema_table(table) 458 bt = quote_identifier(backup_table_name(qt)) 459 a = [ 460 "ALTER TABLE #{qt} RENAME TO #{bt}", 461 "CREATE TABLE #{qt}(#{def_columns_str})", 462 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 463 "DROP TABLE #{bt}" 464 ] 465 indexes(table).each do |name, h| 466 next if skip_indexes.include?(name) 467 if (h[:columns].map(&:to_s) - new_columns).empty? 468 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 469 end 470 end 471 a 472 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 476 def on_delete_sql_to_sym(str) 477 case str 478 when 'RESTRICT' 479 :restrict 480 when 'CASCADE' 481 :cascade 482 when 'SET NULL' 483 :set_null 484 when 'SET DEFAULT' 485 :set_default 486 when 'NO ACTION' 487 :no_action 488 end 489 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 492 def parse_pragma(table_name, opts) 493 pks = 0 494 sch = _parse_pragma_ds(table_name, opts).map do |row| 495 if sqlite_version > 33100 496 # table_xinfo PRAGMA used, remove hidden columns 497 # that are not generated columns 498 if row[:generated] = (row.delete(:hidden) != 0) 499 next unless row[:type].end_with?(' GENERATED ALWAYS') 500 row[:type] = row[:type].sub(' GENERATED ALWAYS', '') 501 end 502 end 503 504 row.delete(:cid) 505 row[:allow_null] = row.delete(:notnull).to_i == 0 506 row[:default] = row.delete(:dflt_value) 507 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 508 row[:db_type] = row.delete(:type) 509 if row[:primary_key] = row.delete(:pk).to_i > 0 510 pks += 1 511 # Guess that an integer primary key uses auto increment, 512 # since that is Sequel's default and SQLite does not provide 513 # a way to introspect whether it is actually autoincrementing. 514 row[:auto_increment] = row[:db_type].downcase == 'integer' 515 end 516 row[:type] = schema_column_type(row[:db_type]) 517 row 518 end 519 520 sch.compact! 521 522 if pks > 1 523 # SQLite does not allow use of auto increment for tables 524 # with composite primary keys, so remove auto_increment 525 # if composite primary keys are detected. 526 sch.each{|r| r.delete(:auto_increment)} 527 end 528 529 sch 530 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 534 def schema_parse_table(table_name, opts) 535 m = output_identifier_meth(opts[:dataset]) 536 parse_pragma(table_name, opts).map do |row| 537 [m.call(row.delete(:name)), row] 538 end 539 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 542 def sqlite_error_code(exception) 543 nil 544 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 547 def tables_and_views(filter, opts) 548 m = output_identifier_meth 549 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 550 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 555 def type_literal_generic_bignum_symbol(column) 556 column[:auto_increment] ? :integer : super 557 end