From: Thierry Parmentelat Date: Wed, 23 Nov 2022 08:06:44 +0000 (+0100) Subject: trailing spaces in sql schema X-Git-Url: http://git.onelab.eu/?p=plcapi.git;a=commitdiff_plain;h=1d3540bd2ece27d91a2ec5843628c5fa38a25024 trailing spaces in sql schema --- diff --git a/planetlab5.sql b/planetlab5.sql index 8dab501..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/ @@ -131,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); @@ -222,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); @@ -373,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 @@ -393,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); @@ -487,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 @@ -541,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 @@ -556,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 @@ -564,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 @@ -656,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 @@ -719,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 @@ -727,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 ); @@ -1048,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; @@ -1069,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, @@ -1115,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, @@ -1147,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); @@ -1168,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 @@ -1208,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); @@ -1220,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;