Closed Bug 720738 Opened 12 years ago Closed 12 years ago

need a method to create a new empty database schema

Categories

(Socorro :: Database, task)

x86_64
Linux
task
Not set
normal

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: lars, Assigned: rhelmer)

References

Details

(Whiteboard: [qa-])

Right now, if a user were to try to adopt Socorro, there is no way to create an empty Postgres schema for version 2.4.  The old system using .../socorro/database/schema.py could create a new database schema.  We need to be able to do this.  The vagrant virtual box needs this capability if it is to be seriously used as a development platform.
(In reply to K Lars Lohn [:lars] [:klohn] from comment #0)
> Right now, if a user were to try to adopt Socorro, there is no way to create
> an empty Postgres schema for version 2.4.  The old system using
> .../socorro/database/schema.py could create a new database schema.  We need
> to be able to do this.  The vagrant virtual box needs this capability if it
> is to be seriously used as a development platform.

There is a way to do this, and vagrant does it:
https://github.com/rhelmer/socorro-vagrant/blob/master/manifests/classes/socorro-db.pp#L42

However vagrant then goes the extra step and imports some dummy data:
https://github.com/rhelmer/socorro-vagrant/blob/master/manifests/classes/socorro-db.pp#L78

The "dummy data" is just enough to get the UI to load, and is equivalent to following the instructions in this document: http://socorro.readthedocs.org/en/latest/populatepostgres.html
we also need to be able to create and destroy complete Socorro database schema within the unit/integration tests.  As we move to configman, old tests that use schema.py for this will be retired.

rhelmer: could you please update the links in comment #1 as they have rotted.
Notes-to-self:

* rhelmer to write a script that creates the schema + things like partitions

* running integration tests will REQUIRE that tables already exists

* tearDown to truncate all tables it touches

* setUp must edit the UUID fixture to reflect current UUIDs that refer to a current date

* tests would need to always make sure the insert table is the current date.
Assignee: nobody → rhelmer
Blocks: 721018
(In reply to K Lars Lohn [:lars] [:klohn] from comment #2)
> we also need to be able to create and destroy complete Socorro database
> schema within the unit/integration tests.  As we move to configman, old
> tests that use schema.py for this will be retired.
> 
> rhelmer: could you please update the links in comment #1 as they have rotted.

The script peterbe mentions in comment 3 should replace almost every exec{} block in https://github.com/mozilla/socorro/blob/master/puppet/manifests/classes/socorro-db.pp#files (currently duplicated in the second set of commands in http://socorro.readthedocs.org/en/latest/installation.html#postgresql-config )

It should set up a DB for at least these purposes:

* unit tests
* new Socorro installs
* dev instances (vagrant will use it)

The closest we have now is https://github.com/mozilla/socorro/blob/master/socorro/unittest/database/createSchema.py (this imports TestDB which imports schema.py, and is intended for unit tests) but the problem is that schema.py is way out of date, since it's not the way the database is actually maintained and there's no simple way to rebuild it based on the current DB schema.

For starters, I intend to simply use the schema dump that we create as part of the release process, e.g. https://github.com/mozilla/socorro/blob/master/sql/schema/5/breakpad_schema.sql since this is trivial to keep up to date.

The actionable items I see in this bug are:

1) create scripts/loadDB.py (use configman to take usual socorro config options, and also provide a --dropdb so it's safe to run by default)
2) remove all traces of schema.py
3) convert unit tests to not create their own tables or call schema.py - just truncate on tearDown
4) puppet manifests call this script
5) docs refer to this script
Status: NEW → ASSIGNED
Also let's stop putting the schema and roles SQL dumps in versioned directories like https://github.com/mozilla/socorro/tree/master/sql/schema

This makes sense for upgrade scripts but not the schema, and makes it hard to discover with scripts.
For the record, Rob's code is up for contributions here: 
https://github.com/mozilla/socorro/pull/516
Status: ASSIGNED → NEW
Depends on: 748608
Code is landed, works locally (and peterbe contributed a unit test for setupdb itself)

However Jenkins is currently busted because it can't create the databases it needs, see bug 748608
Status: NEW → ASSIGNED
Target Milestone: --- → 8
Whiteboard: [qa-]
Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/1de85266b5c236c67a83b291dfddc37a02faa00c
bug 720738 - fix fallout from loadDB->setupdb_app rename

https://github.com/mozilla/socorro/commit/aff0500007b382cfbea0414b83ee2d39740eb2e1
Merge pull request #537 from rhelmer/master

bug 720738 - fix fallout from loadDB->setupdb_app rename
Status: ASSIGNED → RESOLVED
Closed: 12 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.