free, lightweight sql engine

[revolution at knowledgeworks.plus.com]

I thought this might be of interest to Revolution developers.  SQLite is an cross-platform embedded SQL engine (basically DLL and an ODBC driver).  It appears to be completely free (as in beer and speech).  In this sense it is more free than MySQL (my understanding is that the latter is not free for use if distributed as part of a commercial application).

There are bindings for many different languages (there could be a native Transcript binding if Runrev were so inclined, and SQLite could be distributed as a part of Rev).  

In terms of SQL compliance, it seems to be at the level that MySQL was at up until the last release of MySQL.  Obviously, it is not a multi-user relational database (but then it also doesn't cost thousands of pounds/euros/dollars, nor a DBA....)

Here's some more info.  I haven't had time to test it other than to see that I could create a database and populate it with test data, and then connect to it from within Rev and query the data.  (I'm involved in so many other technology assessments right now that I just don't have time to use this, but thought this could be of use to Revolution users...  )

This is the main site: http://www.hwaci.com/sw/sqlite/

Sqlite seems to offer the best kind of relational storage for a Revolution/Metacard stack.  It seems to me that the primary reason why Rev/Metacard developers might want to store data in a RDBMS is to be able to access data faster than they can if it was simply stored in stacks (and really the key here is to be able to index the data).

What does Sqlite have to recommend it?

Sqlite is multiplatform - http://cvs.hwaci.com/sqlite/wiki?p=HowToCompile
(One doesn't necessarily have to compile it - there are binaries for Windows and Linux here: http://www.hwaci.com/sw/sqlite/  I was unable to find a binary for OS X).

The databases can be accessed from various languages and wrappers:
http://www.hwaci.com/sw/sqlite/lang.html
http://cvs.hwaci.com/sqlite/wiki?p=SqliteWrappers
(It is not surprising to see Delphi, Perl and Smalltalk in that list, but it is quite surprising to see Java and PHP.  Apparently the use of SQLite as the backend for websites is growing very rapidly).

There is an ODBC interface, so it can already be accessed from Revolution: http://www.ch-werner.de/sqliteodbc

I'm sure many traditional programmers would think that it is a bad idea to have typeless columns in a relational database, but I think this could appeal to Metacard/Rev developers: http://www.hwaci.com/sw/sqlite/datatypes.html

>From the benchmarks on that site (and another site referenced on there), it provides data access that is faster than Postgresql and MySql (of course, anyone who is going to use it instead of an alternative product should do their own benchmarking).  But if these figures are true, then this is quite significant since MySql is often cited as being faster than most other well-known 'relational' databases (the scare quotes are there because users of high-end commercial relational databases dispute that MySql is relational - but it is also debatable that ANY of the mainstream commercial systems are truly relational).  I am sure they would fault Sqlite too, for similar reasons.

If you really, absolutely, positvely must have speedy access, you can even put the entire database in RAMhttp://cvs.hwaci.com/sqlite/wiki?p=InMemoryDatabase  A database that is totally RAM-based will almost certainly be orders of magnitude faster than one that is retrieving data from the hard disk.

The author even provides considerable detail about how Sqlite works: http://www.hwaci.com/sw/sqlite/opcode.html  I'm not aware of another database provider that gives such an easy overview of the workings of the engine.

One might worry about how SQL-conformant this product could be.  Well, there is an issue with _all_ RDBMS and how SQL-conformant they are (i.e. there is no single SQL standard, so in a sense none of them are SQL conformant).  But when it comes to a vitally important issue such as how nulls will be treated, he is quite upfront and pragmatic: nulls will be handled in a way that conforms with the majority of RDBMSs: http://www.hwaci.com/sw/sqlite/nulls.html

So what are the obvious limitations?  Well, locking seems like it would be an issue in a multi-user environment:

(from the documentation)
>>
Locking in SQLite is very course-grained. SQLite locks the entire database. Big database servers (PostgreSQL, Oracle, etc.) generally have finer grained locking, such as locking on a single table or a single row within a table. If you have a massively parallel database application, you should consider using big database server instead of SQLite....Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at once.
<<

With regard to licensing, it looks totally liberal: (from the FAQ)
>>
(15) Can I use SQLite in my commerical product without paying royalties?
Yes. SQLite is in the public domain. No claim of ownership is made to any part of the code. You can do anything you want with it.
<<

The author also lists the other features of SQL-92 that are omitted (I do not believe that any of the mainstream RDBMSs are fully-compliant with SQL-92):  http://www.sqlite.org/omitted.html

To me the most significant 'omission' is the absence of foreign keys.  (I like the fact the author sees these as omissions - most DB vendors trumpet how they are compliant with a subset of SQL-92).  I guess since it is not a commercial or ideological thing, he has nothing to lose by highlighting the 'omissions' of the engine.

It might also seem odd to those of you that have used client/server DBs that there is no authentication.  But as the author points out, Sqlite is an embedded database, and it is not really appropriate:
>>Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine. 
>>
As a side-note, it is not uncommon to find large organisations which totally bypass the DB vendors authentication in client-server applications, controlling access to the data programmatically as one would have to do with Sqlite.
And just to show that it is being used in serious projects:
http://cvs.hwaci.com/sqlite/wiki?p=SqliteUsers

There are a couple of (win32) GUI programs for interacting with SQLite databases: http://members.rogers.com/mbi/software/software_index.htm

If you want to see how well-supported it is (and how clued-up many of the users of it are) then visit the support forum: http://groups.yahoo.com/group/sqlite/

I think Runrev should investigate the inclusion of SQLite, and think about producing a native Transcript wrapper to access Sqlite databases.  Until they do that, it could be accessed via Shell commands or ODBC.  

I have no association with Sqlite or the provider of it.

Oh yes, I forgot to mention: the whole engine of the database is only 300k in size..... Downloading the binaries for the database and the command-line program took less than 5 seconds on my adsl link!  

If it proves adequate to your needs, why bother including a bigger and more complicated engine with your application (and lose a share of your profits in licenses...)

I hope this is of use to people on this list.  If anyone is interested I suggest they follow the links I provide, and direct any questions to the SQLite group at Yahoo (because really I don't know any more than I've said here!)

Regards 
Bernard