Porting Code to Postgresql

**Richard Welty** · Nov 12 '05, 12:16 AM

Re: Porting Code to Postgresql

On Wed, 15 Oct 2003 10:16:29 -0400 Errol Neal <sysadmins@enht ech.com> wrote:[color=blue]
> 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[/color]
.... and the[color=blue]
> 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?[/color]

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@averillp ark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Ryan Mahoney** · Nov 12 '05, 12:16 AM

Re: Porting Code to Postgresql

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:[color=blue]
> 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[/color]
--
Ryan Mahoney <ryan@paymental liance.net>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Dennis Gearon** · Nov 12 '05, 12:16 AM

Re: Porting Code to Postgresql

[color=blue]
>$count = pg_numrows( $result);
>for( $i = 0; $i < $count; $i++){
> $row = pg_fetch_object ( $result, $row);
> ...
>}
>
>if you want it to work.
>
>
>[/color]
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);
...
}

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Richard Welty** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

On Wed, 15 Oct 2003 08:43:07 -0700 Dennis Gearon <gearond@firese rve.net> wrote:
[color=blue][color=green]
> >$count = pg_numrows( $result);
> >for( $i = 0; $i < $count; $i++){[/color][/color]
....[color=blue]
> in case of no rows, maybe do:
>
> $count = pg_numrows( $result);
> while ( 0 < $result ){[/color]
....

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@averillp ark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Rick Gigger** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

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@enht ech.com>
To: <pgsql-general@postgre sql.org>
Sent: Wednesday, October 15, 2003 8:16 AM
Subject: [GENERAL] Porting Code to Postgresql

[color=blue]
> Hi all,
>
> Not sure if this is a question for a php list or this one, but I'll give[/color]
it[color=blue]
> 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[/color]
excellent[color=blue]
> 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[/color]
to[color=blue]
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql and[/color]
the[color=blue]
> 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[/color]
to[color=blue]
> 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
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**scott.marlowe** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

On Wed, 15 Oct 2003, Errol Neal wrote:
[color=blue]
> 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.[/color]

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,
counterintuitiv ely, 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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Alvaro Herrera** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

On Wed, Oct 15, 2003 at 12:44:55PM -0600, scott.marlowe wrote:
[color=blue]
> All the rest if pretty straight forward hacking, having converted or
> helped to convert a few other MySQL tools to Postgresql recently.[/color]

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

**Rick Gigger** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

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.u chile.cl>
To: "scott.marl owe" <scott.marlowe@ ihs.com>
Cc: "Errol Neal" <sysadmins@enht ech.com>; <pgsql-general@postgre sql.org>
Sent: Wednesday, October 15, 2003 1:04 PM
Subject: Re: [GENERAL] Porting Code to Postgresql

[color=blue]
> On Wed, Oct 15, 2003 at 12:44:55PM -0600, scott.marlowe wrote:
>[color=green]
> > All the rest if pretty straight forward hacking, having converted or
> > helped to convert a few other MySQL tools to Postgresql recently.[/color]
>
> 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
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**scott.marlowe** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

On Wed, 15 Oct 2003, Dennis Gearon wrote:
[color=blue]
>[color=green]
> >$count = pg_numrows( $result);
> >for( $i = 0; $i < $count; $i++){
> > $row = pg_fetch_object ( $result, $row);
> > ...
> >}
> >
> >if you want it to work.
> >
> >
> >[/color]
> in case of no rows, maybe do:
>
> $count = pg_numrows( $result);
> while ( 0 < $result ){
> $result--;
> $row = pg_fetch_object ( $result, $row);
> ...
> }[/color]

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);
...
}

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Dennis Gearon** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

Richard Welty wrote:
[color=blue]
>On Wed, 15 Oct 2003 08:43:07 -0700 Dennis Gearon <gearond@firese rve.net> wrote:
>
>
>[color=green][color=darkred]
>>>$count = pg_numrows( $result);
>>>for( $i = 0; $i < $count; $i++){
>>>
>>>[/color][/color]
>...
>
>[color=green]
>>in case of no rows, maybe do:
>>
>>$count = pg_numrows( $result);
>>while ( 0 < $result ){
>>
>>[/color]
>...
>
>shouldn't make a difference; in php, the condition on a for() should be at
>the top of the loop anyway.
>
>richard
>
>[/color]
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Dennis Gearon** · Nov 12 '05, 12:17 AM

Re: Porting Code to Postgresql

Richard Welty wrote:
[color=blue]
>[taking this offlist]
>
>On Wed, 15 Oct 2003 19:51:00 -0700 Dennis Gearon <gearond@firese rve.net> wrote:
>
>[color=green]
>>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.
>>
>>[/color]
>
>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
>
>[/color]
You're right! I looked up

PHP: for - Manual

http://www.php.net/for

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

Porting Code to Postgresql

Porting Code to Postgresql

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment