= emDB =
[[TOC]]

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

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:

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

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

{{{#!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.length) {
        rows.forEach(function(row) {
            // Do something with the row
        });
    }
});
}}}

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

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

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

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

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

...you can do it like:

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

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

=== Aggregates and Groupby ===

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

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

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

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