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



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

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

    • []



370
371
372
373
 
# File 'ext/extralite/query.c', line 370

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



405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
 
# File 'ext/extralite/query.c', line 405

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



454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
 
# File 'ext/extralite/query.c', line 454

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



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

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:



514
515
516
517
518
519
520
521
522
 
# File 'ext/extralite/query.c', line 514

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

#close ⇒ Extralite::Query

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

Returns:



528
529
530
531
532
533
534
535
536
 
# File 'ext/extralite/query.c', line 528

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



542
543
544
545
 
# File 'ext/extralite/query.c', line 542

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



500
501
502
503
504
 
# File 'ext/extralite/query.c', line 500

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:



482
483
484
485
 
# File 'ext/extralite/query.c', line 482

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:



482
483
484
485
 
# File 'ext/extralite/query.c', line 482

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:



514
515
516
517
518
519
520
521
522
 
# File 'ext/extralite/query.c', line 514

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

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



308
309
310
311
312
313
314
 
# File 'ext/extralite/query.c', line 308

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



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

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)



339
340
341
342
343
344
 
# File 'ext/extralite/query.c', line 339

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



578
579
580
581
582
583
584
585
586
587
588
589
 
# File 'ext/extralite/query.c', line 578

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



595
596
597
598
 
# File 'ext/extralite/query.c', line 595

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



608
609
610
611
612
 
# File 'ext/extralite/query.c', line 608

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:



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

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:



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

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



491
492
493
494
 
# File 'ext/extralite/query.c', line 491

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)



559
560
561
562
563
564
565
566
567
568
569
570
571
 
# File 'ext/extralite/query.c', line 559

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



296
297
298
299
300
 
# File 'ext/extralite/query.c', line 296

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 ⇒ Proc, ...

Returns the transform set for the query.

Returns:



619
620
621
622
 
# File 'ext/extralite/query.c', line 619

VALUE Query_transform_get(VALUE self) {
  Query_t *query = self_to_query_verify(self);
  return query->transform;
}

#transform=(transform) ⇒ Extralite::Query

Sets the transform block to the given proc or transform object. If a transform is set, calls to #to_a, #next, #each and #batch_query will transform values fetched from the database using the transform before passing them to the application code. To remove the transform, call #transform= with a nil value.

# fetch column c as an ORM object
q = db.prepare('select * from foo')
q.transform = ->(h) {
  MyModel.new(h)
}

# use a custom transform
q = db.prepare('select id, name from foo')
q.transform = Extralite::Transform.new(
  columns: {
    id: { type: integer, identity: true },
    name: { type: :text }
  }
)

Parameters:

Returns:



649
650
651
652
653
654
 
# File 'ext/extralite/query.c', line 649

VALUE Query_transform_set(VALUE self, VALUE transform) {
  Query_t *query = self_to_query(self);
  query->transform = transform;
  query->transform_object = rb_obj_is_instance_of(transform, cTransform);
  return self;
}