1 -- Thierry Parmentelat - INRIA
5 -- this is part of the script to migrate from 4.2 to 5.0
7 -- most of the renamings have taken place already when this script is invoked
10 ----------------------------------------
12 ----------------------------------------
13 -- we want the views to get out of our way, i.e. to drop all views;
14 -- the views will be reinstantiated later upon loading of planetlab5.sql
16 -- this lists all views
17 CREATE OR REPLACE VIEW mgn_all_views AS
18 SELECT c.relname FROM pg_catalog.pg_class c
19 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
20 WHERE c.relkind IN ('v','') AND n.nspname in ('public')
21 AND pg_catalog.pg_table_is_visible(c.oid);
24 select * from mgn_all_views;
26 -- this one version almost works, but somehow does not, could not figure why
27 CREATE OR REPLACE FUNCTION mgn_drop_all_views () RETURNS INTEGER AS $$
29 row mgn_all_views%ROWTYPE;
31 FOR row IN SELECT * FROM mgn_all_views where relname != 'mgn_all_views' LOOP
32 RAISE NOTICE 'Dropping %',row.relname;
33 EXECUTE 'DROP VIEW ' || row.relname || ' CASCADE' ;
37 $$ LANGUAGE 'plpgsql';
39 -- SELECT mgn_drop_all_views();
41 -- so let's have it the boring way
42 DROP VIEW address_address_types CASCADE;
43 DROP VIEW conf_file_nodegroups CASCADE;
44 DROP VIEW conf_file_nodes CASCADE;
45 DROP VIEW dummybox_nodes CASCADE;
46 DROP VIEW event_objects CASCADE;
47 DROP VIEW node_conf_files CASCADE;
48 DROP VIEW node_nodegroups CASCADE;
49 DROP VIEW interfaces_ordered CASCADE;
50 -- caught by some previous cascade -- DROP VIEW node_interfaces CASCADE;
51 DROP VIEW node_pcus CASCADE;
52 DROP VIEW node_slices CASCADE;
53 DROP VIEW node_slices_whitelist CASCADE;
54 DROP VIEW nodegroup_conf_files CASCADE;
55 DROP VIEW nodegroup_nodes CASCADE;
56 DROP VIEW interface_tags CASCADE;
57 DROP VIEW pcu_nodes CASCADE;
58 DROP VIEW pcu_protocol_types CASCADE;
59 DROP VIEW peer_keys CASCADE;
60 DROP VIEW peer_nodes CASCADE;
61 DROP VIEW peer_persons CASCADE;
62 DROP VIEW peer_sites CASCADE;
63 DROP VIEW peer_slices CASCADE;
64 DROP VIEW person_keys CASCADE;
65 DROP VIEW person_roles CASCADE;
66 DROP VIEW person_site_ordered CASCADE;
67 -- caught by some previous cascade -- DROP VIEW person_sites CASCADE;
68 DROP VIEW person_slices CASCADE;
69 DROP VIEW site_addresses CASCADE;
70 DROP VIEW site_nodes CASCADE;
71 DROP VIEW site_pcus CASCADE;
72 DROP VIEW site_persons CASCADE;
73 DROP VIEW site_slices CASCADE;
74 DROP VIEW slice_tags CASCADE;
75 DROP VIEW slice_nodes CASCADE;
76 DROP VIEW slice_persons CASCADE;
77 DROP VIEW slivers CASCADE;
78 -- caught by some previous cascade -- DROP VIEW view_addresses CASCADE;
79 -- caught by some previous cascade -- DROP VIEW view_conf_files CASCADE;
80 -- caught by some previous cascade -- DROP VIEW view_dummyboxes CASCADE;
81 DROP VIEW view_event_objects CASCADE;
82 -- caught by some previous cascade -- DROP VIEW view_events CASCADE;
83 DROP VIEW view_keys CASCADE;
84 -- caught by some previous cascade -- DROP VIEW view_nodegroups CASCADE;
85 DROP VIEW view_interface_tags CASCADE;
86 -- caught by some previous cascade -- DROP VIEW view_interfaces CASCADE;
87 -- caught by some previous cascade -- DROP VIEW view_nodes CASCADE;
88 -- caught by some previous cascade -- DROP VIEW view_pcu_types CASCADE;
89 -- caught by some previous cascade -- DROP VIEW view_pcus CASCADE;
90 -- caught by some previous cascade -- DROP VIEW view_peers CASCADE;
91 -- caught by some previous cascade -- DROP VIEW view_persons CASCADE;
92 DROP VIEW view_sessions CASCADE;
93 -- caught by some previous cascade -- DROP VIEW view_sites CASCADE;
94 DROP VIEW view_slice_tags CASCADE;
95 -- caught by some previous cascade -- DROP VIEW view_slices CASCADE;
98 select * from mgn_all_views;
100 -- cleanup migration utilities
101 drop view mgn_all_views;
102 drop function mgn_drop_all_views ();
104 ----------------------------------------
106 ----------------------------------------
107 ALTER TABLE peers ADD COLUMN shortname TEXT;
108 ALTER TABLE peers ADD COLUMN hrn_root TEXT;
111 ----------------------------------------
113 ----------------------------------------
114 CREATE TABLE run_levels (
115 run_level text PRIMARY KEY
117 INSERT INTO run_levels (run_level) VALUES ('boot');
118 INSERT INTO run_levels (run_level) VALUES ('safeboot');
119 INSERT INTO run_levels (run_level) VALUES ('failboot');
120 INSERT INTO run_levels (run_level) VALUES ('reinstall');
122 ----------------------------------------
124 ----------------------------------------
125 ALTER TABLE nodes ADD COLUMN node_type TEXT NOT NULL DEFAULT 'regular';
126 ALTER TABLE nodes ADD COLUMN verified boolean NOT NULL DEFAULT false; -- whether or not the node & pcu are verified
127 ALTER TABLE nodes ADD COLUMN extrainfo TEXT;
128 ALTER TABLE nodes ADD COLUMN run_level TEXT REFERENCES run_levels DEFAULT NULL; -- Node Run Level
130 ----------------------------------------
132 ----------------------------------------
133 --- merge former slice attribute types and setting attribute types into tagtypes
135 ---------- slice attributes
137 --- the tag_types table is obtained from the former slice_attribute_types table
138 ALTER TABLE tag_types RENAME COLUMN name TO tagname;
139 --- former slice_attribute_types had no 'category'
140 ALTER TABLE tag_types ADD COLUMN category TEXT NOT NULL DEFAULT 'slice/legacy';
142 --- append in tag_types the contents of former nodenetwork_setting_types
143 INSERT INTO tag_types (tagname,description,min_role_id,category)
144 SELECT name,description,min_role_id,'interface/legacy' FROM interface_tag_types;
146 ---------- interface settings
148 --- former nodenetwork_setting_type_id are now renumbered, need to fix interface_tag accordingly
150 -- old_index -> new_index relation
151 CREATE OR REPLACE VIEW mgn_setting_renumber AS
153 interface_tag_types.interface_tag_type_id AS old_index,
154 tag_types.tag_type_id AS new_index
156 interface_tag_types INNER JOIN tag_types
157 ON interface_tag_types.name = tag_types.tagname;
159 -- need to temporarily drop constraint on interface_tag_type_id
160 ALTER TABLE interface_tag DROP CONSTRAINT interface_tag_interface_tag_type_id_fkey;
162 -- do the transcoding
164 SET interface_tag_type_id =
165 (select new_index from mgn_setting_renumber where old_index=interface_tag_type_id);
167 -- alter column name to reflect change
168 ALTER TABLE interface_tag RENAME interface_tag_type_id TO tag_type_id;
170 -- add constraint again
171 ALTER TABLE interface_tag ADD CONSTRAINT interface_tag_tag_type_id_fkey
172 FOREIGN KEY (tag_type_id) references tag_types(tag_type_id) ;
174 -- drop former interface_tag_types altogether
175 drop view mgn_setting_renumber;
176 drop table interface_tag_types;
180 CREATE TABLE node_tag (
181 node_tag_id serial PRIMARY KEY, -- ID
182 node_id integer REFERENCES nodes NOT NULL, -- node id
183 tag_type_id integer REFERENCES tag_types, -- tag type id
184 value text -- value attached
188 ----------------------------------------
190 ----------------------------------------
192 ilink_id serial PRIMARY KEY, -- id
193 tag_type_id integer REFERENCES tag_types, -- id of the tag type
194 src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
195 dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
196 value text -- optional value on the link
199 ----------------------------------------
201 ----------------------------------------
203 ---------- nodegroups table - start
204 -- nodegroup_id is preserved for conf_files and other references
205 -- former nodegroups table was (nodegroup_id,name,description)
206 -- new table is now (nodegroup_id, groupname, tag_type_id, value)
209 ALTER TABLE nodegroups RENAME name TO groupname;
211 ---------- create missing tag types
212 -- change default for the entries about to be created
213 ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'nodegroup/migration';
216 INSERT INTO tag_types (tagname)
217 SELECT DISTINCT tagname FROM mgn_site_nodegroup
218 WHERE tagname NOT IN (SELECT tagname from tag_types);
220 -- xxx drop description in former nodegroups for now,
221 -- but could have been attached to newly created tag types first
222 ALTER TABLE nodegroups DROP COLUMN description;
224 ---------- set the right tags so as to recover former nodegroups
225 INSERT INTO node_tag (node_id, tag_type_id, value)
226 SELECT node_id, tag_type_id, value FROM
227 nodegroup_node LEFT JOIN nodegroups USING (nodegroup_id)
228 INNER JOIN mgn_site_nodegroup USING (groupname)
229 LEFT JOIN tag_types using (tagname);
231 ---------- nodegroups table - conclusion
232 ALTER TABLE nodegroups ADD COLUMN tag_type_id INTEGER;
233 ALTER TABLE nodegroups ADD COLUMN value TEXT;
235 CREATE OR REPLACE VIEW mgn_nodegroups AS
236 SELECT groupname, tag_types.tag_type_id, mgn_site_nodegroup.value
237 FROM nodegroups INNER JOIN mgn_site_nodegroup USING (groupname)
238 INNER JOIN tag_types USING (tagname);
240 UPDATE nodegroups SET tag_type_id = (SELECT tag_type_id FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
241 UPDATE nodegroups SET value = (SELECT value FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
243 -- install corresponding constraints
244 ALTER TABLE nodegroups ADD CONSTRAINT nodegroups_tag_type_id_fkey
245 FOREIGN KEY (tag_type_id) REFERENCES tag_types (tag_type_id);
247 --- change default now that the column is filled
248 ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'general';
250 -- cleanup the nodegroup area
251 drop view mgn_nodegroups;
252 drop table mgn_site_nodegroup;
253 drop table nodegroup_node;
256 ----------------------------------------
258 ----------------------------------------
260 INSERT INTO boot_states (boot_state) VALUES ('safeboot');
261 INSERT INTO boot_states (boot_state) VALUES ('failboot');
262 INSERT INTO boot_states (boot_state) VALUES ('disabled');
263 INSERT INTO boot_states (boot_state) VALUES ('reinstall');
266 -- b/c boot_states are declared by users not reported by node.
267 UPDATE nodes SET boot_state='safeboot' WHERE boot_state='dbg';
268 UPDATE nodes SET boot_state='safeboot' WHERE boot_state='diag';
269 UPDATE nodes SET boot_state='disabled' WHERE boot_state='disable';
270 UPDATE nodes SET boot_state='reinstall' WHERE boot_state='inst';
271 UPDATE nodes SET boot_state='reinstall' WHERE boot_state='rins';
272 UPDATE nodes SET boot_state='reinstall' WHERE boot_state='new';
273 UPDATE nodes SET boot_state='reinstall' WHERE boot_state='rcnf';
276 DELETE FROM boot_states WHERE boot_state='dbg';
277 DELETE FROM boot_states WHERE boot_state='diag';
278 DELETE FROM boot_states WHERE boot_state='disable';
279 DELETE FROM boot_states WHERE boot_state='install';
280 DELETE FROM boot_states WHERE boot_state='inst';
281 DELETE FROM boot_states WHERE boot_state='rins';
282 DELETE FROM boot_states WHERE boot_state='new';
283 DELETE FROM boot_states WHERE boot_state='rcnf';
285 -- ----------------------------------------
286 -- -- debug/information : display current constraints
287 -- ----------------------------------------
288 -- CREATE OR REPLACE VIEW mgn_all_constraints AS
289 -- SELECT * FROM pg_catalog.pg_constraint c
290 -- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
291 -- LEFT JOIN pg_catalog.pg_class t ON t.oid = c.conrelid
292 -- WHERE c.contype IN ('c','f','p','u') AND n.nspname in ('public')
293 -- AND pg_catalog.pg_table_is_visible(c.oid);
295 -- select * from mgn_all_constraints;
297 -- drop view mgn_all_constraints;
299 --- versioning (plc_db_version): ignore for now, so we keep both entries (v4 and v5)