1226651 - Several reference data tables are missing composite unique keys after bug 1193836

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P1)

Description

[Ed Morley [:emorley]]

The template SQL files for the reference data tables were removed, since we're now using the Django model to create the tables instead:
https://github.com/mozilla/treeherder/commit/0f3788036290146d99df76dea73edbebaa2b1aab

However in the template files the following had composite unique keys, which aren't in the model:
* reference_data_signatures
* job_group
* user_exclusion_profile

Also the exclusion_profile_exclusions table doesn't exist in the model at all, when it was in the template SQL. Is this expected?

Comment 1

[Ed Morley [:emorley]]

Attachment: Schema before (using 987f803)

Comment 2

[Ed Morley [:emorley]]

Attachment: Schema after (using 27941c3)

Comment 3

[Ed Morley [:emorley]]

Attachment: Schema diff

I performed a `mysqldump -d -u root treeherder` on the revisions mentioned above, then on the new schema file tweaked it in cases where differences were just ordering or auto-index naming.

This is the diff -U8 of the resultant files.

Mauro, apart from the `unique_together`s, is there anything else that you can see that needs changing?

Comment 4

[Ed Morley [:emorley]]

I'd recommend viewing the diff locally in an editor with syntax highlighting; the split diff view on native bugzilla and also splinter is really less than ideal for this use case :-)

Comment 5

[Ed Morley [:emorley]]

Or view here:
https://gist.github.com/edmorley/78270fbad7d8cd48a3c2

Comment 6

[Mauro Doglio [:mdoglio]]

The exclusion_profile_exclusions table is generated from the ManyToManyField defined in [1]. For more info see [2] 

[1] https://github.com/mozilla/treeherder/blob/9c7f7ff08a76b49b9812035ffc0b04ab45e29180/treeherder/model/models.py#L409
[2] https://docs.djangoproject.com/en/1.8/ref/models/fields/#id1

Comment 7

[Mauro Doglio [:mdoglio]]

there are also a couple of indexes missing:
https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-L139
https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-L273

on the positive side, I noticed a few foreign keys constraints that were not enforced in the sql template:
https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-L617
https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-L275-L276
https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-L275-L276

hopefully this is the last issue caused by the hybrid db situation, thanks for finding this out.

Comment 8

[Ed Morley [:emorley]]

(In reply to Mauro Doglio [:mdoglio] from comment #7)
> there are also a couple of indexes missing:
> https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-
> L139
> https://gist.github.com/edmorley/78270fbad7d8cd48a3c2#file-bug1226651-diff-
> L273

Those two have equivalent unique keys, so were actually doubled up before.

Comment 9

[Ed Morley [:emorley]]

Attachment: Add missing unique_together model constraints

Comment 10

[Treeherder GitHub Bugbot]

Commit pushed to master at https://github.com/mozilla/treeherder

https://github.com/mozilla/treeherder/commit/998f4d374d7be843bf25ec449cc4d19752601836
Bug 1226651 - Add missing unique_together model constraints

These were present in the raw SQL used prior to bug 1193836, so should
already exist on stage/prod/heroku. As such, I've added these to the
initial migration to avoid errors during migrate/having to use `--fake`.