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 result is 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 use a local variable for table.$('fieldname') and use that 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