Class: Amalgalite::Statement

Inherits:
Object
  • Object
show all
Includes:
Amalgalite::SQLite3::Constants
Defined in:
lib/amalgalite/statement.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db, sql) ⇒ Statement

Initialize a new statement on the database.



28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/amalgalite/statement.rb', line 28

def initialize( db, sql )
  @db = db
  #prepare_method   =  @db.utf16? ? :prepare16 : :prepare
  prepare_method   =  :prepare
  @param_positions = {}
  @stmt_api        = @db.api.send( prepare_method, sql )
  @blobs_to_write  = []
  @rowid_index     = nil
  @result_meta     = nil
  @open            = true
end

Instance Attribute Details

#api ⇒ Object (readonly)

Returns the value of attribute api.



16
17
18
 
# File 'lib/amalgalite/statement.rb', line 16

def api
  @api
end

#db ⇒ Object (readonly)

Returns the value of attribute db.



15
16
17
 
# File 'lib/amalgalite/statement.rb', line 15

def db
  @db
end

Class Method Details

.rowid_column_names ⇒ Object

special column names that indicate that indicate the column is a rowid



20
21
22
 
# File 'lib/amalgalite/statement.rb', line 20

def rowid_column_names
  @rowid_column_names ||= %w[ ROWID OID _ROWID_ ]
end

Instance Method Details

#all_rows ⇒ Object

Return all rows from the statement as one array



312
313
314
315
316
317
318
 
# File 'lib/amalgalite/statement.rb', line 312

def all_rows
  rows = []
  while row = next_row
    rows << row
  end
  return rows
end

#bind(*params) ⇒ Object

Bind parameters to the sql statement.

Bindings in SQLite can have a number of formats:

?
?num
:var
@var
$var

Where ‘num’ is an Integer and ‘var’is an alphanumerical variable. They may exist in the SQL for which this Statement was created.

Amalgalite binds parameters to these variables in the following manner:

If bind is passed in an Array, either as bind( “foo”, “bar”, “baz”) or as bind( [“foo”, “bar”, “baz”] ) then each of the params is assumed to be positionally bound to the statement( ?, ?num ).

If bind is passed a Hash, either as bind( :foo => 1, :bar => ‘sqlite’ ) or as bind( { :foo => 1, ‘bar’ => ‘sqlite’ }) then it is assumed that each parameter should be bound as a named parameter (:var, @var, $var).

If bind is not passed any parameters, or nil, then nothing happens.



141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/amalgalite/statement.rb', line 141

def bind( *params )
  if params.nil? or params.empty? then
    check_parameter_count!( 0 )
    return nil 
  end

  if params.first.instance_of?( Hash ) then
    bind_named_parameters( params.first )
  elsif params.first.instance_of?( Array ) then
    bind_positional_parameters( *params )
  else
    bind_positional_parameters( params )
  end
end

#bind_named_parameters(params) ⇒ Object

Bind parameters to the statement based upon named parameters



159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/amalgalite/statement.rb', line 159

def bind_named_parameters( params )
  check_parameter_count!( params.size )
  params.each_pair do | param, value |
    position = param_position_of( param )
    if position > 0 then
      bind_parameter_to( position, value )
    else
      raise Amalgalite::Error, "Unable to find parameter '#{param}' in SQL statement [#{sql}]"
    end
  end
end

#bind_parameter_to(position, value) ⇒ Object

bind a single parameter to a particular position



185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/amalgalite/statement.rb', line 185

def bind_parameter_to( position, value )
  bind_type = db.type_map.bind_type_of( value ) 
  case bind_type
  when DataType::FLOAT
    @stmt_api.bind_double( position, value )
  when DataType::INTEGER
    @stmt_api.bind_int64( position, value )
  when DataType::NULL
    @stmt_api.bind_null( position )
  when DataType::TEXT
    @stmt_api.bind_text( position, value.to_s )
  when DataType::BLOB
    if value.incremental? then
      @stmt_api.bind_zeroblob( position, value.length )
      @blobs_to_write << value
    else
      @stmt_api.bind_blob( position, value.source )
    end
  else
    raise ::Amalgalite::Error, "Unknown binding type of #{bind_type} from #{db.type_map.class.name}.bind_type_of"
  end
end

#bind_positional_parameters(params) ⇒ Object

Bind parameters to the statements based upon positions.



174
175
176
177
178
179
180
 
# File 'lib/amalgalite/statement.rb', line 174

