Skip to content

Instantly share code, notes, and snippets.

@jdnichollsc
Last active Apr 14, 2019
Embed
What would you like to do?
SQLite plugin with ngCordova in Ionic Framework => Using service pattern (Works for litehelpers/Cordova-sqlite-storage and MSOpenTech/cordova-plugin-websql) Code => http://1drv.ms/1Ono0Ys Template => https://github.com/jdnichollsc/Ionic-Starter-Template
angular.module('Demo', ['ionic', 'Demo.controllers', 'Demo.services', 'ngCordova'])
.run(function ($ionicPlatform, sqliteService) {
$ionicPlatform.ready(function () {
// Hide the accessory bar by default (remove this to show the accessory bar above the keyboard
// for form inputs)
if (window.cordova && window.cordova.plugins.Keyboard) {
cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
cordova.plugins.Keyboard.disableScroll(true);
}
if (window.StatusBar) {
// org.apache.cordova.statusbar required
StatusBar.styleDefault();
}
sqliteService.preloadDataBase(true); //true for debugging
});
})
var queries = [
"DROP TABLE IF EXISTS Users;",
"DROP TABLE IF EXISTS Products;",
"DROP TABLE IF EXISTS Categories;",
"CREATE TABLE Users (IdUser integer primary key , FirstName text not null);",
"CREATE TABLE Categories(IdCategory integer primary key, Name text not null);",
"CREATE TABLE Products(IdProduct integer primary key autoincrement, Name text not null, IdCategory integer not null);",
"INSERT INTO 'Users' ('IdUser','FirstName') VALUES (1, 'Daniel');",
"INSERT INTO 'Categories' ('IdCategory','Name') VALUES (1, 'Computadores');",
"INSERT INTO 'Categories' ('IdCategory','Name') VALUES (2, 'Celulares');",
"INSERT INTO 'Products' ('Name','IdCategory') VALUES ('Note 4', 2);"
];
controllers.controller('ProductsController', ['$scope', '$ionicPlatform', 'Products' function ($scope, $ionicPlatform, Products) {
$scope.products = [];
$scope.product = {
IdProduct: 1,
Name: 'Pizza',
Icon: 'ion-pizza',
Color: '#F056C2'
};
$ionicPlatform.ready(function () {
Products.createTable().then(function(){
return Products.addProduct($scope.product); //Insert a new product
}).then(function () {
return Products.getProducts(); //Get Products
}).then(function (products) {
$scope.products = angular.copy(products);
}).catch(function (err) {
console.log(err);
});
});
} ]);
services.factory('Products', ['$q', 'sqliteService', function ($q, sqliteService) {
return {
getProducts: function () {
var query = 'SELECT * FROM Products';
return $q.when(sqliteService.getItems(query));
},
addProduct: function (product) {
var query = "INSERT INTO Products (IdProduct, Name, Icon, Color) VALUES (?,?,?,?)";
return $q.when(sqliteService.executeSql(query, [product.IdProduct, product.Name, product.Icon, product.Color]));
},
dropTable: function () {
var query = 'DROP TABLE IF EXISTS Products';
return $q.when(sqliteService.executeSql(query));
},
createTable: function () {
var query = 'CREATE TABLE IF NOT EXISTS Products (IdProduct integer primary key, Name text, Icon text, Color text)';
return $q.when(sqliteService.executeSql(query));
}
};
});
services.service('sqliteService', ['$q', '$cordovaSQLite', function ($q, $cordovaSQLite) {
var self = this;
var _db;
self.db = function () {
if (!_db) {
if (window.sqlitePlugin !== undefined) {
//www/demo.db is a file created with SqliteBrowser tool :)
_db = window.sqlitePlugin.openDatabase({ name: "demo.db", location: 2, createFromLocation: 1 });
} else {
// For debugging in the browser
_db = window.openDatabase("demo.db", "1.0", "Demo", 200000);
}
}
return _db;
};
self.getFirstItem = function (query, parameters) {
var deferred = $q.defer();
self.executeSql(query, parameters).then(function (res) {
if(res.rows.length > 0)
return deferred.resolve(res.rows.item(0));
else
return deferred.reject("There aren't items matching");
}, function (err) {
return deferred.reject(err);
});
return deferred.promise;
};
self.getFirstOrDefaultItem = function (query, parameters) {
var deferred = $q.defer();
self.executeSql(query, parameters).then(function (res) {
if(res.rows.length > 0)
return deferred.resolve(res.rows.item(0));
else
return deferred.resolve(null);
}, function (err) {
return deferred.reject(err);
});
return deferred.promise;
};
self.getItems = function (query, parameters) {
var deferred = $q.defer();
self.executeSql(query, parameters).then(function (res) {
var items = [];
for (var i = 0; i < res.rows.length; i++) {
items.push(res.rows.item(i));
}
return deferred.resolve(items);
}, function (err) {
return deferred.reject(err);
});
return deferred.promise;
};
self.preloadDataBase = function (enableLog) {
var deferred = $q.defer();
if (window.sqlitePlugin === undefined) {
enableLog && console.log('%c ***************** Starting the creation of the database in the browser ***************** ', 'background: #222; color: #bada55');
self.db().transaction(function (tx) {
for (var i = 0; i < window.queries.length; i++) {
enableLog && console.log(window.queries[i]);
tx.executeSql(queries[i]);
}
}, function (error) {
deferred.reject(error);
}, function () {
enableLog && console.log('%c ***************** Completing the creation of the database in the browser ***************** ', 'background: #222; color: #bada55');
deferred.resolve("OK");
});
}
else {
deferred.resolve("OK");
}
return deferred.promise;
};
self.executeSql = function (query, parameters) {
return $cordovaSQLite.execute(self.db(), query, parameters);
};
}]);
@PterPmnta
Copy link

PterPmnta commented Dec 11, 2015

Sorry, that you use a javascript file to query , if not call them as independent objects , or not see where inject

@jdnichollsc
Copy link
Author

jdnichollsc commented Dec 12, 2015

@betollaque
Copy link

betollaque commented Feb 4, 2016

Hello, I runned it but It just show "Hello world".. There is an extra configuration?

@jdnichollsc
Copy link
Author

jdnichollsc commented Mar 11, 2016

You can see my new starter template that include this! 👯 https://github.com/jdnichollsc/Ionic-Starter-Template

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