0

My program calculates a next state based on current state and an increment. Calculation is a breadth first search graph exploration spanning 100-1000 explored nodes.

So far I've had a separate datastructure for traversal (because if initial incompetence regarding SQLite), but now would like to use an in-memory SQLite database for the state (because keeping dual datastructures in sync has proven to be hell, and competence regarding SQLite has somewhat improved; in particular the WITH RECURSIVE statement was an eye-opener).

For my previous data structure I could easily make copies of the state and put in the explored graph nodes, but for SQLite this does not seem as easy or lightweight. Here are the options I imagine might work:

  1. serialize/deserialize
    • Works pretty much like my old data structure. However memory is already an issue, and the complete database is unlikely smaller than the separate structure.
    • Initial testing suggests ActiveTcl distro does not compile sqlite3 with serialization support enabled.
  2. record, rollback, replay
    • Recording the transactions from initial state (e.g. using trace execution) to node and then replay later, this is probably much more efficient than 1. How to actually record is percieved as risky (see a previous question of mine), so any advice is appreciated.

Before setting out for a week long adventure in futility, I'm posting this question in hopes of someone else having experience of branching an SQLite database.

Andreas
  • 5,086
  • 3
  • 16
  • 36
  • Do you need full branching or can you just use a series of versions? – Donal Fellows May 20 '19 at 12:45
  • @DonalFellows I only need the "frontier", not the branching itself. (assuming that is what you meant by "series of versions") – Andreas May 20 '19 at 13:08
  • I'd add an extra column that gives the “revision” of the data. Then I could simply make a delta by copying within the structure (usually relatively quick) but incrementing the revision ID; each table would then be bumped to the new version with a single UPDATE (and queries could be against a TEMP VIEW that hides this). But I don't know that this solves your problem. – Donal Fellows May 20 '19 at 13:46
  • @DonalFellows nice tip. looking closer at my data only two tables actually need revisions, so shouldn't be a big deal datawise. and by opening the database as temporary memory issues should less likely to happen (or at least more efficiently handled than OS swap files). and perhaps most importantly apparent in the model for future generations :-) – Andreas May 20 '19 at 15:23
  • Aren't you missing some tags, e.g., `sql` and `database-design`? This family of design problems have been covered a number of times in SO, see, e.g., https://stackoverflow.com/questions/9430743/selecting-most-recent-and-specific-version-in-each-group-of-records-for-multipl ... tony-ohagan answer sketches out a a widely adopted implementation. – mrcalvin May 21 '19 at 07:34
  • @mrcalvin I don't think so. The question is how to branch an SQLite database regardless of its definition. Both serialization/deserialization and record/rollback/replay satisfies this condition (rollback arguably interferring with transactions as it does not nest). Donal's comment made me investigate an inside solution (manipulating its tables) which turned out feasable. Should I run into any problem with that it would be an other question tagged as you propose. – Andreas May 21 '19 at 08:36

0 Answers0