Merge system_admin_map into system_group (bug 797584)
-----------------------------------------------------

Run the following SQL:

    ALTER TABLE system_group
    ADD admin BOOL NOT NULL DEFAULT FALSE;

    UPDATE system_group sg
    INNER JOIN system_admin_map sa
        ON sg.system_id = sa.system_id AND sg.group_id = sa.group_id
    SET sg.admin = TRUE;

    DROP TABLE system_admin_map;

To roll back, first downgrade the beaker-server package and run beaker-init to
recreate the system_admin_map table. Then run the following SQL to populate it:

    INSERT INTO system_admin_map (system_id, group_id)
    SELECT system_id, group_id
    FROM system_group
    WHERE admin = TRUE;

    ALTER TABLE system_group
    DROP admin;


Add indexes on device (bug 731615)
----------------------------------

Run the following SQL:

    ALTER TABLE device
        ADD INDEX ix_device_driver (driver),
        ADD INDEX ix_device_pciid (vendor_id, device_id);

To roll back:

    ALTER TABLE device
        DROP INDEX ix_device_driver,
        DROP INDEX ix_device_pciid;


Convert reference tables to enums
---------------------------------

Run the following SQL statements to convert to enums:

    -- job table
    ALTER TABLE job
        ADD COLUMN status ENUM (
            'New', 'Processed', 'Queued', 'Scheduled', 'Waiting', 'Running',
            'Completed', 'Cancelled', 'Aborted'
        ) NOT NULL DEFAULT 'New' AFTER status_id,
        ADD COLUMN result ENUM ('New', 'Pass', 'Warn', 'Fail', 'Panic')
            NOT NULL DEFAULT 'New' AFTER result_id;
    UPDATE job
        INNER JOIN task_status ON job.status_id = task_status.id
        SET job.status = task_status.status;
    UPDATE job
        INNER JOIN task_result ON job.result_id = task_result.id
        SET job.result = task_result.result;
    ALTER TABLE job
        DROP FOREIGN KEY job_ibfk_3, -- foreign key for result_id
        DROP FOREIGN KEY job_ibfk_4, -- foreign key for status_id
        DROP COLUMN status_id,
        DROP COLUMN result_id;

    -- recipe_set table
    ALTER TABLE recipe_set
        ADD COLUMN status ENUM (
            'New', 'Processed', 'Queued', 'Scheduled', 'Waiting', 'Running',
            'Completed', 'Cancelled', 'Aborted'
        ) NOT NULL DEFAULT 'New' AFTER status_id,
        ADD COLUMN result ENUM ('New', 'Pass', 'Warn', 'Fail', 'Panic')
            NOT NULL DEFAULT 'New' AFTER result_id,
        ADD COLUMN priority ENUM ('Low', 'Medium', 'Normal', 'High', 'Urgent')
            NOT NULL DEFAULT 'Normal' AFTER priority_id;
    UPDATE recipe_set
        INNER JOIN task_status ON recipe_set.status_id = task_status.id
        SET recipe_set.status = task_status.status;
    UPDATE recipe_set
        INNER JOIN task_result ON recipe_set.result_id = task_result.id
        SET recipe_set.result = task_result.result;
    UPDATE recipe_set
        INNER JOIN task_priority ON recipe_set.priority_id = task_priority.id
        SET recipe_set.priority = task_priority.priority;
    ALTER TABLE recipe_set
        DROP FOREIGN KEY recipe_set_ibfk_1, -- foreign key for priority_id
        DROP FOREIGN KEY recipe_set_ibfk_3, -- foreign key for result_id
        DROP FOREIGN KEY recipe_set_ibfk_4, -- foreign key for status_id
        DROP COLUMN status_id,
        DROP COLUMN result_id,
        DROP COLUMN priority_id;

    -- recipe table
    ALTER TABLE recipe
        ADD COLUMN status ENUM (
            'New', 'Processed', 'Queued', 'Scheduled', 'Waiting', 'Running',
            'Completed', 'Cancelled', 'Aborted'
        ) NOT NULL DEFAULT 'New' AFTER status_id,
        ADD COLUMN result ENUM ('New', 'Pass', 'Warn', 'Fail', 'Panic')
            NOT NULL DEFAULT 'New' AFTER result_id;
    UPDATE recipe
        INNER JOIN task_status ON recipe.status_id = task_status.id
        SET recipe.status = task_status.status;
    UPDATE recipe
        INNER JOIN task_result ON recipe.result_id = task_result.id
        SET recipe.result = task_result.result;
    ALTER TABLE recipe
        DROP FOREIGN KEY recipe_ibfk_2, -- foreign key for status_id
        DROP FOREIGN KEY recipe_ibfk_5, -- foreign key for result_id
        DROP COLUMN status_id,
        DROP COLUMN result_id;

    -- recipe_task table
    ALTER TABLE recipe_task
        ADD COLUMN status ENUM (
            'New', 'Processed', 'Queued', 'Scheduled', 'Waiting', 'Running',
            'Completed', 'Cancelled', 'Aborted'
        ) NOT NULL DEFAULT 'New' AFTER status_id,
        ADD COLUMN result ENUM ('New', 'Pass', 'Warn', 'Fail', 'Panic')
            NOT NULL DEFAULT 'New' AFTER result_id;
    UPDATE recipe_task
        INNER JOIN task_status ON recipe_task.status_id = task_status.id
        SET recipe_task.status = task_status.status;
    UPDATE recipe_task
        INNER JOIN task_result ON recipe_task.result_id = task_result.id
        SET recipe_task.result = task_result.result;
    ALTER TABLE recipe_task
        DROP FOREIGN KEY recipe_task_ibfk_2, -- foreign key for status_id
        DROP FOREIGN KEY recipe_task_ibfk_3, -- foreign key for result_id
        DROP COLUMN status_id,
        DROP COLUMN result_id;

    -- recipe_task_result table
    ALTER TABLE recipe_task_result
        ADD COLUMN result ENUM ('New', 'Pass', 'Warn', 'Fail', 'Panic')
            NOT NULL DEFAULT 'New' AFTER result_id;
    UPDATE recipe_task_result
        INNER JOIN task_result ON recipe_task_result.result_id = task_result.id
        SET recipe_task_result.result = task_result.result;
    ALTER TABLE recipe_task_result
        DROP FOREIGN KEY recipe_task_result_ibfk_2, -- foreign key for result_id
        DROP COLUMN result_id;

    -- system table
    ALTER TABLE system
        ADD COLUMN status ENUM ('Automated', 'Broken', 'Manual', 'Removed')
            NOT NULL AFTER status_id,
        ADD COLUMN release_action ENUM ('PowerOff', 'LeaveOn', 'ReProvision')
            AFTER release_action_id,
        ADD COLUMN type ENUM ('Machine', 'Virtual', 'Resource', 'Laptop', 'Prototype')
            NOT NULL AFTER type_id;
    UPDATE system
        INNER JOIN system_status ON system.status_id = system_status.id
        SET system.status = system_status.status;
    UPDATE system
        INNER JOIN release_action ON system.release_action_id = release_action.id
        SET system.release_action = release_action.action;
    UPDATE system
        INNER JOIN system_type ON system.type_id = system_type.id
        SET system.type = system_type.type;
    ALTER TABLE system
        DROP FOREIGN KEY system_ibfk_3, -- foreign key for type_id
        DROP FOREIGN KEY system_ibfk_4, -- foreign key for status_id
        DROP FOREIGN KEY system_ibfk_8, -- foreign key for release_action_id
        DROP COLUMN status_id,
        DROP COLUMN release_action_id,
        DROP COLUMN type_id;

    -- system_status_duration table
    ALTER TABLE system_status_duration
        ADD COLUMN status ENUM ('Automated', 'Broken', 'Manual', 'Removed')
            NOT NULL AFTER status_id;
    UPDATE system_status_duration
        INNER JOIN system_status ON system_status_duration.status_id = system_status.id
        SET system_status_duration.status = system_status.status;
    ALTER TABLE system_status_duration
        DROP FOREIGN KEY system_status_duration_ibfk_2, -- foreign key for status_id
        DROP COLUMN status_id;

    -- command_queue table
    ALTER TABLE command_queue
        ADD COLUMN status ENUM ('Queued', 'Running', 'Completed', 'Failed', 'Aborted')
            NOT NULL AFTER status_id;
    UPDATE command_queue
        INNER JOIN command_status ON command_queue.status_id = command_status.id
        SET command_queue.status = command_status.status;
    ALTER TABLE command_queue
        DROP FOREIGN KEY command_queue_ibfk_2, -- foreign key for status_id
        DROP COLUMN status_id;

    -- clean up old tables
    DROP TABLE task_status;
    DROP TABLE task_result;
    DROP TABLE task_priority;
    DROP TABLE system_status;
    DROP TABLE release_action;
    DROP TABLE system_type;
    DROP TABLE command_status;

