Class: SQLite3::Database

Inherits:
Object
  • Object
show all
Includes:
Pragmas
Defined in:
lib/sqlite3/database.rb,
lib/sqlite3/ffi/database.rb

Overview

Overview

The Database class encapsulates a single connection to a SQLite3 database. Here’s a straightforward example of usage:

require 'sqlite3'

SQLite3::Database.new( "data.db" ) do |db|
  db.execute( "select * from table" ) do |row|
    p row
  end
end

It wraps the lower-level methods provided by the selected driver, and includes the Pragmas module for access to various pragma convenience methods.

The Database class provides type translation services as well, by which the SQLite3 data types (which are all represented as strings) may be converted into their corresponding types (as defined in the schemas for their tables). This translation only occurs when querying data from the database–insertions and updates are all still typeless.

Furthermore, the Database class has been designed to work well with the ArrayFields module from Ara Howard. If you require the ArrayFields module before performing a query, and if you have not enabled results as hashes, then the results will all be indexible by field name.

Thread safety

When SQLite3.threadsafe? returns true, it is safe to share instances of the database class among threads without adding specific locking. Other object instances may require applications to provide their own locks if they are to be shared among threads. Please see the README.md for more information.

SQLite Extensions

SQLite3::Database supports the universe of sqlite extensions. It’s possible to load an extension into an existing Database object using the #load_extension method and passing a filesystem path:

db = SQLite3::Database.new(":memory:")
db.enable_load_extension(true)
db.load_extension("/path/to/extension")

As of v2.4.0, it’s also possible to pass an object that responds to #to_path. This documentation will refer to the supported interface as _ExtensionSpecifier, which can be expressed in RBS syntax as:

interface _ExtensionSpecifier
  def to_path: ()

So, for example, if you are using the sqlean gem which provides modules that implement this interface, you can pass the module directly:

db = SQLite3::Database.new(":memory:")
db.enable_load_extension(true)
db.load_extension(SQLean::Crypto)

It’s also possible in v2.4.0+ to load extensions via the SQLite3::Database constructor by using the extensions: keyword argument to pass an array of String paths or extension specifiers:

db = SQLite3::Database.new(":memory:", extensions: ["/path/to/extension", SQLean::Crypto])

Note that when loading extensions via the constructor, there is no need to call #enable_load_extension; however it is still necessary to call #enable_load_extensions before any subsequently invocations of #load_extension on the initialized Database object.

You can load extensions in a Rails application by using the extensions: configuration option:

# config/database.yml
development:
  adapter: sqlite3
  extensions:
    - .sqlpkg/nalgeon/crypto/crypto.so # a filesystem path
    - <%= SQLean::UUID.to_path %>      # or ruby code returning a path
    - SQLean::Crypto                   # Rails 8.1+ accepts the name of a constant that responds to `to_path`

Defined Under Namespace

Classes: FunctionProxy

Constant Summary

Constants included from Pragmas

Pragmas::AUTO_VACUUM_MODES, Pragmas::ENCODINGS, Pragmas::JOURNAL_MODES, Pragmas::LOCKING_MODES, Pragmas::SYNCHRONOUS_MODES, Pragmas::TEMP_STORE_MODES, Pragmas::WAL_CHECKPOINTS

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Pragmas

