Categories
Mozilla Web Development

The Winner For Most Embedded Is: SQLite

So the format war of Blue-ray vs. HD-DVD is over. There are still several other rather significant battles going on in the tech world right now that aren’t Microsoft vs. Apple or Yahoo vs. Google. For example:

Adobe Air vs. Mozilla Prism vs. Microsoft Silverlight

Google Gears vs. HTML5 Offline support

Android vs. iPhone SDK vs. Symbian

Ruby On Rails vs. PHP

Not every case will have a true “winner”. That’s not really a bad thing. Choice is good. In some cases they will merge to form one standard, such as what’s likely for offline web applications.

What is interesting is that SQLite really dominates right now. Adobe Air, Mozilla Prism, Google Gears, Android, iPhone SDK (likely through Core Data API), Symbian, Ruby On Rails (default DB in 2.0), PHP 5 (bundled but disabled in php.ini by default). It’s becoming harder and harder to ignore that SQL survived the transition from mainframe to server, and now is going from server to client.

No longer is the term “database” purely referring to an expensive RAID5 machine in a datacenter running Oracle, MySQL, DB2 or Microsoft SQL Server. It can now refer to someone’s web browser, or mobile phone.

This has really just begun to have an impact on things. The availability of good information storage, retrieval, and sorting means much less of these poorly concocted solutions and much better applications. Client side databases are the next AJAX.

Edit [2/27/2008 9:14 AM EST]: Added Symbian, since they also use SQLite. Thanks Chris.

7 replies on “The Winner For Most Embedded Is: SQLite”

I don’t think there’s a Google Gears vs HTML5 battle — we’re both working closely together, and HTML5 has been learning from Gears and Gears has been leaning towards the HTML5 APIs.

In fact if anything I would say that HTML5 belongs in the “Adobe Air vs. Mozilla Prism vs. Microsoft Silverlight” line.

@Ian Hickson: Good to hear about Google Gears working with HTML5. I hate the idea of multiple platforms.

I’m not so sure I’d put HTML5 with Adobe Air vs. Mozilla Prism. vs. Microsoft Silverlight. My main reason is that they are all platforms rather than standards. Also Adobe Air uses WebKit, Prism uses Gecko, both likely HTML5 adopters. So Adobe Air is HTML5 proprietary. Mozilla Prism is XUL HTML5. HTML5 not end-user marketable since it’s not a product, it’s a standard. Air, Prism, Silverlight are. Google Gears if it succeeds would likely end up being both since I’m pretty sure Google will push for it and even put their developers on the task of implementing anywhere and everywhere to ensure their Apps work correctly anywhere.

@Chris: good point, I accidentally left them out when putting that list together. Added now.

SQLite is absolutely fantastic for embedded applications. Just not having to write code to save and read persistent data, nor to have to ensure its consistency, integrity, etc is is a boon for programmer productivity. A small footprint and pretty good performance doesn’t hurt either. Having SQL at the client means we can get server-side productivity benefits in the client and in embedded devices.

Check out my page at http://www.squidoo.com/sqlitehammer for more discussion on SQLite. Mike Chirico’s page at souptonuts.sourceforge.net is also a great resource on programming SQLite.

What follows is just my opinion.

The consortium members ought to agree to adopt all changes that the SQLite architects may make to the SQLite core and ought to agree to maintain a basic degree of interoperability as defined by the architects. A SQLite database developed in one application ought to remain portable and queryable by another, at least to the extent that it makes no use of any proprietary features introduced by a consortium member.

A case in point: Adobe AIR treats INT PRIMARY KEYS as if they were INTEGER primary keys yet in the SQLite core these are treated very differently: INTEGER PRIMARY KEYS are treated as an alias for the rowid; but a column defined as INT PRIMARY KEY is treated as a normal column, not an alias. In order to get accurate query results in AIR with a SQLite database created outside of AIR, any table with an INT PRIMARY KEY must be recreated without a PK declaration, and a unique index must be placed on the INT column instead. Adobe has said it will document this difference in an upcoming revision of their docs.

Developers also should not have to guess why a query plan is not the same from one consortium member to the next. If SQLITE3.EXE EXPLAIN QUERY PLAN returns a plan for a query that is different from from the plan returned by a consortium member’s implementation, it should be possible for the developer to consult the consortium members documentation to find out why. If interoperability is sacrificed to some other end, the consortium member should be as open about that choice as possible.

There should be more to consortium membership than an obligation to fund core development.

Leave a Reply

Your email address will not be published. Required fields are marked *