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

RESOLVED FIXED

Status

RESOLVED FIXED
6 years ago
4 years ago

People

(Reporter: emorley, Assigned: emorley)

Tracking

Dependency tree / graph

Details

Attachments

(8 attachments, 4 obsolete attachments)

(Assignee)

Description

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

Updated

6 years ago
Depends on: 803009
(Assignee)

Comment 1

6 years ago
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
Created attachment 675248 [details]
tbpl production schema
(Assignee)

Comment 4

6 years ago
That's great, thank you :-)

(Unhiding, since we'll be syncing this with a public repo).
Group: mozilla-corporation-confidential
(Assignee)

Comment 5

6 years ago
Created attachment 675269 [details] [diff] [review]
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).
(Assignee)

Updated

6 years ago
Attachment #675269 - Attachment is patch: true
(Assignee)

Comment 6

6 years ago
Created attachment 675281 [details] [diff] [review]
Part 1: Fix quoting
(Assignee)

Comment 7

6 years ago
Created attachment 675283 [details] [diff] [review]
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.
Attachment #675269 - Attachment is obsolete: true
(Assignee)

Comment 8

6 years ago
Created attachment 675306 [details] [diff] [review]
Part 2: Add foreign key constraint names

...to match those in production.
(Assignee)

Comment 9

6 years ago
Created attachment 675322 [details] [diff] [review]
Part 3: Reorder field attributes

...to match more typical style.
(Assignee)

Comment 10

6 years ago
Created attachment 675325 [details] [diff] [review]
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).
(Assignee)

Updated

6 years ago
Attachment #675283 - Attachment is obsolete: true
(Assignee)

Comment 11

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

Comment 12

6 years ago
Created attachment 675543 [details] [diff] [review]
Part 4: Be explicit about defaults
(Assignee)

Comment 13

6 years ago
Created attachment 675544 [details] [diff] [review]
Part 5: Add indices that are already in production
(Assignee)

Comment 14

6 years ago
Created attachment 675547 [details] [diff] [review]
Part 5: Update indices to match production
Attachment #675544 - Attachment is obsolete: true
(Assignee)

Updated

6 years ago
Attachment #675325 - Attachment is obsolete: true
(Assignee)

Comment 15

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

Comment 16

6 years ago
Created attachment 675576 [details]
in-tree schema with patches 1-5 applied
Created attachment 675614 [details]
tbpl dev schema

Dev schema attached.
Flags: needinfo?(scabral)
(Assignee)

Comment 18

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

Updated

6 years ago
Blocks: 806121
(Assignee)

Updated

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