Bug 34611 - Add a script for pseudonymizing existing data
Summary: Add a script for pseudonymizing existing data
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Nick Clemens
QA Contact: Testopia
URL:
Keywords:
Depends on: 33608
Blocks:
  Show dependency treegraph
 
Reported: 2023-08-24 19:02 UTC by Nick Clemens
Modified: 2023-10-25 22:10 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 34611: Add pseudonymize routine to Koha::Statistics (6.13 KB, patch)
2023-08-25 12:24 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 34611: Add a cript to pseudonymize statistics from before a given date (5.07 KB, patch)
2023-08-25 12:24 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 34611: Unit tests (5.37 KB, patch)
2023-10-24 18:24 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 34611: Add pseudonymize routine to Koha::Statistics (6.70 KB, patch)
2023-10-24 18:25 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 34611: Add a script to pseudonymize statistics from before a given date (5.07 KB, patch)
2023-10-24 18:25 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 34611: Unit tests (5.42 KB, patch)
2023-10-24 21:37 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 34611: Add pseudonymize routine to Koha::Statistics (6.75 KB, patch)
2023-10-24 21:37 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 34611: Add a script to pseudonymize statistics from before a given date (5.12 KB, patch)
2023-10-24 21:37 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 34611: Unit tests (5.42 KB, patch)
2023-10-25 19:11 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 34611: Add pseudonymize routine to Koha::Statistics (6.50 KB, patch)
2023-10-25 19:11 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 34611: Add a script to pseudonymize statistics from before a given date (5.12 KB, patch)
2023-10-25 19:11 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback (2.00 KB, patch)
2023-10-25 19:11 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 34611: Unit tests (5.42 KB, patch)
2023-10-25 22:09 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 34611: Add pseudonymize routine to Koha::Statistics (6.50 KB, patch)
2023-10-25 22:09 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 34611: Add a script to pseudonymize statistics from before a given date (5.12 KB, patch)
2023-10-25 22:09 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback (2.05 KB, patch)
2023-10-25 22:09 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2023-08-24 19:02:27 UTC
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
Comment 1 Emmi Takkinen 2023-08-25 05:41:46 UTC
+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.
Comment 2 Nick Clemens 2023-08-25 12:24:00 UTC
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)
Comment 3 Nick Clemens 2023-08-25 12:24:02 UTC
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
Comment 4 Nick Clemens 2023-08-25 12:24:14 UTC Comment hidden (obsolete)
Comment 5 Tomás Cohen Arazi 2023-10-24 18:24:59 UTC
Created attachment 157753 [details] [review]
Bug 34611: Unit tests

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 6 Tomás Cohen Arazi 2023-10-24 18:25:02 UTC
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
Comment 7 Tomás Cohen Arazi 2023-10-24 18:25:05 UTC
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
Comment 8 Andrew Fuerste-Henry 2023-10-24 21:37:24 UTC
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>
Comment 9 Andrew Fuerste-Henry 2023-10-24 21:37:26 UTC
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>
Comment 10 Andrew Fuerste-Henry 2023-10-24 21:37:28 UTC
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>
Comment 11 Andrew Fuerste-Henry 2023-10-24 21:39:03 UTC
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.
Comment 12 Tomás Cohen Arazi 2023-10-24 23:01:33 UTC
(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.
Comment 13 Tomás Cohen Arazi 2023-10-25 16:09:24 UTC
This will need some rebasing work on top of bug 33608, which was much longer in the queue and thus pushed first.
Comment 14 Nick Clemens 2023-10-25 19:11:02 UTC
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>
Comment 15 Nick Clemens 2023-10-25 19:11:05 UTC
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>
Comment 16 Nick Clemens 2023-10-25 19:11:07 UTC
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>
Comment 17 Nick Clemens 2023-10-25 19:11:09 UTC
Created attachment 157872 [details] [review]
Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback
Comment 18 Nick Clemens 2023-10-25 19:14:48 UTC
(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.
Comment 19 Andrew Fuerste-Henry 2023-10-25 22:09:03 UTC
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>
Comment 20 Andrew Fuerste-Henry 2023-10-25 22:09:06 UTC
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>
Comment 21 Andrew Fuerste-Henry 2023-10-25 22:09:09 UTC
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>
Comment 22 Andrew Fuerste-Henry 2023-10-25 22:09:11 UTC
Created attachment 157880 [details] [review]
Bug 34611: Confirm run if pseudonymized transactions found and add verbose feedback

Signed-off-by: AFHDubCoLib <andrewfh@dubcolib.org>
Comment 23 Andrew Fuerste-Henry 2023-10-25 22:10:31 UTC
re-signed-off with new check and verbose mode!