Query iteration or cursor
Query is used when you do not know the object key exactly.
In fact, IndexedDB do not have query processor but instead open a
stream of records, called cursor, in an object store.
Cursor can be open for the whole object store or subset of
them by limiting boundary by key(s). Records can be stream forward
or backward.
A store-wise cursor can be constructed from a database instance by specifying object store name as follow:
q = new ydn.db.Cursor('customer')
This will iterate all records in the customer object
store.
Use key range, to define a subset of an object store:
var direction = 'next'; // could be 'prev'
var index = 'age'; // optional, if not defined index is in primary key
var lower_boundary_key = 18;
var upper_boundary_key = 19;
q = new ydn.db.Cursor('customer', direction, index, lower_boundary_key, upper_boundary_key)
It will enumerate records from 18 to 19 inclusive
of indexed field age of the record on customer
object store.
The method to retrieve the result is fetch() method,
which list values of records as Array in deferred
function as follow:
results = db.fetch(q) results.done(print_it)
For convenient, get() method is available to query for
fetching only one result as Object.
In addition to fetching all the result as a list, we can preform map, reduce and filter operations to the resulting record during cursor iteration. These iteration methods, which we will be describing details in later section.
Query
Cursor are very low level and not descriptive. For example, to query
an aggregate property like SUM or COUNT, map
iteration function and reduce iteration function
function must be provided to the cursor. Here we describe how we could
achieved SQL query like processing using ydn.db.Query.
Above query can be rewrite as follow:
var sql = '';
var params = [];
q = new ydn.db.Query(sql, params);
q.from('customer').where('age', '>=', 18, '<=', 19);
results = db.fetch(q)
results.done(print_it)
Currently SQL statement parser are not implemented in the library. So we
are using query constructor methods here.Cursor is native for IndexedDB. And, in fact, query are transformed
into cursor to execute the fetch method. It seems like query is
synthetic sugar to cursor. But it is more than that. Since query
object do not have function reference, query object can be transported
from one thread to another or persist on somewhere else for later use.
Moreover some part of query are execute natively in WebSQL. You can
find out using explain method.
WHERE
WHERE predicate filter the result by executing given conditions. Record for which the predicate evaluates to falsy value are excluded in the query result.
q = new ydn.db.Query();
q = q.from('customer').where('age', '>=', 18 , '<', 25).where('sex', '=', 'FEMALE');
young_girls = db.fetch(q)
young_girls.done(print_it)
The first arguments is field name, the second argument is query operator and the last to the value to evaluate against.
SELECT
A SELECT statement retrieves fields from the iteration record to generated a transformed object.
q = new ydn.db.Query();
q = q.from('customer').select('select', ['email', 'first'])
db.fetch(q).done(print_it)
Aggregations
count
Emulate SQL COUNT method.
q = new ydn.db.Query();
q = q.from('customer').select('count')
db.fetch(q).done(print_it)
sum
Emulate SQL SUM method.
q = new ydn.db.Query();
q = q.from('customer').select('sum', 'age')
db.fetch(q).done(print_it)
average
Emulate SQL AVERAGE method.
q = new ydn.db.Query();
q = q.from('customer').select('avg', 'age')
db.fetch(q).done(print_it)