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 -- the migration scripts do not use the major 'version' number
18 -- so 5.0 sets subversion at 100
19 -- in case your database misses the site and persons tags feature,
20 -- you might wish to first upgrade to 4.3-rc16 before moving to some 5.0
21 -- or run the up script here
22 -- http://svn.planet-lab.org/svn/PLCAPI/branches/4.3/migrations/
24 INSERT INTO sfa_db_version (version, subversion) VALUES (1, 1);
26 --------------------------------------------------------------------------------
27 -- Aggregates and store procedures
28 --------------------------------------------------------------------------------
30 -- Like MySQL GROUP_CONCAT(), this function aggregates values into a
32 CREATE AGGREGATE array_accum (
34 basetype = anyelement,
40 CREATE TABLE record_types (
41 record_type text PRIMARY KEY
43 INSERT INTO record_types (record_type) VALUES ('authority');
44 INSERT INTO record_types (record_type) VALUES ('authority+sa');
45 INSERT INTO record_types (record_type) VALUES ('authority+am');
46 INSERT INTO record_types (record_type) VALUES ('authority+sm');
47 INSERT INTO record_types (record_type) VALUES ('user');
48 INSERT INTO record_types (record_type) VALUES ('slice');
49 INSERT INTO record_types (record_type) VALUES ('node');
53 CREATE TABLE records (
54 record_id serial PRIMARY KEY ,
56 authority text NOT NULL,
59 type text REFERENCES record_types,
61 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
62 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
64 CREATE INDEX sfa_hrn_ids on records (hrn);
65 CREATE INDEX sfa_type_ids on records (type);
66 CREATE INDEX sfa_authority_ids on records (authority);
67 CREATE INDEX sfa_peer_authority_ids on records (peer_authority);
68 CREATE INDEX sfa_pointer_ids on records (pointer);