#application_id, #application_id=, #auto_vacuum, #auto_vacuum=, #automatic_index, #automatic_index=, #cache_size, #cache_size=, #cache_spill, #cache_spill=, #case_sensitive_like=, #cell_size_check, #cell_size_check=, #checkpoint_fullfsync, #checkpoint_fullfsync=, #collation_list, #compile_options, #count_changes, #count_changes=, #data_version, #database_list, #default_cache_size, #default_cache_size=, #default_synchronous, #default_synchronous=, #default_temp_store, #default_temp_store=, #defer_foreign_keys, #defer_foreign_keys=, #encoding=, #foreign_key_check, #foreign_key_list, #foreign_keys, #foreign_keys=, #freelist_count, #full_column_names, #full_column_names=, #fullfsync, #fullfsync=, #get_boolean_pragma, #get_enum_pragma, #get_int_pragma, #get_query_pragma, #ignore_check_constraints=, #incremental_vacuum, #index_info, #index_list, #index_xinfo, #integrity_check, #journal_mode, #journal_mode=, #journal_size_limit, #journal_size_limit=, #legacy_file_format, #legacy_file_format=, #locking_mode, #locking_mode=, #max_page_count, #max_page_count=, #mmap_size, #mmap_size=, #optimize, #page_count, #page_size, #page_size=, #parser_trace=, #query_only, #query_only=, #quick_check, #read_uncommitted, #read_uncommitted=, #recursive_triggers, #recursive_triggers=, #reverse_unordered_selects, #reverse_unordered_selects=, #schema_cookie, #schema_cookie=, #schema_version, #schema_version=, #secure_delete, #secure_delete=, #set_boolean_pragma, #set_enum_pragma, #set_int_pragma, #short_column_names, #short_column_names=, #shrink_memory, #soft_heap_limit, #soft_heap_limit=, #stats, #synchronous, #synchronous=, #table_info, #temp_store, #temp_store=, #threads, #threads=, #user_cookie, #user_cookie=, #user_version, #user_version=, #vdbe_addoptrace=, #vdbe_debug=, #vdbe_listing=, #vdbe_trace, #vdbe_trace=, #wal_autocheckpoint, #wal_autocheckpoint=, #wal_checkpoint, #wal_checkpoint=, #writable_schema=

Constructor Details

#initialize(file, options = {}, zvfs = nil) ⇒ Database

call-seq:

SQLite3::Database.new(file, options = {})

Create a new Database object that opens the given file.

Supported permissions options:

  • the default mode is READWRITE | CREATE

  • readonly: boolean (default false), true to set the mode to READONLY

  • readwrite: boolean (default false), true to set the mode to READWRITE

  • flags: set the mode to a combination of SQLite3::Constants::Open flags.

Supported encoding options:

  • utf16: boolish (default false), is the filename’s encoding UTF-16 (only needed if the filename encoding is not UTF_16LE or BE)

Other supported options:

  • strict: boolish (default false), disallow the use of double-quoted string literals (see www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted)

  • results_as_hash: boolish (default false), return rows as hashes instead of arrays

  • default_transaction_mode: one of :deferred (default), :immediate, or :exclusive. If a mode is not specified in a call to #transaction, this will be the default transaction mode.

  • extensions: Array[String | _ExtensionSpecifier] SQLite extensions to load into the database. See Database@SQLite+Extensions for more information.



142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/sqlite3/database.rb', line 142

def initialize file, options = {}, zvfs = nil
  mode = Constants::Open::READWRITE | Constants::Open::CREATE

  file = file.to_path if file.respond_to? :to_path
  if file.encoding == ::Encoding::UTF_16LE || file.encoding == ::Encoding::UTF_16BE || options[:utf16]
    open16 file
  else
    # The three primary flag values for sqlite3_open_v2 are:
    # SQLITE_OPEN_READONLY
    # SQLITE_OPEN_READWRITE
    # SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE -- always used for sqlite3_open and sqlite3_open16
    mode = Constants::Open::READONLY if options[:readonly]

    if options[:readwrite]
      raise "conflicting options: readonly and readwrite" if options[:readonly]
      mode = Constants::Open::READWRITE
    end

    if options[:flags]
      if options[:readonly] || options[:readwrite]
        raise "conflicting options: flags with readonly and/or readwrite"
      end
      mode = options[:flags]
    end

    open_v2 file.encode("utf-8"), mode, zvfs

    if options[:strict]
      disable_quirk_mode
    end
  end

  @tracefunc = nil
  @authorizer = nil
  @progress_handler = nil
  @collations = {}
  @functions = {}
  @results_as_hash = options[:results_as_hash]
  @readonly = mode & Constants::Open::READONLY != 0
  @default_transaction_mode = options[:default_transaction_mode] || :deferred

  initialize_extensions(options[:extensions])

  ForkSafety.track(self)

  if block_given?
    begin
      yield self
    ensure
      close
    end
  end
end

Instance Attribute Details

#collations ⇒ Object (readonly)

Returns the value of attribute collations.



88
89
90
 
# File 'lib/sqlite3/database.rb', line 88

def collations
  @collations
end

#results_as_hash ⇒ Object

A boolean that indicates whether rows in result sets should be returned as hashes or not. By default, rows are returned as arrays.



120
121
122
 
# File 'lib/sqlite3/database.rb', line 120

