My Safari Browser SQLite Database Hello World Example

Posted June 11, 2010 at 9:38 AM by Ben Nadel

Tags: Javascript / DHTML, HTML / CSS

Reader Comments

Ben,

Offline Web App development requires a lot of planning, which is probably why it hasn't caught on in a big way. It's good that you're helping to raise awareness that it exists.

Everyone is so selfish and stingy with their WiFi, as they're sometimes forced to be by governmental mandates to encrypt. And cell phone access to data is as constantly prone to interruption as their phone calls are. The ability for a web page to save off its data locally and wait for connectivity to sync up to a server is a future that's too slow coming.

AIR helps. Heck, the now-defunct Google Gears helped. But offline apps are still not everywhere (sigh).

What I would like to see are some UDFs from Adobe that give cfscript the same syntax as JavaScript and SQLite to save off data to the database. (Does AIR do that?) And of course the ability to say (cfscript src="savedata.js"). Wouldn't it be great to use the exact same functions on the browser as you use on the server to save data? And not have to code everything twice, once for browser, once for server?

Thanks again for this article.

Thank you for the example. I've never really used that before but I consider it now. It might come in use as a cache for very database intensive web applications as well. I have to try to tweak some performance by caching search results over a browser-window-lifetime ;)

@Steve,

Yeah, it definitely takes more planning. Just this morning, I was thinking about the concept of creating user accounts. Take something like my Dig Deep Fitness app - you need to create a username / password for it. If you get to the mobile site, I guess you need to create a login (email) / password. These credentials then need to be loaded into the live site (to make sure they are valid), and then, I suppose, cached in the local database as well for when the user logs into their mobil / SQLite version of the site. This, in and of itself, seems much more complicated than anything I've done before with logins :)

And then, I guess at some point, there needs to be an explicit sync to the live site? Or does that sync happen automatically when it can.

In any case, this seems like a really fun and challenging thing to start thinking about.

@Alex,

Yeah, this is totally new for me as well. Hopefully, I'll have some more tinkering with this soon.

Ben,

In the absence of (cfscript src="something.js"), here's an approach that helps:

