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