799578 - Update in-tree TBPL DB schema to match that in production

Status: RESOLVED FIXED

(Tree Management Graveyard :: TBPL, defect)

Description

[Ed Morley [:emorley]]

In bug 798689 an index was added to runs_logs.content

At the least, this needs adding to:
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/tip/schema.sql

...but I would also like to check whether there are any other not-checked-in changes in production.

Sheeri (or anyone else), could you possibly export the schema from production and paste in this bug at some point, for me then to update the in-tree copy.

Comment 1

[Ed Morley [:emorley]]

Before I embark on bug 803009, I think it would be good to get the in-tree schema in sync with what's being used in production.

Sheeri, I don't suppose you could export the schema from tbpl production so I can compare with:
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/tip/schema.sql

Cheers! :-)

Comment 2

[Sheeri Cabral [:sheeri]]

sure, I can do that, changing this to confidential just in case (I have no clue if the code is open or what)

Comment 3

[Sheeri Cabral [:sheeri]]

Attachment: tbpl production schema

Comment 4

[Ed Morley [:emorley]]

That's great, thank you :-)

(Unhiding, since we'll be syncing this with a public repo).

Comment 5

[Ed Morley [:emorley]]

Attachment: Diff of in-tree to production schema

I reformatted the schema dump a bit so as to remove the most obvious false positives in the diff. Many of the remaining changes are also presumably non-issues (eg KEY index being a synonym of INDEX; PRIMARY KEY being listed on a separate line etc).

Comment 6

[Ed Morley [:emorley]]

Attachment: Part 1: Fix quoting

Comment 7

[Ed Morley [:emorley]]

Attachment: Diff of in-tree to production schema v2

Updated diff of in-tree to production, with Part 1 patch applied and ignoring INDEX vs KEY differences.

Comment 8

[Ed Morley [:emorley]]

Attachment: Part 2: Add foreign key constraint names

...to match those in production.

Comment 9

[Ed Morley [:emorley]]

Attachment: Part 3: Reorder field attributes

...to match more typical style.

Comment 10

[Ed Morley [:emorley]]

Attachment: Diff of in-tree to production schema v3

Diff with a number of false positives removed & patches parts 1-3 applied.

The main differences between production and in-tree are:
* Production has duplicate indexes on builders.buildername (!?).
* Production has some additional DEFAULT values specified in the runs table.
* Production's runs_notes index is on (`run_id`,`timestamp`) not just `run_id`.
* Production has an index on runs_logs.content (which we already knew).

Comment 11

[Ed Morley [:emorley]]

Sheeri, sorry to bother you again - given the number of differences between the in-tree schema and production, I think it may be worth checking TBPL-dev too - so please may I have a dump of it as well?

Comment 12

[Ed Morley [:emorley]]

Attachment: Part 4: Be explicit about defaults

Comment 13

[Ed Morley [:emorley]]

Attachment: Part 5: Add indices that are already in production

Comment 14

[Ed Morley [:emorley]]

Attachment: Part 5: Update indices to match production

Comment 15

[Ed Morley [:emorley]]

With the attached patches, as far as I can tell, the in-tree schema is now identical to that in production (other than SQL style differences and s/KEY/INDEX/ etc). 

Once they've landed & we have the tbpl-dev dump, I'll file separate bugs for the prod/dev DB changes required to fix up the problems found in comment 10 (eg the duplicate indices).

Comment 16

[Ed Morley [:emorley]]

Attachment: in-tree schema with patches 1-5 applied

Comment 17

[Sheeri Cabral [:sheeri]]

Attachment: tbpl dev schema

Dev schema attached.

Comment 18

[Ed Morley [:emorley]]

(In reply to Sheeri Cabral [:sheeri] from comment #17)
> Dev schema attached.

Thank you :-)

Differences between prod and dev:

* prod builders table has "KEY `buildername` (`buildername`(255))", dev does not

* prod runs_notes table has:
"KEY `runs_notes_run_id_idx` (`run_id`,`timestamp`)"
...whereas dev:
"KEY `runs_notes_run_id_idx` (`run_id`)"

Comment 19

[Ed Morley [:emorley]]

https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/rev/3beaf22c67c4
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/rev/16292ae0647d
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/rev/a01985048f6d
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/rev/2c3c34028518
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/rev/cf6d5636562d

I'll file another bug for fixing the issues found in the prod schema (and getting dev in sync with prod).