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
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 53 def database_type 54 :sqlite 55 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 64 def foreign_key_list(table, opts=OPTS) 65 m = output_identifier_meth 66 h = {} 67 _foreign_key_list_ds(table).each do |row| 68 if r = h[row[:id]] 69 r[:columns] << m.call(row[:from]) 70 r[:key] << m.call(row[:to]) if r[:key] 71 else 72 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])} 73 end 74 end 75 h.values 76 end
# File lib/sequel/adapters/shared/sqlite.rb 78 def freeze 79 sqlite_version 80 use_timestamp_timezones? 81 super 82 end
Use the index_list and index_info PRAGMAs to determine the indexes on the table.
# File lib/sequel/adapters/shared/sqlite.rb 85 def indexes(table, opts=OPTS) 86 m = output_identifier_meth 87 im = input_identifier_meth 88 indexes = {} 89 table = table.value if table.is_a?(Sequel::SQL::Identifier) 90 metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r| 91 if opts[:only_autocreated] 92 # If specifically asked for only autocreated indexes, then return those an only those 93 next unless r[:name] =~ /\Asqlite_autoindex_/ 94 elsif r.has_key?(:origin) 95 # If origin is set, then only exclude primary key indexes and partial indexes 96 next if r[:origin] == 'pk' 97 next if r[:partial].to_i == 1 98 else 99 # When :origin key not present, assume any autoindex could be a primary key one and exclude it 100 next if r[:name] =~ /\Asqlite_autoindex_/ 101 end 102 103 indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1} 104 end 105 indexes.each do |k, v| 106 v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)} 107 end 108 indexes 109 end
Set the integer_booleans
option using the passed in :integer_boolean option.
# File lib/sequel/adapters/shared/sqlite.rb 58 def set_integer_booleans 59 @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true 60 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 113 def sqlite_version 114 return @sqlite_version if defined?(@sqlite_version) 115 @sqlite_version = begin 116 v = fetch('SELECT sqlite_version()').single_value 117 [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])} 118 rescue 119 0 120 end 121 end
SQLite
supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.
# File lib/sequel/adapters/shared/sqlite.rb 124 def supports_create_table_if_not_exists? 125 sqlite_version >= 30300 126 end
SQLite
3.6.19+ supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/sqlite.rb 129 def supports_deferrable_foreign_key_constraints? 130 sqlite_version >= 30619 131 end
SQLite
3.8.0+ supports partial indexes.
# File lib/sequel/adapters/shared/sqlite.rb 134 def supports_partial_indexes? 135 sqlite_version >= 30800 136 end
SQLite
3.6.8+ supports savepoints.
# File lib/sequel/adapters/shared/sqlite.rb 139 def supports_savepoints? 140 sqlite_version >= 30608 141 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 158 def tables(opts=OPTS) 159 tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts) 160 end
Set the default transaction mode.
# File lib/sequel/adapters/shared/sqlite.rb 44 def transaction_mode=(value) 45 if TRANSACTION_MODE.include?(value) 46 @transaction_mode = value 47 else 48 raise Error, "Invalid value for transaction_mode. Please specify one of :deferred, :immediate, :exclusive, nil" 49 end 50 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 150 def use_timestamp_timezones? 151 defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false) 152 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 166 def values(v) 167 @default_dataset.clone(:values=>v) 168 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 174 def views(opts=OPTS) 175 tables_and_views({:type => 'view'}, opts) 176 end
Private Instance Methods
Dataset
used for parsing foreign key lists
# File lib/sequel/adapters/shared/sqlite.rb 181 def _foreign_key_list_ds(table) 182 metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table)) 183 end
Dataset
used for parsing schema
# File lib/sequel/adapters/shared/sqlite.rb 186 def _parse_pragma_ds(table_name, opts) 187 metadata_dataset.with_sql("PRAGMA table_#{'x' if sqlite_version > 33100}info(?)", input_identifier_meth(opts[:dataset]).call(table_name)) 188 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 226 def alter_table_sql(table, op) 227 case op[:op] 228 when :add_index, :drop_index 229 super 230 when :add_column 231 if op[:unique] || op[:primary_key] 232 duplicate_table(table){|columns| columns.push(op)} 233 else 234 super 235 end 236 when :drop_column 237 ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}} 238 duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}} 239 when :rename_column 240 if sqlite_version >= 32500 241 super 242 else 243 ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}} 244 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}} 245 end 246 when :set_column_default 247 duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}} 248 when :set_column_null 249 duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}} 250 when :set_column_type 251 duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}} 252 when :drop_constraint 253 case op[:type] 254 when :primary_key 255 duplicate_table(table) do |columns| 256 columns.each do |s| 257 s[:unique] = false if s[:primary_key] 258 s[:primary_key] = s[:auto_increment] = nil 259 end 260 end 261 when :foreign_key 262 if op[:columns] 263 duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) 264 else 265 duplicate_table(table, :no_foreign_keys=>true) 266 end 267 else 268 duplicate_table(table) 269 end 270 when :add_constraint 271 duplicate_table(table, :constraints=>[op]) 272 when :add_constraints 273 duplicate_table(table, :constraints=>op[:ops]) 274 else 275 raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" 276 end 277 end
Run all alter_table commands in a transaction. This is technically only needed for drop column.
# File lib/sequel/adapters/shared/sqlite.rb 192 def apply_alter_table(table, ops) 193 fks = fetch("PRAGMA foreign_keys") 194 if fks 195 run "PRAGMA foreign_keys = 0" 196 run "PRAGMA legacy_alter_table = 1" if sqlite_version >= 32600 197 end 198 transaction do 199 if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint || op[:op] == :set_column_null} 200 null_ops, ops = ops.partition{|op| op[:op] == :set_column_null} 201 202 # Apply NULL/NOT NULL ops first, since those should be purely idependent of the constraints. 203 null_ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 204 205 # If you are just doing constraints, apply all of them at the same time, 206 # as otherwise all but the last one get lost. 207 alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)} 208 else 209 # Run each operation separately, as later operations may depend on the 210 # results of earlier operations. 211 ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 212 end 213 end 214 remove_cached_schema(table) 215 ensure 216 if fks 217 run "PRAGMA foreign_keys = 1" 218 run "PRAGMA legacy_alter_table = 0" if sqlite_version >= 32600 219 end 220 end
A name to use for the backup table
# File lib/sequel/adapters/shared/sqlite.rb 287 def backup_table_name(table, opts=OPTS) 288 table = table.gsub('`', '') 289 (opts[:times]||1000).times do |i| 290 table_name = "#{table}_backup#{i}" 291 return table_name unless table_exists?(table_name) 292 end 293 end
# File lib/sequel/adapters/shared/sqlite.rb 279 def begin_new_transaction(conn, opts) 280 mode = opts[:mode] || @transaction_mode 281 sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" 282 log_connection_execute(conn, sql) 283 set_transaction_isolation(conn, opts) 284 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 297 def can_add_primary_key_constraint_on_nullable_columns? 298 false 299 end
Surround default with parens to appease SQLite
. Add support for GENERATED ALWAYS AS.
# File lib/sequel/adapters/shared/sqlite.rb 302 def column_definition_default_sql(sql, column) 303 sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) 304 if (generated = column[:generated_always_as]) 305 if (generated_type = column[:generated_type]) && (generated_type == :stored || generated_type == :virtual) 306 generated_type = generated_type.to_s.upcase 307 end 308 sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{generated_type}" 309 end 310 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 314 def connection_pragmas 315 ps = [] 316 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 317 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 318 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 319 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 320 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 321 if v = opts[prag] 322 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 323 ps << "PRAGMA #{prag} = #{v}" 324 end 325 end 326 ps 327 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 330 def create_view_prefix_sql(name, options) 331 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 332 end
# File lib/sequel/adapters/shared/sqlite.rb 342 def database_error_regexps 343 DATABASE_ERROR_REGEXPS 344 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 347 def database_specific_error_class(exception, opts) 348 case sqlite_error_code(exception) 349 when 1299 350 NotNullConstraintViolation 351 when 1555, 2067, 2579 352 UniqueConstraintViolation 353 when 787 354 ForeignKeyConstraintViolation 355 when 275 356 CheckConstraintViolation 357 when 19 358 ConstraintViolation 359 when 517 360 SerializationFailure 361 else 362 super 363 end 364 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 367 def defined_columns_for(table) 368 cols = parse_pragma(table, OPTS) 369 cols.each do |c| 370 c[:default] = LiteralString.new(c[:default]) if c[:default] 371 c[:type] = c[:db_type] 372 end 373 cols 374 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 379 def duplicate_table(table, opts=OPTS) 380 remove_cached_schema(table) 381 def_columns = defined_columns_for(table) 382 old_columns = def_columns.map{|c| c[:name]} 383 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 384 385 yield def_columns if block_given? 386 387 constraints = (opts[:constraints] || []).dup 388 pks = [] 389 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 390 if pks.length > 1 391 constraints << {:type=>:primary_key, :columns=>pks} 392 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 393 end 394 395 # If dropping a foreign key constraint, drop all foreign key constraints, 396 # as there is no way to determine which one to drop. 397 unless opts[:no_foreign_keys] 398 fks = foreign_key_list(table) 399 400 # If dropping a column, if there is a foreign key with that 401 # column, don't include it when building a copy of the table. 402 if ocp = opts[:old_columns_proc] 403 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 404 end 405 406 # Skip any foreign key columns where a constraint for those 407 # foreign keys is being dropped. 408 if sfkc = opts[:skip_foreign_key_columns] 409 fks.delete_if{|c| c[:columns] == sfkc} 410 end 411 412 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 413 end 414 415 # Determine unique constraints and make sure the new columns have them 416 unique_columns = [] 417 skip_indexes = [] 418 indexes(table, :only_autocreated=>true).each do |name, h| 419 skip_indexes << name 420 if h[:columns].length == 1 && h[:unique] 421 unique_columns.concat(h[:columns]) 422 end 423 end 424 unique_columns -= pks 425 unless unique_columns.empty? 426 unique_columns.map!{|c| quote_identifier(c)} 427 def_columns.each do |c| 428 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) && c[:unique] != false 429 end 430 end 431 432 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 433 new_columns = old_columns.dup 434 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 435 436 qt = quote_schema_table(table) 437 bt = quote_identifier(backup_table_name(qt)) 438 a = [ 439 "ALTER TABLE #{qt} RENAME TO #{bt}", 440 "CREATE TABLE #{qt}(#{def_columns_str})", 441 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 442 "DROP TABLE #{bt}" 443 ] 444 indexes(table).each do |name, h| 445 next if skip_indexes.include?(name) 446 if (h[:columns].map(&:to_s) - new_columns).empty? 447 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 448 end 449 end 450 a 451 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 455 def on_delete_sql_to_sym(str) 456 case str 457 when 'RESTRICT' 458 :restrict 459 when 'CASCADE' 460 :cascade 461 when 'SET NULL' 462 :set_null 463 when 'SET DEFAULT' 464 :set_default 465 when 'NO ACTION' 466 :no_action 467 end 468 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 471 def parse_pragma(table_name, opts) 472 pks = 0 473 sch = _parse_pragma_ds(table_name, opts).map do |row| 474 if sqlite_version > 33100 475 # table_xinfo PRAGMA used, remove hidden columns 476 # that are not generated columns 477 if row[:generated] = (row.delete(:hidden) != 0) 478 next unless row[:type].end_with?(' GENERATED ALWAYS') 479 row[:type] = row[:type].sub(' GENERATED ALWAYS', '') 480 end 481 end 482 483 row.delete(:cid) 484 row[:allow_null] = row.delete(:notnull).to_i == 0 485 row[:default] = row.delete(:dflt_value) 486 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 487 row[:db_type] = row.delete(:type) 488 if row[:primary_key] = row.delete(:pk).to_i > 0 489 pks += 1 490 # Guess that an integer primary key uses auto increment, 491 # since that is Sequel's default and SQLite does not provide 492 # a way to introspect whether it is actually autoincrementing. 493 row[:auto_increment] = row[:db_type].downcase == 'integer' 494 end 495 row[:type] = schema_column_type(row[:db_type]) 496 row 497 end 498 499 sch.compact! 500 501 if pks > 1 502 # SQLite does not allow use of auto increment for tables 503 # with composite primary keys, so remove auto_increment 504 # if composite primary keys are detected. 505 sch.each{|r| r.delete(:auto_increment)} 506 end 507 508 sch 509 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 513 def schema_parse_table(table_name, opts) 514 m = output_identifier_meth(opts[:dataset]) 515 parse_pragma(table_name, opts).map do |row| 516 [m.call(row.delete(:name)), row] 517 end 518 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 521 def sqlite_error_code(exception) 522 nil 523 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 526 def tables_and_views(filter, opts) 527 m = output_identifier_meth 528 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 529 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 534 def type_literal_generic_bignum_symbol(column) 535 column[:auto_increment] ? :integer : super 536 end