X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=aa79b56506646e34186271eb7dd9b5e1f55c23c3;hb=2780ce3f3e5636f346809f764aaf51a33818c7f2;hp=14632827f1f54e9ac279f94eec9df5466d13f058;hpb=2aca52e3a7a985c61228d536228868d7ccb14e79;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index 1463282..aa79b56 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -13,6 +13,7 @@ -- NOTE: this file was first created for version 4.3, the filename might be confusing -- -- $Id$ +-- $URL$ -- SET client_encoding = 'UNICODE'; @@ -27,7 +28,14 @@ CREATE TABLE plc_db_version ( 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 @@ -42,6 +50,32 @@ CREATE AGGREGATE array_accum ( 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 -------------------------------------------------------------------------------- @@ -72,6 +106,37 @@ CREATE TABLE persons ( ) 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 -------------------------------------------------------------------------------- @@ -131,6 +196,38 @@ array_accum(person_id) AS person_ids 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 -------------------------------------------------------------------------------- @@ -225,16 +322,6 @@ GROUP BY person_id; -- 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 @@ -277,6 +364,7 @@ CREATE TABLE node_types ( 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 @@ -300,7 +388,6 @@ CREATE TABLE nodes ( 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, -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, @@ -321,14 +408,6 @@ GROUP BY site_id; -------------------------------------------------------------------------------- -- 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 @@ -769,7 +848,7 @@ CREATE INDEX initscripts_name_idx ON initscripts (name); -- 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 @@ -1025,7 +1104,8 @@ COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = perso 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); @@ -1080,7 +1160,6 @@ nodes.model, 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, @@ -1174,7 +1253,8 @@ COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites 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);