[OSSTEST PATCH 09/16] mg-schema-test-database: New script

[Ian Jackson <ian.jackson@eu.citrix.com>]

This allows a user in non-standalone mode to make a whole new test
database, which is largely a clone of the original database.

The new db refers to the same resources (hosts), and more-or-less
safely borrows some of those hosts.

Currently we don't do anything about the queue and owner daemons.
This means that queue-daemon-based resource allocation is broken when
clients are pointed at the test db.  But non-queue-based allocation
(eg, ./mg-allocate without -U) works, and the test db can be used for
db-related experiments and even support individual ts-* scripts (other
than ts-hosts-allocate of course).

Signed-off-by: Ian Jackson <Ian.Jackson@eu.citrix.com>
---
v2: Do not set *Daemon{Host,Port} - move this chunk to a later patch
---
 mg-schema-test-database |  452 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 452 insertions(+)
 create mode 100755 mg-schema-test-database

diff --git a/mg-schema-test-database b/mg-schema-test-database
new file mode 100755
index 0000000..1226761
--- /dev/null
+++ b/mg-schema-test-database
@@ -0,0 +1,452 @@
+#!/bin/bash
+#
+# usages:
+#
+#
+#  ./mg-schema-test-database create [_SUFFIX] [TASK...] \
+#		[-fMINFLIGHT | -f-NUMFLIGHTS]
+#
+# does `drop' and then creates
+#   - the database    osstestdb_test_SUFFIX
+#   - a file          local-config.test-database_SUFFIX
+#
+# default for SUFFIX is your local username
+#
+# Resources owned in the main db by a task in the list of specified
+# TASKs become idle in the test copy.  Others become allocated to
+# a specially-created `owned by someone in real db' task.
+#
+#
+#  ./mg-schema-test-database drop [_SUFFIX]
+#
+# deletes your test database and removes the local-config file
+#
+#
+
+set -e -o posix ${OSSTEST_DEBUG:+-x}
+
+. ./cri-getconfig
+. ./mgi-common
+
+if [ $# -lt 1 ]; then fail "need operation"; fi
+
+cmd="$1"; shift
+
+localconfig=local-config.test-database
+
+maindbname=$(perl -we '
+	use Osstest;
+	use Osstest::Executive;
+	use DBI;
+	csreadconfig();
+	print $dbh_tests->{pg_db},"\n"
+	     or die $!;
+')
+
+parse_only_suffix () {
+	for arg in "$@"; do
+		case "$arg" in
+		_*)	suffix="$arg" ;;
+		*)	fail 'bad usage' ;;
+		esac
+	done
+}
+
+dbname () {
+	dbname="${maindbname}_test$suffix"
+	t="tmp/testdb$suffix"
+	tcfg=local-config.test-database$suffix
+}
+
+psql_query_internal () {
+	$(get_psql_cmd) -At -R' ' -f- "$@"
+}
+
+psql_query () {
+        if [ x$OSSTEST_DEBUG != x ]; then
+		tee /dev/stderr | psql_query_internal "$@"
+	else
+		psql_query_internal "$@"
+	fi
+}
+
+psql_do_cmd () {
+	echo "$(get_psql_cmd) ${OSSTEST_DEBUG:+-e -a}" 
+}
+
+psql_do () {
+	$(psql_do_cmd) -q -f- "$@"
+}
+
+moretasks () {
+	local ifnone="$1"; shift
+	local where="$1"; shift
+	local r
+	r=$(
+		psql_query "$@" <<END
+			SELECT taskid
+			  FROM tasks
+			 $where
+END
+	)
+	if [ "x$r" = x ]; then
+		local m="no tasks matched \`$arg'"
+		case $ifnone in
+		error)
+			fail "error: $m"
+			;;
+		warning)
+			echo >&2 "warning: $m"
+			;;
+		*)
+			fail-bad-moretasks-ifnone-"$ifnone"
+			;;
+		esac
+	fi
+
+	tasks+=" $r"
+}
+
+withtest () {
+	OSSTEST_CONFIG="$test_cfg_setting" "$@"
+}
+
+each_copy_table () {
+	local tab=$1; shift
+	local cond=$1; shift
+
+	p=$t.tabledata.$tab
+	rm -f $p
+
+	cat <<END >>$t.export
+		\\COPY (SELECT * FROM $tab WHERE $cond) TO $p $copyhow
+END
+	cat <<END >>$t.import
+		\\COPY $tab FROM $p $copyhow
+END
+}
+
+make_xdbref_task () {
+	local refkey=$1; shift
+	local comment=$1; shift
+	local refinfo=$1; shift
+	echo "
+		INSERT INTO tasks
+			(type, refkey, username, comment, live, refinfo)
+		  VALUES ('xdbref','$refkey','$username@$nodename',
+			  '$comment','t','$refinfo');
+	"
+}
+
+taskid () {
+	local type=$1; shift
+	local refkey=$1; shift
+	local xcond=$1
+	echo "(SELECT taskid FROM tasks WHERE
+		type='$type' AND refkey='$refkey' $xcond)"
+}
+borrowtaskid () {
+	local bt=$1
+	taskid xdbref $dbname "
+		AND live AND refinfo IS NOT NULL AND refinfo='$bt'
+	"
+}
+
+username=`whoami`
+nodename=`uname -n`
+suffix=_$username
+invocation_now=`date +%s`
+
+case "$cmd" in
+
+#========== CREATE ==========
+
+create)
+	#---------- argument parsing ----------
+
+	tasks=''
+	minflight=-1000
+	for arg in "$@"; do
+		case "$arg" in
+		*@*)
+			moretasks warning			\
+				"WHERE type = 'static'
+				   AND refkey LIKE :'pattern'"	\
+				-v pattern="${arg//\*/%}"	\
+			;;
+		*" "*" "*)
+			local rhs="${arg#* }"
+			moretasks error				\
+				"WHERE taskid = :'taskid'
+				   AND type = :'type'
+				   AND refkey = :'refkey'"	\
+				-v taskid="${arg%% *}"		\
+				-v type="${rhs%% *}"		\
+				-v refkey="${rhs#* }"
+			;;
+		_)	suffix="$arg"
+			;;
+		-f*)	minflight="${arg#-f}"
+			;;
+		*)	fail "bad arg to create"
+			;;
+		esac
+	done
+
+	if [ "x$tasks" = x ]; then
+		moretasks error					\
+			"WHERE type = 'static'
+			   AND refkey = :'refkey'"		\
+			-v refkey="$(whoami)@$(uname -n)"
+	fi
+
+	tasks_cond=${tasks// / OR T=}
+	tasks_cond=${tasks_cond# OR }
+
+	case "$minflight" in
+	-*)
+		minflight=$( psql_query <<END
+                        SELECT flight FROM
+                                (SELECT flight FROM flights
+                                        ORDER BY flight DESC
+                                        LIMIT ${minflight#-})
+				AS last
+                                ORDER BY FLIGHT ASC
+                                LIMIT 1
+END
+		)
+		;;
+	esac
+
+	#---------- preparation and data-gathering ----------
+
+	bad=$(	psql_query <<END
+		SELECT * FROM tasks WHERE (${tasks_cond//T/taskid})
+				AND NOT live
+END
+	)
+	case "$bad" in
+	*[0-9]*)
+		fail "Borrowing from NON-LIVE TASKS $bad"
+		;;
+	esac
+
+	# drop any previous test db
+	"$0" drop $suffix
+
+	dbname
+
+	printf "Setting up %s (minflight=%d, tasks=%s)...\n" \
+		$dbname "$minflight" "${tasks# }"
+
+	# create the config overlay
+	perl >$tcfg.tmp -we '
+		use Osstest;
+		use Osstest::Executive;
+		use DBI;
+		csreadconfig();
+		print "ExecutiveDbname_osstestdb ".
+			"dbname='$dbname';".
+			"host=$dbh_tests->{pg_host};".
+			"user=$dbh_tests->{pg_user};".
+			"port=$dbh_tests->{pg_port}\n"
+			or die $!;
+	'
+	mv -f $tcfg.tmp $tcfg
+
+	# this makes `withtest' work
+	test_cfg_setting="$(perl -we '
+			use Osstest;
+			print globalconfigfiles() or die $!;
+		'):$tcfg"
+
+	# Extract the schema for reference
+	$(get_pgdump_cmd) -s -O -x >$t.schema
+
+	# Keep a copy as it came from dump, for comparison
+	cp $t.schema $t.schema.orig
+
+	# http://www.postgresql.org/message-id/26790.1306355327@sss.pgh.pa.us
+	perl -i~ -pe '
+		s/^/--/ if
+			m/^CREATE EXTENSION IF NOT EXISTS plpgsql / ||
+			m/^COMMENT ON EXTENSION plpgsql /;
+	' $t.schema
+
+	printf "Tables:"
+
+	# What tables are there ?
+	perl -ne <$t.schema >$t.tablevars '
+		if (m/^CREATE SEQUENCE (\w+)/) {
+			print "sequences+=\" $1\"\n";
+		} elsif (m/^CREATE TABLE (\w+)/) {
+			$table=$1;
+		} elsif (m/^\s*flight\s+integer/) {
+			print "ftables+=\" $table\"\n";
+		} elsif ($table && m/^\)\;$/) {
+			print "tables+=\" $table\"\n";
+		}
+	'
+
+	. $t.tablevars
+
+	>$t.tablesortlist
+
+	for table in $tables; do
+		LC_MESSAGES=C $(get_psql_cmd) <<END >$t.display.$table
+			\d $table
+END
+		echo >>$t.tablesortlist "$table $table"
+		perl -ne <$t.display.$table >>$t.tablesortlist '
+			next unless m/^Foreign-key constraints:/ ... m/^\S/;
+			next if m/DEFERRABLE/;
+			next unless m/FOREIGN KEY.*REFERENCES (\w+)/;
+			print "$1 '"$table"'\n" or die $!;
+		'
+		printf " $table"
+	done
+
+	tables=$(tsort <$t.tablesortlist)
+
+	# We don't want to set the permissions
+	perl <executive-postgresql-schema >$t.new-schema -pe '
+		s/^/--/ if
+			m/^ALTER TABLE .* OWNER TO / ||
+			m/^GRANT |^REVOKE /
+	'
+
+	#---------- create test db ----------
+
+	psql_do <<END
+		CREATE DATABASE $dbname;
+END
+	$(withtest get_psql_cmd) -q -f $t.new-schema
+
+	printf ".\n"
+
+	# Schema should now be identical to main DB
+	$(withtest get_pgdump_cmd) -s -O -x >$t.schema.created
+	diff -u $t.schema.orig $t.schema.created
+
+	#---------- mark resources that we are going to borrow ----------
+
+	for task in $tasks; do
+		psql_do <<END
+			BEGIN;
+			$(make_xdbref_task $dbname 'borrowed for test db' $task)
+			UPDATE resources SET owntaskid = $(borrowtaskid $task)
+				WHERE owntaskid=$task;
+			COMMIT;
+END
+	done
+
+	#---------- copy data from live to test db ----------
+
+	copyhow="CSV HEADER NULL e'\\\\n'"
+
+	rm -f $t.import $t.export
+
+	cat >>$t.import <<END
+		\o $t.import-output
+		BEGIN;
+		SET CONSTRAINTS ALL DEFERRED;
+END
+
+	$(get_pgdump_cmd) -a -O -x ${sequences// / -t } >$t.sequences-import
+	perl <$t.sequences-import >>$t.import -ne '
+		next if m/^--/;
+		next if m/^SET /;
+		next unless m/\S/;
+		print or die $!;
+	'
+
+	for table in $tables; do
+		case " $ftables " in
+		*" $table "*)	condition="flight >= $minflight" ;;
+		*)		condition="1=1" ;;
+		esac
+		each_copy_table $table "$condition"
+	done
+
+	# As we copy, we note everything we're not borrowing as
+	# belonging to the parent db.
+	cat >>$t.import <<END
+		$(make_xdbref_task $maindbname 'not borrowed' '')
+		UPDATE resources
+			SET owntaskid = $(taskid xdbref $maindbname)
+			WHERE owntaskid != $(borrowtaskid $task);
+		COMMIT;
+END
+
+	printf "Copy..."
+
+	printf "export..."
+	$(psql_do_cmd) -f $t.export
+
+	printf "import..."
+	$(withtest psql_do_cmd) -f $t.import
+
+	rm -f $t.tabledata.*
+
+	#---------- actually borrow resources ----------
+
+	printf "borrow..."
+
+	for task in $tasks; do
+		withtest psql_do <<END
+			BEGIN;
+			UPDATE resources
+				SET owntaskid = $(taskid magic idle)
+				WHERE owntaskid = $(borrowtaskid $task);
+			COMMIT;
+END
+	done
+	withtest psql_do <<END
+		DELETE FROM tasks
+			WHERE type='xdbref' AND refkey='$dbname';
+END
+
+	printf "\n"
+
+	cat <<END
+Test database $dbname now set up.
+export OSSTEST_CONFIG=$test_cfg_setting
+END
+	;;
+
+#========== DROP ==========
+
+drop)
+	parse_only_suffix "$@"
+
+	dbname
+
+	printf "Dropping %s.\n" "$dbname"
+
+	psql_do <<END
+                SET client_min_messages = WARNING;
+		DROP DATABASE IF EXISTS $dbname;
+		UPDATE resources
+			SET owntaskid = CAST(tasks.refinfo AS INTEGER)
+			FROM tasks
+			WHERE resources.owntaskid = tasks.taskid
+                          AND tasks.type = 'xdbref'
+			  AND tasks.refkey = '$dbname'
+			  AND tasks.live
+			  AND tasks.refinfo IS NOT NULL;
+		UPDATE tasks
+			SET live = 'f'
+			WHERE tasks.type = 'xdbref'
+			  AND tasks.refkey = '$dbname';
+END
+
+	rm -f $tcfg
+
+	;;
+
+#========== EPILOGUE ==========
+
+*)
+	fail "unknown operation \`$cmd'"
+	;;
+esac
-- 
1.7.10.4