X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=5478836479779bfcca8e4ebe33d865568f523f9e;hb=d20644a48d03667bb25dc583517de06e94606c64;hp=61ff7fd86e72f10c81083cc7dde9fb6dfaa4dbb2;hpb=119346542b56f968eb5649e52a929d9937718512;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index 61ff7fd..5478836 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -27,7 +27,7 @@ CREATE TABLE plc_db_version ( -- the migration scripts do not use the major 'version' number -- so 5.0 sets subversion at 100 --- in case your database misses the site and persons tags feature, +-- in case your database misses the site and persons tags feature, -- you might wish to first upgrade to 4.3-rc16 before moving to some 5.0 -- or run the up script here -- http://svn.planet-lab.org/svn/PLCAPI/branches/4.3/migrations/ @@ -40,10 +40,9 @@ INSERT INTO plc_db_version (version, subversion) VALUES (5, 100); -- Like MySQL GROUP_CONCAT(), this function aggregates values into a -- PostgreSQL array. -CREATE AGGREGATE array_accum ( +CREATE AGGREGATE array_accum(anycompatible) ( sfunc = array_append, - basetype = anyelement, - stype = anyarray, + stype = anycompatiblearray, initcond = '{}' ); @@ -132,7 +131,7 @@ tag_types.description, tag_types.category, tag_types.min_role_id, person_tag.value -FROM person_tag +FROM person_tag INNER JOIN tag_types USING (tag_type_id) INNER JOIN persons USING (person_id); @@ -223,7 +222,7 @@ tag_types.description, tag_types.category, tag_types.min_role_id, site_tag.value -FROM site_tag +FROM site_tag INNER JOIN tag_types USING (tag_type_id) INNER JOIN sites USING (site_id); @@ -374,9 +373,9 @@ CREATE TABLE nodes ( DEFAULT 'regular', hostname text NOT NULL, -- Node hostname - site_id integer REFERENCES sites NOT NULL, -- At which site + site_id integer REFERENCES sites NOT NULL, -- At which site boot_state text REFERENCES boot_states NOT NULL -- Node boot state - DEFAULT 'reinstall', + DEFAULT 'reinstall', run_level text REFERENCES run_levels DEFAULT NULL, -- Node Run Level deleted boolean NOT NULL DEFAULT false, -- Is deleted @@ -394,7 +393,7 @@ CREATE TABLE nodes ( last_download timestamp without time zone, last_pcu_reboot timestamp without time zone, last_pcu_confirmation timestamp without time zone, - last_contact timestamp without time zone + last_contact timestamp without time zone ); CREATE INDEX nodes_hostname_idx ON nodes (hostname); CREATE INDEX nodes_site_id_idx ON nodes (site_id); @@ -488,7 +487,7 @@ CREATE TABLE interface_tag ( value text -- value attached ); -CREATE OR REPLACE VIEW interface_tags AS +CREATE OR REPLACE VIEW interface_tags AS SELECT interface_id, array_accum(interface_tag_id) AS interface_tag_ids FROM interface_tag @@ -542,7 +541,7 @@ CREATE TABLE ilink ( ); CREATE OR REPLACE VIEW view_ilinks AS -SELECT * FROM tag_types +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 @@ -557,7 +556,7 @@ INNER JOIN ilink USING (tag_type_id); -- Node groups CREATE TABLE nodegroups ( nodegroup_id serial PRIMARY KEY, -- Group identifier - groupname text UNIQUE NOT NULL, -- Group name + 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 @@ -565,11 +564,11 @@ CREATE TABLE nodegroups ( -- 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 +SELECT nodegroup_id, node_id +FROM tag_types +JOIN node_tag +USING (tag_type_id) +JOIN nodegroups USING (tag_type_id,value); CREATE OR REPLACE VIEW nodegroup_nodes AS @@ -657,7 +656,7 @@ CREATE TABLE pcus ( -- Mandatory pcu_id serial PRIMARY KEY, -- PCU identifier site_id integer REFERENCES sites NOT NULL, -- Site identifier - hostname text, -- Hostname, not necessarily unique + hostname text, -- Hostname, not necessarily unique -- (multiple logical sites could use the same PCU) ip text NOT NULL, -- IP, not necessarily unique @@ -720,7 +719,7 @@ CREATE TABLE slices ( name text NOT NULL, -- Slice name instantiation text REFERENCES slice_instantiations -- Slice state, e.g. plc-instantiated - NOT NULL DEFAULT 'plc-instantiated', + NOT NULL DEFAULT 'plc-instantiated', url text, -- Project URL description text, -- Project description @@ -728,9 +727,9 @@ CREATE TABLE slices ( creator_person_id integer REFERENCES persons, -- Creator created timestamp without time zone NOT NULL -- Creation date - DEFAULT CURRENT_TIMESTAMP, + DEFAULT CURRENT_TIMESTAMP, expires timestamp without time zone NOT NULL -- Expiration date - DEFAULT CURRENT_TIMESTAMP + '2 weeks', + DEFAULT CURRENT_TIMESTAMP + '2 weeks', is_deleted boolean NOT NULL DEFAULT false ); @@ -1049,7 +1048,7 @@ pcu_types.pcu_type_id, pcu_types.model, pcu_types.name, COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types - WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') + WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids FROM pcu_types; @@ -1070,7 +1069,7 @@ COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = ev COALESCE((SELECT object_types FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_types FROM events; -CREATE OR REPLACE VIEW view_event_objects AS +CREATE OR REPLACE VIEW view_event_objects AS SELECT events.event_id, events.person_id, @@ -1116,8 +1115,8 @@ LEFT JOIN peer_person USING (person_id); -------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_peers AS -SELECT -peers.*, +SELECT +peers.*, COALESCE((SELECT site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS site_ids, COALESCE((SELECT peer_site_ids FROM peer_sites WHERE peer_sites.peer_id = peers.peer_id), '{}') AS peer_site_ids, COALESCE((SELECT person_ids FROM peer_persons WHERE peer_persons.peer_id = peers.peer_id), '{}') AS person_ids, @@ -1148,7 +1147,7 @@ tag_types.description, tag_types.category, tag_types.min_role_id, node_tag.value -FROM node_tag +FROM node_tag INNER JOIN tag_types USING (tag_type_id) INNER JOIN nodes USING (node_id); @@ -1169,35 +1168,35 @@ nodes.ssh_rsa_key, nodes.key, CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created, CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated, -CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact, -CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download, -CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot, -CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation, +CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact, +CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download, +CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot, +CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation, peer_node.peer_id, peer_node.peer_node_id, -COALESCE((SELECT interface_ids FROM node_interfaces - WHERE node_interfaces.node_id = nodes.node_id), '{}') +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), '{}') +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), '{}') +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), '{}') +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), '{}') +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), '{}') + 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), '{}') + WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids, -COALESCE((SELECT node_tag_ids FROM node_tags - WHERE node_tags.node_id = nodes.node_id), '{}') +COALESCE((SELECT node_tag_ids FROM node_tags + WHERE node_tags.node_id = nodes.node_id), '{}') AS node_tag_ids, node_session.session_id AS session FROM nodes @@ -1209,11 +1208,11 @@ CREATE OR REPLACE VIEW view_nodegroups AS SELECT nodegroups.*, tag_types.tagname, -COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files - WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') +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), '{}') +COALESCE((SELECT node_ids FROM nodegroup_nodes + WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') AS node_ids FROM nodegroups INNER JOIN tag_types USING (tag_type_id); @@ -1221,11 +1220,11 @@ FROM nodegroups INNER JOIN tag_types USING (tag_type_id); CREATE OR REPLACE VIEW view_conf_files AS SELECT conf_files.*, -COALESCE((SELECT node_ids FROM conf_file_nodes - WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') +COALESCE((SELECT node_ids FROM conf_file_nodes + WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids, -COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups - WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') +COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups + WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids FROM conf_files;