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

In either case, if no table with that name exists, the result will be undefined (can safely be used to validate table names).

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 (but produce undefined).

Therefore the $ method is preferrable, especially in cases where the field name is a variable.

If no field with that name is defined, the $ method returns undefined (can safely be used to validate field names).

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 with a long trail of brackets (bit messy):
var query = field.is(5).or(field.is(6).or(field.is(7)));

// Alternative with anyOf (bit cleaner):
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 - whereas the latter can be useful 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.length) {
        rows.forEach(function(row) {
            // Do something with the row
        });
    }
});

The select() method accepts both field names and Fields in the field list, as well as Field expressions (can even be mixed):

// Example of a field name (=string)
var fieldName = 'myfield';

// Example of a Field:
var field = table.$('myfield');

// Example of a Field expression:
var expr = table.$('myfield').lower();

The callback is invoked with an array of Rows.

If a second callback is added as last argument, it will be invoked with an error message (string) if the SELECT fails for some reason. Without error callback, errors will be shown in an alert dialog.

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 by the inevitable JS punctuation)

Chaining WHERE-Expressions

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:

// Possible alternative:
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

Aggregates and Groupby

Aggregate expressions can be constructed from field expressions using operator functions, e.g.:

// The total (sum) of the field 'value':
var totalValue = table.$('value').sum();

Currently supported operators are count, sum, avg, min, and max.

Aggregate expressions can be used to extract the aggregate values from rows:

// The total (sum) of the field 'value':
var totalValue = table.$('value').sum();
...
// Access the total in a row
var value = row.$(totalValue);

The select() method of Sets accepts a groupby option:

// Extract the total of values per location
var locationID = table.$('location_id'),
    totalValue = table.$('value').sum();
set.select(
    [locationID, totalValue], 
    {
        groupby: locationID
    },
    function(rows) {...}
);