5 SET client_encoding = 'UNICODE';
7 --------------------------------------------------------------------------------
9 --------------------------------------------------------------------------------
12 CREATE TABLE sfa_db_version (
13 version integer NOT NULL,
14 subversion integer NOT NULL DEFAULT 0
17 -- for upgrades/migrations
19 INSERT INTO sfa_db_version (version, subversion) VALUES (1, 1);
21 --------------------------------------------------------------------------------
22 -- Aggregates and store procedures
23 --------------------------------------------------------------------------------
25 -- Like MySQL GROUP_CONCAT(), this function aggregates values into a
27 CREATE AGGREGATE array_accum (
29 basetype = anyelement,
35 CREATE TABLE record_types (
36 record_type text PRIMARY KEY
38 INSERT INTO record_types (record_type) VALUES ('authority');
39 INSERT INTO record_types (record_type) VALUES ('authority+sa');
40 INSERT INTO record_types (record_type) VALUES ('authority+am');
41 INSERT INTO record_types (record_type) VALUES ('authority+sm');
42 INSERT INTO record_types (record_type) VALUES ('user');
43 INSERT INTO record_types (record_type) VALUES ('slice');
44 INSERT INTO record_types (record_type) VALUES ('node');
48 CREATE TABLE records (
49 record_id serial PRIMARY KEY ,
51 authority text NOT NULL,
54 type text REFERENCES record_types,
56 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
57 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
59 CREATE INDEX sfa_hrn_ids on records (hrn);
60 CREATE INDEX sfa_type_ids on records (type);
61 CREATE INDEX sfa_authority_ids on records (authority);
62 CREATE INDEX sfa_peer_authority_ids on records (peer_authority);
63 CREATE INDEX sfa_pointer_ids on records (pointer);