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