Hi, i'm not sure what exact vocabulary I should use for this... I'm looking for a way or technique, that allows rotating the database just like some tools will rotate my /var/log/messages on Linux. So, to rotate a database, I'm guessing the current DB needs to be renamed and kept nearby, new DB will contain additions and modification to the previous DB. But this would need to take care of many many older DBs like this.
This would be complicated if the rotation/increments need to take care of all scenarios... but my scenario is very similar to the Linux system log. The calls that modify the DB are just inserts and rare but huge selects, zero deletes and zero updates. With rotation I can simply copy all "previous" files that will never change on different computers, and only "today" will change constantly (but it's currently less than 1MB per day). Writing to this "rotated DB" is fine, reading from it is the catch. I've looked into ATTACH, but the doc says "the number of attached databases cannot be increased above 30 on a machines with a 32-bit integer". I would have to merge the "old" database at some point, but this is less optimal than writting a sync protocol i think. I've looked into using virtual tables on the reader end. Inserter would just open a new db file each day, with no regards to the past. But the reader could use vtables to go around the ATTACH limit. I think this approach is overly complicated though... Maybe I should program my own means of "attaching" the results of multiple queries on multiple databases. I guess I could use a :MEMORY: database to accumulate the result, but then perhaps I should just use a temp table and "copy" the result of a series of attach-select-detach operations on all the databases. I'm looking for suggestions. I don't have much experience with sqlite so I believe it's likely I misunderstood a few things and will probably run into some other limits. Let me know if you think some of the above is good or just can't work at all, and let me know if you have any other ideas. Thanks! Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
