need a method to create a new empty database schema

RESOLVED FIXED in 8

Status

Socorro
Database
RESOLVED FIXED
6 years ago
5 years ago

People

(Reporter: lars, Assigned: rhelmer)

Tracking

unspecified
x86_64
Linux
Dependency tree / graph

Firefox Tracking Flags

(Not tracked)

Details

(Whiteboard: [qa-])

(Reporter)

Description

6 years ago
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.
(Assignee)

Comment 1

6 years ago
(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
(Reporter)

Comment 2

5 years ago
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
(Assignee)

Updated

5 years ago
Blocks: 721018
(Assignee)

Comment 4

5 years ago
(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
(Assignee)

Comment 5

5 years ago
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

Comment 7

5 years ago
Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/f94160629d27440a7726e15db25385c68b75a713
bug 720738 - no point in using versioned directory for schema

https://github.com/mozilla/socorro/commit/11f148b4b377a562260cb5ad6df2849aea1f751e
bug 720738 - replace old DB load script r=peterbe

https://github.com/mozilla/socorro/commit/af7d91301492c5b2a23607119573e61335b79791
Merge pull request #516 from rhelmer/bug720738-loadDB-script

fixes bug 720738 - new loadDB script
(Assignee)

Updated

5 years ago
Depends on: 748608
(Assignee)

Comment 8

5 years ago
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
(Assignee)

Updated

5 years ago
Target Milestone: --- → 8
(Assignee)

Updated

5 years ago
Whiteboard: [qa-]

Comment 9

5 years ago
Commit pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/bd6bd71acc6e5a12ba9852e657f55834a92213d6
bug 720738 - unit test fixes

Comment 10

5 years ago
Commits pushed to stage at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/f94160629d27440a7726e15db25385c68b75a713
bug 720738 - no point in using versioned directory for schema

https://github.com/mozilla/socorro/commit/11f148b4b377a562260cb5ad6df2849aea1f751e
bug 720738 - replace old DB load script r=peterbe

https://github.com/mozilla/socorro/commit/af7d91301492c5b2a23607119573e61335b79791
Merge pull request #516 from rhelmer/bug720738-loadDB-script

https://github.com/mozilla/socorro/commit/bd6bd71acc6e5a12ba9852e657f55834a92213d6
bug 720738 - unit test fixes

Comment 11

5 years ago
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
(Assignee)

Updated

5 years ago
Status: ASSIGNED → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED

Comment 12

5 years ago
Commits pushed to stage 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
You need to log in before you can comment on or make changes to this bug.