Eyrx9R4P

1BEGIN;
2
3DROP SCHEMA IF EXISTS bookbrainz_internal CASCADE;
4DROP SCHEMA IF EXISTS bookbrainz CASCADE;
5CREATE SCHEMA bookbrainz_internal;
6CREATE SCHEMA bookbrainz;
7
8SET search_path = 'bookbrainz_internal';
9
10--------------------------------------------------------------------------------
11-- Versioning
12
13CREATE TABLE revision (
14    id SERIAL NOT NULL PRIMARY KEY,
15    commited_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
16);
17
18CREATE TABLE branch (
19    id SERIAL NOT NULL PRIMARY KEY,
20    revision INT NOT NULL REFERENCES revision (id),
21    master BOOLEAN NOT NULL DEFAULT FALSE,
22    gid UUID NOT NULL,
23
24    UNIQUE (master, gid)
25);
26
27CREATE OR REPLACE FUNCTION deny_as_immutable()
28RETURNS trigger AS $$
29BEGIN
30    RAISE EXCEPTION 'Cannot update row; this data is immutable';
31END;
32$$ LANGUAGE 'plpgsql';
33
34--------------------------------------------------------------------------------
35-- Edit API
36
37CREATE OR REPLACE FUNCTION make_history(IN version_t REGCLASS,
38                                        IN revision_t REGCLASS,
39                                        IN thedat anyelement)
40RETURNS INT AS $$
41DECLARE
42    commit_id INT;
43    uuid UUID;
44    version_id INT;
45BEGIN
46    EXECUTE 'INSERT INTO ' || version_t || ' SELECT ($1).* RETURNING version, gid' USING thedat INTO version_id, uuid;
47
48    INSERT INTO bookbrainz_internal.revision DEFAULT VALUES
49    RETURNING id INTO commit_id;
50
51    EXECUTE 'INSERT INTO ' || revision_t || ' (revision, version)
52         VALUES ($1, $2)'
53         USING commit_id, version_id;
54
55    INSERT INTO bookbrainz_internal.branch (revision, gid, master)
56    VALUES (commit_id, uuid, TRUE);
57
58    RETURN version_id;
59END;
60$$ LANGUAGE plpgsql;
61
62CREATE OR REPLACE FUNCTION make_core_entity ( IN version_t REGCLASS )
63RETURNS void AS $$
64BEGIN
65    EXECUTE 'ALTER TABLE ' || version_t || ' ADD COLUMN version SERIAL NOT NULL';
66    EXECUTE 'ALTER TABLE ' || version_t || ' ADD PRIMARY KEY (version)';
67    EXECUTE 'ALTER TABLE ' || version_t || ' ADD COLUMN gid UUID NOT NULL
68                                             DEFAULT ''a0196280-b008-11e0-9f1c-0800200c9a66''';
69
70    EXECUTE 'CREATE TRIGGER immutable
71             BEFORE UPDATE ON ' || version_t || '
72             FOR EACH ROW EXECUTE PROCEDURE deny_as_immutable()';
73
74    EXECUTE
75        'CREATE TABLE ' || version_t || '_revision (
76            revision INT NOT NULL REFERENCES revision (id),
77            version  INT NOT NULL REFERENCES ' || version_t || ' (version)
78        )';
79
80    EXECUTE
81        'CREATE VIEW bookbrainz.' || version_t || ' AS
82            SELECT t.*
83            FROM ' || version_t || ' t
84            JOIN ' || version_t || '_revision trev ON trev.version = t.version
85            JOIN revision ON trev.revision = revision.id
86            JOIN branch ON branch.revision = revision.id
87            WHERE branch.master = TRUE';
88
89    EXECUTE
90        'CREATE RULE ' || version_t || '_ins
91         AS ON INSERT TO bookbrainz.' || version_t || '
92         DO INSTEAD SELECT make_history( ''' || version_t || '''::regclass
93                                       , ''' || version_t || '_revision''::regclass
94                                       , NEW)';
95END;
96$$ LANGUAGE 'plpgsql';
97
98--------------------------------------------------------------------------------
99-- People
100
101CREATE TABLE person (
102    name TEXT NOT NULL
103);
104SELECT make_core_entity('person');
105
106SET search_path = 'bookbrainz';
107INSERT INTO person (name) VALUES ('Ollie');
108SELECT * FROM person;
109
110COMMIT;