DATABASE JS TOOLS

database.js

'use strict';

angular.module('shared')
    .factory('$database', ['$q', '$migrations', 'DEBUG_DB',
        function ($q, $migrations, DEBUG_DB) {

            var Factory = function (config) {
                this.data = {};
                this.data.name = 'db';
                this.data.description = Factory.name;
                this.data.version = '1.0';
                this.data.size = 10 * 1024 * 1024;
                this.data.conn = null;
                this.data.ready = false;
                this.data.queue = [];

                if (config) {
                    if (config.name) this.data.name = config.name;
                    if (config.description) this.data.description = config.description;
                    if (config.version) this.data.version = config.version;
                    if (config.size) this.data.size = config.size * 1024 * 1024;
                }

                return this;
            };

            //----------------------------------------------------------------------------------------------------------
            // PROPERTIES
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.name = function () {
                return this.data.name;
            };

            Factory.prototype.description = function () {
                return this.data.description;
            };

            Factory.prototype.version = function () {
                return this.data.version;
            };

            Factory.prototype.size = function () {
                return this.data.size;
            };

            Factory.prototype.conn = function () {
                return this.data.conn;
            };

            Factory.prototype.opened = function () {
                return !!this.data.conn;
            };

            Factory.prototype.ready = function () {
                return this.data.ready;
            };

            //----------------------------------------------------------------------------------------------------------
            // HELPERS
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.prepareVar = function (v, toStr, clon) {
                if (v instanceof Date) {
                    v = v.toISOString();
                } else if (v === true) {
                    v = 1;
                } else if (v === false) {
                    v = 0;
                }
                if (toStr) {
                    if (v === undefined) {
                        v = '';
                    } else if (v === null) {
                        return 'NULL';
                    } else {
                        v = v.toString().replace(/'/g, "\\'");
                    }
                    if (clon) {
                        v = "'" + v + "'";
                    }
                }
                return v;
            };

            Factory.prototype.prepareParams = function (params) {
                if (!params || (params.length === 0)) {
                    return params;
                }
                var p2 = [];
                for (var i = 0; i < params.length; i++) {
                    p2.push(this.prepareVar(params[i]));
                }
                return p2;
            };

            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.open = function () {
                var me = this;
                var deferred = $q.defer();

                me.data.conn = typeof window.sqlitePlugin !== 'undefined'
                    ? window.sqlitePlugin.openDatabase({name: me.name(), version: me.version()})
                    : window.openDatabase(me.name(), me.version(), me.description(), me.size());

                if (DEBUG_DB)
                    console.info('$db(' + me.name() + '): opened');

                if (me.data.conn) {
                    $q.all(me.migrate()).then(function () {
                        me.data.ready = true;
                        if (DEBUG_DB)
                            console.info('$db(' + me.name() + '): ready');
                        me.queue().then(function () {
                            if (DEBUG_DB)
                                console.info('$db(' + me.name() + '): queue finished');
                            deferred.resolve();
                        });
                    });
                } else {
                    deferred.reject(new Error('database "' + me.name() + '" is not accessible.'));
                }

                return deferred.promise
            };

            Factory.prototype.migrate = function () {

                var me = this;
                var files = $migrations.files;
                var commands = $migrations.commands;
                var deferred = $q.defer();

                html5sql.database = me.data.conn;
                html5sql.readTransactionAvailable = typeof me.data.conn.readTransaction === 'function';
                if (DEBUG_DB)
                    html5sql.logInfo = html5sql.logErrors = true;

                if (DEBUG_DB)
                    console.info('$db(' + me.name() + '): processing migrations');

                var processFilesRecursively = function () {
                    var file = files.shift();
                    if (typeof file !== 'undefined') {
                        processFile(file);
                    } else {
                        if (DEBUG_DB)
                            console.info('$db(' + me.name() + '): migration finished');
                        deferred.resolve(true);
                    }
                };

                var processFile = function (file) {
                    $.get('' + file, function (sql) {
                        html5sql.process(sql, function () {
                            processFilesRecursively();
                        }, function (error) {
                            console.error('Erro ao processar a migration:', file, error);
                            processFilesRecursively();
                        });
                    }).fail(function (error, failingQuery) {
                        console.error('Erro ao recuperar o arquivo da migration:', file, error, failingQuery);
                        processFilesRecursively();
                    });
                };

                var processCommandsRecursively = function () {
                    var command = commands.shift();
                    if (typeof command !== 'undefined') {
                        processCommands(command);
                    } else {
                        processFilesRecursively();
                    }
                };

                var processCommands = function (command) {
                    me.run(command, {}, true).then(function () {
                        processCommandsRecursively();
                    }, function (error) {
                        console.error('Erro ao processar a migration:', command, error);
                        processCommandsRecursively();
                    });
                };

                me.run('SELECT file FROM migrations ORDER BY created_at', {}, true).then(function () {
                    if (DEBUG_DB)
                        console.info('$db(' + me.name() + '): up all migrations');
                    processCommandsRecursively();
                }, function () {
                    if (DEBUG_DB)
                        console.info('$db(' + me.name() + '): up new migrations');
                    processCommandsRecursively();
                });

                return deferred;
            };

            Factory.prototype.queue = function () {
                var me = this;
                var promises = [];

                if (DEBUG_DB)
                    console.info('$db(' + me.name() + '): processing queue');

                //for (var i = 0; i < this.data.queue.length; i++)
                //    promises.push(this.data.queue[i].apply(this));
                //this.data.queue = [];

                while (this.data.queue.length > 0) {
                    var fn = this.data.queue[0];
                    this.data.queue.splice(0, 1);
                    promises.push(fn.apply(this));
                }

                return $q.all(promises).then(function () {
                    if (DEBUG_DB)
                        console.info('$db(' + me.name() + '): queue finished');
                });
            };

            Factory.prototype.backup = function () {
                if (typeof window.sqlitePlugin !== 'undefined') {
                    var now = new Date();
                    window.sqlitePlugin.backupDatabase(now.getYear() + '.' + now.getMonth() + '.' + now.getDay() + '.' + this.name() + '.db.backup');
                }
            };

            //----------------------------------------------------------------------------------------------------------
            // RUN
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.done = function (fn, force) {
                if (!angular.isFunction(fn)) throw 'Error! "fn" is not a Function';

                if (this.ready() || force === true) {
                    fn.apply(this);
                } else {
                    if (DEBUG_DB)
                        console.log('$db(' + this.name() + '): push queue');
                    this.data.queue.push(fn);
                }

                return this;
            };

            Factory.prototype.run = function (sql, params, force) {
                var me = this;
                var deferred = $q.defer();

                var successCallback = null;
                deferred.promise.success = function (callback) {
                    successCallback = callback;
                    return deferred.promise;
                };

                var errorCallback = null;
                deferred.promise.error = function (callback) {
                    errorCallback = callback;
                    return deferred.promise;
                };

                var completeCallback = null;
                deferred.promise.complete = function (callback) {
                    completeCallback = callback;
                    return deferred.promise;
                };

                function onSuccess(SQLTransaction, SQLResultSet) {
                    if (successCallback) successCallback(SQLTransaction, SQLResultSet);
                    if (completeCallback) completeCallback(SQLTransaction, SQLResultSet);
                    deferred.resolve(SQLResultSet);
                }

                function onError(SQLTransaction, SQLError) {
                    if (errorCallback) errorCallback(SQLTransaction, SQLError);
                    if (completeCallback) completeCallback(SQLTransaction, null, SQLError);
                    console.error(SQLError);
                    deferred.reject(SQLError);
                }

                me.done(function () {
                    me.conn().transaction(function (SQLTransaction) {
                        var data = me.prepareParams(params);

                        if (DEBUG_DB)
                            console.info('$db(' + me.name() + '):', sql, data);

                        SQLTransaction.executeSql('' + sql, data, onSuccess, onError);
                    });
                    return deferred.promise;
                }, force);

                return deferred.promise;
            };

            //----------------------------------------------------------------------------------------------------------
            // DML
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.exists = function (table) {
                var promise = this.run("SELECT * FROM sqlite_master WHERE type='table' AND name=?", [table], true);

                var yesCallback = null;
                promise.yes = function (callback) {
                    yesCallback = callback;
                    return promise;
                };

                var noCallback = null;
                promise.no = function (callback) {
                    noCallback = callback;
                    return promise;
                };

                promise.then(function (SQLResultSet) {
                    if (SQLResultSet.rows.length > 0) {
                        if (yesCallback) yesCallback(SQLResultSet);
                    } else {
                        if (noCallback) noCallback(SQLResultSet);
                    }
                });

                return promise;
            };

            Factory.prototype.create = function (table, definition, data, drop) {
                var me = this;
                var deferred = $q.defer();

                var first = true;
                var sql = 'CREATE TABLE IF NOT EXISTS ' + table + ' (';
                angular.forEach(definition, function (config, field) {
                    first ? first = false : sql += ',';
                    sql += ' ' + field;

                    var type;
                    if (typeof config === 'string') {
                        type = config.toUpperCase();
                        config = {};
                    } else if (config) {
                        type = (config.type || '').toUpperCase();
                    } else {
                        config = {};
                    }

                    if (type === 'KEY') {
                        sql += ' INTEGER PRIMARY KEY AUTOINCREMENT';
                    } else {
                        // TYPE //
                        if (['BOOL', 'BOOLEAN', 'INTEGER', 'INT'].indexOf(type) >= 0) {
                            sql += ' INTEGER';
                        } else if (['REAL', 'DECIMAL', 'FLOAT'].indexOf(type) >= 0) {
                            sql += ' REAL';
                        } else if (['BLOB', 'LONGTEXT'].indexOf(type) >= 0) {
                            sql += ' BLOB';
                        } else {
                            sql += config.foreign ? ' INTEGER' : ' TEXT';
                        }

                        // PRIMARY //
                        if (config.primary)
                            sql += ' PRIMARY KEY';

                        // AUTOINCREMENT //
                        if (config.autoincrement)
                            sql += ' AUTOINCREMENT';

                        // UNIQUE //
                        if (config.unique)
                            sql += ' UNIQUE';

                        // IS NULL / NOT NULL //
                        sql += config.null === false ? ' NOT NULL' : ' NULL';

                        // DEFAULT //
                        if (config.default !== undefined)
                            sql += ' DEFAULT ' + this.prepareVar(config.default, true, true);

                        // FOREIGN //
                        if (config.foreign) {
                            if (typeof config.foreign === 'string') {
                                var parts = config.foreign.split('.');
                                config = {
                                    table: parts[0],
                                    key: parts[1] || 'id'
                                };
                            } else {
                                config = config.foreign;
                                if (!config.key) {
                                    config.key = 'id';
                                }
                            }
                            sql += ' REFERENCES ' + config.table + '(' + config.key + ')';
                        }
                    }
                });
                sql += ' )';

                function finish() {
                    deferred.resolve();
                }

                function create() {
                    me.run(sql, [], true).then(function () {
                        data && data.length > 0 ? me.ir(table, data, 'INSERT', true).then(finish) : finish();
                        finish();
                    });
                }

                drop ? me.drop(table).then(create) : me.exists(table).yes(finish).no(create);

                return deferred.promise;
            };

            Factory.prototype.alter = function (table, definition, data) {
                alert('$db.alter em construção...');
            };

            Factory.prototype.truncate = function (table) {
                return this.run('DELETE FROM ' + table, [], true);
            };

            Factory.prototype.drop = function (table) {
                return this.run('DROP TABLE IF EXISTS ' + table, [], true);
            };

            //----------------------------------------------------------------------------------------------------------
            // DML
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.select = function (table, columns, where, order, limit) {
                alert('$db.select em construção...');
            };

            Factory.prototype.ir = function (table, data, action, force) {
                var promises = [];

                var items = (data instanceof [].constructor) ? data : [data];
                for (var i = 0; i < items.length; i++) {
                    var item = items[i];
                    var columns = '', values = '', params = [];
                    for (var property in item) {
                        if (item.hasOwnProperty(property)) {
                            if (columns) {
                                columns += ', ';
                                values += ', ';
                            }
                            columns += property;
                            values += '?';
                            params.push(item[property]);
                        }
                    }

                    promises.push(this.run(action + ' INTO ' + table + ' (' + columns + ') VALUES (' + values + ')', params, force));
                }

                var all = $q.all(promises);

                var successCallback = null;
                all.success = function (callback) {
                    successCallback = callback;
                    return deferred.promise;
                };

                var errorCallback = null;
                all.error = function (callback) {
                    errorCallback = callback;
                    return deferred.promise;
                };

                var completeCallback = null;
                all.complete = function (callback) {
                    completeCallback = callback;
                    return deferred.promise;
                };

                function onSuccess(SQLTransaction, SQLResultSet) {
                    if (successCallback) successCallback(SQLTransaction, SQLResultSet);
                    if (completeCallback) completeCallback(SQLTransaction, SQLResultSet);
                }

                function onError(SQLTransaction, SQLError) {
                    if (errorCallback) errorCallback(SQLTransaction, SQLError);
                    if (completeCallback) completeCallback(SQLTransaction, null, SQLError);
                    console.error(SQLError);
                }

                return all.then(onSuccess, onError);
            };

            Factory.prototype.insert = function (table, data) {
                return this.ir(table, data, 'INSERT');
            };

            Factory.prototype.replace = function (table, data) {
                return this.ir(table, data, 'REPLACE');
            };

            Factory.prototype.update = function (table, data, where) {
                alert('$db.update em construção...');
            };

            Factory.prototype.delete = function (table, where) {
                alert('$db.delete em construção...');
            };

            Factory.prototype.deleteById = function (table, id) {
                return this.deleteBy(table, 'id', id);
            };

            Factory.prototype.deleteBy = function (table, field, value) {
                return this.run('DELETE FROM ' + table + ' WHERE ' + field + ' = ?', [value]);
            };

            //----------------------------------------------------------------------------------------------------------

            return Factory;

        }
    ]);

db.js

'use strict';

angular.module('shared')
    .factory('$db', ['$database', 'DEBUG_DB',
        function ($database, DEBUG_DB) {

            var $db = new $database({name: 'db'});

            $db.open().then(function () {
                if (DEBUG_DB)
                    console.log('$db(' + $db.name() + '): started');
            });

            return $db;

        }]);

migrations.js

'use strict';

angular.module('shared')
    .factory('$migrations', [
        function () {
            return {
                commands: [
                    'CREATE TABLE IF NOT EXISTS migrations (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, file LONGTEXT NOT NULL, created_at DATETIME NOT NULL, data BLOB NULL)',
                    'CREATE TABLE IF NOT EXISTS storage_local (id TEXT PRIMARY KEY UNIQUE NOT NULL, value LONGTEXT NULL)',
                    'CREATE TABLE IF NOT EXISTS storage_session (id TEXT PRIMARY KEY UNIQUE NOT NULL, value LONGTEXT NULL)',
                    'CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, title LONGTEXT NOT NULL, user_id UNSIGNED BIG INT, created_at DATETIME NOT NULL, data BLOB NULL)'
                ],
                files: [
                    // '/sql/2017-08-07-13-52-PROPOSALS.sql'
                ]
            }
        }]);

table.js

'use strict';

angular.module('shared')
    .factory('$table', ['$q', '$db',
        function ($q, $db) {

            var Factory = function (name) {
                var db = 'db';
                var table = name;

                var parts = name.toString().split('.');
                if (parts.length > 1) {
                    db = parts[0];
                    table = parts[1];
                }

                this.data = {};
                this.data.db = new $db({name: db});
                this.data.name = table;
            };

            //----------------------------------------------------------------------------------------------------------
            // PROPERTIES
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.db = function () {
                return this.data.db;
            };

            Factory.prototype.name = function () {
                return this.data.name;
            };

            //----------------------------------------------------------------------------------------------------------
            // DDL
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.exists = function () {
                return this.db().exists(this.name());
            };

            Factory.prototype.create = function (definition, data, drop) {
                return this.db().create(this.name(), definition, data, drop);
            };

            Factory.prototype.alter = function (definition, data) {
                return this.db().alter(this.name(), definition, data);
            };

            Factory.prototype.truncate = function () {
                return this.db().truncate(this.name());
            };

            Factory.prototype.drop = function () {
                return this.db().drop(this.name());
            };

            //----------------------------------------------------------------------------------------------------------
            // DML
            //----------------------------------------------------------------------------------------------------------

            Factory.prototype.all = function (columns, order) {
                return this.select(columns, null, order, null);
            };

            Factory.prototype.first = function (columns, where, order) {
                return this.select(columns, where, order, 1);
            };

            Factory.prototype.select = function (columns, where, order, limit) {
                return this.db().select(this.name(), columns, where, order, limit);
            };

            Factory.prototype.insert = function (data) {
                return this.db().insert(this.name(), data);
            };

            Factory.prototype.replace = function (data) {
                return this.db().replace(this.name(), data);
            };

            Factory.prototype.update = function (data, where) {
                return this.db().update(this.name(), data, where);
            };

            Factory.prototype.delete = function (where) {
                return this.db().delete(this.name(), where);
            };

            Factory.prototype.deleteById = function (id) {
                return this.db().deleteById(this.name(), id);
            };

            Factory.prototype.deleteBy = function (field, value) {
                return this.db().deleteBy(this.name(), field, value);
            };

            //----------------------------------------------------------------------------------------------------------

            return Factory;

        }]);