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

integer_booleans[RW]

Whether to use integers for booleans in the database. SQLite recommends booleans be stored as integers, but historically Sequel has used 't'/'f'.

transaction_mode[R]

A symbol signifying the value of the default transaction mode

use_timestamp_timezones[W]

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

database_type() click to toggle source

SQLite uses the :sqlite database type.

# File lib/sequel/adapters/shared/sqlite.rb, line 48
def database_type
  :sqlite
end
foreign_key_list(table, opts=OPTS) click to toggle source

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, line 59
def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  h = {}
  metadata_dataset.with_sql("PRAGMA foreign_key_list(?)", input_identifier_meth.call(table)).each do |row|
    if r = h[row[:id]]
      r[:columns] << m.call(row[:from])
      r[:key] << m.call(row[:to]) if r[:key]
    else
      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])}
    end
  end
  h.values
end
freeze() click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 73
def freeze
  sqlite_version
  use_timestamp_timezones?
  super
end
indexes(table, opts=OPTS) click to toggle source

Use the index_list and index_info PRAGMAs to determine the indexes on the table.

# File lib/sequel/adapters/shared/sqlite.rb, line 80
def indexes(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  indexes = {}
  table = table.value if table.is_a?(Sequel::SQL::Identifier)
  metadata_dataset.with_sql("PRAGMA index_list(?)", im.call(table)).each do |r|
    if opts[:only_autocreated]
      # If specifically asked for only autocreated indexes, then return those an only those
      next unless r[:name] =~ /\Asqlite_autoindex_/
    elsif r.has_key?(:origin)
      # If origin is set, then only exclude primary key indexes and partial indexes
      next if r[:origin] == 'pk'
      next if r[:partial].to_i == 1
    else
      # When :origin key not present, assume any autoindex could be a primary key one and exclude it
      next if r[:name] =~ /\Asqlite_autoindex_/
    end

    indexes[m.call(r[:name])] = {:unique=>r[:unique].to_i==1}
  end
  indexes.each do |k, v|
    v[:columns] = metadata_dataset.with_sql("PRAGMA index_info(?)", im.call(k)).map(:name).map{|x| m.call(x)}
  end
  indexes
end
set_integer_booleans() click to toggle source

Set the #integer_booleans option using the passed in :integer_boolean option.

# File lib/sequel/adapters/shared/sqlite.rb, line 53
def set_integer_booleans
  @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true
end
sqlite_version() click to toggle source

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, line 108
def sqlite_version
  return @sqlite_version if defined?(@sqlite_version)
  @sqlite_version = begin
    v = fetch('SELECT sqlite_version()').single_value
    [10000, 100, 1].zip(v.split('.')).inject(0){|a, m| a + m[0] * Integer(m[1])}
  rescue
    0
  end
end
supports_create_table_if_not_exists?() click to toggle source

SQLite supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.

# File lib/sequel/adapters/shared/sqlite.rb, line 119
def supports_create_table_if_not_exists?
  sqlite_version >= 30300
end
supports_deferrable_foreign_key_constraints?() click to toggle source

SQLite 3.6.19+ supports deferrable foreign key constraints.

# File lib/sequel/adapters/shared/sqlite.rb, line 124
def supports_deferrable_foreign_key_constraints?
  sqlite_version >= 30619
end
supports_partial_indexes?() click to toggle source

SQLite 3.8.0+ supports partial indexes.

# File lib/sequel/adapters/shared/sqlite.rb, line 129
def supports_partial_indexes?
  sqlite_version >= 30800
end
supports_savepoints?() click to toggle source

SQLite 3.6.8+ supports savepoints.

# File lib/sequel/adapters/shared/sqlite.rb, line 134
def supports_savepoints?
  sqlite_version >= 30608
end
tables(opts=OPTS) click to toggle source

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, line 153
def tables(opts=OPTS)
  tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts)
end
transaction_mode=(value) click to toggle source

Set the default transaction mode.

# File lib/sequel/adapters/shared/sqlite.rb, line 39
def transaction_mode=(value)
  if TRANSACTION_MODE.include?(value)
    @transaction_mode = value
  else
    raise Error, "Invalid value for transaction_mode.  Please specify one of :deferred, :immediate, :exclusive, nil"
  end
