import Sencha Touch 2.4.2 source

[sencha-touch.git] / src / src / data / proxy / Sql.js

/**
 * SQL proxy lets you store data in a SQL database.
 * The Sencha Touch SQL proxy outputs model data into an HTML5
 * local database using WebSQL.
 *
 * You can create a Store for the proxy, for example:
 *
 *     Ext.require(["Ext.data.proxy.SQL"]);
 *
 *     Ext.define("User", {
 *        extend: "Ext.data.Model",
 *        config: {
 *           fields: [ "firstName", "lastName" ]
 *        }
 *      });
 *
 *     Ext.create("Ext.data.Store", {
 *        model: "User",
 *        storeId: "Users",
 *        proxy: {
 *           type: "sql"
 *        }
 *     });
 *
 *     Ext.getStore("Users").add({
 *        firstName: "Polly",
 *        lastName: "Hedra"
 *     });
 *
 *     Ext.getStore("Users").sync();
 *
 * To destroy a table use:
 *
 *     Ext.getStore("Users").getProxy().dropTable();
 *
 * To recreate a table use:
 *
 *     Ext.data.Store.sync() or Ext.data.Model.save()
 */
Ext.define('Ext.data.proxy.Sql', {
    alias: 'proxy.sql',
    extend: 'Ext.data.proxy.Client',
    alternateClassName: 'Ext.data.proxy.SQL',

    isSQLProxy: true,

    config: {
        /**
         * @cfg {Object} reader
         * @hide
         */
        reader: null,
        /**
         * @cfg {Object} writer
         * @hide
         */
        writer: null,
        /**
         * @cfg {String} table
         * Optional Table name to use if not provided ModelName will be used
         */
        table: null,
        /**
         * @cfg {String} database
         * Database name to access tables from
         */
        database: 'Sencha',

        columns: '',

        uniqueIdStrategy: false,

        tableExists: false,

        defaultDateFormat: 'Y-m-d H:i:s.u'
    },

    updateModel: function(model) {
        if (model) {
            var modelName = model.modelName,
                defaultDateFormat = this.getDefaultDateFormat(),
                table = modelName.slice(modelName.lastIndexOf('.') + 1);

            model.getFields().each(function (field) {
                if (field.getType().type === 'date' && !field.getDateFormat()) {
                    field.setDateFormat(defaultDateFormat);
                }
            });

            this.setUniqueIdStrategy(model.getIdentifier().isUnique);
            if (!this.getTable()) {
                this.setTable(table);
            }
            this.setColumns(this.getPersistedModelColumns(model));
        }

        this.callParent(arguments);
    },

    setException: function(operation, error) {
        operation.setException(error);
    },

    create: function (operation, callback, scope) {
        var me = this,
            db = me.getDatabaseObject(),
            records = operation.getRecords(),
            tableExists = me.getTableExists();

        operation.setStarted();

        db.transaction(function(transaction) {
                if (!tableExists) {
                    me.createTable(transaction);
                }

                me.insertRecords(records, transaction, function(resultSet, error) {
                    if (operation.process(operation.getAction(), resultSet) === false) {
                        me.fireEvent('exception', me, operation);
                    }

                    if (error) {
                        operation.setException(error);
                    }
                }, me);
            },
            function(transaction, error) {
                me.setException(operation, error);
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            },
            function(transaction) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            }
        );
    },

    read: function(operation, callback, scope) {
        var me = this,
            db = me.getDatabaseObject(),
            model = me.getModel(),
            idProperty = model.getIdProperty(),
            tableExists = me.getTableExists(),
            params = operation.getParams() || {},
            id = params[idProperty],
            sorters = operation.getSorters(),
            filters = operation.getFilters(),
            page = operation.getPage(),
            start = operation.getStart(),
            limit = operation.getLimit(),
            filtered, i, ln;

        params = Ext.apply(params, {
            page: page,
            start: start,
            limit: limit,
            sorters: sorters,
            filters: filters
        });

        operation.setStarted();

        db.transaction(function(transaction) {
                if (!tableExists) {
                    me.createTable(transaction);
                }

                me.selectRecords(transaction, id !== undefined ? id : params, function (resultSet, error) {
                    if (operation.process(operation.getAction(), resultSet) === false) {
                        me.fireEvent('exception', me, operation);
                    }

                    if (error) {
                        operation.setException(error);
                    }

                    if (filters && filters.length) {
                        filtered = Ext.create('Ext.util.Collection', function(record) {
                            return record.getId();
                        });
                        filtered.setFilterRoot('data');
                        for (i = 0, ln = filters.length; i < ln; i++) {
                            if (filters[i].getProperty() === null) {
                                filtered.addFilter(filters[i]);
                            }
                        }
                        filtered.addAll(operation.getRecords());

                        operation.setRecords(filtered.items.slice());
                        resultSet.setRecords(operation.getRecords());
                        resultSet.setCount(filtered.items.length);
                        resultSet.setTotal(filtered.items.length);
                    }
                });
            },
            function(transaction, error) {
                me.setException(operation, error);
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            },
            function(transaction) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            }
        );
    },

    update: function(operation, callback, scope) {
        var me = this,
            records = operation.getRecords(),
            db = me.getDatabaseObject(),
            tableExists = me.getTableExists();

        operation.setStarted();

        db.transaction(function (transaction) {
                if (!tableExists) {
                    me.createTable(transaction);
                }

                me.updateRecords(transaction, records, function(resultSet, errors) {
                    if (operation.process(operation.getAction(), resultSet) === false) {
                        me.fireEvent('exception', me, operation);
                    }

                    if (errors) {
                       operation.setException(errors);
                    }
                });
            },
            function(transaction, error) {
                me.setException(operation, error);
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            },
            function(transaction) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            }
        );
    },

    destroy: function(operation, callback, scope) {
        var me = this,
            records = operation.getRecords(),
            db = me.getDatabaseObject(),
            tableExists = me.getTableExists();

        operation.setStarted();

        db.transaction(function(transaction) {
                if (!tableExists) {
                    me.createTable(transaction);
                }

                me.destroyRecords(transaction, records, function(resultSet, error) {
                    if (operation.process(operation.getAction(), resultSet) === false) {
                        me.fireEvent('exception', me, operation);
                    }

                    if (error) {
                       operation.setException(error);
                    }
                });
            },
            function(transaction, error) {
                me.setException(operation, error);
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            },
            function(transaction) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, operation);
                }
            }
        );
    },

    createTable: function (transaction) {
        transaction.executeSql('CREATE TABLE IF NOT EXISTS ' + this.getTable() + ' (' + this.getSchemaString() + ')');
        this.setTableExists(true);
    },

    insertRecords: function(records, transaction, callback, scope) {
        var me = this,
            table = me.getTable(),
            columns = me.getColumns(),
            totalRecords = records.length,
            executed = 0,
            tmp = [],
            insertedRecords = [],
            errors = [],
            uniqueIdStrategy = me.getUniqueIdStrategy(),
            i, ln, placeholders, result;

        result = new Ext.data.ResultSet({
            records: insertedRecords,
            success: true
        });

        for (i = 0, ln = columns.length; i < ln; i++) {
            tmp.push('?');
        }
        placeholders = tmp.join(', ');

        Ext.each(records, function (record) {
            var id = record.getId(),
                data = me.getRecordData(record),
                values = me.getColumnValues(columns, data);

            transaction.executeSql(
                'INSERT INTO ' + table + ' (' + columns.join(', ') + ') VALUES (' + placeholders + ')', values,
                function (transaction, resultSet) {
                    executed++;
                    insertedRecords.push({
                        clientId: id,
                        id: uniqueIdStrategy ? id : resultSet.insertId,
                        data: data,
                        node: data
                    });

                    if (executed === totalRecords && typeof callback == 'function') {
                        callback.call(scope || me, result, errors.length > 0 ? errors : null);
                    }
                },
                function (transaction, error) {
                    executed++;
                    errors.push({
                        clientId: id,
                        error: error
                    });

                    if (executed === totalRecords && typeof callback == 'function') {
                        callback.call(scope || me, result, errors);
                    }
                }
            );
        });
    },

    selectRecords: function(transaction, params, callback, scope) {
        var me = this,
            table = me.getTable(),
            idProperty = me.getModel().getIdProperty(),
            sql = 'SELECT * FROM ' + table,
            records = [],
            filterStatement = ' WHERE ',
            sortStatement = ' ORDER BY ',
            i, ln, data, result, count, rows, filter, sorter, property, value;

        result = new Ext.data.ResultSet({
            records: records,
            success: true
        });

        if (!Ext.isObject(params)) {
            sql += filterStatement + idProperty + ' = ' + params;
        } else {
            ln = params.filters && params.filters.length;
            if (ln) {
                for (i = 0; i < ln; i++) {
                    filter = params.filters[i];
                    property = filter.getProperty();
                    value = filter.getValue();
                    if (property !== null) {
                        sql += filterStatement + property + ' ' + (filter.getAnyMatch() ? ('LIKE \'%' + value + '%\'') : ('= \'' + value + '\''));
                        filterStatement = ' AND ';
                    }
                }
            }

            ln = params.sorters && params.sorters.length;
            if (ln) {
                for (i = 0; i < ln; i++) {
                    sorter = params.sorters[i];
                    property = sorter.getProperty();
                    if (property !== null) {
                        sql += sortStatement + property + ' ' + sorter.getDirection();
                        sortStatement = ', ';
                    }
                }
            }

            // handle start, limit, sort, filter and group params
            if (params.page !== undefined) {
                sql += ' LIMIT ' + parseInt(params.start, 10) + ', ' + parseInt(params.limit, 10);
            }
        }
        transaction.executeSql(sql, null,
            function(transaction, resultSet) {
                rows = resultSet.rows;
                count = rows.length;

                for (i = 0, ln = count; i < ln; i++) {
                    data = rows.item(i);
                    records.push({
                        clientId: null,
                        id: data[idProperty],
                        data: data,
                        node: data
                    });
                }

                result.setSuccess(true);
                result.setTotal(count);
                result.setCount(count);

                if (typeof callback == 'function') {
                    callback.call(scope || me, result);
                }
            },
            function(transaction, error) {
                result.setSuccess(false);
                result.setTotal(0);
                result.setCount(0);

                if (typeof callback == 'function') {
                    callback.call(scope || me, result, error);
                }
            }
        );
    },

    updateRecords: function (transaction, records, callback, scope) {
        var me = this,
            table = me.getTable(),
            columns = me.getColumns(),
            totalRecords = records.length,
            idProperty = me.getModel().getIdProperty(),
            executed = 0,
            updatedRecords = [],
            errors = [],
            i, ln, result;

        result = new Ext.data.ResultSet({
            records: updatedRecords,
            success: true
        });

        Ext.each(records, function (record) {
            var id = record.getId(),
                data = me.getRecordData(record),
                values = me.getColumnValues(columns, data),
                updates = [];

            for (i = 0, ln = columns.length; i < ln; i++) {
                updates.push(columns[i] + ' = ?');
            }

            transaction.executeSql(
                'UPDATE ' + table + ' SET ' + updates.join(', ') + ' WHERE ' + idProperty + ' = ?', values.concat(id),
                function (transaction, resultSet) {
                    executed++;
                    updatedRecords.push({
                        clientId: id,
                        id: id,
                        data: data,
                        node: data
                    });

                    if (executed === totalRecords && typeof callback == 'function') {
                        callback.call(scope || me, result, errors.length > 0 ? errors : null);
                    }
                },
                function (transaction, error) {
                    executed++;
                    errors.push({
                        clientId: id,
                        error: error
                    });

                    if (executed === totalRecords && typeof callback == 'function') {
                        callback.call(scope || me, result, errors);
                    }
                }
            );
        });
    },

    destroyRecords: function (transaction, records, callback, scope) {
        var me = this,
            table = me.getTable(),
            idProperty = me.getModel().getIdProperty(),
            ids = [],
            values = [],
            destroyedRecords = [],
            i, ln, result, record;

        for (i = 0, ln = records.length; i < ln; i++) {
            ids.push(idProperty + ' = ?');
            values.push(records[i].getId());
        }

        result = new Ext.data.ResultSet({
            records: destroyedRecords,
            success: true
        });

        transaction.executeSql(
            'DELETE FROM ' + table + ' WHERE ' + ids.join(' OR '), values,
            function (transaction, resultSet) {
                for (i = 0, ln = records.length; i < ln; i++) {
                    record = records[i];
                    destroyedRecords.push({
                        id: record.getId()
                    });
                }

                if (typeof callback == 'function') {
                    callback.call(scope || me, result);
                }
            },
            function (transaction, error) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, result, error);
                }
            }
        );
    },

    /**
     * Formats the data for each record before sending it to the server. This
     * method should be overridden to format the data in a way that differs from the default.
     * @param {Object} record The record that we are writing to the server.
     * @return {Object} An object literal of name/value keys to be written to the server.
     * By default this method returns the data property on the record.
     */
    getRecordData: function (record) {
        var me = this,
            fields = record.getFields(),
            idProperty = record.getIdProperty(),
            uniqueIdStrategy = me.getUniqueIdStrategy(),
            data = {},
            name, value;

        fields.each(function (field) {
            if (field.getPersist()) {
                name = field.getName();
                if (name === idProperty && !uniqueIdStrategy) {
                    return;
                }
                value = record.get(name);
                if (field.getType().type == 'date') {
                    value = me.writeDate(field, value);
                }
                data[name] = value;
            }
        }, me);

        return data;
    },

    getColumnValues: function(columns, data) {
        var ln = columns.length,
            values = [],
            i, column, value;

        for (i = 0; i < ln; i++) {
            column = columns[i];
            value = data[column];
            if (value !== undefined) {
                values.push(value);
            }
        }

        return values;
    },

    getSchemaString: function() {
        var me = this,
            schema = [],
            model = me.getModel(),
            idProperty = model.getIdProperty(),
            fields = model.getFields().items,
            uniqueIdStrategy = me.getUniqueIdStrategy(),
            ln = fields.length,
            i, field, type, name;

        for (i = 0; i < ln; i++) {
            field = fields[i];
            type = field.getType().type;
            name = field.getName();

            if (name === idProperty) {
                if (uniqueIdStrategy) {
                    type = me.convertToSqlType(type);
                    schema.unshift(idProperty + ' ' + type);
                } else {
                    schema.unshift(idProperty + ' INTEGER PRIMARY KEY AUTOINCREMENT');
                }
            } else {
                type = me.convertToSqlType(type);
                schema.push(name + ' ' + type);
            }
        }

        return schema.join(', ');
    },

    getPersistedModelColumns: function(model) {
        var fields = model.getFields().items,
            uniqueIdStrategy = this.getUniqueIdStrategy(),
            idProperty = model.getIdProperty(),
            columns = [],
            ln = fields.length,
            i, field, name;

        for (i = 0; i < ln; i++) {
            field = fields[i];
            name = field.getName();

            if (name === idProperty && !uniqueIdStrategy) {
                continue;
            }

            if (field.getPersist()) {
                columns.push(field.getName());
            }
        }
        return columns;
    },

    convertToSqlType: function(type) {
        switch (type.toLowerCase()) {
            case 'date':
            case 'string':
            case 'auto':
                return 'TEXT';
            case 'int':
                return 'INTEGER';
            case 'float':
                return 'REAL';
            case 'bool':
                return 'NUMERIC';
        }
    },

    writeDate: function (field, date) {
        if (Ext.isEmpty(date)) {
            return null;
        }

        var dateFormat = field.getDateFormat() || this.getDefaultDateFormat();
        switch (dateFormat) {
            case 'timestamp':
                return date.getTime() / 1000;
            case 'time':
                return date.getTime();
            default:
                return Ext.Date.format(date, dateFormat);
        }
    },

    dropTable: function(config) {
        var me = this,
            table = me.getTable(),
            callback = config ? config.callback : null,
            scope = config ? config.scope || me : null,
            db = me.getDatabaseObject();

        db.transaction(function(transaction) {
                transaction.executeSql('DROP TABLE ' + table);
            },
            function(transaction, error) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, false, table, error);
                }
            },
            function(transaction) {
                if (typeof callback == 'function') {
                    callback.call(scope || me, true, table);
                }
            }
        );

        me.setTableExists(false);
    },

    getDatabaseObject: function() {
        return openDatabase(this.getDatabase(), '1.0', 'Sencha Database', 5 * 1024 * 1024);
    }
});