Some bug fixes about ext.data.proxy.websql HOT 4 CLOSED

/**
 * @class App.ux.data.proxy.WebSql
 * @author Paul van Santen
 * @author Grgur Grisogono
 *
 * WebSQL proxy connects models and stores to local WebSQL database.
 *
 * WebSQL is deprecated in favor of indexedDb, so use with caution.
 * IndexedDb is not available for mobile browsers at the time of writing this code
 *
 * Version: 0.3
 *
 * TODO:
 *      filters,
 *      failover option for remote proxies,
 *      database version migration
 */
Ext.define('App.ux.data.proxy.WebSql', {
    extend: 'Ext.data.proxy.Proxy',
    alias: 'proxy.websql',

    config: {
        /**
         * @cfg {String} version
         * database version. If different than current, use updatedb event to update database
         */
        dbVersion: '1.0',

        /**
         * @cfg {String} dbName
         * Name of database
         */
        dbName: undefined,

        /**
         * @cfg {String} dbDescription
         * Description of the database
         */
        dbDescription: '',

        /**
         * @cfg {String} dbSize
         * Max storage size in bytes
         */
        dbSize: 5 * 1024 * 1024,

        /**
         * @cfg {String} dbTable
         * Name for table where all the data will be stored
         */
        dbTable: undefined,

        /**
         * @cfg {String} pkField
         * Primary key name. Defaults to idProperty
         */
        pkField: undefined,

        /**
         * @cfg {String} pkType
         * Type of primary key. By default it an autoincrementing integer
         */
        pkType: 'INTEGER PRIMARY KEY ASC'
    },
    /**
     * @type {Database} database
     * @private
     * db object
     */
    database: undefined,

    /**
     * Creates the proxy, throws an error if local storage is not supported in the current browser.
     * @param {Object} config (optional) Config object.
     */
    constructor: function (config) {
        this.initConfig(config);
        this.callParent(arguments);
    },

    /**
     * Executes a query
     * @param {SQLTransaction} transaction
     * @param {string} query
     * @param {Array} [args]
     * @param {function} [success]
     * @param {function} [fail]
     * @param {object} [scope]
     */
    doQuery: function (transaction, query, args, success, fail, scope) {
        if (!Ext.isFunction(success)) {
            success = Ext.emptyFn;
        }
        //<debug>
//        console.log('EXEC_QUERY: ', query, args);
        //</debug>
        transaction.executeSql(query, args,
            Ext.bind(success, scope || this),
            Ext.bind(this.queryError, this, [query, args, fail, scope], true)
        );
    },

    /**
     * Called when a query has an error
     * @param {SQLTransaction} transaction
     * @param {SQLError} error
     * @param {string} query
     * @param {Array} args
     * @param {function} fail
     * @param {object} scope
     */
    queryError: function (transaction, error, query, args, fail, scope) {
        if (Ext.isFunction(fail)) {
            fail.call(scope || this, transaction, error);
        }
        //<debug>
        console.log('QUERY_ERROR: ', error, query, args);
        //</debug>
        throw {
            code: 'websql_error',
            message: "SQL error: " + error.message + "\nSQL query: " + query + (args && args.length ? ("\nSQL params: " + args.join(', ')) : '' ),
            query: query,
            params: args
        };
    },

    /**
     * @private
     * Sets up the Proxy by opening database and creating table if necessary
     */
    initialize: function () {
        var me = this,
            pk = 'id',
            db = openDatabase(me.getDbName(), me.getDbVersion(), me.getDbDescription(), me.getDbSize()),
            query;

        this.database = db;

        db.transaction(function (transaction) {
            pk = me.getPkField() || (me.getReader() && me.getReader().getIdProperty()) || pk;
            me.setPkField(pk);

            query = 'CREATE TABLE IF NOT EXISTS ' + me.getDbTable() + '(' + pk + ' ' + me.getPkType() + ', ' + me.constructFields().join(', ') + ')';

            me.doQuery(transaction, query);
        });
    },

    /**
     * @private
     * Get reader data and set up fields accordingly
     * Used for table creation only
     * @return {Array} Fields array
     */
    constructFields: function () {
        var me = this,
            flatFields = [],
            pkField = me.getPkField().toLowerCase(),
            sqlType;

        me.getModel().getFields().each(function (field) {
            var name = field.getName(),
                type = field.getType().type;

            if (name === pkField) {
                return;
            }

            switch (type.toLowerCase()) {
                case 'int':
                    sqlType = 'INTEGER';
                    break;
                case 'float':
                    sqlType = 'FLOAT';
                    break;
                case 'date':
                    sqlType = 'DATETIME';
                    break;
                case 'boolean':
                    sqlType = 'BOOLEAN';
                    break;
                default:
                    sqlType = 'TEXT';
                    break;
            }

            flatFields.push(name + ' ' + sqlType);
        });

        return flatFields;
    },
    /**
     * Gets an object of the form {key -> value} for every field in the database table
     * Called before saving a record to determine db structure
     * @param {Array} fields
     * @param {Ext.data.Model} record
     * @returns {object}
     */
    getDbFieldData: function(fields, record) {
        var object = {};
        for (var i = 0; i < fields.length; i++) {
            object[fields[i]] = record.get(fields[i]);
        }
        return object;
    },

    /**
     * Inserts or replaces the records of an operation
     * @param {Ext.data.Operation} operation
     * @param {function} callback
     * @param {object} scope
     */
    insertOrReplace: function(operation, callback, scope) {
        var me = this,
            records = operation.getRecords();

        operation.setStarted();

        me.database.transaction(function (transaction) {
            if (!records.length) {
                return;
            }

            var fields = [],
                query,
                queryParts = [],
                recordQueryParts,
                placeholders = [],
                args = [],
                firstRecord = true,
                dbFieldData;

            me.getModel().getFields().each(function(field) {
                fields.push(field.getName());
                placeholders.push('?');
            });

            /**
             * We have to make use of the quirky SQLite multiple insert syntax here
             * @see http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database
             * @private
             */

            query = 'INSERT OR REPLACE INTO ' + me.getDbTable();

            for (var i = 0; i < records.length; i++) {
                recordQueryParts = [];
                dbFieldData = me.getDbFieldData(fields, records[i]);

                if (firstRecord) {
                    for (var key in dbFieldData) {
                        if (dbFieldData.hasOwnProperty(key)) {
                            recordQueryParts.push('? AS ' + key);
                            args.push(dbFieldData[key]);
                        }
                    }
                    query += ' SELECT ' + recordQueryParts.join(', ');
                    firstRecord = false;
                } else {
                    for (var key in dbFieldData) {
                        if (dbFieldData.hasOwnProperty(key)) {
                            recordQueryParts.push('?');
                            args.push(dbFieldData[key]);
                        }
                    }
                    queryParts.push(' UNION SELECT ' + recordQueryParts.join(', '));
                }
            }
            query += queryParts.join("\n");

            me.doQuery(transaction, query, args);

        }, function (/** @private @type {SQLError} error **/ error) {
            // Error
            operation.setException(error);

            if (Ext.isFunction(callback)) {
                callback.call(scope || this, operation);
            }
        }, function () {
            // Success
            operation.setSuccessful();
            operation.setCompleted();

            if (Ext.isFunction(callback)) {
                callback.call(scope || this, operation);
            }
        });
    },

    /**
     * Called when records are created
     * @param {Ext.data.Operation} operation
     * @param {function} callback
     * @param {object} scope
     */
    create: function (operation, callback, scope) {
        this.insertOrReplace(operation, callback, scope);
    },

    /**
     * Updates records
     * @param {Ext.data.Operation} operation
     * @param {function} callback
     * @param {object} scope
     */
    update: function (operation, callback, scope) {
        this.insertOrReplace(operation, callback, scope);
    },

    /**
     * Reads a record from a DB row
     * @param {SQLResultSetRowList} row
     * @return {object} Model object
     */
    readRecordFromRow: function(row) {
        return row;
    },

    /**
     * Reads one or more records
     * @param {Ext.data.Operation} operation
     * @param {function} callback
     * @param {object} scope
     */
    read: function (operation, callback, scope) {
        var me = this,
            records = [],
            params = operation.getParams() || {},
            wheres = [],
            sorters = operation.getSorters(),
            orderBy = [],
            args = [],
            limit = operation.getLimit(),
            start = operation.getStart();

        var query = 'SELECT * FROM ' + me.getDbTable();

        if (params) {
            for (var key in params) {
                if (params.hasOwnProperty(key)) {
                    wheres.push(key + ' = ? ');
                    args.push(params[key]);
                }
            }
        }

        if (wheres.length) {
            query += ' WHERE ' + wheres.join(' AND ');
        }

        if (sorters) {
            for (var i = 0; i < sorters.length; i++) {
                orderBy.push(sorters[i].getProperty() + ' ' + sorters[i].getDirection());
            }
        }

        if (orderBy.length) {
            query += ' ORDER BY ' + orderBy.join(' ');
        }

        if (limit || start) {
            start = start || 0;
            query += ' LIMIT ' + limit + ' OFFSET ' + start;
        }

        me.database.transaction(function (transaction) {
            me.doQuery(transaction, query, args, function (/** @private @type {SQLTransaction} transaction */ transaction, /** @private @type {SQLResultSet} resultset */ resultset) {
                var length = resultset.rows.length,
                    row;

                for (var i = 0; i < length; i++) {
                    row = resultset.rows.item(i);
                    records.push(
                        me.readRecordFromRow(row)
                    );
                }

                operation.setCompleted();

                operation.setResultSet(Ext.create('Ext.data.ResultSet', {
                    records: records,
                    total: records.length,
                    loaded: true
                }));
                operation.setRecords(records);
                operation.setSuccessful();

                if (typeof callback == 'function') {
                    callback.call(scope || this, operation);
                }
            });

        }, function (/** @type {SQLError} error **/ error) {
            // Error
            operation.setException(error);

            if (Ext.isFunction(callback)) {
                callback.call(scope || this, operation);
            }
        });
    },

    /**
     * Destroys records
     * @param {Ext.data.Operation} operation
     * @param {function} callback
     * @param {object} scope
     */
    destroy: function (operation, callback, scope) {
        var me = this,
            ids = [],
            wheres = [],
            records = operation.getRecords(),
            query;

        for (var i = 0; i < records.length; i++) {
            wheres.push(me.getPkField() + ' = ? ');
            ids.push(records[i].get(me.getPkField()));
        }

        query = 'DELETE FROM ' + me.getDbTable() + ' WHERE ' + wheres.join(' OR ');

        me.database.transaction(function (transaction) {
            me.doQuery(transaction, query, ids);
        }, function (/** @private @type {SQLError} error **/ error) {
            // Error
            operation.setException(error);

            if (Ext.isFunction(callback)) {
                callback.call(scope || this, operation);
            }
        }, function () {
            // Success
            operation.setSuccessful();
            operation.setCompleted();

            if (Ext.isFunction(callback)) {
                callback.call(scope || this, operation);
            }
        });
    }
});