showard | 82e1b48 | 2010-01-15 00:21:58 +0000 | [diff] [blame] | 1 | import common |
| 2 | from autotest_lib.database import migrate |
| 3 | |
showard | 26b7ec7 | 2009-12-21 22:43:57 +0000 | [diff] [blame] | 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`); |
jamesren | ac21e99 | 2010-02-12 00:46:21 +0000 | [diff] [blame] | 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`); |
showard | 26b7ec7 | 2009-12-21 22:43:57 +0000 | [diff] [blame] | 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`); |
jamesren | ac21e99 | 2010-02-12 00:46:21 +0000 | [diff] [blame] | 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`); |
showard | 26b7ec7 | 2009-12-21 22:43:57 +0000 | [diff] [blame] | 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 | """ |
showard | 82e1b48 | 2010-01-15 00:21:58 +0000 | [diff] [blame] | 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 | |
jamesren | ac21e99 | 2010-02-12 00:46:21 +0000 | [diff] [blame] | 255 | manager.execute_script(UP_SQL % dict(tko_db_name=tko_manager.get_db_name())) |