1 -- Thierry Parmentelat - INRIA
5 -- this is part of the script to migrate from 4.2 to 5.0
8 -------------------- VIEWS :
9 -- we want the views to get out of our way, i.e. to drop all views;
10 -- the views will be reinstantiated later upon loading of planetlab5.sql
12 -- this lists all views
13 CREATE OR REPLACE VIEW mgn_all_views AS
14 SELECT c.relname FROM pg_catalog.pg_class c
15 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
16 WHERE c.relkind IN ('v','') AND n.nspname in ('public')
17 AND pg_catalog.pg_table_is_visible(c.oid);
20 select * from mgn_all_views;
22 -- this one version almost works, but somehow does not, could not figure why
23 CREATE OR REPLACE FUNCTION mgn_drop_all_views () RETURNS INTEGER AS $$
25 row mgn_all_views%ROWTYPE;
27 FOR row IN SELECT * FROM mgn_all_views where relname != 'mgn_all_views' LOOP
28 RAISE NOTICE 'Dropping %',row.relname;
29 EXECUTE 'DROP VIEW ' || row.relname || ' CASCADE' ;
33 $$ LANGUAGE 'plpgsql';
35 -- SELECT mgn_drop_all_views();
37 -- so let's have it the boring way
38 DROP VIEW address_address_types CASCADE;
39 DROP VIEW conf_file_nodegroups CASCADE;
40 DROP VIEW conf_file_nodes CASCADE;
41 DROP VIEW dummybox_nodes CASCADE;
42 DROP VIEW event_objects CASCADE;
43 DROP VIEW node_conf_files CASCADE;
44 DROP VIEW node_nodegroups CASCADE;
45 DROP VIEW interfaces_ordered CASCADE;
46 -- caught by some previous cascade -- DROP VIEW node_interfaces CASCADE;
47 DROP VIEW node_pcus CASCADE;
48 DROP VIEW node_slices CASCADE;
49 DROP VIEW node_slices_whitelist CASCADE;
50 DROP VIEW nodegroup_conf_files CASCADE;
51 DROP VIEW nodegroup_nodes CASCADE;
52 DROP VIEW interface_settings CASCADE;
53 DROP VIEW pcu_nodes CASCADE;
54 DROP VIEW pcu_protocol_types CASCADE;
55 DROP VIEW peer_keys CASCADE;
56 DROP VIEW peer_nodes CASCADE;
57 DROP VIEW peer_persons CASCADE;
58 DROP VIEW peer_sites CASCADE;
59 DROP VIEW peer_slices CASCADE;
60 DROP VIEW person_keys CASCADE;
61 DROP VIEW person_roles CASCADE;
62 DROP VIEW person_site_ordered CASCADE;
63 -- caught by some previous cascade -- DROP VIEW person_sites CASCADE;
64 DROP VIEW person_slices CASCADE;
65 DROP VIEW site_addresses CASCADE;
66 DROP VIEW site_nodes CASCADE;
67 DROP VIEW site_pcus CASCADE;
68 DROP VIEW site_persons CASCADE;
69 DROP VIEW site_slices CASCADE;
70 DROP VIEW slice_attributes CASCADE;
71 DROP VIEW slice_nodes CASCADE;
72 DROP VIEW slice_persons CASCADE;
73 DROP VIEW slivers CASCADE;
74 -- caught by some previous cascade -- DROP VIEW view_addresses CASCADE;
75 -- caught by some previous cascade -- DROP VIEW view_conf_files CASCADE;
76 -- caught by some previous cascade -- DROP VIEW view_dummyboxes CASCADE;
77 DROP VIEW view_event_objects CASCADE;
78 -- caught by some previous cascade -- DROP VIEW view_events CASCADE;
79 DROP VIEW view_keys CASCADE;
80 -- caught by some previous cascade -- DROP VIEW view_nodegroups CASCADE;
81 DROP VIEW view_interface_settings CASCADE;
82 -- caught by some previous cascade -- DROP VIEW view_interfaces CASCADE;
83 -- caught by some previous cascade -- DROP VIEW view_nodes CASCADE;
84 -- caught by some previous cascade -- DROP VIEW view_pcu_types CASCADE;
85 -- caught by some previous cascade -- DROP VIEW view_pcus CASCADE;
86 -- caught by some previous cascade -- DROP VIEW view_peers CASCADE;
87 -- caught by some previous cascade -- DROP VIEW view_persons CASCADE;
88 DROP VIEW view_sessions CASCADE;
89 -- caught by some previous cascade -- DROP VIEW view_sites CASCADE;
90 DROP VIEW view_slice_attributes CASCADE;
91 -- caught by some previous cascade -- DROP VIEW view_slices CASCADE;
94 select * from mgn_all_views;
97 -------------------- TAG TYPES
98 --- merge former slice attribute types and setting attribute types into tagtypes
100 --- the tag_types table is obtained from the former slice_attribute_types table
101 ALTER TABLE tag_types RENAME COLUMN name TO tagname;
102 --- former slice_attribute_types had no 'category'
103 ALTER TABLE tag_types ADD COLUMN category TEXT NOT NULL DEFAULT 'slice/legacy';
105 --- append in tag_types the contents of nodenetwork_setting_types
106 INSERT INTO tag_types (tagname,description,min_role_id,category)
107 SELECT name,description,min_role_id,'interface/legacy' FROM interface_setting_types;
109 --- former nodenetwork_setting_type_id are now renumbered, need to fix interface_setting accordingly
111 -- old_index -> new_index relation
112 CREATE OR REPLACE VIEW mgn_setting_renumber AS
114 interface_setting_types.interface_setting_type_id AS old_index,
115 tag_types.tag_type_id AS new_index
117 interface_setting_types INNER JOIN tag_types
118 ON interface_setting_types.name = tag_types.tagname;
120 -- need to temporarily drop constraint on interface_setting_type_id
121 ALTER TABLE interface_setting DROP CONSTRAINT interface_setting_interface_setting_type_id_fkey;
123 -- do the transcoding
124 UPDATE interface_setting
125 SET interface_setting_type_id =
126 (select new_index from mgn_setting_renumber where old_index=interface_setting_type_id);
128 -- alter column nam to reflect change
129 ALTER TABLE interface_setting RENAME interface_setting_type_id TO tag_type_id;
131 -- add contraint again
132 ALTER TABLE interface_setting ADD CONSTRAINT interface_setting_tag_type_id_fkey
133 FOREIGN KEY (tag_type_id) references tag_types(tag_type_id) ;
135 -- drop former interface_setting_types altogether
136 drop view mgn_setting_renumber;
137 drop table interface_setting_types;
139 -------------------- NEW STUFF
142 ilink_id serial PRIMARY KEY, -- id
143 tag_type_id integer REFERENCES tag_types, -- id of the tag type
144 src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
145 dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
146 value text -- optional value on the link
150 CREATE TABLE node_tag (
151 node_tag_id serial PRIMARY KEY, -- ID
152 node_id integer REFERENCES nodes NOT NULL, -- node id
153 tag_type_id integer REFERENCES tag_types, -- tag type id
154 tagvalue text -- value attached
157 ----------------------------------------
159 ----------------------------------------
161 ---------- nodegroups table - start
162 -- nodegroup_id is preserved for conf_files and other references
163 -- former nodegroups table was (nodegroup_id,name,description)
164 -- new table is now (nodegroup_id, groupname, tag_type_id, tagvalue)
167 ALTER TABLE nodegroups RENAME name TO groupname;
169 ---------- create missing tag types
170 -- change default for the entries about to be created
171 ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'nodegroup/migration';
174 INSERT INTO tag_types (tagname)
175 SELECT DISTINCT tagname FROM mgn_site_nodegroup
176 WHERE tagname NOT IN (SELECT tagname from tag_types);
178 -- xxx drop description in former nodegroups for now, could be attached to newly created tag types..
179 ALTER TABLE nodegroups DROP COLUMN description;
181 ---------- set the right tags so as to recover former nodegroups
182 INSERT INTO node_tag (node_id, tag_type_id, tagvalue)
183 SELECT node_id, tag_type_id, tagvalue FROM
184 nodegroup_node LEFT JOIN nodegroups USING (nodegroup_id)
185 INNER JOIN mgn_site_nodegroup USING (groupname)
186 LEFT JOIN tag_types using (tagname);
188 ---------- nodegroups table - conclusion
189 ALTER TABLE nodegroups ADD COLUMN tag_type_id INTEGER;
190 ALTER TABLE nodegroups ADD COLUMN tagvalue TEXT;
192 CREATE OR REPLACE VIEW mgn_nodegroups AS
193 SELECT groupname, tag_types.tag_type_id, mgn_site_nodegroup.tagvalue
194 FROM nodegroups INNER JOIN mgn_site_nodegroup USING (groupname)
195 INNER JOIN tag_types USING (tagname);
197 UPDATE nodegroups SET tag_type_id = (SELECT tag_type_id FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
198 UPDATE nodegroups SET tagvalue = (SELECT tagvalue FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
200 -- install corresponding constraints
201 ALTER TABLE nodegroups ADD CONSTRAINT nodegroups_tag_type_id_fkey
202 FOREIGN KEY (tag_type_id) REFERENCES tag_types (tag_type_id);
204 -- display constraints
206 CREATE OR REPLACE VIEW mgn_all_constraints AS
207 SELECT * FROM pg_catalog.pg_constraint c
208 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
209 LEFT JOIN pg_catalog.pg_class t ON t.oid = c.conrelid
210 WHERE c.contype IN ('c','f','p','u') AND n.nspname in ('public')
211 AND pg_catalog.pg_table_is_visible(c.oid);
213 --- change default now that the column is filled
214 ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'general';
216 -- cleanup the nodegroup area
217 drop view mgn_nodegroups;
218 drop table mgn_site_nodegroup;
219 drop table nodegroup_node;
221 -- cleanup migration utilities
222 drop view mgn_all_views;
223 drop view mgn_all_constraints;
224 drop function mgn_drop_all_views ();
226 --- versioning (plc_db_version): ignore for now, so we keep both entries (v4 and v5)