1149978 - Import product detail data in shipit

Status: RESOLVED FIXED

(Release Engineering :: Applications: Shipit, defect)

Description

[Sylvestre Ledru [:Sylvestre]]

Attachment: pd-to-ship-it.sql

The SQL file should be run against the ship-it database.

I don't think we should do that now but only when we moved p-d to ship-it.

Comment 1

[Sylvestre Ledru [:Sylvestre]]

Attachment: generateSQL.py

For the record (and to regenerate the data when we are ready), here is the Python script used to generate it.

It should be run from the svn.mozilla.org/libs/product-details/json directory.

I have to do the trick on the primary key because INSERT INTO ... ON DUPLICATE KEY UPDATE works on the primary key (I want to update existing dataset and insert when does not exist)

Comment 2

[Sylvestre Ledru [:Sylvestre]]

Attachment: generateSQL.py

Fix some issues with ESR

Comment 3

[[e-mail] (:bhearsum)]

So, should I just import the .sql file you attached?

Comment 4

[Sylvestre Ledru [:Sylvestre]]

Attachment: foo.sql

nope, this one is the up to date. It was attached to show what kind of sql file is generated.

Comment 5

[[e-mail] (:bhearsum)]

I had to add some missing semicolons to the latest file, and then I realized that we have to wait for bug 1083718 to add the shippedAt column before we can do this...

Comment 6

[Sylvestre Ledru [:Sylvestre]]

Attachment: generateSQL.py

updated version, now provides the 'name' field

Comment 7

[Sylvestre Ledru [:Sylvestre]]

Attachment: upgrade.sql

Comment 8

[[e-mail] (:bhearsum)]

The data is imported, mostly. I got rid of all of the ALTER and UPDATE lines, because they risk data loss. I had to update all of the INSERT lines to include of all the fields that are set to nullable=False. The new inserts look like this:
INSERT INTO firefox_release(name, complete, ready, dashboardCheck, l10nChangesets, mozillaRevision, branch, shippedAt, version, buildNumber, submitter) VALUES ('Firefox-35.0b3-build1', True, True, False, 'legacy', 'legacy', 'legacy', '2014-12-12', '35.0b3', 1, 'legacy-submitter') ON DUPLICATE KEY UPDATE shippedAt='2014-12-12';


The following versions were skipped because they were too long for the current "version" column:
3.6.7build1, 3.6.4build5, 3.6.3plugin1, 3.6.4build3, 3.6.4build1, 3.6.4build6, 3.6.4build7, 3.6.4build4

Comment 9

[[e-mail] (:bhearsum)]

Also had to run these to fix submittedAt:
update firefox_release set submittedAt=shippedAt where submittedAt is NULL;
update fennec_release set submittedAt=shippedAt where submittedAt is NULL;
update thunderbird_release set submittedAt=shippedAt where submittedAt is NULL;


This is all just dev so far, prod still to do.

Comment 10

[[e-mail] (:bhearsum)]

Attachment: upgrade script, fixed

This is the script I used for production.