To roll back, first downgrade the beaker-server package to the desired version
and run beaker-init to create and populate the reference tables. Then:

    -- job table
    ALTER TABLE job
        ADD COLUMN status_id INT NOT NULL DEFAULT 1 AFTER status,
        ADD CONSTRAINT job_status_id_fk
            FOREIGN KEY (status_id) REFERENCES task_status (id),
        ADD COLUMN result_id INT NOT NULL DEFAULT 1 AFTER result,
        ADD CONSTRAINT job_result_id_fk
            FOREIGN KEY (result_id) REFERENCES task_result (id);
    UPDATE job SET
        status_id = (SELECT id FROM task_status WHERE task_status.status = job.status),
        result_id = (SELECT id FROM task_result WHERE task_result.result = job.result);
    ALTER TABLE job
        DROP COLUMN status,
        DROP COLUMN result;

    -- recipe_set table
    ALTER TABLE recipe_set
        ADD COLUMN status_id INT NOT NULL DEFAULT 1 AFTER status,
        ADD CONSTRAINT recipe_set_status_id_fk
            FOREIGN KEY (status_id) REFERENCES task_status (id),
        ADD COLUMN result_id INT NOT NULL DEFAULT 1 AFTER result,
        ADD CONSTRAINT recipe_set_result_id_fk
            FOREIGN KEY (result_id) REFERENCES task_result (id),
        ADD COLUMN priority_id INT NULL AFTER priority;
    UPDATE recipe_set SET
        status_id = (SELECT id FROM task_status WHERE task_status.status = recipe_set.status),
        result_id = (SELECT id FROM task_result WHERE task_result.result = recipe_set.result),
        priority_id = (SELECT id FROM task_priority WHERE task_priority.priority = recipe_set.priority);
    ALTER TABLE recipe_set
        DROP COLUMN status,
        DROP COLUMN result,
        MODIFY priority_id INT NOT NULL,
        ADD CONSTRAINT recipe_set_priority_id_fk
            FOREIGN KEY (priority_id) REFERENCES task_priority (id),
        DROP COLUMN priority;

    -- recipe table
    ALTER TABLE recipe
        ADD COLUMN status_id INT NOT NULL DEFAULT 1 AFTER status,
        ADD CONSTRAINT recipe_status_id_fk
            FOREIGN KEY (status_id) REFERENCES task_status (id),
        ADD COLUMN result_id INT NOT NULL DEFAULT 1 AFTER result,
        ADD CONSTRAINT recipe_result_id_fk
            FOREIGN KEY (result_id) REFERENCES task_result (id);
    UPDATE recipe SET
        status_id = (SELECT id FROM task_status WHERE task_status.status = recipe.status),
        result_id = (SELECT id FROM task_result WHERE task_result.result = recipe.result);
    ALTER TABLE recipe
        DROP COLUMN status,
        DROP COLUMN result;

    -- recipe_task table
    ALTER TABLE recipe_task
        ADD COLUMN status_id INT NOT NULL DEFAULT 1 AFTER status,
        ADD CONSTRAINT recipe_task_status_id_fk
            FOREIGN KEY (status_id) REFERENCES task_status (id),
        ADD COLUMN result_id INT NOT NULL DEFAULT 1 AFTER result,
        ADD CONSTRAINT recipe_task_result_id_fk
            FOREIGN KEY (result_id) REFERENCES task_result (id);
    UPDATE recipe_task SET
        status_id = (SELECT id FROM task_status WHERE task_status.status = recipe_task.status),
        result_id = (SELECT id FROM task_result WHERE task_result.result = recipe_task.result);
    ALTER TABLE recipe_task
        DROP COLUMN status,
        DROP COLUMN result;

    -- recipe_task_result table
    ALTER TABLE recipe_task_result
        ADD COLUMN result_id INT NOT NULL DEFAULT 1 AFTER result,
        ADD CONSTRAINT recipe_task_result_result_id_fk
            FOREIGN KEY (result_id) REFERENCES task_result (id);
    UPDATE recipe_task_result SET result_id =
        (SELECT id FROM task_result WHERE task_result.result = recipe_task_result.result);
    ALTER TABLE recipe_task_result
        DROP COLUMN result;

    -- system table
    ALTER TABLE system
        ADD COLUMN status_id INT NULL AFTER status,
        ADD COLUMN release_action_id INT NULL AFTER release_action,
        ADD CONSTRAINT system_release_action_id_fk
            FOREIGN KEY (release_action_id) REFERENCES release_action (id),
        ADD COLUMN type_id INT NULL AFTER type;
    UPDATE system SET
        status_id = (SELECT id FROM system_status WHERE system_status.status = system.status),
        release_action_id = (SELECT id FROM release_action WHERE release_action.action = system.release_action),
        type_id = (SELECT id FROM system_type WHERE system_type.type = system.type);
    ALTER TABLE system
        MODIFY status_id INT NOT NULL,
        ADD CONSTRAINT system_status_id_fk
            FOREIGN KEY (status_id) REFERENCES system_status (id),
        DROP COLUMN status,
        DROP COLUMN release_action,
        MODIFY type_id INT NOT NULL,
        ADD CONSTRAINT system_type_id_fk
            FOREIGN KEY (type_id) REFERENCES system_type (id),
        DROP COLUMN type;

    -- system_status_duration table
    ALTER TABLE system_status_duration
        ADD COLUMN status_id INT NULL AFTER status;
    UPDATE system_status_duration SET status_id =
        (SELECT id FROM system_status WHERE system_status.status = system_status_duration.status);
    ALTER TABLE system_status_duration
        MODIFY status_id INT NOT NULL,
        ADD CONSTRAINT system_status_duration_status_id_fk
            FOREIGN KEY (status_id) REFERENCES system_status (id),
        DROP COLUMN status;

    -- command_queue table
    ALTER TABLE command_queue
        ADD COLUMN status_id INT NULL AFTER status;
    UPDATE command_queue SET status_id =
        (SELECT id FROM command_status WHERE command_status.status = command_queue.status);
    ALTER TABLE command_queue
        MODIFY status_id INT NOT NULL,
        ADD CONSTRAINT command_queue_status_id_fk
            FOREIGN KEY (status_id) REFERENCES command_status (id),
        DROP COLUMN status;


Fix existing logs that should have been deleted
-----------------------------------------------

    DELETE FROM log_recipe WHERE recipe_id IS NULL;
    DELETE FROM log_recipe_task WHERE recipe_task_id IS NULL;
    DELETE FROM log_recipe_task_result WHERE recipe_task_result_id IS NULL;

To roll back:

    There is no roll back


Add missing unique constraints (bug 816553)
-------------------------------------------

Run the following SQL:

    DELETE FROM task_type
        USING task_type
        INNER JOIN (
            SELECT MIN(id) AS id, type
            FROM task_type
            GROUP BY type) AS x
        ON x.type = task_type.type AND x.id != task_type.id;
    ALTER TABLE task_type
        MODIFY type VARCHAR(255) NOT NULL UNIQUE;

    DELETE FROM task_package
        USING task_package
        INNER JOIN (
            SELECT MIN(id) AS id, package
            FROM task_package
            GROUP BY package) AS x
        ON x.package = task_package.package AND x.id != task_package.id;
    ALTER TABLE task_package
        MODIFY package VARCHAR(255) NOT NULL COLLATE utf8_bin UNIQUE;

    UPDATE device
        INNER JOIN device_class ON device.device_class_id = device_class.id
        INNER JOIN (
            SELECT MIN(id) AS id, device_class
            FROM device_class
            GROUP BY device_class) AS x
        ON x.device_class = device_class.device_class AND x.id != device_class.id
        SET device.device_class_id = x.id;
    DELETE FROM device_class
        USING device_class
        INNER JOIN (
            SELECT MIN(id) AS id, device_class
            FROM device_class
            GROUP BY device_class) AS x
        ON x.device_class = device_class.device_class AND x.id != device_class.id;
    ALTER TABLE device_class
        MODIFY device_class VARCHAR(24) NOT NULL UNIQUE;

    DELETE FROM system_device_map
        USING system_device_map
        INNER JOIN device ON system_device_map.device_id = device.id
        INNER JOIN (
            SELECT system_id, MIN(device.id) AS id, vendor_id, device.device_id, subsys_device_id,
                subsys_vendor_id, bus, driver, description
            FROM system_device_map
            INNER JOIN device ON system_device_map.device_id = device.id
            GROUP BY system_id, vendor_id, device.device_id, subsys_device_id, subsys_vendor_id,
                bus, driver, description) AS x
        ON x.system_id = system_device_map.system_id
            AND x.vendor_id = device.vendor_id
            AND x.device_id = device.device_id
            AND x.subsys_device_id = device.subsys_device_id
            AND x.subsys_vendor_id = device.subsys_vendor_id
            AND x.bus = device.bus
            AND x.driver = device.driver
            AND x.description = device.description
            AND x.id != device.id;
    UPDATE system_device_map
        INNER JOIN device ON system_device_map.device_id = device.id
        INNER JOIN (
            SELECT MIN(id) AS id, vendor_id, device_id, subsys_device_id,
                subsys_vendor_id, bus, driver, description
            FROM device
            GROUP BY vendor_id, device_id, subsys_device_id, subsys_vendor_id,
                bus, driver, description) AS x
        ON x.vendor_id = device.vendor_id
            AND x.device_id = device.device_id
            AND x.subsys_device_id = device.subsys_device_id
            AND x.subsys_vendor_id = device.subsys_vendor_id
            AND x.bus = device.bus
            AND x.driver = device.driver
            AND x.description = device.description
            AND x.id != device.id
        SET system_device_map.device_id = x.id;
    DELETE FROM device
        USING device
        INNER JOIN (
            SELECT MIN(id) AS id, vendor_id, device_id, subsys_device_id,
                subsys_vendor_id, bus, driver, description
            FROM device
            GROUP BY vendor_id, device_id, subsys_device_id, subsys_vendor_id,
                bus, driver, description) AS x
        ON x.vendor_id = device.vendor_id
            AND x.device_id = device.device_id
            AND x.subsys_device_id = device.subsys_device_id
            AND x.subsys_vendor_id = device.subsys_vendor_id
            AND x.bus = device.bus
            AND x.driver = device.driver
            AND x.description = device.description
            AND x.id != device.id;
    ALTER TABLE device
        MODIFY vendor_id VARCHAR(4),
        MODIFY device_id VARCHAR(4),
        MODIFY subsys_device_id VARCHAR(4),
        MODIFY subsys_vendor_id VARCHAR(4),
        ADD UNIQUE device_uix_1 (
            vendor_id, device_id, subsys_device_id, subsys_vendor_id,
            bus, driver, description, device_class_id);

To roll back:

    ALTER TABLE task_type
        DROP INDEX type,
        MODIFY type VARCHAR(256);

    ALTER TABLE task_package
        DROP INDEX package,
        MODIFY package VARCHAR(2048) COLLATE utf8_bin;

    ALTER TABLE device_class
        DROP INDEX device_class,
        MODIFY device_class VARCHAR(24);

    ALTER TABLE device
        DROP INDEX device_uix_1,
        MODIFY vendor_id VARCHAR(255),
        MODIFY device_id VARCHAR(255),
        MODIFY subsys_device_id VARCHAR(255),
        MODIFY subsys_vendor_id VARCHAR(255);
