Thread: Boolean output format
A friend of mine has told me that using the Zope pgsql driver you can set the output format of postgres booleans... Unfortunately, I'm using php and would like to do this also. Is the zope driver doing this or is it some sort of option that can be sent when the connection is made or a query that can be run? Thanks, Garo. =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
The best way change the output style in general is a stored procedure.
However, with booleans it's simple enough that you could write it out inline
if you want:
------------------------------------------------------------------------------
jdavis=> create table b(a bool);
CREATE
jdavis=> insert into b values('t');
INSERT 67682 1
jdavis=> insert into b values('f');
INSERT 67683 1
jdavis=> select case when a then 'YES' else 'NO' end from b ;
case
------
YES
NO
(2 rows)
--------------------------------------------------------------------------------
The case statement basically just special cases the two values. In this case
it of course changes 't' to 'YES' and 'f' to 'NO'.
You could also make a SQL function out of it no problem:
jdavis=> create function myfn(bool) returns text as 'select case when $1 then
''YES'' else ''NO'' end;' language 'sql';
Then just use that in your selects:
jdavis=> select myfn(a) from b;
myfn
------
YES
NO
(2 rows)
Regards,
Jeff Davis
On Friday 04 October 2002 06:17 pm, Garo Hussenjian wrote:
> A friend of mine has told me that using the Zope pgsql driver you can set
> the output format of postgres booleans...
>
> Unfortunately, I'm using php and would like to do this also.
>
> Is the zope driver doing this or is it some sort of option that can be sent
> when the connection is made or a query that can be run?
>
> Thanks,
> Garo.
>
>
> =-=-==-=-=-==
>
> Xapnet Internet Solutions
> 1501 Powell St., Suite N
> Emeryville, CA 94608
>
> Tel - (510) 655-9771
> Fax - (510) 655-9775
> Web - http://www.xapnet.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Thanks, Jeff.
The problem is that this would require that I rewrite many queries to
utilize the function... I'm currently using smallint to store the bools
because I want 0/1 as output. I'd like to move away from this but in php "f"
evaluates true!
I have followed a good deal of discussion regarding SET DATESTYLE and I
really was hoping that there was some way to SET BOOLEANSTYLE or something
like this.
Unfortunately, I find both the case statement and the sql function to be
overkill... My thinking is why should it require more work to deal with the
simplest of all data types... I very much still appreciate the response. :)
I think I am ultimately making a case for a new feature in a future version
of postgres. This is also important for people who want to port MySQL
applications to PostgreSQL! As a committed postgres advocate, I never want
to say we can't get there from here... (MySQL outputs bools as 1/0 if I am
not mistaken, though I haven't used it in some time!)
I regret I am not a real hacker or I'd work on this myself!
Best Regards,
Garo.
on 10/4/02 6:36 PM, Jeff Davis at list-pgsql-general@empires.org wrote:
>
> The best way change the output style in general is a stored procedure.
> However, with booleans it's simple enough that you could write it out inline
> if you want:
> ------------------------------------------------------------------------------
> jdavis=> create table b(a bool);
> CREATE
> jdavis=> insert into b values('t');
> INSERT 67682 1
> jdavis=> insert into b values('f');
> INSERT 67683 1
> jdavis=> select case when a then 'YES' else 'NO' end from b ;
> case
> ------
> YES
> NO
> (2 rows)
> ------------------------------------------------------------------------------
> --
>
> The case statement basically just special cases the two values. In this case
> it of course changes 't' to 'YES' and 'f' to 'NO'.
>
> You could also make a SQL function out of it no problem:
>
> jdavis=> create function myfn(bool) returns text as 'select case when $1 then
> ''YES'' else ''NO'' end;' language 'sql';
>
> Then just use that in your selects:
> jdavis=> select myfn(a) from b;
> myfn
> ------
> YES
> NO
> (2 rows)
>
>
> Regards,
> Jeff Davis
>
>
> On Friday 04 October 2002 06:17 pm, Garo Hussenjian wrote:
>> A friend of mine has told me that using the Zope pgsql driver you can set
>> the output format of postgres booleans...
>>
>> Unfortunately, I'm using php and would like to do this also.
>>
>> Is the zope driver doing this or is it some sort of option that can be sent
>> when the connection is made or a query that can be run?
>>
>> Thanks,
>> Garo.
>>
>>
>> =-=-==-=-=-==
>>
>> Xapnet Internet Solutions
>> 1501 Powell St., Suite N
>> Emeryville, CA 94608
>>
>> Tel - (510) 655-9771
>> Fax - (510) 655-9775
>> Web - http://www.xapnet.com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
=-=-==-=-=-==
Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608
Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com
Garo Hussenjian <garo@xapnet.com> writes: > Thanks, Jeff. > > The problem is that this would require that I rewrite many queries to > utilize the function... I'm currently using smallint to store the bools > because I want 0/1 as output. I'd like to move away from this but in php "f" > evaluates true! The database adapters I've used (Perl DBI and Java JDBC) arrange for Boolean columns in query output to resolve as "true" or "false" according to that language's conventions. If the PHP adapter doesn't do this, someone needs to fix it IMHO. Different DBs have different conventions about a lot of things and the adapters need to cope. -Doug
I certainly see the dillemma with php interpreting 'f' as true.
I think that your current solution of using a smallint type might be the best
way to go. After all, you want the output in int form, right? Add a
constraint like:
CREATE TABLE b(a smallint check(a in (0,1)));
That means that if you try to enter something other than 0 or 1, it throws an
error. You can insert how you like, it only wastes 1 byte (smallint is only 2
bytes), and you can retrieve the result as you like.
And here's the long explanation about why (feel free not to read it :):
All relational databases, including MySQL, return types as text. Php is
dynamically typed, so when it recieves the text character "0", and you ask it
to interpret the result as a boolean value, it chooses false. So, your
application is already doing some translating.
Now, imagine from the database perspective: should you set up several
conversion functions for every data type? I haven't even counted the
datatypes in postgres, but there are a lot. You could make a case that most
of those datatypes might be more desirable in a different form in many
situations. I would say that it's impossible to cover all of the possible
situations unless you allowed the user to make an arbitrary function that
converted data on the fly. Well, postgres has that! You can make a
user-defined data type, and specify the exact formats that you'd like to
input the data, store the data, and output the data.
So, you could make a datatype called "zero_or_one" that did exactly as you
say. Easy? Not really. But it's there if you need it (that one byte matters
if you have enough records).
The developers tend to like general solutions, like the user-defined data
types and the constraints. It's generally pretty difficult to get a new SET
variable added, so it's unlikely they'd go for that for just a boolean
conversion.
So, I suggest continuing with smallint (but do add that constraint, you don't
want an inconsistant database). Are there any other reasons you don't want to
use it?
Of course you're right about MySQL: people aren't gonna like porting. I don't
really see a solution, but if you have one I'd be interested to know. The SET
variable makes sense, but it's just that I get the impression the developers
don't like too many of those.
Disclaimer: the developers might have very different views from what I've
implied. Those are just my impressions of their standpoint.
Regards,
Jeff Davis
On Friday 04 October 2002 06:58 pm, Garo Hussenjian wrote:
> Thanks, Jeff.
>
> The problem is that this would require that I rewrite many queries to
> utilize the function... I'm currently using smallint to store the bools
> because I want 0/1 as output. I'd like to move away from this but in php
> "f" evaluates true!
>
> I have followed a good deal of discussion regarding SET DATESTYLE and I
> really was hoping that there was some way to SET BOOLEANSTYLE or something
> like this.
>
> Unfortunately, I find both the case statement and the sql function to be
> overkill... My thinking is why should it require more work to deal with the
> simplest of all data types... I very much still appreciate the response. :)
>
> I think I am ultimately making a case for a new feature in a future version
> of postgres. This is also important for people who want to port MySQL
> applications to PostgreSQL! As a committed postgres advocate, I never want
> to say we can't get there from here... (MySQL outputs bools as 1/0 if I am
> not mistaken, though I haven't used it in some time!)
>
> I regret I am not a real hacker or I'd work on this myself!
>
> Best Regards,
> Garo.
>
> on 10/4/02 6:36 PM, Jeff Davis at list-pgsql-general@empires.org wrote:
> > The best way change the output style in general is a stored procedure.
> > However, with booleans it's simple enough that you could write it out
> > inline if you want:
> > -------------------------------------------------------------------------
> >----- jdavis=> create table b(a bool);
> > CREATE
> > jdavis=> insert into b values('t');
> > INSERT 67682 1
> > jdavis=> insert into b values('f');
> > INSERT 67683 1
> > jdavis=> select case when a then 'YES' else 'NO' end from b ;
> > case
> > ------
> > YES
> > NO
> > (2 rows)
> > -------------------------------------------------------------------------
> >----- --
> >
> > The case statement basically just special cases the two values. In this
> > case it of course changes 't' to 'YES' and 'f' to 'NO'.
> >
> > You could also make a SQL function out of it no problem:
> >
> > jdavis=> create function myfn(bool) returns text as 'select case when $1
> > then ''YES'' else ''NO'' end;' language 'sql';
> >
> > Then just use that in your selects:
> > jdavis=> select myfn(a) from b;
> > myfn
> > ------
> > YES
> > NO
> > (2 rows)
> >
> >
> > Regards,
> > Jeff Davis
> >
> > On Friday 04 October 2002 06:17 pm, Garo Hussenjian wrote:
> >> A friend of mine has told me that using the Zope pgsql driver you can
> >> set the output format of postgres booleans...
> >>
> >> Unfortunately, I'm using php and would like to do this also.
> >>
> >> Is the zope driver doing this or is it some sort of option that can be
> >> sent when the connection is made or a query that can be run?
> >>
> >> Thanks,
> >> Garo.
> >>
> >>
> >> =-=-==-=-=-==
> >>
> >> Xapnet Internet Solutions
> >> 1501 Powell St., Suite N
> >> Emeryville, CA 94608
> >>
> >> Tel - (510) 655-9771
> >> Fax - (510) 655-9775
> >> Web - http://www.xapnet.com
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 2: you can get off all lists at once with the unregister command
> >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> =-=-==-=-=-==
>
> Xapnet Internet Solutions
> 1501 Powell St., Suite N
> Emeryville, CA 94608
>
> Tel - (510) 655-9771
> Fax - (510) 655-9775
> Web - http://www.xapnet.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> The database adapters I've used (Perl DBI and Java JDBC) arrange for
> Boolean columns in query output to resolve as "true" or "false"
> according to that language's conventions. If the PHP adapter doesn't
> do this, someone needs to fix it IMHO. Different DBs have different
> conventions about a lot of things and the adapters need to cope.
>
I agree. However, I think "adapter" is too strong a word for PHP's PostgreSQL
interface. It's mostly a wrapper around the C API, and just throws the result
as a string in a variable (just the way it get's it from the C function, as a
string).
Anything beyond that is PHP's dynamic typing at work. In this situation, it's
merely casting MySQL's "0" to false (that's the character zero), and casting
PostgreSQL's "f" to true (bacause it's a non-empty string).
Not something that I'd bet my application on, but it works for PHP/MySQL in
most cases appearently.
Regards,
Jeff Davis
Jeff Davis <list-pgsql-general@empires.org> writes:
> The developers tend to like general solutions, like the user-defined data
> types and the constraints. It's generally pretty difficult to get a new SET
> variable added, so it's unlikely they'd go for that for just a boolean
> conversion.
My two cents (not speaking for core or anything like that, just personal
reaction): my first thought was that SET BOOLEANSTYLE was a reasonable
idea, seeing as how we have SET DATESTYLE. But on second thought I
didn't like it so much. Seems like providing such a choice would be
likely to break those client-side adapters that have gone to the trouble
of correctly interpreting Postgres booleans into their host languages.
Those adapters are going to handle 't' and 'f', but in all probability
they will break if you run them with BOOLEANSTYLE set to anything but
'traditional'. So on reflection this feature seems like it will
penalize the folks who tried to do things right, to reward those who
couldn't be bothered.
Maybe that's stating it too strongly, but there is a definite backwards-
compatibility issue to be considered here.
regards, tom lane
on 10/4/02 7:54 PM, Jeff Davis at list-pgsql-general@empires.org wrote:
>
> I certainly see the dillemma with php interpreting 'f' as true.
>
You are very kind. :)
> I think that your current solution of using a smallint type might be the best
> way to go. After all, you want the output in int form, right? Add a
> constraint like:
> CREATE TABLE b(a smallint check(a in (0,1)));
Interesting... It's looks and acts like a boolean, it's just not a boolean!
I like it though it's not a native solution.
>
> That means that if you try to enter something other than 0 or 1, it throws an
> error. You can insert how you like, it only wastes 1 byte (smallint is only 2
> bytes), and you can retrieve the result as you like.
>
> And here's the long explanation about why (feel free not to read it :):
> All relational databases, including MySQL, return types as text. Php is
> dynamically typed, so when it recieves the text character "0", and you ask it
> to interpret the result as a boolean value, it chooses false. So, your
> application is already doing some translating.
I understand. I can implement a wrapper to convert bools, but I'd have to
store the schema in my application. Using php, the database is the only
place where types are actually known! I should check to see if Pear's DB
object does any type-casting at run-time.
> Now, imagine from the database perspective: should you set up several
> conversion functions for every data type? I haven't even counted the
> datatypes in postgres, but there are a lot. You could make a case that most
> of those datatypes might be more desirable in a different form in many
> situations. I would say that it's impossible to cover all of the possible
> situations unless you allowed the user to make an arbitrary function that
> converted data on the fly. Well, postgres has that! You can make a
> user-defined data type, and specify the exact formats that you'd like to
> input the data, store the data, and output the data.
>
> So, you could make a datatype called "zero_or_one" that did exactly as you
> say. Easy? Not really. But it's there if you need it (that one byte matters
> if you have enough records).
I have thought about this as well. The user-defined types are extremely
useful but they add additional (in this case undesirable) complexity. If we
were talking about anything other than booleans, I would not be making this
case in the first place.
>
> The developers tend to like general solutions, like the user-defined data
> types and the constraints. It's generally pretty difficult to get a new SET
> variable added, so it's unlikely they'd go for that for just a boolean
> conversion.
>
Is this because of processing overhead or other reasons? I'd argue that the
boolean is an extremely important type, and just as (if not more) common as
date and datetime, and it's certainly less complicated.
> So, I suggest continuing with smallint (but do add that constraint, you don't
> want an inconsistant database). Are there any other reasons you don't want to
> use it?
>
Now you've hit the root of the problem! I want to be able to forward and
reverse engineer postgres and mysql schemas in php for a basic set of
datatypes (varchar, int, decimal, boolean, date, datetime, and maybe a
couple others). The problem with smallint is that I can translate boolean to
smallint, but it doesn't work in reverse! This is mostly an interoperability
concern.
I have a database abstraction layer in my application that further
generalizes my queries with some sql abstration... Right now the only
difference between postgres and mysql is the return result of a native
boolean.
> Of course you're right about MySQL: people aren't gonna like porting. I don't
> really see a solution, but if you have one I'd be interested to know. The SET
> variable makes sense, but it's just that I get the impression the developers
> don't like too many of those.
>
I can understand the developers' desire for efficiency in the core. I should
always be weighed against user needs. The answer, in this case, is unlikely
to be a true or false... I'd never expect that the world could be as precise
as our favorite database!
> Disclaimer: the developers might have very different views from what I've
> implied. Those are just my impressions of their standpoint.
I understand and I greatly appreciate your feedback. I wonder if any of the
developers might express their opinions on this. I'd love know what they
think.
Regards,
Garo.
>
> Regards,
> Jeff Davis
>
>
>
> On Friday 04 October 2002 06:58 pm, Garo Hussenjian wrote:
>> Thanks, Jeff.
>>
>> The problem is that this would require that I rewrite many queries to
>> utilize the function... I'm currently using smallint to store the bools
>> because I want 0/1 as output. I'd like to move away from this but in php
>> "f" evaluates true!
>>
>> I have followed a good deal of discussion regarding SET DATESTYLE and I
>> really was hoping that there was some way to SET BOOLEANSTYLE or something
>> like this.
>>
>> Unfortunately, I find both the case statement and the sql function to be
>> overkill... My thinking is why should it require more work to deal with the
>> simplest of all data types... I very much still appreciate the response. :)
>>
>> I think I am ultimately making a case for a new feature in a future version
>> of postgres. This is also important for people who want to port MySQL
>> applications to PostgreSQL! As a committed postgres advocate, I never want
>> to say we can't get there from here... (MySQL outputs bools as 1/0 if I am
>> not mistaken, though I haven't used it in some time!)
>>
>> I regret I am not a real hacker or I'd work on this myself!
>>
>> Best Regards,
>> Garo.
>>
>> on 10/4/02 6:36 PM, Jeff Davis at list-pgsql-general@empires.org wrote:
>>> The best way change the output style in general is a stored procedure.
>>> However, with booleans it's simple enough that you could write it out
>>> inline if you want:
>>> -------------------------------------------------------------------------
>>> ----- jdavis=> create table b(a bool);
>>> CREATE
>>> jdavis=> insert into b values('t');
>>> INSERT 67682 1
>>> jdavis=> insert into b values('f');
>>> INSERT 67683 1
>>> jdavis=> select case when a then 'YES' else 'NO' end from b ;
>>> case
>>> ------
>>> YES
>>> NO
>>> (2 rows)
>>> -------------------------------------------------------------------------
>>> ----- --
>>>
>>> The case statement basically just special cases the two values. In this
>>> case it of course changes 't' to 'YES' and 'f' to 'NO'.
>>>
>>> You could also make a SQL function out of it no problem:
>>>
>>> jdavis=> create function myfn(bool) returns text as 'select case when $1
>>> then ''YES'' else ''NO'' end;' language 'sql';
>>>
>>> Then just use that in your selects:
>>> jdavis=> select myfn(a) from b;
>>> myfn
>>> ------
>>> YES
>>> NO
>>> (2 rows)
>>>
>>>
>>> Regards,
>>> Jeff Davis
>>>
>>> On Friday 04 October 2002 06:17 pm, Garo Hussenjian wrote:
>>>> A friend of mine has told me that using the Zope pgsql driver you can
>>>> set the output format of postgres booleans...
>>>>
>>>> Unfortunately, I'm using php and would like to do this also.
>>>>
>>>> Is the zope driver doing this or is it some sort of option that can be
>>>> sent when the connection is made or a query that can be run?
>>>>
>>>> Thanks,
>>>> Garo.
>>>>
>>>>
>>>> =-=-==-=-=-==
>>>>
>>>> Xapnet Internet Solutions
>>>> 1501 Powell St., Suite N
>>>> Emeryville, CA 94608
>>>>
>>>> Tel - (510) 655-9771
>>>> Fax - (510) 655-9775
>>>> Web - http://www.xapnet.com
>>>>
>>>>
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 2: you can get off all lists at once with the unregister command
>>>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 4: Don't 'kill -9' the postmaster
>>
>> =-=-==-=-=-==
>>
>> Xapnet Internet Solutions
>> 1501 Powell St., Suite N
>> Emeryville, CA 94608
>>
>> Tel - (510) 655-9771
>> Fax - (510) 655-9775
>> Web - http://www.xapnet.com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
=-=-==-=-=-==
Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608
Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com
Well, that's what my disclaimer was for :)
I thought I remembered some rather long discussions about various SETable
variables. I also noticed that the current list of SETable variables is
small. So I kind of put the two together and concluded that the gain from
adding a booleanstyle variable might not be enough to add the extra option.
I don't have any particular argument against the idea, but it kind of worries
me that we'd be headed in the direction of the date/time types. Those seem
complex, and I'd hate to think of boolean as that complex.
Regards,
Jeff Davis
On Friday 04 October 2002 08:43 pm, Tom Lane wrote:
> Jeff Davis <list-pgsql-general@empires.org> writes:
> > The developers tend to like general solutions, like the user-defined data
> > types and the constraints. It's generally pretty difficult to get a new
> > SET variable added, so it's unlikely they'd go for that for just a
> > boolean conversion.
>
> My two cents (not speaking for core or anything like that, just personal
> reaction): my first thought was that SET BOOLEANSTYLE was a reasonable
> idea, seeing as how we have SET DATESTYLE. But on second thought I
> didn't like it so much. Seems like providing such a choice would be
> likely to break those client-side adapters that have gone to the trouble
> of correctly interpreting Postgres booleans into their host languages.
> Those adapters are going to handle 't' and 'f', but in all probability
> they will break if you run them with BOOLEANSTYLE set to anything but
> 'traditional'. So on reflection this feature seems like it will
> penalize the folks who tried to do things right, to reward those who
> couldn't be bothered.
>
> Maybe that's stating it too strongly, but there is a definite backwards-
> compatibility issue to be considered here.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
> I understand. I can implement a wrapper to convert bools, but I'd have to
> store the schema in my application. Using php, the database is the only
> place where types are actually known! I should check to see if Pear's DB
> object does any type-casting at run-time.
I suppose you could also have your application consult the catlogs rather than
store the schema in the app, but that's annoying as well (extra selects).
>
> I have thought about this as well. The user-defined types are extremely
> useful but they add additional (in this case undesirable) complexity. If we
> were talking about anything other than booleans, I would not be making this
> case in the first place.
True enough. Seems like what you want to do is very basic, so it's not
supposed to be hard.
>
> Is this because of processing overhead or other reasons? I'd argue that the
> boolean is an extremely important type, and just as (if not more) common as
> date and datetime, and it's certainly less complicated.
I was mostly wrong about this one, at least with respect to one developer who
also responded. Looks like I spoke too soon. You can read his post, for I
don't think now's the time for me to make another interpretation :)
>
> Now you've hit the root of the problem! I want to be able to forward and
> reverse engineer postgres and mysql schemas in php for a basic set of
> datatypes (varchar, int, decimal, boolean, date, datetime, and maybe a
> couple others). The problem with smallint is that I can translate boolean
> to smallint, but it doesn't work in reverse! This is mostly an
> interoperability concern.
>
> I have a database abstraction layer in my application that further
> generalizes my queries with some sql abstration... Right now the only
> difference between postgres and mysql is the return result of a native
> boolean.
>
I don't entirely understand what you mean, but it looks like you'd like to
easily be able to take data from MySQL and put it in PostgreSQL and vice
versa. Is there a problem with using smallint? PHP should be able to supply
an int to either database or retrieve the result as an int from either
database, right? I don't have a MySQL DB readily available, so I'm not 100%
sure how they handle booleans.
>
> I can understand the developers' desire for efficiency in the core. I
> should always be weighed against user needs. The answer, in this case, is
> unlikely to be a true or false... I'd never expect that the world could be
> as precise as our favorite database!
See above. I'd say the developers do a good job addressing users concerns, and
if they did reject a feature like you describe it would most certainly be for
a reason other than efficiency. Again, of course, I don't speak for the
developers.
Regards,
Jeff Davis
on 10/4/02 8:43 PM, Tom Lane at tgl@sss.pgh.pa.us wrote: > Jeff Davis <list-pgsql-general@empires.org> writes: >> The developers tend to like general solutions, like the user-defined data >> types and the constraints. It's generally pretty difficult to get a new SET >> variable added, so it's unlikely they'd go for that for just a boolean >> conversion. > > My two cents (not speaking for core or anything like that, just personal > reaction): my first thought was that SET BOOLEANSTYLE was a reasonable > idea, seeing as how we have SET DATESTYLE. But on second thought I > didn't like it so much. Seems like providing such a choice would be > likely to break those client-side adapters that have gone to the trouble > of correctly interpreting Postgres booleans into their host languages. > Those adapters are going to handle 't' and 'f', but in all probability > they will break if you run them with BOOLEANSTYLE set to anything but > 'traditional'. So on reflection this feature seems like it will > penalize the folks who tried to do things right, to reward those who > couldn't be bothered. Tom, Thanks for chiming in! I was hoping you were listening. Were there any backward compatibility issues for SET DATESTYLE? What were the repercussions for the adapters? That seems more likely to have had problems than SET BOOLSTYLE. Interestingly, the date formats in PostgreSQL and MySQL are identical. At least for me, the boolean was always a greater problem. Regarding the existing adapters - I very much agree with what you're saying, but such a feature would not necessarily break any existing adapters. The default format would remain unchanged and the event that an end user were to use the brand-new feature with a brand-new distibution of PostgreSQL, I don't think they will hold it against the team if it didn't work with their adapter - as long as it was easy to restore the default and it didn't break anything if left unused. If there were a SET BOOLSTYLE, it's mainly the php folks that will be thanking the postgresql folks. Not to mention the php/mysql->postgres converts who are spared the extra work of porting hundreds of booleans in their applications! I'd be the first one to express my gratitude! I don't think this is a question of reward vs. punishment, but maybe more one of a where the responsibility should fall... If an RDBMS returns only textual data, it does not necessarily imply that the adapter should be interpreting this data. If some adapters do, it need not imply that all adapters should. Lastly, if the RDBMS offers options, not all adapters need to support them, and they have the choice either way. I don't see the harm in providing the choice as long as it does not limit any existing choices in the existing platforms. I don't know if php's treatment of database results is incorrect, but I like the fact that my queries in php return exactly the same results as my queries in the psql client! I work with them in tandem, and I appreciate the transparency of php's adapter! I'd like to think that the php developers made a conscious decision to leave the data alone. I don't really see anything wrong with that, but I'm not familiar with the conventions of writing database drivers. All I know is that if there is a gap between the two platforms, it should be in the best interest of both camps to fill it, and I don't think there need be any price to pay other than a little elbow grease. I hold the work that goes into PostgreSQL as well as PHP in very high regard. Once again, thanks very much for the feedback! Garo. > > Maybe that's stating it too strongly, but there is a definite backwards- > compatibility issue to be considered here. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
>
> Some database abstraction layers provide a consistent query interface to a
> database, like exec() or query(), and you pass in the sql... In our case,
> our abstraction layer also generates much of the sql, so certain queries
> that are very different in MySQL and PgSQL are the same in my code, making
> my applications almost 100% DB-independant. This is the case for my
> databases that use smallint, but I'd like to use "real" booleans without
> loosing the independence.
>
> An example: I get a MySQL db with real smallints and booleans, I translate
> to pgsql and now the bools and original smallints are non-distinguishable!
> I've lost something in the translation, or alternatively, I don't go to the
> smallint and booleans don't evaluate! I'll use smallint for now.
Ah, I think I finally get it. You go MySQL->PostgreSQL->MySQL and end up with
something a little different. Reminds me of some stories I've heard about
online text translators like Babelfish :)
That's certainly a good point.
>
> As for the MySQL thing, it uses 1/0 and as a result remains consistent with
> the php convention for booleans. I guess that's just the trade-off when
> using a dynamically typed language... I suppose I think of postgresql as
> the final destination for my data, and I'd like to think that the
> language/interface that I choose doesn't really matter. Regardless of the
> interface, I'd like to "communicate" with postgres directly if possible!
I don't think you can really get any responses from postgres except in the
form of a NUL-terminated string. I suppose you could store binary data in a
bytea attribute, but that seems like a lot of work. You can't just bring a
byte from the internal data representation of the type into your application
directly.
> BTW/ I'm sort of packing two years of working with both databases and
> hitting various snafus all in one discussion. Ultimately, there are many
> reason's I'd like this feature, but it just boils down to the fact that php
> and postgres are great together, except for boolean! It seems you really do
> understand my frustration.
>
Yup, I have been frustrated by very similar problems. I'm a PHP programmer as
well :)
>
> I read Tom Lane's post and it seems that backward compatibility is the
> primary obstacle... This is something seemingly simple enough to solve, but
> I don't claim to understand even 5% of the core developer's job. I don't
> even full understand where the responsibility of the database ends that of
> the adapters begin. I'm happy to have just gotten the responses and some
> additional understanding. I hope that this conversation does continue on
> until the idea is
>
There's always a little overlap: should the application do the work or the
database? I hope whatever it is works out, and in a backwards-compatible way.
Oh, and you might take a look into using the system catalogs to determine
attribute types in your app. That would at least make it possible for you to
know how to convert.
Well, I'm certainly good at coming up with almost-solutions, aren't I? :)
Regards,
Jeff Davis
Garo Hussenjian <garo@xapnet.com> writes:
> Were there any backward compatibility issues for SET DATESTYLE? What were
> the repercussions for the adapters?
Can't say. It's been there for longer than I've been around this
project; and AFAIR no new values of DATESTYLE have been added in a long
time either. You might try asking Tom Lockhart.
> Regarding the existing adapters - I very much agree with what you're saying,
> but such a feature would not necessarily break any existing adapters. The
> default format would remain unchanged
... not if someone decides to set BOOLEANSTYLE at the postgresql.conf
level, or in the per-user or per-database options that are available in
7.3. Realistically, general-purpose client-side code is going to need
to work with any style that the backend might emit.
You might compare the discussions that have gone on in the past month or
so, since I (overhastily) added some backend support for an auto-commit
off mode. I knew very well that the client libraries were mostly not
prepared to work with that, but I figured we could update them over
time. Judging from the volume of complaints, that was a bad decision.
So I'm applying that experience to this issue with booleans ...
regards, tom lane
on 10/5/02 2:41 AM, Jeff Davis at list-pgsql-general@empires.org wrote: > > Ah, I think I finally get it. You go MySQL->PostgreSQL->MySQL and end up with > something a little different. Reminds me of some stories I've heard about > online text translators like Babelfish :) > > That's certainly a good point. I was thinking about babelfish when I wrote this! > >> >> As for the MySQL thing, it uses 1/0 and as a result remains consistent with >> the php convention for booleans. I guess that's just the trade-off when >> using a dynamically typed language... I suppose I think of postgresql as >> the final destination for my data, and I'd like to think that the >> language/interface that I choose doesn't really matter. Regardless of the >> interface, I'd like to "communicate" with postgres directly if possible! > > I don't think you can really get any responses from postgres except in the > form of a NUL-terminated string. I suppose you could store binary data in a > bytea attribute, but that seems like a lot of work. You can't just bring a > byte from the internal data representation of the type into your application > directly. :) I didn't mean THAT directly! There was a good deal of discussion about what adapters are supposed to do in order to bring data into the program environment. I just meant that I like having a transparent adapter. The downside is that if the database can't adjust the format, with an adapter like php's, we are left to pick up the pieces in our code. This has been an interesting side topic... Should databases accommodate multiple output formats? I think it'd be nice, but I'm now less inclined to say yes than I was at the beginning of this conversation. I am now leaning toward a middleware solution. I have a php app-server project that has done a good deal in terms of db abstraction and I think I just didn't consider it to be the "responsibility" of my code to do this. I'm now thinking that if php's native pgsql drivers don't do it, and postgres isn't going to do it, the we have to do it. In this case, The application server is the place to do it. Maybe when I implement this model I can post it for pg-php developers can have a slightly easier life! > > Oh, and you might take a look into using the system catalogs to determine > attribute types in your app. That would at least make it possible for you to > know how to convert. > > Well, I'm certainly good at coming up with almost-solutions, aren't I? :) You certainly have provided several thoughtful solutions! Thank you. I think this whole thing is just an argument for better php middleware. In the absence of this, I can see that it's just up to me to provide the abstraction - you know, this may even represent an opportunity in the php marketplace! My think this last solution actually works best - to use the db-native types, that is boolean in both cases, and deal with the system tables for to get the types. I will cache the schema and use it to pre-process my query results. So should I expand boolean t/f to 1/0 or TRUE/FALSE in the php environment? Decisions, decisions! :) Regards, Garo. > > Regards, > Jeff Davis > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
on 10/5/02 8:59 AM, Tom Lane at tgl@sss.pgh.pa.us wrote: > Garo Hussenjian <garo@xapnet.com> writes: >> Were there any backward compatibility issues for SET DATESTYLE? What were >> the repercussions for the adapters? > > Can't say. It's been there for longer than I've been around this > project; and AFAIR no new values of DATESTYLE have been added in a long > time either. You might try asking Tom Lockhart. > >> Regarding the existing adapters - I very much agree with what you're saying, >> but such a feature would not necessarily break any existing adapters. The >> default format would remain unchanged > > ... not if someone decides to set BOOLEANSTYLE at the postgresql.conf > level, or in the per-user or per-database options that are available in > 7.3. Realistically, general-purpose client-side code is going to need > to work with any style that the backend might emit. > Couldn't you make the same argument for the adapters? I understand. I'm a little concerned about the performance implications of processing all booleans individually versus setting the style globally in the backend. There is a great deal of replication and additional overhead that I don't think should have to be necessary. I guess it has to happen somewhere. I'm going to try switching to the native bool and manually update boolean f's with FALSE and see what happens! It will work, but it's ugly IMO and I'd still love to see this feature in postgresql. I'd really like to know if this option would in fact break any of the common libraries? I'd be willing to help in any way I can. > You might compare the discussions that have gone on in the past month or > so, since I (overhastily) added some backend support for an auto-commit > off mode. I knew very well that the client libraries were mostly not > prepared to work with that, but I figured we could update them over > time. Judging from the volume of complaints, that was a bad decision. > So I'm applying that experience to this issue with booleans ... Agreed, but perhaps that was a more drastic change? My vote would be to put it into a development tree and let it get tested until we at least know IF it will break any client libraries. Would you at least agree that the current situation is one of a subtle but "costly" incompatibility between postgresql and php? Of course there are workarounds, but they are not ideal. Also, is it really the case that the adapters (JDBC, DBI, others) actually process database output? I think maybe the languages are doing this. Maybe we should be looking at the languages rather than the adapters to predict if there are problems? Garo. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
Garo Hussenjian <garo@xapnet.com> writes:
> Also, is it really the case that the adapters (JDBC, DBI, others) actually
> process database output?
Well, maybe they don't. If they don't then the compatibility issue
largely goes away, I think.
This is the wrong list to be asking in, though. I'd suggest asking in
pgsql-interfaces, pgsql-odbc, and pgsql-jdbc. If there are no
objections there, then I'd be willing to support a BOOLEANSTYLE
parameter.
regards, tom lane
> I was thinking about babelfish when I wrote this!
Wow, that's funny :)
> > I don't think you can really get any responses from postgres except in
> > the form of a NUL-terminated string. I suppose you could store binary
> > data in a bytea attribute, but that seems like a lot of work. You can't
> > just bring a byte from the internal data representation of the type into
> > your application directly.
> >
> :) I didn't mean THAT directly! There was a good deal of discussion about
>
> what adapters are supposed to do in order to bring data into the program
> environment. I just meant that I like having a transparent adapter. The
> downside is that if the database can't adjust the format, with an adapter
> like php's, we are left to pick up the pieces in our code.
>
This brings up another topic. If you have a statically-typed language you need
all the data to come in as one type (intuitively a string). Otherwise you'd
need to know the schema before you wrote the program, and you'd have to
change your variable types when changing a query.
> This has been an interesting side topic... Should databases accommodate
> multiple output formats? I think it'd be nice, but I'm now less inclined to
> say yes than I was at the beginning of this conversation. I am now leaning
> toward a middleware solution. I have a php app-server project that has done
> a good deal in terms of db abstraction and I think I just didn't consider
> it to be the "responsibility" of my code to do this. I'm now thinking that
> if php's native pgsql drivers don't do it, and postgres isn't going to do
> it, the we have to do it. In this case, The application server is the place
> to do it. Maybe when I implement this model I can post it for pg-php
> developers can have a slightly easier life!
Best of luck to you. I hope the solution you come up with after all this is
nice and simple to work with.
> > Oh, and you might take a look into using the system catalogs to determine
> > attribute types in your app. That would at least make it possible for you
> > to know how to convert.
> >
> > Well, I'm certainly good at coming up with almost-solutions, aren't I? :)
>
> You certainly have provided several thoughtful solutions! Thank you.
I was being sarcastic, saying that I never have a complete solution, just
little suggestions. I hope it's been some help.
Regards,
Jeff Davis
On Sat, Oct 05, 2002 at 08:54:11PM -0400, Tom Lane wrote: > Garo Hussenjian <garo@xapnet.com> writes: > > Also, is it really the case that the adapters (JDBC, DBI, others) actually > > process database output? > > Well, maybe they don't. If they don't then the compatibility issue > largely goes away, I think. They do. DBI at least converts t/f into 1/0 which confused the hell out of me the first time I saw it. I was using eq "t" and eq "f" in my if statements, and the response was matching neither. It also returns undef for fields with NULL where Pg returned "". -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.