mozilla/socorro — pull request #2149 by selenamarie (2014-06-26 23:08:29+00:00)
Title: DO NOT MERGE Bug1003527 split schema

**This is for discussion/review.**

Goal: Create a schema divided between `base` tables like:
* raw_crashes
* processed_crashes
* reports

That we can run a subset of stored procedures on for normalizing data in `reports_clean` and all the other "fact" tables. Most other "raw data" tables would continue to reside on our reporting database because they are so small in comparison.

I've still got a bit of work to do yet, but this is how things might look in the future. There are three major sections of schema:

* base
* normalized
* public (reporting tables, basically)

I think what we want to do is at something like: `SET search_path = base, public, normalized, public` to our connection context objects to make finding the tables easy for SELECTs, rather than hard-coding all the schema paths into our queries. Need to do some experimenting to see exactly where and when the best place to do this would be.

There's a lot of hacking at the `models.py` that went into this -- but they are largely "template" changes... moving around things and adding the `schema` attribute to `__table_args__`.

This is going to unfortunately be a huge patch. We can't really change the schema design without touching a lot of code.

A couple immediate pieces of work left:
* Tackle the "base" tables the same way that I've handled the "normalized" ones, with separately named classes and duplicate classes in the 'public' schema for when we are operating with just a single database. This simplifies the testing and any additional refactoring needed for a default install of Socorro that only has one database.
* Following that same train of thought, we might have different stored procedures invoked for the single-database schema vs multi-database schema. Building the logic into the stored procs seems ... like a recipe for excessive complexity.
* Needs tests to verify that two databases are actually created by setupdb_app.py and that the new schemas exist etc.