Module: ArJdbc::SQLite3

Includes:
Util::TableCopier
Included in:
ActiveRecord::ConnectionAdapters::SQLite3Adapter
Defined in:
lib/arjdbc/sqlite3/adapter.rb,
lib/arjdbc/sqlite3/explain_support.rb

Defined Under Namespace

Modules: Column, ExplainSupport

Constant Summary collapse

ADAPTER_NAME = 
'SQLite'.freeze
NATIVE_DATABASE_TYPES = 
{
  :primary_key => nil,
  :string => { :name => "varchar", :limit => 255 },
  :text => { :name => "text" },
  :integer => { :name => "integer" },
  :float => { :name => "float" },
  # :real => { :name=>"real" },
  :decimal => { :name => "decimal" },
  :datetime => { :name => "datetime" },
  :timestamp => { :name => "datetime" },
  :time => { :name => "time" },
  :date => { :name => "date" },
  :binary => { :name => "blob" },
  :boolean => { :name => "boolean" }
}

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Util::TableCopier

#alter_table, #copy_table, #copy_table_contents, #copy_table_indexes, #move_table

Class Method Details

.arel_visitor_type(config = nil) ⇒ Object

See Also:



100
101
102
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 100

def self.arel_visitor_type(config = nil)
  ::Arel::Visitors::SQLite
end

.column_selector ⇒ Object

See Also:

  • ActiveRecord::ConnectionAdapters::JdbcColumn#column_types


18
19
20
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 18

def self.column_selector
  [ /sqlite/i, lambda { |config, column| column.extend(Column) } ]
end

.jdbc_connection_class ⇒ Object

See Also:



11
12
13
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 11

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::SQLite3JdbcConnection
end

Instance Method Details

#adapter_name ⇒ Object 



112
113
114
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 112

def adapter_name
  ADAPTER_NAME
end

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



426
427
428
429
430
431
432
433
434
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 426

def add_column(table_name, column_name, type, options = {})
  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 ⇒ Fixnum

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

Returns:

  • (Fixnum) 


278
279
280
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 278

def allowed_index_name_length
  index_name_length - 2
end

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



480
481
482
483
484
485
486
487
488
489
490
491
492
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 480

def change_column(table_name, column_name, type, options = {})
  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:



465
466
467
468
469
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 465

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 



471
472
473
474
475
476
477
478
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 471

