= 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):

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

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

{{{#!js
// 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:

{{{#!js
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.

{{{#!js
var query = field.is(5);
}}}

AND and OR are functions of query expressions:
{{{#!js
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:
{{{#!js
// 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:

{{{#!js
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:
{{{#!js
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:

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

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

Using {{{Field}}}s 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:

{{{#!js
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:
{{{#!js
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:

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

...you can do it like:

{{{#!js
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}}}:

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

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