You are here

Playtime with Cordova: pt.4 Cordova and SQLite

Friday, July 18, 2014

DISCLAIMER: This is me sharing my learning, I have not yet created a final application, and also, some of the process’ below may not be best practice.  BUT as a place to start playing, which is really what we all want to do, I think it works.

Before we can retrive data from the database and start making some cool dynamic pages, we will need to get some data into the system.  It’s up to you how you go abouts doing it, I am going to go through 2 approaches I implemented (both using a csv file).  These are triggerred from a regular link on a settings page.

 

First, lets create that settings page, do this in your index.html file

       <div data-role="page" id="settings">

           <div data-role="header">

               <h1>Settings</h1>

               <a href="#home" class="ui-btn-left">Home</a>

           </div>

           <div data-role="main">

               <ul data-role="listview" data-inset="true">

                   <li><a href="#settings-rebuild-app" class="settings-rebuild-app">Rebuild (using app data)</a></li>

             <li><a href="#settings-rebuild-online" class="settings-rebuild-online">Rebuild (using online data)</a></li>              

             <li><a href="#settings-purge" class="settings-purge">Purge all data</a></li>

         </ul>

           </div>

       </div>

 

Then we need to setup an SQLite database to use, I just slid this in the ready function in my js.  You may also want to have a quick scan of this page at this point (http://docs.phonegap.com/en/1.2.0/phonegap_storage_storage.md.html).

var dbShell = window.openDatabase("recipes", "1.0", "Recipes", 1000000);

 

Next we setup some triggers for each of these.  On mobile you should not rely on the click event only, as this will result in a 300ms delay, which is noticeable to the end user.  What I have put in is mousedown and touchstart.  Mousedown is there so I can test with cordova serve, but touchstart is the main one you will want in your final project (note, mousedown does cause an extra function call to the document if you leave it in, so be aware of this if you get bugs, and remove before your project goes live).

I have also put in a few console logs just to help us see that these functions are being triggered as we expect them to

   // SETTINGS

   $('.settings-rebuild-online').on('mousedown touchstart', function(e) {

       console.log('rebuild');

       rebuildDataFromOnline();

       e.preventDefault();

   });


   $('.settings-rebuild-app').on('mousedown touchstart', function(e) {

       console.log('reset');

       // Load in the csv file

       $.get('data/recipes.csv', function(data) {

           recipes = $.csv.toArrays(data);

           rebuildDataFromFiles(recipes);

       });

       e.preventDefault();

   });


   $('.settings-purge').on('mousedown touchstart', function(e) {

       console.log('purge');

       // Load in the csv file

       dbPurgeAndSkeleton();

   });

 

Now you have your skeleton, things will happen when you click those links, granted, most of these will be errors on your console.  So let’s begin with the simpler one, the purge (aka dbPurgeAndSkeleton).  Whilst this sounds rather disturbing or dystopian, all we are in fact doing is wiping the database to have a clean start.  This can be very useful in our app, as the SQLite data can hang around (an android at least it IS persistant), and put our tests off a little.

   function dbPurgeAndSkeleton() {

       dbShell.transaction(function(tx) {

           tx.executeSql('DROP TABLE IF EXISTS recipes'),

           tx.executeSql('CREATE TABLE IF NOT EXISTS recipes (rid unique, name)'),

       }, errorCB, successCB);

   }

So, that is it, a few lines to wipe our database, that feels easy and scary at the same time.  You can see there that it uses our dbShell from earlier, and simply executes some sql against it, the first dropping a recipes table, and the second creating a new recipes table.  The final 2 functions are the callbacks for whether the query succeeds or fails.  These callbacks are vital for when you use select queries later, as they ultimately are how you appear to massage and use the data returned.

 

Ok then, let’s get some data into our site, we will do this with our local csv file which we uploaded earlier.  Look at the .settings-rebuild-app listener we added a moment ago, it is doing the initial work already.  It does a regular jQuery get for the csv file, then uses the jquery-csv plugin (from http://code.google.com/p/jquery-csv/) to convert it into an array.  This array is then passed into our transaction.

 

EXTRA TIP

I originally was trying to pass in some extra variables You can see here a slight difference to the previous transactions in that I have inserted a local function into the request rather than call a function directly.  The reason for this is that we can now pass in some extra custom variables, such as that recipes array we just made (just make sure you pass in the transaction (tx) variable too, as that is vital for it to work.

 

Now we have the need for our new function rebuildDataFromFiles, which is a REALLY dull function i’m afraid, but was done this way to make the code as reuseable as possible.

   function rebuildDataFromFiles(tx, recipes) {

       // Delete old tables as we are going to insert new content

       dbPurgeAndSkeleton();

       // Populate the recipes tables

       populateDBrecipes(recipes);

   }

So, there is first runs the purge function we build earlier.  Next it runs a function to populate our new table, so let’s just plough on through and make that shall we.

   function populateDBrecipes(recipes) {

       dbShell.transaction(function(tx) {

           for (var i = 1; i < recipes.length; i++) {

               tx.executeSql('INSERT INTO recipes (rid, name) VALUES (' + recipes[i][0] + ', "' + recipes[i][1] + '")');

           }

       }, errorCB, successCB);

   }

It may look a little more complex due to word count, but look at it for a second, it is basic development code, so should be nice and easy to play with.  This runs another transaction which has a set of SQL scripts within a loop.  We loop through each of the recipes from the CSV file, and insert them into our DB, that is it.  I hope all mobile development is as simple as this don’t you?

 

The final thing is much like the previous insert from a local csv file, but what if you want to grab the file from a remote server.  Well you can see from the code above that we need a new function called rebuildDataFromOnline.  So lets do this thing.

   function rebuildDataFromOnline() {

       dbPurgeAndSkeleton();

       hash = 'thisisapoorlysecuredfile';


       $.ajax({

           url: "http://some-test-server/data/recipes.csv.php?hash=" + hash,

           success: function(data, textStatus, jqXHR) {

               recipes = $.csv.toArrays(data);

               populateDBrecipes(recipes);

           },

           error: function() {

               alert("There was an error loading the feed");

           }

       });

   }

Again, this is not too long, and we start off with a quick database purge to prepare things.  Then we make a jQuery ajax call to our webserver to get the csv file down (quick advise, make this file a little different to your local one so you can see that it is working).  Just like transactions and other functionality that may have a delay, we have our 2 callbacks.  The error one is just an alert message, so I am sure you get what it is doing (if not, cause an error in your url and take a look), the other is what it will do on success, which is pretty much the same as when we handled the local files, it creates the array, and passes that into our populate function.

 

Okay then folks, we now have an app which can generate an SQLite DB table, then populate it in 2 ways, and wipe it clean if/when needed.  Now we are ready to start using the data, how exciting.

 

Useful resources

Related: 

Blog & News

Monday, March 7, 2016

Okay, I know it is awful practice to put 2 things into one article, but I didn't want to write two tbh, so I will keep this tidy, and you can grab out what you need.

 

Subscribe to Blog & News

About

Cultivating a better future, with the seeds of better websites

For several years I have evolved through one of the largest media charities in the UK to the role of Senior developer.  I loved it there, but have to stepped out on my own as a freelance developer.

My main skills are with the Drupal CMS, developing code for it, ensuring standards through projects (whether that design, development, or planning), and leading teams to build applications they can be proud of.

Digital Consultancy, Web Development and Project Architecture are where I try to focus my skills, supporting my passion and desire to create stunning websites on time, in budget, and meeting your objectives.

To build a powerful web presence, I combine my creative, technical, and managerial experience (alongside a good splash of passion) which I have cultivated over 10 years in the industry, creating websites people want to shout about.

More on how I can help