Motivation: New UltraLite versions of SQL like SQLite, SQLAnywhere and others shake up the database market.

While working on a review for the new PHP5 I came across the following interesting item as one of 5 key features of the new Zend Engine 2 and PHP5: " SQLite has been bundled with PHP. For more information on SQLite, please visit their website.". Now this little recommendation comes from the same people who partnered with MySQL way back when (approximately 1998) to become the database and application development part of the phenomenon known as LAMP=>Linux-Apache-MySQL-PHP. So what are Zend and the PHP people doing? Why are they distributing a new database with their newest product ? Well it all has to do with being lite, UltraLite.

These days processors are so cheap they are finding their way into any and every device as part of embedded systems. The estimated ratio is 20 devices with at least one embedded processor for every PC sold. And disk drives are becoming ultra small and cheap. Hitachi has bought out the IBM Microdrive division and can now deliver credit card sized drives with 1-4GB of storage selling in quantity for well under $100/unit. Ditto for Toshiba. Its not a hard stretch to look to provide some of those embedded processors with persistent storage. Millions of sensors no longer just storing data but also analyzing it and signaling"something of interest may be available - pick me up." Or "let me pass my data onto to the next monitoring point". And that's just the tip of the embedded database iceberg.

But embedded processors are cheap and often limited in both memory and processing power. Hence the need for an UltraLite database. Of course with most users familiar with Brobadingnangs such as DB2, Oracle and SQLServer, an UltraLite database seems like a oxymoronic exercise. However, there are actually probably two dozen UltraLite databases. What we highlight are two UltraLite SQL databases, one open source - SQLite and the other commercial - SQLAnywhere from Sybase plus a number of other interesting Ultralite developments. Common to both SQL Ultralites are very small size (both run in 250K or a lot less) plus a fairly robust implementation of SQL. And we are not kidding, these databases provide full SQL DML including joins, subselects, views, plus inserts, updates, and deletes with triggers and Commit/Rollback capabilities. In addition each supplies some special replication, stored procedure, and other database functionality.

SQLite SQLite began life as a portable database for a photo cataloging app called mPhoto. But it was written in a nearly SQL92 complete version - much more compliant than MySQL is even now. Also SQLite as a series of C routines has less than a 125K footprint. Finally all the tables and indices for a SQLite database are contained in one file on disk. These are quite attractive attributes for a database; but then make the database open source and distribute it on Linux and Windows in source and

command line binary format (see screenshot above)and then you have the basis for a great deal of interest and use; particularly for embedded apps. Here are the basic specs on SQLite.
+ Implements most of SQL92 including triggers, transactions, and integrity;
+ A complete database (multiple tables and indices) is stored in a single disk file;
+ ACID (Atomic, Consistent, Isolated, Durable) transactions but not nested ones;
+ Database files can be freely shared between machines with different byte orders;
+ Supports databases up to 2 terabytes (2^41 bytes) in size;
+ Small memory footprint: less than 25K lines of C code with numerous comments;
+ Self-contained: no external dependencies; but no stored procedures or synchronizations;
+ Faster than PostgreSQL and MySQL for many common SQL operations;
+ Very simple C/C++ interface requires the use of only three functions and one opaque structure;
+ TCL bindings included; also for COM, Java, Perl, PHP, Python, Ruby and others here;
+ Drivers available for ADO.NET, LIBDBI, JDBC, ODBC;
+ Simple, well-commented source code available in the public domain;
+ Built and tested under Linux and Windows with large automated test suite;
+ Windows self-contained executable=278K, configurable .DLL as low as 120KB;
What this list of features means is that developers can embed for free a complete SQL database into their applications with a very versatile database. This same database is gaining a wide array of links to popular development environs and servers. To really get an appreciation of what SQLite can do, users should download the administration program SQLite.exe (both Linux and Windows binaries are available). Read the documentation on it here. It is the command line version which comes in the downloadable Windows binary. With this program and the ODBC driver one can test out SQLite databases in a number of environs such as DBQwikEdit or DBVisualizer..

