1 -- revert migration 005
3 -- this is a rather complex example, so for next times, make sure that you
4 -- * first restore old columns or tables
5 -- * then create or replace views
6 -- * and only finally drop new columns and tables
7 -- otherwise the columns may refuse to get dropped if they are still used by views
12 ALTER TABLE events ADD object_type text NOT NULL Default 'Unknown';
14 ---------- view changes
16 -- for some reason these views require to be dropped first
17 DROP VIEW view_events;
18 DROP VIEW event_objects;
22 CREATE OR REPLACE VIEW event_objects AS
24 array_accum(object_id) AS object_ids
28 CREATE OR REPLACE VIEW view_events AS
39 CAST(date_part('epoch', events.time) AS bigint) AS time,
40 COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_ids
43 CREATE OR REPLACE VIEW view_nodes AS
55 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
56 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
58 peer_node.peer_node_id,
59 COALESCE((SELECT nodenetwork_ids FROM node_nodenetworks WHERE node_nodenetworks.node_id = nodes.node_id), '{}') AS nodenetwork_ids,
60 COALESCE((SELECT nodegroup_ids FROM node_nodegroups WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids,
61 COALESCE((SELECT slice_ids FROM node_slices WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids,
62 COALESCE((SELECT pcu_ids FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids,
63 COALESCE((SELECT ports FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports,
64 COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids,
65 node_session.session_id AS session
67 LEFT JOIN peer_node USING (node_id)
68 LEFT JOIN node_session USING (node_id);
70 CREATE OR REPLACE VIEW view_sites AS
75 sites.abbreviated_name,
84 CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created,
85 CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated,
87 peer_site.peer_site_id,
88 COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids,
89 COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids,
90 COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids,
91 COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids,
92 COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids
94 LEFT JOIN peer_site USING (site_id);
98 ALTER TABLE sites DROP COLUMN ext_consortium_id;
100 ALTER TABLE nodes DROP COLUMN last_contact;
102 DROP INDEX initscripts_name_idx;
103 DROP TABLE initscripts;
105 ALTER TABLE events DROP COLUMN auth_type;
107 ALTER TABLE event_object DROP COLUMN object_type;
109 ---------- revert subversion
111 UPDATE plc_db_version SET subversion = 4;
112 SELECT subversion from plc_db_version;