Simon Slavin wrote: > On 8 Jun 2009, at 8:07pm, Mohit Sindhwani wrote: > > >> 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! >> > > You can do all this in one database without problems. What you need > is to have a 'dataset' concept: every row in the data table belongs to > a particular dataset. All you need is an integer. And you make > another table in the same database that has one entry that just stores > which dataset is the current one. > > When a client needs to consult the table with the data in it, it first > reads the current dataset number (an integer) from the other table. > Then it reads just the rows from the data table that have the same > dataset number. That's all. > > The application that updates the database keeps at least two datasets > in the data table: the current one, and one previous one in case > applications haven't finished reading it yet. When new data comes in, > it adds a new dataset, updates the current dataset table, then deletes > an old dataset. > > SQLite is pretty good at recovering space from deleted records. You > might want to read up on VACUUM, but you shouldn't even need that. >
I like your idea better than mine. I would suggest some means of ensuring that some process isn't attempting to use a particular dataset, although five minutes may be adequate now, some future process may be introduced which takes longer. It may be a "bad thing" for the data to suddenly vanish while it was working with it. That was the underlying motivation for reference counting; although reference counting may not be the only way of accomplishing this goal. John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