However just using the command line utility is quite a useful way to test out SQL syntax locally. In fact we were impressed that SQLite could do operations now that MySQL will only gain in the upcoming version 5. Even more impressive was the speed of processing. Our tests on a 100,000 record table confirmed that SQLite does indeed compare well with MySQL on basic select, update operations but we had mixed results on inserts and update operations. However, the program was most impressive and appears to be attracting a growing developer community. Direct PHP support will only help. What appears to missing from SQLite is the overall support, tools development, and marketing that has developed around MySQL and other Open Source databases such as PostgreSQL or Berkley DB. For example there are numerous tools and even add-ons for backup, archiving and better replication/synchronization plus messaging support for databases like MySQL and PostgreSQL. Also security in its basic forms of Grants/Revokes, encryption, signatures, journaling and compression are weak or just missing from SQLite. But like Linux in its youthful days, one can recognize a strong database program whose ultralite credentials may carry it into some heavyweight application usage.

SQL Anywhere

From Middleweight to UltraLite, SQL Anywhere is the just about the polar opposite of SQLite. Originally developed at the University of Waterloo as a full SQL but for PC and middle tier client/server applications, Watcom/SQL (as it was called in those days) with its robust data integrity options, stored procedures plus full query optimizations still could not break out of the middle tier. So when Sybase acquired Watcom it became clear that Watcom/SQLwould be redirected downward to be satellite processor for the top of the line Sybase SQLServer database. Fortunately, much had already been done to build up Watcom-now-SQLAnywhere's replication and database synchronization capabilities. But a growing demand existed from the embedded processors to PDAs and mobile phones - the embedded, industrial computing marketplace (think smart inventory magic wands, industrial controls and sensor/monitoring devices) had already grown around SQL Anywhere. And SQL Anywhere has served that market well. Its powerful SQL Anywhere Studio allows development of

database applications and data messaging on a wide range of programming languages and OS platforms. But in addition, SQL Anywhere has developed an UltraLite Analyser Technology that allows database developers to refine the size of an app depending on exactly which modules are required. The result is that a robust, yet ultrarefined app can come in at 75K in size for some of the demanding embedded and mobile apps. And since SQL Anywhere has been in the market for for a fair degree of time it has a good array of administration, backup, journalling and third party support tools. But the real attraction of SQL Anywhere is the robust transaction processing it allows. Not just two-phase commit, key and referential integrity but also enterprise caliber replication and synchronization capabilities. Added to this Sybase is making the cost of some of its developmental and deployment strategies more attractive. But Sybase has to pay heed to the new open source lightweight database options like SQLite, Berkley DB and others. But perhaps the most important players will come from the desktop.

Yukon and XML

Microsoft still owns the most popular desktop database, the mixed blessing that is Access. But soon a really finely tuned database engine, the new Yukon version of SQL Server will come distributed with the Longhorn operating system. This is very significant. Because Yukon through SQL Server has the pedigree to do replication, synchronization, data messaging, along with large scale clustered database operations. And SQL Server already has experience in downsizing to the Win/CE platform in a fashion similar to SQL Anywhere's Ultralite Analyser Technology. If the AS/400 continues to thrive 20 years after intro because of its database built into the operating system - can you imagine what a lease on life Yukon may bring to the security and reliability tarnished Windows desktop OS ?

And the hidden jewel in the SQL Server crown is its extensive XML storage and data interchange enablement. in fact XML databases using some combination of XPath, XPointer, XSD, XForms, XQuery, XSLT and XML-WebServices are already starting to proliferate in EAI-Enterprise Application Integration settings. Since XML has three very strong virtues: standards-based cross platform capabilities, huge libraries of ever-better optimized code, and rich bindings to just about every programming language, database and application server; XML will be a growing database presence. On the downside XML's bloated verbosity and penchant for incessant transliterations has to be managed carefully. But XML is rapidly emerging as the adhoc persistent store and data interchange media of choice. Now what remains to be seen is whether this XML can be placed in an ultralite and performant package. But consider this, every product and every service are enhanced by giving them ever richer memories plus ever more accessible and smarter persistent datastores. Literally there are hundreds of millions of applications awaiting their custom Ultralite database.