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)