Would you use SQLite?

[Matts]

As some of you may have read, I've just finished creating a module called DBD::SQLite, which you can now find on CPAN. This is a DBD driver that is a self contained local RDBMS, so you install it and you don't have to install any server (like MySQL or Postgres), it just works. It's a lot like DBD::CSV or DBD::Sprite. In fact it's almost identical to DBD::Sprite except it's fast. The docs for DBD::Sprite say that it's only good for a few records. Well today I've been testing DBD::SQLite with 100_000 records, and it was still fast - results were coming back before I could blink. I'm going to update my tests to 1 million records, but it'll take a while.

But then Barrie Slaymaker asked me when and why you would use DBD::SQLite, and I honestly couldn't answer him. If you want a toy database for demos, DBD::CSV is fine and great for knocking apps together with. If you want to go into production with something, then you'll probably be happy with MySQL, and not be happy with SQLite, since it's completely typeless (it treats all columns as strings of variable length regardless). The only real argument for SQLite is that it's fast and easy to manage. In that sense it gives you all the advantages of DBD::Sprite (well, most of them), and none of the downside.

But what I really want to know is if you monks out there would use it, so that I can update the docs to reflect if/when/why you would use it. So please, let me know. Thanks!

[tilly]

Why wouldn't I use it?

My use would be a good place to cache data on a local filesystem. Say you have your database server, and several local machines. Suppose that you have a lot of read-only queries where the backing data does not change often.

Well then, why not cache some of the often hit stuff per webserver and not even bother the database unless you need to? The typing issue is unimportant since all of the data is actually managed elsewhere. Since the entire thing (drivers and all) is in one self-contained module, that makes installation even more convenient than MySQL.

UPDATE
After looking at the documentation for the database, read-only cached data looks like pretty much the only thing I would use it for. The locking is incredibly coarse-grained. However there are cases when it is convenient to have an easily-installed read-only data store.

[drewbie]

In the same vein, I'd like to see some benchmarks with SQLite vs Cache::Cache, DBM, etc for readonly caches. Since the write lock is global, reading would seem to be it's biggest benefit. If it's faster than Cache::Cache, then you have a potential user in me. :-)

As for what I would use it for, I'd probably use it in a project like the one I'm finishing up right now. It is a simple order app with 5 screens. Currently the data is stored in CSV files & read using Text::CSV. And I'm using hidden form elements to maintain state between screens. In this case, it's not worth it to use a "real" database. And I'm not even sure they have access to one. But DBD::SQLite might be just the ticket for this instance. As long as it's simple to compile/install.

[Corion]

I'm not sure whether I'd use SQlite, as I use databases mainly for two purposes :

• Data storage - of course, with the amounts of my stored data, I could most likely keep the whole stuff in memory and wouldn't need a "real" database for it.
• Process coordination/serialization - most "real" databases have internal protection against multiprocess issues, so I can offload the burden of maintaining a locked list to a database. I can easily set up producer/consumer relationships via a database table without having to worry about locking and stability; I can even upgrade a consumer or a producer on the fly without the other half noticing.

For the data storage part, the SQL syntax helps much here, as it makes searching easier to comprehend unless you want to do fancy (re/grep) searching. SQlite would help with the expandability/portability of the solution, as I could go from a flat file to a "real" database anytime.

I guess that, to use SQlite as a scheduler/coordinating entity, I would have to start a "SQlite SQL server" (you heard it first on http://www.perlmonks.org), which opens sockets, and have some "special" DBD driver for my other processes to connect to the central server to cope with forked childs. This is not necessarily bad, as there is no other administration overhead with this SQL server, but on the other side, such a SQL server could be written independant of the underlying storage structure; it would be possible to have a server over DBD::CSV as well.

These are my raw thoughts, if I come up with more points, I'll add them to this node ...

perl -MHTTP::Daemon -MHTTP::Response -MLWP::Simple -e ' ;    # The  
$d = new HTTP::Daemon and fork and getprint $d->url and exit;#spider
($c = $d->accept())->get_request(); $c->send_response( new   #in the
HTTP::Response(200,$_,$_,qq(Just another Perl hacker\n))); ' #  web

[Matts]

Actually SQLite will handle concurrency issues for you (with a big ugly global lock ;-)

So maybe that's one checkmark off for you.

[mirod]

I would (actually I probably will) use it in 2 cases:

• mono-user open-source software that needs a database: why impose installing MySQL for storing and analyzing log files? SQLite increases the potential audience for such a software. I am thinking about software that needs more than GDBM, SQL queries can be really useful, but less than MySQL,
• software that uses a read-only database: the fact that the DB is a single file makes it easier to duplicate it or move it, installation is much simpler, the fact that it is fast is a plus, and the lack of type is not a problem, as the data is generated outside of the DB. A display/search engine on top of "frozen" XML data for example (will you take the bait Matts?).

[maverick]

How about self contained demos? Put a copy of perl on the cd, have the autorun.inf fire up a little perl webserver and use DBD::SQLite as the database engine for your demo sites.

/\/\averick
perl -l -e "eval pack('h*','072796e6470272f2c5f2c5166756279636b672');"

[simon.proctor]

Well I had a think and my answer would be yes, I would use this module provided it was stable (;P). Seriously though, I think this module would become more useful the more you use it (if you see what I mean).

Some immediate ideas I had (not necessarily good ones) are:
1. Alternative to htaccess under apache (with mod_perl of course)
2. Provide a tie interface and use it to store logs
3. Those cases where you have no RDBMS on your host.
4. Learning tool for teaching simple SQL (TK Gui etc)
5. Caching (mentioned above but I liked it)
However, I'll probably download it, play with it and then use it for something totally different.

Just a thought :)

