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!