Mohit Sindhwani wrote:
> Hi Everyone,
>
> I'm having a problem that I'm trying to find an elegant solution to. I
> have a database that stores real-time information - this information is
> replaced by new values every 5 minutes and has about 30,000 entries.
> Once a new database is made available, I should start using that one.
>
> This database is used as part of an HTTP server that responds to
> requests based on the data stored in the database. So, I'm running into
> a design issue trying to solve how to "switch" the database from the old
> one to the new one. With multiple incoming requests, I'm not sure how/
> when to update the database. Also, due to the large number of records
> (30,000) that are to be put in every 5 minutes, I don't think I should
> just keep adding to the database since it takes quite some time - it
> feels better to let a separate process create the database and alert my
> program that a new file is ready!
>
> Any suggestions on how I should approach this problem?
>
Since you are using Windows, maybe:
server_metadata.db
with a single table:
create table database_links (
db_name varchar,
current boolean,
user_count
);
Your load process will create a new database with a unique name
(suggestion: use a guid mangled to a valid filename). Upon completion
of loading, insert a single record in a transaction (pseudocode)
begin;
execute("select db_name from database_links where current = 'F'") do |name|
deleteFile(name);
execute "delete from database_links where user_count = 0 and current = 'F';"
execute "update database_links set current = 'F';"
execute "insert into database_links values (:db_name, 'T', 0);"
commit;
As each client process connects to the database, it retrieves the
current database name:
select db_name from database_links where current = 'T';
It then updates the count:
update database_links set user_count = user_count + 1 where db_name = ?
When the process is done, decrement the count:
update database_links set user_count = user_count - 1 where db_name = ?
You should get SQL_BUSY if the database is being changed, and your old
database will still exist until the last process is done using it and
the newer database has been written.
FWIW,
John
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
