1UP_SQL = """ 2DROP TABLE IF EXISTS planner_test_run_bugs; 3DROP TABLE IF EXISTS planner_test_runs; 4DROP TABLE IF EXISTS planner_history; 5DROP TABLE IF EXISTS planner_autoprocess_bugs; 6DROP TABLE IF EXISTS planner_bugs; 7DROP TABLE IF EXISTS planner_hosts; 8DROP TABLE IF EXISTS planner_additional_parameter_values; 9DROP TABLE IF EXISTS planner_additional_parameters; 10DROP TABLE IF EXISTS planner_autoprocess_labels; 11DROP TABLE IF EXISTS planner_autoprocess_keyvals; 12DROP TABLE IF EXISTS planner_autoprocess; 13DROP TABLE IF EXISTS planner_custom_queries; 14DROP TABLE IF EXISTS planner_plan_host_labels; 15DROP TABLE IF EXISTS planner_plan_owners; 16DROP TABLE IF EXISTS planner_saved_objects; 17DROP TABLE IF EXISTS planner_test_configs_skipped_hosts; 18DROP TABLE IF EXISTS planner_test_jobs; 19DROP TABLE IF EXISTS planner_data_types; 20DROP TABLE IF EXISTS planner_keyvals; 21DROP TABLE IF EXISTS planner_test_configs; 22DROP TABLE IF EXISTS planner_test_control_files; 23DROP TABLE IF EXISTS planner_plans; 24""" 25 26DOWN_SQL = """ 27-- 28-- Table structure for table `planner_plans` 29-- 30 31SET @saved_cs_client = @@character_set_client; 32SET character_set_client = utf8; 33CREATE 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; 44SET character_set_client = @saved_cs_client; 45 46-- 47-- Table structure for table `planner_test_control_files` 48-- 49 50SET @saved_cs_client = @@character_set_client; 51SET character_set_client = utf8; 52CREATE 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; 59SET character_set_client = @saved_cs_client; 60 61-- 62-- Table structure for table `planner_test_configs` 63-- 64 65SET @saved_cs_client = @@character_set_client; 66SET character_set_client = utf8; 67CREATE 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; 82SET character_set_client = @saved_cs_client; 83 84-- 85-- Table structure for table `planner_keyvals` 86-- 87 88SET @saved_cs_client = @@character_set_client; 89SET character_set_client = utf8; 90CREATE 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; 98SET character_set_client = @saved_cs_client; 99 100-- 101-- Table structure for table `planner_data_types` 102-- 103 104SET @saved_cs_client = @@character_set_client; 105SET character_set_client = utf8; 106CREATE 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; 112SET character_set_client = @saved_cs_client; 113 114-- 115-- Table structure for table `planner_test_jobs` 116-- 117 118SET @saved_cs_client = @@character_set_client; 119SET character_set_client = utf8; 120CREATE 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; 133SET character_set_client = @saved_cs_client; 134 135-- 136-- Table structure for table `planner_test_configs_skipped_hosts` 137-- 138 139CREATE 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 145ALTER TABLE planner_test_configs_skipped_hosts 146ADD CONSTRAINT planner_test_configs_skipped_hosts_testconfig_ibfk 147FOREIGN KEY (testconfig_id) REFERENCES planner_test_configs (id); 148 149ALTER TABLE planner_test_configs_skipped_hosts 150ADD CONSTRAINT planner_test_configs_skipped_hosts_host_ibfk 151FOREIGN KEY (host_id) REFERENCES afe_hosts (id); 152 153-- 154-- Table structure for table `planner_saved_objects` 155-- 156 157SET @saved_cs_client = @@character_set_client; 158SET character_set_client = utf8; 159CREATE 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; 170SET character_set_client = @saved_cs_client; 171 172-- 173-- Table structure for table `planner_plan_owners` 174-- 175 176SET @saved_cs_client = @@character_set_client; 177SET character_set_client = utf8; 178CREATE 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; 188SET character_set_client = @saved_cs_client; 189 190-- 191-- Table structure for table `planner_plan_host_labels` 192-- 193 194SET @saved_cs_client = @@character_set_client; 195SET character_set_client = utf8; 196CREATE 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; 206SET character_set_client = @saved_cs_client; 207 208-- 209-- Table structure for table `planner_custom_queries` 210-- 211 212SET @saved_cs_client = @@character_set_client; 213SET character_set_client = utf8; 214CREATE 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; 222SET character_set_client = @saved_cs_client; 223 224-- 225-- Table structure for table `planner_autoprocess` 226-- 227 228SET @saved_cs_client = @@character_set_client; 229SET character_set_client = utf8; 230CREATE 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; 240SET character_set_client = @saved_cs_client; 241 242-- 243-- Table structure for table `planner_autoprocess_keyvals` 244-- 245 246SET @saved_cs_client = @@character_set_client; 247SET character_set_client = utf8; 248CREATE 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; 258SET character_set_client = @saved_cs_client; 259 260-- 261-- Table structure for table `planner_autoprocess_labels` 262-- 263 264SET @saved_cs_client = @@character_set_client; 265SET character_set_client = utf8; 266CREATE 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; 276SET character_set_client = @saved_cs_client; 277 278-- 279-- Table structure for table `planner_additional_parameters` 280-- 281 282CREATE 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 290ALTER TABLE planner_additional_parameters 291ADD CONSTRAINT planner_additional_parameters_plan_ibfk 292FOREIGN KEY (plan_id) REFERENCES planner_plans (id); 293 294ALTER TABLE planner_additional_parameters 295ADD CONSTRAINT planner_additional_parameters_unique 296UNIQUE KEY (plan_id, hostname_regex, param_type); 297 298-- 299-- Table structure for table `planner_additional_parameter_values` 300-- 301 302CREATE 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 309ALTER TABLE planner_additional_parameter_values 310ADD CONSTRAINT planner_additional_parameter_values_additional_parameter_ibfk 311FOREIGN KEY (additional_parameter_id) 312 REFERENCES planner_additional_parameters (id); 313 314ALTER TABLE planner_additional_parameter_values 315ADD CONSTRAINT planner_additional_parameter_values_unique 316UNIQUE KEY (additional_parameter_id, `key`); 317 318-- 319-- Table structure for table `planner_hosts` 320-- 321 322SET @saved_cs_client = @@character_set_client; 323SET character_set_client = utf8; 324CREATE 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; 337SET character_set_client = @saved_cs_client; 338 339-- 340-- Table structure for table `planner_bugs` 341-- 342 343SET @saved_cs_client = @@character_set_client; 344SET character_set_client = utf8; 345CREATE 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; 351SET character_set_client = @saved_cs_client; 352 353-- 354-- Table structure for table `planner_autoprocess_bugs` 355-- 356 357SET @saved_cs_client = @@character_set_client; 358SET character_set_client = utf8; 359CREATE 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; 369SET character_set_client = @saved_cs_client; 370 371-- 372-- Table structure for table `planner_history` 373-- 374 375SET @saved_cs_client = @@character_set_client; 376SET character_set_client = utf8; 377CREATE 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; 395SET character_set_client = @saved_cs_client; 396 397-- 398-- Table structure for table `planner_test_runs` 399-- 400 401SET @saved_cs_client = @@character_set_client; 402SET character_set_client = utf8; 403CREATE 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; 424SET character_set_client = @saved_cs_client; 425 426-- 427-- Table structure for table `planner_test_run_bugs` 428-- 429 430SET @saved_cs_client = @@character_set_client; 431SET character_set_client = utf8; 432CREATE 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; 442SET character_set_client = @saved_cs_client; 443""" 444