Skip to content

Instantly share code, notes, and snippets.

@khacanh
Created June 18, 2013 04:43
Show Gist options
  • Save khacanh/5802721 to your computer and use it in GitHub Desktop.
Save khacanh/5802721 to your computer and use it in GitHub Desktop.
Simple DB interface for phonegap / sqlite for app SpeakEnglish
var SpeakEnglish = {
db: {
name: 'speakEnglishDB',
displayName: 'Speak English DB',
version: '1.0',
size: 1000000,
categories: {
name: 'categories',
columns: ['name']
},
sentences: {
name: 'sentences',
columns: ['name']
},
records: {
name: 'records',
columns: ['normCat', 'normSen', 'fileName']
}
}
};
function normalizeData(datas) {
var normalizedDatas = [];
for(var i in datas) {
var data = datas[i];
if (typeof data == "string") {
data = "\'" + data + "\'";
}
normalizedDatas.push(data);
}
return normalizedDatas;
}
function isPhone() {
return navigator.userAgent.match(/(iPhone|iPod|iPad|Android|BlackBerry)/);
};
SpeakEnglish.persistence = {
db: null,
initialize: function() {
if (isPhone()) {
this.db = window.openDatabase(SpeakEnglish.db.name, SpeakEnglish.db.version, SpeakEnglish.db.displayName, SpeakEnglish.db.size);
this.createTable(SpeakEnglish.db.records.name, SpeakEnglish.db.records.columns);
}
},
dropTable: function(table) {
this.db.transaction(function(tx) {
tx.executeSql(Mustache.render('DROP TABLE IF EXISTS {{table}}', {table: table}));
}, this.errorCB, this.successCB);
},
createTable: function(table, columns) {
this.db.transaction(function(tx) {
var refs = {
table: table,
columns: columns.join(', ')
};
var sqlStr = Mustache.render('CREATE TABLE IF NOT EXISTS {{table}} ({{columns}})', refs);
tx.executeSql(sqlStr);
}, this.errorCB, this.successCB);
},
deleteFromTable: function(query, dbCallback) {
this.db.transaction(function(tx) {
tx.executeSql(query, [], function() {
dbCallback();
}, SpeakEnglish.persistence.errorCB);
}, this.errorCB, this.successCB);
},
insertRowIntoTable: function(table, columns, datas) {
this.db.transaction(function(tx) {
var refs = {
table: table,
columns: columns.join(', '),
datas: normalizeData(datas).join(', ')
};
var sqlStr = Mustache.render('INSERT INTO {{table}} ({{columns}}) VALUES ({{{datas}}})', refs);
console.log("Inserting data: " + sqlStr);
tx.executeSql(sqlStr);
}, this.errorCB, this.successCB);
},
errorCB: function(error) {
console.error('Error processing SQL, code = ' + error.code + ', message = ' + error.message);
},
successCB: function() {
console.log('Proccess SQL sucessfully');
},
queryTable: function(query, dbCallback) {
if (isPhone())
{
this.db.transaction(function(tx) {
tx.executeSql(query, [], function(tx1, results) {
dbCallback(results);
}, SpeakEnglish.persistence.errorCB);
}, this.errorCB);
}
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment