blob: 568cbec202caa71849fabf256758442e399610a3 [file] [log] [blame]
import common
from autotest_lib.database import migrate
UP_SQL = """\
BEGIN;
SET storage_engine = InnoDB;
CREATE TABLE `planner_plans` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` varchar(255) NOT NULL UNIQUE,
`label_override` varchar(255) NULL,
`support` longtext NOT NULL,
`complete` bool NOT NULL,
`dirty` bool NOT NULL,
`initialized` bool NOT NULL
)
;
CREATE TABLE `planner_hosts` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`host_id` integer NOT NULL,
`complete` bool NOT NULL,
`blocked` bool NOT NULL
)
;
ALTER TABLE `planner_hosts` ADD CONSTRAINT hosts_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
ALTER TABLE `planner_hosts` ADD CONSTRAINT hosts_host_id_fk FOREIGN KEY (`host_id`) REFERENCES `afe_hosts` (`id`);
CREATE TABLE `planner_test_control_files` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`the_hash` varchar(40) NOT NULL UNIQUE,
`contents` longtext NOT NULL
)
;
CREATE TABLE `planner_tests` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`control_file_id` integer NOT NULL,
`execution_order` integer NOT NULL
)
;
ALTER TABLE `planner_tests` ADD CONSTRAINT tests_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
ALTER TABLE `planner_tests` ADD CONSTRAINT tests_control_file_id_fk FOREIGN KEY (`control_file_id`) REFERENCES `planner_test_control_files` (`id`);
CREATE TABLE `planner_test_jobs` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`test_id` integer NOT NULL,
`afe_job_id` integer NOT NULL
)
;
ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_test_id_fk FOREIGN KEY (`test_id`) REFERENCES `planner_tests` (`id`);
ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_afe_job_id_fk FOREIGN KEY (`afe_job_id`) REFERENCES `afe_jobs` (`id`);
CREATE TABLE `planner_bugs` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`external_uid` varchar(255) NOT NULL UNIQUE
)
;
CREATE TABLE `planner_test_runs` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`test_job_id` integer NOT NULL,
`tko_test_id` integer(10) UNSIGNED NOT NULL,
`status` varchar(16) NOT NULL,
`finalized` bool NOT NULL,
`seen` bool NOT NULL,
`triaged` bool NOT NULL
)
;
ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_test_job_id_fk FOREIGN KEY (`test_job_id`) REFERENCES `planner_test_jobs` (`id`);
ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_tko_test_id_fk FOREIGN KEY (`tko_test_id`) REFERENCES `%(tko_db_name)s`.`tko_tests` (`test_idx`);
CREATE TABLE `planner_data_types` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` varchar(255) NOT NULL,
`db_table` varchar(255) NOT NULL
)
;
CREATE TABLE `planner_history` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`action_id` integer NOT NULL,
`user_id` integer NOT NULL,
`data_type_id` integer NOT NULL,
`object_id` integer NOT NULL,
`old_object_repr` longtext NOT NULL,
`new_object_repr` longtext NOT NULL,
`time` datetime NOT NULL
)
;
ALTER TABLE `planner_history` ADD CONSTRAINT history_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
ALTER TABLE `planner_history` ADD CONSTRAINT history_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`);
ALTER TABLE `planner_history` ADD CONSTRAINT history_data_type_id_fk FOREIGN KEY (`data_type_id`) REFERENCES `planner_data_types` (`id`);
CREATE TABLE `planner_saved_objects` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`user_id` integer NOT NULL,
`type` varchar(16) NOT NULL,
`name` varchar(255) NOT NULL,
`encoded_object` longtext NOT NULL,
UNIQUE (`user_id`, `type`, `name`)
)
;
ALTER TABLE `planner_saved_objects` ADD CONSTRAINT saved_objects_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`);
CREATE TABLE `planner_custom_queries` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`query` longtext NOT NULL
)
;
ALTER TABLE `planner_custom_queries` ADD CONSTRAINT custom_queries_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
CREATE TABLE `planner_keyvals` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`the_hash` varchar(40) NOT NULL UNIQUE,
`key` varchar(1024) NOT NULL,
`value` varchar(1024) NOT NULL
)
;
CREATE TABLE `planner_autoprocess` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`condition` longtext NOT NULL,
`enabled` bool NOT NULL,
`reason_override` varchar(255) NULL
)
;
ALTER TABLE `planner_autoprocess` ADD CONSTRAINT autoprocess_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
CREATE TABLE `planner_plan_owners` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`plan_id` integer NOT NULL,
`user_id` integer NOT NULL,
UNIQUE (`plan_id`, `user_id`)
)
;
ALTER TABLE `planner_plan_owners` ADD CONSTRAINT plan_owners_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`);
ALTER TABLE `planner_plan_owners` ADD CONSTRAINT plan_owners_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`);
CREATE TABLE `planner_test_run_bugs` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`testrun_id` integer NOT NULL,
`bug_id` integer NOT NULL,
UNIQUE (`testrun_id`, `bug_id`)
)
;
ALTER TABLE `planner_test_run_bugs` ADD CONSTRAINT test_run_bugs_testrun_id_fk FOREIGN KEY (`testrun_id`) REFERENCES `planner_test_runs` (`id`);
ALTER TABLE `planner_test_run_bugs` ADD CONSTRAINT test_run_bugs_bug_id_fk FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`);
CREATE TABLE `planner_autoprocess_labels` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`autoprocess_id` integer NOT NULL,
`testlabel_id` integer NOT NULL,
UNIQUE (`autoprocess_id`, `testlabel_id`)
)
;
ALTER TABLE `planner_autoprocess_labels` ADD CONSTRAINT autoprocess_labels_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`);
ALTER TABLE `planner_autoprocess_labels` ADD CONSTRAINT autoprocess_labels_testlabel_id_fk FOREIGN KEY (`testlabel_id`) REFERENCES `%(tko_db_name)s`.`tko_test_labels` (`id`);
CREATE TABLE `planner_autoprocess_keyvals` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`autoprocess_id` integer NOT NULL,
`keyval_id` integer NOT NULL,
UNIQUE (`autoprocess_id`, `keyval_id`)
)
;
ALTER TABLE `planner_autoprocess_keyvals` ADD CONSTRAINT autoprocess_keyvals_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`);
ALTER TABLE `planner_autoprocess_keyvals` ADD CONSTRAINT autoprocess_keyvals_keyval_id_fk FOREIGN KEY (`keyval_id`) REFERENCES `planner_keyvals` (`id`);
CREATE TABLE `planner_autoprocess_bugs` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`autoprocess_id` integer NOT NULL,
`bug_id` integer NOT NULL,
UNIQUE (`autoprocess_id`, `bug_id`)
)
;
ALTER TABLE `planner_autoprocess_bugs` ADD CONSTRAINT autoprocess_bugs_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`);
ALTER TABLE `planner_autoprocess_bugs` ADD CONSTRAINT autoprocess_bugs_bug_id_fk FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`);
CREATE INDEX `planner_hosts_plan_id` ON `planner_hosts` (`plan_id`);
CREATE INDEX `planner_hosts_host_id` ON `planner_hosts` (`host_id`);
CREATE INDEX `planner_tests_plan_id` ON `planner_tests` (`plan_id`);
CREATE INDEX `planner_tests_control_file_id` ON `planner_tests` (`control_file_id`);
CREATE INDEX `planner_test_jobs_plan_id` ON `planner_test_jobs` (`plan_id`);
CREATE INDEX `planner_test_jobs_test_id` ON `planner_test_jobs` (`test_id`);
CREATE INDEX `planner_test_jobs_afe_job_id` ON `planner_test_jobs` (`afe_job_id`);
CREATE INDEX `planner_test_runs_plan_id` ON `planner_test_runs` (`plan_id`);
CREATE INDEX `planner_test_runs_test_job_id` ON `planner_test_runs` (`test_job_id`);
CREATE INDEX `planner_test_runs_tko_test_id` ON `planner_test_runs` (`tko_test_id`);
CREATE INDEX `planner_history_plan_id` ON `planner_history` (`plan_id`);
CREATE INDEX `planner_history_user_id` ON `planner_history` (`user_id`);
CREATE INDEX `planner_history_data_type_id` ON `planner_history` (`data_type_id`);
CREATE INDEX `planner_saved_objects_user_id` ON `planner_saved_objects` (`user_id`);
CREATE INDEX `planner_custom_queries_plan_id` ON `planner_custom_queries` (`plan_id`);
CREATE INDEX `planner_autoprocess_plan_id` ON `planner_autoprocess` (`plan_id`);
COMMIT;
"""
DOWN_SQL = """\
DROP TABLE IF EXISTS planner_autoprocess_labels;
DROP TABLE IF EXISTS planner_autoprocess_bugs;
DROP TABLE IF EXISTS planner_autoprocess_keyvals;
DROP TABLE IF EXISTS planner_autoprocess;
DROP TABLE IF EXISTS planner_custom_queries;
DROP TABLE IF EXISTS planner_saved_objects;
DROP TABLE IF EXISTS planner_history;
DROP TABLE IF EXISTS planner_data_types;
DROP TABLE IF EXISTS planner_hosts;
DROP TABLE IF EXISTS planner_keyvals;
DROP TABLE IF EXISTS planner_plan_owners;
DROP TABLE IF EXISTS planner_test_run_bugs;
DROP TABLE IF EXISTS planner_test_runs;
DROP TABLE IF EXISTS planner_test_jobs;
DROP TABLE IF EXISTS planner_tests;
DROP TABLE IF EXISTS planner_test_control_files;
DROP TABLE IF EXISTS planner_bugs;
DROP TABLE IF EXISTS planner_plans;
"""
def migrate_up(manager):
tko_manager = migrate.get_migration_manager(db_name='TKO', debug=False,
force=False)
if tko_manager.get_db_version() < 31:
raise Exception('You must update the TKO database to at least version '
'31 before applying AUTOTEST_WEB migration 45')
manager.execute_script(UP_SQL % dict(tko_db_name=tko_manager.get_db_name()))