Class: ActiveRecord::ConnectionAdapters::SQLite3Adapter

Inherits:
AbstractAdapter show all
Includes:
Savepoints
Defined in:
lib/active_record/connection_adapters/sqlite3_adapter.rb

Overview

The SQLite3 adapter works SQLite 3.6.16 or newer with the sqlite3-ruby drivers (available as gem from rubygems.org/gems/sqlite3).

Options:

  • :database - Path to the database file.

Defined Under Namespace

Classes: BindSubstitution, ExplainPrettyPrinter, StatementPool, Version

Constant Summary collapse

NATIVE_DATABASE_TYPES = 
{
  primary_key:  'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL',
  string:       { name: "varchar", limit: 255 },
  text:         { name: "text" },
  integer:      { name: "integer" },
  float:        { name: "float" },
  decimal:      { name: "decimal" },
  datetime:     { name: "datetime" },
  timestamp:    { name: "datetime" },
  time:         { name: "time" },
  date:         { name: "date" },
  binary:       { name: "blob" },
  boolean:      { name: "boolean" }
}

Constants inherited from AbstractAdapter

AbstractAdapter::SIMPLE_INT

Instance Attribute Summary

Attributes inherited from AbstractAdapter

#in_use, #last_use, #logger, #pool, #schema_cache, #visitor

Attributes included from QueryCache

#query_cache, #query_cache_enabled

Instance Method Summary collapse

Methods included from Savepoints

#create_savepoint, #release_savepoint, #rollback_to_savepoint

Methods inherited from AbstractAdapter

#active_threadsafe?, #case_insensitive_comparison, #case_sensitive_modifier, #close, #create_savepoint, #current_savepoint_name, #disable_extension, #disable_referential_integrity, #enable_extension, #expire, #extensions, #index_algorithms, #lease, #open_transactions, #prefetch_primary_key?, #raw_connection, #reconnect!, #release_savepoint, #reset!, #rollback_to_savepoint, #schema_creation, #substitute_at, #supports_bulk_alter?, #supports_count_distinct?, #supports_extensions?, #supports_transaction_isolation?, type_cast_config_to_boolean, type_cast_config_to_integer, #unprepared_statement, #unprepared_visitor, #valid_type?, #verify!

Methods included from ColumnDumper

#column_spec, #migration_keys, #prepare_column_options

Methods included from QueryCache

#cache, #clear_query_cache, dirties_query_cache, #disable_query_cache!, #enable_query_cache!, included, #select_all, #uncached

Methods included from DatabaseLimits

#column_name_length, #columns_per_multicolumn_index, #columns_per_table, #in_clause_length, #index_name_length, #indexes_per_table, #joins_per_query, #sql_query_length, #table_alias_length, #table_name_length

Methods included from Quoting

#quote_table_name, #quoted_false, #quoted_true

Methods included from DatabaseStatements

#add_transaction_record, #begin_isolated_db_transaction, #begin_transaction, #commit_transaction, #current_transaction, #default_sequence_name, #delete, #empty_insert_statement_value, #exec_insert, #insert, #insert_fixture, #join_to_delete, #join_to_update, #limited_update_conditions, #reset_sequence!, #reset_transaction, #rollback_transaction, #sanitize_limit, #select_all, #select_one, #select_value, #select_values, #to_sql, #transaction, #transaction_isolation_levels, #transaction_open?, #update, #within_new_transaction

Methods included from SchemaStatements

#add_index, #add_reference, #add_timestamps, #assume_migrated_upto_version, #change_table, #column_exists?, #columns_for_distinct, #create_join_table, #create_table, #drop_join_table, #drop_table, #dump_schema_information, #index_exists?, #index_name, #index_name_exists?, #initialize_schema_migrations_table, #remove_columns, #remove_index, #remove_reference, #remove_timestamps, #rename_index, #table_alias_for, #type_to_sql, #update_table_definition

Constructor Details

#initialize(connection, logger, config) ⇒ SQLite3Adapter

Returns a new instance of SQLite3Adapter.



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 130

def initialize(connection, logger, config)
  super(connection, logger)

  @active     = nil
  @statements = StatementPool.new(@connection,
                                  self.class.type_cast_config_to_integer(config.fetch(:statement_limit) { 1000 }))
  @config = config

  if self.class.type_cast_config_to_boolean(config.fetch(:prepared_statements) { true })
    @prepared_statements = true
    @visitor = Arel::Visitors::SQLite.new self
  else
    @visitor = unprepared_visitor
  end
end

Instance Method Details

#active? ⇒ Boolean

Returns:

  • (Boolean) 


185
186
187
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 185

def active?
  @active != false
end

#adapter_name ⇒ Object

:nodoc:



146
147
148
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 146

def adapter_name #:nodoc:
  'SQLite'
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



455
456
457
458
459
460
461
462
463
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 455

def add_column(table_name, column_name, type, options = {}) #:nodoc:
  if supports_add_column? && valid_alter_table_options( type, options )
    super(table_name, column_name, type, options)
  else
    alter_table(table_name) do |definition|
      definition.column(column_name, type, options)
    end
  end
end

#allowed_index_name_length ⇒ Object

Returns 62. SQLite supports index names up to 64 characters. The rest is used by rails internally to perform temporary rename operations



209
210
211
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 209

def allowed_index_name_length
  index_name_length - 2
end

#begin_db_transaction ⇒ Object

:nodoc:



357
358
359
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 357

def begin_db_transaction #:nodoc:
  log('begin transaction',nil) { @connection.transaction }
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



486
487
488
489
490
491
492
493
494
495
496
497
498
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 486

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  alter_table(table_name) do |definition|
    include_default = options_include_default?(options)
    definition[column_name].instance_eval do
      self.type    = type
      self.limit   = options[:limit] if options.include?(:limit)
      self.default = options[:default] if include_default
      self.null    = options[:null] if options.include?(:null)
      self.precision = options[:precision] if options.include?(:precision)
      self.scale   = options[:scale] if options.include?(:scale)
    end
  end
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



471
472
473
474
475
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 471

def change_column_default(table_name, column_name, default) #:nodoc:
  alter_table(table_name) do |definition|
    definition[column_name].default = default
  end
end

#change_column_null(table_name, column_name, null, default = nil) ⇒ Object 



477
478
479
480
481
482
483
484
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 477

def change_column_null(table_name, column_name, null, default = nil)
  unless null || default.nil?
    exec_query("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end
  alter_table(table_name) do |definition|
    definition[column_name].null = null
  end
end

#clear_cache! ⇒ Object

Clears the prepared statements cache.



198
199
200
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 198

def clear_cache!
  @statements.clear
end

#columns(table_name) ⇒ Object

Returns an array of SQLite3Column objects for the table specified by table_name.



389
390
391
392
393
394
395
396
397
398
399
400
401
402
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 389

def columns(table_name) #:nodoc:
  table_structure(table_name).map do |field|
    case field["dflt_value"]
    when /^null$/i
      field["dflt_value"] = nil
    when /^'(.*)'$/m
      field["dflt_value"] = $1.gsub("''", "'")
    when /^"(.*)"$/m
      field["dflt_value"] = $1.gsub('""', '"')
    end

    SQLite3Column.new(field['name'], field['dflt_value'], field['type'], field['notnull'].to_i == 0)
  end
end

#commit_db_transaction ⇒ Object

:nodoc:



361
362
363
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 361

def commit_db_transaction #:nodoc:
  log('commit transaction',nil) { @connection.commit }
end

#delete_sql(sql, name = nil) ⇒ Object

:nodoc:



342
343
344
345
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 342

def delete_sql(sql, name = nil) #:nodoc:
  sql += " WHERE 1=1" unless sql =~ /WHERE/i
  super sql, name
end

#disconnect! ⇒ Object

Disconnects from the database if already connected. Otherwise, this method does nothing.



191
192
193
194
195
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 191

def disconnect!
  super
  @active = false
  @connection.close rescue nil
end

#encoding ⇒ Object

Returns the current database encoding format as a string, eg: ‘UTF-8’



218
219
220
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 218

def encoding
  @connection.encoding.to_s
end

#exec_delete(sql, name = 'SQL', binds = []) ⇒ Object Also known as: exec_update 



323
324
325
326
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 323

def exec_delete(sql, name = 'SQL', binds = [])
  exec_query(sql, name, binds)
  @connection.changes
end

#exec_query(sql, name = nil, binds = []) ⇒ Object 



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 293

def exec_query(sql, name = nil, binds = [])
  type_casted_binds = binds.map { |col, val|
    [col, type_cast(val, col)]
  }

  log(sql, name, type_casted_binds) do
    # Don't cache statements if they are not prepared
    if without_prepared_statement?(binds)
      stmt    = @connection.prepare(sql)
      begin
        cols    = stmt.columns
        records = stmt.to_a
      ensure
        stmt.close
      end
      stmt = records
    else
      cache = @statements[sql] ||= {
        :stmt => @connection.prepare(sql)
      }
      stmt = cache[:stmt]
      cols = cache[:cols] ||= stmt.columns
      stmt.reset!
      stmt.bind_params type_casted_binds.map { |_, val| val }
    end

    ActiveRecord::Result.new(cols, stmt.to_a)
  end
end

#execute(sql, name = nil) ⇒ Object

:nodoc:



333
334
335
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 333

def execute(sql, name = nil) #:nodoc:
  log(sql, name) { @connection.execute(sql) }
end

#explain(arel, binds = []) ⇒ Object

DATABASE STATEMENTS ======================================



274
275
276
277
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 274

def explain(arel, binds = [])
  sql = "EXPLAIN QUERY PLAN #{to_sql(arel, binds)}"
  ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds))
