Creating a PostgreSQL and MySQL driver

By Tony Marston
2005-09-20


Connecting to a Database

MySQL has tables within databases while PostgreSQL has tables within schemas within databases. Every database has a default "public" schema, while other schemas can be created on demand. It is possible for the same table name to exist within different schemas within the same database.

With MySQL once you have established a connection you may access any table within any database. One database is nominated as the "default" or "current" database by using the mysqli_select_db function, in which case any table names within that database do not have to be qualified. Tables in other databases can only be accessed by qualifying them with the database name, as in dbname.tablename.

With PostgreSQL a connection is limited to a single database, and you may only access tables that belong to schemas within that particular database. You can make a particular schema "default" or "current" by issuing an SQL query to set the schema search_path. Tables in other schemas can only be accessed by qualifying them with the schema name, as in schema.tablename.

I personally like to create a separate database for each of my software subsystems as it makes backups and restores that much easier, so the ability to swap a MySQL "database" for a PostgreSQL "schema" is very convenient.

In order to make my code access database tables through PostgreSQL I made the following changes to my code:

1. DB.INC

This is a small include file which exists outside the web root, and which contains the details necessary to connect to the database. It has been changed from this:

'mysql';

$dbhost     = 'localhost';

$dbusername = '****';

$dbuserpass = '****';

?> 

to this:

'pgsql'; 

$PGSQL_dbname = '****'; 

$dbhost     = 'localhost';

$dbusername = '****';

$dbuserpass = '****';

?> 

The global variable $dbms identifies which class file to access before creating the Data Access Object that will be responsible for all communication with the database. This is documented in my FAQ.

The new global variable $PGSQL_dbname identifies the database name to be used in the pg_connect function.

2. Database Class (dml..class.inc)

Each database class has a function which connects to the relevant server. The code in the MySQL class is as follows: function connect ($dbname=null)
// establish a connection to the database
{
global $dbhost, $dbusername, $dbuserpass;

$this->errors = array();
$this->dbname = $dbname;

static $dbconn;

if (!$dbconn) {
$dbconn = mysqli_connect($dbhost, $dbusername, $dbuserpass);
$this->dbconnect = $dbconn;
} // if

if (!$dbconn) {
return FALSE;
} elseif ($dbname) {
if (!mysqli_select_db($dbconn, $dbname)) {
return FALSE;
} // if
} // if

return TRUE;

} // connect

The code in the PostgreSQL class is as follows:

function connect ($schema=null)
// establish a connection to the database
{
global $dbhost, $dbusername, $dbuserpass, $PGSQL_dbname;

$this->errors = array();
$this->dbname = $PGSQL_dbname;

static $dbconn;

if (!$dbconn) {
$string = "host=$dbhost user=$dbusername password=$dbuserpass dbname=$PGSQL_dbname ";
$dbconn = pg_connect($string);
$this->dbconnect = $dbconn;
} // if
if (!$dbconn) {
return FALSE;
} elseif ($schema) {
$this->query = "SET search_path TO $schema";
$result = pg_query($dbconn, $this->query) or trigger_error($this, E_USER_ERROR);
return TRUE;
} // if

return TRUE;

} // connect

As you can see a schema in PostgreSQL maps easily to a database in MySQL.

Tutorial Pages:
» Introduction
» Administrative programs
» Connecting to a Database
» CREATE TABLE syntax
» SQL queries
» Conclusion

Related Tutorials: » Installing MySQL on Windows » Implementing High Availability in MySQL » Stored Procedures are EVIL » MySQL Database Handling in PHP » A Flexible Method of Storing Control Data » Exploring MySQL CURDATE and NOW. The Same But Different.