Perl-Sensitive Sunglasses PerlMonks

### Re^4: SQLite with mod_perl

by punkish (Priest)
 on Oct 09, 2010 at 02:12 UTC ( #864309=note: print w/replies, xml ) Need Help??

in reply to Re^3: SQLite with mod_perl
in thread SQLite with mod_perl

Hi james2vegas, sorry for the delay in reply.

Well, I thought I had surmounted this problem, but no, it is back with a vengeance. Here is what is going on in a single web action --

    SELECT col1 FROM table WHERE condition;
if (col1 exists) {
UPDATE table SET col2 = <new value>
}
[download]

Well, I get the "db is locked" error at the UPDATE statement. Now, in my httpd.conf I started using

    PerlModule Apache::DBI
[download]

and I thought my troubles were over. Except, this lock problem has started rearing up again. I have tried a few other things, such as starting every db action with $dbh->begin_work and ending with$dbh->commit, but I am already using AutoCommit => 1 so I am not sure if that even has any effect. I have even tried sqlite_use_immediate_transaction => 1, but that doesn't help either.

My application is being loaded in Apache conf file with

    <Location />
SetHandler perl-script
PerlHandler Plack::Handler::Apache2
PerlSetVar psgi_app /path/to/application.pl
</Location>
[download]

So, I want all the perl modules to be loaded when Apache2 starts, and then a $dbh created for each user when the user comes to the web site, but not recreated for the same user on every reload. I am assuming that would be the correct way to work speedily. -- when small people start casting long shadows, it is time to go to bed Replies are listed 'Best First'. Re^5: SQLite with mod_perl by james2vegas (Chaplain) on Oct 09, 2010 at 09:27 UTC Do not cache your database handles, rather let Apache::DBI take care of it, use Apache::DBI in your mod_perl startup script (or use PerlModule Apache::DBI in your httpd.conf) then whenever you need to open a database connection, do it as would normally my$dbh = DBI->connect(...). If you need connections per user add private_username => $username to your connection attributes, like so: my$dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", "", "", { private_ +username =>$username });
[download]
This would ensure that each user would get a per-process database handle. Note that you can't share database handles across processes unless you use something single process like FastCGI.
Hmmm... as I mentioned in my post above, I am indeed using  PerlModule Apache::DBI. Maybe I tinkered above (too smart for my own good) trying to cache the db handle. Now I am trying with just plain my \$dbh = ... , and things seem to be working.

One other thing I have done now -- I have turned off transactions. My db handle is now created with AutoCommit => 1.

This is really unreliable. I have no idea what caused my app to start puking and croaking yesterday, and what caused it to start working today. I have become too dependent on SQLite (and for good reason -- it is a great db store), and I really don't know of any alternatives. Any suggestions? I could try some of the key-value stores such as MongoDB/CouchDB, etc., but it would require learning a whole new paradigm, and writing new code for retrieving values (is there a DBI interface to a key-value store? **) While I do use PostGres for the bigger datasets, SQLite seems perfect for serving as the Session store, and user accounts and such.

Update: Hot damn! DBD::DBM

--

when small people start casting long shadows, it is time to go to bed

Create A New User
Node Status?
node history
Node Type: note [id://864309]
help
Chatterbox?
 erix . o O( "what fools the french are, Jeeves" ) [choroba]: Also some Достое́вс кий [Discipulus]: mmh windows understand something like: cd c:\\\\path\\\\to weird.. [Discipulus]: even with odd number of \

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (9)
As of 2017-05-24 08:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
My favorite model of computation is ...

Results (183 votes). Check out past polls.