--
-- Copyright (C) 2006 The Trustees of Princeton University
--
+-- NOTE: this file was first created for version 4.3, the filename might be confusing
+--
-- $Id$
+-- $URL$
--
SET client_encoding = 'UNICODE';
subversion integer NOT NULL DEFAULT 0
) WITH OIDS;
-INSERT INTO plc_db_version (version, subversion) VALUES (5, 0);
+-- the migration scripts do not use the major 'version' number
+-- so 5.0 sets subversion at 100
+-- in case your database misses the site and persons tags feature,
+-- you might wish to first upgrade to 4.3-rc16 before moving to some 5.0
+-- or run the up script here
+-- http://svn.planet-lab.org/svn/PLCAPI/branches/4.3/migrations/
+
+INSERT INTO plc_db_version (version, subversion) VALUES (5, 100);
--------------------------------------------------------------------------------
-- Aggregates and store procedures
initcond = '{}'
);
+--------------------------------------------------------------------------------
+-- Roles
+--------------------------------------------------------------------------------
+
+-- Valid account roles
+CREATE TABLE roles (
+ role_id integer PRIMARY KEY, -- Role identifier
+ name text UNIQUE NOT NULL -- Role symbolic name
+) WITH OIDS;
+INSERT INTO roles (role_id, name) VALUES (10, 'admin');
+INSERT INTO roles (role_id, name) VALUES (20, 'pi');
+INSERT INTO roles (role_id, name) VALUES (30, 'user');
+INSERT INTO roles (role_id, name) VALUES (40, 'tech');
+
+--------------------------------------------------------------------------------
+-- The building block for attaching tags
+--------------------------------------------------------------------------------
+CREATE TABLE tag_types (
+
+ tag_type_id serial PRIMARY KEY, -- ID
+ tagname text UNIQUE NOT NULL, -- Tag Name
+ description text, -- Optional Description
+ min_role_id integer REFERENCES roles DEFAULT 10, -- set minimal role required
+ category text NOT NULL DEFAULT 'general' -- Free text for grouping tags together
+) WITH OIDS;
+
--------------------------------------------------------------------------------
-- Accounts
--------------------------------------------------------------------------------
) WITH OIDS;
CREATE INDEX persons_email_idx ON persons (email);
+--------------------------------------------------------------------------------
+-- person tags
+--------------------------------------------------------------------------------
+CREATE TABLE person_tag (
+ person_tag_id serial PRIMARY KEY, -- ID
+ person_id integer REFERENCES persons NOT NULL, -- person id
+ tag_type_id integer REFERENCES tag_types, -- tag type id
+ value text -- value attached
+) WITH OIDS;
+
+CREATE OR REPLACE VIEW person_tags AS
+SELECT person_id,
+array_accum(person_tag_id) AS person_tag_ids
+FROM person_tag
+GROUP BY person_id;
+
+CREATE OR REPLACE VIEW view_person_tags AS
+SELECT
+person_tag.person_tag_id,
+person_tag.person_id,
+persons.email,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+tag_types.min_role_id,
+person_tag.value
+FROM person_tag
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN persons USING (person_id);
+
--------------------------------------------------------------------------------
-- Sites
--------------------------------------------------------------------------------
FROM person_site
GROUP BY site_id;
+--------------------------------------------------------------------------------
+-- site tags
+--------------------------------------------------------------------------------
+
+CREATE TABLE site_tag (
+ site_tag_id serial PRIMARY KEY, -- ID
+ site_id integer REFERENCES sites NOT NULL, -- site id
+ tag_type_id integer REFERENCES tag_types, -- tag type id
+ value text -- value attached
+) WITH OIDS;
+
+CREATE OR REPLACE VIEW site_tags AS
+SELECT site_id,
+array_accum(site_tag_id) AS site_tag_ids
+FROM site_tag
+GROUP BY site_id;
+
+CREATE OR REPLACE VIEW view_site_tags AS
+SELECT
+site_tag.site_tag_id,
+site_tag.site_id,
+sites.login_base,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+tag_types.min_role_id,
+site_tag.value
+FROM site_tag
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN sites USING (site_id);
+
--------------------------------------------------------------------------------
-- Mailing Addresses
--------------------------------------------------------------------------------
-- Account roles
--------------------------------------------------------------------------------
--- Valid account roles
-CREATE TABLE roles (
- role_id integer PRIMARY KEY, -- Role identifier
- name text UNIQUE NOT NULL -- Role symbolic name
-) WITH OIDS;
-INSERT INTO roles (role_id, name) VALUES (10, 'admin');
-INSERT INTO roles (role_id, name) VALUES (20, 'pi');
-INSERT INTO roles (role_id, name) VALUES (30, 'user');
-INSERT INTO roles (role_id, name) VALUES (40, 'tech');
-
CREATE TABLE person_role (
person_id integer REFERENCES persons NOT NULL, -- Account identifier
role_id integer REFERENCES roles NOT NULL, -- Role identifier
node_type text PRIMARY KEY
) WITH OIDS;
INSERT INTO node_types (node_type) VALUES ('regular');
+-- old dummynet stuff, to be removed
INSERT INTO node_types (node_type) VALUES ('dummynet');
-- Nodes
version text, -- Boot CD version string updated by Boot Manager
ssh_rsa_key text, -- SSH host key updated by Boot Manager
key text, -- Node key generated when boot file is downloaded
- verified boolean NOT NULL DEFAULT false, -- whether or not the node & pcu are verified
- extrainfo text,
+ verified boolean NOT NULL DEFAULT false, -- whether or not the node & pcu are verified
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
--------------------------------------------------------------------------------
-- node tags
--------------------------------------------------------------------------------
-CREATE TABLE tag_types (
-
- tag_type_id serial PRIMARY KEY, -- ID
- tagname text UNIQUE NOT NULL, -- Tag Name
- description text, -- Optional Description
- min_role_id integer REFERENCES roles DEFAULT 10, -- set minimal role required
- category text NOT NULL DEFAULT 'general' -- Free text for grouping tags together
-) WITH OIDS;
CREATE TABLE node_tag (
node_tag_id serial PRIMARY KEY, -- ID
-- Slice/sliver attributes
CREATE TABLE slice_tag (
- slice_tag_id serial PRIMARY KEY, -- Slice attribute identifier
+ slice_tag_id serial PRIMARY KEY, -- Slice attribute identifier
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
node_id integer REFERENCES nodes, -- Sliver attribute if set
nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set
-- Peers
CREATE TABLE peers (
peer_id serial PRIMARY KEY, -- Peer identifier
- peername text UNIQUE NOT NULL, -- Peer name
+ peername text NOT NULL, -- Peer name
peer_url text NOT NULL, -- (HTTPS) URL of the peer PLCAPI interface
cacert text, -- (SSL) Public certificate of peer API server
key text, -- (GPG) Public key used for authentication
shortname text, -- abbreviated name for displaying foreign objects
- hrn_root text, -- root for this peer domain
+ hrn_root text, -- root for this peer domain
deleted boolean NOT NULL DEFAULT false
) WITH OIDS;
CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false;
COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles,
COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids,
COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids,
-COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids
+COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids,
+COALESCE((SELECT person_tag_ids FROM person_tags WHERE person_tags.person_id = persons.person_id), '{}') AS person_tag_ids
FROM persons
LEFT JOIN peer_person USING (person_id);
nodes.boot_nonce,
nodes.version,
nodes.verified,
-nodes.extrainfo,
nodes.ssh_rsa_key,
nodes.key,
CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids,
COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids,
COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids,
-COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids
+COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids,
+COALESCE((SELECT site_tag_ids FROM site_tags WHERE site_tags.site_id = sites.site_id), '{}') AS site_tag_ids
FROM sites
LEFT JOIN peer_site USING (site_id);