[jifty-devel] Jifty::DBI quoting bug with PostgreSQL bool fields

Jesse Vincent jesse at bestpractical.com
Fri Sep 8 14:39:07 EDT 2006


On Sep 6, 2006, at 7:59 AM, Sean E. Millichamp wrote:

> On Wed, 2006-09-06 at 00:21 -0400, Jesse Vincent wrote:
>
>> On Tue, Sep 05, 2006 at 07:55:00AM -0400, Sean E. Millichamp wrote:
>>> I have not (and probably will not for a while) had the time to  
>>> actually
>>> find what is needed to fix this, but I thought I'd mention it if  
>>> someone
>>> else wants to look into it.  I should further state that I  
>>> haven't even
>>> confirmed if it is a Jifty bug or a Jifty::DBI bug.
>>
>> I think this is a case of "Different databases define boolean  
>> operators
>> differently"  Will everything always take unquoted true and false?  I
>> think jifty::dbi may just need to be smarter about bool types, but  
>> I'd
>> be happiest if we can find a [database] platform-independent way  
>> to do it.
>
> Well, SQLite will not take true and false unquoted, and but even if  
> you
> do pass them quoted (Postgres will take true and false both quoted and
> unquoted) they seem to end up being evaluated and fetched as strings
> later, not as true/false 0/1 boolean values.

That's sqlite's modus operandi. It's agressively untyped.

>
> For Postgres and SQLite a solution would be to make sure that all
> numerical default values are always quoted when the CREATE TABLE is
> issued (at least for bool columns) and then pass 0/1 as the value  
> in the
> column definition.  That seems to satisfy both.  I have no idea about
> MySQL or others,

It works for mysql. I don't have an Oracle handy to test on. Sounds  
like we want a bit of code to coerce booleans.


> but I would like to think a quoted 0/1 would always be
> acceptable for a bool.

I'd like to think that too, but 15 years of building database-backed  
apps has taught me that "it's nice to want things."
;)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
Url : http://lists.bestpractical.com/pipermail/jifty-devel/attachments/20060908/96760c9a/PGP.pgp


More information about the jifty-devel mailing list