HTML5: Web SQL Database

SQLite is one of the most popular databases that you may not know about. It is used in every Firefox, Skype, McAfee,  and Solaris instance. It is also used on Apple Mac computers and iPhones. Adobe, Google and Mozilla use SQLite for Web related applications. Google’s local data storage engine, Google Gears, uses SQLite. But now SQLite may be too popular. Here is the announcement in the W3C Web SQL Database site:

This specification has reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. Until another implementor is interested in implementing this spec, the description of the SQL dialect has been left as simply a reference to Sqlite, which isn’t acceptable for a standard. Should you be an implementor interested in implementing an independent SQL backend, please contact the editor so that he can write a specification for the dialect, thus allowing this specification to move forward.

The following accompanies the latest W3C Editors Draft [our bolding and underline added – curiously, there is no elaboration as to why SQLite is not acceptable for a standard] dated July 23, 2010 for the Web SQL Database standard that is included in the HTML5 documentation listed at Wikipedia. Given that the editor, Ian Hickson, works for Google and Google uses SQLite, notably for its Google Gears local Web store, this impasse seems a bit anomalous. However, further investigation finds that Google is a)leaving Gears intact but  b)not using SQLite itself itself and encouraging its developers to transition to  HTML5 for local Web storage.

Here are some possible reasons for the impasse with SQLite:
1)Ian Hickson has raised objections to the fact that  SQLite does not support the Boolean data type and other shortcomings in SQLite . See here for issue.
2)Mozilla Firefox which does use SQLite, is proposing a new IndexedDB approach [yes shades of BTree]  for local Web Storage and is planning to bypass the Web SQL Database standard. IndexedDB will be used in Firefox 4. See here for the details.
3)Google has had problems in AdWords with its SQLite implementation – but Firefox does not. See here for details.
4)Google has a new Google Cloud Storage API – that can act as  a proxy for a local store. Keep an Open Eye objected that the whole idea is for offline usage; but a colleague said that issue would be worked out and insisted on inclusion. See here for details.
5)The Web browser developers are fighting over the Web Database standard. See here for the details.

Summary

This skirmish over what many would consider a slam dunk for the Web Database or Web SQL Database standard  given a)the availability of SQLite and b)the relatively mature state of database development – this gives a taste for some of the turmoil that lurks in critical parts of the emerging  HTML5 standard. Yes, large chunks of HTML5 are fairly well set and large chunks of those “settled standards” have been implemented by various browser vendor. But as we shall see in the posting – HTML5: Whats Implemented to July 2010, the implementation is hardly complete of uniform among the top 5  browser vendors. Given this , you now can  imagine what is happening over HTML5 standards for Touch, Gestures, and associated Events or Local Storage or 2D Canvas Standards. The first, Touch standards,  will be the topic of  coming HTML5 post.
Update: Google appears to be database neutral and expecting both IndexedDB and Web SQL to appear in Web Stanadrds

9 thoughts on “HTML5: Web SQL Database”

  1. “curiously, there is no elaboration as to why SQLite is not acceptable for a standard”

    a reference saying “we’ve implemented sqlite” is so far away from acceptable that I don’t think it’s worthy of elaboration. They aren’t saying sqlite isn’t the right tool, they’re just saying that the SQL dialect being used for HTML5 needs to be specified in the HTML5 standard; if this happens to match totally what works in sqlite currently (or is a totally-contained subset), then that’s cool. But it needs to be specified, and then someone needs to implement an SQL engine for HTML5 that _isn’t_ sqlite, so they can check the spec matches what actually happens. You don’t need to look as “possible reasons”, they explain exactly why in the post

    1. Frymaster –

      My mistake on the specification of a Web SQL Database standard using SQLite [or some direct subset ] as the basis for that specification. I had assumed this was done. However, this task seems to be a case of taking the SQLite specs and translating them to W3C compliant specs.

      But I am not sure of the next step. What is the reason for a “Not-a-SQLite” instance test? Is this assurance that Web-SQL-ready SQLite will properly reject non-Web SQL syntax or the broader case of repelling SQL security hacks ?

      Your Keep an Open Eye editor

  2. Yeah, because why just specify the SQL standard (ostensibly 92), which is cross-database, when we can get screwed into a specific implementation and then that implementation cannot progress, since people pretend to themselves that its mistakes and idiosyncracies somehow define SQL?

    It’s not like the web has any experience with the importance of an established standard instead of pretending one implementation matters, or anything.

  3. HI, i have a question. OK. i rephrase. I need some help. I am doing a website which has to be accessable using Phone, online or offline. For offline, I have to use Sqlite server. I was told by my supervisor to use HTML 5 to help Sqlite store data on local database. But i dunno where to start and i’m not even sure that’s how things work with HTML5, Sqlite and local database. So if anyone happens to know some resources i can read up or a good site i can learn about things, let me know. And feel free to correct me if i’ve got anything wrong in what i said above.
    Thank you.

    1. Katherine –

      SQLite is the unofficial standard for PC Client database as it is used by Microsoft, Adobe, Zend and therefore all PHP after 5.1 plus countless other major and minor software vendors. SQLite even made it into the working drafts of the HTML5. But now SQLite is stuck, being supplanted by a Mozilla proposal [remarkably, a strong user of SQLite on its Firefox, Thunderbird and other prooducts – so why they are opposing it is somewhat of a mystery]. Mozilla is proposing its own yet to be delivered Key/Value [so called Index database] – think of the old B-Tree databases of the late 1980’s and early 1990’s. Index databases are popular right now because they fit into the JSON/JavaScript Object paradigm neatly plus the distribued and massive scale databases used in the Cloud like Apache Hadoop and Microsoft’s Dryad.

      There really should be two SQL database standards – an SQL one which SQLite is one of the best for personal client use[but there are others but they are all proprietary and charged for like Sybase SQL Anywhere].

      In the end , what Google decides on will likely rule the roost because Ian Hickson, the Major Domo for the HTML5 standards is from Google and Google has with Chrome, Android, Maps, DOCs, and all its search APIs – has massive influence. And the word for SQLite from Google is mixed – Google has abandoned its Google Gears – which still supplies some of the best online/offline operations – and used SQLite; but the replacement has yet to be defined. Meanwhile Adobe with its FLASH AIR,which uses SQLite among other other databases does offer impressive online/offline operations. But you know Steve Jobs pronouncements about Flash etc etc.

      In sum offline/online databases operations, with its inherently awkward synchronization problems, is likely to be a big source of non-compliance and/or “proprietary” HTML5 work.

      In short you have been handed a very tough nut problem – bona fortuna.

  4. What comments would you have now in light of recent actions by Google and Mozilla in regards to them discarding SQL (Web Database and Gears in Chrome) completely and going with IndexedDb?

    1. No answer available yet because the Web SQL Databases and Web Offline standards among others are now in the state of limbo. It should be just use a Web SQLite database read/write … but that is no longer true as it has been “rescinded” as a recommended standard.

Comments are closed.