add extjs 6.0.1 sources

[extjs.git] / extjs / packages / legacy / src / data / proxy / Sql.js

/**\r
 * SQL proxy lets you store data in a SQL database.\r
 * The Sencha Touch SQL proxy outputs model data into an HTML5\r
 * local database using WebSQL.\r
 *\r
 * You can create a Store for the proxy, for example:\r
 *\r
 *     Ext.require(["Ext.data.proxy.SQL"]);\r
 *\r
 *     Ext.define("User", {\r
 *        extend: "Ext.data.Model",\r
 *        config: {\r
 *           fields: [ "firstName", "lastName" ]\r
 *        }\r
 *      });\r
 *\r
 *     Ext.create("Ext.data.Store", {\r
 *        model: "User",\r
 *        storeId: "Users",\r
 *        proxy: {\r
 *           type: "sql"\r
 *        }\r
 *     });\r
 *\r
 *     Ext.getStore("Users").add({\r
 *        firstName: "Polly",\r
 *        lastName: "Hedra"\r
 *     });\r
 *\r
 *     Ext.getStore("Users").sync();\r
 *\r
 * To destroy a table use:\r
 *\r
 *     Ext.getStore("Users").getProxy().dropTable();\r
 *\r
 * To recreate a table use:\r
 *\r
 *     Ext.data.Store.sync() or Ext.data.Model.save()\r
 */\r
Ext.define('Ext.data.proxy.Sql', {\r
    alias: 'proxy.sql',\r
    extend: 'Ext.data.proxy.Client',\r
    alternateClassName: 'Ext.data.proxy.SQL',\r
\r
    isSQLProxy: true,\r
\r
    config: {\r
        /**\r
         * @cfg {Object} reader\r
         * @hide\r
         */\r
        reader: null,\r
        /**\r
         * @cfg {Object} writer\r
         * @hide\r
         */\r
        writer: null,\r
        /**\r
         * @cfg {String} table\r
         * Optional Table name to use if not provided ModelName will be used\r
         */\r
        table: null,\r
        /**\r
         * @cfg {String} database\r
         * Database name to access tables from\r
         */\r
        database: 'Sencha'\r
    },\r
\r
    _createOptions: {\r
        silent: true,\r
        dirty: false\r
    },\r
\r
    updateModel: function(model) {\r
        var me = this,\r
            modelName,\r
            len, i, columns, quoted;\r
\r
        if (model) {\r
            me.uniqueIdStrategy = model.identifier.isUnique;\r
            if (!me.getTable()) {\r
                modelName = model.entityName;\r
                me.setTable(modelName.slice(modelName.lastIndexOf('.') + 1));\r
            }\r
            me.columns = columns = me.getPersistedModelColumns(model);\r
            me.quotedColumns = quoted = [];\r
            for (i = 0, len = columns.length; i < len; ++i) {\r
                quoted.push('"' + columns[i] + '"');\r
            }\r
        }\r
\r
        me.callParent([model]);\r
    },\r
\r
    setException: function(operation, error) {\r
        operation.setException(error);\r
    },\r
\r
    create: function (operation) {\r
        var me = this,\r
            records = operation.getRecords(),\r
            result, error;\r
\r
        operation.setStarted();\r
\r
        me.executeTransaction(function(transaction) {\r
            me.insertRecords(records, transaction, function (resultSet, statementError) {\r
                result = resultSet;\r
                error = statementError;\r
            });\r
        }, function(transactionError) {\r
            operation.setException(transactionError);\r
        }, function() {\r
            if (error) {\r
                operation.setException(statementError);\r
            } else {\r
                operation.process(result);\r
            }\r
        });\r
    },\r
\r
    read: function(operation) {\r
        var me = this,\r
            model = me.getModel(),\r
            records = operation.getRecords(),\r
            record = records ? records[0] : null,\r
            result, error, id, params;\r
\r
        if (record && !record.phantom) {\r
            id = record.getId();\r
        } else {\r
            id = operation.getId();\r
        }\r
\r
        if (id !== undefined) {\r
            params = {\r
                idOnly: true,\r
                id: id\r
            }\r
        } else {\r
            params = {\r
                page: operation.getPage(),\r
                start: operation.getStart(),\r
                limit: operation.getLimit(),\r
                sorters: operation.getSorters(),\r
                filters: operation.getFilters()\r
            };\r
        }\r
\r
        operation.setStarted();\r
\r
        me.executeTransaction(function(transaction) {\r
            me.selectRecords(transaction, params, function (resultSet, statementError) {\r
                result = resultSet;\r
                error = statementError;\r
            });\r
        }, function(transactionError) {\r
            operation.setException(transactionError);\r
        }, function() {\r
            if (error) {\r
                operation.setException(statementError);\r
            } else {\r
                operation.process(result);\r
            }\r
        });\r
    },\r
\r
    update: function(operation) {\r
        var me = this,\r
            records = operation.getRecords(),\r
            result, error;\r
\r
        operation.setStarted();\r
\r
        me.executeTransaction(function(transaction) {\r
            me.updateRecords(transaction, records, function (resultSet, statementError) {\r
                result = resultSet;\r
                error = statementError;\r
            });\r
        }, function(transactionError) {\r
            operation.setException(transactionError);\r
        }, function() {\r
            if (error) {\r
                operation.setException(statementError);\r
            } else {\r
                operation.process(result);\r
            }\r
        });\r
    },\r
\r
    erase: function(operation) {\r
        var me = this,\r
            records = operation.getRecords(),\r
            result, error;\r
\r
        operation.setStarted();\r
\r
        me.executeTransaction(function(transaction) {\r
            me.destroyRecords(transaction, records, function(resultSet, statementError) {\r
                result = resultSet;\r
                error = statementError;\r
            });\r
        }, function(transactionError) {\r
            operation.setException(transactionError);\r
        }, function() {\r
            if (error) {\r
                operation.setException(error);\r
            } else {\r
                operation.process(result);\r
            }\r
        });\r
    },\r
\r
    createTable: function (transaction) {\r
        var me = this;\r
        if (!transaction) {\r
            me.executeTransaction(function(transaction) {\r
                me.createTable(transaction);\r
            });\r
            return;\r
        }\r
        me.executeStatement(transaction, 'CREATE TABLE IF NOT EXISTS "' + me.getTable() + '" (' + me.getSchemaString() + ')', function() {\r
            me.tableExists = true;\r
        });\r
    },\r
\r
    insertRecords: function(records, transaction, callback) {\r
        var me = this,\r
            columns = me.columns,\r
            totalRecords = records.length,\r
            executed = 0,\r
            uniqueIdStrategy = me.uniqueIdStrategy,\r
            setOptions = me._createOptions,\r
            len = records.length,\r
            i, record, placeholders, sql, data, values, errors, completeIf;\r
\r
        completeIf = function(transaction) {\r
            ++executed;\r
            if (executed === totalRecords) {\r
                callback.call(me, new Ext.data.ResultSet({\r
                    success: !errors\r
                }), errors);\r
            }\r
        };\r
        placeholders = Ext.String.repeat('?', columns.length, ',');\r
        sql =  'INSERT INTO "' + me.getTable() + '" (' + me.quotedColumns.join(',') + ') VALUES (' + placeholders + ')';\r
\r
        for (i = 0; i < len; ++i) {\r
            record = records[i];\r
            data = me.getRecordData(record);\r
            values = me.getColumnValues(columns, data);\r
\r
            // Capture the record in closure scope so we can access it later\r
            (function(record) {\r
                me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r
                    if (!uniqueIdStrategy) {\r
                        record.setId(resultSet.insertId, setOptions);\r
                    }\r
                    completeIf();\r
                }, function(transaction, error) {\r
                    if (!errors) {\r
                        errors = [];\r
                    }\r
                    errors.push(error);\r
                    completeIf();\r
                });\r
            })(record);\r
        }\r
    },\r
