emDB

emDB is a service that provides a database abstraction layer for EdenMobile.

Introduction

Tables

A Table object represents a database table, and can be accessed:

...directly from the emDB service (asynchronously):

emDB.table('tablename').then(function(table) {
    ...
});

...or, where a Resource is available (synchronously):

// Access the table of a Resource
var table = resource.table;

// Access any table via a Resource
var table = resource.getTable('tablename');

Fields

To access a Field in the Table, the Table object provides the $ method:

var field = table.$('fieldname');

While it is also possible to access a Field like table.fields['fieldname'], this would not map server-side super-IDs (e.g. pe_id) to the EdenMobile em_object_id. Therefore the $ method is preferrable, especially in cases where the field name is a variable.

Expressions

With Tables and Fields, it is possible to construct query Expressions, e.g.

var query = field.is(5);

AND and OR are functions of query expressions:

var query = field.is(5).or(field.greaterThan(6));

If there are multiple operand expressions in an AND or OR, the global allOf() and anyOf() functions can used to improve readability:

// Multiple operand expressions:
var query = field.is(5).or(field.is(6).or(field.is(7)));

// Alternative with anyOf:
var query = anyOf(field.is(5), field.is(6), field.is(7));

Both anyOf and allOf can of course be nested indefinitely.

Negation has two alternatives as well:

// Global not() function
var query = not(field.in([5, 6, 7]));

// Expression method
var query = field.in([5, 6, 7]).not();

Obviously, the former provides for better readability - whilst the latter can be used if the local context overrides the global not for some reason.

Sets

With a table and a query expression, a Set can be constructed like:

var set = table.where(query);

To extract data from a Set, use select(). This is an asynchronous method, so the result comes in a callback:

set.select([field], function(rows) {
    if (rows.len) {
        rows.forEach(function(row) {
            // Do something with the row
        });
    }
});

The select() method accepts both field names and Fields in the field list (can even be mixed, but not duplicated). The callback is invoked with an array of Rows.

Rows

The Row object implements a $ method again (analogously to accessing fields in a table), but this one accepts both field names and Fields:

// Accessing a column via field name
var value = row.$('fieldname');

// Accessing a column with a Field instance
var value = row.$(table.$('fieldname'));

Using Fields to access column values is recommended (or even necessary) when selecting from joins. Obviously, you can set a local variable for table.$('fieldname'), and then use it in both the select() and the subsequent column access.

Joins

A join can be constructed by calling the join() (=inner join) or left() method of a Table or a Set:

table.left(
    otherTable.on(
        otherTable.$('foreign_key').equals(table.$('id'))
    )
)

...produces a left join of table with otherTable. The on() method of the Table takes a query expression as argument.

The join() or left() methods always return the Set (if called from a Table, they will create a new Set), so they can be chained both with more .join() resp .left() (to construct a multiple-table join), or .where() to filter the set, and eventually a .select() to extract data.

So it can look like this:

table.left(
    otherTable.on(otherTable.$('foreign_key').equals(table.$('id')))
).where(
    table.$('name').equals('some string')
).select(
    [table.$('name'), otherTable.$('value')], 
    function(rows) {
        ...
    });

(written on multiple lines, this becomes very readable - almost undisturbed from the inevitable JS punctuation)

Is is also possible to chain multiple .where()'s, basically producing an AND of all where() expresions in the chain. Surely, where you construct a Set in place, you wouldn't do multiple .where()'s but rather use allOf(). But if you collect filters (e.g. in a loop), you can easily extend the Set by calling .where() multiple times, i.e. instead of:

// Somewhat cumbersome:
for (...) {
    ...
    if (query) {
        query = query.and(subQuery);
    } else {
        query = subQuery;
    }
}
table.where(query).select(...);

...you can do it like:

set = table;
for (...) {
    ...
    set = set.where(subQuery);
}

Orderby and Limitby

The select() method accepts an object with arguments as (optional) second parameter, e.g. limitby and orderby:

set.select(
    [array of fields], 
    {
        limitby: [0,1], 
        orderby: field
    },
    function(rows) {...}
);

NB limitby: [0,1] can also be written as simply limitby: 1