From f7a48ab901d1133508db4494564e4ecba629fe8c Mon Sep 17 00:00:00 2001 From: Thierry Parmentelat Date: Fri, 5 Sep 2008 15:04:49 +0000 Subject: [PATCH] first draft for migrating DB from v4 to v5 - nodegroups not handled properly yet --- migrations/extract-views.py | 57 ++++++++ migrations/migrate-v4-to-v5.sed | 3 + migrations/migrate-v4-to-v5.sh | 175 ++++++++++++++++------ migrations/migrate-v4-to-v5.sql | 248 ++++++++++++++++++++++---------- planetlab5.sql | 7 +- 5 files changed, 371 insertions(+), 119 deletions(-) create mode 100755 migrations/extract-views.py create mode 100644 migrations/migrate-v4-to-v5.sed diff --git a/migrations/extract-views.py b/migrations/extract-views.py new file mode 100755 index 0000000..0daed51 --- /dev/null +++ b/migrations/extract-views.py @@ -0,0 +1,57 @@ +#!/usr/bin/env python + +import sys +import re + +class Schema: + + def __init__ (self,input,output=None): + self.input=input + self.output=output + + # left part is non-greedy + comment=re.compile("(.*?)--.*") + spaces=re.compile("^\s+(\S.*)") + view=re.compile("(?i)\s*create\s+(or\s+replace)?\s+view.*") + + def parse (self): + if self.output: + outfile = open(self.output,"w") + else: + outfile = sys.stdout + contents = file(self.input).read() + parts=contents.split(";") + for part in parts: + # normalize: remove comments, linebreaks, trailing spaces.. + normalized='' + lines=part.split('\n'); + out_lines=[] + for line in lines: + # remove comment + match=Schema.comment.match(line) + if match: + line=match.group(1) + out_lines.append(line) + # get them together + out_line = " ".join(out_lines) + # remove trailing spaces + match=Schema.spaces.match(out_line) + if match: + out_line=match.group(1) + match=Schema.view.match(out_line) + if match: + print >>outfile, out_line,';' + if outfile != sys.stdout: + outfile.close() + +if __name__ == '__main__': + if len(sys.argv) not in [2,3]: + print 'Usage:',sys.argv[0],'input [output]' + sys.exit(1) + input=sys.argv[1] + try: + output=sys.argv[2] + except: + output=None + Schema(input,output).parse() + diff --git a/migrations/migrate-v4-to-v5.sed b/migrations/migrate-v4-to-v5.sed new file mode 100644 index 0000000..76a4263 --- /dev/null +++ b/migrations/migrate-v4-to-v5.sed @@ -0,0 +1,3 @@ +s,nodenetwork,interface,g +s,slice_attribute_type,tag_type,g +s,attribute_type_id,tag_type_id,g diff --git a/migrations/migrate-v4-to-v5.sh b/migrations/migrate-v4-to-v5.sh index b854c79..f7b2d4c 100755 --- a/migrations/migrate-v4-to-v5.sh +++ b/migrations/migrate-v4-to-v5.sh @@ -3,80 +3,169 @@ 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 "$@" diff --git a/migrations/migrate-v4-to-v5.sql b/migrations/migrate-v4-to-v5.sql index 31027b2..75a6886 100644 --- a/migrations/migrate-v4-to-v5.sql +++ b/migrations/migrate-v4-to-v5.sql @@ -1,87 +1,189 @@ +-- -- $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) diff --git a/planetlab5.sql b/planetlab5.sql index 7219d9b..c389da3 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -1,11 +1,12 @@ -- -- PlanetLab Central database schema --- Version 4, PostgreSQL +-- Version 5, PostgreSQL -- -- Aaron Klingaman -- Reid Moran -- Mark Huang -- Tony Mack +-- Thierry Parmentelat -- -- Copyright (C) 2006 The Trustees of Princeton University -- @@ -295,8 +296,8 @@ CREATE TABLE tag_types ( tag_type_id serial PRIMARY KEY, -- ID tagname text UNIQUE NOT NULL, -- Tag Name description text, -- Optional Description - category text NOT NULL DEFAULT 'general', -- Free text for grouping tags together - min_role_id integer REFERENCES roles DEFAULT 10 -- set minimal role required + min_role_id integer REFERENCES roles DEFAULT 10, -- set minimal role required + category text NOT NULL DEFAULT 'general' -- Free text for grouping tags together ) WITH OIDS; CREATE TABLE node_tag ( -- 2.43.0