Porting Code to Postgresql

[Errol Neal]

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

[Richard Welty]

On Wed, 15 Oct 2003 10:16:29 -0400 Errol Neal <sy*******@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 rw****@averillpark.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?

http://archives.postgresql.org

[Ryan Mahoney]

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 <ry**@paymentalliance.net>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Dennis Gearon]

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

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

[Richard Welty]

On Wed, 15 Oct 2003 08:43:07 -0700 Dennis Gearon <ge*****@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 rw****@averillpark.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?

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

[Rick Gigger]

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" <sy*******@enhtech.com>
To: <pg***********@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

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

http://archives.postgresql.org

[scott.marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera]

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

[Rick Gigger]

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" <al******@dcc.uchile.cl>
To: "scott.marlowe" <sc***********@ihs.com>
Cc: "Errol Neal" <sy*******@enhtech.com>; <pg***********@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

---------------------------(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]

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

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

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

[Dennis Gearon]

Richard Welty wrote:

On Wed, 15 Oct 2003 08:43:07 -0700 Dennis Gearon <ge*****@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.

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

[Dennis Gearon]

Richard Welty wrote:

[taking this offlist]

On Wed, 15 Oct 2003 19:51:00 -0700 Dennis Gearon <ge*****@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/b...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 ma*******@postgresql.org