end
use_timestamp_timezones?() click to toggle source

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, line 145
def use_timestamp_timezones?
  defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false)
end
values(v) click to toggle source

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, line 161
def values(v)
  @default_dataset.clone(:values=>v)
end
views(opts=OPTS) click to toggle source

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, line 169
def views(opts=OPTS)
  tables_and_views({:type => 'view'}, opts)
end

Private Instance Methods

alter_table_sql(table, op) click to toggle source

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.

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 200
def alter_table_sql(table, op)
  case op[:op]
  when :add_index, :drop_index
    super
  when :add_column
    if op[:unique] || op[:primary_key]
      duplicate_table(table){|columns| columns.push(op)}
    else
      super
    end
  when :drop_column
    ocp = lambda{|oc| oc.delete_if{|c| c.to_s == op[:name].to_s}}
    duplicate_table(table, :old_columns_proc=>ocp){|columns| columns.delete_if{|s| s[:name].to_s == op[:name].to_s}}
  when :rename_column
    ncp = lambda{|nc| nc.map!{|c| c.to_s == op[:name].to_s ? op[:new_name] : c}}
    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}}
  when :set_column_default
    duplicate_table(table){|columns| columns.each{|s| s[:default] = op[:default] if s[:name].to_s == op[:name].to_s}}
  when :set_column_null
    duplicate_table(table){|columns| columns.each{|s| s[:null] = op[:null] if s[:name].to_s == op[:name].to_s}}
  when :set_column_type
    duplicate_table(table){|columns| columns.each{|s| s.merge!(op) if s[:name].to_s == op[:name].to_s}}
  when :drop_constraint
    case op[:type]
    when :primary_key
      duplicate_table(table){|columns| columns.each{|s| s[:primary_key] = s[:auto_increment] = nil}}
    when :foreign_key
      if op[:columns]
        duplicate_table(table, :skip_foreign_key_columns=>op[:columns])
      else
        duplicate_table(table, :no_foreign_keys=>true)
      end
    else
      duplicate_table(table)
    end
  when :add_constraint
    duplicate_table(table, :constraints=>[op])
  when :add_constraints
    duplicate_table(table, :constraints=>op[:ops])
  else
    raise Error, "Unsupported ALTER TABLE operation: #{op[:op].inspect}"
  end
end
apply_alter_table(table, ops) click to toggle source

Run all alter_table commands in a transaction. This is technically only needed for drop column.

# File lib/sequel/adapters/shared/sqlite.rb, line 177
def apply_alter_table(table, ops)
  fks = fetch("PRAGMA foreign_keys")
  run "PRAGMA foreign_keys = 0" if fks
  transaction do 
    if ops.length > 1 && ops.all?{|op| op[:op] == :add_constraint}
      # If you are just doing constraints, apply all of them at the same time,
      # as otherwise all but the last one get lost.
      alter_table_sql_list(table, [{:op=>:add_constraints, :ops=>ops}]).flatten.each{|sql| execute_ddl(sql)}
    else
      # Run each operation separately, as later operations may depend on the
      # results of earlier operations.
      ops.each{|op| alter_table_sql_list(table, [op]).flatten.each{|sql| execute_ddl(sql)}}
    end
  end
  remove_cached_schema(table)
ensure
  run "PRAGMA foreign_keys = 1" if fks
end
backup_table_name(table, opts=OPTS) click to toggle source

A name to use for the backup table

# File lib/sequel/adapters/shared/sqlite.rb, line 252
def backup_table_name(table, opts=OPTS)
  table = table.gsub('`', '')
  (opts[:times]||1000).times do |i|
    table_name = "#{table}_backup#{i}"
    return table_name unless table_exists?(table_name)
  end
end
begin_new_transaction(conn, opts) click to toggle source
# File lib/sequel/adapters/shared/sqlite.rb, line 244
def begin_new_transaction(conn, opts)
  mode = opts[:mode] || @transaction_mode
  sql = TRANSACTION_MODE[mode] or raise Error, "transaction :mode must be one of: :deferred, :immediate, :exclusive, nil"
  log_connection_execute(conn, sql)
  set_transaction_isolation(conn, opts)
end
can_add_primary_key_constraint_on_nullable_columns?() click to toggle source

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, line 262
def can_add_primary_key_constraint_on_nullable_columns?
  false
end
column_definition_default_sql(sql, column) click to toggle source

