postfixadmin-devel

[Postfixadmin-devel] Boolean values

[Geoff S. <ge...@Qu...>]

Hello,

I apologise for joining the list and posting immediately on a subject that 
may well have had some considerable discussion already.

We are deploying PostfixAdmin (SVN as of about 17:00 UTC yesterday) on a 
new system using PostgreSQL 8.4 and found the following bug.

If a regular admin (not super) logs in and selects Domain List, the 
following error is generated:

    Invalid query: ERROR: column "t" does not exist LINE 4: AND
    domain.active=t ^

I tracked this down to functions.inc.php line 783.  Obviously this needs 
to be quoted, as does the next line.

I see that there's been some recent commits around boolean handling in 
PGSQL, so some kind of policy on this might have already been decided. 
However, looking at the MySQL and PGSQL documentation suggests that it 
would be much easier to use TRUE and FALSE (unquoted) in queries than to 
do what is currently done.  Current PGSQL (8.4) supports this[1] and has 
done since at least version 6.3[2].  MySQL also suppports this[3][4] and 
has done since probably version 4.0[5] (MySQL 3.23.49 does *not* support 
this, despite the manual[5] suggesting otherwise).

It just strikes me that you could eliminate quoting issues and eliminate 
different boolean handling for each db type at the same time.

I've not looked at much of the code but would be willing to help with 
this.

Cheers,
Geoff.


References:

1. http://www.postgresql.org/docs/8.4/static/datatype-boolean.html

2. http://www.postgresql.org/docs/6.3/static/c0805.htm

3. http://dev.mysql.com/doc/refman/6.0/en/numeric-type-overview.html

4. http://dev.mysql.com/doc/refman/5.4/en/numeric-type-overview.html

5. http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html

Re: [Postfixadmin-devel] Boolean values

[David G. <da...@co...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Geoff Shang wrote:
> Hello,
> 
> I apologise for joining the list and posting immediately on a subject that 
> may well have had some considerable discussion already.
> 
> We are deploying PostfixAdmin (SVN as of about 17:00 UTC yesterday) on a 
> new system using PostgreSQL 8.4 and found the following bug.
> 
> If a regular admin (not super) logs in and selects Domain List, the 
> following error is generated:
> 
>     Invalid query: ERROR: column "t" does not exist LINE 4: AND
>     domain.active=t ^
> 
> I tracked this down to functions.inc.php line 783.  Obviously this needs 
> to be quoted, as does the next line.
> 
> I see that there's been some recent commits around boolean handling in 
> PGSQL, so some kind of policy on this might have already been decided. 
> However, looking at the MySQL and PGSQL documentation suggests that it 
> would be much easier to use TRUE and FALSE (unquoted) in queries than to 
> do what is currently done.  Current PGSQL (8.4) supports this[1] and has 
> done since at least version 6.3[2].  MySQL also suppports this[3][4] and 
> has done since probably version 4.0[5] (MySQL 3.23.49 does *not* support 
> this, despite the manual[5] suggesting otherwise).
> 
> It just strikes me that you could eliminate quoting issues and eliminate 
> different boolean handling for each db type at the same time.
> 
> I've not looked at much of the code but would be willing to help with 
> this.


Hi Geoff,

The main problem is that MySQL has 0 or 1 for boolean values (i.e. I
think the schema uses a tinyint field, rather than boolean); whereas
PostgreSQL uses boolean.

Christian did suggest that perhaps we move PostgreSQL to use 0/1, but I
didn't think this was a good idea.

I /think/ I've fixed functions.inc.php for you - but I suspect it
[Postfixadmin] will be broken elsewhere.

(*Wonders why this has suddenly become an issue, and what changed to
screw the boolean values up*)

There is a problem with PHP/PostgreSQL date handling at the moment too -
I've not sat down to fix this yet, but you will find problems with the
last modified columns etc.


thanks
David.
- --
David Goodwin

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqOfDMACgkQ/ISo3RF5V6Y3iQCghqJCkkMadfGHG5V7HrG191Hh
vZwAoIk2gh1KfmLpkFVjDnBNhQU9GDBU
=ANHo
-----END PGP SIGNATURE-----

Re: [Postfixadmin-devel] Boolean values

[Geoff S. <ge...@Qu...>]

On Fri, 21 Aug 2009, David Goodwin wrote:

> The main problem is that MySQL has 0 or 1 for boolean values (i.e. I
> think the schema uses a tinyint field, rather than boolean); whereas
> PostgreSQL uses boolean.

This is true and affects how one would retrieve stored data.  However my 
suggestion related to building queries, and both mysql and postgresql 
support TRUE and FALSE as constants.  It appears that these are the only 
boolean representations in queries that they have in common, all of PG's 
other alternatives are strings ('t', 'true', 'y', 'yes', 'on', '1'). 
Mysql obviously expects an integer, and TRUE and FALSe are simply 
constants that equal 1 and 0 respectively.  This is all spelled out in the 
documentation for both databases which I provided links for in my previous 
message.

> Christian did suggest that perhaps we move PostgreSQL to use 0/1, but I
> didn't think this was a good idea.

Given that the mysql documentation says that they'll eventually implement 
a proper boolean type, you might want to stick with it the way it is for 
now.

> I /think/ I've fixed functions.inc.php for you - but I suspect it
> [Postfixadmin] will be broken elsewhere.

Am happy to report anything else we find.

> There is a problem with PHP/PostgreSQL date handling at the moment too -
> I've not sat down to fix this yet, but you will find problems with the
> last modified columns etc.

I'm new to pg, having previously used mysql only, and am also new to 
postfixadmin.  But I'm happy to help out and will contribute wherever 
possible.  Still, at least odd last-modified dates shouldn't stop PA form 
working.

Thanks for the quick fix,

Geoff.

Re: [Postfixadmin-devel] Boolean values

[Christian B. <pos...@cb...>]

Hello,

Am Freitag, 21. August 2009 schrieb David Goodwin:
> Geoff Shang wrote:
> > I apologise for joining the list and posting immediately on a
> > subject that may well have had some considerable discussion
> > already.

We are always happy about users asking questiosns or reporting bugs - no 
need to apologise ;-)

