-- Nodes
--------------------------------------------------------------------------------
--- Valid node boot states
+-- 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 ('install');
INSERT INTO boot_states (boot_state) 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');
+INSERT INTO node_types (node_type) VALUES ('dummynet');
+
-- Nodes
CREATE TABLE nodes (
-- Mandatory
node_id serial PRIMARY KEY, -- Node identifier
+ node_type text REFERENCES node_types -- node type
+ DEFAULT 'regular',
+
hostname text NOT NULL, -- Node hostname
site_id integer REFERENCES sites NOT NULL, -- At which site
-
boot_state text REFERENCES boot_states NOT NULL -- Node boot state
DEFAULT 'install',
deleted boolean NOT NULL DEFAULT false, -- Is deleted
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
+ 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,
-nodes.hostname,
-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 tag_types USING (tag_type_id)
-INNER JOIN nodes USING (node_id);
-
--------------------------------------------------------------------------------
-- (network) interfaces
--------------------------------------------------------------------------------
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
GROUP BY node_id;
--------------------------------------------------------------------------------
--- Interface settings
+-- Interface tags (formerly known as interface settings)
--------------------------------------------------------------------------------
CREATE TABLE 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
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
+ value text -- with this value attached
) WITH OIDS;
-- xxx - first rough implem. similar to former semantics but might be slow
JOIN node_tag
USING (tag_type_id)
JOIN nodegroups
-USING (tag_type_id,tagvalue);
+USING (tag_type_id,value);
CREATE OR REPLACE VIEW nodegroup_nodes AS
SELECT nodegroup_id,
GROUP BY node_id;
--------------------------------------------------------------------------------
--- Slice attributes
+-- Slice tags (formerly known as slice attributes)
--------------------------------------------------------------------------------
-- Slice/sliver attributes
CREATE INDEX slice_tag_node_id_idx ON slice_tag (node_id);
CREATE INDEX slice_tag_nodegroup_id_idx ON slice_tag (nodegroup_id);
-CREATE OR REPLACE VIEW slice_tags AS
-SELECT slice_id,
-array_accum(slice_tag_id) AS slice_tag_ids
-FROM slice_tag
-GROUP BY slice_id;
-
--------------------------------------------------------------------------------
-- Initscripts
--------------------------------------------------------------------------------
AS pcu_protocol_type_ids
FROM pcu_types;
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_events AS
SELECT
events.event_id,
event_object.object_type
FROM events LEFT JOIN event_object USING (event_id);
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_persons AS
SELECT
persons.person_id,
FROM persons
LEFT JOIN peer_person USING (person_id);
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_peers AS
SELECT
peers.*,
COALESCE((SELECT peer_slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS peer_slice_ids
FROM peers;
+--------------------------------------------------------------------------------
+CREATE OR REPLACE VIEW node_tags AS
+SELECT node_id,
+array_accum(node_tag_id) AS node_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,
+nodes.hostname,
+tag_types.tag_type_id,
+tag_types.tagname,
+tag_types.description,
+tag_types.category,
+tag_types.min_role_id,
+node_tag.value
+FROM node_tag
+INNER JOIN tag_types USING (tag_type_id)
+INNER JOIN nodes USING (node_id);
+
CREATE OR REPLACE VIEW view_nodes AS
SELECT
nodes.node_id,
+nodes.node_type,
nodes.hostname,
nodes.site_id,
nodes.boot_state,
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
+COALESCE((SELECT node_tag_ids FROM node_tags
WHERE node_tags.node_id = nodes.node_id), '{}')
-AS tag_ids,
+AS node_tag_ids,
node_session.session_id AS session
FROM nodes
LEFT JOIN peer_node USING (node_id)
LEFT JOIN node_session USING (node_id);
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_nodegroups AS
SELECT
nodegroups.*,
AS node_ids
FROM nodegroups INNER JOIN tag_types USING (tag_type_id);
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_conf_files AS
SELECT
conf_files.*,
AS nodegroup_ids
FROM conf_files;
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_pcus AS
SELECT
pcus.*,
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
sites.site_id,
FROM sites
LEFT JOIN peer_site USING (site_id);
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_addresses AS
SELECT
addresses.*,
COALESCE((SELECT address_types FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_types
FROM addresses;
+--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_keys AS
SELECT
keys.*,
LEFT JOIN person_key USING (key_id)
LEFT JOIN peer_key USING (key_id);
+--------------------------------------------------------------------------------
+CREATE OR REPLACE VIEW slice_tags AS
+SELECT slice_id,
+array_accum(slice_tag_id) AS slice_tag_ids
+FROM slice_tag
+GROUP BY slice_id;
+
CREATE OR REPLACE VIEW view_slices AS
SELECT
slices.slice_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
SELECT
sessions.session_id,
git://git.onelab.eu / plcapi.git / blobdiff