--
-- Copyright (C) 2006 The Trustees of Princeton University
--
+-- NOTE: this file was first created for version 4.3, the filename might be confusing
+--
-- $Id$
+-- $URL$
--
SET client_encoding = 'UNICODE';
) 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
+) 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');
+INSERT INTO run_levels (run_level) VALUES ('reinstall');
-- Known node types (Nodes.py expect max length to be 20)
CREATE TABLE node_types (
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',
+ 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,
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
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
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
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 (
--------------------------------------------------------------------------------
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;
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,
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)
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