jQTouch HTML5 database api extension

by Cedric Dugas on December 22, 2009

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



23 comments

Can you give an example for storing what users type into a form, please.

by Russell on January 1, 2010 at 4:27 am. Reply #

@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

by Cedric Dugas on January 1, 2010 at 1:57 pm. Reply #

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?

by Daniel J. Pinter on January 3, 2010 at 1:23 pm. Reply #

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.

by Cedric Dugas on January 3, 2010 at 1:52 pm. Reply #

Links are broken.

by Alex Grande on February 22, 2010 at 6:23 pm. Reply #

The links are broken. Could you please check them ? Best regards.

by Pablo Schaffner on March 31, 2010 at 7:09 am. Reply #

JS can be fonded here.

by Andrey Rebrov on April 5, 2010 at 1:40 am. Reply #

Any change to get example back?

by jp on May 23, 2010 at 5:26 am. Reply #

Links are… finally.. back!

by Cedric Dugas on June 28, 2010 at 9:44 pm. Reply #

Great work!!!!! seriously, i’ve only reacently found the JQtouch framework, and this extention is exactly what im looking for, for an app im working on.

Thanks a bunch.

Chris

by Christopher de Beer on August 13, 2010 at 8:44 am. Reply #

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

by Paul Fielder on September 1, 2010 at 4:25 am. Reply #

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.

by Alex Knoll on October 25, 2010 at 10:04 am. Reply #

Hi.. I have a question… Do I need to have a Mac for developing iPhone apps using jQTouch or it works on Windows machine?

by Rutwick on November 17, 2010 at 6:29 am. Reply #

It’s nice to see that your continuing to work on this extension. Have you considered forking jQT on GitHub and adding this extension?

https://github.com/senchalabs/jQTouch

That’s what I did for my extensions.

https://github.com/DataZombies/jQTouch

by Daniel J. Pinter on November 18, 2010 at 4:04 pm. Reply #

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

by Cedric Dugas on November 26, 2010 at 9:51 pm. Reply #

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.

by Daniel J. Pinter on November 27, 2010 at 11:39 am. Reply #

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.

by Vinod on February 13, 2011 at 12:55 pm. Reply #

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);
}
}

by Tony O on March 1, 2011 at 4:49 pm. Reply #

Forgot to add: put that function in the extension below the dbInsertRows function

by Tony O on March 1, 2011 at 4:50 pm. Reply #

[...] Position Absolute, web apps and front-end stuff – jQTouch HTML5 database api extension « [...]

by My Bookmarks « Ruman's Blog on March 29, 2011 at 2:11 am. Reply #

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

by zucky on April 25, 2011 at 12:58 am. Reply #

Take a look at https://github.com/DataZombies/DBi if you want transaction support

by DataZombies on July 31, 2012 at 2:17 pm. Reply #

I hve a question. The database coneccion are permanent or die on each screen?
How can i close it?

by Vero on April 14, 2012 at 10:29 am. Reply #

Leave your comment

Required.

Required. Not published.

If you have one.