1 -- Thierry Parmentelat - INRIA
5 -- this is part of the script to migrate from 4.2 to 5.0
8 ----------------------------------------
10 ----------------------------------------
11 -- we want the views to get out of our way, i.e. to drop all views;
12 -- the views will be reinstantiated later upon loading of planetlab5.sql
14 -- this lists all views
15 CREATE OR REPLACE VIEW mgn_all_views AS
16 SELECT c.relname FROM pg_catalog.pg_class c
17 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
18 WHERE c.relkind IN ('v','') AND n.nspname in ('public')
19 AND pg_catalog.pg_table_is_visible(c.oid);
22 select * from mgn_all_views;
24 -- this one version almost works, but somehow does not, could not figure why
25 CREATE OR REPLACE FUNCTION mgn_drop_all_views () RETURNS INTEGER AS $$
27 row mgn_all_views%ROWTYPE;
29 FOR row IN SELECT * FROM mgn_all_views where relname != 'mgn_all_views' LOOP
30 RAISE NOTICE 'Dropping %',row.relname;
31 EXECUTE 'DROP VIEW ' || row.relname || ' CASCADE' ;
35 $$ LANGUAGE 'plpgsql';
37 -- SELECT mgn_drop_all_views();
39 -- so let's have it the boring way
40 DROP VIEW address_address_types CASCADE;
41 DROP VIEW conf_file_nodegroups CASCADE;
42 DROP VIEW conf_file_nodes CASCADE;
43 DROP VIEW dummybox_nodes CASCADE;
44 DROP VIEW event_objects CASCADE;
45 DROP VIEW node_conf_files CASCADE;
46 DROP VIEW node_nodegroups CASCADE;
47 DROP VIEW interfaces_ordered CASCADE;
48 -- caught by some previous cascade -- DROP VIEW node_interfaces CASCADE;
49 DROP VIEW node_pcus CASCADE;
50 DROP VIEW node_slices CASCADE;
51 DROP VIEW node_slices_whitelist CASCADE;
52 DROP VIEW nodegroup_conf_files CASCADE;
53 DROP VIEW nodegroup_nodes CASCADE;
54 DROP VIEW interface_settings CASCADE;
55 DROP VIEW pcu_nodes CASCADE;
56 DROP VIEW pcu_protocol_types CASCADE;
57 DROP VIEW peer_keys CASCADE;
58 DROP VIEW peer_nodes CASCADE;
59 DROP VIEW peer_persons CASCADE;
60 DROP VIEW peer_sites CASCADE;
61 DROP VIEW peer_slices CASCADE;
62 DROP VIEW person_keys CASCADE;
63 DROP VIEW person_roles CASCADE;
64 DROP VIEW person_site_ordered CASCADE;
65 -- caught by some previous cascade -- DROP VIEW person_sites CASCADE;
66 DROP VIEW person_slices CASCADE;
67 DROP VIEW site_addresses CASCADE;
68 DROP VIEW site_nodes CASCADE;
69 DROP VIEW site_pcus CASCADE;
70 DROP VIEW site_persons CASCADE;
71 DROP VIEW site_slices CASCADE;
72 DROP VIEW slice_attributes CASCADE;
73 DROP VIEW slice_nodes CASCADE;
74 DROP VIEW slice_persons CASCADE;
75 DROP VIEW slivers CASCADE;
76 -- caught by some previous cascade -- DROP VIEW view_addresses CASCADE;
77 -- caught by some previous cascade -- DROP VIEW view_conf_files CASCADE;
78 -- caught by some previous cascade -- DROP VIEW view_dummyboxes CASCADE;
79 DROP VIEW view_event_objects CASCADE;
80 -- caught by some previous cascade -- DROP VIEW view_events CASCADE;
81 DROP VIEW view_keys CASCADE;
82 -- caught by some previous cascade -- DROP VIEW view_nodegroups CASCADE;
83 DROP VIEW view_interface_settings CASCADE;
84 -- caught by some previous cascade -- DROP VIEW view_interfaces CASCADE;
85 -- caught by some previous cascade -- DROP VIEW view_nodes CASCADE;
86 -- caught by some previous cascade -- DROP VIEW view_pcu_types CASCADE;
87 -- caught by some previous cascade -- DROP VIEW view_pcus CASCADE;
88 -- caught by some previous cascade -- DROP VIEW view_peers CASCADE;
89 -- caught by some previous cascade -- DROP VIEW view_persons CASCADE;
90 DROP VIEW view_sessions CASCADE;
91 -- caught by some previous cascade -- DROP VIEW view_sites CASCADE;
92 DROP VIEW view_slice_attributes CASCADE;
93 -- caught by some previous cascade -- DROP VIEW view_slices CASCADE;
96 select * from mgn_all_views;
98 -- cleanup migration utilities
99 drop view mgn_all_views;
100 drop function mgn_drop_all_views ();
102 ----------------------------------------
104 ----------------------------------------
105 --- merge former slice attribute types and setting attribute types into tagtypes
107 ---------- slice attributes
109 --- the tag_types table is obtained from the former slice_attribute_types table
110 ALTER TABLE tag_types RENAME COLUMN name TO tagname;
111 --- former slice_attribute_types had no 'category'
112 ALTER TABLE tag_types ADD COLUMN category TEXT NOT NULL DEFAULT 'slice/legacy';
114 --- append in tag_types the contents of nodenetwork_setting_types
115 INSERT INTO tag_types (tagname,description,min_role_id,category)
116 SELECT name,description,min_role_id,'interface/legacy' FROM interface_setting_types;
118 ---------- interface settings
120 --- former nodenetwork_setting_type_id are now renumbered, need to fix interface_setting accordingly
122 -- old_index -> new_index relation
123 CREATE OR REPLACE VIEW mgn_setting_renumber AS
125 interface_setting_types.interface_setting_type_id AS old_index,
126 tag_types.tag_type_id AS new_index
128 interface_setting_types INNER JOIN tag_types
129 ON interface_setting_types.name = tag_types.tagname;
131 -- need to temporarily drop constraint on interface_setting_type_id
132 ALTER TABLE interface_setting DROP CONSTRAINT interface_setting_interface_setting_type_id_fkey;
134 -- do the transcoding
135 UPDATE interface_setting
136 SET interface_setting_type_id =
137 (select new_index from mgn_setting_renumber where old_index=interface_setting_type_id);
139 -- alter column name to reflect change
140 ALTER TABLE interface_setting RENAME interface_setting_type_id TO tag_type_id;
142 -- add contraint again
143 ALTER TABLE interface_setting ADD CONSTRAINT interface_setting_tag_type_id_fkey
144 FOREIGN KEY (tag_type_id) references tag_types(tag_type_id) ;
146 -- drop former interface_setting_types altogether
147 drop view mgn_setting_renumber;
148 drop table interface_setting_types;
152 CREATE TABLE node_tag (
153 node_tag_id serial PRIMARY KEY, -- ID
154 node_id integer REFERENCES nodes NOT NULL, -- node id
155 tag_type_id integer REFERENCES tag_types, -- tag type id
156 tagvalue text -- value attached
160 ----------------------------------------
162 ----------------------------------------
164 ilink_id serial PRIMARY KEY, -- id
165 tag_type_id integer REFERENCES tag_types, -- id of the tag type
166 src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
167 dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
168 value text -- optional value on the link
171 ----------------------------------------
173 ----------------------------------------
175 ---------- nodegroups table - start
176 -- nodegroup_id is preserved for conf_files and other references
177 -- former nodegroups table was (nodegroup_id,name,description)
178 -- new table is now (nodegroup_id, groupname, tag_type_id, tagvalue)
181 ALTER TABLE nodegroups RENAME name TO groupname;
183 ---------- create missing tag types
184 -- change default for the entries about to be created
185 ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'nodegroup/migration';
188 INSERT INTO tag_types (tagname)
189 SELECT DISTINCT tagname FROM mgn_site_nodegroup
190 WHERE tagname NOT IN (SELECT tagname from tag_types);
192 -- xxx drop description in former nodegroups for now,
193 -- but could have been attached to newly created tag types first
194 ALTER TABLE nodegroups DROP COLUMN description;
196 ---------- set the right tags so as to recover former nodegroups
197 INSERT INTO node_tag (node_id, tag_type_id, tagvalue)
198 SELECT node_id, tag_type_id, tagvalue FROM
199 nodegroup_node LEFT JOIN nodegroups USING (nodegroup_id)
200 INNER JOIN mgn_site_nodegroup USING (groupname)
201 LEFT JOIN tag_types using (tagname);
203 ---------- nodegroups table - conclusion
204 ALTER TABLE nodegroups ADD COLUMN tag_type_id INTEGER;
205 ALTER TABLE nodegroups ADD COLUMN tagvalue TEXT;
207 CREATE OR REPLACE VIEW mgn_nodegroups AS
208 SELECT groupname, tag_types.tag_type_id, mgn_site_nodegroup.tagvalue
209 FROM nodegroups INNER JOIN mgn_site_nodegroup USING (groupname)
210 INNER JOIN tag_types USING (tagname);
212 UPDATE nodegroups SET tag_type_id = (SELECT tag_type_id FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
213 UPDATE nodegroups SET tagvalue = (SELECT tagvalue FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
215 -- install corresponding constraints
216 ALTER TABLE nodegroups ADD CONSTRAINT nodegroups_tag_type_id_fkey
217 FOREIGN KEY (tag_type_id) REFERENCES tag_types (tag_type_id);
219 --- change default now that the column is filled
220 ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'general';
222 -- cleanup the nodegroup area
223 drop view mgn_nodegroups;
224 drop table mgn_site_nodegroup;
225 drop table nodegroup_node;
227 ----------------------------------------
229 ----------------------------------------
230 INSERT INTO boot_states (boot_state) VALUES ('safeboot');
231 INSERT INTO boot_states (boot_state) VALUES ('failboot');
232 INSERT INTO boot_states (boot_state) VALUES ('disabled');
233 INSERT INTO boot_states (boot_state) VALUES ('install');
234 INSERT INTO boot_states (boot_state) VALUES ('reinstall');
248 UPDATE nodes SET boot_state='failboot' WHERE boot_state='dbg';
249 UPDATE nodes SET boot_state='safeboot' WHERE boot_state='diag';
250 UPDATE nodes SET boot_state='disabled' WHERE boot_state='disable';
251 UPDATE nodes SET boot_state='install' WHERE boot_state='inst';
252 UPDATE nodes SET boot_state='reinstall' WHERE boot_state='rins';
253 UPDATE nodes SET boot_state='reinstall' WHERE boot_state='new';
254 UPDATE nodes SET boot_state='failboot' WHERE boot_state='rcnf';
256 -- one-by-one is safer
257 DELETE FROM boot_states WHERE boot_state='dbg';
258 DELETE FROM boot_states WHERE boot_state='diag';
259 DELETE FROM boot_states WHERE boot_state='disable';
260 DELETE FROM boot_states WHERE boot_state='inst';
261 DELETE FROM boot_states WHERE boot_state='rins';
262 DELETE FROM boot_states WHERE boot_state='new';
263 DELETE FROM boot_states WHERE boot_state='rcnf';
265 -- ----------------------------------------
266 -- -- debug/information : display current constraints
267 -- ----------------------------------------
268 -- CREATE OR REPLACE VIEW mgn_all_constraints AS
269 -- SELECT * FROM pg_catalog.pg_constraint c
270 -- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
271 -- LEFT JOIN pg_catalog.pg_class t ON t.oid = c.conrelid
272 -- WHERE c.contype IN ('c','f','p','u') AND n.nspname in ('public')
273 -- AND pg_catalog.pg_table_is_visible(c.oid);
275 -- select * from mgn_all_constraints;
277 -- drop view mgn_all_constraints;
279 --- versioning (plc_db_version): ignore for now, so we keep both entries (v4 and v5)