1185030 - Remove ``contenttype`` field from the datesource table/model

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P2)

Description

[Cameron Dawson [:camd]]

After we remove the objectstore, we should remove the contenttype field from the datasource table and model and references to it in the repo.

It's current set to ``not null`` so I can't stop using it in my current PR for removing the objectstore.

Comment 1

[Ed Morley [:emorley]]

I'm happy to do this after bug 1140349 lands; we'll need to:
(a) set a default for the column on stage/prod/heroku
(b) delete the old objectstore rows on stage/prod/heroku
(c) land the code changes to stop using the column and not reference it during INSERTs
(d) drop the column on stage/prod/heroku

Comment 2

[Ed Morley [:emorley]]

I've double-checked whether all jobs rows have the oauth keys copied over from the objectstore rows, and stage/prod does, but Heroku does not.

(In reply to Ed Morley [:emorley] from comment #1)
> (a) set a default for the column on stage/prod/heroku

ALTER TABLE treeherder.datasource CHANGE contenttype contenttype varchar(25) COLLATE utf8_bin NOT NULL DEFAULT 'jobs';

> (b) delete the old objectstore rows on stage/prod/heroku

DELETE FROM treeherder.datasource WHERE contenttype != 'jobs';

> (c) land the code changes to stop using the column and not reference it during INSERTs

I have this locally; will open a PR once the rm objectstore one lands.

> (d) drop the column on stage/prod/heroku

ALTER TABLE treeherder.datasource DROP contenttype;

(e) Check/set separate unique keys on `name` and `project`

ALTER TABLE treeherder.datasource CHANGE project project varchar(50) COLLATE utf8_bin NOT NULL UNIQUE;
ALTER TABLE treeherder.datasource CHANGE name name varchar(128) COLLATE utf8_bin NOT NULL UNIQUE;

Comment 3

[Ed Morley [:emorley]]

Attachment: Remove the contenttype field from the datasource table

Comment 4

[Cameron Dawson [:camd]]

I copied the OAuth keys over on Heroku yesterday.  We should be good there now.

Comment 5

[Cameron Dawson [:camd]]

Comment on attachment 8636855 [details] [review]
Remove the contenttype field from the datasource table

This all looks great.  Thanks!

Comment 6

[Ed Morley [:emorley]]

Heroku...

(0) Have checked all jobs rows have the oauth credentials set

(a) ALTER TABLE treeherder.datasource CHANGE contenttype contenttype varchar(25) COLLATE utf8_bin NOT NULL DEFAULT 'jobs';

Confirmed:
> SELECT COLUMN_NAME, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE table_name = "datasource"

+ ---------------- + ------------------- +
| COLUMN_NAME      | COLUMN_DEFAULT      |
+ ---------------- + ------------------- +
| id               |                     |
| project          |                     |
| contenttype      | jobs                |
| name             |                     |
| oauth_consumer_key |                     |
| oauth_consumer_secret |                     |
+ ---------------- + ------------------- +
6 rows

(b) Delete !jobs rows:

Execute:
> SELECT contenttype, COUNT(*) FROM treeherder.datasource WHERE contenttype != 'jobs' GROUP BY contenttype

+ ---------------- + ------------- +
| contenttype      | COUNT(*)      |
+ ---------------- + ------------- +
| objectstore      | 44            |
+ ---------------- + ------------- +
1 rows

DELETE FROM treeherder.datasource WHERE contenttype != 'jobs';

> SELECT COUNT(*) FROM treeherder.datasource WHERE contenttype != 'jobs'

+ ------------- +
| COUNT(*)      |
+ ------------- +
| 0             |
+ ------------- +
1 rows

Comment 7

[Treeherder GitHub Bugbot]

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

https://github.com/mozilla/treeherder/commit/21ca58c3d2b0c6823529e1401802efced345bf8d
Bug 1185030 - Remove the contenttype field from the datasource table

Since it's redundant now that the objectstore has been removed.

Comment 8

[Ed Morley [:emorley]]

Stage:

(0) Done
(a) ALTER TABLE treeherder_stage.datasource CHANGE contenttype contenttype varchar(25) COLLATE utf8_bin NOT NULL DEFAULT 'jobs';
(b) SELECT contenttype, COUNT(*) FROM treeherder_stage.datasource WHERE contenttype != 'jobs' GROUP BY contenttype; -> OK
    DELETE FROM treeherder_stage.datasource WHERE contenttype != 'jobs';

Prod:

(0) Done
(a) ALTER TABLE treeherder.datasource CHANGE contenttype contenttype varchar(25) COLLATE utf8_bin NOT NULL DEFAULT 'jobs';
(b) SELECT contenttype, COUNT(*) FROM treeherder.datasource WHERE contenttype != 'jobs' GROUP BY contenttype; -> OK
    DELETE FROM treeherder.datasource WHERE contenttype != 'jobs';

The rest needs to wait until after the PR is deployed to stage/prod.

Comment 9

[Ed Morley [:emorley]]

So I had to reverse some of the changes on prod temporarily on Friday, since we did a non-master push to get the talos e10s fixes deployed sooner (see bug 1187395 comment 5):
https://emorley.pastebin.mozilla.org/8840584

Comment 10

[Ed Morley [:emorley]]

I've re-applied the prod changes:
DELETE FROM treeherder.datasource WHERE contenttype != 'jobs' AND id > 0;

Deployed master:
http://treeherderadm.private.scl3.mozilla.com/chief/treeherder.prod/logs/master.1438093039

And confirmed the credentials are unchanged (which we'd expect to be fine, given the schema changes now match the code being pushed; but still) :-)

I'll leave this bug open to do the remaining steps in comment 2.

Comment 11

[Ed Morley [:emorley]]

Run on Heroku:

ALTER TABLE treeherder.datasource DROP contenttype;

Since there was already a unique key on (`project`,`contenttype`) we're now left with a unique key on `project`, so this just leaves adding one to `name`:

ALTER TABLE treeherder.datasource CHANGE name name varchar(128) COLLATE utf8_bin NOT NULL UNIQUE;

Comment 12

[Ed Morley [:emorley]]

Stage:

ALTER TABLE treeherder_stage.datasource DROP contenttype;
ALTER TABLE treeherder_stage.datasource CHANGE name name varchar(128) COLLATE utf8_bin NOT NULL UNIQUE;

Prod:

ALTER TABLE treeherder.datasource DROP contenttype;
ALTER TABLE treeherder.datasource CHANGE name name varchar(128) COLLATE utf8_bin NOT NULL UNIQUE;