COMMAND=$(basename $0)
BASENAME=$(basename $COMMAND .sh)
DIRNAME=$(dirname $0)
+# normalize
+DIRNAME=$(cd ${DIRNAME}; /bin/pwd)
-. /etc/planetlab/plc_config
+MIGRATION_SED=$DIRNAME/${BASENAME}.sed
+MIGRATION_SQL=$DIRNAME/${BASENAME}.sql
+# look in ..
+SCHEMA_SQL=$(dirname $DIRNAME)/planetlab5.sql
+
+DATE=$(date +%Y-%m-%d-%H-%M)
+DATE_=$(date +%Y_%m_%d_%H_%M)
+LOG=${DIRNAME}/${DATE}.log
+DUMP=${DIRNAME}/${DATE}-pl4.sql
+RESTORE=${DIRNAME}/${DATE}-pl5.sql
+FAKE=${DIRNAME}/input-pl4.sql
+VIEWS_SQL=$DIRNAME/${DATE}-views5.sql
-PLC_DB_USER
+# load config
+. /etc/planetlab/plc_config
# return 0 (yes) or 1 (no) whether the database exists
function check_for_database () {
dbname=$1; shift
- psql --user=$PLC_DB_USER --quiet -c "SELECT subversion from plc_db_version LIMIT 1" $dbname 2> /dev/null
+ psql --user=$PLC_DB_USER --quiet -c "SELECT subversion from plc_db_version LIMIT 1" $dbname > /dev/null 2>&1
return $?
}
# when 'service plc start' gets run, the planetlab5 DB gets created
# so this script will drop the planetlab5 DB and re-create it from scratch
# with the contents of the planetlab4 DB that is epxected to exist
-function main () {
-
+function warning () {
+ echo "$COMMAND"
+ echo "This script is designed to ease the migration from myplc 4.2 to 5.0"
+ echo "You can run it before of after you install a 5.0 myplc"
+ echo "It will attempt to re-create the planetlab5 database from planetlab4"
+ echo "The planetlab5 database is renamed, not dropped, if it is found on the system"
+ echo -n "Are you sure you want to proceed y/[n] ? "
+ read answer
+ case $answer in
+ y|Y) echo See log in $LOG ;;
+ *) echo "Bye" ; exit 1 ;;
+ esac
+}
+
+function check () {
+ [ -f $MIGRATION_SED ] || { echo $MIGRATION_SED not found - exiting ; exit 1; }
+ [ -f $MIGRATION_SQL ] || { echo $MIGRATION_SQL not found - exiting ; exit 1; }
+ [ -f $SCHEMA_SQL ] || { echo $SCHEMA_SQL not found - exiting ; exit 1; }
+}
+
+function run () {
+ message=$1; shift
+
+ if [ -n "$DEBUG" ] ; then
+ echo -n "Type enter to run next step XXX $message XXX ... "
+ read _answer_
+ fi
+
+ echo -n "$message "
+ echo "==================================================" >> $LOG
+ echo $message >> $LOG
+ echo "$@" >> $LOG
+ "$@" >> $LOG 2>&1
+ echo Done
+}
+
+function migrate () {
set -e
- cd /usr/share/plc_api
+ cd $DIRNAME
# check that planetlab4 exists
if check_for_database planetlab4 ; then
- echo OK : FOUND db planetlab4
+ echo "OK : FOUND db planetlab4"
else
- echo ERROR : planetlab4 NOT FOUND - exiting
+ echo "ERROR : planetlab4 NOT FOUND - bye"
exit 1
fi
- # dump planetlab4
- DUMP4=planetlab4-$(date +%Y-%m-%d-%H-%M)
- pg_dump --user=$PLC_DB_USER planetlab4 > $DUMP4.sql
-
# check if planetlab5 exists
if check_for_database planetlab5 ; then
- echo 'WARNING: found an existing DB named planetlab5'
- i=0
- while true; do
- i=$(($i+1))
- bkname=$(printf planetlab5-%03d $i)
- if check_for_database $bkname ; then
- echo "$bkname already exists - skipping"
- else
- echo "Renaming planetab5 into $bkname"
- psql --user=$PLC_DB_USER -c "ALTER DATABASE planetlab5 RENAME TO $bkname"
- echo "Done"
- fi
- done
+ rename=planetlab5_${DATE_}
+ echo -n "There is an existing DB named planetlab5, drop or rename into $rename d/[r] ? "
+ read _answer_
+ case $_answer_ in
+ d|D)
+ run "Dropping planetlab5" psql --user=postgres template1 -c "DROP DATABASE planetlab5" || true
+ ;;
+ *)
+ if check_for_database $rename ; then
+ echo "$rename already exists - exiting"
+ exit 1
+ else
+ run "Renaming planetlab5 into $rename" \
+ psql --user=postgres template1 -c "ALTER DATABASE planetlab5 RENAME TO $rename"
+ fi
+ ;;
+ esac
fi
+
+ # again: now it should not exist
if check_for_database planetlab5 ; then
- echo ERROR : FOUND planetlab5 - exiting
+ echo "ERROR : FOUND planetlab5 - should not happen - exiting"
exit 1
else
- echo OK : db planetlab5 NOT FOUND
+ echo "OK, we're clear, let's go"
fi
- # create it
- createdb --user=postgres --encoding=UNICODE --owner=$PLC_DB_USER planetlab5
- # populate it
- psql --user=$PLC_DB_USER planetlab5 < $DUMP4.sql
-
- # run coarse-grain script
- migration_script | psql --user=$PLC_DB_USER planetlab5
+ # dump planetlab4
+ if [ ! -f $FAKE ] ; then
+ run "Dumping planetlab4 in $DUMP" pg_dump --user=$PLC_DB_USER -f $DUMP planetlab4
+ else
+ echo ''
+ echo 'xxxx WARNING WARNING WARNING WARNING WARNING xxx'
+ echo ''
+ echo Using fake input for tests $FAKE
+ echo ''
+ echo 'xxxx WARNING WARNING WARNING WARNING WARNING xxx'
+ echo ''
+ DUMP=$FAKE
+ fi
+
+ run "Copying into $RESTORE" cp $DUMP $RESTORE
+ run "Renaming identifiers in $RESTORE" sed -f $MIGRATION_SED -i $RESTORE
+
+ run "Creating planetlab5 database" createdb --user=postgres --encoding=UNICODE --owner=$PLC_DB_USER planetlab5
+ run "Loading language plpgsql" createlang -U postgres plpgsql planetlab5 || true
+ run "Populating planetlab5 from $RESTORE" psql --user=postgres -f $RESTORE planetlab5
+ run "Fine-tuning it with $MIGRATION_SQL" psql --user=$PLC_DB_USER -f $MIGRATION_SQL planetlab5
+ run "Extracting views definitions from $SCHEMA_SQL" ./extract-views.py $SCHEMA_SQL $VIEWS_SQL
+ run "Inserting views definitions in planetlab5" \
+ psql --user=$PLC_DB_USER -f $VIEWS_SQL planetlab5
}
+function manage_link () {
+ dest=$1; shift
+ src=$1; shift
+ cd $DIRNAME
+ echo "Managing link $dest"
+ rm -f $dest
+ ln -s $src $dest
+}
-function migration_script () {
+function links () {
+ # tmp
+ result=${DIRNAME}/${DATE}-output.sql
+ run "Dumping result in $result" pg_dump --user=$PLC_DB_USER -f $result planetlab5
+
+ manage_link latest.log $LOG
+ manage_link latest-pl4.sql $DUMP
+ manage_link latest-pl5.sql $RESTORE
+ manage_link latest-views5.sql $VIEWS_SQL
+ manage_link latest-output.sql $result
+
+}
+
+function main () {
+
+ check
+ warning
+ set -e
+ migrate
+ links
- sql_script=$DIRNAME/${BASENAME}.sql
- if [ -f $sql_script ] ; then
- cat $sql_script
- else
- echo Cannot locate ${BASENAME}.sql
- echo exiting
- exit 1
- fi
}
+
+main "$@"
+--
-- $Id$
--
--- this is the script to migrate from 4.2 to 5.0
+-- this is part of the script to migrate from 4.2 to 5.0
--
-----------------------------------------
--- 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;
----------------------------------------
-- nodegroups
--- xxx - todo
--- a more usable migration script would need to capture more data
----------------------------------------
+--- xxx - need to capture this first
+--- xxx would dump some python script to capture current nodegroups...
+
+--- xxx would maybe like to preserve it in nodegroups_v4 or something
DROP TABLE IF EXISTS nodegroups CASCADE;
+DROP TABLE IF EXISTS nodegroup_node CASCADE;
--- Node groups
-CREATE TABLE nodegroups ...
-CREATE OR REPLACE VIEW nodegroup_node AS ...
-CREATE OR REPLACE VIEW nodegroup_nodes AS ...
-CREATE OR REPLACE VIEW node_nodegroups AS ...
-CREATE OR REPLACE VIEW view_nodegroups AS
+CREATE TABLE nodegroups (
+ nodegroup_id serial PRIMARY KEY, -- Group identifier
+ groupname text UNIQUE NOT NULL, -- Group name
+ tag_type_id integer REFERENCES tag_types, -- node is in nodegroup if it has this tag defined
+ tagvalue text NOT NULL -- with this value attached
+) WITH OIDS;
-----------------------------------------
--- the nodes view
-----------------------------------------
-DROP VIEW view_nodes;
-CREATE OR REPLACE VIEW view_nodes AS ...
+-------------
+-- display constraints
-----------------------------------------
--- ilinks
-----------------------------------------
---CREATE TABLE link_types ...
-CREATE TABLE ilink ...
+CREATE OR REPLACE VIEW 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);
-CREATE OR REPLACE VIEW ilinks AS ...
-CREATE OR REPLACE VIEW ilink_src_node AS ...
-CREATE OR REPLACE VIEW ilink_nodes AS ...
+-- cleanup
+drop view all_views;
-----------------------------------------
--- update versioning
-----------------------------------------
-UPDATE plc_db_version SET version=5, subversion=0;
+--- versioning (plc_db_version): ignore for now, so we keep both entries (v4 and v5)
git://git.onelab.eu / plcapi.git / commitdiff