--- /dev/null
+#!/usr/bin/env python
+
+import sys
+import re
+
+class Nodegroups:
+
+ def __init__ (self,input,output=None):
+ self.input=input
+ self.output=output
+
+ # strip off comments
+ comment=re.compile("\s*#.*")
+ id="[\w\.-]+|\'[^\']+\'"
+ id3="\s*(?P<groupname>%s)\s+(?P<tagname>%s)\s+(?P<tagvalue>%s\s*)"%(id,id,id)
+ line=re.compile(id3)
+
+ def parse (self):
+ if self.output:
+ outfile = open(self.output,"w")
+ else:
+ outfile = sys.stdout
+ lineno=0
+ print >> outfile, """
+CREATE TABLE mgn_site_nodegroup (groupname text, tagname text, tagvalue text);
+"""
+ for line in file(self.input).readlines():
+ lineno += 1
+ if Nodegroups.comment.match(line):
+ continue
+ match=Nodegroups.line.match(line)
+ if not match:
+ print "%s:%s:%d: syntax error %s"%(
+ sys.argv[0],self.input,lineno,line)
+ sys.exit(1)
+ def normalize (id):
+ if id.find("'")==0:
+ return id
+ return "'%s'"%id
+ [groupname,tagname,tagvalue]=[normalize(x) for x in match.groups()]
+
+ print >> outfile, \
+"INSERT INTO mgn_site_nodegroup (groupname,tagname,tagvalue) VALUES (%s,%s,%s);"%\
+(groupname,tagname,tagvalue)
+ 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
+ Nodegroups(input,output).parse()
RESTORE=${DIRNAME}/${DATE}-pl5.sql
FAKE=${DIRNAME}/input-pl4.sql
VIEWS_SQL=$DIRNAME/${DATE}-views5.sql
+NODEGROUPS_DEF=$DIRNAME/site-nodegroups.def
+NODEGROUPS_SQL=$DIRNAME/${DATE}-nodegroups.sql
# 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 > /dev/null 2>&1
+ psql --user=$PLC_DB_USER --quiet -c "SELECT datname from pg_database where datname= '$dbname' LIMIT 1" $dbname > /dev/null 2>&1
return $?
}
# 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 warning () {
+ echo "========================================"
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 ""
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 ""
+ echo "You might wish to edit/review $NODEGROUPS_DEF to finetune your migration"
+ echo "========================================"
echo -n "Are you sure you want to proceed y/[n] ? "
read answer
case $answer in
[ -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; }
+ [ -f $NODEGROUPS_DEF ] || { echo $NODEGROUPS_DEF not found - exiting ; exit 1; }
}
function run () {
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 "Parsing $NODEGROUPS_DEF" $DIRNAME/v4-to-v5-nodegroups.py $NODEGROUPS_DEF $NODEGROUPS_SQL
+ run "Loading $NODEGROUPS_SQL" psql --user=$PLC_DB_USER -f $NODEGROUPS_SQL 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" \
---
+-- Thierry Parmentelat - INRIA
+--
-- $Id$
--
-- this is part of the script to migrate from 4.2 to 5.0
-- the views will be reinstantiated later upon loading of planetlab5.sql
-- this lists all views
-CREATE OR REPLACE VIEW all_views AS
+CREATE OR REPLACE VIEW mgn_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;
+select * from mgn_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 $$
+CREATE OR REPLACE FUNCTION mgn_drop_all_views () RETURNS INTEGER AS $$
DECLARE
- row all_views%ROWTYPE;
+ row mgn_all_views%ROWTYPE;
BEGIN
- FOR row IN SELECT * FROM all_views where relname != 'all_views' LOOP
+ FOR row IN SELECT * FROM mgn_all_views where relname != 'mgn_all_views' LOOP
RAISE NOTICE 'Dropping %',row.relname;
EXECUTE 'DROP VIEW ' || row.relname || ' CASCADE' ;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
--- SELECT drop_all_views();
+-- SELECT mgn_drop_all_views();
-- so let's have it the boring way
DROP VIEW address_address_types CASCADE;
-- caught by some previous cascade -- DROP VIEW view_slices CASCADE;
-- shows in logfile
-select * from all_views;
+select * from mgn_all_views;
-------------------- TAG TYPES
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;
+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
+CREATE OR REPLACE VIEW mgn_setting_renumber AS
SELECT
interface_setting_types.interface_setting_type_id AS old_index,
tag_types.tag_type_id AS new_index
-- do the transcoding
UPDATE interface_setting
SET interface_setting_type_id =
- (select new_index from index_renumber where old_index=interface_setting_type_id);
+ (select new_index from mgn_setting_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;
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 view mgn_setting_renumber;
drop table interface_setting_types;
-------------------- NEW STUFF
----------------------------------------
-- nodegroups
----------------------------------------
---- 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;
-
-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;
+---------- 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)
+
+-- rename column
+ALTER TABLE nodegroups RENAME name TO groupname;
+
+---------- create missing tag types
+-- change default for the entries about to be created
+ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'nodegroup/migration';
+
+-- do it
+INSERT INTO tag_types (tagname)
+ 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..
+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
+ 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;
+
+CREATE OR REPLACE VIEW mgn_nodegroups AS
+ SELECT groupname, tag_types.tag_type_id, mgn_site_nodegroup.tagvalue
+ 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);
+
+-- 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 all_constraints AS
+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);
--- cleanup
-drop view all_views;
+--- change default now that the column is filled
+ALTER TABLE tag_types ALTER COLUMN category SET DEFAULT 'general';
+
+-- cleanup the nodegroup area
+drop view mgn_nodegroups;
+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 ();
--- versioning (plc_db_version): ignore for now, so we keep both entries (v4 and v5)
git://git.onelab.eu / plcapi.git / commitdiff