frontend/migrations/066_drop_planner.py

   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 """