Changes between Initial Version and Version 1 of DeveloperGuidelines/EdenMobile/Services/emDB

Timestamp:

11/23/17 22:32:46 (8 years ago)

Author:

Dominic König

Comment:

--

DeveloperGuidelines/EdenMobile/Services/emDB

@@ +1 @@
+= 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.
+
+=== 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}}}