I am working on a short term solution to a bigger issue. The long term solution is switch databases, that is next on the list, after I patch the current DB.
My background: I am the sole developer of this product that uses lots of technologies, one of them is Apache Modules. It has been a long while, likes a year+ since I have had to do anything really hard core, so I am not 100% up to speed with terms and exact techniques. My ultimate goal in this post is to get some definition/direction as to know were to start on this short term solution. I am currently using the DB, SQLite. It is a great and outstanding DB, except... It is File Locking database, when one thread/process writes to it, the whole DB is locked. This is all fine and dandy, except there is one query (not an update/insert/delete) that is pretty intensive and runs a LOT, could be as much as 10~20 times a second. The great part is... It is somewhat static, so I would like to create a system to pool the result in memory and refresh it when it needs to be changed. Here is the pseudo code: Process 1 (not Apache) that creates the static file: 1. Create a Lock file: query.results.lock 2. Dump results from query to a text file: query.results 3. Remove Lock file Right now a request comes in via Axis2/C module (a web service). The WS is what needs access to this new piece. I have already done a lot of hacking on the Axis2/C piece so within the WS code, I have access to the core of the Apache request, server, etc. So in the WS, I want to make a call into this "connection pooling" code to do the following: 1. Check to see if query.results is in memory, if not load it 2. Check to see there is a lock file, if so use the copy in memory for this 'connection' 3. Check to see if the last modified date/time is newer then in memory, if so, load it, else use what is memory Basically what I am looking for is the same basic connection pooling that is implemented in the mod_dbd. Is there an easy way to do this, or will I simple need to get into the internals of the mod_dbd to figure out what it is doing and do it myself. Sam