1import common 2from autotest_lib.database import migrate 3 4UP_SQL = """\ 5BEGIN; 6 7SET storage_engine = InnoDB; 8 9CREATE TABLE `planner_plans` ( 10 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 11 `name` varchar(255) NOT NULL UNIQUE, 12 `label_override` varchar(255) NULL, 13 `support` longtext NOT NULL, 14 `complete` bool NOT NULL, 15 `dirty` bool NOT NULL, 16 `initialized` bool NOT NULL 17) 18; 19 20 21CREATE TABLE `planner_hosts` ( 22 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 23 `plan_id` integer NOT NULL, 24 `host_id` integer NOT NULL, 25 `complete` bool NOT NULL, 26 `blocked` bool NOT NULL 27) 28; 29ALTER TABLE `planner_hosts` ADD CONSTRAINT hosts_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 30ALTER TABLE `planner_hosts` ADD CONSTRAINT hosts_host_id_fk FOREIGN KEY (`host_id`) REFERENCES `afe_hosts` (`id`); 31 32 33CREATE TABLE `planner_test_control_files` ( 34 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 35 `the_hash` varchar(40) NOT NULL UNIQUE, 36 `contents` longtext NOT NULL 37) 38; 39 40 41CREATE TABLE `planner_tests` ( 42 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 43 `plan_id` integer NOT NULL, 44 `control_file_id` integer NOT NULL, 45 `execution_order` integer NOT NULL 46) 47; 48ALTER TABLE `planner_tests` ADD CONSTRAINT tests_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 49ALTER TABLE `planner_tests` ADD CONSTRAINT tests_control_file_id_fk FOREIGN KEY (`control_file_id`) REFERENCES `planner_test_control_files` (`id`); 50 51 52CREATE TABLE `planner_test_jobs` ( 53 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 54 `plan_id` integer NOT NULL, 55 `test_id` integer NOT NULL, 56 `afe_job_id` integer NOT NULL 57) 58; 59ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 60ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_test_id_fk FOREIGN KEY (`test_id`) REFERENCES `planner_tests` (`id`); 61ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_afe_job_id_fk FOREIGN KEY (`afe_job_id`) REFERENCES `afe_jobs` (`id`); 62CREATE TABLE `planner_bugs` ( 63 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 64 `external_uid` varchar(255) NOT NULL UNIQUE 65) 66; 67 68 69CREATE TABLE `planner_test_runs` ( 70 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 71 `plan_id` integer NOT NULL, 72 `test_job_id` integer NOT NULL, 73 `tko_test_id` integer(10) UNSIGNED NOT NULL, 74 `status` varchar(16) NOT NULL, 75 `finalized` bool NOT NULL, 76 `seen` bool NOT NULL, 77 `triaged` bool NOT NULL 78) 79; 80ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 81ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_test_job_id_fk FOREIGN KEY (`test_job_id`) REFERENCES `planner_test_jobs` (`id`); 82ALTER 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`); 83 84 85CREATE TABLE `planner_data_types` ( 86 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 87 `name` varchar(255) NOT NULL, 88 `db_table` varchar(255) NOT NULL 89) 90; 91 92 93CREATE TABLE `planner_history` ( 94 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 95 `plan_id` integer NOT NULL, 96 `action_id` integer NOT NULL, 97 `user_id` integer NOT NULL, 98 `data_type_id` integer NOT NULL, 99 `object_id` integer NOT NULL, 100 `old_object_repr` longtext NOT NULL, 101 `new_object_repr` longtext NOT NULL, 102 `time` datetime NOT NULL 103) 104; 105ALTER TABLE `planner_history` ADD CONSTRAINT history_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 106ALTER TABLE `planner_history` ADD CONSTRAINT history_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`); 107ALTER TABLE `planner_history` ADD CONSTRAINT history_data_type_id_fk FOREIGN KEY (`data_type_id`) REFERENCES `planner_data_types` (`id`); 108 109 110CREATE TABLE `planner_saved_objects` ( 111 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 112 `user_id` integer NOT NULL, 113 `type` varchar(16) NOT NULL, 114 `name` varchar(255) NOT NULL, 115 `encoded_object` longtext NOT NULL, 116 UNIQUE (`user_id`, `type`, `name`) 117) 118; 119ALTER TABLE `planner_saved_objects` ADD CONSTRAINT saved_objects_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`); 120 121 122CREATE TABLE `planner_custom_queries` ( 123 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 124 `plan_id` integer NOT NULL, 125 `query` longtext NOT NULL 126) 127; 128ALTER TABLE `planner_custom_queries` ADD CONSTRAINT custom_queries_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 129 130 131CREATE TABLE `planner_keyvals` ( 132 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 133 `the_hash` varchar(40) NOT NULL UNIQUE, 134 `key` varchar(1024) NOT NULL, 135 `value` varchar(1024) NOT NULL 136) 137; 138 139 140CREATE TABLE `planner_autoprocess` ( 141 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 142 `plan_id` integer NOT NULL, 143 `condition` longtext NOT NULL, 144 `enabled` bool NOT NULL, 145 `reason_override` varchar(255) NULL 146) 147; 148ALTER TABLE `planner_autoprocess` ADD CONSTRAINT autoprocess_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 149 150 151CREATE TABLE `planner_plan_owners` ( 152 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 153 `plan_id` integer NOT NULL, 154 `user_id` integer NOT NULL, 155 UNIQUE (`plan_id`, `user_id`) 156) 157; 158ALTER TABLE `planner_plan_owners` ADD CONSTRAINT plan_owners_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 159ALTER TABLE `planner_plan_owners` ADD CONSTRAINT plan_owners_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`); 160 161 162CREATE TABLE `planner_test_run_bugs` ( 163 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 164 `testrun_id` integer NOT NULL, 165 `bug_id` integer NOT NULL, 166 UNIQUE (`testrun_id`, `bug_id`) 167) 168; 169ALTER TABLE `planner_test_run_bugs` ADD CONSTRAINT test_run_bugs_testrun_id_fk FOREIGN KEY (`testrun_id`) REFERENCES `planner_test_runs` (`id`); 170ALTER TABLE `planner_test_run_bugs` ADD CONSTRAINT test_run_bugs_bug_id_fk FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`); 171 172 173CREATE TABLE `planner_autoprocess_labels` ( 174 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 175 `autoprocess_id` integer NOT NULL, 176 `testlabel_id` integer NOT NULL, 177 UNIQUE (`autoprocess_id`, `testlabel_id`) 178) 179; 180ALTER TABLE `planner_autoprocess_labels` ADD CONSTRAINT autoprocess_labels_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`); 181ALTER TABLE `planner_autoprocess_labels` ADD CONSTRAINT autoprocess_labels_testlabel_id_fk FOREIGN KEY (`testlabel_id`) REFERENCES `%(tko_db_name)s`.`tko_test_labels` (`id`); 182 183 184CREATE TABLE `planner_autoprocess_keyvals` ( 185 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 186 `autoprocess_id` integer NOT NULL, 187 `keyval_id` integer NOT NULL, 188 UNIQUE (`autoprocess_id`, `keyval_id`) 189) 190; 191ALTER TABLE `planner_autoprocess_keyvals` ADD CONSTRAINT autoprocess_keyvals_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`); 192ALTER TABLE `planner_autoprocess_keyvals` ADD CONSTRAINT autoprocess_keyvals_keyval_id_fk FOREIGN KEY (`keyval_id`) REFERENCES `planner_keyvals` (`id`); 193 194 195CREATE TABLE `planner_autoprocess_bugs` ( 196 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 197 `autoprocess_id` integer NOT NULL, 198 `bug_id` integer NOT NULL, 199 UNIQUE (`autoprocess_id`, `bug_id`) 200) 201; 202ALTER TABLE `planner_autoprocess_bugs` ADD CONSTRAINT autoprocess_bugs_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`); 203ALTER TABLE `planner_autoprocess_bugs` ADD CONSTRAINT autoprocess_bugs_bug_id_fk FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`); 204 205 206CREATE INDEX `planner_hosts_plan_id` ON `planner_hosts` (`plan_id`); 207CREATE INDEX `planner_hosts_host_id` ON `planner_hosts` (`host_id`); 208CREATE INDEX `planner_tests_plan_id` ON `planner_tests` (`plan_id`); 209CREATE INDEX `planner_tests_control_file_id` ON `planner_tests` (`control_file_id`); 210CREATE INDEX `planner_test_jobs_plan_id` ON `planner_test_jobs` (`plan_id`); 211CREATE INDEX `planner_test_jobs_test_id` ON `planner_test_jobs` (`test_id`); 212CREATE INDEX `planner_test_jobs_afe_job_id` ON `planner_test_jobs` (`afe_job_id`); 213CREATE INDEX `planner_test_runs_plan_id` ON `planner_test_runs` (`plan_id`); 214CREATE INDEX `planner_test_runs_test_job_id` ON `planner_test_runs` (`test_job_id`); 215CREATE INDEX `planner_test_runs_tko_test_id` ON `planner_test_runs` (`tko_test_id`); 216CREATE INDEX `planner_history_plan_id` ON `planner_history` (`plan_id`); 217CREATE INDEX `planner_history_user_id` ON `planner_history` (`user_id`); 218CREATE INDEX `planner_history_data_type_id` ON `planner_history` (`data_type_id`); 219CREATE INDEX `planner_saved_objects_user_id` ON `planner_saved_objects` (`user_id`); 220CREATE INDEX `planner_custom_queries_plan_id` ON `planner_custom_queries` (`plan_id`); 221CREATE INDEX `planner_autoprocess_plan_id` ON `planner_autoprocess` (`plan_id`); 222 223COMMIT; 224""" 225 226DOWN_SQL = """\ 227DROP TABLE IF EXISTS planner_autoprocess_labels; 228DROP TABLE IF EXISTS planner_autoprocess_bugs; 229DROP TABLE IF EXISTS planner_autoprocess_keyvals; 230DROP TABLE IF EXISTS planner_autoprocess; 231DROP TABLE IF EXISTS planner_custom_queries; 232DROP TABLE IF EXISTS planner_saved_objects; 233DROP TABLE IF EXISTS planner_history; 234DROP TABLE IF EXISTS planner_data_types; 235DROP TABLE IF EXISTS planner_hosts; 236DROP TABLE IF EXISTS planner_keyvals; 237DROP TABLE IF EXISTS planner_plan_owners; 238DROP TABLE IF EXISTS planner_test_run_bugs; 239DROP TABLE IF EXISTS planner_test_runs; 240DROP TABLE IF EXISTS planner_test_jobs; 241DROP TABLE IF EXISTS planner_tests; 242DROP TABLE IF EXISTS planner_test_control_files; 243DROP TABLE IF EXISTS planner_bugs; 244DROP TABLE IF EXISTS planner_plans; 245""" 246 247 248def migrate_up(manager): 249 tko_manager = migrate.get_migration_manager(db_name='TKO', debug=False, 250 force=False) 251 if tko_manager.get_db_version() < 31: 252 raise Exception('You must update the TKO database to at least version ' 253 '31 before applying AUTOTEST_WEB migration 45') 254 255 manager.execute_script(UP_SQL % dict(tko_db_name=tko_manager.get_db_name())) 256