-- 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/
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);
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);
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
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);
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
);
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
-- 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
-- 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
-- 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
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
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
);
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;
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,
--------------------------------------------------------------------------------
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,
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);
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
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);
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;