Class: Extralite::Query

Inherits:
Object
  • Object
show all
Defined in:
ext/extralite/query.c,
lib/extralite.rb,
ext/extralite/query.c

Overview

This class represents a prepared query that can be reused with different parameters. It encapsulates SQLite prepared statements.

Instance Method Summary collapse

Constructor Details

#initialize(db, sql, mode) ⇒ void

Initializes a new prepared query with the given database and SQL string. A Query is normally instantiated by calling Database#prepare:

query = @db.prepare('select * from foo')

Parameters:

  • db (Extralite::Database) —

    associated database

  • sql (String) —

    SQL string

  • mode (Symbol) —

    query mode



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'ext/extralite/query.c', line 111

VALUE Query_initialize(VALUE self, VALUE db, VALUE sql, VALUE mode) {
  Query_t *query = self_to_query(self);

  sql = rb_funcall(sql, ID_strip, 0);
  if (!RSTRING_LEN(sql))
    rb_raise(cError, "Cannot prepare an empty SQL query");

  RB_OBJ_WRITE(self, &query->db, db);
  RB_OBJ_WRITE(self, &query->sql, sql);
  if (rb_block_given_p())
    RB_OBJ_WRITE(self, &query->transform_proc, rb_block_proc());

  query->self = self;
  query->db = db;
  query->db_struct = self_to_database(db);
  query->sqlite3_db = Database_sqlite3_db(db);
  query->stmt = NULL;
  query->closed = 0;
  query->eof = 0;
  query->should_reset = 0;
  query->query_mode = symbol_to_query_mode(mode);

  return Qnil;
}

Instance Method Details

#<<([...]) ⇒ Object #<< { ... } ⇒ Object

Runs the with the given parameters, returning the total changes effected. This method should be used for data- or schema-manipulation queries.

Query parameters to be bound to placeholders in the query can be specified as a list of values or as a hash mapping parameter names to values. When parameters are given as an array, the query should specify parameters using ?:

query = db.prepare('update foo set x = ? where y = ?')
query << [42, 43]

Named placeholders are specified using :. The placeholder values are specified using a hash, where keys are either strings are symbols. String keys can include or omit the : prefix. The following are equivalent:

query = db.prepare('update foo set x = :bar')
query << { bar: 42 }
query << { 'bar' => 42 }
query << { ':bar' => 42 }

Overloads:

  • #<< { ... } ⇒ Object

    Yields:

    • []



366
367
368
369
 
# File 'ext/extralite/query.c', line 366

VALUE Query_execute_chevrons(VALUE self, VALUE params) {
  Query_execute(1, &params, self);
  return self;
}

#batch_execute(params_array) ⇒ Integer #batch_execute(enumerable) ⇒ Integer #batch_execute(callable) ⇒ Integer Also known as: execute_multi

Executes the query for each set of parameters in the paramter source. If an enumerable is given, it is iterated and each of its values is used as the parameters for running the query. If a callable is given, it is called repeatedly and each of its return values is used as the parameters, until nil is returned.

Returns the number of changes effected. This method is designed for inserting multiple records.

query = db.prepare('insert into foo values (?, ?, ?)')
records = [
  [1, 2, 3],
  [4, 5, 6]
]
query.batch_execute(records)

source = [
  [1, 2, 3],
  [4, 5, 6]
]
query.batch_execute { records.shift }

Parameters:

  • parameters (Array<Array, Hash>, Enumerable, Enumerator, Callable) —

    array of parameters to run query with

Returns:

  • (Integer) —

    number of changes effected



401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
 
# File 'ext/extralite/query.c', line 401

VALUE Query_batch_execute(VALUE self, VALUE parameters) {
  Query_t *query = self_to_query_verify(self);
  if (query->closed) rb_raise(cError, "Query is closed");

  if (!query->stmt)
    prepare_single_stmt(DB_GVL_MODE(query), query->sqlite3_db, &query->stmt, query->sql);

  query_ctx ctx = QUERY_CTX(
    self,
    query->sql,
    query->db_struct,
    query->stmt,
    parameters,
    Qnil,
    QUERY_HASH,
    ROW_YIELD_OR_MODE(ROW_MULTI),
    ALL_ROWS
  );
  return safe_batch_execute(&ctx);
}

#batch_query(sql, params_array) ⇒ Array<Array>, Integer #batch_query(sql, enumerable) ⇒ Array<Array>, Integer #batch_query(sql, callable) ⇒ Array<Array>, Integer #batch_query(sql, params_array) {|rows| ... } ⇒ Array<Array>, Integer #batch_query(sql, enumerable) {|rows| ... } ⇒ Array<Array>, Integer #batch_query(sql, callable) {|rows| ... } ⇒ Array<Array>, Integer

