= EdenMobile Database =
[[TOC]]

== $emdb Service ==

EdenMobile provides the '''$emdb''' service to access the database.

To use the service, it must be included in the controller dependencies:

{{{
#!js
EdenMobile.controller("MyController", [
    '$scope', '$stateParams', '$emdb',
    function($scope, $stateParams, $emdb) {
        // controller code goes here
    }
]);
}}}

{{{$emdb}}} automatically creates the database and tables when it is initialized for the first time.

== API ==

'''$emdb''' exposes APIs for common database transactions.

{{{
#!div class="important" style="border: 2pt solid; text-align: center"
Remember that '''all database transactions are asynchronous''', so callback functions may need to perform a {{{$scope.$apply}}} to trigger a digest cycle explicitly.
}}}

=== table ===

The '''table''' API provides fundamental methods to access data in database tables, examples:

{{{#!js
// Instantiate the API with the table name
table = $emdb.table("mytable");

// Insert records into the table
table.insert({
    // The data to insert
    'name': 'Example',
    'number': 4
}, function(recordID) {
    // Callback function after successful insert
});

// Select records from the table - general pattern
var fields = ['id', 'name'],
    query = 'number > 3';
table.select(fields, query, function(result) {
    var rows = result.rows,
    for (var i=0, len=rows.length; i<len; i++) {
        record = rows.item(i);
        // do something with the record
    }
});

// Other variants of select:
// Extract all records, but only certain fields
table.select(fields, function(result) {
    // process the result
});

// Extract all fields, but only certain records
table.select(query, function(result) {
    // process the result
});

// Extract all fields from all records
table.select(function(result) {
    // process the result
});

}}}

== Default Schema ==

The database schema is stored ''in'' in the database itself, in the {{{em_schema}}} table.

For the initial setup of the database, a static {{{emDefaultSchema}}} (in www/js/modules/schema.js) is used:

{{{
#!js
var emSchemaVersion = '1';

var emDefaultSchema = {

    /**
     * Table to store the current schema version
     */
    'em_version': {
        'version': {
            type: 'string',
            label: 'Version',
            notnull: true
        },
        _records: [
            {'version': emSchemaVersion}
        ]
    },

    /**
     * Table to store table schemas
     */
    'em_schema': {
        'name': {
            type: 'string',
            label: 'Name',
            notnull: true
        },
        'schema': {
            type: 'json',
            label: 'Schema',
            notnull: true
        }
    }
};
}}}

Once this schema has been written to the new database, it will be ignored.

== emSQL ==

{{{emSQL}}} is a helper module to construct common SQL statements. It can be found in www/js/modules/sql.js.

Examples:
{{{
#!js

// Schema definition
var schema = {
    id: {
        type: 'id'
    },
    name: {
        type: 'string',
        label: 'Name',
        notnull: true
    },
    number: {
        type: 'integer',
    },
};

// Create an emSQL helper for this schema
var table = emSQL.Table('testtable', schema);

// Produce SQL to create the table
// sql == 'CREATE IF NOT EXISTS "testtable" (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,number INTEGER)'
var sql = table.create();

// Produce SQL to insert data
// => produces a tuple: an SQL string with placeholders and an array with values (for use with db.executeSql)
// sql == ['INSERT INTO "testtable" (name,number) VALUES (?,?)', ['testing',4]]
sql = table.insert({name: 'testing', number: 4});

// Produce SQL to select data
// sql == 'SELECT testtable.id,testtable.name FROM "testtable" WHERE number>2'
sql = table.select(['id', 'name'], 'number>2');

// Produce SQL to drop the table
// sql == 'DROP TABLE IF EXISTS "testtable"'
sql = table.drop();

}}}