Simon

[trs80]

I am really surprised at how far you have followed this Matts, your original post to the dbi-dev included:

"Anyway, this is my first hack at a DBD driver, so it's likely buggy, leaky, etc. But seems to work for the simple selects I've run on it so far. I've done no optimization, no performance tests, or anything. I literally hacked this together today for fun. And I'm not particularly looking to maintain it unless there's really strong interest in it, and even then I'd rather someone offered to take it over."

I had assumed (falsely) that it was more of a programming exercise. Now it seems (here I go assuming again) that you are very interested in how this module is regarded by the community.

My opinions on this?


• The DBIx and DBD namespace is polluted right now. The number of choices is almost overwhelming for new comers.
• I like using MySQL as a database and it is convient for *me* to do that.
• I am interested in DBD::SQLite, but I haven't taken the time to play with it. So maybe I am not interested enough.
• I would be more interested if a clear benefit in using it over the other currently available solutions was presented.
• I don't consider speed enough of a benefit.
• Issues like number of users, remote access, support, etc. would contribute to my use of the module.
• Since you indicate(d) you are not interested in maintaining I am not likely to invest time into until someone does take over maintaining it.

In short I am looking for a compelling reason to *use* DBD::SQLite.

[zengargoyle]

Maybe. It actually sounds like a good fit for my current major undertaking.

I'm writing a server that listens to Cisco Netflow data, does some real-time statistics (the flows are saved for heavy-duty processing), looks for interesting developments and notifies somebody if needed. The coolest part is the client which connects to the sever over SSL, and allows you to query the current store of data. This sounds like a job for SQLite!!! The data doesn't need to be kept for long, speed is needed to keep up with the flows, the intended audience has a better grasp of SQL than perl (the current client part of the server is a eval $_ while(<>) type of loop), and it's read-only to boot.

Sounds like a winner to me.

[gav^]

A lot of my work involves data processing using some kind of database to store intermediate results. Usually I use Storable or MS-Access (yuck! but it allows a non-technical person the ability to edit it). The idea of being able to use a database that doesn't to be installed and just uses one file sounds great, the speed benefit sounds fantastic! DBD::SQLite is definatly on the list of my things to look into.

Update: does it build on win32 perl? /me investigates...

gav^

[Anonymous Monk]

The next release will compile on Win32. Had to do some minor fixes. It will also compile on 5.00404 in the next release.

Lost my logon on my laptop so posting anon ;-)

[dash2]

As a software developer providing commercial tools for customers on a wide variety of platforms, I think there is a crying need for a simple way to store information without relying on a particular database being installed. Many of our customers are small players with shared hosting. Some will have mysql, but we can't afford the maintenance/debugging/support costs of multiple versions. If DBD::SQLite is reliable enough, and has no security issues, I would definitely be interested!

dave hj~

[scottstef]

Just a thought- wouldn't this possibly be a quickie CGI tool/solution for people that use shared hosting? Having a quick, small database, that could be installed as a non-root user kinda makes sense.

"The social dynamics of the net are a direct consequence of the fact that nobody has yet developed a Remote Strangulation Protocol." -- Larry Wall

[SwellJoe]

Hi Matt,

tilly referred me to SQLite in a couple of threads I've posted (Performance quandary and Berkeley DB performance, profiling, and degradation...) regarding a BerkeleyDB based program I'm developing...

