Some libraries would like to eliminate old statistics, but keep the data for reporting. While pseudonymization works on new transactions once enabled, it would be nice to have a method to pseudonymize older transactions
+1 for this. This would simplify making statistics reports (no need to make join e.g. borrowers table to get patrons information). We also have plugin to collect statistics and using pseudonymized_transactions table with it currently means that we can't generate reports before pseudonymization was enabled.
Created attachment 154808 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted)
Created attachment 154809 [details] [review] Bug 34611: Add a cript to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized
Needs Unit tests
Created attachment 157753 [details] [review] Bug 34611: Unit tests Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 157754 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted) Tests are added and can be run using: 1. Run: $ ktd --shell k$ prove t/db_dependent/Koha/PseudonymizedTransaction.t \ t/db_dependent/Koha/Pseudonymization.t => SUCCESS: New tests pass, old tests keep passing
Created attachment 157755 [details] [review] Bug 34611: Add a script to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized
Created attachment 157763 [details] [review] Bug 34611: Unit tests Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157764 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted) Tests are added and can be run using: 1. Run: $ ktd --shell k$ prove t/db_dependent/Koha/PseudonymizedTransaction.t \ t/db_dependent/Koha/Pseudonymization.t => SUCCESS: New tests pass, old tests keep passing Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157765 [details] [review] Bug 34611: Add a script to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
It's worth noting that running this script repeatedly *did* create duplicate entries in the pseudonymized_transactions table. I don't feel like that's a blocker, but should be documented.
(In reply to Andrew Fuerste-Henry from comment #11) > It's worth noting that running this script repeatedly *did* create duplicate > entries in the pseudonymized_transactions table. I don't feel like that's a > blocker, but should be documented. That's bad. Let's try to fix it.
This will need some rebasing work on top of bug 33608, which was much longer in the queue and thus pushed first.
Created attachment 157869 [details] [review] Bug 34611: Unit tests Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157870 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted) Tests are added and can be run using: 1. Run: $ ktd --shell k$ prove t/db_dependent/Koha/PseudonymizedTransaction.t \ t/db_dependent/Koha/Pseudonymization.t => SUCCESS: New tests pass, old tests keep passing Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157871 [details] [review] Bug 34611: Add a script to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157872 [details] [review] Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback
(In reply to Tomás Cohen Arazi from comment #12) > (In reply to Andrew Fuerste-Henry from comment #11) > > It's worth noting that running this script repeatedly *did* create duplicate > > entries in the pseudonymized_transactions table. I don't feel like that's a > > blocker, but should be documented. > > That's bad. Let's try to fix it. The whole point is that we shouldn't be able to get to the patron from the pseudonymized, so pointing out the overlap would seem wrong. It would also get tricky where one pseudonymized today might provide different patron info than an original, so detecting duplicates would be flawed. As a safeguard against accidental repetition I added a rudimentary check - if there are pseudonymized transactions found before the date you must now confirm the run or we exit.
Created attachment 157877 [details] [review] Bug 34611: Unit tests Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157878 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted) Tests are added and can be run using: 1. Run: $ ktd --shell k$ prove t/db_dependent/Koha/PseudonymizedTransaction.t \ t/db_dependent/Koha/Pseudonymization.t => SUCCESS: New tests pass, old tests keep passing Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157879 [details] [review] Bug 34611: Add a script to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Created attachment 157880 [details] [review] Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
re-signed-off with new check and verbose mode!
Created attachment 159909 [details] [review] Bug 34611: Unit tests Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 159910 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted) Tests are added and can be run using: 1. Run: $ ktd --shell k$ prove t/db_dependent/Koha/PseudonymizedTransaction.t \ t/db_dependent/Koha/Pseudonymization.t => SUCCESS: New tests pass, old tests keep passing Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 159911 [details] [review] Bug 34611: Add a script to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 159912 [details] [review] Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
This also needs a rebase, conflict in Statistics.pm - I think it will be better if you resolve yourself. Also goes on my list to be pushed once it's bounced back. Apply? [(y)es, (n)o, (i)nteractive] y Applying: Bug 34611: Unit tests Applying: Bug 34611: Add pseudonymize routine to Koha::Statistics Using index info to reconstruct a base tree... M Koha/Statistic.pm Falling back to patching base and 3-way merge... Auto-merging Koha/Statistic.pm CONFLICT (content): Merge conflict in Koha/Statistic.pm error: Failed to merge in the changes. Patch failed at 0001 Bug 34611: Add pseudonymize routine to Koha::Statistics hint: Use 'git am --show-current-patch=diff' to see the failed patch When you have resolved this problem run "git bz apply --continue". If you would prefer to skip this patch, instead run "git bz apply --skip". To restore the original branch and stop patching run "git bz apply --abort". Patch left in /tmp/Bug-34611-Add-pseudonymize-routine-to-KohaStatisti-tmb2y2g5.patch
Created attachment 163344 [details] [review] Bug 34611: Unit tests Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 163345 [details] [review] Bug 34611: Add pseudonymize routine to Koha::Statistics This patch adds a new routine to pseudonymize a statistic and adjusts C4::Stats to use this new routine. Additionally Koha::PseudonymizedTransaction->new_from_statistic is updated to check for the existence of objects before using them (in the case of older stats where things may have been deleted) Tests are added and can be run using: 1. Run: $ ktd --shell k$ prove t/db_dependent/Koha/PseudonymizedTransaction.t \ t/db_dependent/Koha/Pseudonymization.t => SUCCESS: New tests pass, old tests keep passing Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 163346 [details] [review] Bug 34611: Add a script to pseudonymize statistics from before a given date This script takes a date parameter in SQL format and pseudonymizes all statistics found before this date. Only values that can be found will be added, i.e. no deleted patron or item info will be present. Additionally - the values stored will be the current values from patrons and items, so some info will be approximate, much as it is when joining from the statistics table for reporting. To test: 1 - Perform some issues/returns/renewals/on-site checkouts 2 - Make sure Pseudonymization is disabled 3 - perl misc/maintenance/pseudonymize_statistics.pl 4 - Script ends and reports that preference is not active 5 - Enable the pref, and choose some borrower and item fields NOTE: See bug 28911 if you need a bcrypt key for your koha-conf.xml 6 - perl misc/maintenance/pseudonymize_statistics.pl 7 - sudo koha-mysql kohadev 8 - SELECT * FROM pseudonymized_transactions 9 - Confirm data is correctly stored 10 - DELETE FROM pseudonymized_transactions; 11 - UPDATE statistics SET datetime = '2023-01-01 00:00:00'; 12 - perl misc/maintenance/pseudonymize_statistics.pl -b "2022-12-31 23:59:59"; 13 - SELECT * FROM pseudonymized_transactions; 14 - Confirm no entries were made 15 - Select different options in Pseudonmyization prefs, including borrower attributes This wil require defining an attribute that can be kept for pseudonymization 16 - Confirm options are correctly pseudonymized Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 163347 [details] [review] Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Looking at the script, I feel like adding a -c confirmation flag before any action is taken might be good. What do you think?
Pushed for 24.05! Well done everyone, thank you!
(In reply to Katrin Fischer from comment #33) > Looking at the script, I feel like adding a -c confirmation flag before any > action is taken might be good. > What do you think? +1 scripts are always better with confirm flag
Created attachment 163756 [details] [review] Bug 34611: (follow-up) Add commit option to misc/maintenance/pseudonymize_statistics.pl To test: 1 - Perform some transactions 2 - Enable Pseudonymization 3 - perl misc/maintenance/pseudonymize_statistics.pl -v 4 - Confirm test run an nothing changed 5 - perl misc/maintenance/pseudonymize_statistics.pl -v -c 6 - Confirm statistics are pseudonymized
Created attachment 163757 [details] [review] Bug 34611: (follow-up) Preserve statistic date if passed or null if not When testing the confirm option, I found that I was not warned on repeated runs. It turns out creating a new statistic object was setting the datetime to current timestamp This patch reset the object from the values passed To test: 1 - Disable Pseudonymization 2 - Perform some transactions, update datetimein DB to set them to yesterday 3 - Enable pseudonymization, ensure 'Date and time of transaction' is checked 4 - perl misc/maintenance/pseudonymize_statistics.pl -v 5 - Check the DB, note the new pseudonymized transactions have todays date 6 - DELETE FROM pseudonymized_transactions 7 - Apply patch 8 - perl misc/maintenance/pseudonymize_statistics.pl -v 9 - Check DB and confirm the transactoins have the original date 10 - DELETE FROM pseudonymized_transactions 11 - Unset date and time from pseudonymization pref 12 - perl misc/maintenance/pseudonymize_statistics.pl -v 13 - Confirm the transactions have a null date
Could we have QA on the last 2 patches please? Question: test plan for second patch doesn't mention the commit flag?
(In reply to Katrin Fischer from comment #38) > Question: test plan for second patch doesn't mention the commit flag? That's just forgetfulness :-)
(In reply to Nick Clemens from comment #39) > (In reply to Katrin Fischer from comment #38) > > Question: test plan for second patch doesn't mention the commit flag? > > That's just forgetfulness :-) Thanks for clarifying ;)
I'm moving the follow-up patches to their own bug reports for clarity on the status of this bug.
Changing status to Needs documenting. (Bug was pushed to 24.05.00 and was not back ported to any earlier release.)