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 49 def database_type 50 :sqlite 51 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 60 def foreign_key_list(table, opts=OPTS) 61 m = output_identifier_meth 62 h = {} 63 metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table)).each do |row| 64 if r = h[row[:id]] 65 r[:columns] << m.call(row[:from]) 66 r[:key] << m.call(row[:to]) if r[:key] 67 else 68 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])} 69 end 70 end 71 h.values 72 end
# File lib/sequel/adapters/shared/sqlite.rb 74 def freeze 75 sqlite_version 76 use_timestamp_timezones? 77 super 78 end
Use the index_list and index_info PRAGMAs to determine the indexes on the table.
# File lib/sequel/adapters/shared/sqlite.rb 81 def indexes(table, opts=OPTS) 82 m = output_identifier_meth 83 im = input_identifier_meth 84 indexes = {} 85 table = table.value if table.is_a?(Sequel::SQL::Identifier) 86 metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r| 87 if opts[:only_autocreated] 88 # If specifically asked for only autocreated indexes, then return those an only those 89 next unless r[:name] =~ /\Asqlite_autoindex_/ 90 elsif r.has_key?(:origin) 91 # If origin is set, then only exclude primary key indexes and partial indexes 92 next if r[:origin] == 'pk' 93 next if r[:partial].to_i == 1 94 else 95 # When :origin key not present, assume any autoindex could be a primary key one and exclude it 96 next if r[:name] =~ /\Asqlite_autoindex_/ 97 end 98 99 indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1} 100 end 101 indexes.each do |k, v| 102 v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)} 103 end 104 indexes 105 end
Set the integer_booleans
option using the passed in :integer_boolean option.
# File lib/sequel/adapters/shared/sqlite.rb 54 def set_integer_booleans 55 @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true 56 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 109 def sqlite_version 110 return @sqlite_version if defined?(@sqlite_version) 111 @sqlite_version = begin 112 v = fetch('SELECT sqlite_version()').single_value 113 [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])} 114 rescue 115 0 116 end 117 end
SQLite
supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.
# File lib/sequel/adapters/shared/sqlite.rb 120 def supports_create_table_if_not_exists? 121 sqlite_version >= 30300 122 end
SQLite
3.6.19+ supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/sqlite.rb 125 def supports_deferrable_foreign_key_constraints? 126 sqlite_version >= 30619 127 end
SQLite
3.8.0+ supports partial indexes.
# File lib/sequel/adapters/shared/sqlite.rb 130 def supports_partial_indexes? 131 sqlite_version >= 30800 132 end
SQLite
3.6.8+ supports savepoints.
# File lib/sequel/adapters/shared/sqlite.rb 135 def supports_savepoints? 136 sqlite_version >= 30608 137 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 154 def tables(opts=OPTS) 155 tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts) 156 end
Set the default transaction mode.
# File lib/sequel/adapters/shared/sqlite.rb 40 def transaction_mode=(value) 41 if TRANSACTION_MODE.include?(value) 42 @transaction_mode = value 43 else 44 raise Error, "Invalid value for transaction_mode. Please specify one of :deferred, :immediate, :exclusive, nil" 45 end 46 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 146 def use_timestamp_timezones? 147 defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false) 148 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 162 def values(v) 163 @default_dataset.clone(:values=>v) 164 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 170 def views(opts=OPTS) 171 tables_and_views({:type => 'view'}, opts) 172 end
Private Instance Methods
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 212 def alter_table_sql(table, op) 213 case op[:op] 214 when :add_index, :drop_index 215 super 216 when :add_column 217 if op[:unique] || op[:primary_key] 218 duplicate_table(table){|columns| columns.push(op)} 219 else 220 super 221 end 222 when :drop_column 223 ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}} 224 duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}} 225 when :rename_column 226 if sqlite_version >= 32500 227 super 228 else 229 ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}} 230 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}} 231 end 232 when :set_column_default 233 duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}} 234 when :set_column_null 235 duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}} 236 when :set_column_type 237 duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}} 238 when :drop_constraint 239 case op[:type] 240 when :primary_key 241 duplicate_table(table){|columns| columns.each{|s| s[:primary_key] = s[:auto_increment] = nil}} 242 when :foreign_key 243 if op[:columns] 244 duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) 245 else 246 duplicate_table(table, :no_foreign_keys=>true) 247 end 248 else 249 duplicate_table(table) 250 end 251 when :add_constraint 252 duplicate_table(table, :constraints=>[op]) 253 when :add_constraints 254 duplicate_table(table, :constraints=>op[:ops]) 255 else 256 raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" 257 end 258 end
Run all alter_table commands in a transaction. This is technically only needed for drop column.
# File lib/sequel/adapters/shared/sqlite.rb 178 def apply_alter_table(table, ops) 179 fks = fetch("PRAGMA foreign_keys") 180 if fks 181 run "PRAGMA foreign_keys = 0" 182 run "PRAGMA legacy_alter_table = 1" if sqlite_version >= 32600 183 end 184 transaction do 185 if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint || op[:op] == :set_column_null} 186 null_ops, ops = ops.partition{|op| op[:op] == :set_column_null} 187 188 # Apply NULL/NOT NULL ops first, since those should be purely idependent of the constraints. 189 null_ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 190 191 # If you are just doing constraints, apply all of them at the same time, 192 # as otherwise all but the last one get lost. 193 alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)} 194 else 195 # Run each operation separately, as later operations may depend on the 196 # results of earlier operations. 197 ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 198 end 199 end 200 remove_cached_schema(table) 201 ensure 202 if fks 203 run "PRAGMA foreign_keys = 1" 204 run "PRAGMA legacy_alter_table = 0" if sqlite_version >= 32600 205 end 206 end
A name to use for the backup table
# File lib/sequel/adapters/shared/sqlite.rb 268 def backup_table_name(table, opts=OPTS) 269 table = table.gsub('`', '') 270 (opts[:times]||1000).times do |i| 271 table_name = "#{table}_backup#{i}" 272 return table_name unless table_exists?(table_name) 273 end 274 end
# File lib/sequel/adapters/shared/sqlite.rb 260 def begin_new_transaction(conn, opts) 261 mode = opts[:mode] || @transaction_mode 262 sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" 263 log_connection_execute(conn, sql) 264 set_transaction_isolation(conn, opts) 265 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 278 def can_add_primary_key_constraint_on_nullable_columns? 279 false 280 end
Surround default with parens to appease SQLite
# File lib/sequel/adapters/shared/sqlite.rb 283 def column_definition_default_sql(sql, column) 284 sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) 285 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 289 def connection_pragmas 290 ps = [] 291 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 292 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 293 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 294 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 295 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 296 if v = opts[prag] 297 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 298 ps << "PRAGMA #{prag} = #{v}" 299 end 300 end 301 ps 302 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 305 def create_view_prefix_sql(name, options) 306 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 307 end
# File lib/sequel/adapters/shared/sqlite.rb 317 def database_error_regexps 318 DATABASE_ERROR_REGEXPS 319 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 322 def database_specific_error_class(exception, opts) 323 case sqlite_error_code(exception) 324 when 1299 325 NotNullConstraintViolation 326 when 1555, 2067, 2579 327 UniqueConstraintViolation 328 when 787 329 ForeignKeyConstraintViolation 330 when 275 331 CheckConstraintViolation 332 when 19 333 ConstraintViolation 334 when 517 335 SerializationFailure 336 else 337 super 338 end 339 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 342 def defined_columns_for(table) 343 cols = parse_pragma(table, OPTS) 344 cols.each do |c| 345 c[:default] = LiteralString.new(c[:default]) if c[:default] 346 c[:type] = c[:db_type] 347 end 348 cols 349 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 354 def duplicate_table(table, opts=OPTS) 355 remove_cached_schema(table) 356 def_columns = defined_columns_for(table) 357 old_columns = def_columns.map{|c| c[:name]} 358 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 359 360 yield def_columns if block_given? 361 362 constraints = (opts[:constraints] || []).dup 363 pks = [] 364 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 365 if pks.length > 1 366 constraints << {:type=>:primary_key, :columns=>pks} 367 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 368 end 369 370 # If dropping a foreign key constraint, drop all foreign key constraints, 371 # as there is no way to determine which one to drop. 372 unless opts[:no_foreign_keys] 373 fks = foreign_key_list(table) 374 375 # If dropping a column, if there is a foreign key with that 376 # column, don't include it when building a copy of the table. 377 if ocp = opts[:old_columns_proc] 378 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 379 end 380 381 # Skip any foreign key columns where a constraint for those 382 # foreign keys is being dropped. 383 if sfkc = opts[:skip_foreign_key_columns] 384 fks.delete_if{|c| c[:columns] == sfkc} 385 end 386 387 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 388 end 389 390 # Determine unique constraints and make sure the new columns have them 391 unique_columns = [] 392 skip_indexes = [] 393 indexes(table, :only_autocreated=>true).each do |name, h| 394 skip_indexes << name 395 if h[:columns].length == 1 && h[:unique] 396 unique_columns.concat(h[:columns]) 397 end 398 end 399 unique_columns -= pks 400 unless unique_columns.empty? 401 unique_columns.map!{|c| quote_identifier(c)} 402 def_columns.each do |c| 403 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) 404 end 405 end 406 407 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 408 new_columns = old_columns.dup 409 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 410 411 qt = quote_schema_table(table) 412 bt = quote_identifier(backup_table_name(qt)) 413 a = [ 414 "ALTER TABLE #{qt} RENAME TO #{bt}", 415 "CREATE TABLE #{qt}(#{def_columns_str})", 416 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 417 "DROP TABLE #{bt}" 418 ] 419 indexes(table).each do |name, h| 420 next if skip_indexes.include?(name) 421 if (h[:columns].map(&:to_s) - new_columns).empty? 422 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 423 end 424 end 425 a 426 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 430 def on_delete_sql_to_sym(str) 431 case str 432 when 'RESTRICT' 433 :restrict 434 when 'CASCADE' 435 :cascade 436 when 'SET NULL' 437 :set_null 438 when 'SET DEFAULT' 439 :set_default 440 when 'NO ACTION' 441 :no_action 442 end 443 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 446 def parse_pragma(table_name, opts) 447 pks = 0 448 sch = metadata_dataset.with_sql("PRAGMA table_info(?)", input_identifier_meth(opts[:dataset]).call(table_name)).map do |row| 449 row.delete(:cid) 450 row[:allow_null] = row.delete(:notnull).to_i == 0 451 row[:default] = row.delete(:dflt_value) 452 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 453 row[:db_type] = row.delete(:type) 454 if row[:primary_key] = row.delete(:pk).to_i > 0 455 pks += 1 456 # Guess that an integer primary key uses auto increment, 457 # since that is Sequel's default and SQLite does not provide 458 # a way to introspect whether it is actually autoincrementing. 459 row[:auto_increment] = row[:db_type].downcase == 'integer' 460 end 461 row[:type] = schema_column_type(row[:db_type]) 462 row 463 end 464 465 if pks > 1 466 # SQLite does not allow use of auto increment for tables 467 # with composite primary keys, so remove auto_increment 468 # if composite primary keys are detected. 469 sch.each{|r| r.delete(:auto_increment)} 470 end 471 472 sch 473 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 477 def schema_parse_table(table_name, opts) 478 m = output_identifier_meth(opts[:dataset]) 479 parse_pragma(table_name, opts).map do |row| 480 [m.call(row.delete(:name)), row] 481 end 482 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 485 def sqlite_error_code(exception) 486 nil 487 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 490 def tables_and_views(filter, opts) 491 m = output_identifier_meth 492 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 493 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 498 def type_literal_generic_bignum_symbol(column) 499 column[:auto_increment] ? :integer : super 500 end