From 3ae5fadbc03bfaa1eb1d21c9521ddf797ba37d31 Mon Sep 17 00:00:00 2001 From: Thierry Parmentelat Date: Thu, 4 Feb 2010 15:22:15 +0000 Subject: [PATCH] start the db from a clean slate --- migrations/011-down-site-and-person-tags.sql | 16 - migrations/011-up-site-and-person-tags.sql | 129 -------- migrations/100-up-major-to-5.sql | 9 + migrations/README.txt | 17 +- migrations/v42-to-v43/migrate.sed | 5 - migrations/v42-to-v43/migrate.sh | 223 ------------- migrations/v42-to-v43/migrate.sql | 310 ------------------ .../v42-to-v43/parse-site-nodegroups.py | 57 ---- migrations/v42-to-v43/site-nodegroups.def | 37 --- planetlab5.sql | 98 +++++- 10 files changed, 107 insertions(+), 794 deletions(-) delete mode 100644 migrations/011-down-site-and-person-tags.sql delete mode 100644 migrations/011-up-site-and-person-tags.sql create mode 100644 migrations/100-up-major-to-5.sql delete mode 100644 migrations/v42-to-v43/migrate.sed delete mode 100755 migrations/v42-to-v43/migrate.sh delete mode 100644 migrations/v42-to-v43/migrate.sql delete mode 100755 migrations/v42-to-v43/parse-site-nodegroups.py delete mode 100644 migrations/v42-to-v43/site-nodegroups.def diff --git a/migrations/011-down-site-and-person-tags.sql b/migrations/011-down-site-and-person-tags.sql deleted file mode 100644 index c70424d..0000000 --- a/migrations/011-down-site-and-person-tags.sql +++ /dev/null @@ -1,16 +0,0 @@ --- --- purpose: provide a way to delete the additions added by the up script. --- NOTE: this does not leave the DB in a usable state, since it drops the view_sites and view_persons; --- - -DROP VIEW view_site_tags; -DROP VIEW view_sites; -DROP VIEW site_tags; -DROP TABLE site_tag; - -DROP VIEW view_person_tags; -DROP VIEW view_persons; -DROP VIEW person_tags; -DROP TABLE person_tag; - -UPDATE plc_db_version SET subversion = 0; diff --git a/migrations/011-up-site-and-person-tags.sql b/migrations/011-up-site-and-person-tags.sql deleted file mode 100644 index 3dee8d0..0000000 --- a/migrations/011-up-site-and-person-tags.sql +++ /dev/null @@ -1,129 +0,0 @@ --- --- PlanetLab --- --- migration 001 --- --- purpose: provide tags on site and person objects in db --- --- - --- SITES - -CREATE TABLE site_tag ( - site_tag_id serial PRIMARY KEY, -- ID - site_id integer REFERENCES sites NOT NULL, -- site id - tag_type_id integer REFERENCES tag_types, -- tag type id - value text -- value attached -) WITH OIDS; - -CREATE OR REPLACE VIEW site_tags AS -SELECT site_id, -array_accum(site_tag_id) AS site_tag_ids -FROM site_tag -GROUP BY site_id; - -CREATE OR REPLACE VIEW view_site_tags AS -SELECT -site_tag.site_tag_id, -site_tag.site_id, -sites.login_base, -tag_types.tag_type_id, -tag_types.tagname, -tag_types.description, -tag_types.category, -tag_types.min_role_id, -site_tag.value -FROM site_tag -INNER JOIN tag_types USING (tag_type_id) -INNER JOIN sites USING (site_id); - -DROP VIEW view_sites; -CREATE OR REPLACE VIEW view_sites AS -SELECT -sites.site_id, -sites.login_base, -sites.name, -sites.abbreviated_name, -sites.deleted, -sites.enabled, -sites.is_public, -sites.max_slices, -sites.max_slivers, -sites.latitude, -sites.longitude, -sites.url, -sites.ext_consortium_id, -CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created, -CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated, -peer_site.peer_id, -peer_site.peer_site_id, -COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids, -COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids, -COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids, -COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids, -COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids, -COALESCE((SELECT site_tag_ids FROM site_tags WHERE site_tags.site_id = sites.site_id), '{}') AS site_tag_ids -FROM sites -LEFT JOIN peer_site USING (site_id); - --- PERSONS - -CREATE TABLE person_tag ( - person_tag_id serial PRIMARY KEY, -- ID - person_id integer REFERENCES persons NOT NULL, -- person id - tag_type_id integer REFERENCES tag_types, -- tag type id - value text -- value attached -) WITH OIDS; - -CREATE OR REPLACE VIEW person_tags AS -SELECT person_id, -array_accum(person_tag_id) AS person_tag_ids -FROM person_tag -GROUP BY person_id; - -CREATE OR REPLACE VIEW view_person_tags AS -SELECT -person_tag.person_tag_id, -person_tag.person_id, -persons.email, -tag_types.tag_type_id, -tag_types.tagname, -tag_types.description, -tag_types.category, -tag_types.min_role_id, -person_tag.value -FROM person_tag -INNER JOIN tag_types USING (tag_type_id) -INNER JOIN persons USING (person_id); - -DROP VIEW view_persons; -CREATE OR REPLACE VIEW view_persons AS -SELECT -persons.person_id, -persons.email, -persons.first_name, -persons.last_name, -persons.deleted, -persons.enabled, -persons.password, -persons.verification_key, -CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires, -persons.title, -persons.phone, -persons.url, -persons.bio, -CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created, -CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated, -peer_person.peer_id, -peer_person.peer_person_id, -COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS role_ids, -COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles, -COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids, -COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids, -COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids, -COALESCE((SELECT person_tag_ids FROM person_tags WHERE person_tags.person_id = persons.person_id), '{}') AS person_tag_ids -FROM persons -LEFT JOIN peer_person USING (person_id); - - -UPDATE plc_db_version SET subversion = 11; diff --git a/migrations/100-up-major-to-5.sql b/migrations/100-up-major-to-5.sql new file mode 100644 index 0000000..0d310df --- /dev/null +++ b/migrations/100-up-major-to-5.sql @@ -0,0 +1,9 @@ +-- myplc v5.0 starts with (5,100) +-- the expected former values would be (4,11) +-- +-- if you somehow start from a 4.3 not entirely up-dated to rc17, +-- then manually run +-- http://svn.planet-lab.org/svn/PLCAPI/branches/4.3/migrations/011-up-site-and-person-tags.sql +-- +UPDATE plc_db_version SET version = 5; +UPDATE plc_db_version SET subversion = 100; diff --git a/migrations/README.txt b/migrations/README.txt index f273076..0d14ca7 100644 --- a/migrations/README.txt +++ b/migrations/README.txt @@ -1,8 +1,13 @@ Store here migration scripts, named --up-any-text. -with contiguous indices starting with 001 +-up-.sql + handled as a sql script to be run against planetlab5, or +-up-.sh + which is assumed to be a shell script and is run as is -if the extension is .sql it is ran on the planetlab5 database -otherwise it is run as is - -See myplc/plc.d/db for how this is used +Another assumption is that + * nnn-up- script will set subversion number to + * nnn-down script will set subversion number to -1 + +=== +See the migration script in plc.d/db for how this is used +=== diff --git a/migrations/v42-to-v43/migrate.sed b/migrations/v42-to-v43/migrate.sed deleted file mode 100644 index c432d9d..0000000 --- a/migrations/v42-to-v43/migrate.sed +++ /dev/null @@ -1,5 +0,0 @@ -s,slice_attribute_type,tag_type,g -s,attribute_type_id,tag_type_id,g -s,nodenetwork_setting,interface_tag,g -s,slice_attribute,slice_tag,g -s,nodenetwork,interface,g diff --git a/migrations/v42-to-v43/migrate.sh b/migrations/v42-to-v43/migrate.sh deleted file mode 100755 index ccb9914..0000000 --- a/migrations/v42-to-v43/migrate.sh +++ /dev/null @@ -1,223 +0,0 @@ -#!/bin/bash - -COMMAND=$(basename $0) -BASENAME=$(basename $COMMAND .sh) -DIRNAME=$(dirname $0) -# normalize -DIRNAME=$(cd ${DIRNAME}; /bin/pwd) - -MIGRATION_SED=$DIRNAME/${BASENAME}.sed -MIGRATION_SQL=$DIRNAME/${BASENAME}.sql -# look in .. -UP=$(dirname $DIRNAME) -UPUP=$(dirname $UP) -SCHEMA_SQL=$UPUP/planetlab5.sql - -DATE=$(date +%Y-%m-%d-%H-%M) -DATE_=$(date +%Y_%m_%d_%H_%M) -LOG=${DIRNAME}/${DATE}.log -DUMP=${DIRNAME}/pl4.sql -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 - -PGM_VIEWS=$UP/extract-views.py -PGM_NODEGROUPS=$DIRNAME/parse-site-nodegroups.py - -INTERACTIVE_MODE="true" - -# load config -. /etc/planetlab/plc_config - -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 -} - -# 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 datname from pg_database where datname= '$dbname' 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 confirm_nodegroups () { - echo "========================================" - echo "$COMMAND" - echo "This script is designed to ease the migration from myplc 4.2 to 4.3" - echo "It attempts to (re)create the planetlab5 database from planetlab4" - echo "" - echo "You might wish to edit/review" - echo " $NODEGROUPS_DEF" - echo " to finetune your migration" - echo "" - echo "Please refer to http://svn.planet-lab.org/wiki/Migration4to5" - echo "========================================" - echo -n "Are you sure you want to proceed y/[n] ? " - if [ "$INTERACTIVE_MODE" = "true" ] ; then - read answer - case $answer in - y|Y) echo See log in $LOG ;; - *) echo "Bye" ; exit 1 ;; - esac - fi -} - -function check_env () { - [ -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; } -} - -# connect to the former myplc, performs a local dump of planetlab4 and creates is locally -function get_planetlab4 () { - - # for faster tests .. - if [ -f $FAKE ] ; then - 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 - elif [ -f $DUMP ] ; then - echo "Using planetlab4 from $DUMP" - else - - echo -n "Enter the hostname for the former DB service : " - if [ "$INTERACTIVE_MODE" = "true" ] ; then - read hostname - echo "Running pg_dump on $hostname.." - pg_dump --ignore-version --host=$hostname --user=$PLC_DB_USER planetlab4 -f ${DUMP} - else - pg_dump --ignore-version --user=$PLC_DB_USER planetlab4 -f ${DUMP} - fi - DUMP=$DUMP - fi -} - -function prepare_planetlab5 () { - - # check if planetlab5 exists - if check_for_database planetlab5 ; then - rename=planetlab5_${DATE_} - echo -n "There is an existing DB named planetlab5, drop or rename into $rename d/[r] ? " - if [ "$INTERACTIVE_MODE" = "true" ] ; then - read _answer_ - else - _answer_='r' - fi - 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 - should not happen - exiting" - exit 1 - fi -} - - - -function migrate () { - set -e - cd $DIRNAME - - # dump planetlab4 - - 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 "Parsing $NODEGROUPS_DEF" $PGM_NODEGROUPS $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" $PGM_VIEWS $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 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 () { - - while getopts "b" opt ; do - case $opt in - b) INTERACTIVE_MODE='false' ;; - *) - echo "migrate.sh [-b]" - echo " -b -- execute in batch mode without asking for user feedback" - exit - ;; - esac - done - - check_env - confirm_nodegroups - echo "OK, we're clear, let's go" - set -e - get_planetlab4 - prepare_planetlab5 - migrate - links - echo "See logfile $LOG for detailed log" - echo "Checking for 'error' in the logfile" - grep -i error $LOG - -} - -main "$@" diff --git a/migrations/v42-to-v43/migrate.sql b/migrations/v42-to-v43/migrate.sql deleted file mode 100644 index 2b5fb2b..0000000 --- a/migrations/v42-to-v43/migrate.sql +++ /dev/null @@ -1,310 +0,0 @@ --- Thierry Parmentelat - INRIA --- --- $Id$ --- $URL$ --- --- this is part of the script to migrate from 4.2 to 4.3 --- --- most of the renamings have taken place already when this script is invoked --- - ----------------------------------------- --- 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 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 mgn_all_views; - --- this one version almost works, but somehow does not, could not figure why -CREATE OR REPLACE FUNCTION mgn_drop_all_views () RETURNS INTEGER AS $$ - DECLARE - row mgn_all_views%ROWTYPE; - BEGIN - 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; - RETURN 0; - END; -$$ LANGUAGE 'plpgsql'; - --- SELECT mgn_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_tags 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_tags 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_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_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_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; - - ----------------------------------------- --- run levels ----------------------------------------- -CREATE TABLE run_levels ( - run_level text PRIMARY KEY -) WITH OIDS; -INSERT INTO run_levels (run_level) VALUES ('boot'); -INSERT INTO run_levels (run_level) VALUES ('safeboot'); -INSERT INTO run_levels (run_level) VALUES ('failboot'); -INSERT INTO run_levels (run_level) VALUES ('reinstall'); - - ----------------------------------------- --- node types ----------------------------------------- -CREATE TABLE node_types ( - node_type text PRIMARY KEY -) WITH OIDS; -INSERT INTO node_types (node_type) VALUES ('regular'); -INSERT INTO node_types (node_type) VALUES ('dummynet'); - ----------------------------------------- --- nodes ----------------------------------------- -ALTER TABLE nodes ADD COLUMN node_type TEXT REFERENCES node_types DEFAULT 'regular'; -- node types -ALTER TABLE nodes ALTER COLUMN boot_state SET DEFAULT 'reinstall'; -- boot_states changed in planetlab5.sql -ALTER TABLE nodes ADD COLUMN verified boolean NOT NULL DEFAULT false; -- whether or not the node & pcu are verified -ALTER TABLE nodes ADD COLUMN run_level TEXT REFERENCES run_levels DEFAULT NULL; -- Node Run Level - ----------------------------------------- --- 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 former nodenetwork_setting_types -INSERT INTO tag_types (tagname,description,min_role_id,category) - SELECT name,description,min_role_id,'interface/legacy' FROM interface_tag_types; - ----------- 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_tag_types.interface_tag_type_id AS old_index, - tag_types.tag_type_id AS new_index - FROM - interface_tag_types INNER JOIN tag_types - ON interface_tag_types.name = tag_types.tagname; - --- 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_tag - SET interface_tag_type_id = - (select new_index from mgn_setting_renumber where old_index=interface_tag_type_id); - --- alter column name to reflect change -ALTER TABLE interface_tag RENAME interface_tag_type_id TO tag_type_id; - --- 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_tag_types altogether -drop view mgn_setting_renumber; -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; - - ----------------------------------------- --- ilinks ----------------------------------------- -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; - ----------------------------------------- --- 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, value) - --- 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, --- 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, 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 value TEXT; - -CREATE OR REPLACE VIEW mgn_nodegroups AS - 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 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); - ---- 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; - - ----------------------------------------- --- 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 ('reinstall'); - --- map old ones --- b/c boot_states are declared by users not reported by node. -UPDATE nodes SET boot_state='safeboot' 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='reinstall' 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='reinstall' 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='install'; -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) diff --git a/migrations/v42-to-v43/parse-site-nodegroups.py b/migrations/v42-to-v43/parse-site-nodegroups.py deleted file mode 100755 index 1405ca0..0000000 --- a/migrations/v42-to-v43/parse-site-nodegroups.py +++ /dev/null @@ -1,57 +0,0 @@ -#!/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%s)\s+(?P%s)\s+(?P%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, value 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,value]=[normalize(x) for x in match.groups()] - - print >> outfile, \ -"INSERT INTO mgn_site_nodegroup (groupname,tagname,value) VALUES (%s,%s,%s);"%\ -(groupname,tagname,value) - 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() diff --git a/migrations/v42-to-v43/site-nodegroups.def b/migrations/v42-to-v43/site-nodegroups.def deleted file mode 100644 index 2d094a2..0000000 --- a/migrations/v42-to-v43/site-nodegroups.def +++ /dev/null @@ -1,37 +0,0 @@ -# this file allows you to plan the migration of your nodegroups -# -# groupname tagname value -# -# single quotes can be used -# although tagnames can have spaces, it is not recommended -# if use of accessors is planned -# -# catured on private.one-lab.org -onelab-i386 arch i386 -x86_64 arch x86_64 -umts extension umts -# ### captured on PLE -# alpha deployment alpha -# onelab-x86_64 arch x86_64 -# onelab-i386 arch i386 -# ### captured on PLC -# Alpha deployment alpha -# Beta deployment beta -# Nightly Nightly true -# Internet2 Internet2 true -# V2 V2 true -# v3 v3 true -# Uppsala Uppsala true -# LogWatch LogWatch true -# DC7800Deployment DC7800Deployment true -# AlphaDeployment AlphaDeployment true -# debug.sh.sgn debug.sh.sgn true -# hdboot-serial.sh.sgn hdboot-serial.sh.sgn true -# 'Shark NFS' 'Shark NFS' true -# cluster cluster true -# IRIS IRIS true -# 'NM prototype' 'NM prototype' true -# 'IRIS + NM prototype' 'IRIS + NM prototype' true -# 'JHU honeypot' 'JHU honeypot' true -# chopstix chopstix true -# PolishTelecom PolishTelecom true diff --git a/planetlab5.sql b/planetlab5.sql index fc39535..1ae9aef 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -28,7 +28,14 @@ CREATE TABLE plc_db_version ( subversion integer NOT NULL DEFAULT 0 ) WITH OIDS; -INSERT INTO plc_db_version (version, subversion) VALUES (5, 0); +-- the migration scripts do not use the major 'version' number +-- so 5.0 sets subversion at 100 +-- in case your database misses the site and persons tags feature, +-- you might wish to first upgrade to 4.3-rc16 before moving to some 5.0 +-- or run the up script here +-- http://svn.planet-lab.org/svn/PLCAPI/branches/4.3/migrations/ + +INSERT INTO plc_db_version (version, subversion) VALUES (5, 100); -------------------------------------------------------------------------------- -- Aggregates and store procedures @@ -43,6 +50,18 @@ CREATE AGGREGATE array_accum ( initcond = '{}' ); +-------------------------------------------------------------------------------- +-- The building block for attaching tags +-------------------------------------------------------------------------------- +CREATE TABLE tag_types ( + + tag_type_id serial PRIMARY KEY, -- ID + tagname text UNIQUE NOT NULL, -- Tag Name + description text, -- Optional Description + 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; + -------------------------------------------------------------------------------- -- Accounts -------------------------------------------------------------------------------- @@ -73,6 +92,37 @@ CREATE TABLE persons ( ) WITH OIDS; CREATE INDEX persons_email_idx ON persons (email); +-------------------------------------------------------------------------------- +-- person tags +-------------------------------------------------------------------------------- +CREATE TABLE person_tag ( + person_tag_id serial PRIMARY KEY, -- ID + person_id integer REFERENCES persons NOT NULL, -- person id + tag_type_id integer REFERENCES tag_types, -- tag type id + value text -- value attached +) WITH OIDS; + +CREATE OR REPLACE VIEW person_tags AS +SELECT person_id, +array_accum(person_tag_id) AS person_tag_ids +FROM person_tag +GROUP BY person_id; + +CREATE OR REPLACE VIEW view_person_tags AS +SELECT +person_tag.person_tag_id, +person_tag.person_id, +persons.email, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +person_tag.value +FROM person_tag +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN persons USING (person_id); + -------------------------------------------------------------------------------- -- Sites -------------------------------------------------------------------------------- @@ -132,6 +182,38 @@ array_accum(person_id) AS person_ids FROM person_site GROUP BY site_id; +-------------------------------------------------------------------------------- +-- site tags +-------------------------------------------------------------------------------- + +CREATE TABLE site_tag ( + site_tag_id serial PRIMARY KEY, -- ID + site_id integer REFERENCES sites NOT NULL, -- site id + tag_type_id integer REFERENCES tag_types, -- tag type id + value text -- value attached +) WITH OIDS; + +CREATE OR REPLACE VIEW site_tags AS +SELECT site_id, +array_accum(site_tag_id) AS site_tag_ids +FROM site_tag +GROUP BY site_id; + +CREATE OR REPLACE VIEW view_site_tags AS +SELECT +site_tag.site_tag_id, +site_tag.site_id, +sites.login_base, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +site_tag.value +FROM site_tag +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN sites USING (site_id); + -------------------------------------------------------------------------------- -- Mailing Addresses -------------------------------------------------------------------------------- @@ -322,14 +404,6 @@ GROUP BY site_id; -------------------------------------------------------------------------------- -- node tags -------------------------------------------------------------------------------- -CREATE TABLE tag_types ( - - tag_type_id serial PRIMARY KEY, -- ID - tagname text UNIQUE NOT NULL, -- Tag Name - description text, -- Optional Description - 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 ( node_tag_id serial PRIMARY KEY, -- ID @@ -1026,7 +1100,8 @@ COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = perso COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles, COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids, COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids, -COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids +COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids, +COALESCE((SELECT person_tag_ids FROM person_tags WHERE person_tags.person_id = persons.person_id), '{}') AS person_tag_ids FROM persons LEFT JOIN peer_person USING (person_id); @@ -1174,7 +1249,8 @@ COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids, COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids, COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids, -COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids +COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids, +COALESCE((SELECT site_tag_ids FROM site_tags WHERE site_tags.site_id = sites.site_id), '{}') AS site_tag_ids FROM sites LEFT JOIN peer_site USING (site_id); -- 2.43.0