Converting php code from MySQL to postgreSQL

I’ve been using mysql since 2005, when my daughter pressured me into creating a simple web site that she and her friends could log onto. My first iteration of that web site was simply using php to store userid and password information in a text file, but I soon found how easy mysql was to use so I quickly headed that direction.

Facilities I Use

I actually use very few factilities of mysql – so let’s see if we can make a quick list and focus on them:

  • mssql task is up and running (of course)
  • php-mysql interface (of course)
  • phpMyAdmin web administration – Note: I very rarely use mssql line-mode
    • logon and access to web interface
    • database creation, deletion, import, export
    • table creation, deletion, alteration
    • field creation, deletion, alteration
    • userid, password, and permissions
  • php functions
    • mysql_connect
    • mysql_select_db
    • mysql_query
    • mysql_fetch_array
    • mysql_num_rows
    • mysql_error
    • mysql_result
    • mysql_insert_id
    • mysql_real_escape_string
  • query functions
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
  • handling unusual characters (addslashes)

So I think we can experiment with just that small subset of items and see if we can convert each one from mysql to pgsql. By the way, what is pgsql really called? I’ve seen it called PostgreSQL, Postgre, and PostgreS among others. Let’s just call it pgsql for now, since that rhymes with mysql.

Product Implementation

I’m already running mysql on my test Linux server (CentOS 6.5) which listens on port 3306. Can we run pgsql on the same server? Yes we can – pgsql listens on port 5432 and has no relationship to mysql. Here’s what I did to implement (I think – there were some steps forward and then backward):

yum install postgresql-server.x86_64
yum install php-pgsql

That resulted in /var/lib/pgsql being created and loaded with various files. Userid postgres was created as the owner of these files, and in fact, userid postgres is setup by default as a pgsql Superuser, which I assume has full access to everything. At that point I used the passwd command to set a known password for postgres, since we’ll be needing it later.

service postgresql initdb 
service postgresql start
chkconfig postgresql on

The commands above setup postgresql to init and start at reboot. I’m not sure what the initdb actually does, or if it will ever be needed again (probably not). After the task is running, we can issue the psql command to get into the line-mode facility. But this looks pretty restricted – apparently only userid postgres has access.

Later I had problems logging on to phpPgAdmin, which seemed to be caused by the “ident” method of validating the userid and password. I don’t know what “ident” means, but if we change the method to “password” I can logon ok. So let’s do that, at least for now:

vi /var/lib/pgsql/data/pg_hba.conf
Change ident to password in 3 places
pg_ctl restart

That allows phpPgAdmin to work, but causes a password to be required when issuing the psql line-mode command. When using psql, some commands have backslashes, and others are standard SQL. See the web for more doc on this, and hopefully with phpPgAdmin we can avoid this as much as possible.

By the way, there are dozens of phpMyAdmin equivalents for pgsql. I chose phpPgAdmin because the name was similar to phpMyAdmin. To install, I did this (I think):

wget http://yum.postgresql.org/9.1/redhat/rhel-5.0-x86_64//pgdg-centos91-9.1-4.noarch.rpm
yum install pgdg-centos91-9.1-4.noarch.rpm

This resulted in /var/lib/pgsql/phpPgAdmin-5.1 being created (I think). The doc says to point apache to that directory, but instead I copied it to /var/www/html/phppgadmin on my server, which is accessible locally only. That way I don’t have to update httpd config parameters.

vi /var/www/html/phppgadmin/conf/config.inc.php
$conf['servers'][0]['desc'] = 'localhost';
$conf['extra_login_security'] = false;

I’m not sure if the first is really required, but the extra_login_security seems to be needed if we want to logon to phpPgAdmin as userid postgres, which we will need to do from time to time.

If you change anything in the postgres or phpPgAdmin config files, it is probably necessary to restart either or both tasks:

pg_ctl restart
service httpd restart

And I would recommend a reboot too, just to make sure the chkconfig item is in place.

phpPgAdmin Usage and Authority

Probably the first thing to do is to create a test database, table, and add some fields. While phpPgAdmin looks quite different from phpMyAdmin, they are enough alike that these actions should be no problem. One major difference seems to be the user ownership and permissions. In mysql there are tables for userids that include the authorization to various tables. In pgsql it appears (at least at first glance) that while we have a userid table, the actual authorization is done by testing the owner field in each database target table, not in the userid table. So be aware of that and use the Alter buttons as needed to adjust database/table ownership as needed.

