/** * SecureSql Database. * This class should not be created directly but instead should be obtained via * `Ext.space.SecureSql.get` * */Ext.define("Ext.space.securesql.Database", { /** * @type {String} * Database name */ name: null, /** * @type {String} * Database display name */ displayName: null, /** * @type {Number} * @private * Database ID */ id: -1, /** * @type {version} * @private * Database version */ version: -1, /** * @type {String} * Database version table name * @private */ versionTable: null, /** * @type {Number} * Current version of the schema */ loadedVersion: -1, /** * @type {Ext.space.Promise} * Promise that fulfills once the database is initialized. * @private */ loaded: null, /** * @type {Ext.space.Promise} * The most recent transaction promise to have been queued for execution. * @private */ _lastTransaction: null, /** * @type {Function} * Callback function that runs when the database is dropped or closed * @private */ _cleanup: null, /** * @type {Boolean} * True if the database has been dropped or closed, in which case object methods will no longer work * @private */ _expired: false, /** * @private * @param {Object} options Database configuration options * @param {String} options.name Database name * @param {String} options.displayName (optional) Database display name * @param {Boolean} options.skipVersionTracking (optional) Set to true to skip * creating the schema version table * @param {Function} options.cleanup (optional) Function to run after the database * is either dropped or closed */ constructor: function(options) { this.name = options.name; this.displayName = options.displayName || options.name; this._cleanup = options.cleanup; this.versionTable = "_sencha_schema_version"; this.loaded = new Ext.space.Promise(); //Don't execute queries until we have loaded/upgraded the schema. this.schemaLoaded = new Ext.space.Promise(); this.hasLoadedSchema = false; // initialize the transaction promise queue with our 'loaded' promise this._lastTransaction = this.loaded; var me = this; Ext.onSpaceReady().then(function() { Ext.space.Logger.debug("Open database"); var config = {name: me.name}; if (me.displayName) { config.displayName = me.displayName; } return Ext.space.SecureSql._open(config); }).then(function(dbSpec) { me.id = dbSpec.id; Ext.space.Logger.debug("Create database finished.", me.id); if (!(options.hasOwnProperty("skipVersionTracking") && options.skipVersionTracking)) { //Create schema version tracking table; var transaction = new Ext.space.securesql.Transaction(me, {immediate: true}); transaction.sql( "CREATE TABLE IF NOT EXISTS " + me.versionTable + " " + "(" + "id INT, " + "version INT, " + "created_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " + "modified_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " + "PRIMARY KEY (id)" + ")" ); transaction.sql("SELECT VERSION FROM " + me.versionTable, []).then(function(results) { Ext.space.Logger.debug("Found db version raw", results); if (results.rows.length > 0) { me.loadedVersion = parseInt(results.rows[0][0]); Ext.space.Logger.debug("me.loadedVersion", me.loadedVersion); } }); return transaction.execute().then(function() { Ext.space.Logger.debug("setup transaction complete."); me.loaded.fulfill(me); }).error(function(e) { Ext.space.Logger.error("TX error?", e); }); } else { me.loaded.fulfill(me); } }).error(function(e) { me.loaded.reject(e); }); }, /** * */ createSchema: function(version, callback){ version = parseInt(version); var schemaReady = new Ext.space.Promise(); if (this._expired) { schemaReady.reject("Cannot call createSchema; object is expired"); return schemaReady; } // TODO should only be called once, need error condition otherwise //create TX, callback, run TX. this.loaded.then(function(me) { Ext.space.Logger.info("Loaded Version", me.loadedVersion, version); if (me.loadedVersion === version) { //already done! schemaReady.fulfill(); return; } else if (me.loadedVersion >= 0 && me.loadedVersion < version) { //Run migrations! return; } var tx = me.createTransaction(); try { callback(tx); me._setVersion(tx, version); tx.execute().connect(schemaReady); } catch(e) { Ext.space.Logger.error("Error calling callback", e); schemaReady.reject(e); throw e; } }); return schemaReady; }, /** * * */ migration: function(toVersion, callback){ // If the user has a version that does not match version specified by // createSchema then the migrations are run in order until the version number // matches. It is up to the developer to implement correct migrations. TX is // passed in, and automatically executed after migration callback function // db.migration(version, function(tx){ // tx.query(); // tx.query(); // tx.query(); // }).then; }, /** * @private */ _setVersion: function(transaction, versionNumber){ var me = this; var sql = "INSERT OR REPLACE INTO " + this.versionTable + " " + "(id, version, modified_dt)" + " VALUES " + "(?, ?, datetime())"; transaction.query(sql, [1, versionNumber]).then(function() { me.loadedVersion = versionNumber; }); }, /** * Do any necessary cleanup and mark the object as expired (i.e., any * promise-returning methods will immediately reject). * * @private */ _expire: function() { if (this._cleanup) { this._cleanup(this); this._cleanup = null; } this._expired = true; }, /** * @private */ queueTransaction: function(transaction, transactionComplete) { // Since we currently cannot run nested or concurrent transactions, we need // a way to serialize their execution. So, we continually track the most // recently-queued transaction, setting it up to execute as soon as the one // prior to it finishes. When a transaction is queued, we use it to replace // the existing most-recently-queued transaction, and the cycle can repeat. // Note that this is uglier than the "proper" Promise-based solution because // we're breaking the promise chain by manually creating our own stepPromise // here. That's intentional, because we don't want to propagate errors into // independent transactions. var stepPromise = new Ext.space.Promise(); function next() { function step() { stepPromise.fulfill(); } transaction.run(); transactionComplete.then(step, step); return stepPromise; } this._lastTransaction = this._lastTransaction.then(next, next); }, /** * Execute a query against the database in a single transaction. * * The returned promise will return an array of records that match the query. * Each array value will be an object, the object properties will match field * names in the query. If the query uses column aliases then the alias will be * returned as the field name. * * testDatabase.query("select id, name from " + TEST_TABLE1 + " where id = ?",[1]).then(function(rows){ * var row = rows[0]; * Ext.space.Logger.log("record ",row.id, row.name); * }).error(function(e){ * Ext.space.Logger.error("Error with query?",e); * }); * * * testDatabase.query("select count(*) as countOfTest from test").then(function(rows){ * var row = rows[0]; * Ext.space.Logger.log("Count of rows in test ", row.countOfTest); * }).error(function(e){ * Ext.space.Logger.error("Error with query?",e); * }); * * @param {String} query The SQL statement * @param {Array} fields The fields of the statement. * @return {Ext.space.Promise} promise that will resolve when the query completes * * */ query: function(query, fields) { var rs = new Ext.space.Promise(); var me = this; if (this._expired) { rs.reject("Cannot run queries; object is expired"); return rs; } Ext.space.Logger.debug("Query, tx start", query, fields); var transaction = this.createTransaction(); transaction.sql(query, fields).then(function(results) { var insertId = results.insertId; // store here because convertResults will lose it results = me.convertResults(results); rs.fulfill(results, insertId); }).error(function(e) { rs.reject(e); }); transaction.execute(); return rs; }, /** * Generate a list of tables in the current database. * * @return {Ext.space.Promise} Promise that resolves with an array of table names. */ listTables: function() { return this.query("SELECT * FROM sqlite_master WHERE type='table';").then(function(tables) { return tables.map(function(table) { return table.name; }); }); }, /** * Convert results from a normal SQLite result set to a JavaScript array. * @private * @param {Object} results SQLite result set * @return {Array} array-ified result set */ convertResults: function(results){ var converted = []; var fields = results.names; var numFields = fields.length; var rows = results.rows; for (var i=0, l=rows.length; i<l; i++) { var row = rows[i]; var obj = {}; for (var j=0; j<numFields; j++ ) { obj[fields[j]] = row[j]; } converted.push(obj); } return converted; }, /** * Insert a single record into a table. * * You can pass either an array or object into this function: * - If an array then the order of elements in the array must match the order of * the fields. * - If an object then the field names will be extracted from the properties * * Array Example: * * testDatabase.insert('test', ["id", "name"], [1, "one"]).then(function(insertId){ * //done with insert * //insertId will be 1 or the auto increment value of the id of the table if the table has one. * }).error(function(e){ * Ext.space.Logger.error("Error with insert",e); * }); * * would be equivalent to * * INSERT INTO TABLE test (id, name) VALUES (1, 'one'); * * Object Example: * * testDatabase.insert('test', ["id", "name"], {id: 2, name:"two"}).then(function() { * //done with insert * //insertId will be 2 or the auto increment value of the id of the table if the table has one. * }).error(function(e) { * Ext.space.Logger.error("Error with insert",e); * }); * * would be equivalent to * * INSERT INTO TABLE test (id, name) VALUES (2, 'two'); * * @param {String} table the name of the table the record will be inserted into * @param {Array} fields an array of field names to insert. * @param {Array|Object} values either an array or object to insert. * @return {Ext.space.Promise} promise that will resolve when the insert is complete. * */ insert: function(table, fields, values){ var q = this._buildInsertStatement(table, fields, values); return this.query(q.statement, q.values).then(function(results, insertId){ return insertId; }); }, /** * @private */ _buildInsertStatement: function(table, fields, values){ var statement = "INSERT INTO " + table + "(" + fields.join(",") + ")"; var v = []; var vals = []; var isArray = Array.isArray(values); for(var i = 0, l = fields.length; i<l; i++){ v.push("?"); vals[i] = isArray ? values[i] : values[fields[i]]; } statement += " VALUES (" + v.join(",") + ")"; return {statement: statement, values: vals}; }, /** * Insert a multiple records in a single transaction into a table. Either an * array of arrays or objects can be passed into this function * - If an array then the order of elements in the array must match the order of * the fields. * - If an object then the field names will be extracted from the prop * * Because this is a transaction either all of inserts will be inserted or no * records will be inserted. In the event of a failure the promise will reject * with the sql statement that caused the error. * * Insert 500 records in a single transaction: * * var records = []; * for(var i = 1, l = 500; i <= l; i++){ * records.push([i, "row "+ i]); * } * * testDatabase.insertMany(TEST_TABLE1, ["id", "name"], records).then(function() { * //Insert complete. * }).error(function(e) { * Ext.space.Logger.error("Error with insert",e); * }); * * @param {String} table the name of the table the record will be inserted into * @param {Array} fields an array of field names to insert. * @param {Array} values An array of either arrays or objects to insert. * @return {Ext.space.Promise} promise that will resolve when the insert is complete. */ insertMany: function(table, fields, values){ if (this._expired) { var nope = new Ext.space.Promise(); nope.reject("Cannot run queries; object is expired"); return nope; } Ext.space.Logger.debug("insert many tx start"); var transaction = this.createTransaction(); for (var r=0; r<values.length; r++) { var q = this._buildInsertStatement(table, fields, values[r]); transaction.sql(q.statement, q.values); } return transaction.execute(); }, /** * Creates a new Transaction. * * Add queries, then execute them in a single database transaction. * * var tx = testDatabase.createTransaction(); * tx.query("select * from test where id = ?",[2]).then(function(rows) { * // statement done * }).error(function(e) { * Ext.space.Logger.error("Error with query?",e); * }); * * tx.query("select * from test where id = ?",[1]).then(function(rows) { * // statement done * }).error(function(e) { * Ext.space.Logger.error("Error with query?",e); * }); * * tx.execute().then(function() { * // transaction done * }).error(function(e) { * Ext.space.Logger.error("Error with TX?",e); * }); * * @return {Ext.space.securesql.Transaction} Transaction object, or null if the * database has been closed or dropped */ createTransaction: function(){ if (this._expired) { Ext.space.Logger.warn("Cannot create a transaction on an expired database", this); return null; } return new Ext.space.securesql.Transaction(this); }, /** * Permanently delete this database. This operation cannot be undone. All data * in this database will be lost. * * @return {Ext.space.Promise} promise that will resolve when the db has been deleted. */ drop: function(){ var promise = new Ext.space.Promise(); this.loaded.then(function(me) { Ext.space.Communicator.send({ command: "Sqlite#dropDatabase", databaseId: me.id, callbacks: { success: function(results) { me._expire(); promise.fulfill(results); }, failure: function(e) { promise.reject(e); } } }); }); return promise; }, /** * Close this database. * * @return {Ext.space.Promise} promise that will resolve when the db has been closed. */ close: function(){ var promise = new Ext.space.Promise(); if (this._expired) { promise.reject("Cannot close database; object is expired"); return promise; } this.loaded.then(function(me) { Ext.space.Communicator.send({ command: "Sqlite#closeDatabase", databaseId: me.id, callbacks: { onSuccess: function() { me._expire(); promise.fulfill(); }, onError: function(e) { promise.reject(e); } } }); }); return promise; }, /** * Import data from the file system into the database. The data can either be * in CSV (or similar) format, or in the form of a script full of SQL statements. * * @param {Number} fileKey File key of the file from which to import data * @param {String} type "csv" for delimited text records * @param {String} tableName Name of the database table to populate (for CSV) * @param {Array} fields Array of string names of the columns in the file (for CSV) * @param {String} delimiter (optional) Field delimiter (for CSV), defaults to "," * @param {Function} progressCallback (optional) Callback function to execute as the import process proceeds; will be passed a single parameter: the number of rows inserted * @param {Number} progressCount (optional) Interval in milliseconds at which to call the progressCallback * */ importData: function(fileKey, type, tableName, fields, delimiter, progressCallback, progressCount) { var promise = new Ext.space.Promise(); if (this._expired) { promise.reject("Cannot import data; object is expired"); return promise; } this.loaded.then(function(me) { Ext.space.Communicator.send({ command: "Sqlite#importData", databaseId: me.id, file: fileKey, progressInterval: progressCallback ? (progressCount || 5000) : undefined, delimiter: delimiter, type: type, fields: fields, table: tableName, callbacks: { onComplete: function(results) { promise.fulfill(results); }, onError: function(e) { promise.reject(e); }, onProgress: function(imported){ Ext.space.Logger.info("Rows", imported); if(progressCallback && imported.rowsInserted){ progressCallback(imported.rowsInserted); } } } }); }); return promise; }});