1 import common 2 from autotest_lib.database import migrate 3 4 UP_SQL = """\ 5 BEGIN; 6 7 SET storage_engine = InnoDB; 8 9 CREATE 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 21 CREATE 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 ; 29 ALTER TABLE `planner_hosts` ADD CONSTRAINT hosts_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 30 ALTER TABLE `planner_hosts` ADD CONSTRAINT hosts_host_id_fk FOREIGN KEY (`host_id`) REFERENCES `afe_hosts` (`id`); 31 32 33 CREATE 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 41 CREATE 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 ; 48 ALTER TABLE `planner_tests` ADD CONSTRAINT tests_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 49 ALTER TABLE `planner_tests` ADD CONSTRAINT tests_control_file_id_fk FOREIGN KEY (`control_file_id`) REFERENCES `planner_test_control_files` (`id`); 50 51 52 CREATE 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 ; 59 ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 60 ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_test_id_fk FOREIGN KEY (`test_id`) REFERENCES `planner_tests` (`id`); 61 ALTER TABLE `planner_test_jobs` ADD CONSTRAINT test_jobs_afe_job_id_fk FOREIGN KEY (`afe_job_id`) REFERENCES `afe_jobs` (`id`); 62 CREATE TABLE `planner_bugs` ( 63 `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 64 `external_uid` varchar(255) NOT NULL UNIQUE 65 ) 66 ; 67 68 69 CREATE 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 ; 80 ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 81 ALTER TABLE `planner_test_runs` ADD CONSTRAINT test_runs_test_job_id_fk FOREIGN KEY (`test_job_id`) REFERENCES `planner_test_jobs` (`id`); 82 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`); 83 84 85 CREATE 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 93 CREATE 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 ; 105 ALTER TABLE `planner_history` ADD CONSTRAINT history_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 106 ALTER TABLE `planner_history` ADD CONSTRAINT history_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`); 107 ALTER TABLE `planner_history` ADD CONSTRAINT history_data_type_id_fk FOREIGN KEY (`data_type_id`) REFERENCES `planner_data_types` (`id`); 108 109 110 CREATE 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 ; 119 ALTER TABLE `planner_saved_objects` ADD CONSTRAINT saved_objects_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`); 120 121 122 CREATE 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 ; 128 ALTER TABLE `planner_custom_queries` ADD CONSTRAINT custom_queries_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 129 130 131 CREATE 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 140 CREATE 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 ; 148 ALTER TABLE `planner_autoprocess` ADD CONSTRAINT autoprocess_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 149 150 151 CREATE 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 ; 158 ALTER TABLE `planner_plan_owners` ADD CONSTRAINT plan_owners_plan_id_fk FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`); 159 ALTER TABLE `planner_plan_owners` ADD CONSTRAINT plan_owners_user_id_fk FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`); 160 161 162 CREATE 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 ; 169 ALTER TABLE `planner_test_run_bugs` ADD CONSTRAINT test_run_bugs_testrun_id_fk FOREIGN KEY (`testrun_id`) REFERENCES `planner_test_runs` (`id`); 170 ALTER TABLE `planner_test_run_bugs` ADD CONSTRAINT test_run_bugs_bug_id_fk FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`); 171 172 173 CREATE 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 ; 180 ALTER TABLE `planner_autoprocess_labels` ADD CONSTRAINT autoprocess_labels_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`); 181 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`); 182 183 184 CREATE 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 ; 191 ALTER TABLE `planner_autoprocess_keyvals` ADD CONSTRAINT autoprocess_keyvals_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`); 192 ALTER TABLE `planner_autoprocess_keyvals` ADD CONSTRAINT autoprocess_keyvals_keyval_id_fk FOREIGN KEY (`keyval_id`) REFERENCES `planner_keyvals` (`id`); 193 194 195 CREATE 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 ; 202 ALTER TABLE `planner_autoprocess_bugs` ADD CONSTRAINT autoprocess_bugs_autoprocess_id_fk FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`); 203 ALTER TABLE `planner_autoprocess_bugs` ADD CONSTRAINT autoprocess_bugs_bug_id_fk FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`); 204 205 206 CREATE INDEX `planner_hosts_plan_id` ON `planner_hosts` (`plan_id`); 207 CREATE INDEX `planner_hosts_host_id` ON `planner_hosts` (`host_id`); 208 CREATE INDEX `planner_tests_plan_id` ON `planner_tests` (`plan_id`); 209 CREATE INDEX `planner_tests_control_file_id` ON `planner_tests` (`control_file_id`); 210 CREATE INDEX `planner_test_jobs_plan_id` ON `planner_test_jobs` (`plan_id`); 211 CREATE INDEX `planner_test_jobs_test_id` ON `planner_test_jobs` (`test_id`); 212 CREATE INDEX `planner_test_jobs_afe_job_id` ON `planner_test_jobs` (`afe_job_id`); 213 CREATE INDEX `planner_test_runs_plan_id` ON `planner_test_runs` (`plan_id`); 214 CREATE INDEX `planner_test_runs_test_job_id` ON `planner_test_runs` (`test_job_id`); 215 CREATE INDEX `planner_test_runs_tko_test_id` ON `planner_test_runs` (`tko_test_id`); 216 CREATE INDEX `planner_history_plan_id` ON `planner_history` (`plan_id`); 217 CREATE INDEX `planner_history_user_id` ON `planner_history` (`user_id`); 218 CREATE INDEX `planner_history_data_type_id` ON `planner_history` (`data_type_id`); 219 CREATE INDEX `planner_saved_objects_user_id` ON `planner_saved_objects` (`user_id`); 220 CREATE INDEX `planner_custom_queries_plan_id` ON `planner_custom_queries` (`plan_id`); 221 CREATE INDEX `planner_autoprocess_plan_id` ON `planner_autoprocess` (`plan_id`); 222 223 COMMIT; 224 """ 225 226 DOWN_SQL = """\ 227 DROP TABLE IF EXISTS planner_autoprocess_labels; 228 DROP TABLE IF EXISTS planner_autoprocess_bugs; 229 DROP TABLE IF EXISTS planner_autoprocess_keyvals; 230 DROP TABLE IF EXISTS planner_autoprocess; 231 DROP TABLE IF EXISTS planner_custom_queries; 232 DROP TABLE IF EXISTS planner_saved_objects; 233 DROP TABLE IF EXISTS planner_history; 234 DROP TABLE IF EXISTS planner_data_types; 235 DROP TABLE IF EXISTS planner_hosts; 236 DROP TABLE IF EXISTS planner_keyvals; 237 DROP TABLE IF EXISTS planner_plan_owners; 238 DROP TABLE IF EXISTS planner_test_run_bugs; 239 DROP TABLE IF EXISTS planner_test_runs; 240 DROP TABLE IF EXISTS planner_test_jobs; 241 DROP TABLE IF EXISTS planner_tests; 242 DROP TABLE IF EXISTS planner_test_control_files; 243 DROP TABLE IF EXISTS planner_bugs; 244 DROP TABLE IF EXISTS planner_plans; 245 """ 246 247 248 def 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