> >     Invalid query: ERROR: column "t" does not exist LINE 4: AND
> >     domain.active=t ^
[...]
> The main problem is that MySQL has 0 or 1 for boolean values (i.e. I
> think the schema uses a tinyint field, rather than boolean); whereas
> PostgreSQL uses boolean.
>
> Christian did suggest that perhaps we move PostgreSQL to use 0/1, but
> I didn't think this was a good idea.

Well, it would have avoided the quoting issue ;-)
(but it would probably have caused other issues, like a need to change 
the postfix config)

> I /think/ I've fixed functions.inc.php for you - but I suspect it
> [Postfixadmin] will be broken elsewhere.
>
> (*Wonders why this has suddenly become an issue, and what changed to
> screw the boolean values up*)

Probably your change in db_get_boolean to return t/f instead of 
true/false for postgresql (don't say I didn't warn you ;-)

I fixed some more queries (after grepping trough the code) - please test 
if the files I modified are working with postgresql. (I also still have 
to test them with MySQL ;-)

> There is a problem with PHP/PostgreSQL date handling at the moment
> too - I've not sat down to fix this yet, but you will find problems
> with the last modified columns etc.

Do you mean the string formatting problem you mentioned in IRC? Or 
something else?


Regards,

Christian Boltz
-- 
Hmm ... Du meinst das RamA ... Das macht das Frühstück gut ... denn 
wenn Du RamA verwendest, dann meckert die Frau nicht, wenn Du schon
zum Frühstück vor dem Rechner sitzt. [Konrad Neitzel in suse-linux]

Re: [Postfixadmin-devel] Boolean values

