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 206 def alter_table_sql(table, op) 207 case op[:op] 208 when :add_index, :drop_index 209 super 210 when :add_column 211 if op[:unique] || op[:primary_key] 212 duplicate_table(table){|columns| columns.push(op)} 213 else 214 super 215 end 216 when :drop_column 217 ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}} 218 duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}} 219 when :rename_column 220 ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}} 221 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}} 222 when :set_column_default 223 duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}} 224 when :set_column_null 225 duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}} 226 when :set_column_type 227 duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}} 228 when :drop_constraint 229 case op[:type] 230 when :primary_key 231 duplicate_table(table){|columns| columns.each{|s| s[:primary_key] = s[:auto_increment] = nil}} 232 when :foreign_key 233 if op[:columns] 234 duplicate_table(table, :skip_foreign_key_columns=>op[:columns]) 235 else 236 duplicate_table(table, :no_foreign_keys=>true) 237 end 238 else 239 duplicate_table(table) 240 end 241 when :add_constraint 242 duplicate_table(table, :constraints=>[op]) 243 when :add_constraints 244 duplicate_table(table, :constraints=>op[:ops]) 245 else 246 raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}" 247 end 248 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 run "PRAGMA foreign_keys = 0" if fks 181 transaction do 182 if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint || op[:op] == :set_column_null} 183 null_ops, ops = ops.partition{|op| op[:op] == :set_column_null} 184 185 # Apply NULL/NOT NULL ops first, since those should be purely idependent of the constraints. 186 null_ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 187 188 # If you are just doing constraints, apply all of them at the same time, 189 # as otherwise all but the last one get lost. 190 alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)} 191 else 192 # Run each operation separately, as later operations may depend on the 193 # results of earlier operations. 194 ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}} 195 end 196 end 197 remove_cached_schema(table) 198 ensure 199 run "PRAGMA foreign_keys = 1" if fks 200 end
A name to use for the backup table
# File lib/sequel/adapters/shared/sqlite.rb 258 def backup_table_name(table, opts=OPTS) 259 table = table.gsub('`', '') 260 (opts[:times]||1000).times do |i| 261 table_name = "#{table}_backup#{i}" 262 return table_name unless table_exists?(table_name) 263 end 264 end
# File lib/sequel/adapters/shared/sqlite.rb 250 def begin_new_transaction(conn, opts) 251 mode = opts[:mode] || @transaction_mode 252 sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil" 253 log_connection_execute(conn, sql) 254 set_transaction_isolation(conn, opts) 255 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 268 def can_add_primary_key_constraint_on_nullable_columns? 269 false 270 end
Surround default with parens to appease SQLite
# File lib/sequel/adapters/shared/sqlite.rb 273 def column_definition_default_sql(sql, column) 274 sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default) 275 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 279 def connection_pragmas 280 ps = [] 281 v = typecast_value_boolean(opts.fetch(:foreign_keys, 1)) 282 ps << "PRAGMA foreign_keys = #{v ? 1 : 0}" 283 v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1)) 284 ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}" 285 [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con| 286 if v = opts[prag] 287 raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym) 288 ps << "PRAGMA #{prag} = #{v}" 289 end 290 end 291 ps 292 end
SQLite
support creating temporary views.
# File lib/sequel/adapters/shared/sqlite.rb 295 def create_view_prefix_sql(name, options) 296 create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns]) 297 end
# File lib/sequel/adapters/shared/sqlite.rb 307 def database_error_regexps 308 DATABASE_ERROR_REGEXPS 309 end
Recognize SQLite
error codes if the exception provides access to them.
# File lib/sequel/adapters/shared/sqlite.rb 312 def database_specific_error_class(exception, opts) 313 case sqlite_error_code(exception) 314 when 1299 315 NotNullConstraintViolation 316 when 2067 317 UniqueConstraintViolation 318 when 787 319 ForeignKeyConstraintViolation 320 when 275 321 CheckConstraintViolation 322 when 19 323 ConstraintViolation 324 else 325 super 326 end 327 end
The array of column schema hashes for the current columns in the table
# File lib/sequel/adapters/shared/sqlite.rb 330 def defined_columns_for(table) 331 cols = parse_pragma(table, OPTS) 332 cols.each do |c| 333 c[:default] = LiteralString.new(c[:default]) if c[:default] 334 c[:type] = c[:db_type] 335 end 336 cols 337 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 342 def duplicate_table(table, opts=OPTS) 343 remove_cached_schema(table) 344 def_columns = defined_columns_for(table) 345 old_columns = def_columns.map{|c| c[:name]} 346 opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc] 347 348 yield def_columns if block_given? 349 350 constraints = (opts[:constraints] || []).dup 351 pks = [] 352 def_columns.each{|c| pks << c[:name] if c[:primary_key]} 353 if pks.length > 1 354 constraints << {:type=>:primary_key, :columns=>pks} 355 def_columns.each{|c| c[:primary_key] = false if c[:primary_key]} 356 end 357 358 # If dropping a foreign key constraint, drop all foreign key constraints, 359 # as there is no way to determine which one to drop. 360 unless opts[:no_foreign_keys] 361 fks = foreign_key_list(table) 362 363 # If dropping a column, if there is a foreign key with that 364 # column, don't include it when building a copy of the table. 365 if ocp = opts[:old_columns_proc] 366 fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]} 367 end 368 369 # Skip any foreign key columns where a constraint for those 370 # foreign keys is being dropped. 371 if sfkc = opts[:skip_foreign_key_columns] 372 fks.delete_if{|c| c[:columns] == sfkc} 373 end 374 375 constraints.concat(fks.each{|h| h[:type] = :foreign_key}) 376 end 377 378 # Determine unique constraints and make sure the new columns have them 379 unique_columns = [] 380 skip_indexes = [] 381 indexes(table, :only_autocreated=>true).each do |name, h| 382 skip_indexes << name 383 if h[:columns].length == 1 && h[:unique] 384 unique_columns.concat(h[:columns]) 385 end 386 end 387 unique_columns -= pks 388 unless unique_columns.empty? 389 unique_columns.map!{|c| quote_identifier(c)} 390 def_columns.each do |c| 391 c[:unique] = true if unique_columns.include?(quote_identifier(c[:name])) 392 end 393 end 394 395 def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ') 396 new_columns = old_columns.dup 397 opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc] 398 399 qt = quote_schema_table(table) 400 bt = quote_identifier(backup_table_name(qt)) 401 a = [ 402 "ALTER TABLE #{qt} RENAME TO #{bt}", 403 "CREATE TABLE #{qt}(#{def_columns_str})", 404 "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}", 405 "DROP TABLE #{bt}" 406 ] 407 indexes(table).each do |name, h| 408 next if skip_indexes.include?(name) 409 if (h[:columns].map(&:to_s) - new_columns).empty? 410 a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name)) 411 end 412 end 413 a 414 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 418 def on_delete_sql_to_sym(str) 419 case str 420 when 'RESTRICT' 421 :restrict 422 when 'CASCADE' 423 :cascade 424 when 'SET NULL' 425 :set_null 426 when 'SET DEFAULT' 427 :set_default 428 when 'NO ACTION' 429 :no_action 430 end 431 end
Parse the output of the table_info pragma
# File lib/sequel/adapters/shared/sqlite.rb 434 def parse_pragma(table_name, opts) 435 pks = 0 436 sch = metadata_dataset.with_sql("PRAGMA table_info(?)", input_identifier_meth(opts[:dataset]).call(table_name)).map do |row| 437 row.delete(:cid) 438 row[:allow_null] = row.delete(:notnull).to_i == 0 439 row[:default] = row.delete(:dflt_value) 440 row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL' 441 row[:db_type] = row.delete(:type) 442 if row[:primary_key] = row.delete(:pk).to_i > 0 443 pks += 1 444 # Guess that an integer primary key uses auto increment, 445 # since that is Sequel's default and SQLite does not provide 446 # a way to introspect whether it is actually autoincrementing. 447 row[:auto_increment] = row[:db_type].downcase == 'integer' 448 end 449 row[:type] = schema_column_type(row[:db_type]) 450 row 451 end 452 453 if pks > 1 454 # SQLite does not allow use of auto increment for tables 455 # with composite primary keys, so remove auto_increment 456 # if composite primary keys are detected. 457 sch.each{|r| r.delete(:auto_increment)} 458 end 459 460 sch 461 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 465 def schema_parse_table(table_name, opts) 466 m = output_identifier_meth(opts[:dataset]) 467 parse_pragma(table_name, opts).map do |row| 468 [m.call(row.delete(:name)), row] 469 end 470 end
Don't support SQLite
error codes for exceptions by default.
# File lib/sequel/adapters/shared/sqlite.rb 473 def sqlite_error_code(exception) 474 nil 475 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/sqlite.rb 478 def tables_and_views(filter, opts) 479 m = output_identifier_meth 480 metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])} 481 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 486 def type_literal_generic_bignum_symbol(column) 487 column[:auto_increment] ? :integer : super 488 end