Since writing my two tutorials regarding using SQLite in Ionic Framework and shipping an app with a pre-populated database, I’ve received many requests for a tutorial for making a full blown app. I listen to my readers, so I figured what better way to show such an example, than to create a todo-list type application.
In this tutorial, I hope to accomplish the following:
All items that I wish to accomplish have seen their own tutorials. If you’re familiar with the Marvel comic book movies, you’ll know that every hero has their own story, then they meet up for the bigger picture in an Avengers movie. Think of this tutorial like that.
You’ve probably come to expect this by now, but let’s go ahead and create a fresh Ionic Framework project to work with:
ionic start TodoApp blank
cd TodoApp
ionic platform add android
ionic platform add ios
It is important to note that if you’re not using a Mac, you cannot add and build for the iOS platform.
Before we go any further, I’d like to strongly recommend you check out the tutorials I made for each component listed. This major tutorial won’t offer the depth that I did on each of the components which is why I suggest you continue from this point with a strong knowledge of everything we’re about to do.
With our project as the current working directory, lets add all the plugins we plan to use in this project:
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git
cordova plugin add https://github.com/an-rahulpandey/cordova-plugin-dbcopy.git
Our Apache Cordova plugins will work fine at this point, but because we’re using Ionic Framework, it is best to use the AngularJS extension set, ngCordova. Since ngCordova is still alpha / beta, I’m not going to recommend you download the latest version when following this tutorial because you could be viewing this tutorial many months after it was published. With alpha and beta libraries, breaking changes can be introduced at any time. With that said, I’m going to recommend you download project commit 5eaa5552fd from GitHub since it is what I’m using in the tutorial.
Extract the ngCordova archive you downloaded and include ng-cordova.min.js into your project’s www/js directory.
Now crack open your www/index.html file because we need to include the script in our project:
<script src="js/ng-cordova.min.js"></script>
<script src="cordova.js"></script>
Notice that I’ve included ng-cordova.min.js above the cordova.js line? It is very important you do that, otherwise you’re going to get a mess of strange results.
The last thing we need to do in regards to including ngCordova into our project is we need to include the directive in our angular.module
found in our www/js/app.js file:
var todoApp = angular.module('starter', ['ionic', 'ngCordova']);
Now that all the plugins and libraries are installed, it is time to explain what we’re trying to accomplish. Our todo list application will consist of four screens:
The pre-filled SQLite database that we ship with our application will contain a list of categories. If we are testing in a web browser, we will pre-populate the database via a script instead since it cannot be copied over. For the purpose of this example, the category list cannot be changed. However, we will be able to add and remove any todo list name or todo list item.
Let’s start by creating a SQLite database with the following table structure.
populated.db
tblCategories
id: integer auto increment
category_name: text
tblTodoLists
id: integer auto increment
category_id: integer
todo_list_name: text
tblTodoListItems
id: integer auto increment
todo_list_id: id
todo_list_item_name: text
You can poke fun at my tables all you want, but they’ll accomplish the task at hand. Now using your favorite SQLite client, add some categories to the tblCategories table.
It is finally time to start programming the application!
Create a directory in your www folder called templates and add the following three files:
www
templates
config.html
categories.html
lists.html
items.html
Inside your www/js/app.js file add the following angular.module.config
method to your code:
todoApp.config(function($stateProvider, $urlRouterProvider) {
$stateProvider
.state('config', {
url: '/config',
templateUrl: 'templates/config.html',
controller: 'ConfigController'
})
.state('categories', {
url: '/categories',
templateUrl: 'templates/categories.html',
controller: 'CategoriesController'
})
.state('lists', {
url: '/lists/:categoryId',
templateUrl: 'templates/lists.html',
controller: 'ListsController'
})
.state('items', {
url: "/items/:listId",
templateUrl: "templates/items.html",
controller: "ItemsController"
});
$urlRouterProvider.otherwise('/config');
});
We will always start in the config view. It will be the place that our database is copied over and loaded from. When navigating between views we will be passing the unique id values obtained from our SQLite database. We’ll get to that later though. It is time to create each of the expected controllers based on what we put into the UI states. In your www/js/app.js file, add the following controllers:
todoApp.controller("ConfigController", function($scope, $ionicPlatform, $ionicLoading, $location, $ionicHistory, $cordovaSQLite) {
});
todoApp.controller("CategoriesController", function($scope, $ionicPlatform, $cordovaSQLite) {
});
todoApp.controller("ListsController", function($scope, $ionicPlatform, $ionicPopup, $cordovaSQLite, $stateParams) {
});
todoApp.controller("ItemsController", function($scope, $ionicPlatform, $ionicPopup, $cordovaSQLite, $stateParams) {
});
Go ahead and open your www/index.html file because we need to tell it that we’ll be using states in our application:
<ion-nav-view></ion-nav-view>
We aren’t quite done yet. We need to add some code to our template files so the router can pick them up as templates. In each of the four template files, add <ion-view></ion-view>
. Ignore how simple they are as we’re going to improve upon them later.
This can get a little nutty here if you’re not careful, so please consult my other tutorials if you get into a jam. One major difference here between this tutorial and my others is that we will be processing SQLite via a controller rather than in the angular.module.run
method. This is because it is my understanding that many people will be trying to work with very large SQLite databases that are too large to be processed in the run
method.
Add the following to your ConfigController
in your www/js/app.js file:
var db = null;
todoApp.controller("ConfigController", function($scope, $ionicPlatform, $ionicLoading, $location, $ionicHistory, $cordovaSQLite) {
$ionicHistory.nextViewOptions({
disableAnimate: true,
disableBack: true
});
$ionicPlatform.ready(function() {
$ionicLoading.show({ template: 'Loading...' });
if(window.cordova) {
window.plugins.sqlDB.copy("populated.db", function() {
db = $cordovaSQLite.openDB("populated.db");
$location.path("/categories");
$ionicLoading.hide();
}, function(error) {
console.error("There was an error copying the database: " + error);
db = $cordovaSQLite.openDB("populated.db");
$location.path("/categories");
$ionicLoading.hide();
});
} else {
db = openDatabase("websql.db", '1.0', "My WebSQL Database", 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql("DROP TABLE IF EXISTS tblCategories");
tx.executeSql("CREATE TABLE IF NOT EXISTS tblCategories (id integer primary key, category_name text)");
tx.executeSql("CREATE TABLE IF NOT EXISTS tblTodoLists (id integer primary key, category_id integer, todo_list_name text)");
tx.executeSql("CREATE TABLE IF NOT EXISTS tblTodoListItems (id integer primary key, todo_list_id integer, todo_list_item_name text)");
tx.executeSql("INSERT INTO tblCategories (category_name) VALUES (?)", ["Shopping"]);
tx.executeSql("INSERT INTO tblCategories (category_name) VALUES (?)", ["Chores"]);
tx.executeSql("INSERT INTO tblCategories (category_name) VALUES (?)", ["School"]);
});
$location.path("/categories");
$ionicLoading.hide();
}
});
});
We are first checking to see if window.cordova
exists and if it doesn’t, it means we are using a web browser. On the device we are copying over the populated database and opening it. We don’t have that luxury with WebSQL, so we need to create all the tables if they don’t exist. Because we are lazy and don’t want to check if the table data exists, we are going to drop the categories table every time and re-populate it. It won’t mess up any keys in the other tables.
We have three controllers that will be querying the database in different ways. Our CategoriesController
will be doing a select-all, for all categories in the table. You can accomplish this task by doing the following:
todoApp.controller("CategoriesController", function($scope, $ionicPlatform, $cordovaSQLite) {
$scope.categories = [];
$ionicPlatform.ready(function() {
var query = "SELECT id, category_name FROM tblCategories";
$cordovaSQLite.execute(db, query, []).then(function(res) {
if(res.rows.length > 0) {
for(var i = 0; i < res.rows.length; i++) {
$scope.categories.push({id: res.rows.item(i).id, category_name: res.rows.item(i).category_name});
}
}
}, function (err) {
console.error(err);
});
});
});
All categories are translated into JSON and added to the scope which we can then use directly in our categories view. However, notice that we are wrapping the SQL in $ionicPlatform.ready()
. This is because we cannot try to query the database until our plugins are ready.
Let’s go ahead and finish up our other controllers. I suggest you brush up on your SQL knowledge if you’re rusty.
todoApp.controller("ListsController", function($scope, $ionicPlatform, $ionicPopup, $cordovaSQLite, $stateParams) {
$scope.lists = [];
$ionicPlatform.ready(function() {
var query = "SELECT id, category_id, todo_list_name FROM tblTodoLists where category_id = ?";
$cordovaSQLite.execute(db, query, [$stateParams.categoryId]).then(function(res) {
if(res.rows.length > 0) {
for(var i = 0; i < res.rows.length; i++) {
$scope.lists.push({id: res.rows.item(i).id, category_id: res.rows.item(i).category_id, todo_list_name: res.rows.item(i).todo_list_name});
}
}
}, function (err) {
console.error(err);
});
});
$scope.insert = function() {
$ionicPopup.prompt({
title: 'Enter a new TODO list',
inputType: 'text'
})
.then(function(result) {
if(result !== undefined) {
var query = "INSERT INTO tblTodoLists (category_id, todo_list_name) VALUES (?,?)";
$cordovaSQLite.execute(db, query, [$stateParams.categoryId, result]).then(function(res) {
$scope.lists.push({id: res.insertId, category_id: $stateParams.categoryId, todo_list_name: result});
}, function (err) {
console.error(err);
});
} else {
console.log("Action not completed");
}
});
}
});
todoApp.controller("ItemsController", function($scope, $ionicPlatform, $ionicPopup, $cordovaSQLite, $stateParams) {
$scope.items = [];
$ionicPlatform.ready(function() {
var query = "SELECT id, todo_list_id, todo_list_item_name FROM tblTodoListItems where todo_list_id = ?";
$cordovaSQLite.execute(db, query, [$stateParams.listId]).then(function(res) {
if(res.rows.length > 0) {
for(var i = 0; i < res.rows.length; i++) {
$scope.items.push({id: res.rows.item(i).id, todo_list_id: res.rows.item(i).todo_list_id, todo_list_item_name: res.rows.item(i).todo_list_item_name});
}
}
}, function (err) {
console.error(err);
});
});
$scope.insert = function() {
$ionicPopup.prompt({
title: 'Enter a new TODO list',
inputType: 'text'
})
.then(function(result) {
if(result !== undefined) {
var query = "INSERT INTO tblTodoListItems (todo_list_id, todo_list_item_name) VALUES (?,?)";
$cordovaSQLite.execute(db, query, [$stateParams.listId, result]).then(function(res) {
$scope.items.push({id: res.insertId, todo_list_id: $stateParams.listId, todo_list_item_name: result});
}, function (err) {
console.error(err);
});
} else {
console.log("Action not completed");
}
});
}
});
Notice the repetitiveness of the code? It can be avoided if you want to put in the effort, but for the simplicity of this example I decided to leave it as is. Again all we’re doing is querying the tables for all data and converting the results into a JSON object that can be cycled through in our view. The methods of inserting we’re using will initiate a popup rather than a new page.
Now that all of our data is being queried as it should and our states are configured, it is time do do a little UI development. Each of our three templates will be near identical, so open each of them and add the following:
<ion-view title="Categories">
<ion-content>
<ion-list>
<ion-item ng-repeat="category in categories" href="#/lists/{{category.id}}">
{{category.category_name}}
</ion-item>
</ion-list>
</ion-content>
</ion-view>
The above code should go in the project’s www/templates/categories.html file and the code below should go in the project’s www/templates/lists.html file:
<ion-view title="Lists">
<ion-nav-buttons side="right">
<button class="right button button-icon icon ion-plus" ng-click="insert()"></button>
</ion-nav-buttons>
<ion-content>
<ion-list>
<ion-item ng-repeat="list in lists" href="#/items/{{list.id}}">
{{list.todo_list_name}}
</ion-item>
</ion-list>
</ion-content>
</ion-view>
Finally, the code below should go in the project’s www/templates/items.html file:
<ion-view title="Items">
<ion-nav-buttons side="right">
<button class="right button button-icon icon ion-plus" ng-click="insert()"></button>
</ion-nav-buttons>
<ion-content>
<ion-list>
<ion-item ng-repeat="item in items">
{{item.todo_list_item_name}}
</ion-item>
</ion-list>
</ion-content>
</ion-view>
Nothing really crazy happening here. We just print out the data in a list. However on the categories and lists view we pass the id when we click on an element. That id is picked up in the $stateParams
in the controller.
So at this point we have shipped a pre-populated SQLite database in a mobile Ionic Framework application that has three views. We can add new todo lists in a specific category and add todo items in a specific todo list. Sounds like we have a pretty solid application at the moment. This is not correct yet. We need to be able to remove todo lists or items.
For simplicity, our logic is going to be wrapped around the following for removing records. If we want to remove a single todo item, go ahead and remove it, but if we want to remove a todo list, then remove the list and all items that are assigned to it.
Starting in our ItemsController
add the following function:
$scope.delete = function(item) {
var query = "DELETE FROM tblTodoListItems where id = ?";
$cordovaSQLite.execute(db, query, [item.id]).then(function(res) {
$scope.items.splice($scope.items.indexOf(item), 1);
}, function (err) {
console.error(err);
});
}
What’s going to happen is we’re going to pass in the item we want to delete, then we are going to remove it from the database, and then we are going to remove it from the scope.
Next we are going to modify our items.html template so our list looks like this:
<ion-list show-delete="false" can-swipe="true">
<ion-item ng-repeat="item in items">
{{item.todo_list_item_name}}
<ion-option-button class="button-assertive icon ion-trash-a" ng-click="delete(item)"></ion-option-button>
</ion-item>
</ion-list>
Notice how we added logic for swipe options. Similar can be added for lists.html but this time we will be deleting all items as well as the list. Again, here is our delete function which will delete from both levels:
$scope.delete = function(item) {
var outerquery = "DELETE FROM tblTodoListItems where todo_list_id = ?";
var innerquery = "DELETE FROM tblTodoLists where id = ?";
$cordovaSQLite.execute(db, outerquery, [item.id]).then(function(res) {
$cordovaSQLite.execute(db, innerquery, [item.id]).then(function(res) {
$scope.lists.splice($scope.lists.indexOf(item), 1);
});
}, function (err) {
console.error(err);
});
}
And then here is our lists.html template changes to go with it:
<ion-list show-delete="false" can-swipe="true">
<ion-item ng-repeat="list in lists" href="#/items/{{list.id}}">
{{list.todo_list_name}}
<ion-option-button class="button-assertive icon ion-trash-a" ng-click="delete(list)"></ion-option-button>
</ion-item>
</ion-list>
With a little bit of luck, you were able to follow all my instructions and create a fabulous todo list application that made use of various topics that I’ve covered previously on my blog. If you run into issues, I encourage you to look at my previous articles before asking for help in the comments. I am happy to help, but you’ll learn it better if you first do the research.
A video version of this article can be seen below.