Module: Sequel::SQLite::DatabaseMethods

Includes:
UnmodifiedIdentifiers::DatabaseMethods
Included in:
Amalgalite::Database, DataObjects::SQLite::DatabaseMethods, JDBC::SQLite::DatabaseMethods, Database, Sequel::Swift::SQLite::DatabaseMethods
Defined in:
lib/sequel/adapters/shared/sqlite.rb

Overview

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.

Constant Summary collapse

PRIMARY_KEY_INDEX_RE = 
/\Asqlite_autoindex_/.freeze
TABLES_FILTER = 
Sequel.~(:name=>'sqlite_sequence'.freeze) & {:type => 'table'.freeze}
VIEWS_FILTER = 
{:type => 'view'.freeze}.freeze
AUTO_VACUUM = 
[:none, :full, :incremental].freeze
SYNCHRONOUS = 
[:off, :normal, :full].freeze
TEMP_STORE = 
[:default, :file, :memory].freeze
TRANSACTION_MODE = 
{
  :deferred => "BEGIN DEFERRED TRANSACTION".freeze,
  :immediate => "BEGIN IMMEDIATE TRANSACTION".freeze,
  :exclusive => "BEGIN EXCLUSIVE TRANSACTION".freeze,
  nil => "BEGIN".freeze
}.freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#integer_booleans ⇒ Object

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



40
41
42
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 40

def integer_booleans
  @integer_booleans
end

#transaction_mode ⇒ Object

A symbol signifying the value of the default transaction mode



43
44
45
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 43

def transaction_mode
  @transaction_mode
end

#use_timestamp_timezones=(value) ⇒ Object (writeonly)

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.



187
188
189
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 187

def use_timestamp_timezones=(value)
  @use_timestamp_timezones = value
end

Instance Method Details

#auto_vacuum ⇒ Object

SEQUEL5: Remove



60
61
62
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 60

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

#auto_vacuum=(value) ⇒ Object 



63
64
65
66
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 63

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

#case_sensitive_like=(value) ⇒ Object 



67
68
69
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 67

def case_sensitive_like=(value)
  pragma_set(:case_sensitive_like, !!value ? 'on' : 'off') if sqlite_version >= 30203
end

#database_type ⇒ Object

SQLite uses the :sqlite database type.



55
56
57
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 55

def database_type
  :sqlite
end

#foreign_key_list(table, opts = OPTS) ⇒ Object

Return the array of foreign key info hashes using the foreign_key_list PRAGMA, including information for the :on_update and :on_delete entries.



106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 106

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

#foreign_keys ⇒ Object 



70
71
72
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 70

def foreign_keys
  pragma_get(:foreign_keys).to_i == 1 if sqlite_version >= 30619
end

#foreign_keys=(value) ⇒ Object 



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

def foreign_keys=(value)
  pragma_set(:foreign_keys, !!value ? 'on' : 'off') if sqlite_version >= 30619
end

#freeze ⇒ Object 



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

def freeze
  sqlite_version
  use_timestamp_timezones?
  super
end

#indexes(table, opts = OPTS) ⇒ Object

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



127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 127

def indexes(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  indexes = {}
  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

#pragma_get(name) ⇒ Object 



76
77
78
79
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 76

def pragma_get(name)
  Sequel::Deprecation.deprecate('Database#{pragma_get,auto_vacuum,case_sensitive_like,foreign_keys,synchronous,temp_store} on SQLite', "These methods may not be safe when using multiple connections, call fetch(#{"PRAGMA #{name}".inspect}).single_value if you really want to get the pragma value")
  self["PRAGMA #{name}"].single_value
end

#pragma_set(name, value) ⇒ Object 



80
81
82
83
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 80

def pragma_set(name, value)
  Sequel::Deprecation.deprecate('Database#{pragma_set,auto_vacuum=,case_sensitive_like=,foreign_keys=,synchronous=,temp_store=} on SQLite', "These methods are not thread safe or safe when using multiple connections, pass the appropriate option when connecting to set the pragma correctly for all connections")
  execute_ddl("PRAGMA #{name} = #{value}")
end

#set_integer_booleans ⇒ Object

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



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

def set_integer_booleans
  @integer_booleans = @opts.has_key?(:integer_booleans) ? typecast_value_boolean(@opts[:integer_booleans]) : true
end

#sqlite_version ⇒ Object

The version of the server as an integer, where 3.6.19 = 30619. If the server version can’t be determined, 0 is used.



154
155
156
157
158
159
160
161
162
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 154

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? ⇒ Boolean

SQLite supports CREATE TABLE IF NOT EXISTS syntax since 3.3.0.

Returns:

  • (Boolean) 


165
166
167
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 165

def supports_create_table_if_not_exists?
  sqlite_version >= 30300
end

#supports_deferrable_foreign_key_constraints? ⇒ Boolean

SQLite 3.6.19+ supports deferrable foreign key constraints.

Returns:

  • (Boolean) 


170
171
172
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 170

def supports_deferrable_foreign_key_constraints?
  sqlite_version >= 30619
end

#supports_partial_indexes? ⇒ Boolean

SQLite 3.8.0+ supports partial indexes.

Returns:

  • (Boolean) 


175
176
177
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 175

def supports_partial_indexes?
  sqlite_version >= 30800
end

#supports_savepoints? ⇒ Boolean

SQLite 3.6.8+ supports savepoints.

Returns:

  • (Boolean) 


180
181
182
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 180

def supports_savepoints?
  sqlite_version >= 30608
end

#synchronous ⇒ Object 



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

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

#synchronous=(value) ⇒ Object 



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

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 = OPTS) ⇒ Object

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

Options:

:server

Set the server to use.



199
200
201
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 199

def tables(opts=OPTS)
  tables_and_views(Sequel.~(:name=>'sqlite_sequence') & {:type => 'table'}, opts)
end

#temp_store ⇒ Object 



91
92
93
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 91

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

#temp_store=(value) ⇒ Object 



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

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

#use_timestamp_timezones? ⇒ Boolean

SQLite supports timezones in timestamps, since it just stores them as strings, but it breaks the usage of SQLite’s datetime functions.

Returns:

  • (Boolean) 


191
192
193
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 191

def use_timestamp_timezones?
  defined?(@use_timestamp_timezones) ? @use_timestamp_timezones : (@use_timestamp_timezones = false)
end

#values(v) ⇒ Object

Creates a dataset that uses the VALUES clause:

DB.values([[1, 2], [3, 4]])
VALUES ((1, 2), (3, 4))



207
208
209
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 207

def values(v)
  @default_dataset.clone(:values=>v)
end

#views(opts = OPTS) ⇒ Object

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

Options:

:server

Set the server to use.



215
216
217
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 215

def views(opts=OPTS)
  tables_and_views({:type => 'view'}, opts)
end