Alon Bar-Lev has uploaded a new change for review. Change subject: db: cleanup: move public interface to top ......................................................................
db: cleanup: move public interface to top Change-Id: I9174019cce81acf7e2085a9dcf8070f5df78c929 Signed-off-by: Alon Bar-Lev <[email protected]> --- M packaging/dbscripts/dbfunc-common.sh 1 file changed, 219 insertions(+), 230 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/30/25130/1 diff --git a/packaging/dbscripts/dbfunc-common.sh b/packaging/dbscripts/dbfunc-common.sh index d42aa36..2414494 100644 --- a/packaging/dbscripts/dbfunc-common.sh +++ b/packaging/dbscripts/dbfunc-common.sh @@ -45,214 +45,6 @@ dbfunc_common_hook_init_insert_data } -#drops views before upgrade or refresh operations -dbfunc_common_views_drop() { - # common stored procedures are executed first (for new added functions to be valid) - dbfunc_psql_die --file="common_sp.sql" > /dev/null - dbfunc_psql_die --command="select * from generate_drop_all_views_syntax();" | \ - dbfunc_psql_die > /dev/null -} - -#drops sps before upgrade or refresh operations -dbfunc_common_sps_drop() { - dbfunc_psql_die --file="common_sp.sql" > /dev/null - local statement - statement="$( - dbfunc_psql_die --command="select * from generate_drop_all_functions_syntax();" - )" || exit 1 - dbfunc_psql_die --command="${statement}" > /dev/null - - # recreate generic functions - dbfunc_psql_die --file="create_functions.sql" > /dev/null -} - -#refreshes sps -dbfunc_common_sps_refresh() { - echo "Creating stored procedures..." - local sql - for sql in $(ls *sp.sql | sort); do - echo "Creating stored procedures from ${sql}..." - dbfunc_psql_die --file="${sql}" > /dev/null - done - dbfunc_psql_die --file="common_sp.sql" > /dev/null -} - -_dbfunc_common_install_common_func() { - # common stored procedures are executed first (for new added functions to be valid) - dbfunc_psql_die --file="common_sp.sql" > /dev/null -} - -_dbfunc_common_delete_async_tasks_and_compensation_data() { - dbfunc_psql_die --file="delete_async_tasks_and_compensation_data.sql" > /dev/null -} - -_dbfunc_common_run_pre_upgrade() { - #Dropping all views & sps - dbfunc_common_views_drop - dbfunc_common_sps_drop - _dbfunc_common_install_common_func - #update sequence numers - dbfunc_common_hook_sequence_numbers_update - #run pre upgrade scripts - _dbfunc_common_psql_statements_in_dir 'pre_upgrade' - dbfunc_common_hook_materialized_views_install - #drop materialized views to support views changesin upgrade - #Materialized views are restored in the post_upgrade step - dbfunc_common_hook_materialized_views_drop - - # TODO: move this to custom? - if [ -n "${CLEAN_TASKS}" ]; then - echo "Cleaning tasks metadata..." - _dbfunc_common_delete_async_tasks_and_compensation_data - fi -} - -_dbfunc_common_run_post_upgrade() { - #Refreshing all views & sps & run post-upgrade scripts - dbfunc_common_hook_views_refresh - dbfunc_common_sps_refresh - #Running post-upgrade scripts - _dbfunc_common_psql_statements_in_dir 'post_upgrade' - #run custom materialized views if exists - custom_materialized_views_file="upgrade/post_upgrade/custom/create_materialized_views.sql" - if [ -f "${custom_materialized_views_file}" ]; then - echo "running custom materialized views from '${custom_materialized_views_file}'..." - if ! dbfunc_psql --file="${custom_materialized_views_file}"; then - #drop all custom views - dbfunc_psql --command="select DropAllCustomMaterializedViews();" > /dev/null - echo "Illegal syntax in custom Materialized Views, Custom Materialized Views were dropped." - fi - fi - dbfunc_common_hook_materialized_viewsrefresh_ -} - -# Runs all the SQL scripts in directory upgrade/$1/ -# The second argument is the label to use while notifying -# the user about the running of the script -_dbfunc_common_psql_statements_in_dir() { - local dir="$1" - if [ -d "upgrade/${dir}" ]; then - files="$(_dbfunc_common_get_files "upgrade/${dir}" 1)" - for file in $(ls ${files} | sort); do - _dbfunc_common_run_file "${file}" - done - fi -} - -_dbfunc_common_run_required_scripts() { - local script="$1" - # check for helper functions that the script needs - # source scripts must be defined in the first lines of the script - local line - while read line; do - expr="$(echo "${line}" | cut -d " " -f1 | grep "\-\-#source")" - [ -z "${expr}" ] && break - local sql="$(echo "${line}" | cut -d " " -f2)" - echo "${sql}" | grep -q "_sp.sql" || \ - die "invalid source file ${sql} in ${file}, source files must end with '_sp.sql'" - echo "Running helper functions from '${sql}' for '${file}'" - dbfunc_psql_die --file="${sql}" > /dev/null - done < "${script}" -} - -_dbfunc_common_run_file() { - local file="$1" - if [ -x "${file}" ]; then - # delegate all DBFUNC_ vars in subshell - echo "Running upgrade shell script '${file}'..." - ( - eval "$(set | grep '^DBFUNC_' | sed 's/^\([^=]*\)=.*/export \1/')" - "./${file}" - ) - else - echo "Running upgrade sql script '${file}'..." - dbfunc_psql_die --file="${file}" > /dev/null - fi -} - -_dbfunc_common_get_current_version() { - dbfunc_psql_statement_parsable " - select version - from schema_version - where current = true - order by id - LIMIT 1 - " -} - -_dbfunc_common_get_installed_version() { - local cheksum="$1" - dbfunc_psql_statement_parsable " - select version - from schema_version - where - checksum = '${cheksum}' and - state = 'INSTALLED' - " -} - -_dbfunc_common_set_last_version() { - local id="$( - dbfunc_psql_statement_parsable " - select max(id) - from schema_version - where state in ('INSTALLED','SKIPPED') - " - )" - dbfunc_psql_die --command=" - update schema_version - set current=(id=${id}); - " > /dev/null -} - -_dbfunc_common_get_db_time(){ - dbfunc_psql_statement_parsable "select now()" -} - -# gets a directory and required depth and return all sql & sh files -_dbfunc_common_get_files() { - local dir="$1" - local maxdepth="$2" - find "${dir}" \ - -maxdepth "${maxdepth}" \ - -name '*.sql' -or -name '*.sh' | \ - sort -} - -_dbfunc_common_is_view_or_sp_changed() { - local files="$(_dbfunc_common_get_files "upgrade" 3)" - local md5sum_file="${DBFUNC_COMMON_MD5DIR}/.${DBFUNC_DB_DATABASE}.scripts.md5" - local md5sum_tmp_file="${md5sum_file}.tmp" - md5sum ${files} create_*views.sql *_sp.sql > "${md5sum_tmp_file}" - diff -s -q "${md5sum_file}" "${md5sum_tmp_file}" > /dev/null 2>&1 - result=$? - - # 0 - identical , 1 - differ , 2 - error - if [ $result -eq 0 ] ; then - rm -f "${md5sum_tmp_file}" - else - # there is a diff or md5 file does not exist - mv -f "${md5sum_tmp_file}" "${md5sum_file}" - fi - return $result -} - -_dbfunc_common_get_file_version() { - local file="$1" - basename "${file}" | sed -e 's#\(..........\).*#\1#' -e 's/_//g' -} - -_dbfunc_common_validate_version_uniqueness() { - local prev="" - local files="$(_dbfunc_common_get_files "upgrade" 1)" - local file - for file in $(ls ${files} | sort) ; do - local ver="$(_dbfunc_common_get_file_version "${file}")" - [ "${ver}" != "${prev}" ] || die "Operation aborted, found duplicate version: ${ver}" - prev="${ver}" - done -} - dbfunc_common_upgrade() { local res="$(find upgrade/ -name '*.sql' -or -name '*.sh' | wc -l)" local CMD @@ -376,30 +168,37 @@ )" } -#adds a record to audit_log in case of calling unlock_entity -_dbfunc_common_log_unlock_entity() { - local object_type="$1" - local id="$2" - local user="$3" - - dbfunc_psql_die --command=" - insert into audit_log( - log_time, - log_type_name, - log_type, - severity, - message - ) - values( - now(), - 'USER_RUN_UNLOCK_ENTITY_SCRIPT', - 2024, - 10, - 'System user ${user} run unlock_entity script on ${object_type} ${id} with db user ${DBUTILS_DB_USER}}' - ) - " +#drops views before upgrade or refresh operations +dbfunc_common_views_drop() { + # common stored procedures are executed first (for new added functions to be valid) + dbfunc_psql_die --file="common_sp.sql" > /dev/null + dbfunc_psql_die --command="select * from generate_drop_all_views_syntax();" | \ + dbfunc_psql_die > /dev/null } +#drops sps before upgrade or refresh operations +dbfunc_common_sps_drop() { + dbfunc_psql_die --file="common_sp.sql" > /dev/null + local statement + statement="$( + dbfunc_psql_die --command="select * from generate_drop_all_functions_syntax();" + )" || exit 1 + dbfunc_psql_die --command="${statement}" > /dev/null + + # recreate generic functions + dbfunc_psql_die --file="create_functions.sql" > /dev/null +} + +#refreshes sps +dbfunc_common_sps_refresh() { + echo "Creating stored procedures..." + local sql + for sql in $(ls *sp.sql | sort); do + echo "Creating stored procedures from ${sql}..." + dbfunc_psql_die --file="${sql}" > /dev/null + done + dbfunc_psql_die --file="common_sp.sql" > /dev/null +} #unlocks the given VM/Template and its disks or a given disk #in case of VM/Template the id is the name, in case of a disk, the id is the disk UUID @@ -529,3 +328,193 @@ " fi } + +_dbfunc_common_run_pre_upgrade() { + #Dropping all views & sps + dbfunc_common_views_drop + dbfunc_common_sps_drop + # common stored procedures are executed first (for new added functions to be valid) + dbfunc_psql_die --file="common_sp.sql" > /dev/null + #update sequence numers + dbfunc_common_hook_sequence_numbers_update + #run pre upgrade scripts + _dbfunc_common_psql_statements_in_dir 'pre_upgrade' + dbfunc_common_hook_materialized_views_install + #drop materialized views to support views changesin upgrade + #Materialized views are restored in the post_upgrade step + dbfunc_common_hook_materialized_views_drop + + # TODO: move this to custom? + if [ -n "${CLEAN_TASKS}" ]; then + echo "Cleaning tasks metadata..." + dbfunc_psql_die --file="delete_async_tasks_and_compensation_data.sql" > /dev/null + fi +} + +_dbfunc_common_run_post_upgrade() { + #Refreshing all views & sps & run post-upgrade scripts + dbfunc_common_hook_views_refresh + dbfunc_common_sps_refresh + #Running post-upgrade scripts + _dbfunc_common_psql_statements_in_dir 'post_upgrade' + #run custom materialized views if exists + custom_materialized_views_file="upgrade/post_upgrade/custom/create_materialized_views.sql" + if [ -f "${custom_materialized_views_file}" ]; then + echo "running custom materialized views from '${custom_materialized_views_file}'..." + if ! dbfunc_psql --file="${custom_materialized_views_file}"; then + #drop all custom views + dbfunc_psql_statement "select DropAllCustomMaterializedViews();" > /dev/null + echo "Illegal syntax in custom Materialized Views, Custom Materialized Views were dropped." + fi + fi + dbfunc_common_hook_materialized_viewsrefresh_ +} + +# Runs all the SQL scripts in directory upgrade/$1/ +_dbfunc_common_psql_statements_in_dir() { + local dir="$1" + if [ -d "upgrade/${dir}" ]; then + files="$(_dbfunc_common_get_files "upgrade/${dir}" 1)" + for file in $(ls ${files} | sort); do + _dbfunc_common_run_file "${file}" + done + fi +} + +_dbfunc_common_run_required_scripts() { + local script="$1" + # check for helper functions that the script needs + # source scripts must be defined in the first lines of the script + local line + while read line; do + expr="$(echo "${line}" | cut -d " " -f1 | grep "\-\-#source")" + [ -z "${expr}" ] && break + local sql="$(echo "${line}" | cut -d " " -f2)" + echo "${sql}" | grep -q "_sp.sql" || \ + die "invalid source file ${sql} in ${file}, source files must end with '_sp.sql'" + echo "Running helper functions from '${sql}' for '${file}'" + dbfunc_psql_die --file="${sql}" > /dev/null + done < "${script}" +} + +_dbfunc_common_run_file() { + local file="$1" + if [ -x "${file}" ]; then + # delegate all DBFUNC_ vars in subshell + echo "Running upgrade shell script '${file}'..." + ( + eval "$(set | grep '^DBFUNC_' | sed 's/^\([^=]*\)=.*/export \1/')" + "./${file}" + ) + else + echo "Running upgrade sql script '${file}'..." + dbfunc_psql_die --file="${file}" > /dev/null + fi +} + +_dbfunc_common_get_current_version() { + dbfunc_psql_statement_parsable " + select version + from schema_version + where current = true + order by id + LIMIT 1 + " +} + +_dbfunc_common_get_installed_version() { + local cheksum="$1" + dbfunc_psql_statement_parsable " + select version + from schema_version + where + checksum = '${cheksum}' and + state = 'INSTALLED' + " +} + +_dbfunc_common_set_last_version() { + local id="$( + dbfunc_psql_statement_parsable " + select max(id) + from schema_version + where state in ('INSTALLED','SKIPPED') + " + )" + dbfunc_psql_die --command=" + update schema_version + set current=(id=${id}); + " > /dev/null +} + +_dbfunc_common_get_db_time(){ + dbfunc_psql_statement_parsable "select now()" +} + +# gets a directory and required depth and return all sql & sh files +_dbfunc_common_get_files() { + local dir="$1" + local maxdepth="$2" + find "${dir}" \ + -maxdepth "${maxdepth}" \ + -name '*.sql' -or -name '*.sh' | \ + sort +} + +_dbfunc_common_is_view_or_sp_changed() { + local files="$(_dbfunc_common_get_files "upgrade" 3)" + local md5sum_file="${DBFUNC_COMMON_MD5DIR}/.${DBFUNC_DB_DATABASE}.scripts.md5" + local md5sum_tmp_file="${md5sum_file}.tmp" + md5sum ${files} create_*views.sql *_sp.sql > "${md5sum_tmp_file}" + diff -s -q "${md5sum_file}" "${md5sum_tmp_file}" > /dev/null 2>&1 + result=$? + + # 0 - identical , 1 - differ , 2 - error + if [ $result -eq 0 ] ; then + rm -f "${md5sum_tmp_file}" + else + # there is a diff or md5 file does not exist + mv -f "${md5sum_tmp_file}" "${md5sum_file}" + fi + return $result +} + +_dbfunc_common_get_file_version() { + local file="$1" + basename "${file}" | sed -e 's#\(..........\).*#\1#' -e 's/_//g' +} + +_dbfunc_common_validate_version_uniqueness() { + local prev="" + local files="$(_dbfunc_common_get_files "upgrade" 1)" + local file + for file in $(ls ${files} | sort) ; do + local ver="$(_dbfunc_common_get_file_version "${file}")" + [ "${ver}" != "${prev}" ] || die "Operation aborted, found duplicate version: ${ver}" + prev="${ver}" + done +} + +#adds a record to audit_log in case of calling unlock_entity +_dbfunc_common_log_unlock_entity() { + local object_type="$1" + local id="$2" + local user="$3" + + dbfunc_psql_die --command=" + insert into audit_log( + log_time, + log_type_name, + log_type, + severity, + message + ) + values( + now(), + 'USER_RUN_UNLOCK_ENTITY_SCRIPT', + 2024, + 10, + 'System user ${user} run unlock_entity script on ${object_type} ${id} with db user ${DBUTILS_DB_USER}}' + ) + " +} -- To view, visit http://gerrit.ovirt.org/25130 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I9174019cce81acf7e2085a9dcf8070f5df78c929 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Alon Bar-Lev <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
