Article
Migrate your site from MySQL to PostgreSQL -- Part 2
PHP Surgery: Hacking The Code
First, create a central configuration file if you don't already have one. This makes it easy to change Postgres server settings within your code and have the changes instantly affect your entire site. Make sure that the configuration file is not in a Web-accessible spot on your Web server, which is a security risk. The default include directory for PHP is /usr/local/lib/php/, so you may want to have your Web host create a directory there for any configuration files you don't want accessible from the Web. Also make sure it's not readable by other people using your Web host's services. If your site, like mine, includes a standard header file, you can include the Postgres settings file from there. It goes something like this:
<?php
// /usr/local/lib/php/mysite/configfile.php
$hostname = "localhost";
$username = "username";
$database = "mydb";
$password = "mypasswd";
?>
<?php
//Standard HTML Header
include("mysite/configfile.php");
?>
<html>
<head>
<title>Bill's Kazoos</title>
<head>
<body>
header continues...
A central repository for information like the previous example makes switching database systems a lot easier. Speaking of switching, it's time to start editing files.
Connections and Queries
PHP's MySQL functions are similar enough to the Postgres ones to make converting code easy but different enough to make it just a little quirky. In fact, you may want to write some special functions to make converting easier. But before you do that, here's an overview of the connection and query differences:
Connecting to a MySQL database requires two commands:
$connection_id=mysql_connect($hostname, $username, $password);
mysql_select_db($database, $connection_id);
Or if you use persistent connections:
$connection_id=mysql_pconnect($hostname, $username, $password);
mysql_select_db($database, $connection_id);
However, PHP's Postgres connect function takes a single string argument, unlike the MySQL function, which takes multiple string arguments. The Postgres functions also let you specify which database to use when you initially connect. Here's an example:
$connection_id=pg_connect("host=$hostname dbname=$database user=$username
password=$password");
The persistent connection function works exactly the same, except it requires a call to pg_pconnect().
PHP's MySQL and Postgres query functions are a bit different as well. The MySQL query function is $result_data = mysql_query("query goes here", $connection_id);, but Postgres's is reversed from that: $result_data = pg_exec($connection_id, "query goes here").
As you can see, the differences between PHP's MySQL and Postgres support in connecting and querying aren't great, but the differences in function arguments make the editing process slower. To speed things up, you can write some wrapper functions that take the same arguments as the MySQL functions but connect to Postgres instead. If you have a central library of utility functions, you can place them there. You could also put them in the configuration file explained previously, since it is automatically included into every page.
//connect to database
function postg_connect($hostname, $username, $password, $database)
{
return pg_connect("host=$hostname dbname=$database
user=$username password=$password");
}
//alternately, if you use only one database
//you can make best use of the variables in
//your configuration file by doing the following,
//which eliminates the need for any variable
//passing at all.
function postg_autoconnect()
{
global $hostname, $username $password $database;
return pg_connect("host=$hostname dbname=$database
user=$username password=$password");
}
//query-making function
function postg_query($query, $connection_id)
{
return pg_exec($connection_id, $query);
}
Whether or not you use wrapper functions, converting the connection code and query calls is pretty simple. Postgres is mostly able to support the old SQL queries used under MySQL, but you might have to tweak your queries a bit. Since data models and code vary from site to site, I won't explain things here. However, converting the SQL isn't that hard. Convert the code first, then see what queries fail in Postgres. Next compare the MySQL Language Reference side by side with the PostgreSQL Users Guide. You probably won't have to do more than just finding the Postgres equivalent of MySQL features, as Postgres supports all of the common MySQL features.
Now that you have made the connection and query code Postgres-friendly, it's time to get your hands dirty. The differences between PHP's MySQL and Postgres support place several hurdles to working with result sets that will require some more code tweaking.