By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

Porting Code to Postgresql

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
$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

Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

P: n/a
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

Nov 12 '05 #7

P: n/a
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

Nov 12 '05 #8

P: n/a
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

Nov 12 '05 #9

P: n/a
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

Nov 12 '05 #10

P: n/a
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

Nov 12 '05 #11

P: n/a
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

Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.