Skip to main content

HTML5 Web SQL Databases w/ JavaScript

For fun I started building a small application using PhoneGap. More than anything I want to build a simple mobile app and see how easy it would be to deploy to multiple platforms. Problem was the app needed to store some data locally. I immediately thought of HTML 5 and web sql databases as an option. Turns out it is pretty simple if you have done any database work in the past. There are a few gotchas when working with SQLite and for me it was particularly frustrating working with the date/time features.

Create a database

For simplicity I made an object called Database and added methods to the proto chain for each database action. One thing to note is that a reference to mydb has been placed in the function for the Database object. This way once the database is open we can set this.mydb to that open database connection for further calls.

var Database = function(){
 var mydb=false;
}

Database.prototype.initDB = function() {
     try { 
        if (!window.openDatabase) { 
          alert('not supported'); 
        } else { 
          var shortName = 'sampledatabase'; 
          var version = '1.0'; 
          var displayName = 'Sample Database'; 
          var maxSize = 65536; // in bytes 
          this.mydb = openDatabase(shortName, version, displayName, maxSize); 
         }
      } catch(e) {}
}

// Then we can easily make a new Database object and call the initDB method.
var database = new Database();
database.initDB();

Create a table

Next, we are going to want to create a database table to store our data. We only want to create this table IF it does not exist already. Once again this is pretty simple. We created a table with an auto id, name_field, interval, and date_added. One thing to be aware of when using dates is that SQLite will use GMT as the standard for the SQL variable “now”. To change this you can do as we did below. Just set now to ‘localtime’. Also, we have to set a callback handler for success and error. In this example we are setting success to a nullDataHandler which does nothing and an error handler which alerts the error.

Database.prototype.createTable = function() {
 try {
  this.mydb.transaction(
    function(transaction) {
   //transaction.executeSql('DROP TABLE sample_db', [], this.nullDataHandler, this.errorHandler);
            transaction.executeSql("CREATE TABLE IF NOT EXISTS sample_db(id INTEGER PRIMARY KEY AUTOINCREMENT, name_field TEXT NOT NULL DEFAULT "", interval INTEGER NOT NULL DEFAULT 0, date_added TIMESTAMP DEFAULT (datetime('now','localtime')));", [], this.nullDataHandler, this.errorHandler); 
          });
      } catch(e) {}
}

Database.prototype.errorHandler = function (transaction, error) { 
  // returns true to rollback the transaction
  alert("Error processing SQL: "+ error);
  return true;  
}

// null db data handler
Database.prototype.nullDataHandler = function (transaction, results) {
}

Insert a row

If you have ever written a SQL insert statement this is as easy as Paul Walker winning a race for pinks. (Side note: Can’t believe they have made 5 of those movies…) Anyway, once again we need to specify success and error handlers.

Database.prototype.saveData = function(name_field, interval) {
 try{
  this.mydb.transaction(
    function(transaction) {
   transaction.executeSql("INSERT INTO sample_db (name_field, interval) VALUES (" + name_field + ", " + interval + ");", [], this.nullDataHandler, this.errorHandler);
  });
 } catch(e) {
  alert("Error processing SQL: "+ e.message);
  return;
 }
}


Crud everywhere

Reading and updating from the database are just as easy as inserting. We will illustrate a select below. Using the strftime function provided by SQLite we can manipulate the format of our date_added timestamp for output. This will help since I personally find dates in JavaScript annoying to deal with. One more thing to note is we are using a success handler named resultSetHandler this time to handle the sql query response.


// load the currently selected icons
Database.prototype.loadData = function() {
 try {
  this.mydb.transaction(
   function(transaction) {
               transaction.executeSql("SELECT id,name_field,interval,date_added, strftime('%d', date_added) as day, strftime('%Y', date_added) as year, strftime('%w-%m-%Y', date_added) as formatted_date, strftime('%H:%M:%S', date_added) as time_added FROM sample_db ORDER BY date_added desc",[], this.resultSetHandler, this.errorHandler);
         });
    } catch(e) {
        alert(e.message);
    }
}

Database.prototype.resultSetHandler = function(transaction, results) {
// For now just outputting the results.
 console.log(results);
}

As you can see it is pretty simple and straightforward to start using WebSQL.With support in iOS 3.2+ and Android 3+ it is ideal for mobile projects. As a side note I l know there is a definite movement server side towards ORM and writing less SQL. That being said there are some options to abstract the SQL away.

Comments

Popular posts from this blog

Python and Parquet Performance

In Pandas, PyArrow, fastparquet, AWS Data Wrangler, PySpark and Dask. This post outlines how to use all common Python libraries to read and write Parquet format while taking advantage of  columnar storage ,  columnar compression  and  data partitioning . Used together, these three optimizations can dramatically accelerate I/O for your Python applications compared to CSV, JSON, HDF or other row-based formats. Parquet makes applications possible that are simply impossible using a text format like JSON or CSV. Introduction I have recently gotten more familiar with how to work with  Parquet  datasets across the six major tools used to read and write from Parquet in the Python ecosystem:  Pandas ,  PyArrow ,  fastparquet ,  AWS Data Wrangler ,  PySpark  and  Dask . My work of late in algorithmic trading involves switching between these tools a lot and as I said I often mix up the APIs. I use Pandas and PyArrow for in-RAM comput...

Cloud computing: Update

Cloud service contracts are still too complex for many businesses to grasp the potential risks and liabilities,  Businesses are buying into cloud services without fully understanding what they're paying for and what they can expect from the service. "One of the big barriers to using cloud computing is a lack of trust. I think you should be able to know what you're getting and what it means — and it should be easy to ensure that the terms in your contract are reasonable: open, transparent, safe and fair. Even if you don’t have a law degree," "Sensible, plain language contracts" be designed to spell out clear service level agreements and what a businesses' rights are on a range of issues, such as which third parties would be able to access a businesses' information or whether a firm will be notified in the event of data being stolen. Drawing up model contracts for cloud services is a "key pillar" of the  Cloud Computing strategy ....

How to construct a File System that lives in Shared Memory.

Shared Memory File System Goals 1. MOUNTED IN SHARED MEMORY The result is a very fast, real time file system. We use Shared Memory so that the file system is public and not private. 2. PERSISTS TO DISK When the file system is unmounted, what happens to it? We need to be able to save the file system so that a system reboot does not destroy it. A great way to achieve this is to save the file system to disk. 3. EXTENSIBLE IN PLACE We want to be able to grow the file system in place. 4. SUPPORTS CONCURRENCY We want multiple users to be able to access the file system at the same time. In fact, we want multiple users to be able to access the same file at the same time. With the goals now in mind we can now talk about the major design issues: FAT File System & Design Issues The  FAT File System  has been around for quite some time. Basically it provides a pretty good file structure. But I have two problems with it: 1. FAT IS NOT EXTENSIBLE IN PLAC...