On Sat, Oct 9, 2010 at 6:24 AM, Simon Slavin <[email protected]> wrote: > > On 9 Oct 2010, at 7:49am, P Kishor wrote: > >> <stuff> > > My answers to these things are a little weird and I'm not sure I understand > at all what you're doing. But it's a weekend so I'll answer anyway and let > you correct me where I've misunderstood it. > >> SELECT col1 FROM table WHERE condition; >> if (col1 exists) { >> UPDATE table SET col2 = <new value> >> } >> >> Well, I get the "db is locked" error at the UPDATE statement. > > Because you're trying to UPDATE a table while you're in the middle of looking > at it, and the UPDATE you make might change the thing you're looking at, > which would ruin the SELECT.
I am not. The above was pseudo code. >There are two cures: > > Cure#1: Do the whole SELECT first, and store the answer in an array. Then > work through the array issuing UPDATE commands. This is not the most > efficient way to use SQL but it makes sense to people who are used to > programming. > > Here is cure #2, which is more likely to be the SQL way of doing it. > > I'm not really sure what you mean by your '(col1 exists)' bit, but let's get > rid of it for a second and look at the rest of it: > >> SELECT col1 FROM table WHERE condition; >> UPDATE table SET col2 = <new value> > > The way you do this in SQL is this: > > UPDATE table SET col2 = <new value> WHERE condition > > So you have one instruction that does all the work for you, including > ignoring records it doesn't need to change. Now, what did you mean by '(col1 > exists)' ? Pseudo code. > >> 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 think you may have misunderstood how Apache does its job. Unless you do > things in an unusual manner, Apache treats each page request as a separate > 'program', It spawns a separate 'httpd' process to respond to each request, > and the various processes don't talk to one-another. Variables and handles > belong to one particular request for a web page, and disappear as soon as the > reply to that request has been sent. > Yes, that is how Apache under normal CGI works. But Apache with mod_perl is a totally different beast. Perl is compiled inside Apache, and my entire program is compiled and loaded in memory once when Apache starts up. Apache still starts separate processes for each request, but all those processes use the same instance of the once compiled program handled by the instance of Perl compiled within Apache. Hence, variables can be shared within processes unless care is taken to program so they don't. > So you can't store a database handle between page requests. Nor should you > want to, because you won't be able to tell when the last request comes: any > user can close their browser window at any time and you'd never know when to > do sqlite3_close(). You close the connection after servicing each web page > request. > > Treat the servicing of every web page request as a separate running of your > program: once it knows it's going to need a SQLite database it opens it using > its own handle, and it closes it once it had done all the SQL stuff it's > going to need to do. Of course, if you have many users, each with web > browsers open at the same time, then you have many different processes > accessing your SQLite database, and you have to handle multi-user situations > correctly. No, to the extent that I understand, mod_perl does not work that way. My problem is occurring because a shared_lock on the entire db file is being created for the SELECT, and then that lock is not being released, so the lock required for the UPDATE is not being given out. This may be as explained in http://www.sqlite.org/faq.html#q6 I am just trying to solve the above. It may well be that sqlite and mod_perl may not be good companions (in which case, I am up a **** creek without a db, and will have to look for an alternative storage solution). > > Simon. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
