|UltraLite SQL Databases|
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.
command line binary format (see screenshot above)and then you have
the basis for a great deal of interest and use; particularly for embedded
Here are the basic specs
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
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
archiving and better replication/synchronization plus messaging support for
databases like MySQL and PostgreSQL. Also security in its basic forms of
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
credentials may carry it into some
heavyweight application usage.
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.
Top of Page Tutorials Home