In the future, we should do these as part of actual migration scripts, rather than random sql that we run. This was very painful :(.

Comment 11

[Sylvestre Ledru [:Sylvestre]]

Ben, I would like to come back on bug 1083718. As I don't think you want to run the script again, I can provide a script written by hand to update the data (from last month).
Would that work for you? (if yes, I would need the last dump of the db) Thanks.

Comment 12

[[e-mail] (:bhearsum)]

(In reply to Sylvestre Ledru [:sylvestre] from comment #11)
> Ben, I would like to come back on bug 1083718. As I don't think you want to
> run the script again, I can provide a script written by hand to update the
> data (from last month).
> Would that work for you? (if yes, I would need the last dump of the db)
> Thanks.

I think Rail is already working with you on this...

Comment 13

[Sylvestre Ledru [:Sylvestre]]

Here are the queries that Rail run 19/08/2015:
update firefox_release set shippedAt="2015-03-20" where name="Firefox-39.0b7-build1";
update firefox_release set shippedAt="2015-08-11" where name="Firefox-40.0-build5";
update firefox_release set shippedAt="2015-08-13" where name="Firefox-40.0.2-build1";
update firefox_release set shippedAt="2015-07-03" where name="Firefox-40.0b1-build2";
update firefox_release set shippedAt="2015-07-07" where name="Firefox-40.0b2-build1";
update firefox_release set shippedAt="2015-07-10" where name="Firefox-40.0b3-build1";
update firefox_release set shippedAt="2015-07-14" where name="Firefox-40.0b4-build1";
update firefox_release set shippedAt="2015-07-21" where name="Firefox-40.0b6-build1";
update firefox_release set shippedAt="2015-07-24" where name="Firefox-40.0b7-build1";
update firefox_release set shippedAt="2015-07-28" where name="Firefox-40.0b8-build1";
update firefox_release set shippedAt="2015-07-31" where name="Firefox-40.0b9-build1";
update firefox_release set shippedAt="2015-08-12" where name="Firefox-41.0b1-build1";
update firefox_release set shippedAt="2015-08-18" where name="Firefox-41.0b2-build1";
update firefox_release set shippedAt="2015-07-02" where name="Firefox-38.1.0esr-build1";
update firefox_release set shippedAt="2015-08-06" where name="Firefox-38.1.1esr-build1";
update firefox_release set shippedAt="2015-08-11" where name="Firefox-38.2.0esr-build2";


update fennec_release set shippedAt="2015-08-11" where name="Fennec-40.0-build1";
update fennec_release set shippedAt="2015-07-03" where name="Fennec-40.0b1-build2";
update fennec_release set shippedAt="2015-07-07" where name="Fennec-40.0b2-build1";
update fennec_release set shippedAt="2015-08-04" where name="Fennec-40.0b10-build1";
update fennec_release set shippedAt="2015-07-14" where name="Fennec-40.0b4-build1";
update fennec_release set shippedAt="2015-07-21" where name="Fennec-40.0b6-build1";
update fennec_release set shippedAt="2015-07-28" where name="Fennec-40.0b8-build1";
update thunderbird_release set shippedAt="2015-07-27" where name="Thunderbird-40.0b1-build3";

Comment 14

[Sylvestre Ledru [:Sylvestre]]

Rail, could you execute these queries? thanks
update firefox_release set shippedAt="2015-08-28" where name="Firefox-41.0b5-build1";
update firefox_release set shippedAt="2015-08-25" where name="Firefox-41.0b4-build1";
update firefox_release set shippedAt="2015-08-21" where name="Firefox-41.0b3-build1";
update firefox_release set shippedAt="2015-08-27" where name="Firefox-40.0.3-build1";
update firefox_release set shippedAt="2015-07-02" where name="Firefox-39.0-build6";
update firefox_release set shippedAt="2015-06-02" where name="Firefox-38.0.5-build4";
update firefox_release set shippedAt="2015-08-27" where name="Firefox-38.2.1esr-build2";

update thunderbird_release set shippedAt="2015-08-14" where name="Thunderbird-38.2.0-build1";

BTW, once https://github.com/mozilla/ship-it/pull/32 is merged, we should not need to do that again.
(bug 1198640)

Comment 15

[Rail Aliiev [:rail]]

Done

Comment 16

[Sylvestre Ledru [:Sylvestre]]

Jordan, could you manage that for me? 

Just run these SQL query on the ship-it db? I will have one more query once I have the latest dump. Thanks!

update firefox_release set shippedAt='2015-09-01' where name='Firefox-41.0b6-build1';
update firefox_release set shippedAt='2015-09-04' where name='Firefox-41.0b7-build1';
update firefox_release set shippedAt='2015-09-08' where name='Firefox-41.0b8-build1';
update firefox_release set shippedAt='2015-09-11' where name='Firefox-41.0b9-build1';
update firefox_release set shippedAt='2015-11-03' where name='Firefox-42.0-build1';
update firefox_release set shippedAt='2015-09-29' where name='Firefox-42.0b2-build1';
update firefox_release set shippedAt='2015-10-06' where name='Firefox-42.0b4-build1';
update firefox_release set shippedAt='2015-10-09' where name='Firefox-42.0b5-build1';
update firefox_release set shippedAt='2015-10-13' where name='Firefox-42.0b6-build1';
update firefox_release set shippedAt='2015-10-16' where name='Firefox-42.0b7-build1';
update firefox_release set shippedAt='2015-10-20' where name='Firefox-42.0b8-build1';
update firefox_release set shippedAt='2015-10-23' where name='Firefox-42.0b9-build1';
update firefox_release set shippedAt='2015-09-30' where name='Firefox-41.0.1-build2';
update firefox_release set shippedAt='2015-10-15' where name='Firefox-41.0.2-build2';
update firefox_release set shippedAt='2015-09-22' where name='Firefox-41.0-build3';

update fennec_release set shippedAt='2015-08-12' where name='Fennec-41.0b1-build1';
update fennec_release set shippedAt='2015-09-23' where name='Fennec-42.0b1-build1';
update fennec_release set shippedAt='2015-10-28' where name='Fennec-42.0b10-build1';
update fennec_release set shippedAt='2015-09-29' where name='Fennec-42.0b2-build1 ';
update fennec_release set shippedAt='2015-10-07' where name='Fennec-42.0b4-build1 ';
update fennec_release set shippedAt='2015-10-13' where name='Fennec-42.0b6-build1 ';
update fennec_release set shippedAt='2015-10-20' where name='Fennec-42.0b8-build1 ';
update fennec_release set shippedAt='2015-10-23' where name='Fennec-42.0b9-build1 ';
update fennec_release set shippedAt='2015-08-18' where name='Fennec-41.0b2-build1 ';
update fennec_release set shippedAt='2015-08-25' where name='Fennec-41.0b4-build1 ';
update fennec_release set shippedAt='2015-09-01' where name='Fennec-41.0b6-build1 ';
update fennec_release set shippedAt='2015-09-08' where name='Fennec-41.0b8-build1 ';
update fennec_release set shippedAt='2015-10-15' where name='Fennec-41.0.2-build2';

update thunderbird_release set shippedAt='2015-10-13' where name='Thunderbird-42.0b2-build1';
update thunderbird_release set shippedAt='2015-09-10' where name='Thunderbird-41.0b1-build2';

Comment 17

[Jordan Lund (:jlund)]

> update fennec_release set shippedAt='2015-09-01' where
> name='Fennec-41.0b6-build1 ';
> update fennec_release set shippedAt='2015-09-08' where
> name='Fennec-41.0b8-build1 ';

are these trailing spaces a typo or are they entered into the db as that by earlier mistake? I guess I can query and find out myself ;)

also, I'm guessing  there is a separate db of ship-it dev instance and I don't need to worry about running this there and keeping them in sync?

Comment 18

[Nick Thomas [:nthomas] (UTC+12)]

Turns out Jordan doesn't have access to the generic cluster so I've run the sql commands instead. Just on production, since the releases aren't present on dev or stage, and with the trailing spaces on the Fennec release names removed.

Comment 19

[Sylvestre Ledru [:Sylvestre]]

rail, could run these commands on the ship-it db (yes, I have a fantastic life):

update firefox_release set shippedAt="2015-11-04" where name="Firefox-43.0b1-build1";
update firefox_release set shippedAt="2015-11-10" where name="Firefox-43.0b2-build1";
update firefox_release set shippedAt="2015-11-13" where name="Firefox-43.0b3-build1";
update firefox_release set shippedAt="2015-11-17" where name="Firefox-43.0b4-build1";
update firefox_release set shippedAt="2015-11-20" where name="Firefox-43.0b5-build1";
update firefox_release set shippedAt="2015-11-24" where name="Firefox-43.0b6-build1";
update firefox_release set shippedAt="2015-06-19" where name="Firefox-39.0b7-build1";
update firefox_release set shippedAt="2015-12-18" where name="Firefox-44.0b1-build1";
update firefox_release set shippedAt="2015-12-29" where name="Firefox-44.0b4-build1";
update firefox_release set shippedAt="2016-01-08" where name="Firefox-44.0b7-build1";
update firefox_release set shippedAt="2016-01-28" where name="Firefox-45.0b1-build1";
update firefox_release set shippedAt="2016-02-05" where name="Firefox-45.0b3-build1";
update firefox_release set shippedAt="2016-02-26" where name="Firefox-45.0b10-build1";
update firefox_release set shippedAt="2016-02-05" where name="Firefox-45.0b3-build1";
update firefox_release set shippedAt="2016-02-12" where name="Firefox-45.0b5-build1";
update firefox_release set shippedAt="2016-02-16" where name="Firefox-45.0b6-build1";
update firefox_release set shippedAt="2016-02-22" where name="Firefox-45.0b8-build1";
update firefox_release set shippedAt="2016-02-24" where name="Firefox-45.0b9-build1";
update firefox_release set shippedAt="2016-04-15" where name="Firefox-46.0b11-build1";
update firefox_release set shippedAt="2016-03-17" where name="Firefox-46.0b2-build3";
update firefox_release set shippedAt="2016-03-23" where name="Firefox-46.0b4-build1";
update firefox_release set shippedAt="2016-03-25" where name="Firefox-46.0b5-build1";
update firefox_release set shippedAt="2016-04-04" where name="Firefox-46.0b7-build2";

update firefox_release set shippedAt="2016-04-08" where name="Firefox-46.0b9-build1";

update firefox_release set shippedAt="2016-01-26" where name="Firefox-44.0-build3";

update firefox_release set shippedAt="2015-01-26" where name="Firefox-31.8.0esr-build3";
update firefox_release set shippedAt="2015-09-22" where name="Firefox-38.3.0esr-build1";
update firefox_release set shippedAt="2015-10-03" where name="Firefox-38.4.0esr-build1";
update firefox_release set shippedAt="2015-12-15" where name="Firefox-38.5.0esr-build1";
update firefox_release set shippedAt="2015-12-22" where name="Firefox-38.5.2esr-build1";

update firefox_release set shippedAt="2016-03-08" where name="Firefox-38.7.0esr-build1";
update firefox_release set shippedAt="2016-03-17" where name="Firefox-38.7.1esr-build1";
update firefox_release set shippedAt="2015-08-06" where name="Firefox-39.0.3-build1";

update firefox_release set shippedAt="2015-12-18" where name="Firefox-43.0.1-build1";
update firefox_release set shippedAt="2015-12-22" where name="Firefox-43.0.2-build1";
update firefox_release set shippedAt="2015-12-28" where name="Firefox-43.0.3-build1";

update firefox_release set shippedAt="2016-02-08" where name="Firefox-44.0.1-build1";
update firefox_release set shippedAt="2016-04-11" where name="Firefox-45.0.2-build1";


update fennec_release set shippedAt="2015-06-19" where name="Fennec-39.0b7-build1";
update fennec_release set shippedAt="2015-09-16" where name="Fennec-41.0b10-build1";

update fennec_release set shippedAt="2015-12-10" where name="Fennec-43.0b10-build1";
update fennec_release set shippedAt="2015-11-10" where name="Fennec-43.0b2-build1";
update fennec_release set shippedAt="2015-11-17" where name="Fennec-43.0b4-build1";
update fennec_release set shippedAt="2015-11-24" where name="Fennec-43.0b6-build1";
update fennec_release set shippedAt="2015-11-29" where name="Fennec-43.0b7-build1";
update fennec_release set shippedAt="2015-12-02" where name="Fennec-43.0b8-build1";

update fennec_release set shippedAt="2015-12-22" where name="Fennec-44.0b2-build1";
update fennec_release set shippedAt="2015-12-29" where name="Fennec-44.0b4-build1";
update fennec_release set shippedAt="2016-01-05" where name="Fennec-44.0b6-build1";
update fennec_release set shippedAt="2016-01-08" where name="Fennec-44.0b7-build1";

update fennec_release set shippedAt="2016-01-28" where name="Fennec-45.0b1-build1";
update fennec_release set shippedAt="2016-03-02" where name="Fennec-45.0b11-build1";
update fennec_release set shippedAt="2016-02-05" where name="Fennec-45.0b3-build1";
update fennec_release set shippedAt="2016-02-16" where name="Fennec-45.0b6-build1";

update fennec_release set shippedAt="2016-02-24" where name="Fennec-45.0b9-build1";

update fennec_release set shippedAt="2016-03-23" where name="Fennec-46.0b4-build1";
update fennec_release set shippedAt="2016-04-04" where name="Fennec-46.0b7-build1";

update fennec_release set shippedAt="2016-04-19" where name="Fennec-46.0b12-build1";

update fennec_release set shippedAt="2015-07-02" where name="Fennec-39.0-build1";
update fennec_release set shippedAt="2015-09-22" where name="Fennec-41.0-build3";
update fennec_release set shippedAt="2015-11-03" where name="Fennec-42.0-build1";

update fennec_release set shippedAt="2016-01-26" where name="Fennec-44.0-build2";
update fennec_release set shippedAt="2016-03-08" where name="Fennec-45.0-build1";

update fennec_release set shippedAt="2015-08-27" where name="Fennec-40.0.3-build1";
update fennec_release set shippedAt="2015-11-19" where name="Fennec-42.0.1-build1";
update fennec_release set shippedAt="2015-12-02" where name="Fennec-42.0.2-build1";

Comment 20

[Rail Aliiev [:rail]]

Done

Comment 21

[Sylvestre Ledru [:Sylvestre]]

The database is up to date