2 -- to apply changes from import done in april 2007 from the
3 -- planetlab-4_0-branch tag
5 -- this is a rather complex example, so for next times, make sure that you
6 -- * first add new columns and new tables
7 -- * then create or replace views
8 -- * and only finally drop columns
9 -- otherwise the columns may refuse to get dropped if they are still used by views
14 ALTER TABLE sites ADD ext_consortium_id integer;
16 ALTER TABLE nodes ADD last_contact timestamp without time zone;
19 CREATE TABLE initscripts (
20 initscript_id serial PRIMARY KEY, -- Initscript identifier
21 name text NOT NULL, -- Initscript name
22 enabled bool NOT NULL DEFAULT true, -- Initscript is active
23 script text NOT NULL, -- Initscript
26 CREATE INDEX initscripts_name_idx ON initscripts (name);
28 -- rather drop the tables altogether,
29 -- ALTER TABLE events ADD auth_type text;
30 -- ALTER TABLE event_object ADD COLUMN object_type text NOT NULL Default 'Unknown';
31 -- CREATE INDEX event_object_object_type_idx ON event_object (object_type);
33 -- for some reason these views require to be dropped first
34 DROP VIEW view_events;
35 DROP VIEW event_objects;
39 ----dropping tables must be preceded by dropping views using those tables
40 ----otherwise dependency problems
41 DROP TABLE event_object;
45 event_id serial PRIMARY KEY, -- Event identifier
46 person_id integer REFERENCES persons, -- Person responsible for event, if any
47 node_id integer REFERENCES nodes, -- Node responsible for event, if any
48 auth_type text, -- Type of auth used. i.e. AuthMethod
49 fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error
50 call_name text NOT NULL, -- Call responsible for this event
51 call text NOT NULL, -- Call responsible for this event, including parameters
52 message text, -- High level description of this event
53 runtime float DEFAULT 0, -- Event run time
54 time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp
57 -- Database object(s) that may have been affected by a particular event
58 CREATE TABLE event_object (
59 event_id integer REFERENCES events NOT NULL, -- Event identifier
60 object_id integer NOT NULL, -- Object identifier
61 object_type text NOT NULL Default 'Unknown' -- What type of object is this event affecting
63 CREATE INDEX event_object_event_id_idx ON event_object (event_id);
64 CREATE INDEX event_object_object_id_idx ON event_object (object_id);
65 CREATE INDEX event_object_object_type_idx ON event_object (object_type);
67 ---------- view changes
69 CREATE OR REPLACE VIEW event_objects AS
71 array_accum(object_id) AS object_ids,
72 array_accum(object_type) AS object_types
76 CREATE OR REPLACE VIEW view_events AS
87 CAST(date_part('epoch', events.time) AS bigint) AS time,
88 COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_ids,
89 COALESCE((SELECT object_types FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_types
92 CREATE OR REPLACE VIEW view_nodes AS
104 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
105 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
106 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
108 peer_node.peer_node_id,
109 COALESCE((SELECT nodenetwork_ids FROM node_nodenetworks WHERE node_nodenetworks.node_id = nodes.node_id), '{}') AS nodenetwork_ids,
110 COALESCE((SELECT nodegroup_ids FROM node_nodegroups WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids,
111 COALESCE((SELECT slice_ids FROM node_slices WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids,
112 COALESCE((SELECT pcu_ids FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids,
113 COALESCE((SELECT ports FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports,
114 COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids,
115 node_session.session_id AS session
117 LEFT JOIN peer_node USING (node_id)
118 LEFT JOIN node_session USING (node_id);
120 CREATE OR REPLACE VIEW view_sites AS
125 sites.abbreviated_name,
134 sites.ext_consortium_id,
135 CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created,
136 CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated,
138 peer_site.peer_site_id,
139 COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids,
140 COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids,
141 COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids,
142 COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids,
143 COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids
145 LEFT JOIN peer_site USING (site_id);
148 --dont need to drop this colum it doesn't exit anymore
149 -----ALTER TABLE events DROP COLUMN object_type;
151 ---------- bump subversion
153 UPDATE plc_db_version SET subversion = 5;
154 SELECT subversion from plc_db_version;