Executes the prepared query for each list of parameters in the given paramter source. If a block is given, it is called with the resulting rows for each invocation of the query, and the total number of changes is returned. Otherwise, an array containing the resulting rows for each invocation is returned.

Rows are returned according to the query mode and transform.

q = db.prepare('insert into foo values (?, ?) returning bar, baz')
records = [
  [1, 2],
  [3, 4]
]
q.batch_query(records)
#=> [{ bar: 1, baz: 2 }, { bar: 3, baz: 4}]

*

Overloads:

  • #batch_query(sql, params_array) {|rows| ... } ⇒ Array<Array>, Integer

    Yields:

    • (rows)
  • #batch_query(sql, enumerable) {|rows| ... } ⇒ Array<Array>, Integer

    Yields:

    • (rows)
  • #batch_query(sql, callable) {|rows| ... } ⇒ Array<Array>, Integer

    Yields:

    • (rows)

Parameters:

  • sql (String) —

    query SQL

  • parameters (Array<Array, Hash>, Enumerable, Enumerator, Callable) —

    parameters to run query with

Returns:

  • (Array<Array>, Integer) —

    Returned rows or total number of changes effected



450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
 
# File 'ext/extralite/query.c', line 450

VALUE Query_batch_query(VALUE self, VALUE parameters) {
  Query_t *query = self_to_query_verify(self);
  if (query->closed) rb_raise(cError, "Query is closed");

  if (!query->stmt)
    prepare_single_stmt(DB_GVL_MODE(query), query->sqlite3_db, &query->stmt, query->sql);

  query_ctx ctx = QUERY_CTX(
    self,
    query->sql,
    query->db_struct,
    query->stmt,
    parameters,
    query->transform_proc,
    query->query_mode,
    ROW_YIELD_OR_MODE(ROW_MULTI),
    ALL_ROWS
  );
  return safe_batch_query(&ctx);
}

#bind(*args) ⇒ Extralite::Query

Resets the underlying prepared statement and rebinds parameters if any are given. After calling this method the underlying prepared statement is reset to its initial state, and any call to one of the #next_xxx methods will return the first row in the query's result set.

Bound parameters can be specified as a list of values or as a hash mapping parameter names to values. When parameters are given as a splatted array, the query should specify parameters using ?:

query = db.prepare('select * from foo where x = ?')
query.bind(42)

Named placeholders are specified using :. The placeholder values are specified using a hash, where keys are either strings are symbols. String keys can include or omit the : prefix. The following are equivalent:

query = db.prepare('select * from foo where x = :bar')
query.bind(bar: 42)

Returns:



208
209
210
211
212
213
214
 
# File 'ext/extralite/query.c', line 208

VALUE Query_bind(int argc, VALUE *argv, VALUE self) {
  Query_t *query = self_to_query_verify(self);

  query_bind(query, argc, argv);
  query->should_reset = 1;
  return self;
}

#clone ⇒ Extralite::Query #dup ⇒ Extralite::Query

Returns a new query instance for the same SQL as the original query.

Returns:



510
511
512
513
514
515
516
517
518
 
# File 'ext/extralite/query.c', line 510

VALUE Query_clone(VALUE self) {
  Query_t *query = self_to_query(self);
  VALUE args[] = {
    query->db,
    query->sql,
    query_mode_to_symbol(query->query_mode)
  };
  return rb_funcall_with_block(cQuery, ID_new, 3, args, query->transform_proc);
}

#close ⇒ Extralite::Query

Closes the query. Attempting to run a closed query will raise an error.

Returns:



524
525
526
527
528
529
530
531
532
 
# File 'ext/extralite/query.c', line 524

VALUE Query_close(VALUE self) {
  Query_t *query = self_to_query(self);
  if (query->stmt) {
    sqlite3_finalize(query->stmt);
    query->stmt = NULL;
  }
  query->closed = 1;
  return self;
}

#closed? ⇒ boolean

Returns true if the query is closed.

Returns:

  • (boolean) —

    true if query is closed



538
539
540
541
 
# File 'ext/extralite/query.c', line 538

VALUE Query_closed_p(VALUE self) {
  Query_t *query = self_to_query(self);
  return query->closed ? Qtrue : Qfalse;
}

#columns ⇒ Array<Symbol>

Returns the column names for the query without running it.

Returns:

  • (Array<Symbol>) —

    column names



496
497
498
499
500
 
# File 'ext/extralite/query.c', line 496

VALUE Query_columns(VALUE self) {
  Query_t *query = self_to_query_verify(self);
  query_reset(query);
  return Query_perform_next(self, ALL_ROWS, safe_query_columns);
}

