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
|
SELECT setting::integer < 90600 AS pre_96
FROM pg_settings WHERE name = 'server_version_num';
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
-- Unique keys are already pretty much guaranteed by the underlying features of
-- PostgreSQL, but test them anyway.
CREATE TABLE uk (id integer, s integer, e integer, CONSTRAINT uk_pkey PRIMARY KEY (id, s, e));
SELECT periods.add_period('uk', 'p', 's', 'e');
SELECT periods.add_unique_key('uk', ARRAY['id'], 'p', key_name => 'uk_id_p', unique_constraint => 'uk_pkey');
TABLE periods.unique_keys;
INSERT INTO uk (id, s, e) VALUES (100, 1, 3), (100, 3, 4), (100, 4, 10); -- success
INSERT INTO uk (id, s, e) VALUES (200, 1, 3), (200, 3, 4), (200, 5, 10); -- success
INSERT INTO uk (id, s, e) VALUES (300, 1, 3), (300, 3, 5), (300, 4, 10); -- fail
CREATE TABLE fk (id integer, uk_id integer, s integer, e integer, PRIMARY KEY (id));
SELECT periods.add_period('fk', 'q', 's', 'e');
SELECT periods.add_foreign_key('fk', ARRAY['uk_id'], 'q', 'uk_id_p',
key_name => 'fk_uk_id_q',
fk_insert_trigger => 'fki',
fk_update_trigger => 'fku',
uk_update_trigger => 'uku',
uk_delete_trigger => 'ukd');
TABLE periods.foreign_keys;
SELECT periods.drop_foreign_key('fk', 'fk_uk_id_q');
SELECT periods.add_foreign_key('fk', ARRAY['uk_id'], 'q', 'uk_id_p', key_name => 'fk_uk_id_q');
TABLE periods.foreign_keys;
-- INSERT
INSERT INTO fk VALUES (0, 100, 0, 1); -- fail
INSERT INTO fk VALUES (0, 100, 0, 10); -- fail
INSERT INTO fk VALUES (0, 100, 1, 11); -- fail
INSERT INTO fk VALUES (1, 100, 1, 3); -- success
INSERT INTO fk VALUES (2, 100, 1, 10); -- success
-- UPDATE
UPDATE fk SET e = 20 WHERE id = 1; -- fail
UPDATE fk SET e = 6 WHERE id = 1; -- success
UPDATE uk SET s = 2 WHERE (id, s, e) = (100, 1, 3); -- fail
UPDATE uk SET s = 0 WHERE (id, s, e) = (100, 1, 3); -- success
-- DELETE
DELETE FROM uk WHERE (id, s, e) = (100, 3, 4); -- fail
DELETE FROM uk WHERE (id, s, e) = (200, 3, 5); -- success
DROP TABLE fk;
DROP TABLE uk;
|
