--------------------------------------------------------------------------------
-- node tags
--------------------------------------------------------------------------------
-CREATE TABLE node_tag_types (
+CREATE TABLE tag_types (
- node_tag_type_id serial PRIMARY KEY, -- ID
+ tag_type_id serial PRIMARY KEY, -- ID
tagname text UNIQUE NOT NULL, -- Tag Name
description text, -- Optional Description
category text NOT NULL DEFAULT 'general', -- Free text for grouping tags together
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
+ tag_type_id integer REFERENCES tag_types, -- tag type id
tagvalue text -- value attached
) WITH OIDS;
node_tag.node_tag_id,
node_tag.node_id,
nodes.hostname,
-node_tag_types.node_tag_type_id,
-node_tag_types.tagname,
-node_tag_types.description,
-node_tag_types.category,
-node_tag_types.min_role_id,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+tag_types.min_role_id,
node_tag.tagvalue
FROM node_tag
-INNER JOIN node_tag_types USING (node_tag_type_id)
+INNER JOIN tag_types USING (tag_type_id)
INNER JOIN nodes USING (node_id);
--------------------------------------------------------------------------------
--- Node groups
---------------------------------------------------------------------------------
-
--- Node groups
-CREATE TABLE nodegroups (
- nodegroup_id serial PRIMARY KEY, -- Group identifier
- groupname 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
- tagvalue text NOT NULL -- with this value attached
-) WITH OIDS;
-
--- xxx - first rough implem
-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,tagvalue);
-
-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 OR REPLACE VIEW node_nodegroups AS
-SELECT node_id,
-array_accum(nodegroup_id) AS nodegroup_ids
-FROM nodegroup_node
-GROUP BY node_id;
-
---------------------------------------------------------------------------------
--- Node configuration files
---------------------------------------------------------------------------------
-
-CREATE TABLE conf_files (
- conf_file_id serial PRIMARY KEY, -- Configuration file identifier
- enabled bool NOT NULL DEFAULT true, -- Configuration file is active
- source text NOT NULL, -- Relative path on the boot server
- -- where file can be downloaded
- dest text NOT NULL, -- Absolute path where file should be installed
- file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions
- file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner
- file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner
- preinstall_cmd text, -- Shell command to execute prior to installing
- postinstall_cmd text, -- Shell command to execute after installing
- 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
- node_id integer REFERENCES nodes NOT NULL, -- Node identifier
- PRIMARY KEY (conf_file_id, node_id)
-);
-CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id);
-CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id);
-
--- Nodes linked to each configuration file
-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 OR REPLACE VIEW node_conf_files AS
-SELECT node_id,
-array_accum(conf_file_id) AS conf_file_ids
-FROM conf_file_node
-GROUP BY node_id;
-
-CREATE TABLE conf_file_nodegroup (
- conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
- nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier
- PRIMARY KEY (conf_file_id, nodegroup_id)
-);
-CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id);
-CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id);
-
--- Node groups linked to each configuration file
-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 OR REPLACE VIEW nodegroup_conf_files AS
-SELECT nodegroup_id,
-array_accum(conf_file_id) AS conf_file_ids
-FROM conf_file_nodegroup
-GROUP BY nodegroup_id;
-
---------------------------------------------------------------------------------
--- Node network interfaces
+-- (network) interfaces
--------------------------------------------------------------------------------
-- Valid network addressing schemes
GROUP BY node_id;
--------------------------------------------------------------------------------
--- Interface setting types and interfaces settings
+-- Interface settings
--------------------------------------------------------------------------------
-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 DEFAULT 'general', -- Free text for grouping, e.g. Wifi, or whatever
- min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimal role required
-) WITH OIDS;
-
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
+ tag_type_id integer REFERENCES tag_types NOT NULL, -- the setting type
value text -- value attached
) WITH OIDS;
CREATE OR REPLACE VIEW interface_settings AS
SELECT interface_id,
-array_accum(interface_setting_id) AS setting_ids
+array_accum(interface_setting_id) AS interface_setting_ids
FROM interface_setting
GROUP BY interface_id;
SELECT
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,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+tag_types.min_role_id,
interface_setting.value
FROM interface_setting
-INNER JOIN interface_setting_types USING (interface_setting_type_id);
+INNER JOIN tag_types USING (tag_type_id);
CREATE OR REPLACE VIEW view_interfaces AS
SELECT
interfaces.dns2,
interfaces.bwlimit,
interfaces.hostname,
-COALESCE((SELECT setting_ids FROM interface_settings WHERE interface_settings.interface_id = interfaces.interface_id), '{}') AS setting_ids
+COALESCE((SELECT interface_setting_ids FROM interface_settings WHERE interface_settings.interface_id = interfaces.interface_id), '{}') AS interface_setting_ids
FROM interfaces;
+--------------------------------------------------------------------------------
+-- ilinks : links between interfaces
+--------------------------------------------------------------------------------
+CREATE TABLE ilink (
+ ilink_id serial PRIMARY KEY, -- id
+ tag_type_id integer REFERENCES tag_types, -- id of the tag type
+ 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
+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;
+
+--------------------------------------------------------------------------------
+-- Node groups
+--------------------------------------------------------------------------------
+
+-- Node groups
+CREATE TABLE nodegroups (
+ nodegroup_id serial PRIMARY KEY, -- Group identifier
+ groupname text UNIQUE NOT NULL, -- Group name
+ tag_type_id integer REFERENCES tag_types, -- node is in nodegroup if it has this tag defined
+ tagvalue text NOT NULL -- 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
+USING (tag_type_id,tagvalue);
+
+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 OR REPLACE VIEW node_nodegroups AS
+SELECT node_id,
+array_accum(nodegroup_id) AS nodegroup_ids
+FROM nodegroup_node
+GROUP BY node_id;
+
+--------------------------------------------------------------------------------
+-- Node configuration files
+--------------------------------------------------------------------------------
+
+CREATE TABLE conf_files (
+ conf_file_id serial PRIMARY KEY, -- Configuration file identifier
+ enabled bool NOT NULL DEFAULT true, -- Configuration file is active
+ source text NOT NULL, -- Relative path on the boot server
+ -- where file can be downloaded
+ dest text NOT NULL, -- Absolute path where file should be installed
+ file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions
+ file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner
+ file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner
+ preinstall_cmd text, -- Shell command to execute prior to installing
+ postinstall_cmd text, -- Shell command to execute after installing
+ 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
+ node_id integer REFERENCES nodes NOT NULL, -- Node identifier
+ PRIMARY KEY (conf_file_id, node_id)
+);
+CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id);
+CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id);
+
+-- Nodes linked to each configuration file
+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 OR REPLACE VIEW node_conf_files AS
+SELECT node_id,
+array_accum(conf_file_id) AS conf_file_ids
+FROM conf_file_node
+GROUP BY node_id;
+
+CREATE TABLE conf_file_nodegroup (
+ conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
+ nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier
+ PRIMARY KEY (conf_file_id, nodegroup_id)
+);
+CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id);
+CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id);
+
+-- Node groups linked to each configuration file
+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 OR REPLACE VIEW nodegroup_conf_files AS
+SELECT nodegroup_id,
+array_accum(conf_file_id) AS conf_file_ids
+FROM conf_file_nodegroup
+GROUP BY nodegroup_id;
+
--------------------------------------------------------------------------------
-- Power control units (PCUs)
--------------------------------------------------------------------------------
-- Slice attributes
--------------------------------------------------------------------------------
--- Slice attribute types
-CREATE TABLE slice_attribute_types (
- attribute_type_id serial PRIMARY KEY, -- Attribute type identifier
- name text UNIQUE NOT NULL, -- Attribute name
- description text, -- Attribute description
- min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can
- -- set or change this attribute
-) WITH OIDS;
-
-- Slice/sliver attributes
CREATE TABLE slice_attribute (
slice_attribute_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
- attribute_type_id integer -- Attribute type identifier
- REFERENCES slice_attribute_types NOT NULL,
+ tag_type_id integer REFERENCES tag_types NOT NULL, -- Attribute type identifier
value text
) WITH OIDS;
CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id);
CREATE OR REPLACE VIEW view_nodegroups AS
SELECT
nodegroups.*,
-node_tag_types.tagname,
+tag_types.tagname,
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), '{}')
AS node_ids
-FROM nodegroups INNER JOIN node_tag_types USING (node_tag_type_id);
+FROM nodegroups INNER JOIN tag_types USING (tag_type_id);
CREATE OR REPLACE VIEW view_conf_files AS
SELECT
slice_attribute.slice_id,
slice_attribute.node_id,
slice_attribute.nodegroup_id,
-slice_attribute_types.attribute_type_id,
-slice_attribute_types.name,
-slice_attribute_types.description,
-slice_attribute_types.min_role_id,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+tag_types.min_role_id,
slice_attribute.value
FROM slice_attribute
-INNER JOIN slice_attribute_types USING (attribute_type_id);
+INNER JOIN tag_types USING (tag_type_id);
CREATE OR REPLACE VIEW view_sessions AS
SELECT
git://git.onelab.eu / plcapi.git / blobdiff