<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body>A new request with request id 7238 has been created by koha-devel-request@lists.koha-community.org. Short info on the request is : <br><br>Title : Koha-devel Digest, Vol 202, Issue 17<br>Category : <br>Description : <div>Send Koha-devel mailing list submissions to<br> koha-devel@lists.koha-community.org<br><br>To subscribe or unsubscribe via the World Wide Web, visit<br> https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel<br>or, via email, send a message with subject or body 'help' to<br> koha-devel-request@lists.koha-community.org<br><br>You can reach the person managing the list at<br> koha-devel-owner@lists.koha-community.org<br><br>When replying, please edit your Subject line so it is more specific<br>than "Re: Contents of Koha-devel digest..."<br><br><br>Today's Topics:<br><br> 1. Re: Handling normalized phone number data<br> (dcook@prosentient.com.au)<br> 2. Re: Handling normalized phone number data<br> (dcook@prosentient.com.au)<br><br><br>----------------------------------------------------------------------<br><br>Message: 1<br>Date: Thu, 15 Sep 2022 13:23:11 +1000<br>From: <dcook@prosentient.com.au><br>To: "'Tomas Cohen Arazi'" <tomascohen@theke.io><br>Cc: "'koha-devel'" <koha-devel@lists.koha-community.org>, "'Kyle<br> Hall'" <kyle@bywatersolutions.com>, "'Martin Renvoize'"<br> <martin.renvoize@ptfs-europe.com><br>Subject: Re: [Koha-devel] Handling normalized phone number data<br>Message-ID: <1aff01d8c8b2$8fc69640$af53c2c0$@prosentient.com.au><br>Content-Type: text/plain; charset="utf-8"<br><br>So you’d add it to the client side, eh…<br><br> <br><br>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. <br><br> <br><br>Although like Kyle was saying. Adding a phone number table wouldn’t solve this problem (unless we had a normalized phone number column).<br><br> <br><br>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.pod#Using-SQL-functions-on-the-left-hand-side-of-a-comparison<br><br> <br><br>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. <br><br> <br><br>The only thing I can think to do is to rewrite the query after it hits the REST API. <br><br> <br><br>I’ll be writing about that again shortly…<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br>From: Tomas Cohen Arazi <tomascohen@theke.io> <br>Sent: Thursday, 15 September 2022 11:06 AM<br>To: David Cook <dcook@prosentient.com.au><br>Cc: koha-devel <koha-devel@lists.koha-community.org>; Kyle Hall <kyle@bywatersolutions.com>; Martin Renvoize <martin.renvoize@ptfs-europe.com><br>Subject: Re: Handling normalized phone number data<br><br> <br><br>For the REST API you would just add an embed option. Correct.<br><br> <br><br>El mié, 14 sept 2022 21:37, <dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió:<br><br>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. <br><br> <br><br>With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm.<br><br> <br><br>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. <br><br> <br><br>But all of that sounds like a lot of effort :/<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br>From: Tomas Cohen Arazi <tomascohen@theke.io <mailto:tomascohen@theke.io> > <br>Sent: Wednesday, 14 September 2022 11:10 PM<br>To: dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> <br>Cc: koha-devel <koha-devel@lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; Kyle Hall <kyle@bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize@ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> ><br>Subject: Re: Handling normalized phone number data<br><br> <br><br>Should we also think of moving phones to their own table?<br><br> <br><br>El mié, 14 sept 2022 a las 3:43, <dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió:<br><br>Hi all,<br><br> <br><br>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.<br><br> <br><br>Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. <br><br> <br><br>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.<br><br> <br><br>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. <br><br> <br><br>Thoughts?<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br><br><br><br> <br><br>-- <br><br>Tomás Cohen Arazi<br><br>Theke Solutions (http://theke.io)<br>✆ +54 9351 3513384<br>GPG: B2F3C15F<br><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20220915/6a5ab28b/attachment-0001.htm><br><br>------------------------------<br><br>Message: 2<br>Date: Thu, 15 Sep 2022 14:32:42 +1000<br>From: <dcook@prosentient.com.au><br>To: "'Tomas Cohen Arazi'" <tomascohen@theke.io><br>Cc: "'koha-devel'" <koha-devel@lists.koha-community.org>, "'Kyle<br> Hall'" <kyle@bywatersolutions.com>, "'Martin Renvoize'"<br> <martin.renvoize@ptfs-europe.com><br>Subject: Re: [Koha-devel] Handling normalized phone number data<br>Message-ID: <1b1001d8c8bc$41dbf290$c593d7b0$@prosentient.com.au><br>Content-Type: text/plain; charset="utf-8"<br><br>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:<br><br> <br><br>Current:<br><br>{"me.phone"=>{"like"=>"%020404123456%"}}<br><br> <br><br>Option 1:<br><br>{"me.phone"=>{"in"=> \["SELECT phone FROM borrowers WHERE regexp_replace(phone,?,?) LIKE ?","[^0-9]","",$query->{'like'}]}<br><br> <br><br>Option 2:<br><br>\["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"]<br><br> <br><br>Option 3:<br><br>Delete {"me.phone"=>{"like"=>"%020404123456%"}}<br><br>Inject the following into the WHERE via the $attributes: \["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"]<br><br> <br><br>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.<br><br> <br><br>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. <br><br> <br><br>I was leaning towards Option 1 until I figured out Option 3.<br><br> <br><br>Forutnately, DataTables sends a particular HTTP header, so I’m able to do the re-writing just for DataTables requests to the API.<br><br> <br><br>I’ll be posting the patch to https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817 shortly.<br><br> <br><br>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. <br><br> <br><br>One way or another it’s a problem that needs to be solved…<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br>From: dcook@prosentient.com.au <dcook@prosentient.com.au> <br>Sent: Thursday, 15 September 2022 1:23 PM<br>To: 'Tomas Cohen Arazi' <tomascohen@theke.io><br>Cc: 'koha-devel' <koha-devel@lists.koha-community.org>; 'Kyle Hall' <kyle@bywatersolutions.com>; 'Martin Renvoize' <martin.renvoize@ptfs-europe.com><br>Subject: RE: Handling normalized phone number data<br><br> <br><br>So you’d add it to the client side, eh…<br><br> <br><br>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. <br><br> <br><br>Although like Kyle was saying. Adding a phone number table wouldn’t solve this problem (unless we had a normalized phone number column).<br><br> <br><br>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.pod#Using-SQL-functions-on-the-left-hand-side-of-a-comparison<br><br> <br><br>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. <br><br> <br><br>The only thing I can think to do is to rewrite the query after it hits the REST API. <br><br> <br><br>I’ll be writing about that again shortly…<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br>From: Tomas Cohen Arazi <tomascohen@theke.io <mailto:tomascohen@theke.io> > <br>Sent: Thursday, 15 September 2022 11:06 AM<br>To: David Cook <dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> ><br>Cc: koha-devel <koha-devel@lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> >; Kyle Hall <kyle@bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize@ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> ><br>Subject: Re: Handling normalized phone number data<br><br> <br><br>For the REST API you would just add an embed option. Correct.<br><br> <br><br>El mié, 14 sept 2022 21:37, <dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió:<br><br>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. <br><br> <br><br>With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm.<br><br> <br><br>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. <br><br> <br><br>But all of that sounds like a lot of effort :/<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br>From: Tomas Cohen Arazi <tomascohen@theke.io <mailto:tomascohen@theke.io> > <br>Sent: Wednesday, 14 September 2022 11:10 PM<br>To: dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> <br>Cc: koha-devel <koha-devel@lists.koha-community.org>; Kyle Hall <kyle@bywatersolutions.com <mailto:kyle@bywatersolutions.com> >; Martin Renvoize <martin.renvoize@ptfs-europe.com <mailto:martin.renvoize@ptfs-europe.com> ><br>Subject: Re: Handling normalized phone number data<br><br> <br><br>Should we also think of moving phones to their own table?<br><br> <br><br>El mié, 14 sept 2022 a las 3:43, <dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> > escribió:<br><br>Hi all,<br><br> <br><br>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.<br><br> <br><br>Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. <br><br> <br><br>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.<br><br> <br><br>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. <br><br> <br><br>Thoughts?<br><br> <br><br>David Cook<br><br>Senior Software Engineer<br><br>Prosentient Systems<br><br>Suite 7.03<br><br>6a Glen St<br><br>Milsons Point NSW 2061<br><br>Australia<br><br> <br><br>Office: 02 9212 0899<br><br>Online: 02 8005 0595<br><br> <br><br><br><br><br> <br><br>-- <br><br>Tomás Cohen Arazi<br><br>Theke Solutions (http://theke.io)<br>✆ +54 9351 3513384<br>GPG: B2F3C15F<br><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20220915/02edc252/attachment.htm><br><br>------------------------------<br><br>Subject: Digest Footer<br><br>_______________________________________________<br>Koha-devel mailing list<br>Koha-devel@lists.koha-community.org<br>https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel<br>website : https://www.koha-community.org/<br>git : https://git.koha-community.org/<br>bugs : https://bugs.koha-community.org/<br><br><br>------------------------------<br><br>End of Koha-devel Digest, Vol 202, Issue 17<br>*******************************************<br></div><br><br>NOTE: You are receiving this mail because, the Requester/Technician wanted you to get notified on this request creation.<br></body></html>