Resolve inconsistent results when invoking LOWER() in C vs. UTF8 locale databases
authordbs <dbs@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Sat, 12 Mar 2011 02:59:01 +0000 (02:59 +0000)
committerdbs <dbs@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Sat, 12 Mar 2011 02:59:01 +0000 (02:59 +0000)
commitae167d1760a5e452ed8e320921cd7b8e35061239
treed99d6e822beb8097d8e15dd84dfa8ae4161db3e3
parent09223aa9bed6a62329c6fc3844a64aa97d1f1e10
Resolve inconsistent results when invoking LOWER() in C vs. UTF8 locale databases

As reported by the Fundamental Science Library of Armenia, patron searches
were not retrieving patrons who were known to be part of the patron database.

While the initial approach to solve this problem used an encode_utf8() call
to encode the data before passing it on to the database, this turned out to
break the patron search function in other environments.

Testing by Dan Wells confirmed that the LOWER() function returned different
results when invoked against text in a database created with LC_CTYPE=C vs
a database created with LC_CTYPE=*.UTF-8. As the patron search function
used a Perl lc() function call to convert the incoming data to lowercase,
the success of the call depended on the LC_CTYPE value of the database.

To avoid this problem in the future, we define our own evergreen.lowercase()
function that can reliably produce lowercase text for characters outside
the Latin1 range, and we convert our indexes and function calls to use that
function consistently.

git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_2_1@19716 dcc99617-32d9-48b4-a31d-7c20da2025e4
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm
Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm
Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm
Open-ILS/src/perlmods/lib/OpenILS/WWW/Reporter/transforms.pm
Open-ILS/src/sql/Pg/002.functions.general.sql [new file with mode: 0644]
Open-ILS/src/sql/Pg/002.schema.config.sql
Open-ILS/src/sql/Pg/005.schema.actors.sql
Open-ILS/src/sql/Pg/012.schema.vandelay.sql
Open-ILS/src/sql/Pg/build-db.sh
Open-ILS/src/sql/Pg/upgrade/0498.lowercase_via_perl.sql [new file with mode: 0644]