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
|
CREATE TYPE enum_owner_type AS ENUM('user', 'usergroup', 'role');
CREATE TABLE director_basket (
uuid bytea CHECK(LENGTH(uuid) = 16) NOT NULL,
basket_name VARCHAR(64) NOT NULL,
owner_type enum_owner_type NOT NULL,
owner_value VARCHAR(255) NOT NULL,
objects text NOT NULL, -- json-encoded
PRIMARY KEY (uuid)
);
CREATE UNIQUE INDEX basket_basket_name ON director_basket (basket_name);
CREATE TABLE director_basket_content (
checksum bytea CHECK(LENGTH(checksum) = 20) NOT NULL,
summary VARCHAR(255) NOT NULL, -- json
content text NOT NULL, -- json
PRIMARY KEY (checksum)
);
CREATE TABLE director_basket_snapshot (
basket_uuid bytea CHECK(LENGTH(basket_uuid) = 16) NOT NULL,
ts_create bigint NOT NULL,
content_checksum bytea CHECK(LENGTH(content_checksum) = 20) NOT NULL,
PRIMARY KEY (basket_uuid, ts_create),
CONSTRAINT basked_snapshot_basket
FOREIGN KEY (basket_uuid)
REFERENCES director_basket (uuid)
ON DELETE CASCADE
ON UPDATE RESTRICT,
CONSTRAINT basked_snapshot_content
FOREIGN KEY (content_checksum)
REFERENCES director_basket_content (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
CREATE INDEX basket_snapshot_sort_idx ON director_basket_snapshot (ts_create);
INSERT INTO director_schema_migration
(schema_version, migration_time)
VALUES (153, NOW());
|