def results_as_hash
  @results_as_hash
end

Class Method Details

.open(*args) ⇒ Object

Without block works exactly as new. With block, like new closes the database at the end, but unlike new returns the result of the block instead of the database instance.



96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/sqlite3/database.rb', line 96

def open(*args)
  database = new(*args)

  if block_given?
    begin
      yield database
    ensure
      database.close
    end
  else
    database
  end
end

.quote(string) ⇒ Object

Quotes the given string, making it safe to use in an SQL statement. It replaces all instances of the single-quote character with two single-quote characters. The modified string is returned.



113
114
115
 
# File 'lib/sqlite3/database.rb', line 113

def quote(string)
  string.gsub("'", "''")
end

Instance Method Details

#authorizer(&block) ⇒ Object

Installs (or removes) a block that will be invoked for every access to the database. If the block returns 0 (or nil), the statement is allowed to proceed. Returning 1 causes an authorization error to occur, and returning 2 causes the access to be silently denied.



207
208
209
 
# File 'lib/sqlite3/database.rb', line 207

def authorizer(&block)
  self.authorizer = block
end

#authorizer=(authorizer) ⇒ Object 



93
94
95
96
97
98
99
 
# File 'lib/sqlite3/ffi/database.rb', line 93

def authorizer=(authorizer)
  require_open_db

  status = FFI::CApi.sqlite3_set_authorizer(@db, authorizer.nil? ? nil : FFI::AUTH, FFI.wrap(authorizer))
  FFI.check(@db, status)
  @authorizer = authorizer
end

#build_result_set(stmt) ⇒ Object

Given a statement, return a result set. This is not intended for general consumption :nodoc:



790
791
792
793
794
795
796
 
# File 'lib/sqlite3/database.rb', line 790

def build_result_set stmt
  if results_as_hash
    HashResultSet.new(self, stmt)
  else
    ResultSet.new(self, stmt)
  end
end

#busy_handler(blk = nil, &block) ⇒ Object 



28
29
30
31
32
33
34
35
36
 
# File 'lib/sqlite3/ffi/database.rb', line 28

def busy_handler(blk = nil, &block)
  require_open_db

  blk ||= block
  @busy_handler = blk
  status = FFI::CApi.sqlite3_busy_handler(@db, FFI::BUSY_HANDLER, FFI.wrap(self))
  FFI.check(@db, status)
  self
end

#busy_handler_timeout=(milliseconds) ⇒ Object

Sets a #busy_handler that releases the GVL between retries, but only retries up to the indicated number of milliseconds. This is an alternative to #busy_timeout, which holds the GVL while SQLite sleeps and retries.



693
694
695
696
697
698
699
700
701
702
703
704
705
706
 
# File 'lib/sqlite3/database.rb', line 693

def busy_handler_timeout=(milliseconds)
  timeout_seconds = milliseconds.fdiv(1000)

  busy_handler do |count|
    now = Process.clock_gettime(Process::CLOCK_MONOTONIC)
    if count.zero?
      @timeout_deadline = now + timeout_seconds
    elsif now > @timeout_deadline
      next false
    else
      sleep(0.001)
    end
  end
end

#busy_timeout ⇒ Object 



387
 
# File 'lib/sqlite3/database.rb', line 387

alias_method :busy_timeout, :busy_timeout=

#busy_timeout=(timeout) ⇒ Object 



101
102
103
104
105
 
# File 'lib/sqlite3/ffi/database.rb', line 101

def busy_timeout=(timeout)
  require_open_db

  FFI.check(@db, FFI::CApi.sqlite3_busy_timeout(@db, timeout))
end

#changes ⇒ Object 



87
88
89
90
91
 
# File 'lib/sqlite3/ffi/database.rb', line 87

def changes
  require_open_db

  FFI::CApi.sqlite3_changes(@db)
end

#close ⇒ Object 



3
4
5
6
7
 
# File 'lib/sqlite3/ffi/database.rb', line 3

def close
  close_or_discard_db
  @aggregators = nil
  self
end

#closed? ⇒ Boolean

Returns:

  • (Boolean) 


9
10
11
 
# File 'lib/sqlite3/ffi/database.rb', line 9

def closed?
  @db.nil?
end

#collation(name, comparator) ⇒ Object 