(1) Define a string that represents an update to the database (XML, JSON, comma/tab/column-delimited text, whatever).
(2) Write your server-side update routine to accept that string as input. Code it to be used via cfinclude, expecting some Variables or Request scope data to be predefined.
(3) Write a Web Service that accepts username, password and that string as its cfarguments. Make the Web Service copy the data into the appropriate scope and cfinclude the update routine.
(4) On the browser, if the online flag is false, save the string to the SQLite database.
(5) When you come back online and you have cached updates in SQLite, call the Web Service.
(6) When you're online and don't have cached updates, post to the server (the form's action page). On the server, use the Form scope data to build the same string format and cfinclude the update routine.
(7) If the browser has JavaScript off, you're screwed on (4) and (5), but you can always do (6) when they're online.

This is called Service Oriented Architecture, where the cfincluded update routine is the shared service. I've created applications that are capable of being used that way, but they don't have any pressing need to enter data while the user is offline. I work at a government agency, and the disaster assistance team has a need to do stuff offline (walking through the rubble of Earthquakes or oil spills, with no 3G or WiFi). But I'm not maintaining the apps that team uses. I'm licking my chops to code an offline app, but currently have no need to. Hope the explanation above helps you, however.

Steve

@Steve,

I think I see what you're saying. What I'd like to do, however, is always use the local database when possible, and only sync to the live site when necessary or explicitly asked to do so. Even when I do have 3G available (which, in a gym is more likely than WiFi - at least in gyms I've been to), the live-updates are not fast... and if you lose connectivity for a few minutes, you're dead in the water.

That's why, I figure I'd always go local until I need to sync something - just for speed.

That said, I think for account creation, I would always want the new accounts to be verified via the web service to make sure that the login is available and to issue a new user ID (internally). You have a great point, however, to pass the username and password along with all the web service calls. That is something that I had not even thought of; having to verify your login when interacting with the web service seems crucial if people might try to hack the app locally and upload corrupt data (or data not belonging to the correct user).

Now, I just need to come up with a nice, small example, if possible.

@Ben,

As for working offline even when you CAN work online, many offline-capable browsers have a "Work Offline" menu item that allows you to override the DOM's online flag. Firefox and Opera for-sure do, but I don't see it in Safari 5. I'm writing from a Mac right now so I can't check MSIE. In any case, with the right browser, you can already do what you want.

As for passing login on the Web Service architecture, that's what truly makes it SOA. Other apps can call the same WS (if they have permission to do so, based on their login, of course). I like the idea of cfincluding if you're on the same server as the update routine. What an overhead saver. Easier debug too.

And as for a nice, small example, how about a Web page to record station, sets, reps and weight when you're in the gym?

Steve

@Steve,

That is basically what Dig Deep Fitness does right now (exercise, sets, reps). But, it's slow. I want to speed it up and "modernize" it with some local database action.

If nothing else, it'll get me thinking outside the box.

Last night, I tried to refactor this example to use a more object-oriented approach (losest sense of the term):

http://www.bennadel.com/blog/1942-Refactoring-My-Safari-SQLite-Local-Database-Example.htm

I figure this way, I can start to think more effectively about how I want to move forward with the exploration.

Ben, I tested it in Safari 5 and Chrome 5 it works fine but it doesn't seems to work on FireFox 3.6.8, any ideas why?

Also, how did you get that SQLite Administration tool down there on your browser is that some kind of browser extension?

Thanks,
Marcin.

@Marcin,

I don't believe FireFox supports SQLite databases yet. As far as the admin, that's part of the core Safari developer console.

@Ben, as for your previous post about your iPhone app and this is all just in words:

When user registers add user to live website/database and stored credentials at that point locally. Whenever user logs off remove local credentials so that user must login next time.

Let's say user is logged in, the local credentials are passed to live web just at the beginning of the application launch.

Usage of the login functionality is done through live web server and all that user data will be stored locally.

One option to synch is to send SQLite file to live web/server for backup and recovery at the close of the app.

Another option is to do it through live through triggers. When user inserts/updates or deletes a record from/to local table a trigger fires on such events to auto synch with live web server behind the scenes.

Great blog,
Thanks Ben.

@Marcin,

So, are you saying that they have to log-in every time they use the locally cached app? Or, just after any time they have logged-out?

Perhaps I can let them use the local database without authorization; but, ask for credentials anytime they need to sync with the live database. Ultimately, if the database is local, I think we have to accept that security cannot be 100% solid since you can probably create your own Javascript that will update the local database (if you were so intent on hacking it).

The key, is to make sure that authorization is provided when the central, web-based database gets augmented?

I really need to think more about data syncing in general; looks like a dark art to me right now.

@Ben, I was thinking that they have to login every time the user have logged-out, and also do authentication (send locally stored credentials to the server) every time the app is being started/opened and/or the user requests to sync the data. There is always a potential that the user's locally stored data might be hacked but that's just that one user's data rather than all the users that are stored on the server side. I would probably not worry about unless you're storing some financial or personal information.

@Marcin,

I'm sure I'll play around with this stuff eventually. I just experimented with offline data sync for the first time the other day. Wow - that was waaaay more complicated than I hoped it would be. The more I dig, the more complicated local database storage becomes.

Hi Ben,

It is a very good article. I am interested in this area and couple of questions, if you could help me.

- I tried this example on Chrome on Win and it worked. But if you delete cache, then the sqlite data seems to get wiped out. This seems pretty dangerous as one cannot hope that no cache deletion ever happens. Surprisingly, on Safari, cache deletion still retained the sqlite data.

- If I enter data via Chrome and access same url via Safari, it doesn't show the data. Is the sqlite db specific to each browser. Is it possible to connect to a central db and access it from there. So if I had opened in Chrome earlier and saved and now I open in Safari, I am still able to see the data.

- The syncing with a live Mysql db would be important I guess. Any ideas if this needs to be done auto in the background, how one could go about. The requirement would be something like, the moment the phone gets 3G signal or if it gets connected to wifi, then sync.

- Is there a limit on the sqlite db size and limit on the size of each transaction ?

Thanks in advance.

Regards

Nilesh

@Nilesh,

The databases are definitely browser-specific. As such, things entered in Safari won't be available to Chrome. I am sure there also all kinds of domain-level restrictions as well (but I am not sure how these work with things like sub-domains).

I am surprised that clearing the cache deleted the database. I was told that the cache and the database are typically separated. But, again, these are all kind of "cutting edge" features and are all still evolving.

Each browser has a different limit I think, but I am not sure. I think some browsers will ask you every time you want to increment it while other will simply just stop you at one point.

Although, on that last point, I might be getting confused with the offline application cache, which is another kind of local storage.

Now data syncing - that's just a bear of a topic. I have only played around with it once and it was enough to scare me sufficiently:

http://www.bennadel.com/blog/2005-Experimenting-With-Offline-Data-Synchronization-Using-jQuery-And-ColdFusion.htm

As one developer put it, "syncing offline databases is a non-trivial task, like putting a man on the moon." It is not easy :D

I've heard that things like LiveCycle Data Services can make this kind of stuff easier; but I have no experience with that.

Ben,

Great post, very usefull, but I have a question, may be you have the answer, how can I make a backup of a database stored in iphone, obviously, it is a SQLite database in the iphone's safari browser.

Thanks for your time

sorry, noobs question, what's safari extension you use to debug SQLite data at the bottom of video there?

thanks

Ben, you're the greatest!!!!
Thanx 4 sharing this Tutorial!

@Mauricio,

I am not sure I understand your question? Are you asking how to make a remote backup (say, on your sever) of a SQLite database on the phone?

@Katopz,

I am just using the built-in Safari Developer tools. They can be hard to find - hidden in the menus somewhere is the "Developer" sub-menu. Look at the console.

@Reinhard,

Thanks my man - glad you found this useful :)

Hi Ben,

Thanks for your answer.

... And Yes, that is the question, I want to make one of two possible things.

1. How can I make a backup on-line of the database (the entire database) in a remote server?
2. Is there a way to make the backup over itunes software? I mean, is it possible to interact with the desktop software (itunes) to save a database's backup?

Thanks Ben for your time.

Mauricio M.

@Mauricio,

That's a tough question. Probably, the easiest thing to do would be do put a flag on the rows to indicate whether or not they have been backed up. Or, perhaps create some sort of table that stores the last ID of each table that needs to be backed up; then, you could, from time to time, flush any newer records (based on ID) to the server.

I've never really done anything like this.

Ben,

I ran across this posting and am new to Safari development, I cut/pasted your code and it doesn't seem to interact with the database.

I don't have air, that I'm aware of.

What am I missing?

It creates the db but then doesn't write to it.

Thanks.

@Amy: I can't see your code, but I implemented something very similar to this, and the error reporting is non-existent without callbacks. I ended up adding callback functions for both success and failure (with alerts ... ugh). I had a problem where the table I had created was missing a column, so the insert failed for that -- but I would not have known without some debugging.

In your "transaction.executeSQL" function, make sure you have 4 arguments: the sql, any parameters (or []), and 2 functions to report success/failure:

function(tx, rs) {
alert("insert succeeded");
},
function(tx, error) {
alert('sql error: ' + error.message);
}

(tx = transaction, rs = recordset, error = an error structure)

@Tom,

Thanks Tom! I had a config problem and finally figured that one out. I appreciate you following up though.

After I got over my initial problem and had the thing talking to me I was able to resolve any further problems.

This was a nice "hello database" example that Ben posted, and the only thing I'd change about it is adding in an error routine.

Cheers!

Hi, Thanks for the codes.
I am still confused about how to use "callback" for my application.
Here is what I am trying to do: I have 2 tables
Girls[id, name, idBoy]
Boys[id, name]
I want to show the list of girls and for each the name of the boy they "like". In PhP, the code might look something like that:
$qGirls="SELECT `Girl`.`id`, `Girl`.`name`, `Girl`.`idBoy` FROM `Girls`";
$rqGirls=mysql_query($qGirls);
while ($rowGirl = mysql_fetch_array($rqGirls, MYSQL_NUM)) {
$myBoyId = $rowGirl[2];
$qBoys="SELECT `Boys`.`name` FROM `Boys` WHERE `Boys`.`id` = $rowGirl[2]";
$rqBoys=mysql_query($qBoys);
$herBoy = mysql_fetch_array($rqBoys, MYSQL_NUM);

echo("$rowGirl[1] likes $herBoy <br>");
};

I wrote an equivalent code for SQLite database but herBoy comes out as undefined. Basically the code is something like that:
tx.executeSql(selectAllGirls, [], function(tx, result) {
'for loop to get girls-name';
tx.executeSql(selectherBoy, [idBoy], function(tx, result) {
}
}

I understand I need to use callback. But how?
Thank you.

@JM,

Ok, forget abou' it!!! I just realized that I should be using INNER JOIN to do it. Thanks though.

@JM,

your question is valid even though you solved your problem, the error callback (4th) is a function following the success callback (3rd parameter).

You MAY want to post your inner join solution as many people will find your post when searching for answers.

@Amy,

Here is a very good tutorial on how to use JOIN:
http://www.sqltutorial.org/sqljoin-innerjoin.aspx

Very interesting code, the best I've found so far on the internet, thank you :-))))
The girls-example works with a table containing 2 columns ('id' and 'name'). Only the girls-name is saved in the table (and an 'id' for each record). But how does the 'INSERT INTO' command looks like if, for examle, the 'name', the 'age' and 'city' are saved in the girls-table ?

I tried to make the code work to do this, all works, with the exception of the INSERT INTO command. Can you help ?

Thank you
Daniel

Thank you very much for this interactive tutorial.
It's very useful for me !

I copied your code to an htm file and ran it in safari 5.1.7 on Windows 7, with the developer console on.

It seems each time I run this I create a new sqlite_helloWord database but no tables get created and no records saved.

I am unsure where, physically, the sql files are located.

I have looked in the Applecomputer folders under the user appdata folder.

@Kymberley,

Hi

Please right click on the page in safari. It will show Inspect element, go to Resources tab. on the left panel there we can able to see a database. click on that .....

@Jayakrishnan

Hi I had already been able to display this console, although the right click is easier. I clicked on the page icon then develop, which gives you a choice of windows.

I can see that:
1. there is a girls table created
2. it has no elements
3. when I enter a new name,
a. A new database element appears.
b. I t has a girls table
c. it is empty as well
d. when I click on the old element girls table I get an error message.

Given that this blog is 2 years old, I would expect Ben to have move on quite a lot and maybe have different ways of doing this sort of exercise.

However it is pretty basic and once it works, the principalecould be used in a multitude of other apps.

Hello guys,

I want to create login page in html5, javascript,
can any one help me in this please

Hello guys,

I'm new to Sqlite specially in html5, JS
I want to create login page in html5, javascript, Sqlite
can any one help me in this please,
i will be thankfull

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment