lib/sqlite3/database.rb in sqlite3-0.1.1 vs lib/sqlite3/database.rb in sqlite3-1.3.3.beta.1
- old
+ new
@@ -1,52 +1,25 @@
-# Copyright (c) 2004, Jamis Buck (jamis@jamisbuck.org)
-# All rights reserved.
+require 'sqlite3/constants'
+require 'sqlite3/errors'
+require 'sqlite3/pragmas'
+require 'sqlite3/statement'
+require 'sqlite3/translator'
+require 'sqlite3/value'
-# Redistribution and use in source and binary forms, with or without
-# modification, are permitted provided that the following conditions
-# are met:
-
-# * Redistributions of source code must retain the above copyright
-# notice, this list of conditions and the following disclaimer.
-
-# * Redistributions in binary form must reproduce the above copyright
-# notice, this list of conditions and the following disclaimer in
-# the documentation and/or other materials provided with the
-# distribution.
-
-# * The names of its contributors may not be used to endorse or
-# promote products derived from this software without specific prior
-# written permission.
-
-# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
-# FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-# COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
-# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
-# CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
-# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
-# ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
-# POSSIBILITY OF SUCH DAMAGE.
-
module SQLite3
# The Database class encapsulates a single connection to a SQLite3 database.
# Its usage is very straightforward:
#
- # require "sqlite3"
+ # require 'sqlite3'
#
- # db = SQLite3::Database.new("data.db")
- #
- # db.execute("select * from table") do |row|
- # p row
+ # SQLite3::Database.new( "data.db" ) do |db|
+ # db.execute( "select * from table" ) do |row|
+ # p row
+ # end
# end
#
- # db.close
- #
# It wraps the lower-level methods provides 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
@@ -58,105 +31,72 @@
# 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.
class Database
+ attr_reader :collations
+
include Pragmas
- include Extensions
class << self
alias :open :new
# 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.
- def quote(string)
- string.gsub(/'/, "''")
+ def quote( string )
+ string.gsub( /'/, "''" )
end
end
- # The low-level opaque database handle that this object wraps.
- attr_reader :handle
-
- # A reference to the underlying SQLite3 driver used by this database.
- attr_reader :driver
-
# A boolean that indicates whether rows in result sets should be returned
# as hashes or not. By default, rows are returned as arrays.
attr_accessor :results_as_hash
- # Encoding used to comunicate with database.
- attr_reader :encoding
-
- # Create a new Database object that opens the given file. If utf16
- # is +true+, the filename is interpreted as a UTF-16 encoded string.
- #
- # By default, the new database will return result rows as arrays
- # (#results_as_hash) and has type translation disabled (#type_translation=).
- def initialize(file_name, options = {})
- @encoding = Encoding.find(options.fetch(:encoding, "utf-8"))
-
- @driver = Driver.new
-
- @statement_factory = options[:statement_factory] || Statement
-
- result, @handle = @driver.open(file_name, Encoding.utf_16?(@encoding))
- Error.check(result, self, "could not open database")
-
- @closed = false
- @results_as_hash = options.fetch(:results_as_hash, false)
+ def type_translation= value # :nodoc:
+ warn(<<-eowarn) if $VERBOSE
+#{caller[0]} is calling SQLite3::Database#type_translation=
+SQLite3::Database#type_translation= is deprecated and will be removed
+in version 2.0.0.
+ eowarn
+ @type_translation = value
end
+ attr_reader :type_translation # :nodoc:
- # Return +true+ if the string is a valid (ie, parsable) SQL statement, and
- # +false+ otherwise
- def complete?(string)
- @driver.complete?(string)
+ # Return the type translator employed by this database instance. Each
+ # database instance has its own type translator; this allows for different
+ # type handlers to be installed in each instance without affecting other
+ # instances. Furthermore, the translators are instantiated lazily, so that
+ # if a database does not use type translation, it will not be burdened by
+ # the overhead of a useless type translator. (See the Translator class.)
+ def translator
+ @translator ||= Translator.new
end
- # Return a string describing the last error to have occurred with this
- # database.
- def errmsg
- @driver.errmsg(@handle)
+ # 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.
+ def authorizer( &block )
+ self.authorizer = block
end
- # Return an integer representing the last error to have occurred with this
- # database.
- def errcode
- @driver.errcode(@handle)
- end
-
- # Closes this database.
- def close
- unless @closed
- result = @driver.close(@handle)
- Error.check(result, self)
- end
- @closed = true
- end
-
- # Returns +true+ if this database instance has been closed (see #close).
- def closed?
- @closed
- end
-
# 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.
#
- def prepare(sql)
- stmt = @statement_factory.new(self, sql, Encoding.utf_16?(@encoding))
- if block_given?
- begin
- yield stmt
- ensure
- stmt.close
- end
- else
- return stmt
+ def prepare sql
+ stmt = SQLite3::Statement.new( self, sql )
+ return stmt unless block_given?
+
+ begin
+ yield stmt
+ ensure
+ stmt.close
end
end
# Executes the given SQL statement. If additional parameters are given,
# they are treated as bind variables, and are bound to the placeholders in
@@ -170,17 +110,55 @@
# 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.
- def execute(sql, *bind_vars)
- prepare(sql) do |stmt|
- result = stmt.execute(*bind_vars)
+ def execute sql, bind_vars = [], *args, &block
+ # FIXME: This is a terrible hack and should be removed but is required
+ # for older versions of rails
+ hack = Object.const_defined?(:ActiveRecord) && sql =~ /^PRAGMA index_list/
+
+ if bind_vars.nil? || !args.empty?
+ if args.empty?
+ bind_vars = []
+ else
+ bind_vars = [bind_vars] + args
+ end
+
+ warn(<<-eowarn) if $VERBOSE
+#{caller[0]} is calling SQLite3::Database#execute with nil or multiple bind params
+without using an array. Please switch to passing bind parameters as an array.
+Support for bind parameters as *args will be removed in 2.0.0.
+ eowarn
+ end
+
+ prepare( sql ) do |stmt|
+ stmt.bind_params(bind_vars)
+ columns = stmt.columns
+ stmt = ResultSet.new(self, stmt).to_a if type_translation
+
if block_given?
- result.each { |row| yield row }
+ stmt.each do |row|
+ if @results_as_hash
+ yield type_translation ? row : ordered_map_for(columns, row)
+ else
+ yield row
+ end
+ end
else
- return result.inject([]) { |arr, row| arr << row; arr }
+ if @results_as_hash
+ stmt.map { |row|
+ h = type_translation ? row : ordered_map_for(columns, row)
+
+ # FIXME UGH TERRIBLE HACK!
+ h['unique'] = h['unique'].to_s if hack
+
+ h
+ }
+ else
+ stmt.to_a
+ end
end
end
end
# Executes the given SQL statement, exactly as with #execute. However, the
@@ -191,64 +169,301 @@
# 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.
- def execute2(sql, *bind_vars)
- prepare(sql) do |stmt|
- result = stmt.execute(*bind_vars)
+ def execute2( sql, *bind_vars )
+ prepare( sql ) do |stmt|
+ result = stmt.execute( *bind_vars )
if block_given?
- yield result.columns
+ yield stmt.columns
result.each { |row| yield row }
else
- return result.inject([result.columns]) { |arr,row| arr << row; arr }
+ return result.inject( [ stmt.columns ] ) { |arr,row|
+ arr << row; arr }
end
end
end
+ # 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 +nil+, making it unsuitable for queries that return
+ # rows.
+ def execute_batch( sql, bind_vars = [], *args )
+ # FIXME: remove this stuff later
+ unless [Array, Hash].include?(bind_vars.class)
+ bind_vars = [bind_vars]
+ warn(<<-eowarn) if $VERBOSE
+#{caller[0]} is calling SQLite3::Database#execute_batch with bind parameters
+that are not a list of a hash. Please switch to passing bind parameters as an
+array or hash. Support for this behavior will be removed in version 2.0.0.
+ eowarn
+ end
+
+ # FIXME: remove this stuff later
+ if bind_vars.nil? || !args.empty?
+ if args.empty?
+ bind_vars = []
+ else
+ bind_vars = [nil] + args
+ end
+
+ warn(<<-eowarn) if $VERBOSE
+#{caller[0]} is calling SQLite3::Database#execute_batch with nil or multiple bind params
+without using an array. Please switch to passing bind parameters as an array.
+Support for this behavior will be removed in version 2.0.0.
+ eowarn
+ end
+
+ sql = sql.strip
+ until sql.empty? do
+ prepare( sql ) do |stmt|
+ # 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
+ stmt.step
+ sql = stmt.remainder.strip
+ end
+ end
+ nil
+ end
+
+ # This is a convenience method for creating a statement, binding
+ # paramters 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.
+ def query( sql, bind_vars = [], *args )
+
+ if bind_vars.nil? || !args.empty?
+ if args.empty?
+ bind_vars = []
+ else
+ bind_vars = [nil] + args
+ end
+
+ warn(<<-eowarn) if $VERBOSE
+#{caller[0]} is calling SQLite3::Database#query with nil or multiple bind params
+without using an array. Please switch to passing bind parameters as an array.
+Support for this will be removed in version 2.0.0.
+ eowarn
+ end
+
+ result = prepare( sql ).execute( bind_vars )
+ if block_given?
+ begin
+ yield result
+ ensure
+ result.close
+ end
+ else
+ return result
+ end
+ end
+
# 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.
- def get_first_row(sql, *bind_vars)
- execute(sql, *bind_vars) { |row| return row }
- nil
+ def get_first_row( sql, *bind_vars )
+ execute( sql, *bind_vars ).first
end
# 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.
- def get_first_value(sql, *bind_vars)
- execute(sql, *bind_vars) { |row| return row[0] }
+ def get_first_value( sql, *bind_vars )
+ execute( sql, *bind_vars ) { |row| return row[0] }
nil
end
- # Obtains the unique row ID of the last row to be inserted by this Database
- # instance.
- def last_insert_row_id
- @driver.last_insert_rowid(@handle)
+ alias :busy_timeout :busy_timeout=
+
+ # 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" )
+ def create_function name, arity, text_rep=Constants::TextRep::ANY, &block
+ define_function(name) do |*args|
+ fp = FunctionProxy.new
+ block.call(fp, *args)
+ fp.result
+ end
+ self
end
- # Returns the number of changes made to this database instance by the last
- # operation performed. Note that a "delete from table" without a where
- # clause will not affect this value.
- def changes
- @driver.changes(@handle)
+ # 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.
+ def create_aggregate( name, arity, step=nil, finalize=nil,
+ text_rep=Constants::TextRep::ANY, &block )
+
+ factory = Class.new do
+ def self.step( &block )
+ define_method(:step, &block)
+ end
+
+ def self.finalize( &block )
+ define_method(:finalize, &block)
+ end
+ end
+
+ if block_given?
+ factory.instance_eval(&block)
+ else
+ factory.class_eval do
+ define_method(:step, step)
+ define_method(:finalize, finalize)
+ end
+ end
+
+ proxy = factory.new
+ proxy.extend(Module.new {
+ attr_accessor :ctx
+
+ def step( *args )
+ super(@ctx, *args)
+ end
+
+ def finalize
+ super(@ctx)
+ end
+ })
+ proxy.ctx = FunctionProxy.new
+ define_aggregator(name, proxy)
end
- # Indicates that if a request for a resource terminates because that
- # resource is busy, SQLite should sleep and retry for up to the indicated
- # number of milliseconds. By default, SQLite does not retry
- # busy resources. To restore the default behavior, send 0 as the
- # +ms+ parameter.
+ # 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:
#
- # See also the mutually exclusive #busy_handler.
- def busy_timeout(ms)
- result = @driver.busy_timeout(@handle, ms)
- Error.check(result, self)
+ # +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 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" )
+ def create_aggregate_handler( handler )
+ proxy = Class.new do
+ def initialize handler
+ @handler = handler
+ @fp = FunctionProxy.new
+ end
+
+ def step( *args )
+ @handler.step(@fp, *args)
+ end
+
+ def finalize
+ @handler.finalize @fp
+ @fp.result
+ end
+ end
+ define_aggregator(handler.name, proxy.new(handler.new))
+ self
end
# 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.
@@ -263,11 +478,11 @@
# 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.
- def transaction(mode = :deferred)
+ def transaction( mode = :deferred )
execute "begin #{mode.to_s} transaction"
@transaction_active = true
if block_given?
abort = false
@@ -305,8 +520,68 @@
end
# Returns +true+ if there is a transaction active, and +false+ otherwise.
def transaction_active?
@transaction_active
+ end
+
+ # Returns +true+ if the database has been open in readonly mode
+ # A helper to check before performing any operation
+ def readonly?
+ @readonly
+ end
+
+ # A helper class for dealing with custom functions (see #create_function,
+ # #create_aggregate, and #create_aggregate_handler). It encapsulates the
+ # opaque function object that represents the current invocation. It also
+ # provides more convenient access to the API functions that operate on
+ # the function object.
+ #
+ # This class will almost _always_ be instantiated indirectly, by working
+ # with the create methods mentioned above.
+ class FunctionProxy
+ attr_accessor :result
+
+ # Create a new FunctionProxy that encapsulates the given +func+ object.
+ # If context is non-nil, the functions context will be set to that. If
+ # it is non-nil, it must quack like a Hash. If it is nil, then none of
+ # the context functions will be available.
+ def initialize
+ @result = nil
+ @context = {}
+ end
+
+ # Set the result of the function to the given error message.
+ # The function will then return that error.
+ def set_error( error )
+ @driver.result_error( @func, error.to_s, -1 )
+ end
+
+ # (Only available to aggregate functions.) Returns the number of rows
+ # that the aggregate has processed so far. This will include the current
+ # row, and so will always return at least 1.
+ def count
+ @driver.aggregate_count( @func )
+ end
+
+ # Returns the value with the given key from the context. This is only
+ # available to aggregate functions.
+ def []( key )
+ @context[ key ]
+ end
+
+ # Sets the value with the given key in the context. This is only
+ # available to aggregate functions.
+ def []=( key, value )
+ @context[ key ] = value
+ end
+ end
+
+ private
+
+ def ordered_map_for columns, row
+ h = Hash[*columns.zip(row).flatten]
+ row.each_with_index { |r, i| h[i] = r }
+ h
end
end
end