114
115
116
117
118
119
120
121
 
# File 'lib/sqlite3/ffi/database.rb', line 114

def collation(name, comparator)
  require_open_db

  status = FFI::CApi.sqlite3_create_collation(@db, name, FFI::CApi::SQLITE_UTF8, FFI.wrap(comparator), comparator.nil? ? nil : FFI::COMPARATOR)
  FFI.check(@db, status)
  @collations[name] = comparator
  self
end

#commit ⇒ Object

Commits the current transaction. If there is no current transaction, this will cause an error to be raised. This returns true, in order to allow it to be used in idioms like abort? and rollback or commit.



669
670
671
672
 
# File 'lib/sqlite3/database.rb', line 669

def commit
  execute "commit transaction"
  true
end

#complete?(sql) ⇒ Boolean

Returns:

  • (Boolean) 


83
84
85
 
# File 'lib/sqlite3/ffi/database.rb', line 83

def complete?(sql)
  FFI::CApi.sqlite3_complete(FFI.string_value(sql)) != 0
end

#create_aggregate(name, arity, step = nil, finalize = nil, text_rep = Constants::TextRep::ANY, &block) ⇒ Object

Creates a new aggregate function for use in SQL statements. Aggregate functions are functions that apply over every row in the result set, instead of over just a single row. (A very common aggregate function is the “count” function, for determining the number of rows that match a query.)

The new function will be added as name, with the given arity. (For variable arity functions, use -1 for the arity.)

The step parameter must be a proc object that accepts as its first parameter a FunctionProxy instance (representing the function invocation), with any subsequent parameters (up to the function’s arity). The step callback will be invoked once for each row of the result set.

The finalize parameter must be a proc object that accepts only a single parameter, the FunctionProxy instance representing the current function invocation. It should invoke FunctionProxy#result= to store the result of the function.

Example:

db.create_aggregate( "lengths", 1 ) do
  step do |func, value|
    func[ :total ] ||= 0
    func[ :total ] += ( value ? value.length : 0 )
  end

  finalize do |func|
    func.result = func[ :total ] || 0
  end
end

puts db.get_first_value( "select lengths(name) from table" )

See also #create_aggregate_handler for a more object-oriented approach to aggregate functions.



457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
 
# File 'lib/sqlite3/database.rb', line 457

def create_aggregate(name, arity, step = nil, finalize = nil,
  text_rep = Constants::TextRep::ANY, &block)

  proxy = Class.new do
    def self.step(&block)
      define_method(:step_with_ctx, &block)
    end

    def self.finalize(&block)
      define_method(:finalize_with_ctx, &block)
    end
  end

  if block
    proxy.instance_eval(&block)
  else
    proxy.class_eval do
      define_method(:step_with_ctx, step)
      define_method(:finalize_with_ctx, finalize)
    end
  end

  proxy.class_eval do
    # class instance variables
    @name = name
    @arity = arity

    def self.name
      @name
    end

    def self.arity
      @arity
    end

    def initialize
      @ctx = FunctionProxy.new
    end

    def step(*args)
      step_with_ctx(@ctx, *args)
    end

    def finalize
      finalize_with_ctx(@ctx)
      @ctx.result
    end
  end
  define_aggregator2(proxy, name)
end

#create_aggregate_handler(handler) ⇒ Object

