SET client_encoding = 'UNICODE';
+--------------------------------------------------------------------------------
+-- Version
+--------------------------------------------------------------------------------
+
+-- Database version
+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);
+
--------------------------------------------------------------------------------
-- Aggregates and store procedures
--------------------------------------------------------------------------------
initcond = '{}'
);
---------------------------------------------------------------------------------
--- Version
---------------------------------------------------------------------------------
-
--- Database version
-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 (4, 9);
-
--------------------------------------------------------------------------------
-- Accounts
--------------------------------------------------------------------------------
CREATE INDEX person_site_site_id_idx ON person_site (site_id);
-- Ordered by primary site first
-CREATE VIEW person_site_ordered AS
+CREATE OR REPLACE VIEW person_site_ordered AS
SELECT person_id, site_id
FROM person_site
ORDER BY is_primary DESC;
-- Sites that each person is a member of
-CREATE VIEW person_sites AS
+CREATE OR REPLACE VIEW person_sites AS
SELECT person_id,
array_accum(site_id) AS site_ids
FROM person_site_ordered
GROUP BY person_id;
-- Accounts at each site
-CREATE VIEW site_persons AS
+CREATE OR REPLACE VIEW site_persons AS
SELECT site_id,
array_accum(person_id) AS person_ids
FROM person_site
description text -- Address type description
) WITH OIDS;
-INSERT INTO address_types (name) VALUES ('Personal');
-INSERT INTO address_types (name) VALUES ('Shipping');
--- XXX Used to be Site
-INSERT INTO address_types (name) VALUES ('Billing');
+-- 'Billing' Used to be 'Site'
+INSERT INTO address_types (name) VALUES ('Personal'), ('Shipping'), ('Billing');
-- Mailing addresses
CREATE TABLE addresses (
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);
-CREATE VIEW address_address_types AS
+CREATE OR REPLACE VIEW address_address_types AS
SELECT address_id,
array_accum(address_type_id) AS address_type_ids,
array_accum(address_types.name) AS address_types
CREATE INDEX site_address_site_id_idx ON site_address (site_id);
CREATE INDEX site_address_address_id_idx ON site_address (address_id);
-CREATE VIEW site_addresses AS
+CREATE OR REPLACE VIEW site_addresses AS
SELECT site_id,
array_accum(address_id) AS address_ids
FROM site_address
) WITH OIDS;
CREATE INDEX person_key_person_id_idx ON person_key (person_id);
-CREATE VIEW person_keys AS
+CREATE OR REPLACE VIEW person_keys AS
SELECT person_id,
array_accum(key_id) AS key_ids
FROM person_key
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');
+INSERT INTO roles (role_id, name) VALUES (10, 'admin'), (20, 'pi'), (30, 'user'), (40, 'tech');
CREATE TABLE person_role (
person_id integer REFERENCES persons NOT NULL, -- Account identifier
CREATE INDEX person_role_person_id_idx ON person_role (person_id);
-- Account roles
-CREATE VIEW person_roles AS
+CREATE OR REPLACE VIEW person_roles AS
SELECT person_id,
array_accum(role_id) AS role_ids,
array_accum(roles.name) AS roles
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 ('dbg');
-INSERT INTO boot_states (boot_state) VALUES ('diag');
-INSERT INTO boot_states (boot_state) VALUES ('disable');
-INSERT INTO boot_states (boot_state) VALUES ('inst');
-INSERT INTO boot_states (boot_state) VALUES ('rins');
-INSERT INTO boot_states (boot_state) VALUES ('new');
+INSERT INTO boot_states (boot_state)
+ VALUES ('boot'), ('dbg'), ('diag'), ('disable'), ('inst'), ('rins'), ('new');
-- Nodes
CREATE TABLE nodes (
CREATE INDEX nodes_site_id_idx ON nodes (site_id);
-- Nodes at each site
-CREATE VIEW site_nodes AS
+CREATE OR REPLACE VIEW site_nodes AS
SELECT site_id,
array_accum(node_id) AS node_ids
FROM nodes
WHERE deleted IS false
GROUP BY site_id;
+--------------------------------------------------------------------------------
+-- node tags
+--------------------------------------------------------------------------------
+CREATE TABLE node_tag_types (
+
+ node_tag_type_id serial PRIMARY KEY, -- ID
+ name text UNIQUE NOT NULL, -- Tag Name
+ description text, -- Optional Description
+ category text NOT NULL, -- Free text for grouping tags together
+ min_role_id integer REFERENCES roles -- set minimal role required
+) WITH OIDS;
+
+CREATE TABLE node_tag (
+ node_tag_id serial PRIMARY KEY, -- ID
+ node_id integer REFERENCES nodes NOT NULL, -- node id
+ node_tag_type_id integer REFERENCES node_tag_types,
+ -- tag type id
+ value text -- value attached
+) WITH OIDS;
+
+CREATE OR REPLACE VIEW node_tags AS
+SELECT node_id,
+array_accum(node_tag_id) AS tag_ids
+FROM node_tag
+GROUP BY node_id;
+
+CREATE OR REPLACE VIEW view_node_tags AS
+SELECT
+node_tag.node_tag_id,
+node_tag.node_id,
+node_tag_types.node_tag_type_id,
+node_tag_types.name,
+node_tag_types.description,
+node_tag_types.category,
+node_tag_types.min_role_id,
+node_tag.value
+FROM node_tag
+INNER JOIN node_tag_types USING (node_tag_type_id);
+
--------------------------------------------------------------------------------
-- Node groups
--------------------------------------------------------------------------------
-- Node groups
CREATE TABLE nodegroups (
- nodegroup_id serial PRIMARY KEY, -- Group identifier
- name text UNIQUE NOT NULL, -- Group name
- description text -- Group description
+ nodegroup_id serial PRIMARY KEY, -- Group identifier
+ name text UNIQUE NOT NULL, -- Group name
+ node_tag_type_id integer REFERENCES node_tag_types, -- node is in nodegroup if it has this tag defined
+ value text -- with value 'value'
) WITH OIDS;
--- Node group membership
-CREATE TABLE nodegroup_node (
- nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Group identifier
- node_id integer REFERENCES nodes NOT NULL, -- Node identifier
- PRIMARY KEY (nodegroup_id, node_id)
-) WITH OIDS;
-CREATE INDEX nodegroup_node_nodegroup_id_idx ON nodegroup_node (nodegroup_id);
-CREATE INDEX nodegroup_node_node_id_idx ON nodegroup_node (node_id);
--- Nodes in each node group
-CREATE VIEW nodegroup_nodes AS
+CREATE OR REPLACE VIEW nodegroup_node AS
+SELECT nodegroup_id, node_id
+FROM node_tag_types
+JOIN node_tag
+USING (node_tag_type_id)
+JOIN nodegroups
+USING (node_tag_type_id,value);
+
+CREATE OR REPLACE VIEW nodegroup_nodes AS
SELECT nodegroup_id,
array_accum(node_id) AS node_ids
FROM nodegroup_node
GROUP BY nodegroup_id;
-- Node groups that each node is a member of
-CREATE VIEW node_nodegroups AS
+CREATE OR REPLACE VIEW node_nodegroups AS
SELECT node_id,
array_accum(nodegroup_id) AS nodegroup_ids
FROM nodegroup_node
CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id);
-- Nodes linked to each configuration file
-CREATE VIEW conf_file_nodes AS
+CREATE OR REPLACE VIEW conf_file_nodes AS
SELECT conf_file_id,
array_accum(node_id) AS node_ids
FROM conf_file_node
GROUP BY conf_file_id;
-- Configuration files linked to each node
-CREATE VIEW node_conf_files AS
+CREATE OR REPLACE VIEW node_conf_files AS
SELECT node_id,
array_accum(conf_file_id) AS conf_file_ids
FROM conf_file_node
CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id);
-- Node groups linked to each configuration file
-CREATE VIEW conf_file_nodegroups AS
+CREATE OR REPLACE VIEW conf_file_nodegroups AS
SELECT conf_file_id,
array_accum(nodegroup_id) AS nodegroup_ids
FROM conf_file_nodegroup
GROUP BY conf_file_id;
-- Configuration files linked to each node group
-CREATE VIEW nodegroup_conf_files AS
+CREATE OR REPLACE VIEW nodegroup_conf_files AS
SELECT nodegroup_id,
array_accum(conf_file_id) AS conf_file_ids
FROM conf_file_nodegroup
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 ('proxy');
-INSERT INTO network_methods (method) VALUES ('tap');
-INSERT INTO network_methods (method) VALUES ('ipmi');
-INSERT INTO network_methods (method) VALUES ('unknown');
+INSERT INTO network_methods (method) VALUES
+ ('static'), ('dhcp'), ('proxy'), ('tap'), ('ipmi'), ('unknown');
-- Node network interfaces
-CREATE TABLE nodenetworks (
+CREATE TABLE interfaces (
-- Mandatory
- nodenetwork_id serial PRIMARY KEY, -- Network interface identifier
+ interface_id serial PRIMARY KEY, -- Network interface identifier
node_id integer REFERENCES nodes NOT NULL, -- Which node
is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node
type text REFERENCES network_types NOT NULL, -- Addressing scheme
bwlimit integer, -- Bandwidth limit in bps
hostname text -- Hostname of this interface
) WITH OIDS;
-CREATE INDEX nodenetworks_node_id_idx ON nodenetworks (node_id);
+CREATE INDEX interfaces_node_id_idx ON interfaces (node_id);
-- Ordered by primary interface first
-CREATE VIEW nodenetworks_ordered AS
-SELECT node_id, nodenetwork_id
-FROM nodenetworks
+CREATE OR REPLACE VIEW interfaces_ordered AS
+SELECT node_id, interface_id
+FROM interfaces
ORDER BY is_primary DESC;
-- Network interfaces on each node
-CREATE VIEW node_nodenetworks AS
+CREATE OR REPLACE VIEW node_interfaces AS
SELECT node_id,
-array_accum(nodenetwork_id) AS nodenetwork_ids
-FROM nodenetworks_ordered
+array_accum(interface_id) AS interface_ids
+FROM interfaces_ordered
GROUP BY node_id;
--------------------------------------------------------------------------------
--- Nodenetwork setting types and nodenetworks settings
+-- Interface setting types and interfaces settings
--------------------------------------------------------------------------------
-CREATE TABLE nodenetwork_setting_types (
- nodenetwork_setting_type_id serial PRIMARY KEY,
+CREATE TABLE interface_setting_types (
+ interface_setting_type_id serial PRIMARY KEY,
-- Setting Type Identifier
name text UNIQUE NOT NULL, -- Setting Name
description text, -- Optional Description
category text NOT NULL, -- Category, e.g. Wifi, or whatever
- min_role_id integer references roles -- If set, minimal role required
+ min_role_id integer REFERENCES roles -- If set, minimal role required
) WITH OIDS;
-CREATE TABLE nodenetwork_setting (
- nodenetwork_setting_id serial PRIMARY KEY, -- Nodenetwork Setting Identifier
- nodenetwork_id integer REFERENCES nodenetworks NOT NULL,
- -- the nodenetwork this applies to
- nodenetwork_setting_type_id integer REFERENCES nodenetwork_setting_types NOT NULL,
+CREATE TABLE interface_setting (
+ interface_setting_id serial PRIMARY KEY, -- Interface Setting Identifier
+ interface_id integer REFERENCES interfaces NOT NULL,
+ -- the interface this applies to
+ interface_setting_type_id integer REFERENCES interface_setting_types NOT NULL,
-- the setting type
- value text
+ value text -- value attached
) WITH OIDS;
-CREATE OR REPLACE VIEW nodenetwork_settings AS
-SELECT nodenetwork_id,
-array_accum(nodenetwork_setting_id) AS nodenetwork_setting_ids
-FROM nodenetwork_setting
-GROUP BY nodenetwork_id;
+CREATE OR REPLACE VIEW interface_settings AS
+SELECT interface_id,
+array_accum(interface_setting_id) AS setting_ids
+FROM interface_setting
+GROUP BY interface_id;
-CREATE OR REPLACE VIEW view_nodenetwork_settings AS
+CREATE OR REPLACE VIEW view_interface_settings AS
SELECT
-nodenetwork_setting.nodenetwork_setting_id,
-nodenetwork_setting.nodenetwork_id,
-nodenetwork_setting_types.nodenetwork_setting_type_id,
-nodenetwork_setting_types.name,
-nodenetwork_setting_types.description,
-nodenetwork_setting_types.category,
-nodenetwork_setting_types.min_role_id,
-nodenetwork_setting.value
-FROM nodenetwork_setting
-INNER JOIN nodenetwork_setting_types USING (nodenetwork_setting_type_id);
-
-CREATE OR REPLACE VIEW view_nodenetworks AS
+interface_setting.interface_setting_id,
+interface_setting.interface_id,
+interface_setting_types.interface_setting_type_id,
+interface_setting_types.name,
+interface_setting_types.description,
+interface_setting_types.category,
+interface_setting_types.min_role_id,
+interface_setting.value
+FROM interface_setting
+INNER JOIN interface_setting_types USING (interface_setting_type_id);
+
+CREATE OR REPLACE VIEW view_interfaces AS
SELECT
-nodenetworks.nodenetwork_id,
-nodenetworks.node_id,
-nodenetworks.is_primary,
-nodenetworks.type,
-nodenetworks.method,
-nodenetworks.ip,
-nodenetworks.mac,
-nodenetworks.gateway,
-nodenetworks.network,
-nodenetworks.broadcast,
-nodenetworks.netmask,
-nodenetworks.dns1,
-nodenetworks.dns2,
-nodenetworks.bwlimit,
-nodenetworks.hostname,
-COALESCE((SELECT nodenetwork_setting_ids FROM nodenetwork_settings WHERE nodenetwork_settings.nodenetwork_id = nodenetworks.nodenetwork_id), '{}') AS nodenetwork_setting_ids
-FROM nodenetworks;
+interfaces.interface_id,
+interfaces.node_id,
+interfaces.is_primary,
+interfaces.type,
+interfaces.method,
+interfaces.ip,
+interfaces.mac,
+interfaces.gateway,
+interfaces.network,
+interfaces.broadcast,
+interfaces.netmask,
+interfaces.dns1,
+interfaces.dns2,
+interfaces.bwlimit,
+interfaces.hostname,
+COALESCE((SELECT setting_ids FROM interface_settings WHERE interface_settings.interface_id = interfaces.interface_id), '{}') AS setting_ids
+FROM interfaces;
--------------------------------------------------------------------------------
-- Power control units (PCUs)
) WITH OIDS;
CREATE INDEX pcus_site_id_idx ON pcus (site_id);
-CREATE VIEW site_pcus AS
+CREATE OR REPLACE VIEW site_pcus AS
SELECT site_id,
array_accum(pcu_id) AS pcu_ids
FROM pcus
CREATE INDEX pcu_node_pcu_id_idx ON pcu_node (pcu_id);
CREATE INDEX pcu_node_node_id_idx ON pcu_node (node_id);
-CREATE VIEW node_pcus AS
+CREATE OR REPLACE VIEW node_pcus AS
SELECT node_id,
array_accum(pcu_id) AS pcu_ids,
array_accum(port) AS ports
FROM pcu_node
GROUP BY node_id;
-CREATE VIEW pcu_nodes AS
+CREATE OR REPLACE VIEW pcu_nodes AS
SELECT pcu_id,
array_accum(node_id) AS node_ids,
array_accum(port) AS ports
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
-INSERT INTO slice_instantiations (instantiation) VALUES ('nm-controller'); -- NM Controller
+INSERT INTO slice_instantiations (instantiation) VALUES
+ ('not-instantiated'), -- Placeholder slice
+ ('plc-instantiated'), -- Instantiated by Node Manager
+ ('delegated'), -- Manually instantiated
+ ('nm-controller'); -- NM Controller
-- Slices
CREATE TABLE slices (
CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
-- Synonym for slice_node
-CREATE VIEW slivers AS
+CREATE OR REPLACE VIEW slivers AS
SELECT * FROM slice_node;
-- Nodes in each slice
-CREATE VIEW slice_nodes AS
+CREATE OR REPLACE VIEW slice_nodes AS
SELECT slice_id,
array_accum(node_id) AS node_ids
FROM slice_node
GROUP BY slice_id;
-- Slices on each node
-CREATE VIEW node_slices AS
+CREATE OR REPLACE VIEW node_slices AS
SELECT node_id,
array_accum(slice_id) AS slice_ids
FROM slice_node
GROUP BY node_id;
-- Slices at each site
-CREATE VIEW site_slices AS
+CREATE OR REPLACE VIEW site_slices AS
SELECT site_id,
array_accum(slice_id) AS slice_ids
FROM slices
CREATE INDEX slice_person_person_id_idx ON slice_person (person_id);
-- Members of the slice
-CREATE VIEW slice_persons AS
+CREATE OR REPLACE VIEW slice_persons AS
SELECT slice_id,
array_accum(person_id) AS person_ids
FROM slice_person
GROUP BY slice_id;
-- Slices of which each person is a member
-CREATE VIEW person_slices AS
+CREATE OR REPLACE VIEW person_slices AS
SELECT person_id,
array_accum(slice_id) AS slice_ids
FROM slice_person
CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id);
-- Slices on each node
-CREATE VIEW node_slices_whitelist AS
+CREATE OR REPLACE VIEW node_slices_whitelist AS
SELECT node_id,
array_accum(slice_id) AS slice_ids_whitelist
FROM node_slice_whitelist
CREATE INDEX slice_attribute_node_id_idx ON slice_attribute (node_id);
CREATE INDEX slice_attribute_nodegroup_id_idx ON slice_attribute (nodegroup_id);
-CREATE VIEW slice_attributes AS
+CREATE OR REPLACE VIEW slice_attributes AS
SELECT slice_id,
array_accum(slice_attribute_id) AS slice_attribute_ids
FROM slice_attribute
) WITH OIDS;
CREATE INDEX peer_site_peer_id_idx ON peers (peer_id);
-CREATE VIEW peer_sites AS
+CREATE OR REPLACE VIEW peer_sites AS
SELECT peer_id,
array_accum(site_id) AS site_ids,
array_accum(peer_site_id) AS peer_site_ids
) WITH OIDS;
CREATE INDEX peer_person_peer_id_idx ON peer_person (peer_id);
-CREATE VIEW peer_persons AS
+CREATE OR REPLACE VIEW peer_persons AS
SELECT peer_id,
array_accum(person_id) AS person_ids,
array_accum(peer_person_id) AS peer_person_ids
) WITH OIDS;
CREATE INDEX peer_key_peer_id_idx ON peer_key (peer_id);
-CREATE VIEW peer_keys AS
+CREATE OR REPLACE VIEW peer_keys AS
SELECT peer_id,
array_accum(key_id) AS key_ids,
array_accum(peer_key_id) AS peer_key_ids
) WITH OIDS;
CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id);
-CREATE VIEW peer_nodes AS
+CREATE OR REPLACE VIEW peer_nodes AS
SELECT peer_id,
array_accum(node_id) AS node_ids,
array_accum(peer_node_id) AS peer_node_ids
) WITH OIDS;
CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id);
-CREATE VIEW peer_slices AS
+CREATE OR REPLACE VIEW peer_slices AS
SELECT peer_id,
array_accum(slice_id) AS slice_ids,
array_accum(peer_slice_id) AS peer_slice_ids
CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
peer_node.peer_id,
peer_node.peer_node_id,
-COALESCE((SELECT nodenetwork_ids FROM node_nodenetworks WHERE node_nodenetworks.node_id = nodes.node_id), '{}') AS nodenetwork_ids,
+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), '{}') AS nodegroup_ids,
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), '{}') AS slice_ids_whitelist,
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), '{}') AS ports,
COALESCE((SELECT conf_file_ids FROM node_conf_files 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,
node_session.session_id AS session
FROM nodes
LEFT JOIN peer_node USING (node_id)
VALUES
('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true);
-INSERT INTO person_role (person_id, role_id) VALUES (1, 10);
-INSERT INTO person_role (person_id, role_id) VALUES (1, 20);
-INSERT INTO person_role (person_id, role_id) VALUES (1, 30);
-INSERT INTO person_role (person_id, role_id) VALUES (1, 40);
+INSERT INTO person_role (person_id, role_id)
+ VALUES (1, 10), (1, 20), (1, 30), (1, 40);
INSERT INTO sites
(login_base, name, abbreviated_name, max_slices)
git://git.onelab.eu / plcapi.git / blobdiff