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_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){|columns| columns.each{|s| s[:primary_key] = s[:auto_increment] = nil}} 256 when :foreign_key 257 if op[:columns] 258 duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) 259 else 260 duplicate_table(table, :no_foreign_keys=>true) 261 end 262 else 263 duplicate_table(table) 264 end 265 when :add_constraint 266 duplicate_table(table, :constraints=>[op]) 267 when :add_constraints 268 duplicate_table(table, :constraints=>op[:ops]) 269 else 270 raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" 271 end 272 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 282 def backup_table_name(table, opts=OPTS) 283 table = table.gsub('`', '') 284 (opts[:times]||1000).times do |i| 285 table_name = "#{table}_backup#{i}" 286 return table_name unless table_exists?(table_name) 287 end 288 end
# File lib/sequel/adapters/shared/sqlite.rb 274 def begin_new_transaction(conn, opts) 275 mode = opts[:mode] || @transaction_mode 276 sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" 277 log_connection_execute(conn, sql) 278 set_transaction_isolation(conn, opts) 279 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 292 def can_add_primary_key_constraint_on_nullable_columns? 293 false 294 end
Surround default with parens to appease SQLite
# File lib/sequel/adapters/shared/sqlite.rb 297 def column_definition_default_sql(sql, column) 298 sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) 299 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 303 def connection_pragmas 304 ps = [] 305 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 306 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 307 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 308 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 309 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 310 if v = opts[prag] 311 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 312 ps << "PRAGMA #{prag} = #{v}" 313 end 314 end 315 ps 316 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 319 def create_view_prefix_sql(name, options) 320 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 321 end
# File lib/sequel/adapters/shared/sqlite.rb 331 def database_error_regexps 332 DATABASE_ERROR_REGEXPS 333 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 336 def database_specific_error_class(exception, opts) 337 case sqlite_error_code(exception) 338 when 1299 339 NotNullConstraintViolation 340 when 1555, 2067, 2579 341 UniqueConstraintViolation 342 when 787 343 ForeignKeyConstraintViolation 344 when 275 345 CheckConstraintViolation 346 when 19 347 ConstraintViolation 348 when 517 349 SerializationFailure 350 else 351 super 352 end 353 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 356 def defined_columns_for(table) 357 cols = parse_pragma(table, OPTS) 358 cols.each do |c| 359 c[:default] = LiteralString.new(c[:default]) if c[:default] 360 c[:type] = c[:db_type] 361 end 362 cols 363 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 368 def duplicate_table(table, opts=OPTS) 369 remove_cached_schema(table) 370 def_columns = defined_columns_for(table) 371 old_columns = def_columns.map{|c| c[:name]} 372 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 373 374 yield def_columns if block_given? 375 376 constraints = (opts[:constraints] || []).dup 377 pks = [] 378 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 379 if pks.length > 1 380 constraints << {:type=>:primary_key, :columns=>pks} 381 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 382 end 383 384 # If dropping a foreign key constraint, drop all foreign key constraints, 385 # as there is no way to determine which one to drop. 386 unless opts[:no_foreign_keys] 387 fks = foreign_key_list(table) 388 389 # If dropping a column, if there is a foreign key with that 390 # column, don't include it when building a copy of the table. 391 if ocp = opts[:old_columns_proc] 392 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 393 end 394 395 # Skip any foreign key columns where a constraint for those 396 # foreign keys is being dropped. 397 if sfkc = opts[:skip_foreign_key_columns] 398 fks.delete_if{|c| c[:columns] == sfkc} 399 end 400 401 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 402 end 403 404 # Determine unique constraints and make sure the new columns have them 405 unique_columns = [] 406 skip_indexes = [] 407 indexes(table, :only_autocreated=>true).each do |name, h| 408 skip_indexes << name 409 if h[:columns].length == 1 && h[:unique] 410 unique_columns.concat(h[:columns]) 411 end 412 end 413 unique_columns -= pks 414 unless unique_columns.empty? 415 unique_columns.map!{|c| quote_identifier(c)} 416 def_columns.each do |c| 417 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) 418 end 419 end 420 421 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 422 new_columns = old_columns.dup 423 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 424 425 qt = quote_schema_table(table) 426 bt = quote_identifier(backup_table_name(qt)) 427 a = [ 428 "ALTER TABLE #{qt} RENAME TO #{bt}", 429 "CREATE TABLE #{qt}(#{def_columns_str})", 430 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 431 "DROP TABLE #{bt}" 432 ] 433 indexes(table).each do |name, h| 434 next if skip_indexes.include?(name) 435 if (h[:columns].map(&:to_s) - new_columns).empty? 436 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 437 end 438 end 439 a 440 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 444 def on_delete_sql_to_sym(str) 445 case str 446 when 'RESTRICT' 447 :restrict 448 when 'CASCADE' 449 :cascade 450 when 'SET NULL' 451 :set_null 452 when 'SET DEFAULT' 453 :set_default 454 when 'NO ACTION' 455 :no_action 456 end 457 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 460 def parse_pragma(table_name, opts) 461 pks = 0 462 sch = _parse_pragma_ds(table_name, opts).map do |row| 463 row.delete(:cid) 464 row[:allow_null] = row.delete(:notnull).to_i == 0 465 row[:default] = row.delete(:dflt_value) 466 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 467 row[:db_type] = row.delete(:type) 468 if row[:primary_key] = row.delete(:pk).to_i > 0 469 pks += 1 470 # Guess that an integer primary key uses auto increment, 471 # since that is Sequel's default and SQLite does not provide 472 # a way to introspect whether it is actually autoincrementing. 473 row[:auto_increment] = row[:db_type].downcase == 'integer' 474 end 475 row[:type] = schema_column_type(row[:db_type]) 476 row 477 end 478 479 if pks > 1 480 # SQLite does not allow use of auto increment for tables 481 # with composite primary keys, so remove auto_increment 482 # if composite primary keys are detected. 483 sch.each{|r| r.delete(:auto_increment)} 484 end 485 486 sch 487 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 491 def schema_parse_table(table_name, opts) 492 m = output_identifier_meth(opts[:dataset]) 493 parse_pragma(table_name, opts).map do |row| 494 [m.call(row.delete(:name)), row] 495 end 496 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 499 def sqlite_error_code(exception) 500 nil 501 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 504 def tables_and_views(filter, opts) 505 m = output_identifier_meth 506 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 507 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 512 def type_literal_generic_bignum_symbol(column) 513 column[:auto_increment] ? :integer : super 514 end