#database ⇒ Extralite::Database #db ⇒ Extralite::Database

Returns the database associated with the query.

Overloads:



478
479
480
481
 
# File 'ext/extralite/query.c', line 478

VALUE Query_database(VALUE self) {
  Query_t *query = self_to_query(self);
  return query->db;
}

#database ⇒ Extralite::Database #db ⇒ Extralite::Database

Returns the database associated with the query.

Overloads:



478
479
480
481
 
# File 'ext/extralite/query.c', line 478

VALUE Query_database(VALUE self) {
  Query_t *query = self_to_query(self);
  return query->db;
}

#clone ⇒ Extralite::Query #dup ⇒ Extralite::Query

Returns a new query instance for the same SQL as the original query.

Returns:



510
511
512
513
514
515
516
517
518
 
# File 'ext/extralite/query.c', line 510

VALUE Query_clone(VALUE self) {
  Query_t *query = self_to_query(self);
  VALUE args[] = {
    query->db,
    query->sql,
    query_mode_to_symbol(query->query_mode)
  };
  return rb_funcall_with_block(cQuery, ID_new, 3, args, query->transform_proc);
}

#each ⇒ Extralite::Query, Extralite::Iterator

Iterates through the result set, passing each row to the given block. If no block is given, returns a Extralite::Iterator instance. Rows are given to the block according to the query mode and the query transform.

Returns:



304
305
306
307
308
309
310
 
# File 'ext/extralite/query.c', line 304

VALUE Query_each(VALUE self) {
  if (!rb_block_given_p()) return rb_funcall(cIterator, ID_new, 1, self);

  Query_t *query = self_to_query_verify(self);
  query_reset(query);
  return Query_perform_next(self, ALL_ROWS, query_impl(query->query_mode));
}

#eof? ⇒ boolean

Returns true if iteration has reached the end of the result set.

Returns:

  • (boolean) —

    true if iteration has reached the end of the result set



220
221
222
223
224
 
# File 'ext/extralite/query.c', line 220

VALUE Query_eof_p(VALUE self) {
  Query_t *query = self_to_query_verify(self);

  return query->eof ? Qtrue : Qfalse;
}

#execute(*parameters) ⇒ Object

Runs a query returning the total changes effected. This method should be used for data- or schema-manipulation queries.

Query parameters to be bound to placeholders in the query can be specified as a list of values or as a hash mapping parameter names to values. When parameters are given as an array, the query should specify parameters using ?:

query = db.prepare('update foo set x = ? where y = ?')
query.execute(42, 43)

Named placeholders are specified using :. The placeholder values are specified using a hash, where keys are either strings are symbols. String keys can include or omit the : prefix. The following are equivalent:

query = db.prepare('update foo set x = :bar')
query.execute(bar: 42)
query.execute('bar' => 42)
query.execute(':bar' => 42)



335
336
337
338
339
340
 
# File 'ext/extralite/query.c', line 335

VALUE Query_execute(int argc, VALUE *argv, VALUE self) {
  Query_t *query = self_to_query_verify(self);
  query_bind(query, argc, argv);
  query_reset(query);
  return Query_perform_next(self, ALL_ROWS, safe_query_changes);
}

#inspect ⇒ String

Returns a short string representation of the query instance, including the SQL string.

Returns:

  • (String) —

    string representation



595
596
597
598
599
600
601
602
603
604
605
606
 
# File 'ext/extralite/query.c', line 595

VALUE Query_inspect(VALUE self) {
  VALUE cname = rb_class_name(CLASS_OF(self));
  VALUE sql = self_to_query(self)->sql;
  if (RSTRING_LEN(sql) > 48) {
    sql = rb_funcall(sql, ID_slice, 2, INT2FIX(0), INT2FIX(45));
    rb_str_cat2(sql, "...");
  }
  sql = rb_funcall(sql, ID_inspect, 0);

  RB_GC_GUARD(sql);
  return rb_sprintf("#<%"PRIsVALUE":%p %"PRIsVALUE">", cname, (void*)self, sql);
}

#mode ⇒ Symbol

Returns the query mode.

Returns:

  • (Symbol) —

    query mode



612
613
614
615
 
# File 'ext/extralite/query.c', line 612

VALUE Query_mode_get(VALUE self) {
  Query_t *query = self_to_query(self);
  return query_mode_to_symbol(query->query_mode);
}

#mode=(mode) ⇒ Symbol

Sets the query mode. This can be one of :hash, :splat, :array.

Parameters:

  • mode (Symbol) —

    query mode

Returns:

  • (Symbol) —

    query mode



625
626
627
628
629
 