def bind_positional_parameters( params )
  check_parameter_count!( params.size )
  params.each_with_index do |value, index|
    position = index + 1
    bind_parameter_to( position, value )
  end
end

#check_parameter_count!(num) ⇒ Object

Check and make sure that the number of parameters aligns with the number that sqlite expects



224
225
226
227
228
229
230
 
# File 'lib/amalgalite/statement.rb', line 224

def check_parameter_count!( num )
  expected = @stmt_api.parameter_count
  if num != expected then 
    raise Amalgalite::Error, "#{sql} has #{expected} parameters, but #{num} were passed to bind."
  end
  return expected
end

#close ⇒ Object

Close the statement. The statement is no longer valid for use after it has been closed.



414
415
416
417
418
419
 
# File 'lib/amalgalite/statement.rb', line 414

def close
  if open? then
    @stmt_api.close
    @open = false
  end
end

#column_count ⇒ Object

return the number of columns in the result of this query



399
400
401
 
# File 'lib/amalgalite/statement.rb', line 399

def column_count
  @stmt_api.column_count
end

#each ⇒ Object

Iterate over the results of the statement returning each row of results as a hash by column_name. The column names are the value after an ‘AS’ in the query or default chosen by sqlite.



249
250
251
252
253
254
 
# File 'lib/amalgalite/statement.rb', line 249

def each
  while row = next_row
    yield row
  end
  return self
end

#execute(*params) ⇒ Object

Execute the statement with the given parameters

If a block is given, then yield each returned row to the block. If no block is given then return all rows from the result. No matter what the prepared statement should be reset before returning the final time.



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/amalgalite/statement.rb', line 91

def execute( *params )
  bind( *params )
  begin
    # save the error state at the beginning of the execution.  We only want to
    # reraise the error if it was raised during this execution.
    s_before = $!
    if block_given? then
      while row = next_row
        yield row
      end
    else
      all_rows
    end
  ensure
    s = $!
    begin
      reset_for_next_execute!
    rescue
      # rescuing nothing on purpose
    end
    raise s if s != s_before
  end
end

#is_column_rowid?(table_name, column_name) ⇒ Boolean

is the column indicated by the Column a ‘rowid’ column

Returns:



365
366
367
368
369
370
371
372
373
374
375
376
377
378
 
# File 'lib/amalgalite/statement.rb', line 365

def is_column_rowid?( table_name, column_name )
  table_schema = @db.schema.tables[table_name]
  return false unless table_schema

  column_schema  = table_schema.columns[column_name]
  if column_schema then
    if column_schema.primary_key? and column_schema.declared_data_type and column_schema.declared_data_type.upcase == "INTEGER" then
      return true
    end
  else
    return true if Statement.rowid_column_names.include?( column_name.upcase )
  end
  return false
end

#next_row ⇒ Object

Return the next row of data, with type conversion as indicated by the Database#type_map



260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/amalgalite/statement.rb', line 260

def next_row
  row = []
  case rc = @stmt_api.step
  when ResultCode::ROW
    result_meta.each_with_index do |col, idx|
      value = nil
      column_type = @stmt_api.column_type( idx )
      case column_type
      when DataType::TEXT
        value = @stmt_api.column_text( idx )
      when DataType::FLOAT
        value = @stmt_api.column_double( idx )
      when DataType::INTEGER
        value = @stmt_api.column_int64( idx )
      when DataType::NULL
        value = nil
      when DataType::BLOB
        # if the rowid column is encountered, then we can use an incremental
        # blob api, otherwise we have to use the all at once version.
        if using_rowid_column? then
          value = Amalgalite::Blob.new( :db_blob => SQLite3::Blob.new( db.api,
                                                                       col.schema.db,
                                                                       col.schema.table,
                                                                       col.schema.name,
                                                                       @stmt_api.column_int64( @rowid_index ),
                                                                       "r"),
                                        :column => col.schema)
        else
          value = Amalgalite::Blob.new( :string => @stmt_api.column_blob( idx ), :column => col.schema )
        end
      else
        raise ::Amalgalite::Error, "BUG! : Unknown SQLite column type of #{column_type}"
      end

      row << db.type_map.result_value_of( col.schema.declared_data_type, value )
    end
    row.fields = result_fields
  when ResultCode::DONE
    row = nil
    write_blobs
  else
    self.close # must close so that the error message is guaranteed to be pushed into the database handler
               # and we can can call last_error_message on it
    msg = "SQLITE ERROR #{rc} (#{Amalgalite::SQLite3::Constants::ResultCode.name_from_value( rc )}) : #{@db.api.last_error_message}"
    raise Amalgalite::SQLite3::Error, msg
  end
  return row