end

#indexes(table_name, name = nil) ⇒ Object

Returns an array of indexes for the given table.



405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 405

def indexes(table_name, name = nil) #:nodoc:
  exec_query("PRAGMA index_list(#{quote_table_name(table_name)})", 'SCHEMA').map do |row|
    sql = <<-SQL
      SELECT sql
      FROM sqlite_master
      WHERE name=#{quote(row['name'])} AND type='index'
      UNION ALL
      SELECT sql
      FROM sqlite_temp_master
      WHERE name=#{quote(row['name'])} AND type='index'
    SQL
    index_sql = exec_query(sql).first['sql']
    match = /\sWHERE\s+(.+)$/i.match(index_sql)
    where = match[1] if match
    IndexDefinition.new(
      table_name,
      row['name'],
      row['unique'] != 0,
      exec_query("PRAGMA index_info('#{row['name']}')", "SCHEMA").map { |col|
        col['name']
      }, nil, nil, where)
  end
end

#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object Also known as: create

:nodoc:



347
348
349
350
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 347

def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  super
  id_value || @connection.last_insert_row_id
end

#last_inserted_id(result) ⇒ Object 



329
330
331
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 329

def last_inserted_id(result)
  @connection.last_insert_row_id
end

#native_database_types ⇒ Object

:nodoc:



213
214
215
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 213

def native_database_types #:nodoc:
  NATIVE_DATABASE_TYPES
end

#primary_key(table_name) ⇒ Object

:nodoc:



429
430
431
432
433
434
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 429

def primary_key(table_name) #:nodoc:
  column = table_structure(table_name).find { |field|
    field['pk'] == 1
  }
  column && column['name']
end

#quote(value, column = nil) ⇒ Object

QUOTING ==================================================



228
229
230
231
232
233
234
235
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 228

def quote(value, column = nil)
  if value.kind_of?(String) && column && column.type == :binary
    s = value.unpack("H*")[0]
    "x'#{s}'"
  else
    super
  end
end

#quote_column_name(name) ⇒ Object

:nodoc:



245
246
247
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 245