Surround default with parens to appease SQLite

# File lib/sequel/adapters/shared/sqlite.rb, line 267
def column_definition_default_sql(sql, column)
  sql << " DEFAULT (#{literal(column[:default])})" if column.include?(:default)
end
connection_pragmas() click to toggle source

Array of PRAGMA SQL statements based on the Database options that should be applied to new connections.

# File lib/sequel/adapters/shared/sqlite.rb, line 273
def connection_pragmas
  ps = []
  v = typecast_value_boolean(opts.fetch(:foreign_keys, 1))
  ps << "PRAGMA foreign_keys = #{v ? 1 : 0}"
  v = typecast_value_boolean(opts.fetch(:case_sensitive_like, 1))
  ps << "PRAGMA case_sensitive_like = #{v ? 1 : 0}"
  [[:auto_vacuum, AUTO_VACUUM], [:synchronous, SYNCHRONOUS], [:temp_store, TEMP_STORE]].each do |prag, con|
    if v = opts[prag]
      raise(Error, "Value for PRAGMA #{prag} not supported, should be one of #{con.join(', ')}") unless v = con.index(v.to_sym)
      ps << "PRAGMA #{prag} = #{v}"
    end
  end
  ps
end
create_view_prefix_sql(name, options) click to toggle source

SQLite support creating temporary views.

# File lib/sequel/adapters/shared/sqlite.rb, line 289
def create_view_prefix_sql(name, options)
  create_view_sql_append_columns("CREATE #{'TEMPORARY 'if options[:temp]}VIEW #{quote_schema_table(name)}", options[:columns])
end
database_error_regexps() click to toggle source
# File lib/sequel/adapters/shared/sqlite.rb, line 301
def database_error_regexps
  DATABASE_ERROR_REGEXPS
end
database_specific_error_class(exception, opts) click to toggle source

Recognize SQLite error codes if the exception provides access to them.

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 306
def database_specific_error_class(exception, opts)
  case sqlite_error_code(exception)
  when 1299
    NotNullConstraintViolation
  when 2067
    UniqueConstraintViolation
  when 787
    ForeignKeyConstraintViolation
  when 275
    CheckConstraintViolation
  when 19
    ConstraintViolation
  else
    super
  end
end
defined_columns_for(table) click to toggle source

The array of column schema hashes for the current columns in the table

# File lib/sequel/adapters/shared/sqlite.rb, line 324
def defined_columns_for(table)
  cols = parse_pragma(table, {})
  cols.each do |c|
    c[:default] = LiteralString.new(c[:default]) if c[:default]
    c[:type] = c[:db_type]
  end
  cols
end
duplicate_table(table, opts=OPTS) { |def_columns| ... } click to toggle source

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, line 336
def duplicate_table(table, opts=OPTS)
  remove_cached_schema(table)
  def_columns = defined_columns_for(table)
  old_columns = def_columns.map{|c| c[:name]}
  opts[:old_columns_proc].call(old_columns) if opts[:old_columns_proc]

  yield def_columns if block_given?

  constraints = (opts[:constraints] || []).dup
  pks = []
  def_columns.each{|c| pks << c[:name] if c[:primary_key]}
  if pks.length > 1
    constraints << {:type=>:primary_key, :columns=>pks}
    def_columns.each{|c| c[:primary_key] = false if c[:primary_key]}
  end

  # If dropping a foreign key constraint, drop all foreign key constraints,
  # as there is no way to determine which one to drop.
  unless opts[:no_foreign_keys]
    fks = foreign_key_list(table)

    # If dropping a column, if there is a foreign key with that
    # column, don't include it when building a copy of the table.
    if ocp = opts[:old_columns_proc]
      fks.delete_if{|c| ocp.call(c[:columns].dup) != c[:columns]}
    end
    
    # Skip any foreign key columns where a constraint for those
    # foreign keys is being dropped.
    if sfkc = opts[:skip_foreign_key_columns]
      fks.delete_if{|c| c[:columns] == sfkc}
    end

    constraints.concat(fks.each{|h| h[:type] = :foreign_key})
  end

  # Determine unique constraints and make sure the new columns have them
  unique_columns = []
  skip_indexes = []
  indexes(table, :only_autocreated=>true).each do |name, h|
    skip_indexes << name
    if h[:columns].length == 1 && h[:unique]
      unique_columns.concat(h[:columns])
    end
  end
  unique_columns -= pks
  unless unique_columns.empty?
    unique_columns.map!{|c| quote_identifier(c)}
    def_columns.each do |c|
      c[:unique] = true if unique_columns.include?(quote_identifier(c[:name]))
    end
  end
  
  def_columns_str = (def_columns.map{|c| column_definition_sql(c)} + constraints.map{|c| constraint_definition_sql(c)}).join(', ')
  new_columns = old_columns.dup
  opts[:new_columns_proc].call(new_columns) if opts[:new_columns_proc]

  qt = quote_schema_table(table)
  bt = quote_identifier(backup_table_name(qt))
  a = [
     "ALTER TABLE #{qt} RENAME TO #{bt}",
     "CREATE TABLE #{qt}(#{def_columns_str})",
     "INSERT INTO #{qt}(#{dataset.send(:identifier_list, new_columns)}) SELECT #{dataset.send(:identifier_list, old_columns)} FROM #{bt}",
     "DROP TABLE #{bt}"
  ]
  indexes(table).each do |name, h|
    next if skip_indexes.include?(name)
    if (h[:columns].map(&:to_s) - new_columns).empty?
      a << alter_table_sql(table, h.merge(:op=>:add_index, :name=>name))
    end
  end
  a
