Thread: Porting Code to Postgresql

Porting Code to Postgresql

From

Errol Neal

Date:

15 October 2003, 14:17:18

Hi all,

Not sure if this is a question for a php list or this one, but I'll give it
a shot and
if I am wrong, please do not crucify me. :-)

There is a php based sourceforge project called mailwatch.
(http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
Mailscanner security product into a mysql database. Now, I am not a php
programmer,
and I am barely a Postgres DBA, but I would really like to port the code to
Postgresql.
I have my trust Postgresql Book which covers the API for Postgresql and the
PHP statements
used for Postgresql seem almost identical to those used for Mysql. I
understand that there are
some slight differences in the data types supported by Mysql and
Postgresql, however are the differences
between the two Databases and API's that great to make task impossible for
an unexperienced person
such as myself? We currently use Postgresql in conjunction with sendmail to
store our access, mailertable
and other db's, so it would be very convenient for us to achieve this.


Best Regards,

Errol U. Neal

Re: Porting Code to Postgresql

From

Richard Welty

Date:

15 October 2003, 14:57:11

On Wed, 15 Oct 2003 10:16:29 -0400 Errol Neal <sysadmins@enhtech.com> wrote:
> There is a php based sourceforge project called mailwatch.
> (http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
> Mailscanner security product into a mysql database. Now, I am not a php
> programmer,
> and I am barely a Postgres DBA, but I would really like to port the code to
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql
... and the
> PHP statements
> used for Postgresql seem almost identical to those used for Mysql. I
> understand that there are
> some slight differences in the data types supported by Mysql and
> Postgresql, however are the differences
> between the two Databases and API's that great to make task impossible for
> an unexperienced person
> such as myself?

it shouldn't be too awful as long as you're willing to learn.

watch for case folding issues. i ultimately ended up always making my table
names and column names all lower because of the way that php behaves with
case. if you have mixed case stuff, then ultimately you will end up
spending a lot of time chasing annoying, stupid bugs because php doesn't
require variables to be initialized, it just creates them, so you could end
up referencing $row->ColumnName and getting null because php put it in
$row->columnname.

when iterating over a result set, be aware that in at least some versions
of the php->postgresql interface,

while( $row = pg_fetch_object( $result, $row))
{
   ...
}

will error at the last row instead of returning a false value. you need to do:

$count = pg_numrows( $result);
for( $i = 0; $i < $count; $i++){
    $row = pg_fetch_object( $result, $row);
    ...
}

if you want it to work.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

Re: Porting Code to Postgresql

From

Ryan Mahoney

Date:

15 October 2003, 15:20:44

If the code is hard coded to use the built in mysql function calls, you
can replace them with postgresql calls or add a layer of database
abstraction.  Once you dump the schema into postgres and make whatever
datatype etc, changes that need to be made you should be able to test
the application by running it.  If it works you can go straight to
performance tuning 8) if not you will have to visit each statement and
resolve the issues one at a time.  I recomment turning on query logging
in your postgresql.conf file to make the debugging process easier.

Good Luck - although it may seem daunting the task is probably more
tedious than difficult.

-r

On Wed, 2003-10-15 at 10:16, Errol Neal wrote:
> Hi all,
>
> Not sure if this is a question for a php list or this one, but I'll give it
> a shot and
> if I am wrong, please do not crucify me. :-)
>
> There is a php based sourceforge project called mailwatch.
> (http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
> Mailscanner security product into a mysql database. Now, I am not a php
> programmer,
> and I am barely a Postgres DBA, but I would really like to port the code to
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql and the
> PHP statements
> used for Postgresql seem almost identical to those used for Mysql. I
> understand that there are
> some slight differences in the data types supported by Mysql and
> Postgresql, however are the differences
> between the two Databases and API's that great to make task impossible for
> an unexperienced person
> such as myself? We currently use Postgresql in conjunction with sendmail to
> store our access, mailertable
> and other db's, so it would be very convenient for us to achieve this.
>
>
> Best Regards,
>
> Errol U. Neal
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
--
Ryan Mahoney <ryan@paymentalliance.net>

Re: Porting Code to Postgresql

From

Dennis Gearon

Date:

15 October 2003, 15:43:35

>$count = pg_numrows( $result);
>for( $i = 0; $i < $count; $i++){
>    $row = pg_fetch_object( $result, $row);
>    ...
>}
>
>if you want it to work.
>
>
>
in case of no rows, maybe do:

$count = pg_numrows( $result);
while ( 0 < $result ){
   $result--;
   $row = pg_fetch_object( $result, $row);
   ...
}

or

$count = pg_numrows( $result);
$offset = $count;
while ( 0 < $offset ){
   $offset--;
   $row = pg_fetch_object( $result-$offset, $row);
   ...
}

Re: Porting Code to Postgresql

From

Richard Welty

Date:

15 October 2003, 17:36:33

On Wed, 15 Oct 2003 08:43:07 -0700 Dennis Gearon <gearond@fireserve.net> wrote:

> >$count = pg_numrows( $result);
> >for( $i = 0; $i < $count; $i++){
...
> in case of no rows, maybe do:
>
> $count = pg_numrows( $result);
> while ( 0 < $result ){
...

shouldn't make a difference; in php, the condition on a for() should be at
the top of the loop anyway.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

Re: Porting Code to Postgresql

From

"Rick Gigger"

Date:

15 October 2003, 17:57:18

I would guess most likely not.  There are a few mysql features that postgres
doesn't have (for example mysql_insert_id) but there are still ways to do
them in postgers.  I doubt it will be very hard.

----- Original Message -----
From: "Errol Neal" <sysadmins@enhtech.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, October 15, 2003 8:16 AM
Subject: [GENERAL] Porting Code to Postgresql


> Hi all,
>
> Not sure if this is a question for a php list or this one, but I'll give
it
> a shot and
> if I am wrong, please do not crucify me. :-)
>
> There is a php based sourceforge project called mailwatch.
> (http://www.sourceforge.net/projects/mailwatch) It logs data from the
excellent
> Mailscanner security product into a mysql database. Now, I am not a php
> programmer,
> and I am barely a Postgres DBA, but I would really like to port the code
to
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql and
the
> PHP statements
> used for Postgresql seem almost identical to those used for Mysql. I
> understand that there are
> some slight differences in the data types supported by Mysql and
> Postgresql, however are the differences
> between the two Databases and API's that great to make task impossible for
> an unexperienced person
> such as myself? We currently use Postgresql in conjunction with sendmail
to
> store our access, mailertable
> and other db's, so it would be very convenient for us to achieve this.
>
>
> Best Regards,
>
> Errol U. Neal
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: Porting Code to Postgresql

From

"scott.marlowe"

Date:

15 October 2003, 18:56:55

On Wed, 15 Oct 2003, Errol Neal wrote:

> Hi all,
>
> Not sure if this is a question for a php list or this one, but I'll give it
> a shot and
> if I am wrong, please do not crucify me. :-)
>
> There is a php based sourceforge project called mailwatch.
> (http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
> Mailscanner security product into a mysql database. Now, I am not a php
> programmer,
> and I am barely a Postgres DBA, but I would really like to port the code to
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql and the
> PHP statements
> used for Postgresql seem almost identical to those used for Mysql. I
> understand that there are
> some slight differences in the data types supported by Mysql and
> Postgresql, however are the differences
> between the two Databases and API's that great to make task impossible for
> an unexperienced person
> such as myself? We currently use Postgresql in conjunction with sendmail to
> store our access, mailertable
> and other db's, so it would be very convenient for us to achieve this.

The issues you're likely to hit are twofold:

The first is that MySQL silently accepts that which postgresql may reject.
Look for errors on insert when this happens.  for example, in postgresql,
you use the SQL keyword DEFAULT when inserting an autoincrement field, or
leave it out of your list of inserted fields.  In MySQL you insert,
counterintuitively, a NULL to do the same thing.

The second is the lack of a mysql_last_id type function.  While you can
get the last OID of an insert in postgresql, this is discouraged, as OIDs
may or may not exist for a given table depending on how it was declared.
Tis better to use the currval() / nextval() functions for such things.

All the rest if pretty straight forward hacking, having converted or
helped to convert a few other MySQL tools to Postgresql recently.

Re: Porting Code to Postgresql

From

Alvaro Herrera

Date:

15 October 2003, 19:05:03

On Wed, Oct 15, 2003 at 12:44:55PM -0600, scott.marlowe wrote:

> All the rest if pretty straight forward hacking, having converted or
> helped to convert a few other MySQL tools to Postgresql recently.

Of course, to get maximum performance you should drop the MySQL support
and instead of coding workarounds for missing functionality, use
whatever Postgres gives you (which is much more than what MySQL gives
you).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Si quieres ser creativo, aprende el arte de perder el tiempo"

Re: Porting Code to Postgresql

From

"Rick Gigger"

Date:

15 October 2003, 19:21:30

My guess is this will happen natually after using postgres for a short time.
(That's what happened to me.)

----- Original Message -----
From: "Alvaro Herrera" <alvherre@dcc.uchile.cl>
To: "scott.marlowe" <scott.marlowe@ihs.com>
Cc: "Errol Neal" <sysadmins@enhtech.com>; <pgsql-general@postgresql.org>
Sent: Wednesday, October 15, 2003 1:04 PM
Subject: Re: [GENERAL] Porting Code to Postgresql


> On Wed, Oct 15, 2003 at 12:44:55PM -0600, scott.marlowe wrote:
>
> > All the rest if pretty straight forward hacking, having converted or
> > helped to convert a few other MySQL tools to Postgresql recently.
>
> Of course, to get maximum performance you should drop the MySQL support
> and instead of coding workarounds for missing functionality, use
> whatever Postgres gives you (which is much more than what MySQL gives
> you).
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "Si quieres ser creativo, aprende el arte de perder el tiempo"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Re: Porting Code to Postgresql

From

"scott.marlowe"

Date:

15 October 2003, 22:20:57

On Wed, 15 Oct 2003, Dennis Gearon wrote:

>
> >$count = pg_numrows( $result);
> >for( $i = 0; $i < $count; $i++){
> >    $row = pg_fetch_object( $result, $row);
> >    ...
> >}
> >
> >if you want it to work.
> >
> >
> >
> in case of no rows, maybe do:
>
> $count = pg_numrows( $result);
> while ( 0 < $result ){
>    $result--;
>    $row = pg_fetch_object( $result, $row);
>    ...
> }

But you don't want to decrement $result, it's a result handle, you want to
decrement count:

$count = pg_numrows( $result);
 while ( 0 < $count ){
    $count--;
    $row = pg_fetch_object( $result, $row);
    ...
}

Re: Porting Code to Postgresql

From

Dennis Gearon

Date:

16 October 2003, 02:52:26

Richard Welty wrote:

>On Wed, 15 Oct 2003 08:43:07 -0700 Dennis Gearon <gearond@fireserve.net> wrote:
>
>
>
>>>$count = pg_numrows( $result);
>>>for( $i = 0; $i < $count; $i++){
>>>
>>>
>...
>
>
>>in case of no rows, maybe do:
>>
>>$count = pg_numrows( $result);
>>while ( 0 < $result ){
>>
>>
>...
>
>shouldn't make a difference; in php, the condition on a for() should be at
>the top of the loop anyway.
>
>richard
>
>
In a for loop, you *always* execute the body of the loop one time, as
far as I know. So if you have no rows, you would then have a problem.
That is why I put the WHILE at the top.

--
"You are behaving like a man",
is an insult from some women,
a compliment from an good woman.

Re: Porting Code to Postgresql

From

Dennis Gearon

Date:

16 October 2003, 03:17:50

Richard Welty wrote:

>[taking this offlist]
>
>On Wed, 15 Oct 2003 19:51:00 -0700 Dennis Gearon <gearond@fireserve.net> wrote:
>
>
>>In a for loop, you *always* execute the body of the loop one time, as
>>far as I know. So if you have no rows, you would then have a problem.
>>That is why I put the WHILE at the top.
>>
>>
>
>in some languages, for loops have this problem. this is why i reviewed two
>php references to double check my facts before i posted my followup, and
>both claimed that in php, the test is at the top of the loop.
>
>i can code something up and test it you like.
>
>richard
>
>
You're right! I looked up
    http://www.php.net/for
and found it.
Apparently C also does the test at the top of the loop
    http://www.acm.uiuc.edu/webmonkeys/book/c_guide/1.6.html

Well, that will simplify a lot of my future code :-)

--
"You are behaving like a man",
is an insult from some women,
a compliment from an good woman.