If you want to dive in mobile website and you are a good jQuery developer, you will love jQTouch. It’s one of the best mobile web framework out there.

However, one thing I found it was missing, was some database api. In webkit you now have access to one database per website address. Think of it as a better cookie. You could for example easily create a To do list with this feature.

Webkit is touchy about its database

You don’t mess with the webkit database, if you do, there is a good chance it will crash. Building this script, I never saw so much crash in Safari! so be careful using it. This script creates an abstraction of the webkit database api, it will enable you to write faster and less bloated code. It makes it really easy to play with the database, look at the demo, the HTML5 database section is all loaded from the webkit database.

View demo
(The Demo works only on Safari and webkit devices)

It can really be beneficial performance wise to use the database as a cache, this loads a lot faster than ajax web page, but you need to be careful on how much information you load from it. The iPhone is not a power house, you will need to find a balance. Also, used with the offline mode, you can really create powerful app that can be used offline.

Update: Apple is also doing database cache with the iPhone web user guide, interestingly enough they cache 1.4mb, the entire guide in the database in one shot. I personally did not see great performance when adding thousands of rows in the database, I am currently looking at how the pastry kit deal with the database executions. A good solution would be to create the database on the web application loading screen(when the application is added at the home screen).

How to use it

In fact what you see in the demo runs with basically 7 lines of code. Here a typical use of the extension:

  jQT.dbSelectAll("favoriteThing",function(result){
       	for (var i = 0; i < result.rows.length; ++i) {
            var row = result.rows.item(i);
            $("#database ul").append('<li class="arrow"><a href="#'+row['link']+'">'+row['name']+'</a></li>')
	    $("body").append('<div id="'+row['link']+'"><div class="toolbar"><h1>Database</h1><a class="back" href="#database">Home</a></div>'+row['content']+'</div>')
         }
 })

The API

Open database

jQT.dbOpen(“name”, “version”,”descriptione”, size);
This function is needed on every page where you use the webkit database, be careful with version and size. Set a bigger size that you think you need, trying to add space later could prompt users permission. Example:
jQT.dbOpen(“databaseTest”, “1.0”,”jqTouch web application database”, 200000);

Create Tables

jQT.dbCreateTables(json)
You would use this typically to create the basic structure of your database, I would store it in another file called dbstructure.js, or something like this, a quick reference to go to when you need to change something in the structure. It can also be used to simply add a table at a selected time.
An example of the json structure to follow:

jsonTb= { "createTables" :
					[
						{"table": "favoriteThing",	 "property": [
														{"name": "id", "type": "INTEGER PRIMARY KEY AUTOINCREMENT" },
			  											{"name": "name", "type": "TEXT" },
			  											{"name": "link", "type": "TEXT" },
			  											{"name": "content", "type": "TEXT" }
													 ]
						},
						{"table": "listRecipe",		 "property": [
														{"name": "id", "type": "INTEGER PRIMARY KEY AUTOINCREMENT" },
														{"name": "etat", "type": "REAL" },
			  											{"name": "name", "type": "TEXT" }
													 ]
						},
					]
				}

Create Rows

jQT.dbInsertRows(json)
You would use this typically to add rows in your table.
An example of the json structure to follow:

jsonAddRow1 = { "addRow" :
					[
						{"table": "favoriteThing",	 "property": [
			  											{"name": "name", "value": "Field from database 1" },
			  											{"name": "link", "value": "database1" },
			  											{"name": "content", "value": "This text is coming from the database 1" }
													 ]
						},
						{"table": "favoriteThing",	 "property": [
			  											{"name": "name", "value": "Field from database 2" },
			  											{"name": "link", "value": "database2" },
			  											{"name": "content", "value": "This text is coming from the database2" }
													 ]
						},
					]
				}

Delete table

jQT.dbDropTable(“table”)
Delete a table with all its information

Basic Delete Row

jQT.dbDeleteRow(“table”,”key”,”value”)
Delete a row following the statement WHERE KEY = VALUE. An example : jQT.dbDeleteRow(“listRecipe”,”id”,”‘5′”)

Get all the content from a table

jQT.dbSelectAll(“table”, callback(result))
Get all the information from a specific table. An Example:

jQT.dbSelectAll("favoriteThing",function(result){
 // Handle Result
 for (var i = 0; i < result.rows.length; ++i) {
  var row = result.rows.item(i);
  row['column']
 }
})

