On 11 March 2011 11:53, Dan Scott <[email protected]> wrote: > On 10 March 2011 10:48, Dan Scott <[email protected]> wrote: >> On 10 March 2011 02:36, Tigran Zargaryan <[email protected]> wrote: >>> Hi Dan, >>> reporting about the current situation with patron registration and search >>> using non-Latin scripts.. >>> After installing OpenSrf 2.0 and EG 2.0 we are able to register and save >>> patrons in Armenian. >>> But when searching the patrons by name or last name (e.g. using Armenian >>> script) the system is replying 'No patrons found matching search criteria'. >>> Same search by patron bar code is giving a positive result. >>> So now we can save the patrons, but retrieval by non-Latin script is >>> resulting to null. > > A further update on this: when I built a Debian Squeeze image using > Evergreen 2.0.3 and OpenSRF 2.0.0-rc2, I was able to reproduce your > problem. > > We traced the specific problem in this instance down to the Unicode > data not being properly encoded in the Perl module before being sent > to the database. Using the Encode Perl module and its encode_utf8() > method to properly encode the incoming arguments resolved the problem. > So that fix will go into 2.0.4 - or, if you want to apply it to your > local server now (and I wouldn't blame you!), you can grab the latest > version of > Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/actor.pm > from SVN and copy it to > /openils/lib/perl5/OpenILS/Application/Storage/Publisher/actor.pm, > then restart the open-ils.storage service and all should suddenly > start working for you. > > Note for self in the future: encode_utf8() (normally paired with > decode_utf8()) is your friend! > > Note that my inability to reproduce this problem on Fedora suggests > that Fedora might be doing some automatic encoding of the data that > the Debian-based distributions don't do. Interesting... >
And one more update. Thanks to the assistance of Mike Rylander, Dan Wells, and others in the #evergreen IRC channel, we eventually determined that the database locale (as determined by the "SHOW LC_CTYPE;" command at the psql prompt) was behind some of the inconsistent testing results. As of PostgreSQL 8.4, you can set the database locale when you create the database, using the --lc-ctype and --lc-collate flags to the createdb command. The recommended database locale for performance reasons is "C", essentially meaning no locale. However, the default locale is typically en-US.UTF-8 (or some other locale variation, key being that it is a UTF-8 locale). When the database locale was *.UTF-8, the problems that you reported occurred because the database LOWER() function was unable to correctly convert the input string to lowercase; the encode_utf8() call turned out to fix the problem in that case, but for the wrong reasons. Introducing the encode_utf8() call broke the search results on databases with locale = C. At the suggestion of Mike Rylander, I created a new database function that uses the Perl lc() function to convert incoming text to lower case and redefined the indexes that used the built-in database LOWER() function to use the new evergreen.lowercase() function. This means that when we use the Perl lc() function in business logic, we can count on the database providing the same results when we call the corresponding evergreen.lowercase() database function to provide a match. I'll admit to still being a little nervous about this direction, but it will at the very least provide a consistent result and solve the problem that we're seeing. As an addendum, I have updated the install documentation in the wiki to include the commands required to set the LC_CTYPE and LC_COLLATE variables to "C" when the database is created. And hopefully that's the last of these problems :)
