+++ /dev/null
---
--- 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;
+++ /dev/null
---
--- 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;
--- /dev/null
+-- 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;
Store here migration scripts, named
-<nnn>-up-any-text.<ext>
-with contiguous indices starting with 001
+<nnn>-up-<any-text>.sql
+ handled as a sql script to be run against planetlab5, or
+<nnn>-up-<any-text>.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>
+ * nnn-down script will set subversion number to <nnn>-1
+
+===
+See the migration script in plc.d/db for how this is used
+===
+++ /dev/null
-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
+++ /dev/null
-#!/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 "$@"
+++ /dev/null
--- 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)
+++ /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<value>%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()
+++ /dev/null
-# 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
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
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
--------------------------------------------------------------------------------
) 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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- 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
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);
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);
git://git.onelab.eu / plcapi.git / commitdiff