Module: Sequel::SQLite::DatabaseMethods

Included in:
DataObjects::SQLite::DatabaseMethods, JDBC::SQLite::DatabaseMethods, Database
Defined in:
lib/sequel/adapters/shared/sqlite.rb

Constant Summary collapse

AUTO_VACUUM = 
[:none, :full, :incremental].freeze
SYNCHRONOUS = 
[:off, :normal, :full].freeze
TABLES_FILTER = 
"type = 'table' AND NOT name = 'sqlite_sequence'"
TEMP_STORE = 
[:default, :file, :memory].freeze
TYPES = 
Sequel::Database::TYPES.merge(Bignum=>'integer')

Instance Method Summary collapse

Instance Method Details

#alter_table(name, generator = nil, &block) ⇒ Object

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



14
15
16
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 14

def alter_table(name, generator=nil, &block)
  transaction{super}
end

#alter_table_sql(table, op) ⇒ Object

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.



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 22

def alter_table_sql(table, op)
  case op[:op]
  when :add_column, :add_index, :drop_index
    super
  when :drop_column
    qt = quote_schema_table(table)
    bt = quote_identifier(backup_table_name(qt.gsub('`', '')))
    columns_str = dataset.send(:identifier_list, columns_for(table, :except => op[:name]))
    defined_columns_str = column_list_sql(defined_columns_for(table, :except => op[:name]))
    ["CREATE TEMPORARY TABLE #{bt}(#{defined_columns_str})",
     "INSERT INTO #{bt} SELECT #{columns_str} FROM #{qt}",
     "DROP TABLE #{qt}",
     "CREATE TABLE #{qt}(#{defined_columns_str})",
     "INSERT INTO #{qt} SELECT #{columns_str} FROM #{bt}",
     "DROP TABLE #{bt}"]
  when :rename_column
    qt = quote_schema_table(table)
    bt = quote_identifier(backup_table_name(qt.gsub('`', '')))
    old_columns = dataset.send(:identifier_list, columns_for(table))
    new_columns_arr = columns_for(table)

    # Replace the old column in place. This is extremely important.
    new_columns_arr[new_columns_arr.index(op[:name])] = op[:new_name]
    
    new_columns = dataset.send(:identifier_list, new_columns_arr)
    
    def_old_columns = column_list_sql(defined_columns_for(table))

    def_new_columns_arr = defined_columns_for(table).map do |c|
      c[:name] = op[:new_name].to_s if c[:name] == op[:name].to_s
      c
    end
    
    def_new_columns = column_list_sql(def_new_columns_arr)

    [
     "CREATE TEMPORARY TABLE #{bt}(#{def_old_columns})",
     "INSERT INTO #{bt}(#{old_columns}) SELECT #{old_columns} FROM #{qt}",
     "DROP TABLE #{qt}",
     "CREATE TABLE #{qt}(#{def_new_columns})",
     "INSERT INTO #{qt}(#{new_columns}) SELECT #{old_columns} FROM #{bt}",
     "DROP TABLE #{bt}"
    ]

  else
    raise Error, "Unsupported ALTER TABLE operation"
  end
end

#auto_vacuum ⇒ Object

A symbol signifying the value of the auto_vacuum PRAGMA.



72
73
74
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 72

def auto_vacuum
  AUTO_VACUUM[pragma_get(:auto_vacuum).to_i]
end

#auto_vacuum=(value) ⇒ Object

Set the auto_vacuum PRAGMA using the given symbol (:none, :full, or :incremental).



78
79
80
81
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 78

def auto_vacuum=(value)
  value = AUTO_VACUUM.index(value) || (raise Error, "Invalid value for auto_vacuum option. Please specify one of :none, :full, :incremental.")
  pragma_set(:auto_vacuum, value)
end

#pragma_get(name) ⇒ Object

Get the value of the given PRAGMA.



84
85
86
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 84

def pragma_get(name)
  self["PRAGMA #{name}"].single_value
end

#pragma_set(name, value) ⇒ Object

Set the value of the given PRAGMA to value.



89
90
91
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 89

def pragma_set(name, value)
  execute_ddl("PRAGMA #{name} = #{value}")
end

#synchronous ⇒ Object

A symbol signifying the value of the synchronous PRAGMA.



94
95
96
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 94

def synchronous
  SYNCHRONOUS[pragma_get(:synchronous).to_i]
end

#synchronous=(value) ⇒ Object

Set the synchronous PRAGMA using the given symbol (:off, :normal, or :full).



99
100
101
102
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 99

def synchronous=(value)
  value = SYNCHRONOUS.index(value) || (raise Error, "Invalid value for synchronous option. Please specify one of :off, :normal, :full.")
  pragma_set(:synchronous, value)
end

#tables(opts = {}) ⇒ Object

Array of symbols specifying the table names in the current database.

Options:

  • :server - Set the server to use.



108
109
110
111
112
113
114
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 108

def tables(opts={})
  ds = self[:sqlite_master].server(opts[:server]).filter(TABLES_FILTER)
  ds.identifier_output_method = nil
  ds.identifier_input_method = nil
  ds2 = dataset
  ds.map{|r| ds2.send(:output_identifier, r[:name])}
end

#temp_store ⇒ Object

A symbol signifying the value of the temp_store PRAGMA.



117
118
119
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 117

def temp_store
  TEMP_STORE[pragma_get(:temp_store).to_i]
end

#temp_store=(value) ⇒ Object

Set the temp_store PRAGMA using the given symbol (:default, :file, or :memory).



122
123
124
125
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 122

def temp_store=(value)
  value = TEMP_STORE.index(value) || (raise Error, "Invalid value for temp_store option. Please specify one of :default, :file, :memory.")
  pragma_set(:temp_store, value)
end