SQLite view across multiple databases. Is this okay? Is there a better way?

Question

Using SQlite I have a large database split into years: DB_2006_thru_2007.sq3 DB_2008_thru_2009.sq3 DB_current.sq3 They all have a single table call hist_tbl with two columns (key, data).

The requirements are: 1. to be able to access all the data at once. 2. inserts only go to the current version. 3. the data will continue to be split as time goes on. 4. access is through a single program that has exclusive access. 5. the program can accept some setup SQL but needs to run the same when accessing one database or multiple databases.

To view them cohesively I do the following (really in a program but command line shown here): sqlite3 DB_current.sq3

attach database 'DB_2006_thru_2007.sq3' as hist1; attach database 'DB_2008_thru_2009.sq3' as hist2; create temp view hist_tbl as select * from hist1.hist_tbl union select * from hist2.hist_tbl union select * from main.hist_tbl;

There is now a temp.hist_tbl (view) and a main.hist_tbl (table). When I select without qualifying the table I get the data thru the view. This is desirable since I can use my canned sql queries against either the joined view or the individual databases depending on how I setup. Additionally I can always insert into main.hist_tbl.

Question 1: What are the downsides? Question 2: Is there a better way?

Thanks in advance.

Answer 1

Question 1: What are the downsides?

You have to update the view EVERY. FISCAL. year.

Question 2: Is there a better way?

Add a date column so you can search for things within a given timespan, like a fiscal year.