Skip to content

Instantly share code, notes, and snippets.

@jgoux
Created April 15, 2014 14:53
Star You must be signed in to star a gist
Save jgoux/10738978 to your computer and use it in GitHub Desktop.
Ionic / AngularJS service wrapper for Web SQL API / SQLite-Cordova-Plugin
angular.module('myApp', ['ionic', 'myApp.services', 'myApp.controllers'])
.run(function(DB) {
DB.init();
});
angular.module('myApp.config', [])
.constant('DB_CONFIG', {
name: 'DB',
tables: [
{
name: 'documents',
columns: [
{name: 'id', type: 'integer primary key'},
{name: 'title', type: 'text'},
{name: 'keywords', type: 'text'},
{name: 'version', type: 'integer'},
{name: 'release_date', type: 'text'},
{name: 'filename', type: 'text'},
{name: 'context', type: 'text'}
]
}
]
});
angular.module('myApp.controllers', ['myApp.services'])
.controller('DocumentCtrl', function($scope, Document) {
$scope.documents = [];
$scope.document = null;
// Get all the documents
Document.all().then(function(documents){
$scope.documents = documents;
});
// Get one document, example with id = 2
Document.getById(2).then(function(document) {
$scope.document = document;
});
});
angular.module('myApp.services', ['myApp.config'])
// DB wrapper
.factory('DB', function($q, DB_CONFIG) {
var self = this;
self.db = null;
self.init = function() {
// Use self.db = window.sqlitePlugin.openDatabase({name: DB_CONFIG.name}); in production
self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1);
angular.forEach(DB_CONFIG.tables, function(table) {
var columns = [];
angular.forEach(table.columns, function(column) {
columns.push(column.name + ' ' + column.type);
});
var query = 'CREATE TABLE IF NOT EXISTS ' + table.name + ' (' + columns.join(',') + ')';
self.query(query);
console.log('Table ' + table.name + ' initialized');
});
};
self.query = function(query, bindings) {
bindings = typeof bindings !== 'undefined' ? bindings : [];
var deferred = $q.defer();
self.db.transaction(function(transaction) {
transaction.executeSql(query, bindings, function(transaction, result) {
deferred.resolve(result);
}, function(transaction, error) {
deferred.reject(error);
});
});
return deferred.promise;
};
self.fetchAll = function(result) {
var output = [];
for (var i = 0; i < result.rows.length; i++) {
output.push(result.rows.item(i));
}
return output;
};
self.fetch = function(result) {
return result.rows.item(0);
};
return self;
})
// Resource service example
.factory('Document', function(DB) {
var self = this;
self.all = function() {
return DB.query('SELECT * FROM documents')
.then(function(result){
return DB.fetchAll(result);
});
};
self.getById = function(id) {
return DB.query('SELECT * FROM documents WHERE id = ?', [id])
.then(function(result){
return DB.fetch(result);
});
};
return self;
});
@jdnichollsc
Copy link

See my example using ngCordova and Service Pattern :)
https://gist.github.com/jdnichollsc/9ac79aaa3407e92677ba/

To download the code: http://1drv.ms/1Ono0Ys

Regards, Nicholls

@hamza-d52
Copy link

"TypeError: window.sqlitePlugin" is undefined i am getting this error in browser and in phone i am getting this error Uncaught "TypeError: Cannot call method 'openDatabase' of undefined " please help me to get rid of this error .Thanks !!

Copy link

ghost commented Jan 3, 2016

hello jdnichollsc, okay !! How would the insert, update and delete. thank you.

@attiqJumani
Copy link

very helpful tutorial. just one question, how to define foreign key relation between two tables in the config.js file?

@shraddha1112
Copy link

Any sample for adding foreign key. How to define it?

@skanth91
Copy link

Thank you jgoux ...

@jdnichollsc
Copy link

Hi @Djamilson, see my starter template using SQLite https://github.com/jdnichollsc/Ionic-Starter-Template

@naufal18-bot
Copy link

Thank you @jgoux
But, could you help me, how if I wanna initiate multiple tables?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment