metomi/rose — pull request #1085 by benfitzpatrick (2014-01-10 15:09:36+00:00)
Title: #646: rosie db schema update and speedup

This changes the schema of the rosie suite database in order to improve performance.

The main bottleneck in performance was the `all-revs` join which was highly complex. The schema has been changed to place more emphasis on simplicity of join and less emphasis on avoiding duplication of data across revisions.

The properties for a given suite are stored explicitly for each revision rather than our previous approach of keeping track of diffs. The `modified` table has been removed and each idx-branch-revision is stored separately in the `main` and `optional` tables. The information in the `changeset` table, which already kept track of idx-branch-revision info, has been merged into the `main` table to avoid an unnecessary join.

The change makes the all-revs queries much simpler and faster (around 30x faster). Normal queries are also faster (2x faster) presumably because the `changeset` table has been removed and no longer needs to be joined.
The sqlite files are only around 2x larger (the duplicated data is being compressed correctly by sqlite).
The `db.py` database select code is also now a lot easier to understand.

@matthewrmshin, please test and review.