September 2022 - Koha-devel - lists.koha-community.org

Re: [Koha-devel] Handling normalized phone number data
by dcook＠prosentient.com.au 16 Sep '22

16 Sep '22

I think that there could be merit in having the separate table, although that would require changes to a number of different patron touch points. I don’t know how it would work with the “Import patrons” or “Batch patron modification” or “Batch patron deletion and anonymization” tools (among others). We’d also need to modify patron entry, modification, and self-registration. I was thinking about your example, and I wasn’t sure how DBIC works with a 1 to Many relationship, so I came up with this example after consulting the wiki: curl -u koha:koha --request GET 'http://127.0.0.1:8081/api/v1/patrons/51' --header "x-koha-embed: extended_attributes" I gave the patron multiple extended attributes and it did a fantastic job of including the extended attributes as objects in a list in the JSON output. Awesome! I think that could work really well with phones. (Having flags for “primary” and “sms” could be good in that table.) My only hesitation would be with the time/energy that it would take to refactor Koha completely to use a separate phones table on my own :/. If it were a team effort, I’d be willing to help create and execute a plan though. It could also become a blueprint for how to denormalize other data in the borrowers table (like email and address). David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io> Sent: Thursday, 15 September 2022 9:59 PM To: dcook(a)prosentient.com.au Cc: koha-devel <koha-devel(a)lists.koha-community.org>; Kyle Hall <kyle(a)bywatersolutions.com>; Martin Renvoize <martin.renvoize(a)ptfs-europe.com> Subject: Re: Handling normalized phone number data I insist you should think longer term, and add a separate table. That way you will end up with a simpler (and more performant) query as well: GET /patrons x-koha-embed: phones x-koha-query: {"phones.normalized":{"-like":"123%"}} El jue, 15 sept 2022 a las 1:33, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: At the moment, the only solution I can think of is to rewrite the DBIC query after it hits the REST API. I’ve figured out 3 ways to do it: Current: {"me.phone"=>{"like"=>"%020404123456%"}} Option 1: {"me.phone"=>{"in"=> \["SELECT phone FROM borrowers WHERE regexp_replace(phone,?,?) LIKE ?","[^0-9]","",$query->{'like'}]} Option 2: \["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"] Option 3: Delete {"me.phone"=>{"like"=>"%020404123456%"}} Inject the following into the WHERE via the $attributes: \["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"] On a database of over 100,000 patrons, Option 1 takes 3 seconds to return while Option 2 takes .2 seconds to return. Option 3 is a bit harder to test but should be the .2 seconds too. The problem with Option 2 is that it nukes the original hashref, so if that hashref contains other keys for an OR query, they’d be lost. I was leaning towards Option 1 until I figured out Option 3. Forutnately, DataTables sends a particular HTTP header, so I’m able to do the re-writing just for DataTables requests to the API. I’ll be posting the patch to https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 shortly. Even if the community doesn’t go this way, I might use it locally anyway going forward. I’ve started rolling out a similar method for older Kohas because of the number of libraries flagging phone number searching as a problem. One way or another it’s a problem that needs to be solved… David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > Sent: Thursday, 15 September 2022 1:23 PM To: 'Tomas Cohen Arazi' <tomascohen(a)theke.io <mailto:tomascohen@theke.io> > Cc: 'koha-devel' <koha-devel(a)lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; 'Kyle Hall' <kyle(a)bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; 'Martin Renvoize' <martin.renvoize(a)ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> > Subject: RE: Handling normalized phone number data So you’d add it to the client side, eh… I looked through “koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc” and “koha-tmpl/intranet-tmpl/prog/js/datatables.js”, and found the DataTables integration with the REST API to be a bit… obscure. But now I see what you mean about adding an embed option via the kohaTable constructor. Although like Kyle was saying. Adding a phone number table wouldn’t solve this problem (unless we had a normalized phone number column). That said, we wouldn’t need to normalize the phone number data, if we could use use SQL functions on the left hand side of a comparison: https://metacpan.org/dist/DBIx-Class/view/lib/DBIx/Class/Manual/Cookbook.po… It’s doable from a DBIC level but I don’t see how we can do it with the DataTables + REST API integration since it requires Perl-specific syntax that JSON can’t handle. Handing DBIC-like syntax to the REST API makes this really hard. The only thing I can think to do is to rewrite the query after it hits the REST API. I’ll be writing about that again shortly… David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io <mailto:tomascohen@theke.io> > Sent: Thursday, 15 September 2022 11:06 AM To: David Cook <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > Cc: koha-devel <koha-devel(a)lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; Kyle Hall <kyle(a)bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize(a)ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> > Subject: Re: Handling normalized phone number data For the REST API you would just add an embed option. Correct. El mié, 14 sept 2022 21:37, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: I think at some point both the phone and email details should be moved into their own table(s), and then 1 would get a primary flag. With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm. If we had a “phone” table, we could potentially store both free-text (potentially formatted) and normalized versions of the phone number. Then we’d just update the patron search to use the normalized version. But all of that sounds like a lot of effort :/ David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io <mailto:tomascohen@theke.io> > Sent: Wednesday, 14 September 2022 11:10 PM To: dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> Cc: koha-devel <koha-devel(a)lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; Kyle Hall <kyle(a)bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize(a)ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> > Subject: Re: Handling normalized phone number data Should we also think of moving phones to their own table? El mié, 14 sept 2022 a las 3:43, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: Hi all, I’ve been working lately on https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 in an effort to let staff users easily search phone numbers in the Patrons module. Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API. So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. Thoughts? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F -- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F

1 0

A guide for testing Koha's REST API
by Mason James 15 Sep '22

15 Sep '22

hi folks i've made a guide here... https://wiki.koha-community.org/wiki/REST_API_Debug

2 1

Re: [Koha-devel] Handling normalized phone number data
by dcook＠prosentient.com.au 15 Sep '22

15 Sep '22

At the moment, the only solution I can think of is to rewrite the DBIC query after it hits the REST API. I’ve figured out 3 ways to do it: Current: {"me.phone"=>{"like"=>"%020404123456%"}} Option 1: {"me.phone"=>{"in"=> \["SELECT phone FROM borrowers WHERE regexp_replace(phone,?,?) LIKE ?","[^0-9]","",$query->{'like'}]} Option 2: \["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"] Option 3: Delete {"me.phone"=>{"like"=>"%020404123456%"}} Inject the following into the WHERE via the $attributes: \["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"] On a database of over 100,000 patrons, Option 1 takes 3 seconds to return while Option 2 takes .2 seconds to return. Option 3 is a bit harder to test but should be the .2 seconds too. The problem with Option 2 is that it nukes the original hashref, so if that hashref contains other keys for an OR query, they’d be lost. I was leaning towards Option 1 until I figured out Option 3. Forutnately, DataTables sends a particular HTTP header, so I’m able to do the re-writing just for DataTables requests to the API. I’ll be posting the patch to https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 shortly. Even if the community doesn’t go this way, I might use it locally anyway going forward. I’ve started rolling out a similar method for older Kohas because of the number of libraries flagging phone number searching as a problem. One way or another it’s a problem that needs to be solved… David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: dcook(a)prosentient.com.au <dcook(a)prosentient.com.au> Sent: Thursday, 15 September 2022 1:23 PM To: 'Tomas Cohen Arazi' <tomascohen(a)theke.io> Cc: 'koha-devel' <koha-devel(a)lists.koha-community.org>; 'Kyle Hall' <kyle(a)bywatersolutions.com>; 'Martin Renvoize' <martin.renvoize(a)ptfs-europe.com> Subject: RE: Handling normalized phone number data So you’d add it to the client side, eh… I looked through “koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc” and “koha-tmpl/intranet-tmpl/prog/js/datatables.js”, and found the DataTables integration with the REST API to be a bit… obscure. But now I see what you mean about adding an embed option via the kohaTable constructor. Although like Kyle was saying. Adding a phone number table wouldn’t solve this problem (unless we had a normalized phone number column). That said, we wouldn’t need to normalize the phone number data, if we could use use SQL functions on the left hand side of a comparison: https://metacpan.org/dist/DBIx-Class/view/lib/DBIx/Class/Manual/Cookbook.po… It’s doable from a DBIC level but I don’t see how we can do it with the DataTables + REST API integration since it requires Perl-specific syntax that JSON can’t handle. Handing DBIC-like syntax to the REST API makes this really hard. The only thing I can think to do is to rewrite the query after it hits the REST API. I’ll be writing about that again shortly… David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io <mailto:tomascohen@theke.io> > Sent: Thursday, 15 September 2022 11:06 AM To: David Cook <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > Cc: koha-devel <koha-devel(a)lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; Kyle Hall <kyle(a)bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize(a)ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> > Subject: Re: Handling normalized phone number data For the REST API you would just add an embed option. Correct. El mié, 14 sept 2022 21:37, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: I think at some point both the phone and email details should be moved into their own table(s), and then 1 would get a primary flag. With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm. If we had a “phone” table, we could potentially store both free-text (potentially formatted) and normalized versions of the phone number. Then we’d just update the patron search to use the normalized version. But all of that sounds like a lot of effort :/ David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io <mailto:tomascohen@theke.io> > Sent: Wednesday, 14 September 2022 11:10 PM To: dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> Cc: koha-devel <koha-devel(a)lists.koha-community.org>; Kyle Hall <kyle(a)bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize(a)ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> > Subject: Re: Handling normalized phone number data Should we also think of moving phones to their own table? El mié, 14 sept 2022 a las 3:43, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: Hi all, I’ve been working lately on https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 in an effort to let staff users easily search phone numbers in the Patrons module. Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API. So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. Thoughts? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F

1 0

Re: [Koha-devel] Handling normalized phone number data
by dcook＠prosentient.com.au 15 Sep '22

15 Sep '22

So you’d add it to the client side, eh… I looked through “koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc” and “koha-tmpl/intranet-tmpl/prog/js/datatables.js”, and found the DataTables integration with the REST API to be a bit… obscure. But now I see what you mean about adding an embed option via the kohaTable constructor. Although like Kyle was saying. Adding a phone number table wouldn’t solve this problem (unless we had a normalized phone number column). That said, we wouldn’t need to normalize the phone number data, if we could use use SQL functions on the left hand side of a comparison: https://metacpan.org/dist/DBIx-Class/view/lib/DBIx/Class/Manual/Cookbook.po… It’s doable from a DBIC level but I don’t see how we can do it with the DataTables + REST API integration since it requires Perl-specific syntax that JSON can’t handle. Handing DBIC-like syntax to the REST API makes this really hard. The only thing I can think to do is to rewrite the query after it hits the REST API. I’ll be writing about that again shortly… David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io> Sent: Thursday, 15 September 2022 11:06 AM To: David Cook <dcook(a)prosentient.com.au> Cc: koha-devel <koha-devel(a)lists.koha-community.org>; Kyle Hall <kyle(a)bywatersolutions.com>; Martin Renvoize <martin.renvoize(a)ptfs-europe.com> Subject: Re: Handling normalized phone number data For the REST API you would just add an embed option. Correct. El mié, 14 sept 2022 21:37, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: I think at some point both the phone and email details should be moved into their own table(s), and then 1 would get a primary flag. With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm. If we had a “phone” table, we could potentially store both free-text (potentially formatted) and normalized versions of the phone number. Then we’d just update the patron search to use the normalized version. But all of that sounds like a lot of effort :/ David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io <mailto:tomascohen@theke.io> > Sent: Wednesday, 14 September 2022 11:10 PM To: dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> Cc: koha-devel <koha-devel(a)lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; Kyle Hall <kyle(a)bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize(a)ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> > Subject: Re: Handling normalized phone number data Should we also think of moving phones to their own table? El mié, 14 sept 2022 a las 3:43, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: Hi all, I’ve been working lately on https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 in an effort to let staff users easily search phone numbers in the Patrons module. Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API. So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. Thoughts? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F

1 0

Re: [Koha-devel] Handling normalized phone number data
by dcook＠prosentient.com.au 15 Sep '22

15 Sep '22

I think at some point both the phone and email details should be moved into their own table(s), and then 1 would get a primary flag. With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm. If we had a “phone” table, we could potentially store both free-text (potentially formatted) and normalized versions of the phone number. Then we’d just update the patron search to use the normalized version. But all of that sounds like a lot of effort :/ David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Tomas Cohen Arazi <tomascohen(a)theke.io> Sent: Wednesday, 14 September 2022 11:10 PM To: dcook(a)prosentient.com.au Cc: koha-devel <koha-devel(a)lists.koha-community.org>; Kyle Hall <kyle(a)bywatersolutions.com>; Martin Renvoize <martin.renvoize(a)ptfs-europe.com> Subject: Re: Handling normalized phone number data Should we also think of moving phones to their own table? El mié, 14 sept 2022 a las 3:43, <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió: Hi all, I’ve been working lately on https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 in an effort to let staff users easily search phone numbers in the Patrons module. Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API. So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. Thoughts? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F

1 0

Re: [Koha-devel] Handling normalized phone number data
by dcook＠prosentient.com.au 15 Sep '22

15 Sep '22

Glad to hear other folk are interested to store numbers in E.164 too! Like you say, we’re already doing it for SMS numbers, so I think it makes sense to do it for all phone numbers. Kyle, what do you think about my Koha::Patron->phone() method for doing that? I was thinking how it would be nice to format them for display, but it’s probably easier said than done. For instance, in Australia, we have several different formats (https://www.stylemanual.gov.au/grammar-punctuation-and-conventions/numbers-…) * 0412 345 678 (mobile) * 02 1234 5678 (landline) * 1300 123 456 (nation-wide local rate number) * 13 00 00 (alternative nation-wide local rate number) It looks like Number::Phone doesn’t support Australian numbers and Number::Phone::Normalize looks old/simple. David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Kyle Hall <kyle(a)bywatersolutions.com> Sent: Wednesday, 14 September 2022 8:26 PM To: dcook(a)prosentient.com.au Cc: koha-devel <koha-devel(a)lists.koha-community.org>; Tomas Cohen Arazi <tomascohen(a)theke.io>; Martin Renvoize <martin.renvoize(a)ptfs-europe.com> Subject: Re: Handling normalized phone number data Storing numbers in E.164 makes sense to me! We already enforce it for SMS numbers, so it would make sense to extend this to all other phone numbers. It would then be trivial to have a phone number format syspref in the same way we have a date format syspref. For searching it would be as simple as stripping all non-numerics from the search term and performing a substring search. Perhaps Number::Phone or Number::Phone::Normalize would be of use. Kyle On Wed, Sep 14, 2022 at 2:43 AM <dcook(a)prosentient.com.au <mailto:dcook@prosentient.com.au> > wrote: Hi all, I’ve been working lately on https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 in an effort to let staff users easily search phone numbers in the Patrons module. Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API. So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. Thoughts? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -- Kyle M. Hall Loose Cannon, ByWater Solutions <mailto:kyle@bywatersolutions.com> kyle(a)bywatersolutions.com <http://bywatersolutions.com/> https://bywatersolutions.com <http://bywatersolutions.com/what-is-koha/> What is Koha?

1 0

Enable libraries to moderate OPAC self-registrations
by Alex Buckley 14 Sep '22

14 Sep '22

Kia ora/Hello Koha community, I am currently working on reviving bug 25090 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=25090> ( Moderate OPAC self-registrations before a patron account is created ). *New proposed functionality:* Step 1: The library enables both the new 'PatronSelfRegistrationModeration' syspref and the existing 'OpacResetPassword'syspref. Step 2: When a user submits an OPAC self-registration their Koha patron account is not created immediately - i.e. they cannot yet log into the OPAC. Step 3: A pending registration link appears at the bottom of the staff client home page (like what's currently done with new purchase suggestions, or OPAC patron modification requests). Step 4: Librarians can click on the link to go to a page to approve or decline the registration. Step 4a: If approved the user is sent an email notice, containing their Koha username and an OPAC reset password link. Step 4b: If declined the user is sent a different email notice. *The rationale for adding this feature:* You can currently limit the circulation of self-registered patrons - by using the PatronSelfRegistrationDefaultCategory syspref and creating circulation rule(s) for that category. However, users only need an OPAC login (without the ability to circulate) to access electronic content providers (integrated with Koha via STunnel/SIP2). Some electronic content providers charge libraries based on their usage. Meaning it might not be optimal having anyone from around the world self-registering for a library OPAC login and accessing electronic content from some providers, therefore, incurring extra costs for the library. Bug 25090 was originally developed in the early days of the pandemic to ensure new self-registering OPAC users accessing 3rd party databases were coming from acceptable locations i.e. they were members of the organisation the library is in. More details can be found here: https://www.catalyst.net.nz/blog/mental-health-education-resource-library-n… *Questions I would like to hear your thoughts on please:* Q1: Are you in favour of this as a new feature in Koha? Q2: Would you prefer a new database table be added for self-registrations awaiting approval, or should I use the borrowers_modifications table - as is used by OPAC patron modification requests? Q3: How would you envisage this self-registration moderation feature fitting in with the existing PatronSelfRegistrationVerifyByEmailand PatronSelfRegistrationDefaultCategory sysprefs? Any thoughts much appreciated. Kind regards, Alex

5 16

Handling normalized phone number data
by dcook＠prosentient.com.au 14 Sep '22

14 Sep '22

Hi all, I've been working lately on https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 in an effort to let staff users easily search phone numbers in the Patrons module. Currently, phone number searching doesn't work if you have punctuation or other formatting inconsistent with your search query. So I'm proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that's seemingly impossible now that we're using DataTables and the REST API. So it seems to me the only way forward is to normalize the data in the database. I've attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it's saved in the database, but I don't know what other Koha folk think about that. Thoughts? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595

1 0

Call for news - Newsletter September 2022
by Koha Newsletter 13 Sep '22

13 Sep '22

I'm collecting news for this month's newsletter. Send anything noteworthy to: kohanews (at) gmail (dot) com News criteria: -------------- * News items can be of any length, images are fine. * Please send your text in plain text or as simple HTML (no Word / Google documents etc) * Anything and everything Koha. * Submit by the 26th of the month. For events: * Please include dates for past events. One month back is the cut-off. * Announcements for future events with dates to be announced are fine, e. g. Kohacon. If you are working on an interesting project or development related to Koha, please let me know and I'll include it in the development section. Thank you! Michael Kuhn Editor, Koha Community Newsletter

1 0

Forward this to librarians prone to contribute: Monday 26th: patch testing session to help enhancements and bug fixes to be released
by Victor Grousset/tuxayo 09 Sep '22

09 Sep '22

Hi :) And also come, developers, admins, trainers, etc if you like a dedicated time with the community to test patches. Let's meet at any moment in the following timeframe: 13h-19h UTC / 14h-20h UK/ 9h-15h US East/6h-12h US West The place is https://visio.chapril.org/koha Here is a resource about how to test patches using sandboxes: https://wiki.koha-community.org/wiki/Sandboxes Modalities: The idea is to test patches to contribute to Koha. That's one of the **easiest and best way** to contribute to Koha. There is a huge need of testing to unblock patches and it would make a sizable difference in the pace of development of Koha if we reduce this bottleneck. No need for particular skills, just basic Koha usage. You can come an leave at any moment, even for a short time it's possible to get simple stuff moving forward. We will use a VoIP call with chat tool to speak and eventually screenshare. It's possible to join without sound and only use chat. So if you are at the front desk and there are not much patrons you can still test patches ^^ And also it's easier if you aren't comfortable with oral english. No need to have a local Koha instance. Just with a web browser we can use the sandboxes [1] to have temporary Kohas to test most of patches and do whatever we want without bad consequences. Cheers, [1] https://wiki.koha-community.org/wiki/Sandboxes -- Victor Grousset/tuxayo

1 0