UniDB - MySQL, PostgreSQL, SQLiteFork on Github

Table of Contents

• Introduction
• Requirements
• Required Files
• Code Samples
• API
• FAQ

Introduction

UniDB is a set of classes that provide a unified interface to connect and work with MySQL, PostgreSQL and SQLite databases. Without knowing the interals of either database server, you can use the same codebase to connect to the server, fetch data and iterate over it in your application. UniDB handles all the database specific commands and stuff underneath, so you don't need to worry about the data being different when accessing either database server. Other than the usual data access functionality, UniDB also include a number of utilities functions for each database, allowing you to work more easily with the database of your choice you choose for your application.

Requirements

UniDB requires PHP version 5.3+.

For the code to work, you will need to make sure proper extensions for the database clients are enabled in your PHP configuration.

Database support matrix is as follows:

• MySQL: Version 4.0+ (including version 5)
• PostgreSQL: Version 9.0+
• SQLite: Version 2+

Required files

To start using UniDB, copy the files from the 'classes' folder inside this package anywhere you like.

Once you have copied these files to a folder of your choice, you are ready to use the UniDB class.

Code Samples

Basic Usage

The following is the simplest form of using UniDB in your PHP application:

<?php
  require( "classes/unidb.php" );
  $db = new UniDB( 'mysql5' );
  $db->connect( 'localhost', 'username', 'password' );
  $db->query( 'SELECT * from person' );
  while( $row = $db->fetch_row() ) {
  	echo 'Person Name: ' . $row['Name'];
  }
  $db->disconnect();
?>

Connecting to a particular Database Server

The server type should be specified in the UniDB class constructor when you create new objects:

You can specify either one of these as argument:

• 'mysql4': If database server is MySQL version 4.0
• 'mysql5': If MySQL database server version is 5.0 and above
• 'mysqli': If MySQL database server version is 5.0 and above (requires 'mysqli' extension to be enabled in your PHP installtion)
• 'pgsql': If database server is running PostgreSQL version 9.0 or above
• 'sqlite': If you want to us SQLite databases in your application

Note: Since SQLite databases are portable and do not require a server, you should create a folder for your SQLite databases and specify it's path in the UniDB connect() method as argument, instead of host address.

Using PDO drivers

In order to use the PHP PDO extensions, you need to specify a second argument to the constructor for UniDB.

Currently the only option for this parameter is 'pdo', which makes UniDB use PDO drivers for the database instead of regular functions.

<?php
  require( "classes/unidb.php" );
  $db = new UniDB( 'mysql5', 'pdo' ); // note the second parameter
  $db->connect( 'localhost', 'username', 'password' );
  ...
  // rest of the code remains same for other method calls
?>

Note: Due to limitations of the PDO drivers in PHP, the num_rows() method in UniDB class always returns 0.
Unfortunately I could not find a nice workaround for that, so you are free to modify the code for pdo related functionality if you need num_rows() to work correctly.
There are a few implementations of this on the web. but none general in nature, so I have not included any in my class.
				

CRUD operations

To perform basic CRUD operations on your database, you can use code sample below:

<?php
  require( "classes/unidb.php" );
  $db = new UniDB( 'mysql5' );
  $db->connect( 'localhost', 'username', 'password' );

  // insert a new record
  $db->insert( 'person', array( 'Name' => 'John Doe', 'Dept_id' => 10 ) );

  // update an existing record
  $db->update( 'person', array( 'Name' => 'John Doe', 'Dept_id' => 10 ), "WHERE id = 10" );

  // delete an existing record
  $db->delete( 'person', "WHERE id = 10" );

  $db->disconnect();
?>

Multiple Queries

Sometimes it is desired to run additional queries within a fetch loop. UniDB provides a simple way to do this. See the sample below:

<?php
  require( "classes/unidb.php" );
  $db = new UniDB( 'mysql5' );
  $db->connect( 'localhost', 'username', 'password' );

  $db->query( 'SELECT * from person' );
  while( $row = $db->fetch_row() ) {
  	// run another query on the same db object
  	// notice the separate 'stack' argument to distinguish it from the main query
  	$db->query( 'SELECT * from dept WHERE dept_id = ' . $row['Dept_id'], '_dept' );
  	while( $row2 = $db->fetch_row( '_dept' ) ) {
  		echo 'Department Name: ' . $row2['Name'];
  }

  $db->disconnect();
?>

Note: You can run as many queries on a single object in parallel as you want, you only need to provide a separate string identifier for each query.

UniDB API methods

SQLite does not require userid and password. You can either skip the two parameters, or provide empty values for them.

array(
  'views' => TRUE,
  'functions' => FALSE,
  'procedures' => FALSE,
  'triggers' => TRUE,
  'schemas' => FALSE,
  'sequences' => FALSE,
  'events' => FALSE					
)

Note: When using PDO with pgsql, this function always returns 0

Note: Since PostgreSQL has schemas, the returned table names are prefixed by schema name, e.g. public.person, private.stats etc.

Note: This method only works on databases that actually support views. (e.g. MySQL 5).

Note: This method only works on databases that actually support schemas (e.g. PostgreSQL).

Note: This method only works on databases that actually support triggers. (e.g. MySQL 5).

Note: This method only works on databases that actually support procedures. (e.g. MySQL 5).

Note: This method only works on databases that actually support functions. (e.g. MySQL 5).

Note: This method only works on databases that actually support sequences. (e.g. PostgreSQL).

Examples:
  $db->drop( 'person', 'table' ); // drops the person table
  $db->drop( 'staff_managers', 'view' ); // drops the view named staff_managers
								

Options supported:
  'result_type' // the type of result set array indices (can be 'num', 'assoc' or 'both', default is 'both')
  'file_ext' // filename extension to be used in case of SQLite database (see demos/sqlite3.php for an example)
								

Example:
  $db->set_option('result_type', 'num'); // any result set fetched after this line will only contain numberic indices
  $db->set_option('result_type', 'assoc'); // any result set fetched after this line will only contain associative indices
  $db->set_option('result_type', 'both'); // result set will contain both types of indices
								

Example:
  $db->set_options(array('result_type'=>'num')); // please note that invalid option names will simply be ignored
  							

Pre-defined expressions:
  $db->expr( 'datetime' ); // returns 2014-05-08 20:05:05
  $db->expr( 'date' ); // returns 2014-05-08
  $db->expr( 'time' ); // returns 20:05:05
								

Other usage examples (see demos/expressions.php for running code):
  $db->expr( 'NOW()' ); // works only for MySQL databases
  $db->expr( 'MAX(user_id) + 1' ); // returns integer id greater than the highest user id in database
  $db->expr( 'COUNT(*)' ); // can be used in selected queries 
								

FAQ

Why is there no 'drop_db' method in API?

Drop database is a dangerous method and can cause critical data loss, therefore it is intentionally not included in the API.

Can I create multiple UniDB objects and connect to different databases on the same page?

Yes, you can create as many UniDB objects providing different driver type for each object for your use.

Can I use UniDB in place of an ORM in my application?

No, UniDB is not a replacement for ORM. However, you can modify your ORM code to use UniDB as the underlying database interface for the ORM.

I just get FALSE when I run a query on the database. How do I know what is wrong?

Use the get_last_error() method after a failed query to get the error message