X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=f4c4ef95e99a18d1b2c7993acb59ca76038f287f;hb=405f2d547603094beb95ef7ab222f776959598d3;hp=382589fb3fa41f631d39a4cc5c72f2932b55e565;hpb=a7f0a8c621447d357b9f2b42cfa2513211aeb751;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index 382589f..f4c4ef9 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -1,14 +1,17 @@ -- -- PlanetLab Central database schema --- Version 4, PostgreSQL +-- Version 5, PostgreSQL -- -- Aaron Klingaman -- Reid Moran -- Mark Huang -- Tony Mack +-- Thierry Parmentelat -- -- Copyright (C) 2006 The Trustees of Princeton University -- +-- NOTE: this file was first created for version 4.3, the filename might be confusing +-- -- $Id$ -- @@ -138,8 +141,11 @@ CREATE TABLE address_types ( 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' -INSERT INTO address_types (name) VALUES ('Personal'), ('Shipping'), ('Billing'); +INSERT INTO address_types (name) VALUES ('Personal'); +INSERT INTO address_types (name) VALUES ('Shipping'); +INSERT INTO address_types (name) VALUES ('Billing'); -- Mailing addresses CREATE TABLE addresses ( @@ -224,7 +230,10 @@ 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'), (20, 'pi'), (30, 'user'), (40, 'tech'); +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 @@ -246,22 +255,42 @@ GROUP BY person_id; -- 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 ('boot'), ('dbg'), ('diag'), ('disable'), ('inst'), ('rins'), ('new'); +INSERT INTO boot_states (boot_state) VALUES ('boot'); +INSERT INTO boot_states (boot_state) VALUES ('safeboot'); +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 ( + 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 'inst', + DEFAULT 'reinstall', + run_level text REFERENCES run_levels DEFAULT NULL, -- Node Run Level deleted boolean NOT NULL DEFAULT false, -- Is deleted -- Optional @@ -270,6 +299,7 @@ CREATE TABLE nodes ( 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, @@ -290,42 +320,151 @@ GROUP BY site_id; -------------------------------------------------------------------------------- -- 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 - min_role_id integer REFERENCES roles DEFAULT 10 -- set minimal role required + 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 - node_tag_type_id integer REFERENCES node_tag_types, -- tag type id - tagvalue text -- value attached + tag_type_id integer REFERENCES tag_types, -- tag type id + value text -- value attached ) WITH OIDS; -CREATE OR REPLACE VIEW node_tags AS +-------------------------------------------------------------------------------- +-- (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 ('proxy'); +INSERT INTO network_methods (method) VALUES ('tap'); +INSERT INTO network_methods (method) VALUES ('ipmi'); +INSERT INTO network_methods (method) VALUES ('unknown'); + +-- Network interfaces +CREATE TABLE interfaces ( + -- Mandatory + 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 + method text REFERENCES network_methods NOT NULL, -- Configuration method + + -- Optional, depending on type and method + ip text, -- IP address + mac text, -- MAC address + gateway text, -- Default gateway address + network text, -- Network address + broadcast text, -- Network broadcast address + netmask text, -- Network mask + dns1 text, -- Primary DNS server + dns2 text, -- Secondary DNS server + bwlimit integer, -- Bandwidth limit in bps + hostname text -- Hostname of this interface +) WITH OIDS; +CREATE INDEX interfaces_node_id_idx ON interfaces (node_id); + +-- Ordered by primary interface first +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 OR REPLACE VIEW node_interfaces AS SELECT node_id, -array_accum(node_tag_id) AS tag_ids -FROM node_tag +array_accum(interface_id) AS interface_ids +FROM interfaces_ordered GROUP BY node_id; -CREATE OR REPLACE VIEW view_node_tags AS +-------------------------------------------------------------------------------- +-- Interface tags (formerly known as interface settings) +-------------------------------------------------------------------------------- + +CREATE TABLE interface_tag ( + interface_tag_id serial PRIMARY KEY, -- Interface Setting Identifier + 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 +SELECT interface_id, +array_accum(interface_tag_id) AS interface_tag_ids +FROM interface_tag +GROUP BY interface_id; + +CREATE OR REPLACE VIEW view_interface_tags AS SELECT -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, -node_tag.tagvalue -FROM node_tag -INNER JOIN node_tag_types USING (node_tag_type_id) -INNER JOIN nodes USING (node_id); +interface_tag.interface_tag_id, +interface_tag.interface_id, +interfaces.ip, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +interface_tag.value +FROM interface_tag +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN interfaces USING (interface_id); + +CREATE OR REPLACE VIEW view_interfaces AS +SELECT +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 interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_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); + +-- 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 @@ -333,20 +472,21 @@ INNER JOIN nodes USING (node_id); -- 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 + 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 + -- can be null, make management faster & easier + value text -- with this value attached ) WITH OIDS; --- xxx - first rough implem +-- 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 node_tag_types +FROM tag_types JOIN node_tag -USING (node_tag_type_id) +USING (tag_type_id) JOIN nodegroups -USING (node_tag_type_id,tagvalue); +USING (tag_type_id,value); CREATE OR REPLACE VIEW nodegroup_nodes AS SELECT nodegroup_id, @@ -425,118 +565,6 @@ array_accum(conf_file_id) AS conf_file_ids FROM conf_file_nodegroup GROUP BY nodegroup_id; --------------------------------------------------------------------------------- --- Node 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'), ('dhcp'), ('proxy'), ('tap'), ('ipmi'), ('unknown'); - --- Node network interfaces -CREATE TABLE interfaces ( - -- Mandatory - 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 - method text REFERENCES network_methods NOT NULL, -- Configuration method - - -- Optional, depending on type and method - ip text, -- IP address - mac text, -- MAC address - gateway text, -- Default gateway address - network text, -- Network address - broadcast text, -- Network broadcast address - netmask text, -- Network mask - dns1 text, -- Primary DNS server - dns2 text, -- Secondary DNS server - bwlimit integer, -- Bandwidth limit in bps - hostname text -- Hostname of this interface -) WITH OIDS; -CREATE INDEX interfaces_node_id_idx ON interfaces (node_id); - --- Ordered by primary interface first -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 OR REPLACE VIEW node_interfaces AS -SELECT node_id, -array_accum(interface_id) AS interface_ids -FROM interfaces_ordered -GROUP BY node_id; - --------------------------------------------------------------------------------- --- Interface setting types and interfaces 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 - value text -- value attached -) WITH OIDS; - -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_interface_settings AS -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, -interface_setting.value -FROM interface_setting -INNER JOIN interface_setting_types USING (interface_setting_type_id); - -CREATE OR REPLACE VIEW view_interfaces AS -SELECT -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) -------------------------------------------------------------------------------- @@ -595,11 +623,10 @@ GROUP BY pcu_id; CREATE TABLE slice_instantiations ( instantiation text PRIMARY KEY ) WITH OIDS; -INSERT INTO slice_instantiations (instantiation) VALUES - ('not-instantiated'), -- Placeholder slice - ('plc-instantiated'), -- Instantiated by Node Manager - ('delegated'), -- Manually instantiated - ('nm-controller'); -- NM Controller +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 -- Slices CREATE TABLE slices ( @@ -703,37 +730,21 @@ FROM node_slice_whitelist GROUP BY node_id; -------------------------------------------------------------------------------- --- Slice attributes +-- Slice tags (formerly known as 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 +CREATE TABLE slice_tag ( + 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 - 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 INDEX slice_attribute_node_id_idx ON slice_attribute (node_id); -CREATE INDEX slice_attribute_nodegroup_id_idx ON slice_attribute (nodegroup_id); - -CREATE OR REPLACE VIEW slice_attributes AS -SELECT slice_id, -array_accum(slice_attribute_id) AS slice_attribute_ids -FROM slice_attribute -GROUP BY slice_id; +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); -------------------------------------------------------------------------------- -- Initscripts @@ -761,9 +772,12 @@ CREATE TABLE peers ( 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 ( @@ -954,6 +968,7 @@ COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types AS pcu_protocol_type_ids FROM pcu_types; +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_events AS SELECT events.event_id, @@ -985,6 +1000,7 @@ event_object.object_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, @@ -1012,6 +1028,7 @@ COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = pe FROM persons LEFT JOIN peer_person USING (person_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_peers AS SELECT peers.*, @@ -1027,16 +1044,41 @@ COALESCE((SELECT slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.pe 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, +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, @@ -1065,26 +1107,28 @@ 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 +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.*, -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 conf_files.*, @@ -1096,6 +1140,7 @@ COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups AS nodegroup_ids FROM conf_files; +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_pcus AS SELECT pcus.*, @@ -1103,6 +1148,7 @@ COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), 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, @@ -1130,6 +1176,7 @@ COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id) FROM sites LEFT JOIN peer_site USING (site_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_addresses AS SELECT addresses.*, @@ -1137,6 +1184,7 @@ COALESCE((SELECT address_type_ids FROM address_address_types WHERE address_addre 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.*, @@ -1147,6 +1195,13 @@ FROM 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, @@ -1164,24 +1219,28 @@ peer_slice.peer_id, peer_slice.peer_slice_id, COALESCE((SELECT node_ids FROM slice_nodes WHERE slice_nodes.slice_id = slices.slice_id), '{}') AS node_ids, COALESCE((SELECT person_ids FROM slice_persons WHERE slice_persons.slice_id = slices.slice_id), '{}') AS person_ids, -COALESCE((SELECT slice_attribute_ids FROM slice_attributes WHERE slice_attributes.slice_id = slices.slice_id), '{}') AS slice_attribute_ids +COALESCE((SELECT slice_tag_ids FROM slice_tags WHERE slice_tags.slice_id = slices.slice_id), '{}') AS slice_tag_ids FROM slices LEFT JOIN peer_slice USING (slice_id); -CREATE OR REPLACE VIEW view_slice_attributes AS +CREATE OR REPLACE VIEW view_slice_tags AS SELECT -slice_attribute.slice_attribute_id, -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, -slice_attribute.value -FROM slice_attribute -INNER JOIN slice_attribute_types USING (attribute_type_id); +slice_tag.slice_tag_id, +slice_tag.slice_id, +slice_tag.node_id, +slice_tag.nodegroup_id, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +slice_tag.value, +slices.name +FROM slice_tag +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, @@ -1196,15 +1255,13 @@ LEFT JOIN node_session USING (session_id); -- Built-in maintenance account and default site -------------------------------------------------------------------------------- -INSERT INTO persons -(first_name, last_name, email, password, enabled) -VALUES -('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true); +INSERT INTO persons (first_name, last_name, email, password, enabled) +VALUES ('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true); -INSERT INTO person_role (person_id, role_id) - VALUES (1, 10), (1, 20), (1, 30), (1, 40); +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 sites -(login_base, name, abbreviated_name, max_slices) -VALUES -('pl', 'PlanetLab Central', 'PLC', 100); +INSERT INTO sites (login_base, name, abbreviated_name, max_slices) +VALUES ('pl', 'PlanetLab Central', 'PLC', 100);