-- $Id$
--
-- this is part of the script to migrate from 4.2 to 5.0
+--
+-- most of the renamings have taken place already when this script is invoked
--
--------------------- VIEWS :
+----------------------------------------
+-- views
+----------------------------------------
-- we want the views to get out of our way, i.e. to drop all views;
-- the views will be reinstantiated later upon loading of planetlab5.sql
DROP VIEW node_slices_whitelist CASCADE;
DROP VIEW nodegroup_conf_files CASCADE;
DROP VIEW nodegroup_nodes CASCADE;
-DROP VIEW interface_settings CASCADE;
+DROP VIEW interface_tags CASCADE;
DROP VIEW pcu_nodes CASCADE;
DROP VIEW pcu_protocol_types CASCADE;
DROP VIEW peer_keys CASCADE;
DROP VIEW site_pcus CASCADE;
DROP VIEW site_persons CASCADE;
DROP VIEW site_slices CASCADE;
-DROP VIEW slice_attributes CASCADE;
+DROP VIEW slice_tags CASCADE;
DROP VIEW slice_nodes CASCADE;
DROP VIEW slice_persons CASCADE;
DROP VIEW slivers CASCADE;
-- caught by some previous cascade -- DROP VIEW view_events CASCADE;
DROP VIEW view_keys CASCADE;
-- caught by some previous cascade -- DROP VIEW view_nodegroups CASCADE;
-DROP VIEW view_interface_settings CASCADE;
+DROP VIEW view_interface_tags CASCADE;
-- caught by some previous cascade -- DROP VIEW view_interfaces CASCADE;
-- caught by some previous cascade -- DROP VIEW view_nodes CASCADE;
-- caught by some previous cascade -- DROP VIEW view_pcu_types CASCADE;
-- caught by some previous cascade -- DROP VIEW view_persons CASCADE;
DROP VIEW view_sessions CASCADE;
-- caught by some previous cascade -- DROP VIEW view_sites CASCADE;
-DROP VIEW view_slice_attributes CASCADE;
+DROP VIEW view_slice_tags CASCADE;
-- caught by some previous cascade -- DROP VIEW view_slices CASCADE;
-- shows in logfile
select * from mgn_all_views;
+-- cleanup migration utilities
+drop view mgn_all_views;
+drop function mgn_drop_all_views ();
+
+----------------------------------------
+-- peers
+----------------------------------------
+ALTER TABLE peers ADD COLUMN shortname TEXT;
+ALTER TABLE peers ADD COLUMN hrn_root TEXT;
+
+----------------------------------------
+-- nodes
+----------------------------------------
+ALTER TABLE nodes ADD COLUMN node_type TEXT NOT NULL DEFAULT 'regular';
--------------------- TAG TYPES
+----------------------------------------
+-- tag types
+----------------------------------------
--- merge former slice attribute types and setting attribute types into tagtypes
+---------- slice attributes
+
--- the tag_types table is obtained from the former slice_attribute_types table
ALTER TABLE tag_types RENAME COLUMN name TO tagname;
--- former slice_attribute_types had no 'category'
ALTER TABLE tag_types ADD COLUMN category TEXT NOT NULL DEFAULT 'slice/legacy';
---- append in tag_types the contents of nodenetwork_setting_types
+--- append in tag_types the contents of former nodenetwork_setting_types
INSERT INTO tag_types (tagname,description,min_role_id,category)
- SELECT name,description,min_role_id,'interface/legacy' FROM interface_setting_types;
+ SELECT name,description,min_role_id,'interface/legacy' FROM interface_tag_types;
---- former nodenetwork_setting_type_id are now renumbered, need to fix interface_setting accordingly
+---------- interface settings
+
+--- former nodenetwork_setting_type_id are now renumbered, need to fix interface_tag accordingly
-- old_index -> new_index relation
CREATE OR REPLACE VIEW mgn_setting_renumber AS
SELECT
- interface_setting_types.interface_setting_type_id AS old_index,
+ interface_tag_types.interface_tag_type_id AS old_index,
tag_types.tag_type_id AS new_index
FROM
- interface_setting_types INNER JOIN tag_types
- ON interface_setting_types.name = tag_types.tagname;
+ interface_tag_types INNER JOIN tag_types
+ ON interface_tag_types.name = tag_types.tagname;
--- need to temporarily drop constraint on interface_setting_type_id
-ALTER TABLE interface_setting DROP CONSTRAINT interface_setting_interface_setting_type_id_fkey;
+-- need to temporarily drop constraint on interface_tag_type_id
+ALTER TABLE interface_tag DROP CONSTRAINT interface_tag_interface_tag_type_id_fkey;
-- do the transcoding
-UPDATE interface_setting
- SET interface_setting_type_id =
- (select new_index from mgn_setting_renumber where old_index=interface_setting_type_id);
+UPDATE interface_tag
+ SET interface_tag_type_id =
+ (select new_index from mgn_setting_renumber where old_index=interface_tag_type_id);
--- alter column nam to reflect change
-ALTER TABLE interface_setting RENAME interface_setting_type_id TO tag_type_id;
+-- alter column name to reflect change
+ALTER TABLE interface_tag RENAME interface_tag_type_id TO tag_type_id;
--- add contraint again
-ALTER TABLE interface_setting ADD CONSTRAINT interface_setting_tag_type_id_fkey
+-- add constraint again
+ALTER TABLE interface_tag ADD CONSTRAINT interface_tag_tag_type_id_fkey
FOREIGN KEY (tag_type_id) references tag_types(tag_type_id) ;
--- drop former interface_setting_types altogether
+-- drop former interface_tag_types altogether
drop view mgn_setting_renumber;
-drop table interface_setting_types;
+drop table interface_tag_types;
+
+---------- node tags
+
+CREATE TABLE node_tag (
+ node_tag_id serial PRIMARY KEY, -- ID
+ node_id integer REFERENCES nodes NOT NULL, -- node id
+ tag_type_id integer REFERENCES tag_types, -- tag type id
+ value text -- value attached
+) WITH OIDS;
--------------------- NEW STUFF
+----------------------------------------
+-- ilinks
+----------------------------------------
CREATE TABLE ilink (
ilink_id serial PRIMARY KEY, -- id
tag_type_id integer REFERENCES tag_types, -- id of the tag type
value text -- optional value on the link
) WITH OIDS;
-
-CREATE TABLE node_tag (
- node_tag_id serial PRIMARY KEY, -- ID
- node_id integer REFERENCES nodes NOT NULL, -- node id
- tag_type_id integer REFERENCES tag_types, -- tag type id
- tagvalue text -- value attached
-) WITH OIDS;
-
----------------------------------------
-- nodegroups
----------------------------------------
---------- nodegroups table - start
-- nodegroup_id is preserved for conf_files and other references
-- former nodegroups table was (nodegroup_id,name,description)
--- new table is now (nodegroup_id, groupname, tag_type_id, tagvalue)
+-- new table is now (nodegroup_id, groupname, tag_type_id, value)
-- rename column
ALTER TABLE nodegroups RENAME name TO groupname;
SELECT DISTINCT tagname FROM mgn_site_nodegroup
WHERE tagname NOT IN (SELECT tagname from tag_types);
--- xxx drop description in former nodegroups for now, could be attached to newly created tag types..
+-- xxx drop description in former nodegroups for now,
+-- but could have been attached to newly created tag types first
ALTER TABLE nodegroups DROP COLUMN description;
---------- set the right tags so as to recover former nodegroups
-INSERT INTO node_tag (node_id, tag_type_id, tagvalue)
- SELECT node_id, tag_type_id, tagvalue FROM
+INSERT INTO node_tag (node_id, tag_type_id, value)
+ SELECT node_id, tag_type_id, value FROM
nodegroup_node LEFT JOIN nodegroups USING (nodegroup_id)
INNER JOIN mgn_site_nodegroup USING (groupname)
LEFT JOIN tag_types using (tagname);
---------- nodegroups table - conclusion
ALTER TABLE nodegroups ADD COLUMN tag_type_id INTEGER;
-ALTER TABLE nodegroups ADD COLUMN tagvalue TEXT;
+ALTER TABLE nodegroups ADD COLUMN value TEXT;
CREATE OR REPLACE VIEW mgn_nodegroups AS
- SELECT groupname, tag_types.tag_type_id, mgn_site_nodegroup.tagvalue
+ SELECT groupname, tag_types.tag_type_id, mgn_site_nodegroup.value
FROM nodegroups INNER JOIN mgn_site_nodegroup USING (groupname)
INNER JOIN tag_types USING (tagname);
UPDATE nodegroups SET tag_type_id = (SELECT tag_type_id FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
-UPDATE nodegroups SET tagvalue = (SELECT tagvalue FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
+UPDATE nodegroups SET value = (SELECT value FROM mgn_nodegroups WHERE nodegroups.groupname=mgn_nodegroups.groupname);
-- install corresponding constraints
ALTER TABLE nodegroups ADD CONSTRAINT nodegroups_tag_type_id_fkey
FOREIGN KEY (tag_type_id) REFERENCES tag_types (tag_type_id);
--------------
--- display constraints
-
-CREATE OR REPLACE VIEW mgn_all_constraints AS
- SELECT * FROM pg_catalog.pg_constraint c
- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
- LEFT JOIN pg_catalog.pg_class t ON t.oid = c.conrelid
- WHERE c.contype IN ('c','f','p','u') AND n.nspname in ('public')
- AND pg_catalog.pg_table_is_visible(c.oid);
--- change default now that the column is filled
ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'general';
drop table mgn_site_nodegroup;
drop table nodegroup_node;
--- cleanup migration utilities
-drop view mgn_all_views;
-drop view mgn_all_constraints;
-drop function mgn_drop_all_views ();
+----------------------------------------
+-- boot states
+----------------------------------------
+-- create new ones
+INSERT INTO boot_states (boot_state) VALUES ('safeboot');
+INSERT INTO boot_states (boot_state) VALUES ('failboot');
+INSERT INTO boot_states (boot_state) VALUES ('disabled');
+INSERT INTO boot_states (boot_state) VALUES ('install');
+INSERT INTO boot_states (boot_state) VALUES ('reinstall');
+
+-- map old ones
+UPDATE nodes SET boot_state='failboot' WHERE boot_state='dbg';
+UPDATE nodes SET boot_state='safeboot' WHERE boot_state='diag';
+UPDATE nodes SET boot_state='disabled' WHERE boot_state='disable';
+UPDATE nodes SET boot_state='install' WHERE boot_state='inst';
+UPDATE nodes SET boot_state='reinstall' WHERE boot_state='rins';
+UPDATE nodes SET boot_state='reinstall' WHERE boot_state='new';
+UPDATE nodes SET boot_state='failboot' WHERE boot_state='rcnf';
+
+-- delete old ones
+DELETE FROM boot_states WHERE boot_state='dbg';
+DELETE FROM boot_states WHERE boot_state='diag';
+DELETE FROM boot_states WHERE boot_state='disable';
+DELETE FROM boot_states WHERE boot_state='inst';
+DELETE FROM boot_states WHERE boot_state='rins';
+DELETE FROM boot_states WHERE boot_state='new';
+DELETE FROM boot_states WHERE boot_state='rcnf';
+
+-- ----------------------------------------
+-- -- debug/information : display current constraints
+-- ----------------------------------------
+-- CREATE OR REPLACE VIEW mgn_all_constraints AS
+-- SELECT * FROM pg_catalog.pg_constraint c
+-- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
+-- LEFT JOIN pg_catalog.pg_class t ON t.oid = c.conrelid
+-- WHERE c.contype IN ('c','f','p','u') AND n.nspname in ('public')
+-- AND pg_catalog.pg_table_is_visible(c.oid);
+--
+-- select * from mgn_all_constraints;
+--
+-- drop view mgn_all_constraints;
--- versioning (plc_db_version): ignore for now, so we keep both entries (v4 and v5)