end

#open? ⇒ Boolean

is the statement open for business

Returns:



43
44
45
 
# File 'lib/amalgalite/statement.rb', line 43

def open?
  @open
end

#param_position_of(name) ⇒ Object

Find and cache the binding parameter indexes



212
213
214
215
216
217
218
 
# File 'lib/amalgalite/statement.rb', line 212

def param_position_of( name )
  ns = name.to_s
  unless pos = @param_positions[ns] 
    pos = @param_positions[ns] = @stmt_api.parameter_index( ns )
  end
  return pos
end

#remaining_sql ⇒ Object

Return any unsued SQL from the statement



391
392
393
 
# File 'lib/amalgalite/statement.rb', line 391

def remaining_sql
  @stmt_api.remaining_sql
end

#reset! ⇒ Object

reset the Statement back to it state right after the constructor returned, except if any variables have been bound to parameters, those are still bound.



59
60
61
62
63
64
 
# File 'lib/amalgalite/statement.rb', line 59

def reset!
  @stmt_api.reset!
  @param_positions = {}
  @blobs_to_write.clear
  @rowid_index = nil
end

#reset_and_clear_bindings! ⇒ Object

reset the Statement back to it state right after the constructor returned, AND clear all parameter bindings.



70
71
72
73
 
# File 'lib/amalgalite/statement.rb', line 70

def reset_and_clear_bindings!
  reset!
  @stmt_api.clear_bindings!
end

#reset_for_next_execute! ⇒ Object

reset the statment in preparation for executing it again



78
79
80
81
82
 
# File 'lib/amalgalite/statement.rb', line 78

def reset_for_next_execute!
  @stmt_api.reset!
  @stmt_api.clear_bindings!
  @blobs_to_write.clear
end

#result_fields ⇒ Object

Return the array of field names for the result set, the field names are all strings



384
385
386
 
# File 'lib/amalgalite/statement.rb', line 384

def result_fields
  @fields ||= result_meta.collect { |m| m.name }
end

#result_meta ⇒ Object

Inspect the statement and gather all the meta information about the results, include the name of the column result column and the origin column. The origin column is the original database.table.column the value comes from.

The full meta information from the origin column is also obtained for help in doing type conversion.

As iteration over the row meta informatio happens, record if the special “ROWID”, “OID”, or “ROWID” column is encountered. If that column is encountered then we make note of it.



333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
 
# File 'lib/amalgalite/statement.rb', line 333

def result_meta
  unless @result_meta
    meta = []
    column_count.times do |idx|
      column_meta = ::OpenStruct.new
      column_meta.name = @stmt_api.column_name( idx )

      db_name  = @stmt_api.column_database_name( idx ) 
      tbl_name = @stmt_api.column_table_name( idx ) 
      col_name = @stmt_api.column_origin_name( idx ) 

      column_meta.schema = ::Amalgalite::Column.new( db_name, tbl_name, col_name, idx )
      column_meta.schema.declared_data_type = @stmt_api.column_declared_type( idx )

      # only check for rowid if we have a table name and it is not one of the
      # sqlite_master tables.  We could get recursion in those cases.
      if not using_rowid_column? and tbl_name and
         not %w[ sqlite_master sqlite_temp_master ].include?( tbl_name ) and is_column_rowid?( tbl_name, col_name ) then
        @rowid_index = idx
      end

      meta << column_meta 
    end

    @result_meta = meta
  end
  return @result_meta
end

#sql ⇒ Object

return the raw sql that was originally used to prepare the statement



406
407
408
 
# File 'lib/amalgalite/statement.rb', line 406

def sql
  @stmt_api.sql
end

#using_rowid_column? ⇒ Boolean

Is the special column “ROWID”, “OID”, or “ROWID” used?

Returns:



50
51
52
 
# File 'lib/amalgalite/statement.rb', line 50

def using_rowid_column?
  not @rowid_index.nil?
end

#write_blobs ⇒ Object

Write any blobs that have been bound to parameters to the database. This assumes that the blobs go into the last inserted row



236
237
238
239
240
241
242
 
# File 'lib/amalgalite/statement.rb', line 236

def write_blobs
  unless @blobs_to_write.empty?
    @blobs_to_write.each do |blob|
      blob.write_to_column!
    end
  end
end