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