Eli Mesika has uploaded a new change for review. Change subject: core : DB FK validation ......................................................................
core : DB FK validation The purpose of this utility is to find inconsistent data that violates FK(Forign Keys), display it and enable to remove it Only support may access this utility with care It is mandatory to run this utility on the original database before a backup of the DB is taken for later restore purpose, since if the database is backed up with the corrupted data and the FK definition, the FK will fail creation when the database is restored. Use the -f flag to fix the problem by removing the data caused to the FK violation. Running this utility without the FK flag will only report the violations. Use the -f flag to fix the problem by removing the data caused to the FK violation. Running this utility without the FK flag will only report the violations. Sample Output: >fkvalidator.sh -u postgres -d dbname caution, this operation should be used with care. Please contact support prior to running this command Are you sure you want to proceed? [y/n] y psql:/tmp/tmp.fmQ0Q7O6ic:1: NOTICE: Constraint violation found in weather (city) ... (2 records) >fkvalidator.sh -u postgres -d dbname -f Caution, this operation should be used with care. Please contact support prior to running this command Are you sure you want to proceed? [y/n] y psql:/tmp/tmp.8p8BXKVObk:1: NOTICE: Fixing weather (city) ... (2 records) Change-Id: Ibe54bca7a832c1c358a5e8c7214e7825cb9e4fc3 Signed-off-by: Eli Mesika <[email protected]> --- M backend/manager/dbscripts/common_sp.sql M backend/manager/dbscripts/dbfunctions.sh A backend/manager/dbscripts/fkvalidator.sh 3 files changed, 147 insertions(+), 0 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/48/10248/1 diff --git a/backend/manager/dbscripts/common_sp.sql b/backend/manager/dbscripts/common_sp.sql index fb0b002..e7f5f92 100644 --- a/backend/manager/dbscripts/common_sp.sql +++ b/backend/manager/dbscripts/common_sp.sql @@ -541,3 +541,56 @@ END; $procedure$ LANGUAGE plpgsql; +-- Database FK validation +DROP TYPE IF EXISTS fk_info_rs CASCADE; +CREATE TYPE fk_info_rs AS + (table_name varchar, table_col varchar, fk_table_name varchar, fk_col varchar ); +CREATE OR REPLACE FUNCTION fn_db_validate_fks(v_fix_it boolean) +returns void +AS $procedure$ +DECLARE + v_sql text; + v_msg text; + v_rowcount integer; + v_record fk_info_rs%ROWTYPE; + v_cur CURSOR FOR + SELECT + c.relname as table_name, + substring(substring ((select pg_get_constraintdef(r.oid)) from '[a-zA-Z0-9_\-][(][a-zA-Z0-9_\-]+[)]') from 2) as table_col, + c2.relname AS fk_table_name, + substring ((select pg_get_constraintdef(r.oid)) from ' [(][a-zA-Z0-9_\-]+[)] ') as fk_col + FROM pg_class c, pg_class c2, pg_constraint r + WHERE c.relname in (select table_name from information_schema.tables + where table_schema not in ('pg_catalog','information_schema') and table_type = 'BASE TABLE') AND + r.confrelid = c.oid AND + r.contype = 'f' AND + c2.oid = r.conrelid AND + pg_get_constraintdef(r.oid) not ilike '%ON DELETE SET %' + ORDER BY table_name; + +BEGIN + OPEN v_cur; + LOOP + FETCH v_cur INTO v_record; + EXIT WHEN NOT FOUND; + IF (v_fix_it) THEN + v_sql := 'delete from ' || v_record.fk_table_name || + ' where ' || v_record.fk_col || ' not in (select ' || + v_record.table_col || ' from ' || v_record.table_name || ');'; + v_msg := 'Fixing ' || v_record.fk_table_name || v_record.fk_col; + ELSE + v_sql := 'select ' || v_record.fk_col || ' from ' || v_record.fk_table_name || + ' where ' || v_record.fk_col || ' not in (select ' || + v_record.table_col || ' from ' || v_record.table_name || ');'; + v_msg := 'Constraint violation found in ' || v_record.fk_table_name || v_record.fk_col; + END IF; + EXECUTE v_sql; + GET DIAGNOSTICS v_rowcount = ROW_COUNT; + IF (v_rowcount > 0) THEN + RAISE NOTICE '% ... (% record/s)', v_msg, v_rowcount; + END IF; + + END LOOP; + CLOSE v_cur; +END; $procedure$ +LANGUAGE plpgsql; diff --git a/backend/manager/dbscripts/dbfunctions.sh b/backend/manager/dbscripts/dbfunctions.sh index 6c95e8f..c856701 100755 --- a/backend/manager/dbscripts/dbfunctions.sh +++ b/backend/manager/dbscripts/dbfunctions.sh @@ -437,3 +437,12 @@ psql -c "${CMD}" -U ${USERNAME} -d "${DATABASE}" -h "${SERVERNAME}" -p "${PORT}" fi } + +# Validates DB FKs +# if fix_it is false , constriant violations are reported only +# if fix_it is false , constriant violations cause is removed from DB +validate_db_fks() { + local fix_it=${1} + CMD="select * from fn_db_validate_fks(${fix_it});" + execute_command "${CMD}" "${DATABASE}" "${SERVERNAME}" "${PORT}" +} diff --git a/backend/manager/dbscripts/fkvalidator.sh b/backend/manager/dbscripts/fkvalidator.sh new file mode 100755 index 0000000..a9c056c --- /dev/null +++ b/backend/manager/dbscripts/fkvalidator.sh @@ -0,0 +1,85 @@ +#!/bin/bash +#include db general functions +source ./dbfunctions.sh +source ./dbcustomfunctions.sh + +#setting defaults +set_defaults +############################################################################################################### +# The purpose of this utility is to find inconsistent data that violates FK, display it and enable to remove it +# Only support may access this utility with care +# It is mandatory to run this utility on the original database before a backup of the DB is taken for later +# restore purpose, since if the database is backed up with the corrupted data and the FK definition, the FK +# will fail creation when the database is restored. +# Use the -f flag to fix the problem by removing the data that caused the FK violation. +# Running this utility without the FK flag will only report the violations. +# Use the -f flag to fix the problem by removing the data caused to the FK violation. +# Running this utility without the FK flag will only report the violations. +# Sample Output: +# >fkvalidator.sh -u postgres -d dbname +# caution, this operation should be used with care. Please contact support prior to running this command +# Are you sure you want to proceed? [y/n] +# y +# psql:/tmp/tmp.fmQ0Q7O6ic:1: NOTICE: Constraint violation found in weather (city) ... (2 records) +# +# >fkvalidator.sh -u postgres -d dbname -f +# Caution, this operation should be used with care. Please contact support prior to running this command +# Are you sure you want to proceed? [y/n] +# y +# psql:/tmp/tmp.8p8BXKVObk:1: NOTICE: Fixing weather (city) ... (2 records) +############################################################################################################### + + + +usage() { + printf "Usage: ${ME} [-h] [-s SERVERNAME [-p PORT]] [-d DATABASE] [-u USERNAME] [-l LOGFILE] [-f] [-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 admin username for the database.\n" + printf "\t-l LOGFILE - The logfile for capturing output (def. ${LOGFILE})\n" + printf "\t-f - Fix the non consistent data by removing it from DB.\n" + printf "\t-v - Turn on verbosity (WARNING: lots of output)\n" + printf "\t-h - This help text.\n" + printf "\n" + + exit $ret +} + +DEBUG () { + if $VERBOSE; then + printf "DEBUG: $*" + fi +} + +FIXIT=false + +while getopts hs:d:u:p:l:fv option; do + case $option in + s) SERVERNAME=$OPTARG;; + p) PORT=$OPTARG;; + d) DATABASE=$OPTARG;; + u) USERNAME=$OPTARG;; + l) LOGFILE=$OPTARG;; + f) FIXIT=true;; + v) VERBOSE=true;; + h) ret=0 && usage;; + \?) ret=1 && usage;; + esac +done + +if [ "${FIXIT}" = "true" ]; then + echo "Caution, this operation should be used with care. Please contact support prior to running this command" + echo "Are you sure you want to proceed? [y/n]" + read answer + + if [ "${answer}" = "n" ]; then + echo "Please contact support for further assistance." + exit 1 + fi +fi + +validate_db_fks ${FIXIT} + +exit $? -- To view, visit http://gerrit.ovirt.org/10248 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ibe54bca7a832c1c358a5e8c7214e7825cb9e4fc3 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
