822708 - deploy mozpool-1.2.0

Status: RESOLVED FIXED

(Infrastructure & Operations :: RelOps: General, task)

Description

[Dustin J. Mitchell [:dustin] (he/him)]

This is a bit tricky due to the need to upgrade the schema.  The below adds default values so that it still works with Mozpool-1.1.1 (currently installed), up until the last step -- which will happen after everything's upgraded.  I tested this on my local MySQL.

----

CREATE TABLE hardware_types (
  id integer unsigned not null primary key auto_increment,
  type varchar(32) not null,
  model varchar(32) not null,
  unique index typemodel_index (type, model)
) engine=innodb;

INSERT into hardware_types values (1, 'PandaBoard', 'ES');

ALTER table devices add column hardware_type_id integer unsigned not null default 1;
ALTER table devices add foreign key (hardware_type_id) references hardware_types (id) on delete restrict;
 
CREATE TABLE images (
  id integer unsigned not null primary key auto_increment,
  name varchar(32) not null,
  boot_config_keys text not null,
  can_reuse INTEGER not null, 
  unique index name_idx (name)
) engine=innodb;

INSERT into images values (1, 'b2g', '["b2gbase"]', 0);

CREATE TABLE image_pxe_configs (
  image_id integer unsigned not null,
  foreign key (image_id) references images(id) on delete restrict,
  hardware_type_id integer unsigned not null,
  foreign key (hardware_type_id) references hardware_types(id) on delete restrict,
  pxe_config_id integer unsigned,
  foreign key (pxe_config_id) references pxe_configs(id) on delete restrict,
  
  unique index imagehardware_index (image_id, hardware_type_id),
  primary key pk (image_id, hardware_type_id) 
) engine=innodb; 

ALTER table devices add column last_image_id integer unsigned;
ALTER table devices add foreign key (last_image_id) references images(id) on delete restrict;
ALTER table devices add column image_id integer unsigned not null default 1; 
ALTER table devices add foreign key (image_id) references images(id) on delete restrict;

-- **LATER**

alter table devices drop key last_pxe_config_id, drop foreign key devices_ibfk_2;
ALTER TABLE devices drop column last_pxe_config_id;

Comment 1

[Dustin J. Mitchell [:dustin] (he/him)]

Landed safely, taxiing to the gate now.

Comment 2

[Dustin J. Mitchell [:dustin] (he/him)]

We're parked at the gate.  The **LATER** section is applied, so this is done.

Comment 3

[Dustin J. Mitchell [:dustin] (he/him)]

I made two goofs in the above:

1. Failed to insert a row into image_pxe_configs linking the b2g image to the panda-b2g.2 pxe config.

2. Failed to add the image_id column to the requests table.