Once the new test table was created and ownership assigned (while logged on as superuser postgres), I could re-logon using my own id which I set as owner of the new table. That limits my access to only the new table, which is just what I want.

Now the userid and password (from /etc/passwd) can be used via php to access the table. This is different from mysql where the password is stored in the mysql user table and not in /etc/passwd. So be aware of that and you should probably create specific /etc/passwd userids for each table, perhaps with no Shell capability.

There are probably many more details for table access and authority built into the products, but I probably only need ownership, which I assume gives the superuser equivalent, but just for a particular database.

So let’s try some php processing

Let’s try this:

$dbconn = pg_connect("host=localhost dbname=test user=myserid password=mypass") or die('Error 1000: ' . pg_last_error());

That looks a whole lot like mysql_connect, except there is only one parameter string containing all fields, and includes the database name (which in mysql is given by mysql_select_db). I don’t see anything like mysql_select_db in pg, so to switch databases we probably need another connection, which is actually a good way to do things anyway.

Oddly, pg_last_error() seems to be blank if there is a problem. I don’t know why yet.
Anyway, once connected we can try a query such as:

$result = pg_query($dbconn,"select * from table1") or die('Error 1001: ' . pg_last_error($dbconn));

Looks very much like mysql other than the $dbconn is up front instead of at the back. And once we have the result handle, we can use it to fetch arrays, just like we do a lot with mysql:

while($myrow = pg_fetch_array($result))
 {
 }

Arrays are returned in the same format as mysql, with doubled-up data so that either numeric or named keys can be used. Everything looks very familiar so far. These folks apparently were influenced by the same source, or perhaps each other.

$rc = pg_query($dbconn,"insert into table1(userid,password) values('billy','password3')") or die('Error 1002: ' . pg_last_error($dbconn));

Oddly, $rc is a handle, and not a return code. So be aware of that. But the insert works ok.

This is a problem though. The following works in mysql but does not work in pgsql. I have a habit of coding my inserts using this update format, and apparently I will have to convert to standard insert format. Ouch.

$rc = pg_query($dbconn,"insert into table1 set userid='elmo',password='password4'")

This seems to work as expected:

$num = pg_num_rows($result);

For mysql_result() I cannot find an equivalent. So let’s just convert any of those to mysql_fetch_array()

mysql has a function “autoincrement” which will increment a unique integer for each inserted row. We’ll need the same in pgsql, but setup looks different. This should work for tables with an automatically set id number:

  1. Define auto-increment variable ‘recnum’ as SERIAL using phpPgAdmin, probably as database key too.
  2. $result = pg_query($dbconn,”select currval(pg_get_serial_sequence(‘table1′,’recnum’))”)
  3. list($recnum) = pg_fetch_array($result);

This is a problem however, because the mysql function uses the $result of the last query as input, and the pgsql version needs the table and field names as input.  That means recoding mysql instances to support a new method, I think.

Slashes

pg_escape_string should replace mysql_real_escape_string, but there are differences. With mysql, an apostrophe is escaped by a slash. In pgsql apostrophe’s are doubled. I would imagine the same is done in pgsql for any other characters that it would have trouble with. I read somewhere – do not use addslashes().

INSERT Limitation in pgsql

In both mysql and pgsql the UPDATE statement uses the SET field1=’value1′,field2=’value2′ format.  Because that SET format is so easy to code, I also use it on INSERT in mysql.  But… that fails in pgsql so I must convert any of those instances to the more normal INSERT INTO table(field1,field1) values(‘value1′,’value2′) format.  This might take some work or some additional function calls.

Using phpPgAdmin

phpPgAdmin has a lot of things that work just like phpMyAdmin, but there are enough differences (or possibly limitations) that I get confused at some times.

  • Adding a Primary Key – I don’t see how to do that other than with SQL such as ALTER TABLE table1 ADD PRIMARY KEY (userid);
  • Deleting, editing, altering a number of columns or records. I don’t see any way to select a group of records. Most likely I just have not been looking in the right place. Until then, SQL will have to do.
  • pgsql itself differs from mysql, which can cause some of these differences in the web interface.

Making Code Changes

Ok – I think I tested everything I need to test.  The next step is to take my existing php code and convert each mysql call to an internal call, something like mysql_query() changing to db_query().  Then then new db* routines can be included from either mysql.php or pgsql.php (or perhaps others) so that only the required functions are used.  Then switching is as easy as changing one config include line.

Testing

Once all the code changes have been completed and the web page processing appears to work, the final test is to stop mysqld on the server and see if things still work with pgsql.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>