\r
    selectRecords: function(transaction, params, callback, scope) {\r
        var me = this,\r
            Model = me.getModel(),\r
            idProperty = Model.idProperty,\r
            sql = 'SELECT * FROM "' + me.getTable() + '"',\r
            filterStatement = ' WHERE ',\r
            sortStatement = ' ORDER BY ',\r
            values = [],\r
            sorters, filters, placeholder,\r
            i, len, result, filter, sorter, property, operator, value;\r
\r
        if (params.idOnly) {\r
            sql += filterStatement + '"' + idProperty + '" = ?';\r
            values.push(params);\r
        } else {\r
            filters = params.filters;\r
            len = filters && filters.length;\r
            if (len) {\r
                for (i = 0; i < len; i++) {\r
                    filter = filters[i];\r
                    property = filter.getProperty();\r
                    value = me.toSqlValue(filter.getValue(), Model.getField(property));\r
                    operator = filter.getOperator();\r
                    if (property !== null) {\r
                        operator = operator || '=';\r
                        placeholder = '?';\r
                        if (operator === 'like' || (operator === '=' && filter.getAnyMatch())) {\r
                            operator = 'LIKE';\r
                            value = '%' + value + '%';\r
                        }\r
\r
                        if (operator === 'in' || operator === 'notin') {\r
                            if (operator === 'notin') {\r
                                operator = 'not in';\r
                            }\r
                            placeholder = '(' + Ext.String.repeat('?', value.length, ',') + ')';\r
                            values = values.concat(value);\r
                        } else {\r
                            values.push(value);\r
                        }\r
                        sql += filterStatement + '"' + property + '" ' + operator + ' ' + placeholder;\r
                        filterStatement = ' AND ';\r
                    }\r
                }\r
            }\r
\r
            sorters = params.sorters;\r
            len = sorters && sorters.length;\r
            if (len) {\r
                for (i = 0; i < len; i++) {\r
                    sorter = sorters[i];\r
                    property = sorter.getProperty();\r
                    if (property !== null) {\r
                        sql += sortStatement + '"' + property + '" ' + sorter.getDirection();\r
                        sortStatement = ', ';\r
                    }\r
                }\r
            }\r
\r
            // handle start, limit, sort, filter and group params\r
            if (params.page !== undefined) {\r
                sql += ' LIMIT ' + parseInt(params.start, 10) + ', ' + parseInt(params.limit, 10);\r
            }\r
        }\r
\r
        me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r
            var rows = resultSet.rows,\r
                count = rows.length,\r
                records = [],\r
                fields = Model.fields,\r
                fieldsLen = fields.length,\r
                raw, data, i, len, j, field, name;\r
\r
            for (i = 0, len = count; i < len; ++i) {\r
                raw = rows.item(i);\r
                data = {};\r
                for (j = 0; j < fieldsLen; ++j) {\r
                    field = fields[j];\r
                    name = field.name;\r
                    data[name] = me.fromSqlValue(raw[name], field);\r
                }\r
                records.push(new Model(data));\r
            }\r
\r
            callback.call(me, new Ext.data.ResultSet({\r
                records: records,\r
                success: true,\r
                total: count,\r
                count: count\r
            }));\r
        }, function(transaction, error) {\r
            callback.call(me, new Ext.data.ResultSet({\r
                success: false,\r
                total: 0,\r
                count: 0\r
            }), error);\r
        });\r
    },\r
