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 104
def application_id
  get_int_pragma "application_id"
end

application_id=( integer ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 108
def application_id=( integer )
  set_int_pragma "application_id", integer
end

auto_vacuum() click to toggle source

# File lib/sqlite3/pragmas.rb, line 112
def auto_vacuum
  get_enum_pragma "auto_vacuum"
end

auto_vacuum=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 116
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 120
def automatic_index
  get_boolean_pragma "automatic_index"
end

automatic_index=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 124
def automatic_index=( mode )
  set_boolean_pragma "automatic_index", mode
end

busy_timeout() click to toggle source

# File lib/sqlite3/pragmas.rb, line 128
def busy_timeout
  get_int_pragma "busy_timeout"
end

busy_timeout=( milliseconds ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 132
def busy_timeout=( milliseconds )
  set_int_pragma "busy_timeout", milliseconds
end

cache_size() click to toggle source

# File lib/sqlite3/pragmas.rb, line 136
def cache_size
  get_int_pragma "cache_size"
end

cache_size=( size ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 140
def cache_size=( size )
  set_int_pragma "cache_size", size
end

cache_spill() click to toggle source

# File lib/sqlite3/pragmas.rb, line 144
def cache_spill
  get_boolean_pragma "cache_spill"
end

cache_spill=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 148
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 152
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 156
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 160
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 164
def checkpoint_fullfsync
  get_boolean_pragma "checkpoint_fullfsync"
end

checkpoint_fullfsync=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 168
def checkpoint_fullfsync=( mode )
  set_boolean_pragma "checkpoint_fullfsync", mode
end

collation_list( ) { |row| ... } click to toggle source

# File lib/sqlite3/pragmas.rb, line 172
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 176
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 180
def count_changes
  get_boolean_pragma "count_changes"
end

count_changes=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 184
def count_changes=( mode )
  set_boolean_pragma "count_changes", mode
end

data_version() click to toggle source

# File lib/sqlite3/pragmas.rb, line 188
def data_version
  get_int_pragma "data_version"
end

database_list( ) { |row| ... } click to toggle source

# File lib/sqlite3/pragmas.rb, line 192
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 196
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 200
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 204
def default_synchronous
  get_enum_pragma "default_synchronous"
end

default_synchronous=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 208
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 212
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 216
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 220
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 224
def defer_foreign_keys=( mode )
  set_boolean_pragma "defer_foreign_keys", mode
end

encoding() click to toggle source

# File lib/sqlite3/pragmas.rb, line 228
def encoding
  get_enum_pragma "encoding"
end

encoding=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 232
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 236
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 240
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 244
def foreign_keys
  get_boolean_pragma "foreign_keys"
end

foreign_keys=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 248
def foreign_keys=( mode )
  set_boolean_pragma "foreign_keys", mode
end

freelist_count() click to toggle source

# File lib/sqlite3/pragmas.rb, line 252
def freelist_count
  get_int_pragma "freelist_count"
end

full_column_names() click to toggle source

# File lib/sqlite3/pragmas.rb, line 256
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 260
def full_column_names=( mode )
  set_boolean_pragma "full_column_names", mode
end

fullfsync() click to toggle source

# File lib/sqlite3/pragmas.rb, line 264
def fullfsync
  get_boolean_pragma "fullfsync"
end

fullfsync=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 268
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 13
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 55
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 72
def get_int_pragma( name )
  get_first_value( "PRAGMA #{name}" ).to_i
end

get_query_pragma( name, *parms ) { |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 45
def get_query_pragma( name, *parms, &block ) # :yields: row
  if parms.empty?
    execute( "PRAGMA #{name}", &block )
  else
    args = "'" + parms.join("','") + "'"
    execute( "PRAGMA #{name}( #{args} )", &block )
  end
end

ignore_check_constraints=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 272
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 276
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 280
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 284
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 288
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 292
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 296
def journal_mode
  get_enum_pragma "journal_mode"
end

journal_mode=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 300
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 304
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 308
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 312
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 316
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 320
def locking_mode
  get_enum_pragma "locking_mode"
end

locking_mode=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 324
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 328
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 332
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 336
def mmap_size
  get_int_pragma "mmap_size"
end

mmap_size=( size ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 340
def mmap_size=( size )
  set_int_pragma "mmap_size", size
end

page_count() click to toggle source

# File lib/sqlite3/pragmas.rb, line 344
def page_count
  get_int_pragma "page_count"
end

page_size() click to toggle source

# File lib/sqlite3/pragmas.rb, line 348
def page_size
  get_int_pragma "page_size"
end

page_size=( size ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 352
def page_size=( size )
  set_int_pragma "page_size", size
end

parser_trace=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 356
def parser_trace=( mode )
  set_boolean_pragma "parser_trace", mode
end

query_only() click to toggle source

# File lib/sqlite3/pragmas.rb, line 360
def query_only
  get_boolean_pragma "query_only"
end

query_only=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 364
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 368
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 372
def read_uncommitted
  get_boolean_pragma "read_uncommitted"
end

read_uncommitted=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 376
def read_uncommitted=( mode )
  set_boolean_pragma "read_uncommitted", mode
end

recursive_triggers() click to toggle source

# File lib/sqlite3/pragmas.rb, line 380
def recursive_triggers
  get_boolean_pragma "recursive_triggers"
end

recursive_triggers=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 384
def recursive_triggers=( mode )
  set_boolean_pragma "recursive_triggers", mode
end

reverse_unordered_selects() click to toggle source

# File lib/sqlite3/pragmas.rb, line 388
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 392
def reverse_unordered_selects=( mode )
  set_boolean_pragma "reverse_unordered_selects", mode
end

schema_cookie() click to toggle source

# File lib/sqlite3/pragmas.rb, line 396
def schema_cookie
  get_int_pragma "schema_cookie"
end

schema_cookie=( cookie ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 400
def schema_cookie=( cookie )
  set_int_pragma "schema_cookie", cookie
end

schema_version() click to toggle source

# File lib/sqlite3/pragmas.rb, line 404
def schema_version
  get_int_pragma "schema_version"
end

schema_version=( version ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 408
def schema_version=( version )
  set_int_pragma "schema_version", version
end

secure_delete() click to toggle source

# File lib/sqlite3/pragmas.rb, line 412
def secure_delete
  get_boolean_pragma "secure_delete"
end

secure_delete=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 416
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 20
def set_boolean_pragma( name, mode )
  case mode
  when String
      case mode.downcase
      when "on", "yes", "true", "y", "t"; mode = "'ON'"
      when "off", "no", "false", "n", "f"; mode = "'OFF'"
      else
          raise Exception,
            "unrecognized pragma parameter #{mode.inspect}"
      end
  when true, 1
      mode = "ON"
  when false, 0, nil
      mode = "OFF"
  else
      raise 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 64
def set_enum_pragma( name, mode, enums )
  match = enums.find { |p| p.find { |i| i.to_s.downcase == mode.to_s.downcase } }
  raise Exception,
    "unrecognized #{name} #{mode.inspect}" unless match
  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 78
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 420
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 424
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 428
def shrink_memory
  execute( "PRAGMA shrink_memory" )
end

soft_heap_limit() click to toggle source

# File lib/sqlite3/pragmas.rb, line 432
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 436
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 440
def stats( &block ) # :yields: row
  get_query_pragma "stats", &block
end

synchronous() click to toggle source

# File lib/sqlite3/pragmas.rb, line 444
def synchronous
  get_enum_pragma "synchronous"
end

synchronous=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 448
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 527
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 = Hash[columns.zip(row)]

    # FIXME: This should be removed but is required for older versions
    # of rails
    if(Object.const_defined?(:ActiveRecord))
      new_row['notnull'] = new_row['notnull'].to_s
    end

    tweak_default(new_row) if needs_tweak_default

    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 452
def temp_store
  get_enum_pragma "temp_store"
end

temp_store=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 456
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 460
def threads
  get_int_pragma "threads"
end

threads=( count ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 464
def threads=( count )
  set_int_pragma "threads", count
end

user_cookie() click to toggle source

# File lib/sqlite3/pragmas.rb, line 468
def user_cookie
  get_int_pragma "user_cookie"
end

user_cookie=( cookie ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 472
def user_cookie=( cookie )
  set_int_pragma "user_cookie", cookie
end

user_version() click to toggle source

# File lib/sqlite3/pragmas.rb, line 476
def user_version
  get_int_pragma "user_version"
end

user_version=( version ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 480
def user_version=( version )
  set_int_pragma "user_version", version
end

vdbe_addoptrace=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 484
def vdbe_addoptrace=( mode )
  set_boolean_pragma "vdbe_addoptrace", mode
end

vdbe_debug=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 488
def vdbe_debug=( mode )
  set_boolean_pragma "vdbe_debug", mode
end

vdbe_listing=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 492
def vdbe_listing=( mode )
  set_boolean_pragma "vdbe_listing", mode
end

vdbe_trace() click to toggle source

# File lib/sqlite3/pragmas.rb, line 496
def vdbe_trace
  get_boolean_pragma "vdbe_trace"
end

vdbe_trace=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 500
def vdbe_trace=( mode )
  set_boolean_pragma "vdbe_trace", mode
end

wal_autocheckpoint() click to toggle source

# File lib/sqlite3/pragmas.rb, line 504
def wal_autocheckpoint
  get_int_pragma "wal_autocheckpoint"
end

wal_autocheckpoint=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 508
def wal_autocheckpoint=( mode )
  set_int_pragma "wal_autocheckpoint", mode
end

wal_checkpoint() click to toggle source

# File lib/sqlite3/pragmas.rb, line 512
def wal_checkpoint
  get_enum_pragma "wal_checkpoint"
end

wal_checkpoint=( mode ) click to toggle source

# File lib/sqlite3/pragmas.rb, line 516
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 520
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 576
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 560
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
  return 0
end