SQLite is quite SQL compliant

[John Nagle]

On 10/2/2010 3:06 PM, Seebs wrote:

> I would agree that the word "nonstandard" seems to be a little strong and
> discouraging.  sqlite is a source of joy, a small bright point of decent
> and functional software in a world full of misbehaving crap.  While it
> does omit a few bits of SQL functionality, I'd call it perhaps a "slightly
> incomplete implementation" rather than a "nonstandard variant".

     That's a bit much.

     What SQLite leaves out is the heavy machinery needed for a active
production database being used by many processes.  If you want to store
a modest amount of data for one process, perhaps with a rare conflict
when two programs hit the same table, SQLite is fine. But SQLite
doesn't scale.  That's why it's "lite".

     Some of SQLite's good features, are achieved by rather brutal
means.  For example, SQLite supports atomic transactions.  That's
implemented by locking up all tables involved for the duration
of the entire transaction.  This is fine for low-volume updates,
and a killer for high-volume systems.

     SQLite doesn't have a serious query optimizer, or partial table
locking, or concurrent transaction handling, or replication.
In other words, use SQLite in your desktop app to manage its data
or configuration parameters.  Use MySQL or Postgres for your
web site.

     (Personally, I like MySQL, but I fear Oracle will mess it up.)

					John Nagle