637820 - Socorro - database/schema code refactoring

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[K Lars Lohn [:lars] [:klohn]]

the database schema maintenance objects defined in .../socorro/database/schema.py has never lived up to its potential.  It was designed to provide python objects to manipulate both the tables and the data within them.  Now that the schema has gotten more complicated, this method is falling apart adding no value over just flat .sql files.

We need to come up with a better solution that enables the following:

* schema versioning
* DBA friendly files
* consolidation of sql into a small number of places
* Python objects for sql execution
** there does not have to be a one to one relationship between objects/classes and tables
* automatic future partition generation
* do we want to go the SQLAlchemy direction?

feel free to add more requirements to this bug.

Comment 1

[Laura Thomson :laura]

I'd say:
- the schema SQL should be in SQL files, as discussed, but can be run from python
- each bug that changes SQL should have an associated migration up and down from previous version

I'd also like:
- a way to automatically generate a schema diagram.  I don't know what tools are good for this in PG.

Comment 2

[K Lars Lohn [:lars] [:klohn]]

long ago resolved