extjs/build/packages/legacy/classic/legacy-debug.js

   1 /**
   2  * SQL proxy lets you store data in a SQL database.
   3  * The Sencha Touch SQL proxy outputs model data into an HTML5
   4  * local database using WebSQL.
   5  *
   6  * You can create a Store for the proxy, for example:
   7  *
   8  *     Ext.require(["Ext.data.proxy.SQL"]);
   9  *
  10  *     Ext.define("User", {
  11  *        extend: "Ext.data.Model",
  12  *        config: {
  13  *           fields: [ "firstName", "lastName" ]
  14  *        }
  15  *      });
  16  *
  17  *     Ext.create("Ext.data.Store", {
  18  *        model: "User",
  19  *        storeId: "Users",
  20  *        proxy: {
  21  *           type: "sql"
  22  *        }
  23  *     });
  24  *
  25  *     Ext.getStore("Users").add({
  26  *        firstName: "Polly",
  27  *        lastName: "Hedra"
  28  *     });
  29  *
  30  *     Ext.getStore("Users").sync();
  31  *
  32  * To destroy a table use:
  33  *
  34  *     Ext.getStore("Users").getProxy().dropTable();
  35  *
  36  * To recreate a table use:
  37  *
  38  *     Ext.data.Store.sync() or Ext.data.Model.save()
  39  */
  40 Ext.define('Ext.data.proxy.Sql', {
  41     alias: 'proxy.sql',
  42     extend: 'Ext.data.proxy.Client',
  43     alternateClassName: 'Ext.data.proxy.SQL',
  44     isSQLProxy: true,
  45     config: {
  46         /**
  47          * @cfg {Object} reader
  48          * @hide
  49          */
  50         reader: null,
  51         /**
  52          * @cfg {Object} writer
  53          * @hide
  54          */
  55         writer: null,
  56         /**
  57          * @cfg {String} table
  58          * Optional Table name to use if not provided ModelName will be used
  59          */
  60         table: null,
  61         /**
  62          * @cfg {String} database
  63          * Database name to access tables from
  64          */
  65         database: 'Sencha'
  66     },
  67     _createOptions: {
  68         silent: true,
  69         dirty: false
  70     },
  71     updateModel: function(model) {
  72         var me = this,
  73             modelName, len, i, columns, quoted;
  74         if (model) {
  75             me.uniqueIdStrategy = model.identifier.isUnique;
  76             if (!me.getTable()) {
  77                 modelName = model.entityName;
  78                 me.setTable(modelName.slice(modelName.lastIndexOf('.') + 1));
  79             }
  80             me.columns = columns = me.getPersistedModelColumns(model);
  81             me.quotedColumns = quoted = [];
  82             for (i = 0 , len = columns.length; i < len; ++i) {
  83                 quoted.push('"' + columns[i] + '"');
  84             }
  85         }
  86         me.callParent([
  87             model
  88         ]);
  89     },
  90     setException: function(operation, error) {
  91         operation.setException(error);
  92     },
  93     create: function(operation) {
  94         var me = this,
  95             records = operation.getRecords(),
  96             result, error;
  97         operation.setStarted();
  98         me.executeTransaction(function(transaction) {
  99             me.insertRecords(records, transaction, function(resultSet, statementError) {
 100                 result = resultSet;
 101                 error = statementError;
 102             });
 103         }, function(transactionError) {
 104             operation.setException(transactionError);
 105         }, function() {
 106             if (error) {
 107                 operation.setException(statementError);
 108             } else {
 109                 operation.process(result);
 110             }
 111         });
 112     },
 113     read: function(operation) {
 114         var me = this,
 115             model = me.getModel(),
 116             records = operation.getRecords(),
 117             record = records ? records[0] : null,
 118             result, error, id, params;
 119         if (record && !record.phantom) {
 120             id = record.getId();
 121         } else {
 122             id = operation.getId();
 123         }
 124         if (id !== undefined) {
 125             params = {
 126                 idOnly: true,
 127                 id: id
 128             };
 129         } else {
 130             params = {
 131                 page: operation.getPage(),
 132                 start: operation.getStart(),
 133                 limit: operation.getLimit(),
 134                 sorters: operation.getSorters(),
 135                 filters: operation.getFilters()
 136             };
 137         }
 138         operation.setStarted();
 139         me.executeTransaction(function(transaction) {
 140             me.selectRecords(transaction, params, function(resultSet, statementError) {
 141                 result = resultSet;
 142                 error = statementError;
 143             });
 144         }, function(transactionError) {
 145             operation.setException(transactionError);
 146         }, function() {
 147             if (error) {
 148                 operation.setException(statementError);
 149             } else {
 150                 operation.process(result);
 151             }
 152         });
 153     },
 154     update: function(operation) {
 155         var me = this,
 156             records = operation.getRecords(),
 157             result, error;
 158         operation.setStarted();
 159         me.executeTransaction(function(transaction) {
 160             me.updateRecords(transaction, records, function(resultSet, statementError) {
 161                 result = resultSet;
 162                 error = statementError;
 163             });
 164         }, function(transactionError) {
 165             operation.setException(transactionError);
 166         }, function() {
 167             if (error) {
 168                 operation.setException(statementError);
 169             } else {
 170                 operation.process(result);
 171             }
 172         });
 173     },
 174     erase: function(operation) {
 175         var me = this,
 176             records = operation.getRecords(),
 177             result, error;
 178         operation.setStarted();
 179         me.executeTransaction(function(transaction) {
 180             me.destroyRecords(transaction, records, function(resultSet, statementError) {
 181                 result = resultSet;
 182                 error = statementError;
 183             });
 184         }, function(transactionError) {
 185             operation.setException(transactionError);
 186         }, function() {
 187             if (error) {
 188                 operation.setException(error);
 189             } else {
 190                 operation.process(result);
 191             }
 192         });
 193     },
 194     createTable: function(transaction) {
 195         var me = this;
 196         if (!transaction) {
 197             me.executeTransaction(function(transaction) {
 198                 me.createTable(transaction);
 199             });
 200             return;
 201         }
 202         me.executeStatement(transaction, 'CREATE TABLE IF NOT EXISTS "' + me.getTable() + '" (' + me.getSchemaString() + ')', function() {
 203             me.tableExists = true;
 204         });
 205     },
 206     insertRecords: function(records, transaction, callback) {
 207         var me = this,
 208             columns = me.columns,
 209             totalRecords = records.length,
 210             executed = 0,
 211             uniqueIdStrategy = me.uniqueIdStrategy,
 212             setOptions = me._createOptions,
 213             len = records.length,
 214             i, record, placeholders, sql, data, values, errors, completeIf;
 215         completeIf = function(transaction) {
 216             ++executed;
 217             if (executed === totalRecords) {
 218                 callback.call(me, new Ext.data.ResultSet({
 219                     success: !errors
 220                 }), errors);
 221             }
 222         };
 223         placeholders = Ext.String.repeat('?', columns.length, ',');
 224         sql = 'INSERT INTO "' + me.getTable() + '" (' + me.quotedColumns.join(',') + ') VALUES (' + placeholders + ')';
 225         for (i = 0; i < len; ++i) {
 226             record = records[i];
 227             data = me.getRecordData(record);
 228             values = me.getColumnValues(columns, data);
 229             // Capture the record in closure scope so we can access it later
 230             (function(record) {
 231                 me.executeStatement(transaction, sql, values, function(transaction, resultSet) {
 232                     if (!uniqueIdStrategy) {
 233                         record.setId(resultSet.insertId, setOptions);
 234                     }
 235                     completeIf();
 236                 }, function(transaction, error) {
 237                     if (!errors) {
 238                         errors = [];
 239                     }
 240                     errors.push(error);
 241                     completeIf();
 242                 });
 243             })(record);
 244         }
 245     },
 246     selectRecords: function(transaction, params, callback, scope) {
 247         var me = this,
 248             Model = me.getModel(),
 249             idProperty = Model.idProperty,
 250             sql = 'SELECT * FROM "' + me.getTable() + '"',
 251             filterStatement = ' WHERE ',
 252             sortStatement = ' ORDER BY ',
 253             values = [],
 254             sorters, filters, placeholder, i, len, result, filter, sorter, property, operator, value;
 255         if (params.idOnly) {
 256             sql += filterStatement + '"' + idProperty + '" = ?';
 257             values.push(params);
 258         } else {
 259             filters = params.filters;
 260             len = filters && filters.length;
 261             if (len) {
 262                 for (i = 0; i < len; i++) {
 263                     filter = filters[i];
 264                     property = filter.getProperty();
 265                     value = me.toSqlValue(filter.getValue(), Model.getField(property));
 266                     operator = filter.getOperator();
 267                     if (property !== null) {
 268                         operator = operator || '=';
 269                         placeholder = '?';
 270                         if (operator === 'like' || (operator === '=' && filter.getAnyMatch())) {
 271                             operator = 'LIKE';
 272                             value = '%' + value + '%';
 273                         }
 274                         if (operator === 'in' || operator === 'notin') {
 275                             if (operator === 'notin') {
 276                                 operator = 'not in';
 277                             }
 278                             placeholder = '(' + Ext.String.repeat('?', value.length, ',') + ')';
 279                             values = values.concat(value);
 280                         } else {
 281                             values.push(value);
 282                         }
 283                         sql += filterStatement + '"' + property + '" ' + operator + ' ' + placeholder;
 284                         filterStatement = ' AND ';
 285                     }
 286                 }
 287             }
 288             sorters = params.sorters;
 289             len = sorters && sorters.length;
 290             if (len) {
 291                 for (i = 0; i < len; i++) {
 292                     sorter = sorters[i];
 293                     property = sorter.getProperty();
 294                     if (property !== null) {
 295                         sql += sortStatement + '"' + property + '" ' + sorter.getDirection();
 296                         sortStatement = ', ';
 297                     }
 298                 }
 299             }
 300             // handle start, limit, sort, filter and group params
 301             if (params.page !== undefined) {
 302                 sql += ' LIMIT ' + parseInt(params.start, 10) + ', ' + parseInt(params.limit, 10);
 303             }
 304         }
 305         me.executeStatement(transaction, sql, values, function(transaction, resultSet) {
 306             var rows = resultSet.rows,
 307                 count = rows.length,
 308                 records = [],
 309                 fields = Model.fields,
 310                 fieldsLen = fields.length,
 311                 raw, data, i, len, j, field, name;
 312             for (i = 0 , len = count; i < len; ++i) {
 313                 raw = rows.item(i);
 314                 data = {};
 315                 for (j = 0; j < fieldsLen; ++j) {
 316                     field = fields[j];
 317                     name = field.name;
 318                     data[name] = me.fromSqlValue(raw[name], field);
 319                 }
 320                 records.push(new Model(data));
 321             }
 322             callback.call(me, new Ext.data.ResultSet({
 323                 records: records,
 324                 success: true,
 325                 total: count,
 326                 count: count
 327             }));
 328         }, function(transaction, error) {
 329             callback.call(me, new Ext.data.ResultSet({
 330                 success: false,
 331                 total: 0,
 332                 count: 0
 333             }), error);
 334         });
 335     },
 336     updateRecords: function(transaction, records, callback) {
 337         var me = this,
 338             columns = me.columns,
 339             quotedColumns = me.quotedColumns,
 340             totalRecords = records.length,
 341             executed = 0,
 342             updates = [],
 343             setOptions = me._createOptions,
 344             len, i, record, placeholders, sql, data, values, errors, completeIf;
 345         completeIf = function(transaction) {
 346             ++executed;
 347             if (executed === totalRecords) {
 348                 callback.call(me, new Ext.data.ResultSet({
 349                     success: !errors
 350                 }), errors);
 351             }
 352         };
 353         for (i = 0 , len = quotedColumns.length; i < len; i++) {
 354             updates.push(quotedColumns[i] + ' = ?');
 355         }
 356         sql = 'UPDATE "' + me.getTable() + '" SET ' + updates.join(', ') + ' WHERE "' + me.getModel().idProperty + '" = ?';
 357         for (i = 0 , len = records.length; i < len; ++i) {
 358             record = records[i];
 359             data = me.getRecordData(record);
 360             values = me.getColumnValues(columns, data);
 361             values.push(record.getId());
 362             // Capture the record in closure scope so we can access it later
 363             (function(record) {
 364                 me.executeStatement(transaction, sql, values, function(transaction, resultSet) {
 365                     completeIf();
 366                 }, function(transaction, error) {
 367                     if (!errors) {
 368                         errors = [];
 369                     }
 370                     errors.push(error);
 371                     completeIf();
 372                 });
 373             })(record);
 374         }
 375     },
 376     destroyRecords: function(transaction, records, callback) {
 377         var me = this,
 378             table = me.getTable(),
 379             idProperty = me.getModel().idProperty,
 380             ids = [],
 381             values = [],
 382             destroyedRecords = [],
 383             len = records.length,
 384             idStr = '"' + idProperty + '" = ?',
 385             i, result, record, sql;
 386         for (i = 0; i < len; i++) {
 387             ids.push(idStr);
 388             values.push(records[i].getId());
 389         }
 390         sql = 'DELETE FROM "' + me.getTable() + '" WHERE ' + ids.join(' OR ');
 391         me.executeStatement(transaction, sql, values, function(transaction, resultSet) {
 392             callback.call(me, new Ext.data.ResultSet({
 393                 success: true
 394             }));
 395         }, function(transaction, error) {
 396             callback.call(me, new Ext.data.ResultSet({
 397                 success: false
 398             }), error);
 399         });
 400     },
 401     /**
 402      * Formats the data for each record before sending it to the server. This
 403      * method should be overridden to format the data in a way that differs from the default.
 404      * @param {Object} record The record that we are writing to the server.
 405      * @return {Object} An object literal of name/value keys to be written to the server.
 406      * By default this method returns the data property on the record.
 407      */
 408     getRecordData: function(record) {
 409         var me = this,
 410             fields = record.fields,
 411             idProperty = record.idProperty,
 412             uniqueIdStrategy = me.uniqueIdStrategy,
 413             data = {},
 414             len = fields.length,
 415             recordData = record.data,
 416             i, name, value, field;
 417         for (i = 0; i < len; ++i) {
 418             field = fields[i];
 419             if (field.persist !== false) {
 420                 name = field.name;
 421                 if (name === idProperty && !uniqueIdStrategy) {
 422
 423                     continue;
 424                 }
 425                 data[name] = me.toSqlValue(recordData[name], field);
 426             }
 427         }
 428         return data;
 429     },
 430     getColumnValues: function(columns, data) {
 431         var len = columns.length,
 432             values = [],
 433             i, column, value;
 434         for (i = 0; i < len; i++) {
 435             column = columns[i];
 436             value = data[column];
 437             if (value !== undefined) {
 438                 values.push(value);
 439             }
 440         }
 441         return values;
 442     },
 443     getSchemaString: function() {
 444         var me = this,
 445             schema = [],
 446             model = me.getModel(),
 447             idProperty = model.idProperty,
 448             fields = model.fields,
 449             uniqueIdStrategy = me.uniqueIdStrategy,
 450             len = fields.length,
 451             i, field, type, name;
 452         for (i = 0; i < len; i++) {
 453             field = fields[i];
 454             type = field.getType();
 455             name = field.name;
 456             if (name === idProperty) {
 457                 if (uniqueIdStrategy) {
 458                     type = me.convertToSqlType(type);
 459                     schema.unshift('"' + idProperty + '" ' + type);
 460                 } else {
 461                     schema.unshift('"' + idProperty + '" INTEGER PRIMARY KEY AUTOINCREMENT');
 462                 }
 463             } else {
 464                 type = me.convertToSqlType(type);
 465                 schema.push('"' + name + '" ' + type);
 466             }
 467         }
 468         return schema.join(', ');
 469     },
 470     convertToSqlType: function(type) {
 471         switch (type.toLowerCase()) {
 472             case 'string':
 473             case 'auto':
 474                 return 'TEXT';
 475             case 'int':
 476             case 'date':
 477                 return 'INTEGER';
 478             case 'float':
 479                 return 'REAL';
 480             case 'bool':
 481                 return 'NUMERIC';
 482         }
 483     },
 484     dropTable: function() {
 485         var me = this;
 486         me.executeTransaction(function(transaction) {
 487             me.executeStatement(transaction, 'DROP TABLE "' + me.getTable() + '"', function() {
 488                 me.tableExists = false;
 489             });
 490         }, null, null, false);
 491     },
 492     getDatabaseObject: function() {
 493         return window.openDatabase(this.getDatabase(), '1.0', 'Sencha Database', 5 * 1024 * 1024);
 494     },
 495     privates: {
 496         executeStatement: function(transaction, sql, values, success, failure) {
 497             var me = this;
 498             transaction.executeSql(sql, values, success ? function() {
 499                 success.apply(me, arguments);
 500             } : null, failure ? function() {
 501                 failure.apply(me, arguments);
 502             } : null);
 503         },
 504         executeTransaction: function(runner, failure, success, autoCreateTable) {
 505             var me = this;
 506             autoCreateTable = autoCreateTable !== false;
 507             me.getDatabaseObject().transaction(runner ? function(transaction) {
 508                 if (autoCreateTable && !me.tableExists) {
 509                     me.createTable(transaction);
 510                 }
 511                 runner.apply(me, arguments);
 512             } : null, failure ? function() {
 513                 failure.apply(me, arguments);
 514             } : null, success ? function() {
 515                 success.apply(me, arguments);
 516             } : null);
 517         },
 518         fromSqlValue: function(value, field) {
 519             if (field.isDateField) {
 520                 value = value ? new Date(value) : null;
 521             } else if (field.isBooleanField) {
 522                 value = value === 1;
 523             }
 524             return value;
 525         },
 526         getPersistedModelColumns: function(model) {
 527             var fields = model.fields,
 528                 uniqueIdStrategy = this.uniqueIdStrategy,
 529                 idProperty = model.idProperty,
 530                 columns = [],
 531                 len = fields.length,
 532                 i, field, name;
 533             for (i = 0; i < len; ++i) {
 534                 field = fields[i];
 535                 name = field.name;
 536                 if (name === idProperty && !uniqueIdStrategy) {
 537
 538                     continue;
 539                 }
 540                 if (field.persist !== false) {
 541                     columns.push(field.name);
 542                 }
 543             }
 544             return columns;
 545         },
 546         toSqlValue: function(value, field) {
 547             if (field.isDateField) {
 548                 value = value ? value.getTime() : null;
 549             } else if (field.isBooleanField) {
 550                 value = value ? 1 : 0;
 551             }
 552             return value;
 553         }
 554     }
 555 });
 556