This is another approach to creating an aggregate function (see #create_aggregate). Instead of explicitly specifying the name, callbacks, arity, and type, you specify a factory object (the “handler”) that knows how to obtain all of that information. The handler should respond to the following messages:

arity

corresponds to the arity parameter of #create_aggregate. This message is optional, and if the handler does not respond to it, the function will have an arity of -1.

name

this is the name of the function. The handler must implement this message.

new

this must be implemented by the handler. It should return a new instance of the object that will handle a specific invocation of the function.

The handler instance (the object returned by the new message, described above), must respond to the following messages:

step

this is the method that will be called for each step of the aggregate function’s evaluation. It should implement the same signature as the step callback for #create_aggregate.

finalize

this is the method that will be called to finalize the aggregate function’s evaluation. It should implement the same signature as the finalize callback for #create_aggregate.

Example:

class LengthsAggregateHandler
  def self.arity; 1; end
  def self.name; 'lengths'; end

  def initialize
    @total = 0
  end

  def step( ctx, name )
    @total += ( name ? name.length : 0 )
  end

  def finalize( ctx )
    ctx.result = @total
  end
end

db.create_aggregate_handler( LengthsAggregateHandler )
puts db.get_first_value( "select lengths(name) from A" )



555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
 
# File 'lib/sqlite3/database.rb', line 555

def create_aggregate_handler(handler)
  # This is a compatibility shim so the (basically pointless) FunctionProxy
  # "ctx" object is passed as first argument to both step() and finalize().
  # Now its up to the library user whether he prefers to store his
  # temporaries as instance variables or fields in the FunctionProxy.
  # The library user still must set the result value with
  # FunctionProxy.result= as there is no backwards compatible way to
  # change this.
  proxy = Class.new(handler) do
    def initialize
      super
      @fp = FunctionProxy.new
    end

    def step(*args)
      super(@fp, *args)
    end

    def finalize
      super(@fp)
      @fp.result
    end
  end
  define_aggregator2(proxy, proxy.name)
  self
end

#create_function(name, arity, text_rep = Constants::TextRep::UTF8, &block) ⇒ Object

Creates a new function for use in SQL statements. It will be added as name, with the given arity. (For variable arity functions, use -1 for the arity.)

The block should accept at least one parameter–the FunctionProxy instance that wraps this function invocation–and any other arguments it needs (up to its arity).

The block does not return a value directly. Instead, it will invoke the FunctionProxy#result= method on the func parameter and indicate the return value that way.

Example:

db.create_function( "maim", 1 ) do |func, value|
  if value.nil?
    func.result = nil
  else
    func.result = value.split(//).sort.join
  end
end

puts db.get_first_value( "select maim(name) from table" )



412
413
414
415
416
417
418
419
 
# File 'lib/sqlite3/database.rb', line 412

def create_function name, arity, text_rep = Constants::TextRep::UTF8, &block
  define_function_with_flags(name, text_rep) do |*args|
    fp = FunctionProxy.new
    block.call(fp, *args)
    fp.result
  end
  self
end

#db_filename(db_name) ⇒ Object 



156
157
158
159
160
161
 
# File 'lib/sqlite3/ffi/database.rb', line 156

def db_filename(db_name)
  require_open_db

  fname = FFI::CApi.sqlite3_db_filename(@db, db_name)
  fname.null? ? nil : fname.read_string.force_encoding(Encoding::UTF_8)
end

#define_aggregator(name, aggregator) ⇒ Object

Define an aggregate function named name using a object template object aggregator. aggregator must respond to step and finalize. step will be called with row information and finalize must return the return value for the aggregator function.

_API Change:_ aggregator must also implement clone. The provided aggregator object will serve as template that is cloned to provide the individual instances of the aggregate function. Regular ruby objects already provide a suitable clone. The functions arity is the arity of the step method.



592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
 
# File 'lib/sqlite3/database.rb', line 592

def define_aggregator(name, aggregator)
  # Previously, this has been implemented in C. Now this is just yet
  # another compatibility shim
  proxy = Class.new do
    @template = aggregator
    @name = name

    def self.template
      @template
    end

    def self.name
      @name
    end

    def self.arity
      # this is what sqlite3_obj_method_arity did before
      @template.method(:step).arity
    end

    def initialize
      @klass = self.class.template.clone
    end

    def step(*args)
      @klass.step(*args)
    end

    def finalize
      @klass.finalize
    end
  end
  define_aggregator2(proxy, name)
  self
end

#define_function(name, &block) ⇒ Object 



60
61
62
 
# File 'lib/sqlite3/ffi/database.rb', line 60

def define_function(name, &block)
  define_function_with_flags(name, FFI::CApi::SQLITE_UTF8, &block)
end

#define_function_with_flags(name, flags, &block) ⇒ Object 



51
52
53
54
55
56
57
58
 
# File 'lib/sqlite3/ffi/database.rb', line 51

def define_function_with_flags(name, flags, &block)
  require_open_db

  status = FFI::CApi.sqlite3_create_function(@db, FFI.string_value(name), block.arity, flags, FFI.wrap(block), FFI::FUNC, nil, nil)
  FFI.check(@db, status)
  @functions[name] = block
  self
end

#enable_load_extension(onoff) ⇒ Object 



124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/sqlite3/ffi/database.rb', line 124

def enable_load_extension(onoff)
  require_open_db

  if onoff == true
    onoffparam = 1
  elsif onoff == false
    onoffparam = 0
  else
    onoffparam = onoff.to_i
  end
  FFI.check(@db, FFI::CApi.sqlite3_enable_load_extension(@db, onoffparam))
  self
end

#encoding ⇒ Object

call-seq: db.encoding

Fetch the encoding set on this database



199
200
201
 
# File 'lib/sqlite3/database.rb', line 199

def encoding
  Encoding.find super
end

#errcode ⇒ Object 



77
78
79
80
81
 
# File 'lib/sqlite3/ffi/database.rb', line 77

def errcode
  require_open_db

  FFI::CApi.sqlite3_errcode(@db)
end

#errmsg ⇒ Object 



71
72
73
74
75
 
# File 'lib/sqlite3/ffi/database.rb', line 71

def errmsg
  require_open_db

  FFI::CApi.sqlite3_errmsg(@db)
end

#exec_batch(sql, results_as_hash) ⇒ Object 



145
146
147
148
149
150
151
152
153
154
 
# File 'lib/sqlite3/ffi/database.rb', line 145

def exec_batch(sql, results_as_hash)
  require_open_db

  callback_ary = []
  err_msg = ::FFI::MemoryPointer.new(:pointer)
  callback = results_as_hash == true ? FFI::HASH_CALLBACK : FFI::REGULAR_CALLBACK
  status = FFI::CApi.sqlite3_exec(@db, FFI.string_value(sql), callback, FFI.wrap(callback_ary), err_msg)
  FFI.check_msg(@db, status, err_msg)
  callback_ary
end

#execute(sql, bind_vars = [], &block) ⇒ Object

Executes the given SQL statement. If additional parameters are given, they are treated as bind variables, and are bound to the placeholders in the query.

Note that if any of the values passed to this are hashes, then the key/value pairs are each bound separately, with the key being used as the name of the placeholder to bind the value to.

The block is optional. If given, it will be invoked for each row returned by the query. Otherwise, any results are accumulated into an array and returned wholesale.

See also #execute2, #query, and #execute_batch for additional ways of executing statements.



248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
# File 'lib/sqlite3/database.rb', line 248

def execute sql, bind_vars = [], &block
  prepare(sql) do |stmt|
    stmt.bind_params(bind_vars)
    stmt = build_result_set stmt

    if block
      stmt.each do |row|
        yield row
      end
    else
      stmt.to_a.freeze
    end
  end
end

#execute2(sql, *bind_vars) ⇒ Object

Executes the given SQL statement, exactly as with #execute. However, the first row returned (either via the block, or in the returned array) is always the names of the columns. Subsequent rows correspond to the data from the result set.

Thus, even if the query itself returns no rows, this method will always return at least one row–the names of the columns.

See also #execute, #query, and #execute_batch for additional ways of executing statements.



273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/sqlite3/database.rb', line 273

def execute2(sql, *bind_vars)
  prepare(sql) do |stmt|
    result = stmt.execute(*bind_vars)
    if block_given?
      yield stmt.columns
      result.each { |row| yield row }
    else
      return result.each_with_object([stmt.columns]) { |row, arr|
               arr << row
             }
    end
  end
end

#execute_batch(sql, bind_vars = []) ⇒ Object

Executes all SQL statements in the given string. By contrast, the other means of executing queries will only execute the first statement in the string, ignoring all subsequent statements. This will execute each one in turn. The same bind parameters, if given, will be applied to each statement.

This always returns the result of the last statement.

See also #execute_batch2 for additional ways of executing statements.



297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
 
# File 'lib/sqlite3/database.rb', line 297

def execute_batch(sql, bind_vars = [])
  sql = sql.strip
  result = nil
  until sql.empty?
    prepare(sql) do |stmt|
      unless stmt.closed?
        # FIXME: this should probably use sqlite3's api for batch execution
        # This implementation requires stepping over the results.
        if bind_vars.length == stmt.bind_parameter_count
          stmt.bind_params(bind_vars)
        end
        result = stmt.step
      end
      sql = stmt.remainder.strip
    end
  end

  result
end

#execute_batch2(sql, &block) ⇒ Object

Executes all SQL statements in the given string. By contrast, the other means of executing queries will only execute the first statement in the string, ignoring all subsequent statements. This will execute each one in turn. Bind parameters cannot be passed to #execute_batch2.

If a query is made, all values will be returned as strings. If no query is made, an empty array will be returned.

Because all values except for ‘NULL’ are returned as strings, a block can be passed to parse the values accordingly.

See also #execute_batch for additional ways of executing statements.



330
331
332
333
334
335
336
337
338
339
 
# File 'lib/sqlite3/database.rb', line 330

def execute_batch2(sql, &block)
  if block
    result = exec_batch(sql, @results_as_hash)
    result.map do |val|
      yield val
    end
  else
    exec_batch(sql, @results_as_hash)
  end
end

#extended_result_codes=(enable) ⇒ Object 



107
108
109
110
111
112
 
# File 'lib/sqlite3/ffi/database.rb', line 107

def extended_result_codes=(enable)
  require_open_db

  FFI::CApi.sqlite3_extended_result_codes(@db, enable ? 1 : 0)
  self
end

#filename(db_name = "main") ⇒ Object

Returns the filename for the database named db_name. db_name defaults to “main”. Main return nil or an empty string if the database is temporary or in-memory.



230
231
232
 
# File 'lib/sqlite3/database.rb', line 230

def filename db_name = "main"
  db_filename db_name
end

#get_first_row(sql, *bind_vars) ⇒ Object

A convenience method for obtaining the first row of a result set, and discarding all others. It is otherwise identical to #execute.

See also #get_first_value.



369
370
371
 
# File 'lib/sqlite3/database.rb', line 369

def get_first_row(sql, *bind_vars)
  execute(sql, *bind_vars).first
end

#get_first_value(sql, *bind_vars) ⇒ Object

A convenience method for obtaining the first value of the first row of a result set, and discarding all other values and rows. It is otherwise identical to #execute.

See also #get_first_row.



378
379
380
381
382
383
384
385
 
# File 'lib/sqlite3/database.rb', line 378

def get_first_value(sql, *bind_vars)
  query(sql, bind_vars) do |rs|
    if (row = rs.next)
      return @results_as_hash ? row[rs.columns[0]] : row[0]
    end
  end
  nil
end

#initialize_extensions(extensions) ⇒ Object

:nodoc:

Raises:

  • (TypeError) 


738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
 
# File 'lib/sqlite3/database.rb', line 738

def initialize_extensions(extensions) # :nodoc:
  return if extensions.nil?
  raise TypeError, "extensions must be an Array" unless extensions.is_a?(Array)
  return if extensions.empty?

  begin
    enable_load_extension(true)

    extensions.each do |extension|
      load_extension(extension)
    end
  ensure
    enable_load_extension(false)
  end
end

#interrupt ⇒ Object 



64
65
66
67
68
69
 
# File 'lib/sqlite3/ffi/database.rb', line 64

def interrupt
  require_open_db

  FFI::CApi.sqlite3_interrupt(@db)
  self
end

#last_insert_row_id ⇒ Object 



45
46
47
48
49
 
# File 'lib/sqlite3/ffi/database.rb', line 45

def last_insert_row_id
  require_open_db

  FFI::CApi.sqlite3_last_insert_rowid(@db)
end

#load_extension(extension_specifier) ⇒ Object

call-seq:

load_extension(extension_specifier) -> self

Loads an SQLite extension library from the named file. Extension loading must be enabled using #enable_load_extension prior to using this method.

See also: Database@SQLite+Extensions

Parameters

  • extension_specifier: (String | _ExtensionSpecifier) If a String, it is the filesystem path to the sqlite extension file. If an object that responds to #to_path, the return value of that method is used as the filesystem path to the sqlite extension file.

Example

Using a filesystem path:

db.load_extension("/path/to/my_extension.so")
Example

Using the sqlean gem:

db.load_extension(SQLean::VSV)



729
730
731
732
733
734
735
736
 
# File 'lib/sqlite3/database.rb', line 729

def load_extension(extension_specifier)
  if extension_specifier.respond_to?(:to_path)
    extension_specifier = extension_specifier.to_path
  elsif !extension_specifier.is_a?(String)
    raise TypeError, "extension_specifier #{extension_specifier.inspect} is not a String or a valid extension specifier object"
  end
  load_extension_internal(extension_specifier)
end

#prepare(sql) ⇒ Object

Returns a Statement object representing the given SQL. This does not execute the statement; it merely prepares the statement for execution.

The Statement can then be executed using Statement#execute.



216
217
218
219
220
221
222
223
224
225
 
# File 'lib/sqlite3/database.rb', line 216

def prepare sql
  stmt = SQLite3::Statement.new(self, sql)
  return stmt unless block_given?

  begin
    yield stmt
  ensure
    stmt.close unless stmt.closed?
  end
end

#query(sql, bind_vars = []) ⇒ Object

This is a convenience method for creating a statement, binding parameters to it, and calling execute:

result = db.query( "select * from foo where a=?", [5])
# is the same as
result = db.prepare( "select * from foo where a=?" ).execute( 5 )

You must be sure to call close on the ResultSet instance that is returned, or you could have problems with locks on the table. If called with a block, close will be invoked implicitly when the block terminates.



352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'lib/sqlite3/database.rb', line 352

def query(sql, bind_vars = [])
  result = prepare(sql).execute(bind_vars)
  if block_given?
    begin
      yield result
    ensure
      result.close
    end
  else
    result
  end
end

#readonly? ⇒ Boolean

Returns true if the database has been open in readonly mode A helper to check before performing any operation

Returns:

  • (Boolean) 


685
686
687
 
# File 'lib/sqlite3/database.rb', line 685

def readonly?
  @readonly
end

#rollback ⇒ Object

Rolls the current transaction back. If there is no current transaction, this will cause an error to be raised. This returns true, in order to allow it to be used in idioms like abort? and rollback or commit.



678
679
680
681
 
# File 'lib/sqlite3/database.rb', line 678

def rollback
  execute "rollback transaction"
  true
end

#statement_timeout=(milliseconds) ⇒ Object 



38
39
40
41
42
43
 
# File 'lib/sqlite3/ffi/database.rb', line 38

def statement_timeout=(milliseconds)
  @stmt_timeout = milliseconds.to_i
  n = milliseconds.to_i == 0 ? -1 : 1000
  FFI::CApi.sqlite3_progress_handler(@db, n, FFI::STATEMENT_TIMEOUT, FFI.wrap(self))
  self
end

#total_changes ⇒ Object 



13
14
15
16
17
 
# File 'lib/sqlite3/ffi/database.rb', line 13

def total_changes
  require_open_db

  FFI::CApi.sqlite3_total_changes(@db)
end

#trace(blk = nil, &block) ⇒ Object 



19
20
21
22
23
24
25
26
 
# File 'lib/sqlite3/ffi/database.rb', line 19

def trace(blk = nil, &block)
  require_open_db

  blk ||= block
  @tracefunc = blk
  FFI::CApi.sqlite3_trace_v2(@db, FFI::CApi::SQLITE_TRACE_STMT, blk.nil? ? nil : FFI::TRACE, FFI.wrap(blk))
  self
end

#transaction(mode = nil) ⇒ Object

Begins a new transaction. Note that nested transactions are not allowed by SQLite, so attempting to nest a transaction will result in a runtime exception.

The mode parameter may be either :deferred, :immediate, or :exclusive. If nil is specified, the default transaction mode, which was passed to #initialize, is used.

If a block is given, the database instance is yielded to it, and the transaction is committed when the block terminates. If the block raises an exception, a rollback will be performed instead. Note that if a block is given, #commit and #rollback should never be called explicitly or you’ll get an error when the block terminates.

If a block is not given, it is the caller’s responsibility to end the transaction explicitly, either by calling #commit, or by calling #rollback.



646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
 
# File 'lib/sqlite3/database.rb', line 646

def transaction(mode = nil)
  mode = @default_transaction_mode if mode.nil?
  execute "begin #{mode} transaction"

  if block_given?
    abort = false
    begin
      yield self
    rescue
      abort = true
      raise
    ensure
      abort and rollback or commit
    end
  else
    true
  end
end

#transaction_active? ⇒ Boolean

Returns:

  • (Boolean) 


139
140
141
142
143
 
# File 'lib/sqlite3/ffi/database.rb', line 139

def transaction_active?
  require_open_db

  FFI::CApi.sqlite3_get_autocommit(@db).zero?
end