def change_column_null(table_name, column_name, null, default = nil)
  unless null || default.nil?
    execute("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

#columns(table_name, name = nil) ⇒ Object 



357
358
359
360
361
362
363
364
365
366
367
368
369
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 357

def columns(table_name, name = nil)
  column = jdbc_column_class
  pass_cast_type = respond_to?(:lookup_cast_type)
  table_structure(table_name).map do |field|
    sql_type = field['type']
    if pass_cast_type
      cast_type = lookup_cast_type(sql_type)
      column.new(field['name'], field['dflt_value'], cast_type, sql_type, field['notnull'] == 0)
    else
      column.new(field['name'], field['dflt_value'], sql_type, field['notnull'] == 0)
    end
  end
end

#create_savepoint(name = current_savepoint_name(true)) ⇒ Object 



283
284
285
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 283

def create_savepoint(name = current_savepoint_name(true))
  log("SAVEPOINT #{name}", 'Savepoint') { super }
end

#default_primary_key_type ⇒ Object 



139
140
141
142
143
144
145
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 139

def default_primary_key_type
  if supports_autoincrement?
    'integer PRIMARY KEY AUTOINCREMENT NOT NULL'
  else
    'integer PRIMARY KEY NOT NULL'
  end
end

#empty_insert_statement_value ⇒ Object 



507
508
509
510
511
512
513
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 507

def empty_insert_statement_value
  # inherited (default) on 3.2 : "VALUES(DEFAULT)"
  # inherited (default) on 4.0 : "DEFAULT VALUES"
  # re-defined in native adapter on 3.2 "VALUES(NULL)"
  # on 4.0 no longer re-defined (thus inherits default)
  "DEFAULT VALUES"
end

#encoding ⇒ Object 



515
516
517
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 515

def encoding
  select_value 'PRAGMA encoding'
end

#exec_insert(sql, name, binds, pk = nil, sequence_name = nil) ⇒ Object

Note:

Does not support prepared statements for INSERT statements.



341
342
343
344
345
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 341

def exec_insert(sql, name, binds, pk = nil, sequence_name = nil)
  # NOTE: since SQLite JDBC does not support executeUpdate but only
  # statement.execute we can not support prepared statements here :
  execute(sql, name, binds)
end

#indexes(table_name, name = nil) ⇒ Object

NOTE: do not override indexes without testing support for 3.7.2 & 3.8.7 !



379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 379

def indexes(table_name, name = nil)
  # on JDBC 3.7 we'll simply do super since it can not handle "PRAGMA index_info"
  return @connection.indexes(table_name, name) if sqlite_version < '3.8' # super

  name ||= 'SCHEMA'
  exec_query_raw("PRAGMA index_list(#{quote_table_name(table_name)})", name).map do |row|
    index_name = row['name']
    sql = "SELECT sql FROM sqlite_master"
    sql << " WHERE name=#{quote(index_name)} AND type='index'"
    sql << " UNION ALL "
    sql << "SELECT sql FROM sqlite_temp_master"
    sql << " WHERE name=#{quote(index_name)} AND type='index'"
    where = nil
    exec_query_raw(sql, name) do |index_sql|
      match = /\sWHERE\s+(.+)$/i.match(index_sql)
      where = match[1] if match
    end
    begin
      columns = exec_query_raw("PRAGMA index_info('#{index_name}')", name).map { |col| col['name'] }
    rescue => e
      # NOTE: JDBC <= 3.8.7 bug work-around :
      if e.message && e.message.index('[SQLITE_ERROR] SQL error or missing database')
        columns = []
      end
      raise e
    end
    new_index_definition(table_name, index_name, row['unique'] != 0, columns, nil, nil, where)
  end
end

#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) ⇒ Object

Note:

We have an extra binds argument at the end due AR-2.3 support.



334
335
336
337
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 334

def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = [])
  result = execute(sql, name, binds)
  id_value || last_inserted_id(result)
end

#jdbc_column_class ⇒ Object 



15
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 15

def jdbc_column_class; ::ActiveRecord::ConnectionAdapters::SQLite3Column end

#last_insert_id ⇒ Object 



519
520
521
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 519

def last_insert_id
  @connection.last_insert_rowid
end

#last_inserted_id(result) ⇒ Object (protected) 



525
526
527
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 525

def last_inserted_id(result)
  super || last_insert_id # NOTE: #last_insert_id call should not be needed
end

#native_database_types ⇒ Object 



133
134
135
136
137
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 133

def native_database_types
  types = NATIVE_DATABASE_TYPES.dup
  types[:primary_key] = default_primary_key_type
  types
end

#primary_key(table_name) ⇒ Object 



372
373
374
375
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 372

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

#quote(value, column = nil) ⇒ Object 



218
219
220
221
222
223
224
225
226
227
228
229
230
231
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 218

def quote(value, column = nil)
  return value if sql_literal?(value)

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

#quote_column_name(name) ⇒ Object 



238
239
240
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 238

def quote_column_name(name)
  %Q("#{name.to_s.gsub('"', '""')}") # "' kludge for emacs font-lock
end

#quote_table_name_for_assignment(table, attr) ⇒ Object 



233
234
235
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 233

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.



245
246
247
248
249
250
251
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 245

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

#release_savepoint(name = current_savepoint_name) ⇒ Object 



293
294
295
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 293

def release_savepoint(name = current_savepoint_name)
  log("RELEASE SAVEPOINT #{name}", 'Savepoint') { super }
end

#remove_index!(table_name, index_name) ⇒ Object 



410
411
412
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 410

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

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



494
495
496
497
498
499
500
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 494

def rename_column(table_name, column_name, new_column_name)
  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) if respond_to?(:rename_column_indexes) # AR-4.0 SchemaStatements
end

#rename_table(table_name, new_name) ⇒ Object 



