Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Convenience getType functions, bulk load function, addOptions function #49

Open
wants to merge 5 commits into
base: master
Choose a base branch
from
Open

Convenience getType functions, bulk load function, addOptions function #49

Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
3587f8b
Add convenience functions for using getType
patakijv Sep 22, 2016
b3faab9
Adding load function to load bulk records at once
patakijv Sep 22, 2016
81bc09e
Add addOptions function for dynamic extension
patakijv Sep 22, 2016
3bab17d
Add comment for logical labs added test functions
patakijv Sep 18, 2018
cd7fb3f
Adding ability to handle encrypted database
patakijv Sep 23, 2018
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Jump to
Jump to file
Failed to load files.
Diff view
Diff view
192 changes: 178 additions & 14 deletions joli.js
View file
Open in desktop
Expand Up @@ -45,6 +45,22 @@ var joliCreator = function() {
return typeof obj;
}
},
// [Logical Labs] add some convenience type test functions
isUndefined: function(obj) {
return obj === void 0;
},
isDefined: function(obj) {
return !(obj === void 0);
},
isObject: function(obj) {
return joli.getType(obj) === "object";
},
isArray: function(obj) {
return joli.getType(obj) === "array";
},
isFunction: function(obj) {
return joli.getType(obj) === "function"
},
jsonParse: function(json) {
return JSON.parse(json);
},
Expand Down Expand Up @@ -121,6 +137,13 @@ var joliCreator = function() {
if (!joli.getType(val)) {
return "NULL";
} else {
// [Logical Labs] allow for nested data (objects, arrays) in val
// to be serialized as a string for faster db interaction of large
// amounts of data - requires you to handle (jsonParse) the data
// after its retrieved when the data is expected to be serialized
if ((joli.isObject(val)) || (joli.isArray(val))) {
val = JSON.stringify(val);
}
if (joli.getType(val) === "string") {
// escape single quotes and dollar signs.
// quotes are escaped for SQLite
Expand All @@ -142,27 +165,108 @@ var joliCreator = function() {
}
};

// define available titanium database modules
joli.standardDatabaseModule = Titanium.Database;

// database encyrption is handled by sqlcipher and via a module
// Requires module for handling encrypted database installed in app

// For iOS, we don't need to use the javascript interface for the module, so long as it is at least included in the project
// It appeaars that so long as the project is built with the module we can just go around the interface and use
// the Titanium.Database interface and set our password manually using sqlcipher's query method PRAGMA key = '<password>'
// after opening the database
// read above
joli.encryptedDatabaseModule = Ti.Platform.name === 'android' ? require('appcelerator.encrypteddatabase') : Ti.Database;
// read below
// Notes for doing the above: on iOS when using the encrypted database module's javascript interfce (for open mainly)
// on subsequent opens of different databases for the circuitDB or coompanyDB, it mistakenly tries to run cipher migrate
// and for some reason that fails when attaching the database (one of the steps in the sqlcipher openAndMigrate method)
// 1) I don't understand why the module thinks the cipher migration is necessary because the module version where this was
// initially required is well past the internal setting of the version (at least 2.0.4 internally set)
// 2) When the slqcipher method (openAndMigrate) runs, it is not clear to me why it is failing at the attach database step
// 3) in both 1 and 2 above, it is not clear why it works initially but when switching to a different database it fails
// (sometimes takes a few changes for the failure to show up)

/**
* Connection
*/
joli.Connection = function(database, file) {
this.dbname = database;

joli.Connection = function(database, file, password, readonly) {
var databaseModule,
isAndroid = Titanium.Platform.osname === 'android',
isIOS = (Ti.Platform.osname === 'iphone' || Ti.Platform.osname === 'ipad');
// If iOS and a file object has been passed in, extract file basename wihout extension to use
if (isIOS && typeof database === 'object') {
database = database.name.replace(/(.*)\.(.*?)$/, "$1");
}
// password can be blank, just not undefined
if (arguments.length < 3 || typeof password === 'undefined') {
Ti.API.debug("Password is undefined for db "+database);
this.encrypted = false;
} else {
// don't show password in log when built for production release!! - comment the line out below
// Ti.API.debug("Password is "+password+" for db "+database);
this.encrypted = true;
}
// this.readonly is only for Android due to encrypted database module limitation in android
if (arguments.length < 4 || typeof readonly === 'undefined') {
this.readonly = false;
} else {
this.readonly = readonly;
}
// if connection isn't to be for an encrypted database (detected by undefined password), use standard module
if (!this.encrypted) {
databaseModule = joli.standardDatabaseModule;
} else {
databaseModule = joli.encryptedDatabaseModule;
// for encrpted database model, need to set the password in the module before connecting
if (joli.encryptedDatabaseModule !== joli.standardDatabaseModule) {
// don't show password in log when built for production release!! - comment the line out below
// Ti.API.debug("Setting password using "+password+" for db "+database);
databaseModule.password = password;
}
}
// if a file argument is defined, install the database from the file to standard location
if (file) {
this.database = Titanium.Database.install(file, this.dbname);
Ti.API.debug("Installing db "+database);
this.database = databaseModule.install(file, database);
// if file is not defined, the database is opened from its existing location
} else {
this.database = Titanium.Database.open(this.dbname);
if (isAndroid && this.encrypted && !this.readonly && typeof database === 'object') {
// If using encyrpted database on Android and not wanting readonly, need to make sure
// we pass in an absolute file path as string, not a file object as there is currently a limitation
// in the encrypted database module where if the argument is a fileproxy object, readonly will be true
// Also, need to make sure it is an absolute path without the protocol prepended
var dbFilePath = database.nativePath.replace('file://','');
this.database = databaseModule.open(dbFilePath);
} else {
Ti.API.debug("Opening db "+database);
this.database = databaseModule.open(database);
}
}
// For iOS, we need to set the encryption key after connecting to the database if we aren't using
// the encrypted database module where the password would have been set as a property on the module
if (isIOS && this.encrypted && joli.encryptedDatabaseModule === joli.standardDatabaseModule) {
// don't show password in log when built for production release!! - comment the line out below
// Ti.API.debug("Setting password using "+password+" for db "+database);
this.database.execute("PRAGMA key = '"+password+"'");
}

this.database.execute('PRAGMA read_uncommitted=true');
// run a benign test that queries can be executed in the database (to test for access)
// this is the same test that sqlcipher's android database library uses when connecting
// so effectively this is here for iOS so we can have an exception thrown at connection
// if we don't have a readable database (i.e. the password failed to decrypt)
Ti.API.debug("Testing db "+database);
this.database.execute("select count(*) from sqlite_master")
Ti.API.debug("Database file opened: "+this.database.file.nativePath)
};

joli.Connection.prototype = {
disconnect: function() {
this.database.close();
Ti.API.info("Closing database "+this.database.file.nativePath);
this.database.close();
},
execute: function(query) {
// Titanium.API.log('info', query);
// Ti.API.debug(query);
return this.database.execute(query);
},
lastInsertRowId: function() {
Expand Down Expand Up @@ -230,6 +334,10 @@ var joliCreator = function() {
};

joli.model.prototype = {
// return a query object for this model
query: function() {
return new joli.query().from(this.table);
},
all: function(constraints) {
var q = new joli.query().select().from(this.table);

Expand All @@ -242,7 +350,7 @@ var joliCreator = function() {
q.where(field, value);
});
}

if (constraints.whereIn) {
joli.each(constraints.whereIn, function(value, field) {
q.whereIn(field, value);
Expand Down Expand Up @@ -275,7 +383,7 @@ var joliCreator = function() {
q.where(field, value);
});
}

if (constraints.whereIn) {
joli.each(constraints.whereIn, function(value, field) {
q.whereIn(field, value);
Expand Down Expand Up @@ -315,6 +423,19 @@ var joliCreator = function() {
return result[0];
}
},
// testing extending joli model with multiple where conditions and order together
findOneUsingCompoundWhereOrderedBy: function(field,value,order) {
var q = new joli.query().select().from(this.table);
joli.each(constraints.where, function(value, field) {
q.where(field, value);
});
var result = q.order(order).limit(1).execute();
if (result.length === 0) {
return false;
} else {
return result[0];
}
},
findOneById: function(value) {
return this.findOneBy('id', value);
},
Expand Down Expand Up @@ -362,8 +483,51 @@ var joliCreator = function() {

return q.execute();
},
// [Logical Labs] function for bulk loading many records at once and
// is optimizable with transaction via useTransaction flag (consider as default?)
load: function(recordsDataArray, options, callback) {
var transaction = false;

if (options && options.purgeFirst) {
this.truncate();
}

// use a transaction around the collection of queries to optimize large record set
if (options && options.useTransaction) {
transaction = new joli.transaction("load")
transaction.begin();
}

// determine if the record exists already and if we need to create it or update it
joli.each(recordsDataArray, function(recordData, index) {
var record = false;
if (record = this.findOneById(recordData["id"])) {
record.fromArray(recordData);
} else {
record = this.newRecord(recordData);
}
(record) && (record.save());
}, this);

if (transaction) {
transaction.commit();
}

if (joli.isFunction(callback)) {
callback();
}
},
truncate: function() {
new joli.query().destroy().from(this.table).execute();
},
// [Logical Labs] add options to post model construction - for adding methods later
addOptions: function(options) {
if (options.methods) {
joli.each(options.methods, function(method, name) {
this[name] = method;
}, this);
}
joli.setOptions.call(this, options, this.options);
}
};

Expand Down Expand Up @@ -914,17 +1078,17 @@ var joli = joliCreator();
* var joli = require('joli').connect('your_database_name', '/path/to/database.sqlite');
*/
if (typeof exports === 'object' && exports) {
exports.connect = function(database, file) {
exports.connect = function(database, file, password,readonly) {
var joli = joliCreator();

if (database) {
if (file) {
joli.connection = new joli.Connection(database, file);
joli.connection = new joli.Connection(database, file, password,readonly);
} else {
joli.connection = new joli.Connection(database);
joli.connection = new joli.Connection(database, undefined, password,readonly);
}
}

return joli;
};
}
}