On Tue, May 15, 2012 at 1:55 PM, Charles Samuels <char...@cariden.com>wrote:
> At some point, we get a checkpoint; at this instant, what is in otherdb and > what is in sqlite is what we want committed to sqlite, if either of them > fails, we can rollback both of them and both databases return to a > consistent > state of a previous checkpoint. The problem is that in the time between > checkpoint 1 and checkpoint 1 being committed to disk, more data is > arriving. You will find that in almost all situations where either of them failed, whatever method you're using to protect the integrity of your data will fail too, because it will depend on the same thing. Not only that, but that your method of assuring synchrony is likely to add lots of complication which will make the whole system fail more than a simple one-SQL-engine implementation would. For data integrity of the type you describe, there's nothing much you can do short of using duplicate servers talking to RAIDs with redundancy. On 16 May 2012, at 7:40pm, Charles Samuels <char...@cariden.com> wrote: > I'm trying that now, it involves temporary tables and views; but the > bookkeeping for it is looking to be increasingly difficult and it is also > going > to make my own code unmaintainable. You are inventing a versioning system for database rows. This has been discussed a few times on this list, but never with any useful outcome in the form of code. There's no reason it couldn't be done, but it can turn complicated fast. Especially if you are going to deal correctly with multi-thread, multi-process or multi-user access. Two standard approaches are savepoints and an endless undo mechanism. Each have their advantages and disadvantages. The fact that there is no well-known way to do this should give you an idea how difficult or useful it will end up being. To reduce the complication you have already noted, I recommend you try to divorce the versioning system from the code of your project. Try to write yourself a database library which would be useful for many projects, rather than have any one routine which includes both knowledge about your project and knowledge about how versioning works. Another way to do it is simply to use ROLLBACK points as defined in the SQL standard and implemented by SQLite. We already know that they work correctly and there is a lot of documentation on their use and drawbacks. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
