--
-- 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';
CREATE TABLE plc_db_version (
version integer NOT NULL,
subversion integer NOT NULL DEFAULT 0
-) WITH OIDS;
+);
+
+-- 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, 0);
+INSERT INTO plc_db_version (version, subversion) VALUES (5, 100);
--------------------------------------------------------------------------------
-- Aggregates and store procedures
-- 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
--------------------------------------------------------------------------------
-- 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
--------------------------------------------------------------------------------
-- 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
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
--------------------------------------------------------------------------------
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'
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);
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);
-- 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
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
-- 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
-- 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 ('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
+);
+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
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 'install',
+ DEFAULT 'reinstall',
+ run_level text REFERENCES run_levels DEFAULT NULL, -- Node Run Level
deleted boolean NOT NULL DEFAULT false, -- Is deleted
-- Optional
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_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);
--------------------------------------------------------------------------------
-- 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
- tagvalue text -- value attached
-) WITH OIDS;
+ value text -- value attached
+);
--------------------------------------------------------------------------------
-- (network) interfaces
-- 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');
INSERT INTO network_methods (method) VALUES ('ipmi');
INSERT INTO network_methods (method) VALUES ('unknown');
--- Node network interfaces
+-- Network interfaces
CREATE TABLE interfaces (
-- Mandatory
interface_id serial PRIMARY KEY, -- Network interface identifier
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
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
SELECT
interface_tag.interface_tag_id,
interface_tag.interface_id,
+interfaces.ip,
tag_types.tag_type_id,
tag_types.tagname,
tag_types.description,
tag_types.min_role_id,
interface_tag.value
FROM interface_tag
-INNER JOIN tag_types USING (tag_type_id);
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN interfaces USING (interface_id);
CREATE OR REPLACE VIEW view_interfaces AS
SELECT
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;
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);
--- 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
-- 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
- tagvalue text -- with this value attached
-) WITH OIDS;
+ value text -- with this value attached
+);
-- 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
-USING (tag_type_id,tagvalue);
+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
SELECT nodegroup_id,
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
-- 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
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
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
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
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);
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);
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);
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);
-- 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);
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);
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;
+CREATE INDEX peers_shortname_idx ON peers (shortname) WHERE deleted IS false;
-- Objects at each peer
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
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
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
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
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
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 (
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
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
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 (
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);
subject text, -- Message summary
template text, -- Message template
enabled bool NOT NULL DEFAULT true -- Whether message is enabled
-) WITH OIDS;
+);
--------------------------------------------------------------------------------
-- 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);
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;
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,
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,
--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW node_tags AS
SELECT node_id,
-array_accum(node_tag_id) AS tag_ids
+array_accum(node_tag_id) AS node_tag_ids
FROM node_tag
GROUP BY node_id;
tag_types.description,
tag_types.category,
tag_types.min_role_id,
-node_tag.tagvalue
-FROM node_tag
+node_tag.value
+FROM node_tag
INNER JOIN tag_types USING (tag_type_id)
INNER JOIN nodes USING (node_id);
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_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 tag_ids FROM node_tags
- WHERE node_tags.node_id = nodes.node_id), '{}')
-AS tag_ids,
+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
LEFT JOIN peer_node USING (node_id)
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);
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
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);
tag_types.description,
tag_types.category,
tag_types.min_role_id,
-slice_tag.value
+slice_tag.value,
+slices.name
FROM slice_tag
-INNER JOIN tag_types USING (tag_type_id);
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN slices USING (slice_id);
--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_sessions AS
git://git.onelab.eu / plcapi.git / blobdiff