SQLite as a Light DB component for Zope and Python
I have noticed on the DB lists lately some concern about the future of Gadfly. I have been investigating a marvelous little open-source, no copyright, SQL engine called <http://www.hwaci.com/sw/sqlite/index.html>SQLite: An SQL Database Engine In A C Library. I am quite experienced with Python, reasonably experienced with Zope but a greenhorn at extending Python yet I had a crude but working Python extension module for SQLite up and running in 2 days (most of that time figuring out the Python extension conventions). I think Python needs a lightweight SQL engine as a standard module, and I think this would be a good Zope product candidate as well. I'm proposing SQLite as that engine. Here is the developer's feature list, taken from the link given above: Implements a large subset of SQL92. A complete database (with multiple tables and indices) is stored in a single disk file. Atomic commit and rollback protect data integrity. Small memory footprint: less than 20K lines of C code. Four times faster than PostgreSQL. Twice as fast as SQLite 1.0. Very simple C/C++ interface requires the use of only three functions and one opaque structure. TCL bindings included. A TCL-based test suite provides near 100% code coverage. Self-contained: no external dependencies. Built and tested under Linux and Win2K. Sources are uncopyrighted. Use for any purpose. The SQLite source code is 35% comment. These comments are another important source of information. 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. Thanks for listening. Bill Trenker Internet Applications Developer Kelowna, BC, Canada "The commandments of the LORD are right, bringing joy to the heart. The commands of the LORD are clear, giving insight to life . . . For this is the love of God, that we keep His commandments. And His commandments are not burdensome." (Psalm 19:8, 1John 5:3) <http://torahteacher.com/>torahteacher.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.343 / Virus Database: 190 - Release Date: 3/22/02
Very interesting. I think I've read about this somewhere before. The claim of "4x faster than PostgreSQL" raised my brow. It is true that Gadfly is becoming quite stale, and only supports a *very* limited subset of SQL. It also would be nice to see something a bit more robust than just shelve in the Standard Library. Gadfly is ok to begin a prototype in, but frustrating once you quickly hit a glass ceiling in functionality. I'm interested in seeing how well it can scale, versus Postgres, however. Any experience? Thanks for the info, Eron Lloyd On Mon, 2002-04-01 at 20:54, William Trenker wrote:
I have noticed on the DB lists lately some concern about the future of Gadfly. I have been investigating a marvelous little open-source, no copyright, SQL engine called <http://www.hwaci.com/sw/sqlite/index.html>SQLite: An SQL Database Engine In A C Library. I am quite experienced with Python, reasonably experienced with Zope but a greenhorn at extending Python yet I had a crude but working Python extension module for SQLite up and running in 2 days (most of that time figuring out the Python extension conventions). I think Python needs a lightweight SQL engine as a standard module, and I think this would be a good Zope product candidate as well. I'm proposing SQLite as that engine. Here is the developer's feature list, taken from the link given above:
Implements a large subset of SQL92. A complete database (with multiple tables and indices) is stored in a single disk file. Atomic commit and rollback protect data integrity. Small memory footprint: less than 20K lines of C code. Four times faster than PostgreSQL. Twice as fast as SQLite 1.0. Very simple C/C++ interface requires the use of only three functions and one opaque structure. TCL bindings included. A TCL-based test suite provides near 100% code coverage. Self-contained: no external dependencies. Built and tested under Linux and Win2K. Sources are uncopyrighted. Use for any purpose. The SQLite source code is 35% comment. These comments are another important source of information.
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.
Thanks for listening.
Bill Trenker Internet Applications Developer Kelowna, BC, Canada
"The commandments of the LORD are right, bringing joy to the heart. The commands of the LORD are clear, giving insight to life . . . For this is the love of God, that we keep His commandments. And His commandments are not burdensome." (Psalm 19:8, 1John 5:3) <http://torahteacher.com/>torahteacher.com
----
--- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.343 / Virus Database: 190 - Release Date: 3/22/02
--- [This E-mail scanned for viruses by Declude Virus]
On Tue, Apr 02, 2002 at 10:02:22AM -0500, Eron Lloyd wrote:
Very interesting. I think I've read about this somewhere before. The claim of "4x faster than PostgreSQL" raised my brow. It is true that Gadfly is becoming quite stale, and only supports a *very* limited subset of SQL. It also would be nice to see something a bit more robust than just shelve in the Standard Library. Gadfly is ok to begin a prototype in, but frustrating once you quickly hit a glass ceiling in functionality. I'm interested in seeing how well it can scale, versus Postgres, however. Any experience?
Scale, as in multiuser? Hardly at all: it's an SQL library that accesses a single, textbased, flatfile for the entire database. From the FAQ, multiple readers are allowed (on Unix), but the entire file (yes, that's the whole database, not a single table) is locked for one backend to write. As a lightweight replacement for gadfly, it looks like it might be pretty good. Note that the scripting language of choice of the author seems to be Tcl, rather than Python. This probably explains the 'everything is a string' approach :-) The speed comparisions with PostgreSQL are very much an apples vs. fish sort of thing: the pgsql server was not tuned _at all_, and does a whole lot more that was never tested, such as multi-user writer access. Ross
At 10:45 AM 4/2/02 -0600, "Ross J. Reedstrom" <reedstrm@rice.edu> wrote:
Scale, as in multiuser? Hardly at all: it's an SQL library that accesses a single, textbased, flatfile for the entire database.
As a lightweight replacement for gadfly, it looks like it might be pretty good.
Exactly. I'm proposing this as a lightweight component, just as you say, not as a replacement for something like MySQL or PostgreSQL. What I have in mind is a small, simple, built-in SQL engine that could be used as a step up from something like TinyTablePlus in Zope and be compact enough to even be considered as a module candidate for the standard Python library.
Note that the scripting language of choice of the author seems to be Tcl, rather than Python. This probably explains the 'everything is a string' approach :-)
If you look into the C interface you will find it is almost trivial to build a Python extension module and "bring" SQLite into the realm of Python scripting. SQLite also has it's own C interface for adding expression functions and aggregates to the SQL syntax. I expect this could be hooked into Python through the Python extension interface as a callback. Again, the "everything is a string" approach fits in with the idea of "simple, lightweight". Mind you, SQLite supports SQL expressions so it can do things like "SELECT * FROM my_table WHERE my_field / 2 > 23.8", or "INSERT INTO my_table VALUES (100 / 30.0)". SQLite does implicit conversions, as required.
The speed comparisions with PostgreSQL are very much an apples vs. fish sort of thing: the pgsql server was not tuned _at all_, and does a whole lot more that was never tested, such as multi-user writer access.
You could be right. I'm not an expert with PostgreSQL so I can't comment. But, at the risk of being repetitious, I'm thinking "lightweight". My intent is to propose a small relational tool that doesn't impose a significant overhead on the host system and might be simple enough that the Zope and Python developers would consider SQLite, together with a Python DB API and Zope DA, for their standard libraries. Thanks for commenting, Bill "The commandments of the LORD are right, bringing joy to the heart. The commands of the LORD are clear, giving insight to life . . . For this is the love of God, that we keep His commandments. And His commandments are not burdensome." (Psalm 19:8, 1John 5:3) <http://torahteacher.com/>torahteacher.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.343 / Virus Database: 190 - Release Date: 3/22/02
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
At 01:58 AM 4/3/02 +0200, Magnus Lyckå <magnus@thinkware.se> wrote:
So, we expect to see the announcement of a DB-API 2 complient SQLite driver any day then! :-)
I wish ! :-) Actually, I'd love to try it. But maybe there are some DB-API-2 experienced folks out there who could do this in their sleep (if they ever get any time to sleep). The SQLite C API is really that simple -- one data structure pointer representing an open database, 3 functions (open_db,close_db,execute_sql) and 1 callback (to handle the rows in the result set). Of course I'm being a bit silly -- It takes lots of work to put any robust piece of software together. I'll probably take a stab at it but I wanted to see if the Python/Zope world is even interested.
I'd say a small subset [of SQL92], or perhaps sideset:
Yet but probably sufficient for a small, local relational data store.
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.
Yes, that's what I thought; for small amounts of data a large, fully featured RDBMS is overkill. Yet, often, flat tables are not the solution either. Even for small data stores the benefits of the relational model still apply and flat files can be a real pain when the data relationships are complex. For instance, as a simple use-case, consider the frequent, common software application need for storing configuration data. Often, this data is stored in a collection of flat files. The amount of data in these files may be relatively small but the data relationships can still be very complex. As we all know a collection of flat files doesn't directly provide the mechanism to implement these data relationships. But most RDB software is far too large to justify using this well established data technology for configuration data. I have often thought it would be beneficial to have a small, low-overhead SQL engine as a software component. I am suggesting SQLite as a candidate.
If it lifts entire tables into RAM it might be very memory hungry for large databases.
This is an excellent and important observation. One of the reasons I'm suggesting SQLite is that it's memory model is, fortunately, more sophisticated than simply lifting entire tables into RAM. To quote from The Architecture Of SQLite (http://www.hwaci.com/sw/sqlite/arch.html): "The B-tree module requests information from the disk in 1024 byte chunks. The page cache is responsible for reading, writing, and caching these chunks at the behest of the B-tree module. The page cache also provides the rollback and atomic commit abstraction and takes care of reader/writer locking of the database file. The B- tree driver requests particular pages from the page cache and notifies the page cache when it wants to modify pages or commit or rollback changes and the page cache handles all the messy details of making sure the requests are handled quickly, safely, and efficiently."
So, how active is the mailing list? And how good? I saw a subscribe link, but no archive (maybe I just missed it.)
When you sign up you are taken to a page that lets you have the option of getting to the archives. The SQLite mailing list is a Yahoo eGroup forum so to get at the archives you have to sign up with Yahoo -- oh well. Thanks for commenting, Bill "The commandments of the LORD are right, bringing joy to the heart. The commands of the LORD are clear, giving insight to life . . . For this is the love of God, that we keep His commandments. And His commandments are not burdensome." (Psalm 19:8, 1John 5:3) <http://torahteacher.com/>torahteacher.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.343 / Virus Database: 190 - Release Date: 3/22/02
Magnus Lyckå wrote:
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:
If you campare it with OCELOT you are right but you have to compare it with Gadflay.
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.)
The SQLite mailing list is very active. I am a list member. Jos
I'm working with SQLite right now, I find it a very interesting product, SQLite is tiny, fast, I think it is fast at least as MySQL but SQLite has TRANSACTIONS and it easy to install, for now it needs a python interface based on DB-SIG. IMO SQLite is a good candidate to replace Gadflay Jo William Trenker wrote:
I have noticed on the DB lists lately some concern about the future of Gadfly. I have been investigating a marvelous little open-source, no copyright, SQL engine called SQLite: An SQL Database Engine In A C Library <http://www.hwaci.com/sw/sqlite/index.html> . I am quite experienced with Python, reasonably experienced with Zope but a greenhorn at extending Python yet I had a crude but working Python extension module for SQLite up and running in 2 days (most of that time figuring out the Python extension conventions). I think Python needs a lightweight SQL engine as a standard module, and I think this would be a good Zope product candidate as well. I'm proposing SQLite as that engine. Here is the developer's feature list, taken from the link given above:
Implements a large subset of SQL92. A complete database (with multiple tables and indices) is stored in a single disk file. Atomic commit and rollback protect data integrity. Small memory footprint: less than 20K lines of C code. Four times faster than PostgreSQL. Twice as fast as SQLite 1.0. Very simple C/C++ interface requires the use of only three functions and one opaque structure. TCL bindings included. A TCL-based test suite provides near 100% code coverage. Self-contained: no external dependencies. Built and tested under Linux and Win2K. Sources are uncopyrighted. Use for any purpose. The SQLite source code is 35% comment. These comments are another important source of information.
The author, D. Richard <mailto:drh@hwaci.com> Hipp <mailto:drh@hwaci.com> , 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.
Thanks for listening.
Bill Trenker Internet Applications Developer Kelowna, BC, Canada
"The commandments of the LORD are right, bringing joy to the heart. The commands of the LORD are clear, giving insight to life . . . For this is the love of God, that we keep His commandments. And His commandments are not burdensome." (Psalm 19:8, 1John 5:3) torahteacher.com <http://torahteacher.com/>
------------------------------------------------------------------------
--- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.343 / Virus Database: 190 - Release Date: 3/22/02
participants (5)
-
Eron Lloyd -
jose -
Magnus Lyckå -
Ross J. Reedstrom -
William Trenker