1 UP_SQL = """ 2 DROP TABLE IF EXISTS planner_test_run_bugs; 3 DROP TABLE IF EXISTS planner_test_runs; 4 DROP TABLE IF EXISTS planner_history; 5 DROP TABLE IF EXISTS planner_autoprocess_bugs; 6 DROP TABLE IF EXISTS planner_bugs; 7 DROP TABLE IF EXISTS planner_hosts; 8 DROP TABLE IF EXISTS planner_additional_parameter_values; 9 DROP TABLE IF EXISTS planner_additional_parameters; 10 DROP TABLE IF EXISTS planner_autoprocess_labels; 11 DROP TABLE IF EXISTS planner_autoprocess_keyvals; 12 DROP TABLE IF EXISTS planner_autoprocess; 13 DROP TABLE IF EXISTS planner_custom_queries; 14 DROP TABLE IF EXISTS planner_plan_host_labels; 15 DROP TABLE IF EXISTS planner_plan_owners; 16 DROP TABLE IF EXISTS planner_saved_objects; 17 DROP TABLE IF EXISTS planner_test_configs_skipped_hosts; 18 DROP TABLE IF EXISTS planner_test_jobs; 19 DROP TABLE IF EXISTS planner_data_types; 20 DROP TABLE IF EXISTS planner_keyvals; 21 DROP TABLE IF EXISTS planner_test_configs; 22 DROP TABLE IF EXISTS planner_test_control_files; 23 DROP TABLE IF EXISTS planner_plans; 24 """ 25 26 DOWN_SQL = """ 27 -- 28 -- Table structure for table `planner_plans` 29 -- 30 31 SET @saved_cs_client = @@character_set_client; 32 SET character_set_client = utf8; 33 CREATE TABLE `planner_plans` ( 34 `id` int(11) NOT NULL auto_increment, 35 `name` varchar(255) NOT NULL, 36 `label_override` varchar(255) default NULL, 37 `support` longtext NOT NULL, 38 `complete` tinyint(1) NOT NULL, 39 `dirty` tinyint(1) NOT NULL, 40 `initialized` tinyint(1) default '0', 41 PRIMARY KEY (`id`), 42 UNIQUE KEY `name` (`name`) 43 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 44 SET character_set_client = @saved_cs_client; 45 46 -- 47 -- Table structure for table `planner_test_control_files` 48 -- 49 50 SET @saved_cs_client = @@character_set_client; 51 SET character_set_client = utf8; 52 CREATE TABLE `planner_test_control_files` ( 53 `id` int(11) NOT NULL auto_increment, 54 `the_hash` varchar(40) NOT NULL, 55 `contents` longtext NOT NULL, 56 PRIMARY KEY (`id`), 57 UNIQUE KEY `the_hash` (`the_hash`) 58 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 59 SET character_set_client = @saved_cs_client; 60 61 -- 62 -- Table structure for table `planner_test_configs` 63 -- 64 65 SET @saved_cs_client = @@character_set_client; 66 SET character_set_client = utf8; 67 CREATE TABLE `planner_test_configs` ( 68 `id` int(11) NOT NULL auto_increment, 69 `plan_id` int(11) NOT NULL, 70 `control_file_id` int(11) NOT NULL, 71 `execution_order` int(11) NOT NULL, 72 `alias` varchar(255) NOT NULL, 73 `estimated_runtime` int(11) NOT NULL, 74 `is_server` tinyint(1) default '1', 75 PRIMARY KEY (`id`), 76 UNIQUE KEY `tests_plan_id_alias_unique` (`plan_id`,`alias`), 77 KEY `planner_tests_plan_id` (`plan_id`), 78 KEY `planner_tests_control_file_id` (`control_file_id`), 79 CONSTRAINT `tests_control_file_id_fk` FOREIGN KEY (`control_file_id`) REFERENCES `planner_test_control_files` (`id`), 80 CONSTRAINT `tests_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`) 81 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 82 SET character_set_client = @saved_cs_client; 83 84 -- 85 -- Table structure for table `planner_keyvals` 86 -- 87 88 SET @saved_cs_client = @@character_set_client; 89 SET character_set_client = utf8; 90 CREATE TABLE `planner_keyvals` ( 91 `id` int(11) NOT NULL auto_increment, 92 `the_hash` varchar(40) NOT NULL, 93 `key` varchar(1024) NOT NULL, 94 `value` varchar(1024) NOT NULL, 95 PRIMARY KEY (`id`), 96 UNIQUE KEY `the_hash` (`the_hash`) 97 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 98 SET character_set_client = @saved_cs_client; 99 100 -- 101 -- Table structure for table `planner_data_types` 102 -- 103 104 SET @saved_cs_client = @@character_set_client; 105 SET character_set_client = utf8; 106 CREATE TABLE `planner_data_types` ( 107 `id` int(11) NOT NULL auto_increment, 108 `name` varchar(255) NOT NULL, 109 `db_table` varchar(255) NOT NULL, 110 PRIMARY KEY (`id`) 111 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 112 SET character_set_client = @saved_cs_client; 113 114 -- 115 -- Table structure for table `planner_test_jobs` 116 -- 117 118 SET @saved_cs_client = @@character_set_client; 119 SET character_set_client = utf8; 120 CREATE TABLE `planner_test_jobs` ( 121 `id` int(11) NOT NULL auto_increment, 122 `plan_id` int(11) NOT NULL, 123 `test_config_id` int(11) NOT NULL, 124 `afe_job_id` int(11) NOT NULL, 125 PRIMARY KEY (`id`), 126 KEY `planner_test_jobs_plan_id` (`plan_id`), 127 KEY `planner_test_jobs_afe_job_id` (`afe_job_id`), 128 KEY `planner_test_jobs_test_config_id` (`test_config_id`), 129 CONSTRAINT `test_jobs_afe_job_id_fk` FOREIGN KEY (`afe_job_id`) REFERENCES `afe_jobs` (`id`), 130 CONSTRAINT `test_jobs_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`), 131 CONSTRAINT `test_jobs_test_config_id_fk` FOREIGN KEY (`test_config_id`) REFERENCES `planner_test_configs` (`id`) 132 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 133 SET character_set_client = @saved_cs_client; 134 135 -- 136 -- Table structure for table `planner_test_configs_skipped_hosts` 137 -- 138 139 CREATE TABLE planner_test_configs_skipped_hosts ( 140 testconfig_id INT NOT NULL, 141 host_id INT NOT NULL, 142 PRIMARY KEY (testconfig_id, host_id) 143 ) ENGINE = InnoDB; 144 145 ALTER TABLE planner_test_configs_skipped_hosts 146 ADD CONSTRAINT planner_test_configs_skipped_hosts_testconfig_ibfk 147 FOREIGN KEY (testconfig_id) REFERENCES planner_test_configs (id); 148 149 ALTER TABLE planner_test_configs_skipped_hosts 150 ADD CONSTRAINT planner_test_configs_skipped_hosts_host_ibfk 151 FOREIGN KEY (host_id) REFERENCES afe_hosts (id); 152 153 -- 154 -- Table structure for table `planner_saved_objects` 155 -- 156 157 SET @saved_cs_client = @@character_set_client; 158 SET character_set_client = utf8; 159 CREATE TABLE `planner_saved_objects` ( 160 `id` int(11) NOT NULL auto_increment, 161 `user_id` int(11) NOT NULL, 162 `type` varchar(16) NOT NULL, 163 `name` varchar(255) NOT NULL, 164 `encoded_object` longtext NOT NULL, 165 PRIMARY KEY (`id`), 166 UNIQUE KEY `user_id` (`user_id`,`type`,`name`), 167 KEY `planner_saved_objects_user_id` (`user_id`), 168 CONSTRAINT `saved_objects_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`) 169 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 170 SET character_set_client = @saved_cs_client; 171 172 -- 173 -- Table structure for table `planner_plan_owners` 174 -- 175 176 SET @saved_cs_client = @@character_set_client; 177 SET character_set_client = utf8; 178 CREATE TABLE `planner_plan_owners` ( 179 `id` int(11) NOT NULL auto_increment, 180 `plan_id` int(11) NOT NULL, 181 `user_id` int(11) NOT NULL, 182 PRIMARY KEY (`id`), 183 UNIQUE KEY `plan_id` (`plan_id`,`user_id`), 184 KEY `plan_owners_user_id_fk` (`user_id`), 185 CONSTRAINT `plan_owners_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`), 186 CONSTRAINT `plan_owners_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`) 187 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 188 SET character_set_client = @saved_cs_client; 189 190 -- 191 -- Table structure for table `planner_plan_host_labels` 192 -- 193 194 SET @saved_cs_client = @@character_set_client; 195 SET character_set_client = utf8; 196 CREATE TABLE `planner_plan_host_labels` ( 197 `id` int(11) NOT NULL auto_increment, 198 `plan_id` int(11) NOT NULL, 199 `label_id` int(11) NOT NULL, 200 PRIMARY KEY (`id`), 201 KEY `plan_host_labels_plan_id_fk` (`plan_id`), 202 KEY `plan_host_labels_label_id_fk` (`label_id`), 203 CONSTRAINT `plan_host_labels_label_id_fk` FOREIGN KEY (`label_id`) REFERENCES `afe_labels` (`id`), 204 CONSTRAINT `plan_host_labels_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`) 205 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 206 SET character_set_client = @saved_cs_client; 207 208 -- 209 -- Table structure for table `planner_custom_queries` 210 -- 211 212 SET @saved_cs_client = @@character_set_client; 213 SET character_set_client = utf8; 214 CREATE TABLE `planner_custom_queries` ( 215 `id` int(11) NOT NULL auto_increment, 216 `plan_id` int(11) NOT NULL, 217 `query` longtext NOT NULL, 218 PRIMARY KEY (`id`), 219 KEY `planner_custom_queries_plan_id` (`plan_id`), 220 CONSTRAINT `custom_queries_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`) 221 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 222 SET character_set_client = @saved_cs_client; 223 224 -- 225 -- Table structure for table `planner_autoprocess` 226 -- 227 228 SET @saved_cs_client = @@character_set_client; 229 SET character_set_client = utf8; 230 CREATE TABLE `planner_autoprocess` ( 231 `id` int(11) NOT NULL auto_increment, 232 `plan_id` int(11) NOT NULL, 233 `condition` longtext NOT NULL, 234 `enabled` tinyint(1) NOT NULL, 235 `reason_override` varchar(255) default NULL, 236 PRIMARY KEY (`id`), 237 KEY `planner_autoprocess_plan_id` (`plan_id`), 238 CONSTRAINT `autoprocess_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`) 239 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 240 SET character_set_client = @saved_cs_client; 241 242 -- 243 -- Table structure for table `planner_autoprocess_keyvals` 244 -- 245 246 SET @saved_cs_client = @@character_set_client; 247 SET character_set_client = utf8; 248 CREATE TABLE `planner_autoprocess_keyvals` ( 249 `id` int(11) NOT NULL auto_increment, 250 `autoprocess_id` int(11) NOT NULL, 251 `keyval_id` int(11) NOT NULL, 252 PRIMARY KEY (`id`), 253 UNIQUE KEY `autoprocess_id` (`autoprocess_id`,`keyval_id`), 254 KEY `autoprocess_keyvals_keyval_id_fk` (`keyval_id`), 255 CONSTRAINT `autoprocess_keyvals_autoprocess_id_fk` FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`), 256 CONSTRAINT `autoprocess_keyvals_keyval_id_fk` FOREIGN KEY (`keyval_id`) REFERENCES `planner_keyvals` (`id`) 257 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 258 SET character_set_client = @saved_cs_client; 259 260 -- 261 -- Table structure for table `planner_autoprocess_labels` 262 -- 263 264 SET @saved_cs_client = @@character_set_client; 265 SET character_set_client = utf8; 266 CREATE TABLE `planner_autoprocess_labels` ( 267 `id` int(11) NOT NULL auto_increment, 268 `autoprocess_id` int(11) NOT NULL, 269 `testlabel_id` int(11) NOT NULL, 270 PRIMARY KEY (`id`), 271 UNIQUE KEY `autoprocess_id` (`autoprocess_id`,`testlabel_id`), 272 KEY `autoprocess_labels_testlabel_id_fk` (`testlabel_id`), 273 CONSTRAINT `autoprocess_labels_autoprocess_id_fk` FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`), 274 CONSTRAINT `autoprocess_labels_testlabel_id_fk` FOREIGN KEY (`testlabel_id`) REFERENCES `tko_test_labels` (`id`) 275 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 276 SET character_set_client = @saved_cs_client; 277 278 -- 279 -- Table structure for table `planner_additional_parameters` 280 -- 281 282 CREATE TABLE planner_additional_parameters ( 283 id INT PRIMARY KEY AUTO_INCREMENT, 284 plan_id INT NOT NULL, 285 hostname_regex VARCHAR(255) NOT NULL, 286 param_type VARCHAR(32) NOT NULL, 287 application_order INT NOT NULL 288 ) ENGINE = InnoDB; 289 290 ALTER TABLE planner_additional_parameters 291 ADD CONSTRAINT planner_additional_parameters_plan_ibfk 292 FOREIGN KEY (plan_id) REFERENCES planner_plans (id); 293 294 ALTER TABLE planner_additional_parameters 295 ADD CONSTRAINT planner_additional_parameters_unique 296 UNIQUE KEY (plan_id, hostname_regex, param_type); 297 298 -- 299 -- Table structure for table `planner_additional_parameter_values` 300 -- 301 302 CREATE TABLE planner_additional_parameter_values ( 303 id INT PRIMARY KEY AUTO_INCREMENT, 304 additional_parameter_id INT NOT NULL, 305 `key` VARCHAR(255) NOT NULL, 306 value VARCHAR(255) NOT NULL 307 ) ENGINE = InnoDB; 308 309 ALTER TABLE planner_additional_parameter_values 310 ADD CONSTRAINT planner_additional_parameter_values_additional_parameter_ibfk 311 FOREIGN KEY (additional_parameter_id) 312 REFERENCES planner_additional_parameters (id); 313 314 ALTER TABLE planner_additional_parameter_values 315 ADD CONSTRAINT planner_additional_parameter_values_unique 316 UNIQUE KEY (additional_parameter_id, `key`); 317 318 -- 319 -- Table structure for table `planner_hosts` 320 -- 321 322 SET @saved_cs_client = @@character_set_client; 323 SET character_set_client = utf8; 324 CREATE TABLE `planner_hosts` ( 325 `id` int(11) NOT NULL auto_increment, 326 `plan_id` int(11) NOT NULL, 327 `host_id` int(11) NOT NULL, 328 `complete` tinyint(1) NOT NULL, 329 `blocked` tinyint(1) NOT NULL, 330 `added_by_label` tinyint(1) default '0', 331 PRIMARY KEY (`id`), 332 KEY `planner_hosts_plan_id` (`plan_id`), 333 KEY `planner_hosts_host_id` (`host_id`), 334 CONSTRAINT `hosts_host_id_fk` FOREIGN KEY (`host_id`) REFERENCES `afe_hosts` (`id`), 335 CONSTRAINT `hosts_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`) 336 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 337 SET character_set_client = @saved_cs_client; 338 339 -- 340 -- Table structure for table `planner_bugs` 341 -- 342 343 SET @saved_cs_client = @@character_set_client; 344 SET character_set_client = utf8; 345 CREATE TABLE `planner_bugs` ( 346 `id` int(11) NOT NULL auto_increment, 347 `external_uid` varchar(255) NOT NULL, 348 PRIMARY KEY (`id`), 349 UNIQUE KEY `external_uid` (`external_uid`) 350 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 351 SET character_set_client = @saved_cs_client; 352 353 -- 354 -- Table structure for table `planner_autoprocess_bugs` 355 -- 356 357 SET @saved_cs_client = @@character_set_client; 358 SET character_set_client = utf8; 359 CREATE TABLE `planner_autoprocess_bugs` ( 360 `id` int(11) NOT NULL auto_increment, 361 `autoprocess_id` int(11) NOT NULL, 362 `bug_id` int(11) NOT NULL, 363 PRIMARY KEY (`id`), 364 UNIQUE KEY `autoprocess_id` (`autoprocess_id`,`bug_id`), 365 KEY `autoprocess_bugs_bug_id_fk` (`bug_id`), 366 CONSTRAINT `autoprocess_bugs_autoprocess_id_fk` FOREIGN KEY (`autoprocess_id`) REFERENCES `planner_autoprocess` (`id`), 367 CONSTRAINT `autoprocess_bugs_bug_id_fk` FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`) 368 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 369 SET character_set_client = @saved_cs_client; 370 371 -- 372 -- Table structure for table `planner_history` 373 -- 374 375 SET @saved_cs_client = @@character_set_client; 376 SET character_set_client = utf8; 377 CREATE TABLE `planner_history` ( 378 `id` int(11) NOT NULL auto_increment, 379 `plan_id` int(11) NOT NULL, 380 `action_id` int(11) NOT NULL, 381 `user_id` int(11) NOT NULL, 382 `data_type_id` int(11) NOT NULL, 383 `object_id` int(11) NOT NULL, 384 `old_object_repr` longtext NOT NULL, 385 `new_object_repr` longtext NOT NULL, 386 `time` datetime NOT NULL, 387 PRIMARY KEY (`id`), 388 KEY `planner_history_plan_id` (`plan_id`), 389 KEY `planner_history_user_id` (`user_id`), 390 KEY `planner_history_data_type_id` (`data_type_id`), 391 CONSTRAINT `history_data_type_id_fk` FOREIGN KEY (`data_type_id`) REFERENCES `planner_data_types` (`id`), 392 CONSTRAINT `history_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`), 393 CONSTRAINT `history_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `afe_users` (`id`) 394 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 395 SET character_set_client = @saved_cs_client; 396 397 -- 398 -- Table structure for table `planner_test_runs` 399 -- 400 401 SET @saved_cs_client = @@character_set_client; 402 SET character_set_client = utf8; 403 CREATE TABLE `planner_test_runs` ( 404 `id` int(11) NOT NULL auto_increment, 405 `plan_id` int(11) NOT NULL, 406 `test_job_id` int(11) NOT NULL, 407 `tko_test_id` int(10) unsigned NOT NULL, 408 `status` varchar(16) NOT NULL, 409 `finalized` tinyint(1) NOT NULL, 410 `seen` tinyint(1) NOT NULL, 411 `triaged` tinyint(1) NOT NULL, 412 `host_id` int(11) NOT NULL, 413 PRIMARY KEY (`id`), 414 UNIQUE KEY `test_runs_unique` (`plan_id`,`test_job_id`,`tko_test_id`,`host_id`), 415 KEY `planner_test_runs_plan_id` (`plan_id`), 416 KEY `planner_test_runs_test_job_id` (`test_job_id`), 417 KEY `planner_test_runs_tko_test_id` (`tko_test_id`), 418 KEY `test_runs_host_id_fk` (`host_id`), 419 CONSTRAINT `test_runs_host_id_fk` FOREIGN KEY (`host_id`) REFERENCES `planner_hosts` (`id`), 420 CONSTRAINT `test_runs_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `planner_plans` (`id`), 421 CONSTRAINT `test_runs_test_job_id_fk` FOREIGN KEY (`test_job_id`) REFERENCES `planner_test_jobs` (`id`), 422 CONSTRAINT `test_runs_tko_test_id_fk` FOREIGN KEY (`tko_test_id`) REFERENCES `tko_tests` (`test_idx`) 423 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 424 SET character_set_client = @saved_cs_client; 425 426 -- 427 -- Table structure for table `planner_test_run_bugs` 428 -- 429 430 SET @saved_cs_client = @@character_set_client; 431 SET character_set_client = utf8; 432 CREATE TABLE `planner_test_run_bugs` ( 433 `id` int(11) NOT NULL auto_increment, 434 `testrun_id` int(11) NOT NULL, 435 `bug_id` int(11) NOT NULL, 436 PRIMARY KEY (`id`), 437 UNIQUE KEY `testrun_id` (`testrun_id`,`bug_id`), 438 KEY `test_run_bugs_bug_id_fk` (`bug_id`), 439 CONSTRAINT `test_run_bugs_bug_id_fk` FOREIGN KEY (`bug_id`) REFERENCES `planner_bugs` (`id`), 440 CONSTRAINT `test_run_bugs_testrun_id_fk` FOREIGN KEY (`testrun_id`) REFERENCES `planner_test_runs` (`id`) 441 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 442 SET character_set_client = @saved_cs_client; 443 """ 444
