| Class | Amalgalite::Statement |
| In: |
lib/amalgalite/statement.rb
|
| Parent: | Object |
| api | [R] | |
| db | [R] | |
| sql | [R] |
Initialize a new statement on the database.
# File lib/amalgalite/statement.rb, line 30
30: def initialize( db, sql )
31: @db = db
32: prepare_method = @db.utf16? ? :prepare16 : :prepare
33: @param_positions = {}
34: @stmt_api = @db.api.send( prepare_method, sql )
35: @blobs_to_write = []
36: @rowid_index = nil
37: end
special column names that indicate that indicate the column is a rowid
# File lib/amalgalite/statement.rb, line 22
22: def rowid_column_names
23: @rowid_column_names ||= %w[ ROWID OID _ROWID_ ]
24: end
Return all rows from the statement as one array
# File lib/amalgalite/statement.rb, line 296
296: def all_rows
297: rows = []
298: while row = next_row
299: rows << row
300: end
301: return rows
302: end
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.
# File lib/amalgalite/statement.rb, line 129
129: def bind( *params )
130: if params.nil? or params.empty? then
131: check_parameter_count!( 0 )
132: return nil
133: end
134:
135: if params.first.instance_of?( Hash ) then
136: bind_named_parameters( params.first )
137: else
138: bind_positional_parameters( params )
139: end
140: end
Bind parameters to the statement based upon named parameters
# File lib/amalgalite/statement.rb, line 145
145: def bind_named_parameters( params )
146: check_parameter_count!( params.size )
147: params.each_pair do | param, value |
148: position = param_position_of( param )
149: if position > 0 then
150: bind_parameter_to( position, value )
151: else
152: raise Amalgalite::Error, "Unable to find parameter '#{param}' in SQL statement [#{sql}]"
153: end
154: end
155: end
bind a single parameter to a particular position
# File lib/amalgalite/statement.rb, line 171
171: def bind_parameter_to( position, value )
172: bind_type = db.type_map.bind_type_of( value )
173: case bind_type
174: when DataType::FLOAT
175: @stmt_api.bind_double( position, value )
176: when DataType::INTEGER
177: @stmt_api.bind_int64( position, value )
178: when DataType::NULL
179: @stmt_api.bind_null( position )
180: when DataType::TEXT
181: @stmt_api.bind_text( position, value.to_s )
182: when DataType::BLOB
183: if value.incremental? then
184: @stmt_api.bind_zeroblob( position, value.length )
185: @blobs_to_write << value
186: else
187: @stmt_api.bind_blob( position, value.source )
188: end
189: else
190: raise ::Amalgalite::Error, "Unknown binding type of #{bind_type} from #{db.type_map.class.name}.bind_type_of"
191: end
192: end
Bind parameters to the statements based upon positions.
# File lib/amalgalite/statement.rb, line 160
160: def bind_positional_parameters( params )
161: check_parameter_count!( params.size )
162: params.each_with_index do |value, index|
163: position = index + 1
164: bind_parameter_to( position, value )
165: end
166: end
Check and make sure that the number of parameters aligns with the number that sqlite expects
# File lib/amalgalite/statement.rb, line 210
210: def check_parameter_count!( num )
211: expected = @stmt_api.parameter_count
212: if num != expected then
213: raise Amalgalite::Error, "#{sql} has #{expected} parameters, but #{num} were passed to bind."
214: end
215: return expected
216: end
Close the statement. The statement is no longer valid for use after it has been closed.
# File lib/amalgalite/statement.rb, line 390
390: def close
391: @stmt_api.close
392: end
return the number of columns in the result of this query
# File lib/amalgalite/statement.rb, line 375
375: def column_count
376: @stmt_api.column_count
377: end
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.
# File lib/amalgalite/statement.rb, line 235
235: def each
236: while row = next_row
237: yield row
238: end
239: return self
240: end
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.
# File lib/amalgalite/statement.rb, line 83
83: def execute( *params )
84: bind( *params )
85: begin
86: if block_given? then
87: while row = next_row
88: yield row
89: end
90: else
91: all_rows
92: end
93: ensure
94: s = $!
95: begin
96: reset_for_next_execute!
97: rescue => e
98: end
99: raise s if s
100: end
101: end
is the column indicated by the Column a ‘rowid’ column
# File lib/amalgalite/statement.rb, line 348
348: def is_column_rowid?( table_name, column_name )
349: column_schema = @db.schema.tables[table_name].columns[column_name]
350: if column_schema.primary_key? and column_schema.declared_data_type and column_schema.declared_data_type.upcase == "INTEGER" then
351: return true
352: end
353: return false
354: end
Return the next row of data, with type conversion as indicated by the Database#type_map
# File lib/amalgalite/statement.rb, line 246
246: def next_row
247: row = []
248: case rc = @stmt_api.step
249: when ResultCode::ROW
250: result_meta.each_with_index do |col, idx|
251: value = nil
252: column_type = @stmt_api.column_type( idx )
253: case column_type
254: when DataType::TEXT
255: value = @stmt_api.column_text( idx )
256: when DataType::FLOAT
257: value = @stmt_api.column_double( idx )
258: when DataType::INTEGER
259: value = @stmt_api.column_int64( idx )
260: when DataType::NULL
261: value = nil
262: when DataType::BLOB
263: # if the rowid column is encountered, then we can use an incremental
264: # blob api, otherwise we have to use the all at once version.
265: if using_rowid_column? then
266: value = Amalgalite::Blob.new( :db_blob => SQLite3::Blob.new( db.api,
267: col.schema.db,
268: col.schema.table,
269: col.schema.name,
270: @stmt_api.column_int64( @rowid_index ),
271: "r"),
272: :column => col.schema)
273: else
274: value = Amalgalite::Blob.new( :string => @stmt_api.column_blob( idx ), :column => col.schema )
275: end
276: else
277: raise ::Amalgalite::Error, "BUG! : Unknown SQLite column type of #{column_type}"
278: end
279:
280: row << db.type_map.result_value_of( col.schema.declared_data_type, value )
281: end
282: row.fields = result_fields
283: when ResultCode::DONE
284: row = nil
285: write_blobs
286: else
287: raise Amalgalite::SQLite3::Error,
288: "SQLITE ERROR #{rc} (#{Amalgalite::SQLite3::Constants::ResultCode.from_int( rc )}) : #{@db.api.last_error_message}"
289: end
290: return row
291: end
Find and cache the binding parameter indexes
# File lib/amalgalite/statement.rb, line 198
198: def param_position_of( name )
199: ns = name.to_s
200: unless pos = @param_positions[ns]
201: pos = @param_positions[ns] = @stmt_api.parameter_index( ns )
202: end
203: return pos
204: end
Return any unsued SQL from the statement
# File lib/amalgalite/statement.rb, line 367
367: def remaining_sql
368: @stmt_api.remaining_sql
369: end
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.
# File lib/amalgalite/statement.rb, line 51
51: def reset!
52: @stmt_api.reset!
53: @param_positions = {}
54: @blobs_to_write.clear
55: @rowid_index = nil
56: end
reset the statment in preparation for executing it again
# File lib/amalgalite/statement.rb, line 70
70: def reset_for_next_execute!
71: @stmt_api.reset!
72: @stmt_api.clear_bindings!
73: @blobs_to_write.clear
74: end
Return the array of field names for the result set, the field names are all strings
# File lib/amalgalite/statement.rb, line 360
360: def result_fields
361: @fields ||= result_meta.collect { |m| m.name }
362: end
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 teh 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.
# File lib/amalgalite/statement.rb, line 317
317: def result_meta
318: unless @result_meta
319: meta = []
320: column_count.times do |idx|
321: column_meta = ::OpenStruct.new
322: column_meta.name = @stmt_api.column_name( idx )
323:
324: db_name = @stmt_api.column_database_name( idx )
325: tbl_name = @stmt_api.column_table_name( idx )
326: col_name = @stmt_api.column_origin_name( idx )
327:
328: column_meta.schema = ::Amalgalite::Column.new( db_name, tbl_name, col_name )
329: column_meta.schema.declared_data_type = @stmt_api.column_declared_type( idx )
330:
331: # only check for rowid if we have a table name and it is not the
332: # sqlite_master table. We could get recursion in those cases.
333: if not using_rowid_column? and tbl_name and tbl_name != 'sqlite_master' and is_column_rowid?( tbl_name, col_name ) then
334: @rowid_index = idx
335: end
336:
337: meta << column_meta
338: end
339:
340: @result_meta = meta
341: end
342: return @result_meta
343: end
Is the special column "ROWID", "OID", or "ROWID" used?
# File lib/amalgalite/statement.rb, line 42
42: def using_rowid_column?
43: not @rowid_index.nil?
44: end
Write any blobs that have been bound to parameters to the database. This assumes that the blobs go into the last inserted row
# File lib/amalgalite/statement.rb, line 222
222: def write_blobs
223: unless @blobs_to_write.empty?
224: @blobs_to_write.each do |blob|
225: blob.write_to_column!
226: end
227: end
228: end