// Copyright IBM Corp. 2015. All Rights Reserved. // Node module: loopback-connector-sqlite3 // This file is licensed under the Artistic License 2.0. // License text available at https://opensource.org/licenses/Artistic-2.0 var g = require('strong-globalize')(); /*! * SQLite3 connector for LoopBack */ var SqlConnector = require('loopback-connector').SqlConnector; var async = require('async'); var debug = require('debug')('loopback:connector:sqlite3'); var fmt = require('util').format; var sqlite3 = require('sqlite3'); var util = require('util'); var moment = require('moment'); function InvalidParam(msg) { this.msg = msg; } /** * * Initialize the SQLite3 connector against the given data source * * @param {DataSource} dataSource The loopback-datasource-juggler dataSource * @callback {Function} [callback] The callback function * @param {String|Error} err The error string or object * @header SQLite3.initialize(dataSource, [callback]) */ exports.initialize = function initializeDataSource(dataSource, callback) { if (!sqlite3) { return; } var dbSettings = dataSource.settings || {}; dbSettings.file = dbSettings.file || ':memory:'; dbSettings.debug = dbSettings.debug || debug.enabled || true; dataSource.connector = new SQLite3(sqlite3, dbSettings); dataSource.connector.dataSource = dataSource; if (callback) { dataSource.connecting = true; dataSource.connector.connect(callback); } }; /** * SQLite3 connector constructor * * @param {SQLite3} sqlite3 SQLite3 node.js binding * @options {Object} settings An object for the data source settings. * @property {String} file Path to DB or `:memory`: * * @constructor */ function SQLite3(sqlite3, settings) { this.constructor.super_.call(this, 'sqlite3', settings); this.name = 'sqlite3'; // this._models = {}; this.sqlite3 = sqlite3; this.settings = settings; if (settings.debug) { debug('Settings %j', settings); this.sqlite3.verbose(); } } // Inherit from loopback-datasource-juggler BaseSQL util.inherits(SQLite3, SqlConnector); SQLite3.prototype.debug = function() { if (this.settings.debug) { debug.apply(debug, arguments); } }; /** * Connect to SQLite3 * @callback {Function} [callback] The callback after the connection is * established */ SQLite3.prototype.connect = function(callback) { var self = this; if (!self._defaultConnection) { self._getConnection(function(err, connection) { if (err) { if (callback) callback(err); return; } self._defaultConnection = connection; callback(null, self._defaultConnection); }); } else { callback(null, self._defaultConnection); } }; /** * Internal method to get a connection * @param {function} callback * @private */ SQLite3.prototype._getConnection = function(callback) { var self = this; var mode = self.settings.readonly ? self.sqlite3.OPEN_READONLY : self.sqlite3.OPEN_READWRITE | self.sqlite3.OPEN_CREATE; var db = new self.sqlite3.Database(self.settings.file, mode, function(err) { if (err) return callback(err); callback(null, db); }); }; /** * Execute the sql statement * * @param {String} sql The SQL statement * @param {String[]} params The parameter values for the SQL statement * @callback {Function} [callback] The callback after the SQL statement is * executed * @param {String|Error} err The error string or object * @param {Object[]} data The result from the SQL */ SQLite3.prototype.executeSQL = function(sql, params, options, callback) { var self = this; if (callback === undefined && typeof params === 'function') { callback = params; params = []; options = {}; } if (callback === undefined && typeof options === 'function') { callback = options; options = {}; } if (params && params.length > 0) { debug('SQL: %s\nParameters: %j', sql, params); } else { debug('SQL: %s', sql); } // SQLite doesn't type check so we do this in the connector instead. // See SQLite3.prototype.toColumnValue for (var p in params) { if (!params.hasOwnProperty(p)) continue; if (params[p] instanceof InvalidParam) params[p] = null; } var transaction = options.transaction; if (transaction && transaction.connection && transaction.connector === this) { debug('Execute SQL within a transaction'); executeWithConnection(transaction.connection); } else { self.connect(function(err, connection) { if (err) { debug('closing connection due to error: %s', err.message); if (connection === self._defaultConnection) { self._defaultConnection.close(); self._defaultConnection = null; } return callback(err); } executeWithConnection(connection); }); } function executeWithConnection(client) { var stmtType = sql.trim().toLowerCase(); if (stmtType.indexOf('select') === 0 || stmtType.indexOf('pragma') === 0) { client.all(sql, params, processResult); } else { client.run(sql, params, processResult); } function processResult(err, rows) { if (err) { debug('Error running sql `%s`: %j', sql, err); } else { debug('execute result: %j %j', this, rows); } var result = {}; if (rows) { result = rows; } if (this.hasOwnProperty('changes')) { result.count = this.changes; } if (this.hasOwnProperty('lastID')) { result.lastID = this.lastID; } // XXX: Workaround one of the SQL driver tests which expects /duplicate/ // to be part of error string if (err && err.message.match(/UNIQUE constraint failed/i)) { err.message += ' (duplicate value)'; } callback(err, result); } } }; /*! * Convert to the Database name * @param {String} name The name * @returns {String} The converted name */ SQLite3.prototype.dbName = function(name) { if (!name) { return name; } return name.toLowerCase(); }; /*! * Escape the name for SQLite3 DB * @param {String} name The name * @returns {String} The escaped name */ SQLite3.prototype.escapeName = function(name) { if (!name) { return name; } name.replace(/["]/g, '""'); return '"' + name + '"'; }; SQLite3.prototype.escapeValue = function(value) { if (typeof value === 'string') { return value; } if (typeof value === 'number' || typeof value === 'boolean') { return value; } return value; }; function buildLimit(filter) { var clause = []; if (isNaN(filter.limit)) { filter.limit = 0; } if (isNaN(filter.offset)) { filter.offset = 0; } if (!filter.limit && !filter.offset) { return ''; } if (filter.limit) { clause.push('LIMIT ' + filter.limit); } if (filter.offset) { clause.push('OFFSET ' + filter.offset); } return clause.join(' '); } SQLite3.prototype.applyPagination = function(model, sql, filter) { var limitClause = buildLimit(filter); return limitClause ? sql.merge(limitClause) : sql; }; /** * Disconnect from SQLite3 * @param {Function} [cb] The callback function */ SQLite3.prototype.disconnect = function disconnect(cb) { if (cb) { process.nextTick(cb); } }; SQLite3.prototype.getInsertedId = function(model, info) { return info.lastID; }; /** * Get the place holder in SQL for identifiers, such as ?? * @param {String} key Optional key, such as 1 or id * @returns {String} The place holder */ SQLite3.prototype.getPlaceholderForIdentifier = function(key) { return '$' + key; }; /** * Get the place holder in SQL for values, such as :1 or ? * @param {String} key Optional key, such as 1 or id * @returns {String} The place holder */ SQLite3.prototype.getPlaceholderForValue = function(key) { return '?' + key; }; SQLite3.prototype.getCountForAffectedRows = function(model, info) { return info && info.count; }; /** * Create the table for the given model * @param {String} model The model name * @param {Object} [options] options * @param {Function} [cb] The callback function */ SQLite3.prototype.createTable = function(model, options, cb) { if (typeof options === 'function') { cb = options; options = null; } options = options || {}; var tableName = this.tableEscaped(model); var self = this; var stmts = []; var colDefs = self.buildColumnDefinitions(model); if (colDefs instanceof Error) return cb(colDefs); stmts.push(fmt('CREATE TABLE %s (%s)', tableName, colDefs)); stmts = stmts.concat(self._buildColumnIndexes(model)); async.eachSeries(stmts, function(stmt, cb) { self.executeSQL(stmt, [], options, cb); }, cb); }; SQLite3.prototype.buildColumnDefinitions = function(model) { var properties = this.getModelDefinition(model).properties; var line = []; for (var propertyName in properties) { if (!properties.hasOwnProperty(propertyName)) continue; var colDef = this._buildColumnDefinition(model, propertyName); if (colDef instanceof Error) return colDef; line.push(colDef); } return line.join(','); }; SQLite3.prototype._buildColumnDefinition = function(model, propertyName) { var property = this.getModelDefinition(model).properties[propertyName]; var defaultClause = this._getDefaultClause(property); if (defaultClause instanceof Error) return defaultClause; var columnName = this.column(model, propertyName); var line = '"' + columnName + '" ' + this._columnDataType(model, propertyName) + defaultClause + (this.isNullable(property) ? '' : ' NOT NULL') + (property.id ? ' PRIMARY KEY' : ''); return line; }; function _convertBoolean(value) { var booleanTrue = ['t', 'T', 'y', 'Y', '1', 1, true]; var booleanFalse = ['f', 'F', 'n', 'N', '0', 0, false]; if (booleanTrue.indexOf(value) !== -1) return 1; if (booleanFalse.indexOf(value) !== -1) return 0; return Error(g.f('{{SQLITE3}}: Invalid boolean default: %s', value)); } SQLite3.prototype._getDefaultClause = function(property) { if (!property || !property.sqlite3 || !property.sqlite3.dbDefault) return ''; var value = property.sqlite3.dbDefault; switch (property.type.name) { case 'Number': if (isNaN(value)) return Error(g.f('Invalid numeric default: %s', value)); return ' DEFAULT ' + Number(value); case 'Boolean': return 'DEFAULT ' + _convertBoolean(value); case 'Date': if (value === 'now') { // store as ms since 1970 return ' DEFAULT (CAST(STRFTIME(\'%s\', \'now\') AS INTEGER)*1000)'; } if (!isNaN(value)) return ' DEFAULT ' + value; if (typeof value === 'string') { if (!isNaN(value)) return ' DEFAULT ' + Number(value); var parsedValue = moment(value); if (!parsedValue.isValid()) return ' DEFAULT ' + (parsedValue.unix() * 1000); // store as ms } return Error(g.f('Invalid date default: %s', value)); case 'String': return 'DEFAULT "' + this.escapeValue(value) + '"'; case 'GeoPoint': case 'Point': case 'List': case 'Array': case 'Object': case 'JSON': default: return Error(g.f('Default value for %s is not supported', property.type.name)); } }; SQLite3.prototype._columnDataType = function(model, propertyName) { var columnMetadata = this.columnMetadata(model, propertyName); var colType = columnMetadata && columnMetadata.dataType; if (colType) { colType = colType.toUpperCase(); } var property = this.getModelDefinition(model).properties[propertyName]; if (!property) { return null; } var colLength = columnMetadata && columnMetadata.dataLength || property.length || property.limit; if (colType && colLength) { return colType + '(' + colLength + ')'; } return this._buildColumnType(property); }; SQLite3.prototype._buildColumnType = function(property) { switch (property.type.name) { case 'Number': if (property.id) return 'INTEGER'; return 'REAL'; case 'Boolean': case 'Date': return 'INTEGER'; case 'String': case 'GeoPoint': case 'Point': case 'List': case 'Array': case 'Object': case 'JSON': return 'TEXT'; default: //if (!Array.isArray(prop.type) && !prop.type.modelName) { // return 'TEXT'; //} return 'TEXT'; } }; SQLite3.prototype._buildColumnIndexes = function(model) { var properties = this.getModelDefinition(model).properties; var settings = this.getModelDefinition(model).settings; var indexes = []; var self = this; function escape(name) { return self.escapeName(name.toLowerCase()); } for (var propertyName in properties) { if (!properties.hasOwnProperty(propertyName) || !propertyName) continue; var property = properties[propertyName]; if (property.index) indexes.push(fmt('CREATE INDEX IF NOT EXISTS %s ON %s (%s)', escape(model + '_' + propertyName), self.tableEscaped(model), escape(propertyName) )); } if (settings && settings.indexes) { for (var index in settings.indexes) { if (!settings.indexes.hasOwnProperty(index) || !index) continue; var columns; if (settings.indexes[index].columns) columns = settings.indexes[index].columns.split(','); else if (settings.indexes[index].keys) { if (settings.indexes[index].keys instanceof Array) { columns = settings.indexes[index].keys; } else { columns = Object.keys(settings.indexes[index].keys); } } else { debug('Unable to locate keys for index in settings: %j', settings.indexes[index] ); } if (!columns) continue; for (var i in columns) { if (!columns.hasOwnProperty(i)) continue; columns[i] = self.dbName(columns[i]).trim(); } indexes.push(fmt('CREATE INDEX IF NOT EXISTS %s ON %s (%s)', escape(index || model + '_' + columns.join('_')), self.tableEscaped(model), columns.map(escape).join(',') )); } } return indexes; }; SQLite3.prototype.toColumnValue = function(property, value) { if (value == null) { return null; } if (!property) { return value; } switch (property.type.name) { case 'Number': if (isNaN(value)) return new InvalidParam(g.f('{{SQLITE3}}: Invalid number: %s', value)); return Number(value); case 'Boolean': var b = _convertBoolean(value); return b; case 'String': return value; case 'Date': if (!isNaN(value)) return Number(value); if (typeof value === 'string') { var parsedValue = moment(value); if (!parsedValue.isValid()) return parsedValue.unix() * 1000; // store as ms } return new InvalidParam('SQLITE3: Invalid JSON: ' + value); case 'Array': case 'GeoPoint': case 'Point': case 'List': case 'Object': case 'ModelConstructor': case 'JSON': default: return JSON.stringify(value); } }; SQLite3.prototype.fromColumnValue = function(property, value) { if (value == null || !property) { return value; } switch (property.type.name) { case 'Number': return (+value); case 'Boolean': return ( value === 'Y' || value === 'y' || value === 'T' || value === 't' || value === '1' || value === 1 ); case 'String': return String(value); case 'Date': return new Date(value); case 'GeoPoint': case 'Point': case 'List': case 'Array': case 'Object': case 'ModelConstructor': case 'JSON': default: return JSON.parse(value); } }; // Used when inserting an empty row SQLite3.prototype.buildInsertDefaultValues = function() { return 'DEFAULT VALUES'; }; SQLite3.prototype.ping = function(cb) { if (cb) process.nextTick(cb); }; //require('./discovery')(SQLite3); require('./migration')(SQLite3); require('./transaction')(SQLite3);