\r
    updateRecords: function (transaction, records, callback) {\r
        var me = this,\r
            columns = me.columns,\r
            quotedColumns = me.quotedColumns,\r
            totalRecords = records.length,\r
            executed = 0,\r
            updates = [],\r
            setOptions = me._createOptions,\r
            len, i, record, placeholders, sql, data, values, errors, completeIf;\r
\r
        completeIf = function(transaction) {\r
            ++executed;\r
            if (executed === totalRecords) {\r
                callback.call(me, new Ext.data.ResultSet({\r
                    success: !errors\r
                }), errors);\r
            }\r
        };\r
\r
        for (i = 0, len = quotedColumns.length; i < len; i++) {\r
            updates.push(quotedColumns[i] + ' = ?');\r
        }\r
\r
        sql = 'UPDATE "' + me.getTable() + '" SET ' + updates.join(', ') + ' WHERE "' + me.getModel().idProperty + '" = ?';\r
\r
        for (i = 0, len = records.length; i < len; ++i) {\r
            record = records[i];\r
            data = me.getRecordData(record);\r
            values = me.getColumnValues(columns, data);\r
            values.push(record.getId());\r
\r
            // Capture the record in closure scope so we can access it later\r
            (function(record) {\r
                me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r
                    completeIf();\r
                }, function(transaction, error) {\r
                    if (!errors) {\r
                        errors = [];\r
                    }\r
                    errors.push(error);\r
                    completeIf();\r
                });\r
            })(record);\r
        }\r
    },\r
\r
    destroyRecords: function (transaction, records, callback) {\r
        var me = this,\r
            table = me.getTable(),\r
            idProperty = me.getModel().idProperty,\r
            ids = [],\r
            values = [],\r
            destroyedRecords = [],\r
            len = records.length,\r
            idStr = '"' + idProperty + '" = ?',\r
            i, result, record, sql;\r
\r
        for (i = 0; i < len; i++) {\r
            ids.push(idStr);\r
            values.push(records[i].getId());\r
        }\r
\r
        sql = 'DELETE FROM "' + me.getTable() + '" WHERE ' + ids.join(' OR ');\r
\r
        me.executeStatement(transaction, sql, values, function (transaction, resultSet) {\r
            callback.call(me, new Ext.data.ResultSet({\r
                success: true\r
            }));\r
        }, function (transaction, error) {\r
            callback.call(me, new Ext.data.ResultSet({\r
                success: false\r
            }), error);\r
        });\r
    },\r
\r
    /**\r
     * Formats the data for each record before sending it to the server. This\r
     * method should be overridden to format the data in a way that differs from the default.\r
     * @param {Object} record The record that we are writing to the server.\r
     * @return {Object} An object literal of name/value keys to be written to the server.\r
     * By default this method returns the data property on the record.\r
     */\r
    getRecordData: function (record) {\r
        var me = this,\r
            fields = record.fields,\r
            idProperty = record.idProperty,\r
            uniqueIdStrategy = me.uniqueIdStrategy,\r
            data = {},\r
            len = fields.length,\r
            recordData = record.data,\r
            i, name, value, field;\r
\r
        for (i = 0; i < len; ++i) {\r
            field = fields[i];\r
            if (field.persist !== false) {\r
                name = field.name;\r
                if (name === idProperty && !uniqueIdStrategy) {\r
                    continue;\r
                }\r
                data[name] = me.toSqlValue(recordData[name], field);\r
            }\r
        }\r
\r
        return data;\r
    },\r
