[Zope-DB] Re: [DB-SIG] SQLite as a Light DB component for Zope and Python

Magnus Lyckå magnus@thinkware.se
Wed, 03 Apr 2002 01:58:56 +0200


At 17:54 2002-04-01 -0800, William Trenker wrote:
>but a greenhorn at extending Python yet I had a
>crude but working Python extension module for
>SQLite up and running in 2 days

So, we expect to see the announcement of a DB-API 2
complient SQLite driver any day then! :-)

>Implements a large subset of SQL92.

I'd say a small subset, or perhaps sideset:

SQLite implements the follow syntax:
·       BEGIN TRANSACTION (END COMMIT ROLLBACK)
·       COPY
·       CREATE INDEX
·       CREATE TABLE
·       CREATE VIEW
·       DELETE
·       DROP INDEX
·       DROP TABLE
·       DROP VIEW
·       EXPLAIN
·       expression
·       INSERT
·       ON CONFLICT clause
·       PRAGMA
·       REPLACE
·       SELECT
·       UPDATE
·       VACUUM
(Or at least that's what the language reference at
http://www.hwaci.com/sw/sqlite/lang.html says.)

No GRANT / REVOKE or any other kind of security.
It seems to be a plain single user thingie.

No ALTER TABLE, SET TRANSACTION etc. Ok, these aren't
actually in Entry SQL 92, but everybody else has them...

And COPY, PRAGMA, REPLACE, VACUUM, ON CONFLICT etc is
not in any SQL standard I know of.

It violates SQL92 in a number of ways it seems. For
inststance, it's typeless (like GadFly) which means that
for instance "00" == "0". There are a lot of error
checking in SQL that this engine won't do.

SQLite ignores checks in CREATE TABLE and has no foreign
keys?

But it seems a bit closer to SQL than GadFly...and apart
from typelessness it seems to support rather extensive
SELECT statements. It might be a very useful thing I guess.
There are plenty of cases where installing an RDBMS is
overkill.

>Small memory footprint: less than 20K lines of C code.

Erh? Measuring memory footprint in kLoC seems a bit like
measuring mass in meters. Although according to the 2.4.0
release notes at freshmeat it seems the binary image needs
less than 200kB! Question is how much RAM that actual data
requires. If it lifts entire tables into RAM it might be
very memory hungry for large databases.

>Four times faster than PostgreSQL. Twice as fast as SQLite 1.0.

Yea yea, it just depends on what you measure. And how.
PostgreSQL is infinitely faster on the features SQLite
doesn't support... Benchmarking is a bit too complex to
just describe like a scalar like that.

>The author, <mailto:drh@hwaci.com>D. Richard <mailto:drh@hwaci.com>Hipp, 
>is a computer science Ph.D. who knows his stuff.  This is not green 
>software, it is well designed and tested.  It was first released in May 
>2000 and is very actively updated and supported.

So, how active is the mailing list? And how good? I saw
a subscribe link, but no archive (maybe I just missed it.)



-- 
Magnus Lycka, Thinkware AB
Alvans vag 99, SE-907 50 UMEA, SWEDEN
phone: int+46 70 582 80 65, fax: int+46 70 612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se