X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=95eb02b37d666342040a7504ff0a1fa70e8b19a7;hb=1e4558f1e8eaec5f69f3832a8599d0dfa1531f00;hp=fccc8ac73822104b5d5364c90cb9abf2ff9c8522;hpb=7c328b743dd874fef3a85b6ac48a36ad8ef17590;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index fccc8ac..95eb02b 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -10,7 +10,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id$ +-- NOTE: this file was first created for version 4.3, the filename might be confusing -- SET client_encoding = 'UNICODE'; @@ -25,7 +25,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 @@ -40,6 +47,34 @@ 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 +-- this is deprecated -- see migrations/104* +-- starting with subversion 104, a tag type has a SET OF roles attached to it + 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 -------------------------------------------------------------------------------- @@ -70,6 +105,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 -------------------------------------------------------------------------------- @@ -129,6 +195,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 -------------------------------------------------------------------------------- @@ -223,16 +321,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 @@ -259,16 +347,23 @@ CREATE TABLE boot_states ( ) WITH OIDS; INSERT INTO boot_states (boot_state) VALUES ('boot'); INSERT INTO boot_states (boot_state) VALUES ('safeboot'); -INSERT INTO boot_states (boot_state) VALUES ('failboot'); -INSERT INTO boot_states (boot_state) VALUES ('disabled'); -INSERT INTO boot_states (boot_state) VALUES ('install'); INSERT INTO boot_states (boot_state) VALUES ('reinstall'); +INSERT INTO boot_states (boot_state) VALUES ('disabled'); + +CREATE TABLE run_levels ( + run_level text PRIMARY KEY +) WITH OIDS; +INSERT INTO run_levels (run_level) VALUES ('boot'); +INSERT INTO run_levels (run_level) VALUES ('safeboot'); +INSERT INTO run_levels (run_level) VALUES ('failboot'); +INSERT INTO run_levels (run_level) VALUES ('reinstall'); -- Known node types (Nodes.py expect max length to be 20) 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 @@ -281,7 +376,8 @@ CREATE TABLE nodes ( hostname text NOT NULL, -- Node hostname site_id integer REFERENCES sites NOT NULL, -- At which site boot_state text REFERENCES boot_states NOT NULL -- Node boot state - DEFAULT 'install', + DEFAULT 'reinstall', + run_level text REFERENCES run_levels DEFAULT NULL, -- Node Run Level deleted boolean NOT NULL DEFAULT false, -- Is deleted -- Optional @@ -290,10 +386,14 @@ CREATE TABLE 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 -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_download timestamp without time zone, + last_pcu_reboot timestamp without time zone, + last_pcu_confirmation timestamp without time zone, last_contact timestamp without time zone ) WITH OIDS; CREATE INDEX nodes_hostname_idx ON nodes (hostname); @@ -310,14 +410,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 @@ -367,7 +459,8 @@ CREATE TABLE interfaces ( dns1 text, -- Primary DNS server dns2 text, -- Secondary DNS server bwlimit integer, -- Bandwidth limit in bps - hostname text -- Hostname of this interface + hostname text, -- Hostname of this interface + last_updated timestamp without time zone -- When the interface was last updated ) WITH OIDS; CREATE INDEX interfaces_node_id_idx ON interfaces (node_id); @@ -433,6 +526,7 @@ interfaces.dns1, interfaces.dns2, interfaces.bwlimit, interfaces.hostname, +CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated, COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids FROM interfaces; @@ -451,23 +545,10 @@ CREATE OR REPLACE VIEW view_ilinks AS SELECT * FROM tag_types INNER JOIN ilink USING (tag_type_id); --- expose node_ids ??? --- -- cannot mention the same table twice in a join ? --- -- CREATE OR REPLACE VIEW ilink_src_node AS --- SELECT --- ilink.tag_type_id, --- ilink.src_interface_id, --- interfaces.node_id AS src_node_id, --- ilink.dst_interface_id --- FROM ilink --- INNER JOIN interfaces ON ilink.src_interface_id = interfaces.interface_id; --- --- CREATE OR REPLACE VIEW ilink_nodes AS --- SELECT --- ilink_src_node.*, --- interfaces.node_id as dst_node_id --- FROM ilink_src_node --- INNER JOIN interfaces ON ilink_src_node.dst_interface_id = interfaces.interface_id; +-- xxx TODO : expose to view_interfaces the set of ilinks a given interface is part of +-- this is needed for properly deleting these ilinks when an interface gets deleted +-- as this is not done yet, it prevents DeleteInterface, thus DeleteNode, thus DeleteSite +-- from working correctly when an iLink is set -------------------------------------------------------------------------------- -- Node groups @@ -585,6 +666,7 @@ CREATE TABLE pcus ( username text, -- Username, if applicable "password" text, -- Password, if applicable model text, -- Model, e.g. BayTech or iPal + last_updated timestamp without time zone, notes text -- Random notes ) WITH OIDS; CREATE INDEX pcus_site_id_idx ON pcus (site_id); @@ -738,7 +820,7 @@ GROUP BY node_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 @@ -758,7 +840,7 @@ CREATE TABLE initscripts ( initscript_id serial PRIMARY KEY, -- Initscript identifier name text NOT NULL, -- Initscript name enabled bool NOT NULL DEFAULT true, -- Initscript is active - script text NOT NULL, -- Initscript body + script text NOT NULL, -- Initscript code UNIQUE (name) ) WITH OIDS; CREATE INDEX initscripts_name_idx ON initscripts (name); @@ -771,11 +853,12 @@ 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 shortname text, -- abbreviated name for displaying foreign objects + 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; @@ -1026,7 +1109,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); @@ -1075,15 +1159,21 @@ nodes.node_type, nodes.hostname, nodes.site_id, nodes.boot_state, +nodes.run_level, nodes.deleted, nodes.model, nodes.boot_nonce, nodes.version, +nodes.verified, nodes.ssh_rsa_key, nodes.key, CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created, CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated, CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact, +CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot, +CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download, +CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot, +CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation, peer_node.peer_id, peer_node.peer_node_id, COALESCE((SELECT interface_ids FROM node_interfaces @@ -1141,13 +1231,24 @@ AS nodegroup_ids FROM conf_files; -------------------------------------------------------------------------------- +DROP VIEW view_pcus; CREATE OR REPLACE VIEW view_pcus AS SELECT -pcus.*, +pcus.pcu_id, +pcus.site_id, +pcus.hostname, +pcus.ip, +pcus.protocol, +pcus.username, +pcus.password, +pcus.model, +pcus.notes, +CAST(date_part('epoch', pcus.last_updated) AS bigint) AS last_updated, COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids, COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports FROM pcus; + -------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_sites AS SELECT @@ -1172,7 +1273,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);