Keeping auditor tables up to date after source tables change

[Josh Stompro]

EG 2.10 upgrading to EG 3.1

I noticed that the new fields for tracking merge info and visibility were missing from our auditor.biblio_record_entry table after a test upgrade from 2.10 to 3.1.

Should the upgrade process address keeping existing auditor tables up to date? Or perhaps it just warrants a release note entry that it is something that needs to be dealt with?

Josh

[Jeff Davis (jdavis-sitka)]

There are 10 audited tables in a stock EG install. I think it's reasonable to expect schema updates to those 10 tables to be accompanied by SQL for making the corresponding changes to the auditor tables.

Maybe we want a pgTAP test that compares auditor vs audited?

[Mike Rylander (mrylander)]

As it happens, we have a function that will go through and update auditor tables. Perhaps we should simply run auditor.update_auditors() at the end of every version upgrade script, just to be safe?

[Josh Stompro (u-launchpad-stompro-org)]

Mike, that sounds like a great solution. I added that command "select auditor.update_auditors();" to the end of a test db upgrade and it fixed the problem.

What would be a good way to get this included in the next version upgrade scripts for 3.1 and 3.2? I'm not familiar with how the version upgrade sql files get created. If there are a set of instructions for creating those, them maybe that just needs to be updated? Is there a script that builds the version upgrade from the upgrade/xxx*.sql files?

Thanks
Josh

[Mike Rylander (mrylander)]

The version upgrade script is created during the release bundling process by a script called, unsurprisingly, make_release. I think something like this would work:

diff --git a/build/tools/make_release b/build/tools/make_release
index c3fd2ed..2f00042 100755
--- a/build/tools/make_release
+++ b/build/tools/make_release
@@ -259,6 +259,8 @@ if [ "$PREV_BRANCH" != "PACKAGE" ]; then
                 cat Open-ILS/src/sql/Pg/upgrade/$NUM.* 2>/dev/null | grep -v '^\s*\(BEGIN\|COMMIT\);\s*$' >> $UPGRADE_FILE
             done;
             echo "COMMIT;" >> $UPGRADE_FILE
+ echo "" >> $UPGRADE_FILE
+ echo "SELECT auditor.update_auditors();" >> $UPGRADE_FILE
             MAYBE_DUPES=`grep -oP 'CREATE (OR REPLACE )?FUNCTION +\K[^ ]*(?= *\()' $UPGRADE_FILE | sort | grep -P '^(.*)\n\1$' | sort -u`
             if [ -n "$MAYBE_DUPES" ]; then
                 echo ""

[Josh Stompro (u-launchpad-stompro-org)]

Mike, I put your example into a branch to make it easier for someone to test it out. I'm not sure how to go about using the make_release script, so I haven't tested it.

user/stompro/lp1768869_upgrade_update_auditor_tables

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/stompro/lp1768869_upgrade_update_auditor_tables

I'm going to add the pullrequest tag just so it has more visibility, but if anyone feels like that isn't appropriate please update the tags.
Josh

[Galen Charlton (gmc)]

Pushed to master for inclusion in 3.4. Thanks, Josh!

I've elected not to backport it further, as we shouldn't expect much if any in the way of schema updates of audited tables in 3.3 and later.