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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