end
on_delete_sql_to_sym(str) click to toggle source

Does the reverse of on_delete_clause, eg. converts strings like +'SET NULL'+ to symbols :set_null.

# File lib/sequel/adapters/shared/sqlite.rb, line 412
def on_delete_sql_to_sym(str)
  case str
  when 'RESTRICT'
    :restrict
  when 'CASCADE'
    :cascade
  when 'SET NULL'
    :set_null
  when 'SET DEFAULT'
    :set_default
  when 'NO ACTION'
    :no_action
  end
end
parse_pragma(table_name, opts) click to toggle source

Parse the output of the table_info pragma

# File lib/sequel/adapters/shared/sqlite.rb, line 428
def parse_pragma(table_name, opts)
  pks = 0
  sch = metadata_dataset.with_sql("PRAGMA table_info(?)", input_identifier_meth(opts[:dataset]).call(table_name)).map do |row|
    row.delete(:cid)
    row[:allow_null] = row.delete(:notnull).to_i == 0
    row[:default] = row.delete(:dflt_value)
    row[:default] = nil if blank_object?(row[:default]) || row[:default] == 'NULL'
    row[:db_type] = row.delete(:type)
    if row[:primary_key] = row.delete(:pk).to_i > 0
      pks += 1
      # Guess that an integer primary key uses auto increment,
      # since that is Sequel's default and SQLite does not provide
      # a way to introspect whether it is actually autoincrementing.
      row[:auto_increment] = row[:db_type].downcase == 'integer'
    end
    row[:type] = schema_column_type(row[:db_type])
    row
  end

  if pks > 1
    # SQLite does not allow use of auto increment for tables
    # with composite primary keys, so remove auto_increment
    # if composite primary keys are detected.
    sch.each{|r| r.delete(:auto_increment)}
  end

  sch
end
schema_parse_table(table_name, opts) click to toggle source

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, line 459
def schema_parse_table(table_name, opts)
  m = output_identifier_meth(opts[:dataset])
  parse_pragma(table_name, opts).map do |row|
    [m.call(row.delete(:name)), row]
  end
end
sqlite_error_code(exception) click to toggle source

Don't support SQLite error codes for exceptions by default.

# File lib/sequel/adapters/shared/sqlite.rb, line 467
def sqlite_error_code(exception)
  nil
end
tables_and_views(filter, opts) click to toggle source

Backbone of the tables and views support.

# File lib/sequel/adapters/shared/sqlite.rb, line 472
def tables_and_views(filter, opts)
  m = output_identifier_meth
  metadata_dataset.from(:sqlite_master).server(opts[:server]).where(filter).map{|r| m.call(r[:name])}
end
type_literal_generic_bignum_symbol(column) click to toggle source

SQLite only supports AUTOINCREMENT on integer columns, not bigint columns, so use integer instead of bigint for those columns.

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 480
def type_literal_generic_bignum_symbol(column)
  column[:auto_increment] ? :integer : super
end