-----------------------------------------
--- rename nodenetwork into interface
-----------------------------------------
-
-ALTER TABLE nodenetworks RENAME TO interfaces;
-ALTER TABLE interfaces RENAME COLUMN nodenetwork_id TO interface_id;
-
-ALTER INDEX nodenetworks_node_id_idx RENAME TO interfaces_node_id_idx;
-
--- xxx need manual merge -> turn into tag_type
---ALTER TABLE nodenetwork_setting_types RENAME TO interface_setting_types;
---ALTER TABLE interface_setting_types RENAME COLUMN nodenetwork_setting_type_id TO interface_setting_type_id;
-
-ALTER TABLE nodenetwork_setting RENAME TO interface_setting;
-
--- views
-ALTER TABLE nodenetworks_ordered RENAME TO interfaces_ordered;
-ALTER TABLE interfaces_ordered RENAME COLUMN nodenetwork_id TO interface_id;
-
-ALTER TABLE node_nodenetworks RENAME TO node_interfaces;
-ALTER TABLE node_interfaces RENAME COLUMN nodenetwork_ids TO interface_ids;
-
-ALTER TABLE nodenetwork_settings RENAME TO interface_settings;
-ALTER TABLE interface_settings RENAME COLUMN nodenetwork_id TO interface_id;
-ALTER TABLE interface_settings RENAME COLUMN nodenetwork_setting_ids TO interface_setting_ids;
-
-ALTER TABLE view_nodenetwork_settings RENAME TO view_interface_settings;
-ALTER TABLE view_interface_settings RENAME COLUMN nodenetwork_setting_id TO interface_setting_id;
-ALTER TABLE view_interface_settings RENAME COLUMN nodenetwork_id TO interface_id;
--- xxx need manual merge -> turn into tag_type
---ALTER TABLE view_interface_settings RENAME COLUMN nodenetwork_setting_type_id TO interface_setting_type_id;
-
-ALTER TABLE view_nodenetworks RENAME TO view_interfaces;
-ALTER TABLE view_interfaces RENAME COLUMN nodenetwork_id TO interface_id;
-ALTER TABLE view_interfaces RENAME COLUMN nodenetwork_setting_ids TO interface_setting_ids;
-
-ALTER TABLE view_nodes RENAME COLUMN nodenetwork_ids TO interface_ids;
-
-----------------------------------------
--- node tags
-----------------------------------------
-CREATE TABLE tag_types ...
-CREATE TABLE node_tag ...
-
----------- related views
-CREATE OR REPLACE VIEW node_tags AS ...
-CREATE OR REPLACE VIEW view_node_tags AS ...
+-------------------- 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
+
+-- this lists all views
+CREATE OR REPLACE VIEW all_views AS
+ SELECT c.relname FROM pg_catalog.pg_class c
+ LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+ WHERE c.relkind IN ('v','') AND n.nspname in ('public')
+ AND pg_catalog.pg_table_is_visible(c.oid);
+
+-- shows in logfile
+select * from all_views;
+
+-- this one version almost works, but somehow does not, could not figure why
+CREATE OR REPLACE FUNCTION drop_all_views () RETURNS INTEGER AS $$
+ DECLARE
+ row all_views%ROWTYPE;
+ BEGIN
+ FOR row IN SELECT * FROM all_views where relname != 'all_views' LOOP
+ RAISE NOTICE 'Dropping %',row.relname;
+ EXECUTE 'DROP VIEW ' || row.relname || ' CASCADE' ;
+ END LOOP;
+ RETURN 0;
+ END;
+$$ LANGUAGE 'plpgsql';
+
+-- SELECT drop_all_views();
+
+-- so let's have it the boring way
+DROP VIEW address_address_types CASCADE;
+DROP VIEW conf_file_nodegroups CASCADE;
+DROP VIEW conf_file_nodes CASCADE;
+DROP VIEW dummybox_nodes CASCADE;
+DROP VIEW event_objects CASCADE;
+DROP VIEW node_conf_files CASCADE;
+DROP VIEW node_nodegroups CASCADE;
+DROP VIEW interfaces_ordered CASCADE;
+-- caught by some previous cascade -- DROP VIEW node_interfaces CASCADE;
+DROP VIEW node_pcus CASCADE;
+DROP VIEW node_slices CASCADE;
+DROP VIEW node_slices_whitelist CASCADE;
+DROP VIEW nodegroup_conf_files CASCADE;
+DROP VIEW nodegroup_nodes CASCADE;
+DROP VIEW interface_settings CASCADE;
+DROP VIEW pcu_nodes CASCADE;
+DROP VIEW pcu_protocol_types CASCADE;
+DROP VIEW peer_keys CASCADE;
+DROP VIEW peer_nodes CASCADE;
+DROP VIEW peer_persons CASCADE;
+DROP VIEW peer_sites CASCADE;
+DROP VIEW peer_slices CASCADE;
+DROP VIEW person_keys CASCADE;
+DROP VIEW person_roles CASCADE;
+DROP VIEW person_site_ordered CASCADE;
+-- caught by some previous cascade -- DROP VIEW person_sites CASCADE;
+DROP VIEW person_slices CASCADE;
+DROP VIEW site_addresses CASCADE;
+DROP VIEW site_nodes CASCADE;
+DROP VIEW site_pcus CASCADE;
+DROP VIEW site_persons CASCADE;
+DROP VIEW site_slices CASCADE;
+DROP VIEW slice_attributes CASCADE;
+DROP VIEW slice_nodes CASCADE;
+DROP VIEW slice_persons CASCADE;
+DROP VIEW slivers CASCADE;
+-- caught by some previous cascade -- DROP VIEW view_addresses CASCADE;
+-- caught by some previous cascade -- DROP VIEW view_conf_files CASCADE;
+-- caught by some previous cascade -- DROP VIEW view_dummyboxes CASCADE;
+DROP VIEW view_event_objects 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;
+-- 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_pcus CASCADE;
+-- caught by some previous cascade -- DROP VIEW view_peers 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;
+-- caught by some previous cascade -- DROP VIEW view_slices CASCADE;
+
+-- shows in logfile
+select * from all_views;
+
+
+-------------------- TAG TYPES
+--- merge former slice attribute types and setting attribute types into tagtypes
+
+--- 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';
+--- change default now that the column is filled
+ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'general';
+
+--- append in tag_types the contents of 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;
+
+--- former nodenetwork_setting_type_id are now renumbered, need to fix interface_setting accordingly
+
+-- old_index -> new_index relation
+CREATE OR REPLACE VIEW index_renumber AS
+ SELECT
+ interface_setting_types.interface_setting_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;
+
+-- need to temporarily drop constraint on interface_setting_type_id
+ALTER TABLE interface_setting DROP CONSTRAINT interface_setting_interface_setting_type_id_fkey;
+
+-- do the transcoding
+UPDATE interface_setting
+ SET interface_setting_type_id =
+ (select new_index from index_renumber where old_index=interface_setting_type_id);
+
+-- alter column nam to reflect change
+ALTER TABLE interface_setting RENAME interface_setting_type_id TO tag_type_id;
+
+-- add contraint again
+ALTER TABLE interface_setting ADD CONSTRAINT interface_setting_tag_type_id_fkey
+ FOREIGN KEY (tag_type_id) references tag_types(tag_type_id) ;
+
+--- cleanup
+drop view index_renumber;
+
+-- drop former interface_setting_types altogether
+drop table interface_setting_types;
+
+-------------------- NEW STUFF
+
+CREATE TABLE ilink (
+ ilink_id serial PRIMARY KEY, -- id
+ tag_type_id integer REFERENCES tag_types, -- id of the tag type
+ src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface
+ dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface
+ 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;