[Tickets #10803] Re: Errors in various variables with horde_db 1.1.2 & postgresql 9.1.1

[bugs at horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/10803
------------------------------------------------------------------------------
  Ticket             | 10803
  Updated By         | Michael Slusarz <slusarz at horde.org>
  Summary            | Errors in various variables with horde_db 1.1.2 &
                     | postgresql 9.1.1
  Queue              | Horde Framework Packages
  Version            | Git master
  Type               | Bug
  State              | Feedback
-Priority           | 1. Low
+Priority           | 3. High
-Milestone          |
+Milestone          | Horde 4.0.13
  Patch              |
-Owners             |
+Owners             | Horde Developers, Chuck Hagenbuch, Jan Schneider,
                     | Michael Slusarz
------------------------------------------------------------------------------


Michael Slusarz <slusarz at horde.org> (2011-11-29 14:40) wrote:

> This doesn't make sense though.  We only send escaped values if the  
> column is identified as 'binary'.  So your prefs table is identified  
> that column as binary, even though it isn't so it is not being  
> correctly decoded when retrieving.  Or at least I think that is what  
> the code is saying.  As I always note: the DB code makes no sense to  
> me.  I have difficult tracing the code.

Figured this out.  The problem is that the Prefs library is declaring  
the value field as a *binary* field, and therefore it explicitly uses  
a Horde_Db_Value_Binary object.  So this object is internally calling  
quoteBinary before sending to the server.

Tracing the commit log, this commit broke things because it eliminated  
the 'BYTEA' definition for postrgres:

commit 6d2e2cf77674ba1dda28fcbe9c01e2a9745a5130
Author: Jan Schneider <jan at horde.org>
Date:   Fri Jan 28 18:05:33 2011 +0100

     Convert Horde_Prefs to migrations.


However, this later commit:

commit 9e5828c9e08a801d8cf4ac307da10d4ab129d436
Author: Chuck Hagenbuch <chuck at horde.org>
Date:   Sat Mar 12 20:47:41 2011 -0500

     Bug #9153: Standardize on using Horde_Db_Column::binaryToString() for now

     This isn't automatic-elegant, but it works. Also extend the  
binary tests to
     other backends, and fix double-escaping of percents in SQLite binary data.

now assumes that **ALL** DBs are treating a value as binary.  So it  
seems to be the proper solution would be to convert ALL databases to  
binary for the prefs field, since that is what the Prefs code directly  
requires, and has required since release 1.0.

As a fallback, we would have to create some pseudo-type for the  
prefs_field indicating it can be a text field, but only if it supports  
null characters (IIRC, this is why postgresql requires BYTEA).   
However, this would appear to involve significant BC headaches.

Bumping priority - this apparently breaks all postgresql installations  
so it needs to be resolved ASAP.