module SQLite3::Pragmas

This module is intended for inclusion solely by the Database class. It defines convenience methods for the various pragmas supported by SQLite3.

For a detailed description of these pragmas, see the SQLite3 documentation at sqlite.org/pragma.html.

Constants

AUTO_VACUUM_MODES

The enumeration of valid auto vacuum modes.

ENCODINGS

The list of valid encodings.

JOURNAL_MODES

The list of valid journaling modes.

LOCKING_MODES

The list of valid locking modes.

SYNCHRONOUS_MODES

The enumeration of valid synchronous modes.

TEMP_STORE_MODES

The enumeration of valid temp store modes.

WAL_CHECKPOINTS

The list of valid WAL checkpoints.

Public Instance Methods

application_id() click to toggle source
# File lib/sqlite3/pragmas.rb, line 101
def application_id
  get_int_pragma "application_id"
end
application_id=(integer) click to toggle source
# File lib/sqlite3/pragmas.rb, line 105
def application_id=(integer)
  set_int_pragma "application_id", integer
end
auto_vacuum() click to toggle source
# File lib/sqlite3/pragmas.rb, line 109
def auto_vacuum
  get_enum_pragma "auto_vacuum"
end
auto_vacuum=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 113
def auto_vacuum=(mode)
  set_enum_pragma "auto_vacuum", mode, AUTO_VACUUM_MODES
end
automatic_index() click to toggle source
# File lib/sqlite3/pragmas.rb, line 117
def automatic_index
  get_boolean_pragma "automatic_index"
end
automatic_index=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 121
def automatic_index=(mode)
  set_boolean_pragma "automatic_index", mode
end
busy_timeout() click to toggle source
# File lib/sqlite3/pragmas.rb, line 125
def busy_timeout
  get_int_pragma "busy_timeout"
end
busy_timeout=(milliseconds) click to toggle source
# File lib/sqlite3/pragmas.rb, line 129
def busy_timeout=(milliseconds)
  set_int_pragma "busy_timeout", milliseconds
end
cache_size() click to toggle source
# File lib/sqlite3/pragmas.rb, line 133
def cache_size
  get_int_pragma "cache_size"
end
cache_size=(size) click to toggle source
# File lib/sqlite3/pragmas.rb, line 137
def cache_size=(size)
  set_int_pragma "cache_size", size
end
cache_spill() click to toggle source
# File lib/sqlite3/pragmas.rb, line 141
def cache_spill
  get_boolean_pragma "cache_spill"
end
cache_spill=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 145
def cache_spill=(mode)
  set_boolean_pragma "cache_spill", mode
end
case_sensitive_like=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 149
def case_sensitive_like=(mode)
  set_boolean_pragma "case_sensitive_like", mode
end
cell_size_check() click to toggle source
# File lib/sqlite3/pragmas.rb, line 153
def cell_size_check
  get_boolean_pragma "cell_size_check"
end
cell_size_check=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 157
def cell_size_check=(mode)
  set_boolean_pragma "cell_size_check", mode
end
checkpoint_fullfsync() click to toggle source
# File lib/sqlite3/pragmas.rb, line 161
def checkpoint_fullfsync
  get_boolean_pragma "checkpoint_fullfsync"
end
checkpoint_fullfsync=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 165
def checkpoint_fullfsync=(mode)
  set_boolean_pragma "checkpoint_fullfsync", mode