Custom query

jQT.dbExecuteQuery(“Query”,”Debug text”, callback(result))
You can create your custom query and get a callback with the result. you will probably not need the debug text. Example:

 jQT.dbExecuteQuery("SELECT * FROM favoriteThing","",function(result){
      console.log(result.rows)
 })

Debug mode

I added a debug mode that you can enable in the extension, change the variable debugging to true. It will add an overlay and tell you about every query executed in the script.

Still in a early stage

I consider this release as beta v0.1. It works pretty well for what it does, but it’s not optimized and it’s not thoroughly tested, so be careful and have fun.

Download the source code View demo

Tested on:
Safari 4
Iphone webkit

Version 0.1 Online
December 22, Initial release

Share this article: Facebook Twitter

23 thoughts on “jQTouch HTML5 database api extension”

  2. @Russell

    Well it comes in 2 part , first you create the table with a JSON string, after you insert your form values on the submit event in another JSON to create rows

    {“table”: “user”, “property”: [
    {“name”: “name”, “value”: “‘+$(‘#name’).val()+'” }
    ]
    }

    By the way you have a database viewer in safari, somewhere in the web inspector, its really helpful

  3. Have you considered how to use a transaction as a wrapper for multiple api calls? For example, open, create and insert calls sequenced in such a way that if one of the inserts fails the entire series of calls is rolled back. Perhaps passing a transaction object to the api?

  4. I did not, but it makes sense,

    I hope to use this script in the near future for iphone web application development, I guess it will change a lot confronted to a real big use case.

    I am also trying to get it to ship with the jQTouch core, if it’s not I will probably put it on github so everyone can fork it.

  11. Great article,
    I have been having a play; however, I don’t see how to update changed data items without creating a new record.
    I’m sure I would need to reference the record primary key but I don’t see the update command
    UPDATE Persons
    SET Address=’Nissestien 67′, City=’Sandnes’
    WHERE LastName=’Tjessem’ AND FirstName=’Jakob’

    Regards
    Paul

  12. Have you thought about using this type of database API to tie into page requests? I.E. I’ve been mulling over using an AJAX call to load hundreds of pages into the local sqlite db and to run page calls like this:

    (I was using the process with jquery mobile, but a similar process could be used with jqtouch)
    When a page is requested:
    1. If the ‘page’ is already loaded within the current mobile page structure, load from there,
    2. Search the sqlite db to see if the page can be loaded from offline storage, load from there,
    3. If it can’t be loaded from there, use AJAX to do a page call.

  15. Well, I did this extension quite a while back ago,

    At that time, I signed a release form so it can be included with jQtouch,
    guess it was not good enough

    1. I signed that, too. Then it went GitHub and I said, “Fork ’em! I’ll do it myself.”
      I’d really like you to fork jQT & put this extension in so I can fork your code and add a transaction as a wrapper for multiple api calls I asked about back on Jan 3 ’10.

  16. I found this to be pretty useful, but did you have a chance to work any further on this? Just wondering if you missed out on the function to update records.

  17. The missing ‘update’ function. Syntax is the same JSON as the dbInsertRows function and sqlWHERE is a raw SQL where (because I’m lazy and understand SQL).

    Usage:
    dbUpdateRows( “updateRow”:[{“table”:”test”, “property”:[{“name”:”testCol”,”value”:”testValue”}]}]);

    function dbUpdateRows(tbJson, sqlWHERE){
    for(x=0;x<tbJson.updateRow.length;x++){ // loop in every row from JSON
    createUQueryRow(tbJson.updateRow[x], sqlWHERE);
    }
    function createUQueryRow(tbNode, where){ // Create every row SQL
    debugTxt = "create row " + tbNode.table;

    stringQuery = "UPDATE " + tbNode.table + " SET "
    nodeSize = tbNode.property.length -1;
    for(y=0;y<=nodeSize;y++){
    stringQuery += tbNode.property[y].name + " = '" + tbNode.property[y].value + "'";
    if(y != nodeSize) {stringQuery +=", "}
    }
    stringQuery += " " + where;
    dbExecuteQuery(stringQuery,debugTxt);
    }
    }

  18. Pingback: My Bookmarks « Ruman's Blog

  19. Someone please complete this extension with transactions and all?? Immensely useful if its stable and optimized…

Comments are closed.