Eli Mesika has uploaded a new change for review. Change subject: core:fkvalidator.sh - some args are not honoured ......................................................................
core:fkvalidator.sh - some args are not honoured Adding -l LOGFILE support Adding support for -v flag When -v is used the number of violates records and the query how to get them is displayed. Example: ./fkvalidator.sh -u postgres -d engine_964197 Constraint violation found in event_subscriber (event_up_name) ./fkvalidator.sh -u postgres -d engine_964197 -v Constraint violation found in event_subscriber (event_up_name) Please run the following SQL to get the 1 violated record/s: select (event_up_name) from event_subscriber where (event_up_name) IS NOT NULL and (event_up_name) not in (select (event_up_name) from event_map); " Change-Id: I759dc11424bda345e14e7bc7bdf81d41515a0847 Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=964197 Signed-off-by: Eli Mesika <[email protected]> --- M packaging/setup/dbutils/fkvalidator.sh M packaging/setup/dbutils/fkvalidator_sp.sql 2 files changed, 25 insertions(+), 12 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/15/17015/1 diff --git a/packaging/setup/dbutils/fkvalidator.sh b/packaging/setup/dbutils/fkvalidator.sh index 106807d..caedf0f 100755 --- a/packaging/setup/dbutils/fkvalidator.sh +++ b/packaging/setup/dbutils/fkvalidator.sh @@ -55,25 +55,34 @@ # if fix_it is true , constriant violations cause is removed from DB validate_db_fks() { local fix_it=${1} + local verbose=${2} if [ "${fix_it}" = "true" ]; then - CMD="copy (select fk_violation from fn_db_validate_fks(true)) to stdout;" + if [ "${verbose}" = "true" ]; then + CMD="copy (select fk_violation from fn_db_validate_fks(true,true)) to stdout;" + else + CMD="copy (select fk_violation from fn_db_validate_fks(true,false)) to stdout;" + fi else - CMD="copy (select fk_violation,fk_status from fn_db_validate_fks(false) where fk_status=1) to stdout with csv;" + if [ "${verbose}" = "true" ]; then + CMD="copy (select fk_violation,fk_status from fn_db_validate_fks(false,true) where fk_status=1) to stdout with csv;" + else + CMD="copy (select fk_violation,fk_status from fn_db_validate_fks(false,false) where fk_status=1) to stdout with csv;" + fi fi - res="$(psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 -U ${USERNAME} -c "${CMD}" -h "${SERVERNAME}" -p "${PORT}" "${DATABASE}")" + res="$(psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 -U ${USERNAME} -c "${CMD}" -h "${SERVERNAME}" -p "${PORT}" -L ${LOGFILE} "${DATABASE}")" exit_code=$? out="$(echo "${res}" | cut -f1 -d,)" - if [ "${exit_code}" = "0" ]; then - exit_code="$(echo "${res}" | cut -f2 -d, | head -1)" - fi echo "${out}" + if [[ "${exit_code}" = "0" && "${fix_it}" = "false" ]]; then + exit_code="$(echo "${res}" | cut -f2 -d, | tail -1)" + fi exit ${exit_code} } FIXIT=false -while getopts hs:d:u:p:l:fqv option; do +while getopts hs:d:u:l:p:fqv option; do case $option in s) SERVERNAME=$OPTARG;; p) PORT=$OPTARG;; @@ -103,7 +112,7 @@ fi fi -validate_db_fks ${FIXIT} +validate_db_fks ${FIXIT} ${VERBOSE} popd>/dev/null exit $? diff --git a/packaging/setup/dbutils/fkvalidator_sp.sql b/packaging/setup/dbutils/fkvalidator_sp.sql index 9ce6114..2168cbe 100644 --- a/packaging/setup/dbutils/fkvalidator_sp.sql +++ b/packaging/setup/dbutils/fkvalidator_sp.sql @@ -1,10 +1,10 @@ -- Database FK validation SET client_min_messages=ERROR; DROP TYPE IF EXISTS fk_info_rs CASCADE; -DROP FUNCTION IF EXISTS fn_db_validate_fks(boolean); +DROP FUNCTION IF EXISTS fn_db_validate_fks(boolean,boolean); CREATE TYPE fk_info_rs AS (table_name varchar, table_col varchar, fk_table_name varchar, fk_col varchar, fk_violation varchar, fk_status integer); -CREATE OR REPLACE FUNCTION fn_db_validate_fks(v_fix_it boolean) +CREATE OR REPLACE FUNCTION fn_db_validate_fks(v_fix_it boolean, v_verbose boolean) returns SETOF fk_info_rs AS $procedure$ DECLARE @@ -39,7 +39,7 @@ v_sql := 'delete from ' || v_record.fk_table_name || ' where ' || v_record.fk_col || 'IS NOT NULL and ' || 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; + v_msg := 'Fixing violation/s found in ' || v_record.fk_table_name ; ELSE v_sql := 'select ' || v_record.fk_col || ' from ' || v_record.fk_table_name || ' where ' || v_record.fk_col || 'IS NOT NULL and ' || v_record.fk_col || ' not in (select ' || @@ -49,7 +49,11 @@ EXECUTE v_sql; GET DIAGNOSTICS v_rowcount = ROW_COUNT; IF (v_rowcount > 0) THEN - v_record.fk_violation := v_msg; + IF (v_verbose and not v_fix_it) THEN + v_record.fk_violation := v_msg || E'\nPlease run the following SQL to get the ' || v_rowcount || E' violated record/s: \n' || v_sql || E'\n'; + ELSE + v_record.fk_violation := v_msg; + END IF; v_record.fk_status := 1; END IF; RETURN NEXT v_record; -- To view, visit http://gerrit.ovirt.org/17015 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I759dc11424bda345e14e7bc7bdf81d41515a0847 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
