X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=5478836479779bfcca8e4ebe33d865568f523f9e;hb=8aba8e322df1a83cdc39134b7baf29669f65100f;hp=a4ec8917df8b335cf9dc243205ccd38289d4d1de;hpb=f5cd72e35593c96b9996ab4e49674cccc7525c48;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index a4ec891..5478836 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'; @@ -26,9 +23,16 @@ SET client_encoding = 'UNICODE'; CREATE TABLE plc_db_version ( version integer NOT NULL, 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 @@ -36,13 +40,40 @@ INSERT INTO plc_db_version (version, subversion) VALUES (5, 0); -- Like MySQL GROUP_CONCAT(), this function aggregates values into a -- PostgreSQL array. -CREATE AGGREGATE array_accum ( +CREATE AGGREGATE array_accum(anycompatible) ( sfunc = array_append, - basetype = anyelement, - stype = anyarray, + stype = anycompatiblearray, initcond = '{}' ); +-------------------------------------------------------------------------------- +-- Roles +-------------------------------------------------------------------------------- + +-- Valid account roles +CREATE TABLE roles ( + role_id integer PRIMARY KEY, -- Role identifier + name text UNIQUE NOT NULL -- Role symbolic name +); +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 +); + -------------------------------------------------------------------------------- -- Accounts -------------------------------------------------------------------------------- @@ -70,9 +101,40 @@ CREATE TABLE persons ( -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -) 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 +); + +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 -------------------------------------------------------------------------------- @@ -99,7 +161,7 @@ CREATE TABLE sites ( -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -) WITH OIDS; +); CREATE INDEX sites_login_base_idx ON sites (login_base); -- Account site membership @@ -132,6 +194,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 +); + +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 -------------------------------------------------------------------------------- @@ -140,7 +234,7 @@ CREATE TABLE address_types ( address_type_id serial PRIMARY KEY, -- Address type identifier name text UNIQUE NOT NULL, -- Address type description text -- Address type description -) WITH OIDS; +); -- Multi-rows insertion "insert .. values (row1), (row2)" is not supported by pgsql-8.1 -- 'Billing' Used to be 'Site' @@ -158,14 +252,14 @@ CREATE TABLE addresses ( state text NOT NULL, -- State or province postalcode text NOT NULL, -- Postal code country text NOT NULL -- Country -) WITH OIDS; +); -- Each mailing address can be one of several types CREATE TABLE address_address_type ( address_id integer REFERENCES addresses NOT NULL, -- Address identifier address_type_id integer REFERENCES address_types NOT NULL, -- Address type PRIMARY KEY (address_id, address_type_id) -) WITH OIDS; +); CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id); CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id); @@ -181,7 +275,7 @@ CREATE TABLE site_address ( site_id integer REFERENCES sites NOT NULL, -- Site identifier address_id integer REFERENCES addresses NOT NULL, -- Address identifier PRIMARY KEY (site_id, address_id) -) WITH OIDS; +); CREATE INDEX site_address_site_id_idx ON site_address (site_id); CREATE INDEX site_address_address_id_idx ON site_address (address_id); @@ -198,7 +292,7 @@ GROUP BY site_id; -- Valid key types CREATE TABLE key_types ( key_type text PRIMARY KEY -- Key type -) WITH OIDS; +); INSERT INTO key_types (key_type) VALUES ('ssh'); -- Authentication keys @@ -207,13 +301,13 @@ CREATE TABLE keys ( key_type text REFERENCES key_types NOT NULL, -- Key type key text NOT NULL, -- Key material is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted -) WITH OIDS; +); -- Account authentication key(s) CREATE TABLE person_key ( key_id integer REFERENCES keys PRIMARY KEY, -- Key identifier person_id integer REFERENCES persons NOT NULL -- Account identifier -) WITH OIDS; +); CREATE INDEX person_key_person_id_idx ON person_key (person_id); CREATE OR REPLACE VIEW person_keys AS @@ -226,21 +320,11 @@ 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 PRIMARY KEY (person_id, role_id) -) WITH OIDS; +); CREATE INDEX person_role_person_id_idx ON person_role (person_id); -- Account roles @@ -259,7 +343,7 @@ GROUP BY person_id; -- Valid node boot states (Nodes.py expect max length to be 20) CREATE TABLE boot_states ( boot_state text PRIMARY KEY -) 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 ('reinstall'); @@ -267,7 +351,7 @@ 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'); @@ -276,8 +360,9 @@ 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 @@ -288,9 +373,9 @@ CREATE TABLE nodes ( DEFAULT 'regular', hostname text NOT NULL, -- Node hostname - site_id integer REFERENCES sites NOT NULL, -- At which site + site_id integer REFERENCES sites NOT NULL, -- At which site boot_state text REFERENCES boot_states NOT NULL -- Node boot state - DEFAULT 'reinstall', + DEFAULT 'reinstall', run_level text REFERENCES run_levels DEFAULT NULL, -- Node Run Level deleted boolean NOT NULL DEFAULT false, -- Is deleted @@ -300,13 +385,16 @@ 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_contact timestamp without time zone -) WITH OIDS; + 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 +); CREATE INDEX nodes_hostname_idx ON nodes (hostname); CREATE INDEX nodes_site_id_idx ON nodes (site_id); @@ -321,21 +409,13 @@ 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 node_id integer REFERENCES nodes NOT NULL, -- node id tag_type_id integer REFERENCES tag_types, -- tag type id value text -- value attached -) WITH OIDS; +); -------------------------------------------------------------------------------- -- (network) interfaces @@ -344,13 +424,13 @@ CREATE TABLE node_tag ( -- Valid network addressing schemes CREATE TABLE network_types ( type text PRIMARY KEY -- Addressing scheme -) WITH OIDS; +); INSERT INTO network_types (type) VALUES ('ipv4'); -- Valid network configuration methods CREATE TABLE network_methods ( method text PRIMARY KEY -- Configuration method -) WITH OIDS; +); INSERT INTO network_methods (method) VALUES ('static'); INSERT INTO network_methods (method) VALUES ('dhcp'); @@ -378,8 +458,9 @@ 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 -) WITH OIDS; + hostname text, -- Hostname of this interface + last_updated timestamp without time zone -- When the interface was last updated +); CREATE INDEX interfaces_node_id_idx ON interfaces (node_id); -- Ordered by primary interface first @@ -404,9 +485,9 @@ CREATE TABLE interface_tag ( interface_id integer REFERENCES interfaces NOT NULL,-- the interface this applies to tag_type_id integer REFERENCES tag_types NOT NULL, -- the setting type value text -- value attached -) WITH OIDS; +); -CREATE OR REPLACE VIEW interface_tags AS +CREATE OR REPLACE VIEW interface_tags AS SELECT interface_id, array_accum(interface_tag_id) AS interface_tag_ids FROM interface_tag @@ -444,6 +525,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; @@ -456,10 +538,10 @@ CREATE TABLE ilink ( src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface value text -- optional value on the link -) WITH OIDS; +); CREATE OR REPLACE VIEW view_ilinks AS -SELECT * FROM tag_types +SELECT * FROM tag_types INNER JOIN ilink USING (tag_type_id); -- xxx TODO : expose to view_interfaces the set of ilinks a given interface is part of @@ -474,19 +556,19 @@ INNER JOIN ilink USING (tag_type_id); -- Node groups CREATE TABLE nodegroups ( nodegroup_id serial PRIMARY KEY, -- Group identifier - groupname text UNIQUE NOT NULL, -- Group name + groupname text UNIQUE NOT NULL, -- Group name tag_type_id integer REFERENCES tag_types, -- node is in nodegroup if it has this tag defined -- can be null, make management faster & easier value text -- with this value attached -) WITH OIDS; +); -- xxx - first rough implem. similar to former semantics but might be slow CREATE OR REPLACE VIEW nodegroup_node AS -SELECT nodegroup_id, node_id -FROM tag_types -JOIN node_tag -USING (tag_type_id) -JOIN nodegroups +SELECT nodegroup_id, node_id +FROM tag_types +JOIN node_tag +USING (tag_type_id) +JOIN nodegroups USING (tag_type_id,value); CREATE OR REPLACE VIEW nodegroup_nodes AS @@ -520,7 +602,7 @@ CREATE TABLE conf_files ( error_cmd text, -- Shell command to execute if any error occurs ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged -) WITH OIDS; +); CREATE TABLE conf_file_node ( conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier @@ -574,7 +656,7 @@ CREATE TABLE pcus ( -- Mandatory pcu_id serial PRIMARY KEY, -- PCU identifier site_id integer REFERENCES sites NOT NULL, -- Site identifier - hostname text, -- Hostname, not necessarily unique + hostname text, -- Hostname, not necessarily unique -- (multiple logical sites could use the same PCU) ip text NOT NULL, -- IP, not necessarily unique @@ -583,8 +665,9 @@ 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); CREATE OR REPLACE VIEW site_pcus AS @@ -623,7 +706,7 @@ GROUP BY pcu_id; CREATE TABLE slice_instantiations ( instantiation text PRIMARY KEY -) WITH OIDS; +); INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated @@ -636,7 +719,7 @@ CREATE TABLE slices ( name text NOT NULL, -- Slice name instantiation text REFERENCES slice_instantiations -- Slice state, e.g. plc-instantiated - NOT NULL DEFAULT 'plc-instantiated', + NOT NULL DEFAULT 'plc-instantiated', url text, -- Project URL description text, -- Project description @@ -644,12 +727,12 @@ CREATE TABLE slices ( creator_person_id integer REFERENCES persons, -- Creator created timestamp without time zone NOT NULL -- Creation date - DEFAULT CURRENT_TIMESTAMP, + DEFAULT CURRENT_TIMESTAMP, expires timestamp without time zone NOT NULL -- Expiration date - DEFAULT CURRENT_TIMESTAMP + '2 weeks', + DEFAULT CURRENT_TIMESTAMP + '2 weeks', is_deleted boolean NOT NULL DEFAULT false -) WITH OIDS; +); CREATE INDEX slices_site_id_idx ON slices (site_id); CREATE INDEX slices_name_idx ON slices (name); @@ -658,7 +741,7 @@ CREATE TABLE slice_node ( slice_id integer REFERENCES slices NOT NULL, -- Slice identifier node_id integer REFERENCES nodes NOT NULL, -- Node identifier PRIMARY KEY (slice_id, node_id) -) WITH OIDS; +); CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id); CREATE INDEX slice_node_node_id_idx ON slice_node (node_id); @@ -693,7 +776,7 @@ CREATE TABLE slice_person ( slice_id integer REFERENCES slices NOT NULL, -- Slice identifier person_id integer REFERENCES persons NOT NULL, -- Account identifier PRIMARY KEY (slice_id, person_id) -) WITH OIDS; +); CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id); CREATE INDEX slice_person_person_id_idx ON slice_person (person_id); @@ -719,7 +802,7 @@ CREATE TABLE node_slice_whitelist ( node_id integer REFERENCES nodes NOT NULL, -- Node id of whitelist slice_id integer REFERENCES slices NOT NULL, -- Slice id thats allowd on this node PRIMARY KEY (node_id, slice_id) -) WITH OIDS; +); CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id); CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id); @@ -736,13 +819,13 @@ 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 tag_type_id integer REFERENCES tag_types NOT NULL, -- Attribute type identifier value text -) WITH OIDS; +); CREATE INDEX slice_tag_slice_id_idx ON slice_tag (slice_id); CREATE INDEX slice_tag_node_id_idx ON slice_tag (node_id); CREATE INDEX slice_tag_nodegroup_id_idx ON slice_tag (nodegroup_id); @@ -756,9 +839,9 @@ 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); @@ -774,9 +857,9 @@ 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; CREATE INDEX peers_shortname_idx ON peers (shortname) WHERE deleted IS false; @@ -786,7 +869,7 @@ CREATE TABLE peer_site ( peer_id integer REFERENCES peers NOT NULL, -- Peer identifier peer_site_id integer NOT NULL, -- Foreign site identifier at peer UNIQUE (peer_id, peer_site_id) -- The same foreign site should not be cached twice -) WITH OIDS; +); CREATE INDEX peer_site_peer_id_idx ON peers (peer_id); CREATE OR REPLACE VIEW peer_sites AS @@ -801,7 +884,7 @@ CREATE TABLE peer_person ( peer_id integer REFERENCES peers NOT NULL, -- Peer identifier peer_person_id integer NOT NULL, -- Foreign user identifier at peer UNIQUE (peer_id, peer_person_id) -- The same foreign user should not be cached twice -) WITH OIDS; +); CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id); CREATE OR REPLACE VIEW peer_persons AS @@ -816,7 +899,7 @@ CREATE TABLE peer_key ( peer_id integer REFERENCES peers NOT NULL, -- Peer identifier peer_key_id integer NOT NULL, -- Foreign key identifier at peer UNIQUE (peer_id, peer_key_id) -- The same foreign key should not be cached twice -) WITH OIDS; +); CREATE INDEX peer_key_peer_id_idx ON peer_key (peer_id); CREATE OR REPLACE VIEW peer_keys AS @@ -831,7 +914,7 @@ CREATE TABLE peer_node ( peer_id integer REFERENCES peers NOT NULL, -- Peer identifier peer_node_id integer NOT NULL, -- Foreign node identifier UNIQUE (peer_id, peer_node_id) -- The same foreign node should not be cached twice -) WITH OIDS; +); CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id); CREATE OR REPLACE VIEW peer_nodes AS @@ -846,7 +929,7 @@ CREATE TABLE peer_slice ( peer_id integer REFERENCES peers NOT NULL, -- Peer identifier peer_slice_id integer NOT NULL, -- Slice identifier at peer UNIQUE (peer_id, peer_slice_id) -- The same foreign slice should not be cached twice -) WITH OIDS; +); CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id); CREATE OR REPLACE VIEW peer_slices AS @@ -864,7 +947,7 @@ GROUP BY peer_id; CREATE TABLE sessions ( session_id text PRIMARY KEY, -- Session identifier expires timestamp without time zone -) WITH OIDS; +); -- People can have multiple sessions CREATE TABLE person_session ( @@ -872,7 +955,7 @@ CREATE TABLE person_session ( session_id text REFERENCES sessions NOT NULL, -- Session identifier PRIMARY KEY (person_id, session_id), UNIQUE (session_id) -- Sessions are unique -) WITH OIDS; +); CREATE INDEX person_session_person_id_idx ON person_session (person_id); -- Nodes can have only one session @@ -881,7 +964,7 @@ CREATE TABLE node_session ( session_id text REFERENCES sessions NOT NULL, -- Session identifier UNIQUE (node_id), -- Nodes can have only one session UNIQUE (session_id) -- Sessions are unique -) WITH OIDS; +); ------------------------------------------------------------------------------- -- PCU Types @@ -890,7 +973,7 @@ CREATE TABLE pcu_types ( pcu_type_id serial PRIMARY KEY, model text NOT NULL , -- PCU model name name text -- Full PCU model name -) WITH OIDS; +); CREATE INDEX pcu_types_model_idx ON pcu_types (model); CREATE TABLE pcu_protocol_type ( @@ -899,7 +982,7 @@ CREATE TABLE pcu_protocol_type ( port integer NOT NULL, -- PCU port protocol text NOT NULL, -- Protocol supported boolean NOT NULL DEFAULT True -- Does PLC support -) WITH OIDS; +); CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id); @@ -918,7 +1001,7 @@ CREATE TABLE messages ( subject text, -- Message summary template text, -- Message template enabled bool NOT NULL DEFAULT true -- Whether message is enabled -) WITH OIDS; +); -------------------------------------------------------------------------------- -- Events @@ -937,14 +1020,14 @@ CREATE TABLE events ( runtime float DEFAULT 0, -- Event run time time timestamp without time zone NOT NULL -- Event timestamp DEFAULT CURRENT_TIMESTAMP -) WITH OIDS; +); -- Database object(s) that may have been affected by a particular event CREATE TABLE event_object ( event_id integer REFERENCES events NOT NULL, -- Event identifier object_id integer NOT NULL, -- Object identifier object_type text NOT NULL Default 'Unknown' -- What type of object is this event affecting -) WITH OIDS; +); CREATE INDEX event_object_event_id_idx ON event_object (event_id); CREATE INDEX event_object_object_id_idx ON event_object (object_id); CREATE INDEX event_object_object_type_idx ON event_object (object_type); @@ -965,7 +1048,7 @@ pcu_types.pcu_type_id, pcu_types.model, pcu_types.name, COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types - WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') + WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids FROM pcu_types; @@ -986,7 +1069,7 @@ COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = ev COALESCE((SELECT object_types FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_types FROM events; -CREATE OR REPLACE VIEW view_event_objects AS +CREATE OR REPLACE VIEW view_event_objects AS SELECT events.event_id, events.person_id, @@ -1025,14 +1108,15 @@ 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); -------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_peers AS -SELECT -peers.*, +SELECT +peers.*, COALESCE((SELECT site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS site_ids, COALESCE((SELECT peer_site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS peer_site_ids, COALESCE((SELECT person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS person_ids, @@ -1063,7 +1147,7 @@ tag_types.description, tag_types.category, tag_types.min_role_id, node_tag.value -FROM node_tag +FROM node_tag INNER JOIN tag_types USING (tag_type_id) INNER JOIN nodes USING (node_id); @@ -1084,32 +1168,35 @@ 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_contact) AS bigint) AS last_contact, +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 - WHERE node_interfaces.node_id = nodes.node_id), '{}') +COALESCE((SELECT interface_ids FROM node_interfaces + WHERE node_interfaces.node_id = nodes.node_id), '{}') AS interface_ids, -COALESCE((SELECT nodegroup_ids FROM node_nodegroups - WHERE node_nodegroups.node_id = nodes.node_id), '{}') +COALESCE((SELECT nodegroup_ids FROM node_nodegroups + WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids, -COALESCE((SELECT slice_ids FROM node_slices - WHERE node_slices.node_id = nodes.node_id), '{}') +COALESCE((SELECT slice_ids FROM node_slices + WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids, -COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist - WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') +COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist + WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') AS slice_ids_whitelist, -COALESCE((SELECT pcu_ids FROM node_pcus - WHERE node_pcus.node_id = nodes.node_id), '{}') +COALESCE((SELECT pcu_ids FROM node_pcus + WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids, COALESCE((SELECT ports FROM node_pcus - WHERE node_pcus.node_id = nodes.node_id), '{}') + WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports, COALESCE((SELECT conf_file_ids FROM node_conf_files - WHERE node_conf_files.node_id = nodes.node_id), '{}') + WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids, -COALESCE((SELECT node_tag_ids FROM node_tags - WHERE node_tags.node_id = nodes.node_id), '{}') +COALESCE((SELECT node_tag_ids FROM node_tags + WHERE node_tags.node_id = nodes.node_id), '{}') AS node_tag_ids, node_session.session_id AS session FROM nodes @@ -1121,11 +1208,11 @@ CREATE OR REPLACE VIEW view_nodegroups AS SELECT nodegroups.*, tag_types.tagname, -COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files - WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') +COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files + WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') AS conf_file_ids, -COALESCE((SELECT node_ids FROM nodegroup_nodes - WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') +COALESCE((SELECT node_ids FROM nodegroup_nodes + WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') AS node_ids FROM nodegroups INNER JOIN tag_types USING (tag_type_id); @@ -1133,22 +1220,32 @@ FROM nodegroups INNER JOIN tag_types USING (tag_type_id); CREATE OR REPLACE VIEW view_conf_files AS SELECT conf_files.*, -COALESCE((SELECT node_ids FROM conf_file_nodes - WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') +COALESCE((SELECT node_ids FROM conf_file_nodes + WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids, -COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups - WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') +COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups + WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids FROM conf_files; -------------------------------------------------------------------------------- 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 @@ -1173,7 +1270,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);