\r
    getColumnValues: function(columns, data) {\r
        var len = columns.length,\r
            values = [],\r
            i, column, value;\r
\r
        for (i = 0; i < len; i++) {\r
            column = columns[i];\r
            value = data[column];\r
            if (value !== undefined) {\r
                values.push(value);\r
            }\r
        }\r
\r
        return values;\r
    },\r
\r
    getSchemaString: function() {\r
        var me = this,\r
            schema = [],\r
            model = me.getModel(),\r
            idProperty = model.idProperty,\r
            fields = model.fields,\r
            uniqueIdStrategy = me.uniqueIdStrategy,\r
            len = fields.length,\r
            i, field, type, name;\r
\r
        for (i = 0; i < len; i++) {\r
            field = fields[i];\r
            type = field.getType();\r
            name = field.name;\r
\r
            if (name === idProperty) {\r
                if (uniqueIdStrategy) {\r
                    type = me.convertToSqlType(type);\r
                    schema.unshift('"' + idProperty + '" ' + type);\r
                } else {\r
                    schema.unshift('"' + idProperty + '" INTEGER PRIMARY KEY AUTOINCREMENT');\r
                }\r
            } else {\r
                type = me.convertToSqlType(type);\r
                schema.push('"' + name + '" ' + type);\r
            }\r
        }\r
\r
        return schema.join(', ');\r
    },\r
\r
    convertToSqlType: function(type) {\r
        switch (type.toLowerCase()) {\r
            case 'string':\r
            case 'auto':\r
                return 'TEXT';\r
            case 'int':\r
            case 'date':\r
                return 'INTEGER';\r
            case 'float':\r
                return 'REAL';\r
            case 'bool':\r
                return 'NUMERIC';\r
        }\r
    },\r
\r
    dropTable: function() {\r
        var me = this;\r
\r
        me.executeTransaction(function(transaction) {\r
            me.executeStatement(transaction, 'DROP TABLE "' + me.getTable() + '"', function() {\r
                me.tableExists = false;\r
            });\r
        }, null, null, false);\r
    },\r
\r
    getDatabaseObject: function() {\r
        return window.openDatabase(this.getDatabase(), '1.0', 'Sencha Database', 5 * 1024 * 1024);\r
    },\r
\r
    privates: {\r
        executeStatement: function(transaction, sql, values, success, failure) {\r
            var me = this;\r
            transaction.executeSql(sql, values, success ? function() {\r
                success.apply(me, arguments);\r
            } : null, failure ? function() {\r
                failure.apply(me, arguments);\r
            } : null);\r
        },\r
\r
        executeTransaction: function(runner, failure, success, autoCreateTable) {\r
            var me = this;\r
\r
            autoCreateTable = autoCreateTable !== false;\r
\r
            me.getDatabaseObject().transaction(runner ? function(transaction) {\r
                if (autoCreateTable && !me.tableExists) {\r
                    me.createTable(transaction);\r
                }\r
                runner.apply(me, arguments);\r
            } : null, failure ? function() {\r
                failure.apply(me, arguments);\r
            } : null, success ? function() {\r
                success.apply(me, arguments);\r
            } : null);\r
        },\r
\r
        fromSqlValue: function(value, field) {\r
            if (field.isDateField) {\r
                value = value ? new Date(value) : null;\r
            } else if (field.isBooleanField) {\r
                value = value === 1;\r
            }\r
            return value;\r
        },\r
\r
        getPersistedModelColumns: function(model) {\r
            var fields = model.fields,\r
                uniqueIdStrategy = this.uniqueIdStrategy,\r
                idProperty = model.idProperty,\r
                columns = [],\r
                len = fields.length,\r
                i, field, name;\r
\r
            for (i = 0; i < len; ++i) {\r
                field = fields[i];\r
                name = field.name;\r
\r
                if (name === idProperty && !uniqueIdStrategy) {\r
                    continue;\r
                }\r
\r
                if (field.persist !== false) {\r
                    columns.push(field.name);\r
                }\r
            }\r
            return columns;\r
        },\r
\r
        toSqlValue: function(value, field) {\r
            if (field.isDateField) {\r
                value = value ? value.getTime() : null;\r
            } else if (field.isBooleanField) {\r
                value = value ? 1 : 0;\r
            }\r
            return value;\r
        }\r
    }\r
});