Eli Mesika has uploaded a new change for review.
Change subject: core: Adding cleandb.sh script
......................................................................
core: Adding cleandb.sh script
The script enables to drop all database objects installed by oVirt and
leaving a clean database.
Basic usage:
cleandb -u <user> -d <database>
Change-Id: I7ff33e4ccb8cb66bbe06f5d1c8a00f22d28a2e31
Signed-off-by: Eli Mesika <[email protected]>
---
A packaging/dbscripts/cleandb.sh
M packaging/dbscripts/common_sp.sql
M packaging/dbscripts/dbfunctions.sh
3 files changed, 96 insertions(+), 0 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/32/16832/1
diff --git a/packaging/dbscripts/cleandb.sh b/packaging/dbscripts/cleandb.sh
new file mode 100755
index 0000000..a21ae0d
--- /dev/null
+++ b/packaging/dbscripts/cleandb.sh
@@ -0,0 +1,55 @@
+#!/bin/bash
+
+################################################################################
+# Cleans DB by dropping all DB objects
+################################################################################
+
+#include db general functions
+pushd $(dirname ${0})>/dev/null
+source ./dbfunctions.sh
+source ./dbcustomfunctions.sh
+
+#setting defaults
+set_defaults
+
+usage() {
+ printf "Usage: ${ME} [-h] [-s SERVERNAME] [-p PORT] [-d DATABASE] [-u
USERNAME] [-l LOGFILE] [-v]\n"
+ printf "\n"
+ printf "\t-s SERVERNAME - The database servername for the database (def.
${SERVERNAME})\n"
+ printf "\t-p PORT - The database port for the database (def.
${PORT})\n"
+ printf "\t-d DATABASE - The database name (def.
${DATABASE})\n"
+ printf "\t-u USERNAME - The username for the database (def.
engine)\n"
+ printf "\t-l LOGFILE - The logfile for capturing output (def.
${LOGFILE}\n"
+ printf "\t-v - Turn on verbosity (WARNING: lots of output)\n"
+ printf "\t-h - This help text.\n"
+ printf "\n"
+ popd>/dev/null
+ exit $ret
+}
+
+DEBUG () {
+ if $VERBOSE; then
+ printf "DEBUG: $*"
+ fi
+}
+
+while getopts hs:d:u:p:lv option; do
+ case $option in
+ s) SERVERNAME=$OPTARG;;
+ p) PORT=$OPTARG;;
+ d) DATABASE=$OPTARG;;
+ u) USERNAME=$OPTARG;;
+ l) LOGFILE=$OPTARG;;
+ v) VERBOSE=true;;
+ h) ret=0 && usage;;
+ \?) ret=1 && usage;;
+ esac
+done
+
+echo "Cleaning database..."
+cleandb
+
+ret=$?
+printf "Done.\n"
+popd>/dev/null
+exit $ret
diff --git a/packaging/dbscripts/common_sp.sql
b/packaging/dbscripts/common_sp.sql
index d2374f4..3495052 100644
--- a/packaging/dbscripts/common_sp.sql
+++ b/packaging/dbscripts/common_sp.sql
@@ -296,6 +296,19 @@
END; $procedure$
LANGUAGE plpgsql;
+Create or replace FUNCTION generate_drop_all_tables_syntax() RETURNS SETOF text
+ AS $procedure$
+BEGIN
+RETURN QUERY select 'DROP TABLE if exists ' || table_name || ' CASCADE;' from
information_schema.tables where table_schema = 'public' and table_type = 'BASE
TABLE' order by table_name;
+END; $procedure$
+LANGUAGE plpgsql;
+
+Create or replace FUNCTION generate_drop_all_seq_syntax() RETURNS SETOF text
+ AS $procedure$
+BEGIN
+RETURN QUERY select 'DROP SEQUENCE if exists ' || sequence_name || ' CASCADE;'
from information_schema.sequences where sequence_schema = 'public' order by
sequence_name;
+END; $procedure$
+LANGUAGE plpgsql;
Create or replace FUNCTION fn_get_column_size( v_table varchar(64), v_column
varchar(64)) returns integer
AS $procedure$
diff --git a/packaging/dbscripts/dbfunctions.sh
b/packaging/dbscripts/dbfunctions.sh
index 7ce554c..4032233 100755
--- a/packaging/dbscripts/dbfunctions.sh
+++ b/packaging/dbscripts/dbfunctions.sh
@@ -68,6 +68,34 @@
return $retval
}
+#cleans db by dropping all objects
+cleandb() {
+# common stored procedures are executed first (for new added functions to be
valid)
+execute_file "common_sp.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null
+ CMD="select * from generate_drop_all_seq_syntax();"
+ execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} > drop_all_seq.sql
+ execute_file "drop_all_seq.sql" ${DATABASE} ${SERVERNAME} ${PORT}>
/dev/null
+ \rm -f drop_all_seq.sql
+ CMD="select * from generate_drop_all_tables_syntax();"
+ execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} >
drop_all_tables.sql
+ execute_file "drop_all_tables.sql" ${DATABASE} ${SERVERNAME} ${PORT}>
/dev/null
+ \rm -f drop_all_tables.sql
+ CMD="select * from generate_drop_all_views_syntax();"
+ execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} >
drop_all_views.sql
+ execute_file "drop_all_views.sql" ${DATABASE} ${SERVERNAME} ${PORT}>
/dev/null
+ \rm -f drop_all_views.sql
+ CMD="select * from generate_drop_all_functions_syntax();"
+ execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} >
drop_all_functions.sql
+ execute_file "drop_all_functions.sql" ${DATABASE} ${SERVERNAME} ${PORT} >
/dev/null
+ \rm -f drop_all_functions.sql
+ CMD="select count(*) from pg_proc where proname = 'uuid_nil';"
+ if [ "$(execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} | sed -e
's/ //g' -e '/^$/d')" != 0 ]; then
+ psql -U postgres -h ${SERVERNAME} -p ${PORT} -f
drop_old_uuid_functions.sql ${DATABASE} > /dev/null
+ fi
+ CMD="DROP FUNCTION IF EXISTS uuid_generate_v1();"
+ execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null
+}
+
#drops views before upgrade or refresh operations
drop_views() {
# common stored procedures are executed first (for new added functions to be
valid)
--
To view, visit http://gerrit.ovirt.org/16832
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I7ff33e4ccb8cb66bbe06f5d1c8a00f22d28a2e31
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-engine
Gerrit-Branch: master
Gerrit-Owner: Eli Mesika <[email protected]>
_______________________________________________
Engine-patches mailing list
[email protected]
http://lists.ovirt.org/mailman/listinfo/engine-patches
