1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
|
# Copyright 2010 Canonical Ltd. This software is licensed under the
# GNU Affero General Public License version 3 (see the file LICENSE).
from storm.zope.schema import ZSchema
__all__ = [
"create_schema",
]
def create_schema():
from launchpadresults.schema import project as patch_package
return ZSchema(CREATE, DROP, DELETE, patch_package)
CREATE = [
"""
CREATE TABLE person_twin (
id INTEGER PRIMARY KEY)
""",
"""
CREATE TABLE project_twin (
id INTEGER PRIMARY KEY)
""",
"""
CREATE TABLE test_run (
id SERIAL NOT NULL PRIMARY KEY,
person_id INTEGER NOT NULL REFERENCES person_twin ON DELETE CASCADE,
project_id INTEGER REFERENCES project_twin ON DELETE CASCADE,
date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
CONSTRAINT valid_target CHECK (project_id IS NOT NULL))
""",
"""
CREATE INDEX test_run__person_id__idx ON test_run (person_id)
""",
"""
CREATE UNIQUE INDEX test_run__project_id__date_created__key ON test_run (project_id, date_created) WHERE project_id IS NOT NULL
""",
"""
CREATE TABLE test_case (
id SERIAL NOT NULL PRIMARY KEY,
project_id INTEGER REFERENCES project_twin ON DELETE CASCADE,
name TEXT NOT NULL,
CONSTRAINT valid_target CHECK (project_id IS NOT NULL))
""",
"""
CREATE UNIQUE INDEX test_case__project_id__name__key ON test_case (project_id, name) WHERE project_id IS NOT NULL
""",
"""
CREATE TABLE test_result (
test_case_id INTEGER NOT NULL REFERENCES test_case ON DELETE CASCADE,
status INTEGER NOT NULL DEFAULT 2, -- UNINITIATED
output TEXT NOT NULL,
date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
date_deleted TIMESTAMP WITHOUT TIME ZONE NOT NULL
DEFAULT '3000-01-01',
CONSTRAINT valid_dates CHECK (date_created < date_deleted))
""",
"""
CREATE UNIQUE INDEX test_result__test_id__date_created__key ON test_result (test_case_id, date_created)
""",
"""
CREATE INDEX test_result__test_id__idx ON test_result (test_case_id)
""",
"""
CREATE INDEX test_result__date_created__date_deleted__idx ON test_result (date_created, date_deleted)
""",
"""
CREATE INDEX test_result__test_id__status__output__date_created__date_deleted__idx ON test_result (test_case_id, status, decode(md5(output), 'hex'), date_created, date_deleted)
""",
"""
CREATE LANGUAGE plpgsql
""",
"""
CREATE FUNCTION test_result_delete() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
test_result_record RECORD;
BEGIN
-- Check if there is a test_result between two equivalent dimensions
SELECT INTO test_result_record r2.date_deleted FROM test_result r1
JOIN test_result r2
ON r1.test_case_id = r2.test_case_id
AND r1.status = r2.status
AND decode(md5(r1.output), 'hex') = decode(md5(r2.output), 'hex')
AND r1.date_deleted = OLD.date_created
AND r2.date_created = OLD.date_deleted;
IF FOUND THEN
DELETE FROM test_result
WHERE test_result.test_case_id = OLD.test_case_id
AND test_result.date_created = OLD.date_deleted;
UPDATE test_result SET date_deleted = test_result_record.date_deleted
WHERE test_result.test_case_id = OLD.test_case_id
AND test_result.date_deleted = OLD.date_created;
ELSE
-- Extend forward dimension
UPDATE test_result SET date_deleted = OLD.date_deleted
WHERE test_result.test_case_id = OLD.test_case_id
AND test_result.date_deleted = OLD.date_created;
IF NOT FOUND THEN
-- Reduce backward dimension
UPDATE test_result set date_created = OLD.date_created
WHERE test_result.test_case_id = OLD.test_case_id
AND test_result.date_created = OLD.date_deleted;
END IF;
END IF;
RETURN OLD;
END;
$$
""",
"""
CREATE TRIGGER test_result_delete_trigger
AFTER DELETE ON test_result
FOR EACH ROW
EXECUTE PROCEDURE test_result_delete()
""",
"""
CREATE OR REPLACE FUNCTION test_result_update() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
IF OLD.test_case_id != NEW.test_case_id
OR OLD.status != NEW.status
OR OLD.output != NEW.output THEN
DELETE FROM test_result
WHERE test_case_id = OLD.test_case_id
AND date_created = OLD.date_created;
INSERT INTO test_result (
test_case_id,
status,
output,
date_created,
date_deleted)
VALUES (
NEW.test_case_id,
NEW.status,
NEW.output,
NEW.date_created,
NEW.date_deleted);
RETURN NULL;
END IF;
RETURN NEW;
END;
$$
""",
"""
CREATE TRIGGER test_result_update_trigger
BEFORE UPDATE ON test_result
FOR EACH ROW
EXECUTE PROCEDURE test_result_update()
""",
"""
CREATE OR REPLACE FUNCTION test_result_insert() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
test_result_record RECORD;
BEGIN
-- First, check for the most common case when
-- there is a test result in a backward dimension.
SELECT INTO test_result_record test_result.status, test_result.output
FROM test_result
WHERE test_result.test_case_id = NEW.test_case_id
AND test_result.date_created <= NEW.date_created
AND test_result.date_deleted > NEW.date_created;
IF FOUND THEN
IF NEW.status = test_result_record.status
AND NEW.output = test_result_record.output THEN
-- No need to extend forward dimension
RETURN NULL;
ELSE
-- Reducing backward dimension
UPDATE test_result SET date_deleted = NEW.date_created
WHERE test_result.test_case_id = NEW.test_case_id
AND test_result.date_created <= NEW.date_created
AND test_result.date_deleted > NEW.date_created;
END IF;
END IF;
-- Then, check when there is a test result in a forward dimension.
SELECT INTO test_result_record test_result.status, test_result.output, test_result.date_created
FROM test_result
WHERE test_result.test_case_id = NEW.test_case_id
AND test_result.date_created > NEW.date_created
ORDER BY date_created LIMIT 1;
IF FOUND THEN
IF NEW.status = test_result_record.status
AND NEW.output = test_result_record.output THEN
-- Extending forward dimension
UPDATE test_result SET date_created = NEW.date_created
WHERE test_result.test_case_id = NEW.test_case_id
AND test_result.date_created = test_result_record.date_created;
RETURN NULL;
ELSE
-- Reducing forward dimension
NEW.date_deleted := test_result_record.date_created;
END IF;
END IF;
RETURN NEW;
END;
$$
""",
"""
CREATE TRIGGER test_result_insert_trigger
BEFORE INSERT ON test_result
FOR EACH ROW
EXECUTE PROCEDURE test_result_insert()
""",
"""
CREATE FUNCTION get_test_case_id(
test_project_id INTEGER, test_name TEXT) RETURNS INTEGER
LANGUAGE plpgsql AS
$$
DECLARE
test_case_id INTEGER;
BEGIN
LOOP
SELECT test_case.id INTO test_case_id FROM test_case
WHERE test_case.project_id = test_project_id
AND test_case.name = test_name;
IF FOUND THEN
RETURN test_case_id;
END IF;
BEGIN
INSERT INTO test_case (
project_id,
name)
VALUES (
test_project_id,
test_name)
RETURNING id INTO test_case_id;
RETURN test_case_id;
EXCEPTION
WHEN unique_violation THEN
NULL; -- do nothing
END;
END LOOP;
END;
$$
""",
"""
CREATE OR REPLACE FUNCTION add_test_result(
test_result_test_case_id INTEGER, test_result_status INTEGER,
test_result_output TEXT, test_result_date_created TIMESTAMP)
RETURNS VOID
LANGUAGE plpgsql AS
$$
BEGIN
LOOP
BEGIN
INSERT INTO test_result (
test_case_id,
status,
output,
date_created)
VALUES (
test_result_test_case_id,
test_result_status,
test_result_output,
test_result_date_created);
RETURN;
EXCEPTION
WHEN unique_violation THEN
NULL; -- do nothing
END;
END LOOP;
END;
$$
""",
]
DROP = [
"DROP FUNCTION add_test_result(INTEGER, TEXT, TIMESTAMP)",
"DROP FUNCTION get_test_case_id(INTEGER, TEXT, TEXT)",
"DROP FUNCTION test_result_insert()",
"DROP FUNCTION test_result_update()",
"DROP FUNCTION test_result_delete()",
"DROP LANGUAGE plpgsql",
"DROP TABLE test_result",
"DROP TABLE test_case",
"DROP TABLE test_run",
"DROP TABLE project_twin",
"DROP TABLE person_twin",
]
DELETE = [
"DELETE FROM test_result",
"DELETE FROM test_case",
"DELETE FROM test_run",
"DELETE FROM project_twin",
"DELETE FROM person_twin",
]
|