end
collation_list() { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 169
def collation_list(&block) # :yields: row
  get_query_pragma "collation_list", &block
end
compile_options() { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 173
def compile_options(&block) # :yields: row
  get_query_pragma "compile_options", &block
end
count_changes() click to toggle source
# File lib/sqlite3/pragmas.rb, line 177
def count_changes
  get_boolean_pragma "count_changes"
end
count_changes=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 181
def count_changes=(mode)
  set_boolean_pragma "count_changes", mode
end
data_version() click to toggle source
# File lib/sqlite3/pragmas.rb, line 185
def data_version
  get_int_pragma "data_version"
end
database_list() { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 189
def database_list(&block) # :yields: row
  get_query_pragma "database_list", &block
end
default_cache_size() click to toggle source
# File lib/sqlite3/pragmas.rb, line 193
def default_cache_size
  get_int_pragma "default_cache_size"
end
default_cache_size=(size) click to toggle source
# File lib/sqlite3/pragmas.rb, line 197
def default_cache_size=(size)
  set_int_pragma "default_cache_size", size
end
default_synchronous() click to toggle source
# File lib/sqlite3/pragmas.rb, line 201
def default_synchronous
  get_enum_pragma "default_synchronous"
end
default_synchronous=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 205
def default_synchronous=(mode)
  set_enum_pragma "default_synchronous", mode, SYNCHRONOUS_MODES
end
default_temp_store() click to toggle source
# File lib/sqlite3/pragmas.rb, line 209
def default_temp_store
  get_enum_pragma "default_temp_store"
end
default_temp_store=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 213
def default_temp_store=(mode)
  set_enum_pragma "default_temp_store", mode, TEMP_STORE_MODES
end
defer_foreign_keys() click to toggle source
# File lib/sqlite3/pragmas.rb, line 217
def defer_foreign_keys
  get_boolean_pragma "defer_foreign_keys"
end
defer_foreign_keys=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 221
def defer_foreign_keys=(mode)
  set_boolean_pragma "defer_foreign_keys", mode
end
encoding() click to toggle source
# File lib/sqlite3/pragmas.rb, line 225
def encoding
  get_enum_pragma "encoding"
end
encoding=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 229
def encoding=(mode)
  set_enum_pragma "encoding", mode, ENCODINGS
end
foreign_key_check(*table) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 233
def foreign_key_check(*table, &block) # :yields: row
  get_query_pragma "foreign_key_check", *table, &block
end
foreign_key_list(table) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 237
def foreign_key_list(table, &block) # :yields: row
  get_query_pragma "foreign_key_list", table, &block
end
foreign_keys() click to toggle source
# File lib/sqlite3/pragmas.rb, line 241
def foreign_keys
  get_boolean_pragma "foreign_keys"
end
foreign_keys=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 245
def foreign_keys=(mode)
  set_boolean_pragma "foreign_keys", mode
end
freelist_count() click to toggle source
# File lib/sqlite3/pragmas.rb, line 249
def freelist_count
  get_int_pragma "freelist_count"
end
full_column_names() click to toggle source
# File lib/sqlite3/pragmas.rb, line 253
def full_column_names
  get_boolean_pragma "full_column_names"
end
full_column_names=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 257
def full_column_names=(mode)
  set_boolean_pragma "full_column_names", mode
end
fullfsync() click to toggle source
# File lib/sqlite3/pragmas.rb, line 261
def fullfsync
  get_boolean_pragma "fullfsync"
end
fullfsync=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 265
def fullfsync=(mode)
  set_boolean_pragma "fullfsync", mode
end
get_boolean_pragma(name) click to toggle source

Returns true or false depending on the value of the named pragma.

# File lib/sqlite3/pragmas.rb, line 11
def get_boolean_pragma(name)
  get_first_value("PRAGMA #{name}") != 0
end
get_enum_pragma(name) click to toggle source

Return the value of the given pragma.

# File lib/sqlite3/pragmas.rb, line 51
def get_enum_pragma(name)
  get_first_value("PRAGMA #{name}")
end
get_int_pragma(name) click to toggle source

Returns the value of the given pragma as an integer.

# File lib/sqlite3/pragmas.rb, line 69
def get_int_pragma(name)
  get_first_value("PRAGMA #{name}").to_i
end
get_query_pragma(name, *params) { |row| ... } click to toggle source

Requests the given pragma (and parameters), and if the block is given, each row of the result set will be yielded to it. Otherwise, the results are returned as an array.

# File lib/sqlite3/pragmas.rb, line 41
def get_query_pragma(name, *params, &block) # :yields: row
  if params.empty?
    execute("PRAGMA #{name}", &block)
  else
    args = "'" + params.join("','") + "'"
    execute("PRAGMA #{name}( #{args} )", &block)
  end
end
ignore_check_constraints=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 269
def ignore_check_constraints=(mode)
  set_boolean_pragma "ignore_check_constraints", mode
end
incremental_vacuum(pages) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 273
def incremental_vacuum(pages, &block) # :yields: row
  get_query_pragma "incremental_vacuum", pages, &block
end
index_info(index) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 277
def index_info(index, &block) # :yields: row
  get_query_pragma "index_info", index, &block
end
index_list(table) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 281
def index_list(table, &block) # :yields: row
  get_query_pragma "index_list", table, &block
end
index_xinfo(index) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 285
def index_xinfo(index, &block) # :yields: row
  get_query_pragma "index_xinfo", index, &block
end
integrity_check(*num_errors) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 289
def integrity_check(*num_errors, &block) # :yields: row
  get_query_pragma "integrity_check", *num_errors, &block
end
journal_mode() click to toggle source
# File lib/sqlite3/pragmas.rb, line 293
def journal_mode
  get_enum_pragma "journal_mode"
end
journal_mode=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 297
def journal_mode=(mode)
  set_enum_pragma "journal_mode", mode, JOURNAL_MODES
end
journal_size_limit() click to toggle source
# File lib/sqlite3/pragmas.rb, line 301
def journal_size_limit
  get_int_pragma "journal_size_limit"
end
journal_size_limit=(size) click to toggle source
# File lib/sqlite3/pragmas.rb, line 305
def journal_size_limit=(size)
  set_int_pragma "journal_size_limit", size
end
legacy_file_format() click to toggle source
# File lib/sqlite3/pragmas.rb, line 309
def legacy_file_format
  get_boolean_pragma "legacy_file_format"
end
legacy_file_format=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 313
def legacy_file_format=(mode)
  set_boolean_pragma "legacy_file_format", mode
end
locking_mode() click to toggle source
# File lib/sqlite3/pragmas.rb, line 317
def locking_mode
  get_enum_pragma "locking_mode"
end
locking_mode=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 321
def locking_mode=(mode)
  set_enum_pragma "locking_mode", mode, LOCKING_MODES
end
max_page_count() click to toggle source
# File lib/sqlite3/pragmas.rb, line 325
def max_page_count
  get_int_pragma "max_page_count"
end
max_page_count=(size) click to toggle source
# File lib/sqlite3/pragmas.rb, line 329
def max_page_count=(size)
  set_int_pragma "max_page_count", size
end
mmap_size() click to toggle source
# File lib/sqlite3/pragmas.rb, line 333
def mmap_size
  get_int_pragma "mmap_size"
end
mmap_size=(size) click to toggle source
# File lib/sqlite3/pragmas.rb, line 337
def mmap_size=(size)
  set_int_pragma "mmap_size", size
end
optimize(bitmask = nil) click to toggle source

Attempt to optimize the database.

To customize the optimization options, pass bitmask with a combination of the Constants::Optimize masks.

See www.sqlite.org/pragma.html#pragma_optimize for more information.

# File lib/sqlite3/pragmas.rb, line 347
def optimize(bitmask = nil)
  if bitmask
    set_int_pragma "optimize", bitmask
  else
    execute("PRAGMA optimize")
  end
end
page_count() click to toggle source
# File lib/sqlite3/pragmas.rb, line 355
def page_count
  get_int_pragma "page_count"
end
page_size() click to toggle source
# File lib/sqlite3/pragmas.rb, line 359
def page_size
  get_int_pragma "page_size"
end
page_size=(size) click to toggle source
# File lib/sqlite3/pragmas.rb, line 363
def page_size=(size)
  set_int_pragma "page_size", size
end
parser_trace=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 367
def parser_trace=(mode)
  set_boolean_pragma "parser_trace", mode
end
query_only() click to toggle source
# File lib/sqlite3/pragmas.rb, line 371
def query_only
  get_boolean_pragma "query_only"
end
query_only=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 375
def query_only=(mode)
  set_boolean_pragma "query_only", mode
end
quick_check(*num_errors) { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 379
def quick_check(*num_errors, &block) # :yields: row
  get_query_pragma "quick_check", *num_errors, &block
end
read_uncommitted() click to toggle source
# File lib/sqlite3/pragmas.rb, line 383
def read_uncommitted
  get_boolean_pragma "read_uncommitted"
end
read_uncommitted=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 387
def read_uncommitted=(mode)
  set_boolean_pragma "read_uncommitted", mode
end
recursive_triggers() click to toggle source
# File lib/sqlite3/pragmas.rb, line 391
def recursive_triggers
  get_boolean_pragma "recursive_triggers"
end
recursive_triggers=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 395
def recursive_triggers=(mode)
  set_boolean_pragma "recursive_triggers", mode
end
reverse_unordered_selects() click to toggle source
# File lib/sqlite3/pragmas.rb, line 399
def reverse_unordered_selects
  get_boolean_pragma "reverse_unordered_selects"
end
reverse_unordered_selects=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 403
def reverse_unordered_selects=(mode)
  set_boolean_pragma "reverse_unordered_selects", mode
end
schema_version() click to toggle source
# File lib/sqlite3/pragmas.rb, line 415
def schema_version
  get_int_pragma "schema_version"
end
schema_version=(version) click to toggle source
# File lib/sqlite3/pragmas.rb, line 419
def schema_version=(version)
  set_int_pragma "schema_version", version
end
secure_delete() click to toggle source
# File lib/sqlite3/pragmas.rb, line 423
def secure_delete
  get_boolean_pragma "secure_delete"
end
secure_delete=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 427
def secure_delete=(mode)
  set_boolean_pragma "secure_delete", mode
end
set_boolean_pragma(name, mode) click to toggle source

Sets the given pragma to the given boolean value. The value itself may be true or false, or any other commonly used string or integer that represents truth.

# File lib/sqlite3/pragmas.rb, line 18
def set_boolean_pragma(name, mode)
  case mode
  when String
    case mode.downcase
    when "on", "yes", "true", "y", "t" then mode = "'ON'"
    when "off", "no", "false", "n", "f" then mode = "'OFF'"
    else
      raise SQLite3::Exception, "unrecognized pragma parameter #{mode.inspect}"
    end
  when true, 1
    mode = "ON"
  when false, 0, nil
    mode = "OFF"
  else
    raise SQLite3::Exception, "unrecognized pragma parameter #{mode.inspect}"
  end

  execute("PRAGMA #{name}=#{mode}")
end
set_enum_pragma(name, mode, enums) click to toggle source

Set the value of the given pragma to mode. The mode parameter must conform to one of the values in the given enum array. Each entry in the array is another array comprised of elements in the enumeration that have duplicate values. See synchronous, default_synchronous, temp_store, and default_temp_store for usage examples.

# File lib/sqlite3/pragmas.rb, line 60
def set_enum_pragma(name, mode, enums)
  match = enums.find { |p| p.find { |i| i.to_s.downcase == mode.to_s.downcase } }
  unless match
    raise SQLite3::Exception, "unrecognized #{name} #{mode.inspect}"
  end
  execute("PRAGMA #{name}='#{match.first.upcase}'")
end
set_int_pragma(name, value) click to toggle source

Set the value of the given pragma to the integer value of the value parameter.

# File lib/sqlite3/pragmas.rb, line 75
def set_int_pragma(name, value)
  execute("PRAGMA #{name}=#{value.to_i}")
end
short_column_names() click to toggle source
# File lib/sqlite3/pragmas.rb, line 431
def short_column_names
  get_boolean_pragma "short_column_names"
end
short_column_names=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 435
def short_column_names=(mode)
  set_boolean_pragma "short_column_names", mode
end
shrink_memory() click to toggle source
# File lib/sqlite3/pragmas.rb, line 439
def shrink_memory
  execute("PRAGMA shrink_memory")
end
soft_heap_limit() click to toggle source
# File lib/sqlite3/pragmas.rb, line 443
def soft_heap_limit
  get_int_pragma "soft_heap_limit"
end
soft_heap_limit=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 447
def soft_heap_limit=(mode)
  set_int_pragma "soft_heap_limit", mode
end
stats() { |row| ... } click to toggle source
# File lib/sqlite3/pragmas.rb, line 451
def stats(&block) # :yields: row
  get_query_pragma "stats", &block
end
synchronous() click to toggle source
# File lib/sqlite3/pragmas.rb, line 455
def synchronous
  get_enum_pragma "synchronous"
end
synchronous=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 459
def synchronous=(mode)
  set_enum_pragma "synchronous", mode, SYNCHRONOUS_MODES
end
table_info(table) { |new_row| ... } click to toggle source

Returns information about table. Yields each row of table information if a block is provided.

# File lib/sqlite3/pragmas.rb, line 538
def table_info table
  stmt = prepare "PRAGMA table_info(#{table})"
  columns = stmt.columns

  needs_tweak_default =
    version_compare(SQLite3.libversion.to_s, "3.3.7") > 0

  result = [] unless block_given?
  stmt.each do |row|
    new_row = columns.zip(row).to_h

    tweak_default(new_row) if needs_tweak_default

    # Ensure the type value is downcased.  On Mac and Windows
    # platforms this value is now being returned as all upper
    # case.
    if new_row["type"]
      new_row["type"] = new_row["type"].downcase
    end

    if block_given?
      yield new_row
    else
      result << new_row
    end
  end
  stmt.close

  result
end
temp_store() click to toggle source
# File lib/sqlite3/pragmas.rb, line 463
def temp_store
  get_enum_pragma "temp_store"
end
temp_store=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 467
def temp_store=(mode)
  set_enum_pragma "temp_store", mode, TEMP_STORE_MODES
end
threads() click to toggle source
# File lib/sqlite3/pragmas.rb, line 471
def threads
  get_int_pragma "threads"
end
threads=(count) click to toggle source
# File lib/sqlite3/pragmas.rb, line 475
def threads=(count)
  set_int_pragma "threads", count
end
user_version() click to toggle source
# File lib/sqlite3/pragmas.rb, line 487
def user_version
  get_int_pragma "user_version"
end
user_version=(version) click to toggle source
# File lib/sqlite3/pragmas.rb, line 491
def user_version=(version)
  set_int_pragma "user_version", version
end
vdbe_addoptrace=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 495
def vdbe_addoptrace=(mode)
  set_boolean_pragma "vdbe_addoptrace", mode
end
vdbe_debug=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 499
def vdbe_debug=(mode)
  set_boolean_pragma "vdbe_debug", mode
end
vdbe_listing=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 503
def vdbe_listing=(mode)
  set_boolean_pragma "vdbe_listing", mode
end
vdbe_trace() click to toggle source
# File lib/sqlite3/pragmas.rb, line 507
def vdbe_trace
  get_boolean_pragma "vdbe_trace"
end
vdbe_trace=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 511
def vdbe_trace=(mode)
  set_boolean_pragma "vdbe_trace", mode
end
wal_autocheckpoint() click to toggle source
# File lib/sqlite3/pragmas.rb, line 515
def wal_autocheckpoint
  get_int_pragma "wal_autocheckpoint"
end
wal_autocheckpoint=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 519
def wal_autocheckpoint=(mode)
  set_int_pragma "wal_autocheckpoint", mode
end
wal_checkpoint() click to toggle source
# File lib/sqlite3/pragmas.rb, line 523
def wal_checkpoint
  get_enum_pragma "wal_checkpoint"
end
wal_checkpoint=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 527
def wal_checkpoint=(mode)
  set_enum_pragma "wal_checkpoint", mode, WAL_CHECKPOINTS
end
writable_schema=(mode) click to toggle source
# File lib/sqlite3/pragmas.rb, line 531
def writable_schema=(mode)
  set_boolean_pragma "writable_schema", mode
end

Private Instance Methods

tweak_default(hash) click to toggle source

Since SQLite 3.3.8, the table_info pragma has returned the default value of the row as a quoted SQL value. This method essentially unquotes those values.

# File lib/sqlite3/pragmas.rb, line 588
def tweak_default(hash)
  case hash["dflt_value"]
  when /^null$/i
    hash["dflt_value"] = nil
  when /^'(.*)'$/m
    hash["dflt_value"] = $1.gsub("''", "'")
  when /^"(.*)"$/m
    hash["dflt_value"] = $1.gsub('""', '"')
  end
end
version_compare(v1, v2) click to toggle source

Compares two version strings

# File lib/sqlite3/pragmas.rb, line 572
def version_compare(v1, v2)
  v1 = v1.split(".").map { |i| i.to_i }
  v2 = v2.split(".").map { |i| i.to_i }
  parts = [v1.length, v2.length].max
  v1.push 0 while v1.length < parts
  v2.push 0 while v2.length < parts
  v1.zip(v2).each do |a, b|
    return -1 if a < b
    return 1 if a > b
  end
  0
end