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



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# File 'ext/extralite/query.c', line 100

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->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->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:

    • []



344
345
346
347
 
# File 'ext/extralite/query.c', line 344

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



379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
 
# File 'ext/extralite/query.c', line 379

VALUE Query_batch_execute(VALUE self, VALUE parameters) {
  Query_t *query = self_to_query(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



428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
 
# File 'ext/extralite/query.c', line 428

VALUE Query_batch_query(VALUE self, VALUE parameters) {
  Query_t *query = self_to_query(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:



185
186
187
188
189
190
191
 
# File 'ext/extralite/query.c', line 185

VALUE Query_bind(int argc, VALUE *argv, VALUE self) {
  Query_t *query = self_to_query(self);
  if (query->closed) rb_raise(cError, "Query is closed");

  query_reset_and_bind(query, argc, argv);
  return self;
}

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

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

Returns:



488
489
490
491
492
493
494
495
496
 
# File 'ext/extralite/query.c', line 488

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:



502
503
504
505
506
507
508
509
510
 
# File 'ext/extralite/query.c', line 502

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



516
517
518
519
 
# File 'ext/extralite/query.c', line 516

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



474
475
476
477
478
 
# File 'ext/extralite/query.c', line 474

VALUE Query_columns(VALUE self) {
  Query_t *query = self_to_query(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:



456
457
458
459
 
# File 'ext/extralite/query.c', line 456

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:



456
457
458
459
 
# File 'ext/extralite/query.c', line 456

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:



488
489
490
491
492
493
494
495
496
 
# File 'ext/extralite/query.c', line 488

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:



283
284
285
286
287
288
289
 
# File 'ext/extralite/query.c', line 283

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

  Query_t *query = self_to_query(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



197
198
199
200
201
202
 
# File 'ext/extralite/query.c', line 197

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

  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)



314
315
316
317
318
 
# File 'ext/extralite/query.c', line 314

VALUE Query_execute(int argc, VALUE *argv, VALUE self) {
  Query_t *query = self_to_query(self);
  query_reset_and_bind(query, argc, argv);
  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



574
575
576
577
578
579
580
581
582
583
584
585
 
# File 'ext/extralite/query.c', line 574

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



591
592
593
594
 
# File 'ext/extralite/query.c', line 591

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



604
605
606
607
608
 
# File 'ext/extralite/query.c', line 604

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:



260
261
262
263
264
 
# File 'ext/extralite/query.c', line 260

VALUE Query_next(int argc, VALUE *argv, VALUE self) {
  Query_t *query = self_to_query(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:



156
157
158
159
160
161
162
 
# File 'ext/extralite/query.c', line 156

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

  query_reset(query);
  return self;
}

#sql ⇒ String

Returns the SQL string for the query.

Returns:

  • (String) —

    SQL string



465
466
467
468
 
# File 'ext/extralite/query.c', line 465

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)



533
534
535
536
537
538
539
540
541
542
543
544
545
546
 
# File 'ext/extralite/query.c', line 533

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(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);

  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



271
272
273
274
275
 
# File 'ext/extralite/query.c', line 271

VALUE Query_to_a(VALUE self) {
  Query_t *query = self_to_query(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:



562
563
564
565
566
567
 
# File 'ext/extralite/query.c', line 562

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

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