Tue Aug 27 04:49:46 EDT 2019
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:
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!)
More information about the use-livecode