X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=95eb02b37d666342040a7504ff0a1fa70e8b19a7;hb=refs%2Fheads%2Fremove-xmlrpc;hp=1ae9aef2f810d84c113ed5db1def93154665e515;hpb=3ae5fadbc03bfaa1eb1d21c9521ddf797ba37d31;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index 1ae9aef..95eb02b 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -12,9 +12,6 @@ -- -- NOTE: this file was first created for version 4.3, the filename might be confusing -- --- $Id$ --- $URL$ --- SET client_encoding = 'UNICODE'; @@ -50,6 +47,20 @@ 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 -------------------------------------------------------------------------------- @@ -58,6 +69,8 @@ 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; @@ -97,7 +110,7 @@ CREATE INDEX persons_email_idx ON persons (email); -------------------------------------------------------------------------------- CREATE TABLE person_tag ( person_tag_id serial PRIMARY KEY, -- ID - person_id integer REFERENCES persons NOT NULL, -- person 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; @@ -308,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 @@ -383,11 +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 + 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); @@ -453,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); @@ -519,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; @@ -658,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); @@ -811,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 @@ -831,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); @@ -849,7 +858,7 @@ CREATE TABLE peers ( 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; @@ -1161,6 +1170,10 @@ 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 @@ -1218,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