def quote_column_name(name) #:nodoc:
  %Q("#{name.to_s.gsub('"', '""')}")
end

#quote_string(s) ⇒ Object

:nodoc:



237
238
239
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 237

def quote_string(s) #:nodoc:
  @connection.class.quote(s)
end

#quote_table_name_for_assignment(table, attr) ⇒ Object 



241
242
243
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 241

def quote_table_name_for_assignment(table, attr)
  quote_column_name(attr)
end

#quoted_date(value) ⇒ Object

Quote date/time values for use in SQL input. Includes microseconds if the value is a Time responding to usec.



251
252
253
254
255
256
257
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 251

def quoted_date(value) #:nodoc:
  if value.respond_to?(:usec)
    "#{super}.#{sprintf("%06d", value.usec)}"
  else
    super
  end
end

#remove_column(table_name, column_name, type = nil, options = {}) ⇒ Object

:nodoc:



465
466
467
468
469
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 465

def remove_column(table_name, column_name, type = nil, options = {}) #:nodoc:
  alter_table(table_name) do |definition|
    definition.remove_column column_name
  end
end

#remove_index!(table_name, index_name) ⇒ Object

:nodoc:



436
437
438
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 436

def remove_index!(table_name, index_name) #:nodoc:
  exec_query "DROP INDEX #{quote_column_name(index_name)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



500
501
502
503
504
505
506
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 500

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  unless columns(table_name).detect{|c| c.name == column_name.to_s }
    raise ActiveRecord::ActiveRecordError, "Missing column #{table_name}.#{column_name}"
  end
  alter_table(table_name, :rename => {column_name.to_s => new_column_name.to_s})
  rename_column_indexes(table_name, column_name, new_column_name)
end

#rename_table(table_name, new_name) ⇒ Object

Renames a table.

Example:

rename_table('octopuses', 'octopi')



444
445
446
447
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 444

def rename_table(table_name, new_name)
  exec_query "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
  rename_table_indexes(table_name, new_name)
end

#requires_reloading? ⇒ Boolean

Returns:

  • (Boolean) 


177
178
179
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 177

def requires_reloading?
  true
end

#rollback_db_transaction ⇒ Object

:nodoc:



365
366
367
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 365

def rollback_db_transaction #:nodoc:
  log('rollback transaction',nil) { @connection.rollback }
end

#select_rows(sql, name = nil, binds = []) ⇒ Object 



353
354
355
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 353

def select_rows(sql, name = nil, binds = [])
  exec_query(sql, name, binds).rows
end

#supports_add_column? ⇒ Boolean

Returns:

  • (Boolean) 


181
182
183
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 181

def supports_add_column?
  true
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


150
151
152
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 150

def supports_ddl_transactions?
  true
end

#supports_explain? ⇒ Boolean

Returns:

  • (Boolean) 


222
223
224
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 222

def supports_explain?
  true
end

#supports_index_sort_order? ⇒ Boolean

Returns:

  • (Boolean) 


202
203
204
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 202

def supports_index_sort_order?
  true
end

#supports_migrations? ⇒ Boolean

Returns true, since this connection adapter supports migrations.

Returns:

  • (Boolean) 


169
170
171
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 169

def supports_migrations? #:nodoc:
  true
end

#supports_partial_index? ⇒ Boolean

Returns:

  • (Boolean) 


158
159
160
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 158

def supports_partial_index?
  sqlite_version >= '3.8.0'
end

#supports_primary_key? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


173
174
175
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 173

def supports_primary_key? #:nodoc:
  true
end

#supports_savepoints? ⇒ Boolean

Returns:

  • (Boolean) 


154
155
156
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 154

def supports_savepoints?
  true
end

#supports_statement_cache? ⇒ Boolean

Returns true, since this connection adapter supports prepared statement caching.

Returns:

  • (Boolean) 


164
165
166
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 164

def supports_statement_cache?
  true
end

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


384
385
386
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 384

def table_exists?(table_name)
  table_name && tables(nil, table_name).any?
end

#tables(name = nil, table_name = nil) ⇒ Object

SCHEMA STATEMENTS ========================================



371
372
373
374
375
376
377
378
379
380
381
382
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 371

def tables(name = nil, table_name = nil) #:nodoc:
  sql = <<-SQL
    SELECT name
    FROM sqlite_master
    WHERE type = 'table' AND NOT name = 'sqlite_sequence'
  SQL
  sql << " AND name = #{quote_table_name(table_name)}" if table_name

  exec_query(sql, 'SCHEMA').map do |row|
    row['name']
  end
end

#type_cast(value, column) ⇒ Object

:nodoc:



259
260
261
262
263
264
265
266
267
268
269
270
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 259

def type_cast(value, column) # :nodoc:
  return value.to_f if BigDecimal === value
  return super unless String === value
  return super unless column && value

  value = super
  if column.type == :string && value.encoding == Encoding::ASCII_8BIT
    logger.error "Binary data inserted for `string` type on column `#{column.name}`" if logger
    value = value.encode Encoding::UTF_8
  end
  value
end

#update_sql(sql, name = nil) ⇒ Object

:nodoc:



337
338
339
340
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 337

def update_sql(sql, name = nil) #:nodoc:
  super
  @connection.changes
end

#valid_alter_table_options(type, options) ⇒ Object

See: www.sqlite.org/lang_altertable.html SQLite has an additional restriction on the ALTER TABLE statement



451
452
453
 
# File 'lib/active_record/connection_adapters/sqlite3_adapter.rb', line 451

def valid_alter_table_options( type, options)
  type.to_sym != :primary_key
end