[David G. <da...@co...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> 
> Probably your change in db_get_boolean to return t/f instead of 
> true/false for postgresql (don't say I didn't warn you ;-)

*wonders why he did this*

> 
> I fixed some more queries (after grepping trough the code) - please test 
> if the files I modified are working with postgresql. (I also still have 
> to test them with MySQL ;-)
> 
>> There is a problem with PHP/PostgreSQL date handling at the moment
>> too - I've not sat down to fix this yet, but you will find problems
>> with the last modified columns etc.
> 
> Do you mean the string formatting problem you mentioned in IRC? Or 
> something else?
> 

Yes

David.
- --
David Goodwin

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqOoHYACgkQ/ISo3RF5V6YdvwCfZct4aepJVPe1UORGCGvxEu9u
RjIAoLOr5p2xsiBofi0fzATq1XVYNS2o
=xL2y
-----END PGP SIGNATURE-----

Re: [Postfixadmin-devel] Boolean values

[Christian B. <pos...@cb...>]

Hallo Leute,

Am Freitag, 21. August 2009 schrieb David Goodwin:
> > Probably your change in db_get_boolean to return t/f instead of
> > true/false for postgresql (don't say I didn't warn you ;-)
>
> *wonders why he did this*

OK, you wanted it so ;-)

2009-08-04 log from #postfixadmin:

[22:16] <cboltz> GingerDog, I think you are the perfect tester for the bug that was reported about list-domain always showing "not active" ;-)
[22:16] <cboltz> I think this might be a PgSQL issue...
[22:19] <GingerDog> steps to recreate are...
[22:19] <GingerDog> oh, yes...
[22:19] <GingerDog> reproducable
[22:19] <GingerDog> +e
[22:19] <GingerDog> i never use list-domain
[22:19] <cboltz> try the changes I proposed in the bugreport...
[22:20] * GingerDog goes to rummage
[22:23] <cboltz> https://sourceforge.net/tracker/?func=detail&aid=2830001&group_id=191583&atid=937964
[22:23] <cboltz> to save you some searching ;-)
[22:36] <GingerDog> oddly my postgres returns 't' or 'f' for db_get_boolean(true/false)
[22:36] <GingerDog> sorry that's all wrong
[22:36] <GingerDog> the resultset contains 't' or 'f'
[22:37] <GingerDog> db_get_boolean() returns 'true' or 'false'
[22:37] <GingerDog> so even if they should match, they won't as 't' != 'true'
[22:37] * GingerDog wonders if this is a new postgres feature or something else
[22:37] <cboltz> fix db_get_boolean ;-)
[22:38] <cboltz> AFAIK we mostly used db_get_boolean in INSERT and UPDATE statements, so true/false probably "accidently" work
[22:39] <cboltz> however, you might want to just check for "t" / "f" in the list-domain template to avoid breaking something in the 2.3 release ;-)
[22:40] <GingerDog> hah
[22:40] <GingerDog> edit-domain is quite happy with the new style boolean(s) for postgres
[22:41] <GingerDog> date stamps are fubared though
[22:41] * cboltz thinks about switching to something like tinyint so that PgSQL just returns 0 or 1
[22:42] <GingerDog> are your dates in e.g. alias stored like : 2009-08-04 21:45:45.799683+01 ?
[22:43] <GingerDog> too late now ;0)
[22:44] <cboltz> you mean the "last changed" column?
[22:44] <cboltz> I see something like "2007-05-28 21:43:20"
[22:44] <GingerDog> yes, for example
[22:44] <GingerDog> ah
[22:44] <GingerDog> php is getting confused when redisplaying the dates given to it by postgres
[22:46] <GingerDog> again, sometimes you wish date/time was just stored as a value in seconds ;)
[22:46] <cboltz> or pgsql is just tooo exact in the timestamp it returns
[22:46] <cboltz> if you cut off everything after the seconds, you have the format I see with mysql
[22:48] <GingerDog> but putting a lot of strpos() in where dates are shown would be a bit ugly.
[22:48] <cboltz> I'd use strtotime() and then format it again...
[22:55] <GingerDog> hmm
[22:56] <GingerDog> so you don't see anything under mysql to indicate the timezone for a date?
[22:57] <cboltz> at least not in postfixadmin
[22:57] <cboltz> (no idea how it is stored in the database)
[22:59] <GingerDog> that's where the problem is for me atm
[23:00] <GingerDog> yes, it looks like it's the same in the DB


Regards,

Christian Boltz
-- 
> Adding a self-removing SuSEconfig script calling rpm -e --noscripts
> would be an ugly but working work-around. [...]
Wow. Stanislav, after all these years you still manage to scare me :)
[> Stanislav Brabec and Stephan Kulow in
 https://bugzilla.novell.com/show_bug.cgi?id=310640]