415
416
417
418
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 415

def rename_table(table_name, new_name)
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
  rename_table_indexes(table_name, new_name) if respond_to?(:rename_table_indexes) # AR-4.0 SchemaStatements
end

#rollback_to_savepoint(name = current_savepoint_name) ⇒ Object 



288
289
290
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 288

def rollback_to_savepoint(name = current_savepoint_name)
  log("ROLLBACK TO SAVEPOINT #{name}", 'Savepoint') { super }
end

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



312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 312

def select(sql, name = nil, binds = [])
  result = super # AR::Result (4.0) or Array (<= 3.2)
  if result.respond_to?(:columns) # 4.0
    result.columns.map! do |key| # [ [ 'id', ... ]
      key.is_a?(String) ? key.sub(/^"?\w+"?\./, '') : key
    end
  else
    result.map! do |row| # [ { 'id' => ... }, {...} ]
      record = {}
      row.each_key do |key|
        if key.is_a?(String)
          record[key.sub(/^"?\w+"?\./, '')] = row[key]
        end
      end
      record
    end
  end
  result
end

#supports_add_column? ⇒ Boolean

Returns:

  • (Boolean) 


158
159
160
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 158

def supports_add_column?
  true
end

#supports_autoincrement? ⇒ Boolean

Returns:

  • (Boolean) 


168
169
170
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 168

def supports_autoincrement?
  true
end

#supports_count_distinct? ⇒ Boolean

Returns:

  • (Boolean) 


163
164
165
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 163

def supports_count_distinct?
  true
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


148
149
150
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 148

def supports_ddl_transactions?
  true
end

#supports_index_sort_order? ⇒ Boolean

Returns:

  • (Boolean) 


173
174
175
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 173

def supports_index_sort_order?
  true
end

#supports_migrations? ⇒ Boolean

Returns:

  • (Boolean) 


178
179
180
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 178

def supports_migrations?
  true
end

#supports_primary_key? ⇒ Boolean

Returns:

  • (Boolean) 


183
184
185
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 183

def supports_primary_key?
  true
end

#supports_savepoints? ⇒ Boolean

Returns:

  • (Boolean) 


153
154
155
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 153

def supports_savepoints?
  sqlite_version >= '3.6.8'
end

#supports_views? ⇒ Boolean

Returns:

  • (Boolean) 


208
209
210
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 208

def supports_views?
  true
end

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


266
267
268
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 266

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

#table_structure(table_name) ⇒ Object 



347
348
349
350
351
352
353
354
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 347

def table_structure(table_name)
  sql = "PRAGMA table_info(#{quote_table_name(table_name)})"
  log(sql, 'SCHEMA') { @connection.execute_query_raw(sql) }
rescue ActiveRecord::JDBCError => error
  e = ActiveRecord::StatementInvalid.new("Could not find table '#{table_name}'")
  e.set_backtrace error.backtrace
  raise e
end

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



254
255
256
257
258
259
260
261
262
263
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 254

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

  select_rows(sql, name).map { |row| row[0] }
end

#translate_exception(exception, message) ⇒ Object (protected) 



529
530
531
532
533
534
535
536
537
538
539
540
541
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 529

def translate_exception(exception, message)
  if msg = exception.message
    # SQLite 3.8.2 returns a newly formatted error message:
    #   UNIQUE constraint failed: *table_name*.*column_name*
    # Older versions of SQLite return:
    #   column *column_name* is not unique
    if msg.index('UNIQUE constraint failed: ') ||
       msg =~ /column(s)? .* (is|are) not unique/
      return ::ActiveRecord::RecordNotUnique.new(message, exception)
    end
  end
  super
end

#truncate_fake(table_name, name = nil) ⇒ Object 



270
271
272
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 270

def truncate_fake(table_name, name = nil)
  execute "DELETE FROM #{quote_table_name(table_name)}; VACUUM", name
end

#valid_alter_table_options(type, options) ⇒ Object

SQLite has an additional restriction on the ALTER TABLE statement.

See Also:



422
423
424
 
# File 'lib/arjdbc/sqlite3/adapter.rb', line 422

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