# File 'ext/extralite/query.c', line 625

VALUE Query_mode_set(VALUE self, VALUE mode) {
  Query_t *query = self_to_query(self);
  query->query_mode = symbol_to_query_mode(mode);
  return mode;
}

#next ⇒ any, Extralite::Query #next(row_count) ⇒ Array<any>, Extralite::Query

Returns the next 1 or more rows from the associated query's result set. The row value is returned according to the query mode and the query transform.

If no row count is given, a single row is returned. If a row count is given, an array containing up to the row_count rows is returned. If row_count is -1, all rows are returned. If the end of the result set has been reached, nil is returned.

If a block is given, rows are passed to the block and self is returned.

Overloads:

  • #next ⇒ any, Extralite::Query

    Returns next row or self if block is given.

    Returns:

  • #next(row_count) ⇒ Array<any>, Extralite::Query

    Returns next rows or self if block is given.

    Parameters:

    • row_count (Integer) —

      maximum row count or -1 for all rows

    Returns:



281
282
283
284
285
 
# File 'ext/extralite/query.c', line 281

VALUE Query_next(int argc, VALUE *argv, VALUE self) {
  Query_t *query = self_to_query_verify(self);
  rb_check_arity(argc, 0, 1);
  return Query_perform_next(self, MAX_ROWS_FROM_ARGV(argc, argv), query_impl(query->query_mode));
}

#reset ⇒ Extralite::Query

Resets the underlying prepared statement. After calling this method the underlying prepared statement is reset to its initial state, and any call to one of the #next_xxx methods will return the first row in the query's result set.

query = @db.prepare('select * from foo where bar = ?')
first = query.next
second = query.next
query.reset
query.next #=> returns the first row again

Returns:



180
181
182
183
184
185
 
# File 'ext/extralite/query.c', line 180

VALUE Query_reset(VALUE self) {
  Query_t *query = self_to_query_verify(self);

  query_reset(query);
  return self;
}

#sql ⇒ String

Returns the SQL string for the query.

Returns:

  • (String) —

    SQL string



487
488
489
490
 
# File 'ext/extralite/query.c', line 487

VALUE Query_sql(VALUE self) {
  Query_t *query = self_to_query(self);
  return query->sql;
}

#status(op) ⇒ Integer #status(op, reset) ⇒ Integer

Returns the current status value for the given op. To reset the value, pass true as reset.

Overloads:

  • #status(op) ⇒ Integer

    Returns current status value for the given op.

    Parameters:

    • op (Integer) —

      status op

    Returns:

    • (Integer) —

      current status value for the given op

  • #status(op, reset) ⇒ Integer

    Returns current status value for the given op (before reset).

    Parameters:

    • op (Integer) —

      status op

    • reset (true) —

      reset flag

    Returns:

    • (Integer) —

      current status value for the given op (before reset)



555
556
557
558
559
560
561
562
563
564
565
566
567
 
# File 'ext/extralite/query.c', line 555

VALUE Query_status(int argc, VALUE* argv, VALUE self) {
  VALUE op, reset;

  rb_scan_args(argc, argv, "11", &op, &reset);

  Query_t *query = self_to_query_verify(self);

  if (!query->stmt)
    prepare_single_stmt(DB_GVL_MODE(query), query->sqlite3_db, &query->stmt, query->sql);

  int value = sqlite3_stmt_status(query->stmt, NUM2INT(op), RTEST(reset) ? 1 : 0);
  return INT2NUM(value);
}

#to_a ⇒ Array<any>

Returns all rows in the associated query's result set. Rows are returned according to the query mode and the query transform.

Returns:

  • (Array<any>) —

    all rows



292
293
294
295
296
 
# File 'ext/extralite/query.c', line 292

VALUE Query_to_a(VALUE self) {
  Query_t *query = self_to_query_verify(self);
  query_reset(query);
  return Query_perform_next(self, ALL_ROWS, query_impl(query->query_mode));
}

#transform ⇒ Extralite::Query

Sets the transform block to the given block. If a transform block is set, calls to #to_a, #next, #each and #batch_query will transform values fetched from the database using the transform block before passing them to the application code. To remove the transform block, call #transform without a block. The transform for each row is done by passing the row hash to the block.

# fetch column c as an ORM object
q = db.prepare('select * from foo order by a').transform do |h|
  MyModel.new(h)
end

Returns:



583
584
585
586
587
588
 
# File 'ext/extralite/query.c', line 583

VALUE Query_transform(VALUE self) {
  Query_t *query = self_to_query_verify(self);

  RB_OBJ_WRITE(self, &query->transform_proc, rb_block_given_p() ? rb_block_proc() : Qnil);
  return self;
}