So, in the spirit of desparately trying stuff until something works so that I can stop working on this darned program once and for all, I've just converted it from using BerkeleyDB to DBI::SQLite, and it seems to be working great. So the answer to your question is, "Yep. I would use SQLite." It seems to be working quite nicely, except for one quirk that I think is likely due to my ignorance of the DBI (more on that in a moment). The test database is about a quarter million sub-4k objects, with the real database topping 2 million--I haven't run it on the big database but on the test db it works great! The old BerkeleyDB version was roughly equal or a little faster in the case of a few thousand objects, but performance declined rapidly due to some pathological handling code in my program (which using a relational db allows me to eliminate completely).

Anyway, it seems like an ideal solution for me, as I have to provide a simple to use set of RPMs for my programs and SQLite is a lot easier to package up and use than MySQL or PostGreSQL. I'll be spending the next few hours on converting the support tools and testing with my small db before rolling it out to a client site for more serious work (don't worry, I can keep an eye on things and deal with any bugs that pop up--I know it's early code). Anything I ought to be watching for? In a couple of posts, here and at use.perl, I think you've mentioned suspected memory leaks--still suspicious?

Now to my problem...I have the following line:

$dbh->do("INSERT INTO objects (md5, url) VALUES ('$md5', '$key')");

Which produces the following error:

DBD::SQLite::db do failed: near "'http://www.gnome.org/images/banner-c
+ontribute": syntax error at ./logtaild.pl line 113, <OBJECT> line 1.

But only on a few (very few) entries into the db. I got about 50 errors like this during the full 250,000 object insertion run. The md5 field is a CHAR(32) while the url field is a VARCHAR(8192)...Am I doing something wrong here? I don't quite know what to make of the extra "'" in the entries that lead to an error--there is nothing particularly different about these few entries that I can see. Searching the Monastary and Google doesn't bring me any enlightenment, so it doesn't seem to be a DBI general issue, but I may have missed it.

Anyway, thanks for the SQLite DBD--it's very cool, and worked right out of the box. One issue, it doesn't seem that the NDEBUG option is disabled in the module build...the SQLite author pretty strongly recommends doing so, at least when performance matters.

[broquaint]

Is this possibly because there are single quotes in the $key value? So your SQL may in fact look like this (minus the formatting :-)

INSERT INTO objects (md5, url)
VALUES (
    '$md5',
    ''http://www.gnome.org/images/banner-contribute'
--   ^ note the extra single quote!!!
)

If so, it would be a good case to use those ever handy DBI placholders

$dbh->do("INSERT INTO objects (md5, url) VALUES (?,?)"
         undef,
         $md5, $key);


HTH

broquaint

[Matts]

Also if you do a prepare outside your loop followed by the execute with placeholders inside the loop, you'll see an eensy weeny tiny bit of a performance boost, because I don't have to keep re-parsing the SQL.

But no, all memory leaks appear to be cleared up now.

And I'll look into the NDEBUG issue - thanks!

[SwellJoe]

Thanks broquaint!

That seems to have done it. I had tried that already, but I didn't get the syntax quite right so got strange errors (I left out the 'undef'--why is that there/required?).

[tomhukins]

My laptop only has 128M of memory, so I'd consider running a lightweight database like DBD::SQLite instead of something like MySQL or PostgreSQL to reduce memory requirements.

[lachoy]

FWIW, I don't think the amount of memory you have would preclude you from running either PostgreSQL or MySQL. I have a fairly old laptop P150/80MB and it runs mod_perl, PostgreSQL, X and a browser pretty well. Not great, but certainly sufficient for coffee-shop development and demo purposes.

Chris
M-x auto-bs-mode

[dragonchild]

I have a real production reason to use DBD::SQLite - storing page hits for a large CGI application. One application I used to work on had a MySQL front-end database (used for session info and page-hit stuff) and an Oracle back-end database for actual data. There was a table that recorded every hit this application received. That table took over 45 seconds to return any sort of useful information. (It was extremely useful in debugging issues that occurred when users did stupid things.)

Now, one thing I would've loved to have would be the information stored per week or per month, but making the table structure necessary would've been ... annoying ... to say the least. But, a simple module overlay on DBD::SQLite would've helped immensely. Just have a dbfile for each week and ATTACH it as necessary. From what I can see, it would've easily handled the session information, as well. (Though, again, we could've used a separate file for each session and ATTACHed as necessary ... ? I think this would've been better because there were four front-end servers in a round-robin setup ...)