Closed Bug 799578 Opened 9 years ago Closed 9 years ago

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

Categories

(Tree Management Graveyard :: TBPL, defect)

defect
Not set
normal

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: emorley, Assigned: emorley)

References

Details

Attachments

(8 files, 4 obsolete files)

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.
Depends on: 803009
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! :-)
Blocks: 803009
No longer depends on: 803009
sure, I can do that, changing this to confidential just in case (I have no clue if the code is open or what)
Group: mozilla-corporation-confidential
That's great, thank you :-)

(Unhiding, since we'll be syncing this with a public repo).
Group: mozilla-corporation-confidential
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).
Attachment #675269 - Attachment is patch: true
Updated diff of in-tree to production, with Part 1 patch applied and ignoring INDEX vs KEY differences.
Attachment #675269 - Attachment is obsolete: true
...to match those in production.
...to match more typical style.
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).
Attachment #675283 - Attachment is obsolete: true
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?
Flags: needinfo?(scabral)
Attachment #675544 - Attachment is obsolete: true
Attachment #675325 - Attachment is obsolete: true
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).
Attached file tbpl dev schema
Dev schema attached.
Flags: needinfo?(scabral)
(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`)"
Blocks: 806121
Depends on: 807786
Product: Webtools → Tree Management
Product: Tree Management → Tree Management Graveyard
You need to log in before you can comment on or make changes to this bug.