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.



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

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.



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

def api
  @api
end

#db ⇒ Object (readonly)

Returns the value of attribute db.



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

def db
  @db
end

Class Method Details

.rowid_column_names ⇒ Object

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



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

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



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

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.



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

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



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

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



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

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.



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

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



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

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.



422
423
424
425
426
427
 
# File 'lib/amalgalite/statement.rb', line 422

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



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

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.



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

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.



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

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:



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

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



259
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
 
# File 'lib/amalgalite/statement.rb', line 259

def next_row
  row = nil
  case rc = @stmt_api.step
  when ResultCode::ROW
    row = ::Amalgalite::Result::Row.new(field_map: result_field_map, values: Array.new(result_meta.size))
    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.db,
                                                                       col.table,
                                                                       col.name,
                                                                       @stmt_api.column_int64( @rowid_index ),
                                                                       "r"),
                                        :column => col)
        else
          value = Amalgalite::Blob.new( :string => @stmt_api.column_blob( idx ), :column => col )
        end
      else
        raise ::Amalgalite::Error, "BUG! : Unknown SQLite column type of #{column_type}"
      end

      row.store_by_index(idx, db.type_map.result_value_of( col.normalized_declared_data_type, value ))
    end
  when ResultCode::DONE
    write_blobs
  else
    self.close # must close so that the error message is guaranteed to be pushed into the database handler
               # and we 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:



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

def open?
  @open
end

#param_position_of(name) ⇒ Object

Find and cache the binding parameter indexes



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

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



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

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.



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

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.



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

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



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

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

#result_field_map ⇒ Object 



387
388
389
390
391
392
393
394
 
# File 'lib/amalgalite/statement.rb', line 387

def result_field_map
  @result_field_map ||= {}.tap do |map|
    result_meta.each do |column|
      map[column.as_name.to_s] = column.order
      map[column.as_name.to_sym] = column.order
    end
  end
end

#result_fields ⇒ Object

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



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

def result_fields
  @fields ||= result_meta.collect { |m| m.as_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 information happens, record if the special “ROWID”, “OID”, or “ROWID” column is encountered. If that column is encountered then we make note of it.

This method cannot be called until after the @stmt_api has returne from ‘step` at least once



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
 
# File 'lib/amalgalite/statement.rb', line 334

def result_meta
  unless @result_meta
    meta = []
    column_count.times do |idx|
      as_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 = ::Amalgalite::Column.new( db_name, tbl_name, col_name, idx, as_name )
      column_meta.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



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

def sql
  @stmt_api.sql
end

#using_rowid_column? ⇒ Boolean

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

Returns:



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

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



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

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