MySQL | Joh Man X in action

Have you ever come across a situation where you’re dying to make that API for access to the database, only to find out there are multiple database versions and syntaxes to support? Now ofcourse there’s the PDO library, but we don’t want to use that in our case. Why? Because we want to write our own drivers. This allows us to easily implement support for new databases like CouchDB and MongoDB. (We have to note that these two are not similar to classical relational databases, so we might want to write restrictions or fallbacks for them.)

How shall we proceed?

We are going to have a proper thought process on this. Rushing into programming rather abstract PHP isn’t something I can recommend.

List the public methods / creating an abstract class
Decide how to handle drivers
Decide on a file structure
Create a simple database with a test table
Write protected methods
Write an interface for the drivers
Write the first driver
Test the driver
Design a proprietary database system
Write proprietary driver
Test the proprietary driver
Review & refactor
Conclusion

List the public methods / creating an abstract class

To make ourselves a really simple database layer, we’re going to stick with the following few public methods:

abstract class Db_Abstract{
 
	// Force Db class to define these methods
	abstract protected function _prepare($query);
	abstract protected function _query($query);
	abstract protected function _result(); // resource will be stored inside the driver object
	abstract protected function _isConnected();
	abstract protected function _connect($params);
	abstract protected function _affectedRows(); // resource will be stored inside the driver object
	abstract protected function _driver($driver);
 
	/** =Public methods
	 * These functions will define the behavior of our Database class
	 **/
 
	/*
	* @function database
	* @description Select the database
	* @param String $db
	* @return Bool True|False
	*/
	public function database($db){
		return $this->driver->_selectDb($db);
	}
 
	/*
	* @function select
	* @description Select table rows
	* @param Array $fields
	* @param String $table
	* @param String $arguments (optional)
	* @return Array $rows
	*/
	public function select($fields,$table,$arguments=""){
		return $this->driver->select($fields,$table,$arguments);
	}
 
	/*
	* @function insert
	* @description Insert new row(s) into the table
	* @param Array $keys
	* @param Array mixed (String $values | Array $values)
	* @param String $table
	* @return Int $affectedRows
	*/
	public function insert($keys,$values,$table){
		return $this->driver->insert($keys,$values,$table);
	}
 
	/*
	* @function update
	* @description Update row(s) in the table
	* @param Array $keys
	* @param Array mixed (String $values | Array $values)
	* @param String $table
	* @param String $arguments (optional)
	* @return Int $affectedRows
	*/
	public function update($keys,$values,$table,$arguments=""){
		return $this->driver->update($keys,$values,$table,$arguments);
	}
 
	/*
	* @function delete
	* @description Delete a row from the table
	* @param String $key
	* @param String $value
	* @param String $table
	* @return Int $affectedRows
	*/
	public function delete($key,$value,$table){
		return $this->driver->delete($key,$value,$table);
	}
}

As you can see, the Database Abstract purely gives us a new abstraction layer. It makes simple aliases for the functions within the driver. This allows us to have proper naming conventions across all the drivers.

Decide how to handle drivers

We must now decide how to work with the driver. What is handled by the driver and what is handled by the database class.

The driver must

Set up and maintain a connection with the database
Prepare queries
Execute queries
Store resources
Parse results
Insert, update and delete rows
Handle statistics about the database

The database class must

Provide naming conventions
Handle I/O between the database and the rest of your application

Decide on a file structure

Since we want to keep it as readable as possible, we’re going for a decent file structure that describes the contents of itself.

For now we’ll use the following naming conventions:

Database Abstract class and core class

./db.php
Database drivers

./drivers/{$driver}.php

We take for granted that ./ points to the __DIR__^[1] magic constant of db.php.

Create a simple database with a test table

The first driver we will write will be one for MySQL, since I’m most familiar with the MySQL server. We’ll use the following simple test database:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `test`;
 
CREATE TABLE IF NOT EXISTS `test` (
  `key` VARCHAR(255) NOT NULL,
  `value` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`key`)
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 
INSERT INTO `test` (`key`, `value`) VALUES
('key1', 'value1'),
('key2', 'value2');

test

key	value
key1	value1
key2	value2

Write protected methods

The next step is to provide the I/O layer by writing the protected methods. These methods cannot be called from outside the database class. They will parse input before it is handed over to the driver and prepare the output from the driver before it arrives at your application.

class Db extends Db_Abstract{
 
	var $driver;
 
	/*
	* @function __construct
	* @description Run on object creation
	* @param String $connection (input: 'driver://username:password@server:port/database')
	* @return void
	*/
	function __construct($connection=null){
		if(!defined('__DIR__')){
			define('__DIR__',dirname(__FILE__));
		}
		if(!empty($connection)){
			$connection = parse_url($connection);
			$this->_driver($connection['scheme']);
			$this->_connect($connection);
		}
	}
 
	/*
	* @function _escape
	* @description Emulate the behavior of mysql_real_escape_string() to prevent SQL Injection
	* @param String $query
	* @return String $query
	*/
	protected function _escape($query){
		$unsafe_tokens = array("\x00","\n","\r","\\","'",'"',"\x1a");
		$safe_tokens = array("\\\x00","\\\n","\\\r","\\\\","\\"."'","\\".'"',"\\\x1a");
		return str_replace($unsafe_tokens,$safe_tokens,$query);
	}
 
	/*
	* @function _prepare
	* @description Prepares the query before handing it over to the SQL client
	* @param String $query
	* @return String $query
	*/
	protected function _prepare($query){
		// In case the driver has it's own _prepare method
		if(method_exists($this->driver,'_prepare')){
			return $this->driver->_prepare($query);
		}
		return $this->_escape($query);
	}
 
	/*
	* @function _query
	* @description Runs an internal query request
	* @param String $query
	* @return Int $affectedRows
	*/
	protected function _query($query){
		$this->driver->query($query);
		return $this->_affectedRows();
	}
 
	/*
	* @function _result(void)
	* @description Fetch the result of a query
	* @return mixed $result
	*/
	protected function _result(){
		return $this->driver->_result();
	}
 
	/*
	* @function _isConnected(void)
	* @description Fetch the connection status
	* @return Bool True|False
	*/
	protected function _isConnected(){
		return (bool) is_object($this->driver)&&$this->driver->_isConnected();
	}
 
	/*
	* @function _connect
	* @description Connect to the database
	* @param Array $params (result of parse_url)
	* @return Bool True|False $isConnected
	*/
	protected function _connect($params){
		if(!empty($params['host'])&&!empty($params['user'])&&!empty($params['path'])){
			$host = $params['host'];
			$user = $params['user'];
			$pass = (!empty($params['pass'])) ? $params['pass'] : '';
			$port = (!empty($params['port'])) ? $params['port'] : 3306;
			$database = trim($params['path'],'/');
			$this->driver->_connect($host,$user,$pass,$port);
			$this->driver->_selectDb($database);
		}
		return $this->_isConnected();
	}
 
	/*
	* @function connect
	* @description Connect to the database
	* @param String $connection (input: 'driver://username:password@server:port/database')
	* @return Bool True|False $isConnected
	*/
	public function connect($connection){
		if(!$this->_isConnected()){
			$params = parse_url($connection);
			$this->_driver($params['scheme']);
			return $this->_connect($params);
		}
		return true;
	}
 
	/*
	* @function disconnect(void)
	* @description Disconnect from the database
	* @return void
	*/
	public function disconnect(){
		$this->driver->_disconnect();
	}
 
	/*
	* @function _affectedRows(void)
	* @description Fetch the number of rows affected by the last query
	* @return Int $affectedRows
	*/
	protected function _affectedRows(){
		return $this->driver->_affectedRows();
	}
 
	/*
	* @function _driver
	* @description Use the given driver
	* @param String $driver
	* @return void
	*/
	protected function _driver($driver){
		$driver_class = 'Db_Driver_'.ucfirst(strtolower($driver));
		$driver_file = __DIR__.'/drivers/'.strtolower($driver).'.php';
		if(!class_exists($driver_class)&&file_exists($driver_file)){
			require_once($driver_file);
		}
		if(!is_object($this->driver)||get_class($this->driver)!==$driver_class){
			$this->driver = new $driver_class;
		}
	}
 
}

Some functionality should be explained:

_escape($query)

Normally, to prepare a request for the MySQL client, we use mysql_real_escape_string($query) to prevent SQL Injection. Since this function specifically only works when a MySQL connection is active, we can’t use this to escape queries for other database connections. Therefore, we emulate the same functionality with a manual function. This is specifically done for _prepare($query).
_prepare($query)

This method first looks for a nested _prepare method inside the driver object. If it’s available, then it will execute that method. Otherwise, it will fall back to _escape.
_connect($params)

This method uses the result of parse_url(). I specifically chose that structure, because a URL can store all information needed to select a driver, connect to the database server and select the database.

parse_url return values
- scheme (used for driver)
- host
- port
- user
- pass
- path
- query
- fragment
query and fragment are not used, but allow for future extending on the connection methods.

The driver must store the connection status. The database class will not cache the connection status for obvious reasons.
_driver($driver)

If the corresponding driver class does not exist, this will be loaded from the drivers directory.

When there is no driver set or the driver is not the same as the current driver, a new driver object will be created.

Write an interface for the drivers

Writing an interface is quite similar to writing an abstract class. The difference is that an abstract class can contain methods that will be inherited. An interface can only define methods and variables. It is similar to a set of construction guidelines. It is the bare minimum of which a class should consist, without having predefined what happens inside the methods.

In essence, we want to write down whatever method can be called from within the database class. This way, a driver must consist of at least these methods.

interface Db_Driver_Interface {
 
	public function select($fields,$table,$arguments="");
	public function insert($keys,$values,$table);
	public function update($keys,$values,$table);
	public function delete($key,$value,$table);
 
	public function query($query);
	public function _result();
	public function _affectedRows();
 
	public function _isConnected();
	public function _connect($host,$user,$pass,$port);
	public function _selectDb($database);
	public function _disconnect();
 
}

Write the first driver

As We’re testing the database class on a MySQL server, the first driver is a MySQL driver. It will be stored in ./drivers/mysql.php and the class name will be Db_Driver_Mysql. The naming convention is Db_Driver_$class_name where $class_name = ucfirst(strtolower($driver)); (all lower case and capitalize first character).

class DB_Driver_Mysql implements Db_Driver_Interface {
 
	/*
	* @var $res
	* @description Used to store the connection and result resources
	*/
	var $res;
 
	/*
	* @function __construct(void)
	* @description Populate the resources array
	*/
	function __construct(){
		$this->res = array(
			'connection'	=> null,
			'result'		=> null
		);
	}
 
	/*
	* @function select
	* @description Select table rows
	* @param Array $fields
	* @param String $table
	* @param String $arguments (optional)
	* @return Array $rows
	*/
	public function select($fields,$table,$arguments=""){
		$query = 'SELECT `'.implode('`,`',$fields).'` FROM `'.$table.'`';
		$query .= (!empty($arguments)) ? ' '.$arguments : '';
		$this->query($query);
		return $this->_result();
	}
 
	/*
	* @function insert
	* @description Insert new row(s) into the table
	* @param Array $keys
	* @param Array mixed (String $values | Array $values)
	* @param String $table
	* @return Int $affectedRows
	*/
	public function insert($keys,$values,$table){
		$query = 'INSERT INTO `'.$table.'` (`'.implode('`, `',$keys).'`) VALUES ';
		if(isset($values[0])&&is_array($values[0])){ // There are multiple rows to be inserted
			$rows = array();
			foreach($values AS $row){
				$rows[] = '\''.implode('\', \'',$row).'\'';
			}
			$query .= '('.implode('), (',$rows).')';
		}
		else{
			$query .= '(\''.implode('\', \'',$values).'\')';
		}
		$query .= ';';
		$this->query($query);
		return $this->_affectedRows();
	}
 
	/*
	* @function update
	* @description Update row(s) in the table
	* @param Array $keys
	* @param Array mixed (String $values | Array $values)
	* @param String $table
	* @param String $arguments (optional)
	* @return Int $affectedRows
	*/
	public function update($keys,$values,$table,$arguments=""){
 
		$affectedRows = 0;
 
		if(!is_array($values[0])){ $values = array($values); } // Make it multi-dimensional
		for($i=0;$i<count($values);$i++){
			$vals = $values[$i];
			$query = 'UPDATE `'.$table.'` SET ';
			$updates = array();
			for($j=0;$j<count($keys);$j++){
				$val = (is_numeric($vals[$j])) ? $vals[$j] : "'".$this->_prepare($vals[$j])."'";
				$updates[] = '`'.$keys[$j].'` = '.$val;
			}
			$query .= implode(', ',$updates);
			$query .= (!empty($arguments)) ? ' '.$arguments : '';
			$this->query($query);
			$affectedRows += $this->_affectedRows();
		}
 
		return $affectedRows;
	}
 
	/*
	* @function delete
	* @description Delete a row from the table
	* @param String $key
	* @param String $value
	* @param String $table
	* @return Int $affectedRows
	*/
	public function delete($key,$value,$table){
		$value = (is_numeric($value)) ? $value : "'".$value."'";
		$query = 'DELETE FROM `'.$table.'` WHERE `'.$key.'` = '.$value;
		$this->query($query);
		return $this->_affectedRows();
	}
 
	/*
	* @function query
	* @description Perform a native query
	* @param String $query
	* @return void
	*/
	public function query($query){
		$this->res['result'] = mysql_query($query);
	}
 
	/*
	* @function _prepare
	* @description Prepares the query before handing it over to the SQL client
	* @param String $query
	* @return String $query
	*/
	public function _prepare($query){
		return mysql_real_escape_string($query,$this->res['connection']);
	}
 
	/*
	* @function _result(void)
	* @description Fetch the result of a query
	* @return mixed $result
	*/
	public function _result(){
		$result = array();
		while($row=mysql_fetch_assoc($this->res['result'])){
			$result[] = $row;
		}
		return $result;
	}
 
	/*
	* @function _affectedRows(void)
	* @description Fetch the number of rows affected by the last query
	* @return Int $affectedRows
	*/
	public function _affectedRows(){
		return mysql_affected_rows($this->res['connection']);
	}
 
	/*
	* @function _isConnected(void)
	* @description Fetch the connection status
	* @return Bool True|False
	*/
	public function _isConnected(){
		return (bool) is_resource($this->res['connection']);
	}
 
	/*
	* @function _connect
	* @description Connect to the database
	* @param String $host
	* @param String $user
	* @param String $pass
	* @param Int $port
	* @return void
	*/
	public function _connect($host,$user,$pass,$port){
		$this->_disconnect();
		$this->res['connection'] = mysql_connect($host.':'.$port,$user,$pass);
	}
 
	/*
	* @function _selectDb
	* @description Select the database
	* @param String $db
	* @return Bool True|False
	*/
	public function _selectDb($database){
		if($this->_isConnected()){
			return (bool) mysql_select_db($database,$this->res['connection']);
		}
		return false;
	}
 
	/*
	* @function _disconnect(void)
	* @description Disconnect from the database
	* @return void
	*/
	public function _disconnect(){
		if($this->_isConnected()){
			mysql_close($this->res['connection']);
			$this->res['connection'] = null;
		}
	}
 
	/*
	* @function __destruct(void)
	* @description Clean up any open connection
	*/
	function __destruct(){
		$this->_disconnect();
	}
 
}

It’s a whole lot of code, but a lot of it is very common and we’ll run through the methods real fast.

__construct()

First we set up a local array in which we store the connection and result data.
select($fields,$table,$arguments="")

We can use select to select fields from a table and we can use $arguments to provide restrictions to the query, like $arguments = 'WHERE key = \'key1\'';.
insert($keys,$values,$table)

This method can insert values into the table. The values should be in the same order as the keys. Multiple rows can be entered at once. In that case, the structure of the values array is array( $row => array($val,$val), $row => array($val,$val) ).
update($keys,$values,$table,$arguments="")

Update can do single or multiple updates at once. It uses the same structure as insert for the keys and values and can use $argument the same way as the select method can.
delete($key,$value,$table)

This method can delete single or multiple rows, but simply uses 'DELETE FROM `'.$table.'` WHERE `'.$key.'` = '.$value;.
query($query)

This function enables you to perform queries that are native to the system connected to the driver. The result can be retrieved using _result().
_prepare($query)

This method is optional, but since MySQL provides a native function to prepare values, we make use of that.
_result()

All the resulting query data will be put in an associative multi-dimensional array. The first dimension describes the rows and the second dimension consists of the key-value-pair.
_affectedRows()

The number of rows, affected by the last query.
_isConnected()

Tells if a connection with the database is active.
_connect($host,$user,$pass,$port)

Connect to the database.
_selectDb($database)

Select a different database.
_disconnect()

Disconnect from the database.
__destruct()

Clean up any open database connections.

Test the driver

To test our freshly baked driver, we simply have to take the following steps:

Connect to the database
Select all rows
Insert a new row
Select that row
Update that row
Select that row again
Delete that row
Select all rows

Connect to the database

require_once('db.php');
 
$db = new Db;
$db->connect('mysql://root:lamepass@localhost:3306/test');
/*
* Alternatively you could issue:
$db = new Db('mysql://root:lamepass@localhost:3306/test');
*/
 
echo ($db->driver->_isConnected()) ? 'Connection established' : 'Connection failed'; // returns 'Connection established'

Connection established

Select all rows

$s = $db->select(array('key','value'),'test');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key1
            [value] => value1
        )

    [1] => Array
        (
            [key] => key2
            [value] => value2
        )

)

Insert a new row

echo $db->insert(
	array('key','value'),
	array(
		array('key3','value3'),
		array('key4','value4')
	),
	'test'
); // returns affected rows

Select that row

$s = $db->select(array('key','value'),'test','WHERE `test`.`key` = \'key3\'');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key3
            [value] => value3
        )

)

Update that row

echo $db->update(
	array('value'),
	array('value3-2'),
	'test',
	'WHERE `test`.`key` = \'key3\''
); // returns affected rows

Select that row again

$s = $db->select(array('key','value'),'test','WHERE `test`.`key` = \'key3\'');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key3
            [value] => value3-2
        )

)

Delete that row

echo $db->delete('key','key3','test');

And after repeat:

This proves that it deleted the row in the first place and couldn’t find it the second time.

Select all rows

$s = $db->select(array('key','value'),'test');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key1
            [value] => value1
        )

    [1] => Array
        (
            [key] => key2
            [value] => value2
        )

    [2] => Array
        (
            [key] => key4
            [value] => value4
        )

)

Design a proprietary database system

Because we’re only going to use this database system once, I went for the name dummy.

I’m sorry for all the lists in this article, but bear with me as we make another one. The proprietary system will have no DBMS^[2]. To enable the needed functionality, we will implement features of a DBMS inside our driver. This will have no great influence on how we write the driver. The proprietary system will have the following features:

Multiple databases
Multiple tables
Multiple records
A simple key-value-pair structure
Authentication
Rights per database
Rights per table
A simple storage system

auth

johmanx:lamepass
[test]:create,drop,flush,update
=>[test]:select,insert,update,delete

dummy:lamepass
[test]:
=>[test]:select

dbs

[test]
=>[test]:[key,value]
==>[key1,value1]
==>[key2,value2]

[test2]
=>[test]:[name,mail]
==>[Jan-Marten de Boer,lievheid@johmanx.com]

Write proprietary driver

class DB_Driver_Dummy implements Db_Driver_Interface {
 
	/*
	* @var $res
	* @description Used to store the connection and result resources
	*/
	var $res;
 
	/*
	* @function __construct(void)
	* @description Populate the resources array
	*/
	function __construct(){
		$this->res = array(
			'connection'	=> false,
			'credentials'	=> array(),
			'result'		=> null,
			'database'		=> array(),
			'rights'		=> array(),
			'affectedRows'	=> 0,
			'dbs'			=> array()
		);
	}
 
	/*
	* @function select
	* @description Select table rows
	* @param Array $fields
	* @param String $table
	* @param String $arguments (optional) ("key=key3,key=key4")
	* @return Array $rows
	*/
	public function select($fields,$table,$arguments=""){
		$t = $this->res['dbs'][$this->res['database']][$table];
		$result = array();
		$keys = $t['keys'];
		$records = $t['records'];
		$ks = $this->res['dbs'][$this->res['database']][$table]['keys'];
		$affectedRows = 0;
 
		if(empty($arguments)){
			foreach($records AS $row){
				$r = array();
				foreach($fields AS $field){
					if(in_array($field,$keys)){
						$r[$field] = $row[$field];
					}
				}
				$result[]=$r;
				$affectedRows++;
			}
		}
		else{
			if(strpos($arguments,',')!==false){
				$keywords = explode(',',$arguments);
			}
			else{
				$keywords = array(trim($arguments));
			}
			$kws = array();
			foreach($keywords AS $keyword){
				$keyword = explode('=',$keyword);
				$kws[$keyword[0]][]=$keyword[1];
			}
 
			for($i=0;$i<count($records);$i++){
				$rac = 0;
				for($j=0;$j<count($ks);$j++){
					$key = $ks[$j];
					$val = $records[$i][$key];
					if(isset($kws[$key])&&in_array($val,$kws[$key])){
						$rac++;
					}
				}				
 
				if($rac==count($kws)){
					$row = $records[$i];
					$r = array();
					foreach($fields AS $field){
						if(in_array($field,$keys)){
							$r[$field] = $row[$field];
						}
					}
					$result[]=$r;
					$affectedRows++;
				}	
			}			
		}
 
		$this->res['result'] = $result;
		$this->res['affectedRows'] = $affectedRows;
		return $this->_result();
	}
 
	/*
	* @function insert
	* @description Insert new row(s) into the table
	* @param Array $keys
	* @param Array mixed (String $values | Array $values)
	* @param String $table
	* @return Int $affectedRows
	*/
	public function insert($keys,$values,$table){
		if(!is_array($values[0])){
			$values = array($values);
		}
 
		foreach($values AS $record){
			$r = array();
			for($i=0;$i<count($record);$i++){
				$r[$keys[$i]]=$record[$i];
			}
			$this->res['dbs'][$this->res['database']][$table]['records'][] = $r;
		}
 
		$this->res['affectedRows'] = count($values);
		return $this->_affectedRows();
	}
 
	/*
	* @function update
	* @description Update row(s) in the table
	* @param Array $keys
	* @param Array mixed (String $values | Array $values)
	* @param String $table
	* @param String $arguments (optional) ("key=key3,key=key4")
	* @return Int $affectedRows
	*/
	public function update($keys,$values,$table,$arguments=""){
		$records = $this->res['dbs'][$this->res['database']][$table]['records'];
		$ks = $this->res['dbs'][$this->res['database']][$table]['keys'];
		$affectedRows = 0;
 
		if(empty($arguments)){
			for($i=0;$i<count($records);$i++){
				for($j=0;$j<count($keys);$j++){
					$records[$i][$keys[$j]] = $values[$j];
				}
				$affectedRows++;
			}
		}
		else{
			if(strpos($arguments,',')!==false){
				$keywords = explode(',',$arguments);
			}
			else{
				$keywords = array(trim($arguments));
			}
			$kws = array();
			foreach($keywords AS $keyword){
				$keyword = explode('=',$keyword);
				$kws[$keyword[0]][]=$keyword[1];
			}
 
			for($i=0;$i<count($records);$i++){
				$rac = 0;
				for($j=0;$j<count($ks);$j++){
					$key = $ks[$j];
					$val = $records[$i][$key];
					if(isset($kws[$key])&&in_array($val,$kws[$key])){
						$rac++;
					}
				}				
 
				if($rac==count($kws)){
					for($j=0;$j<count($keys);$j++){
						$records[$i][$keys[$j]] = $values[$j];
					}
					$affectedRows++;
				}				
			}
		}
 
		$this->res['dbs'][$this->res['database']][$table]['records'] = $records;
		$this->res['affectedRows'] = $affectedRows;
		return $this->_affectedRows();
	}
 
	/*
	* @function delete
	* @description Delete a row from the table
	* @param String $key
	* @param String $value
	* @param String $table
	* @return Int $affectedRows
	*/
	public function delete($key,$value,$table){
		$records = $this->res['dbs'][$this->res['database']][$table]['records'];
		$affectedRows=0;
		for($i=0;$i<count($records);$i++){
			if($records[$i][$key]==$value){
				unset($this->res['dbs'][$this->res['database']][$table]['records'][$i]);
				$affectedRows++;
			}
		}
		$this->res['affectedRows'] = $affectedRows;
		return $this->_affectedRows();
	}
 
	/*
	* @function query
	* @description Perform a native query
	* @param String $query
	* @return void
	*/
	public function query($query){
		// there is no native query support
	}
 
	/*
	* @function _result(void)
	* @description Fetch the result of a query
	* @return mixed $result
	*/
	public function _result(){
		return $this->res['result'];
	}
 
	/*
	* @function _affectedRows(void)
	* @description Fetch the number of rows affected by the last query
	* @return Int $affectedRows
	*/
	public function _affectedRows(){
		return (int) $this->res['affectedRows'];
	}
 
	/*
	* @function _isConnected(void)
	* @description Fetch the connection status
	* @return Bool True|False
	*/
	public function _isConnected(){
		return (bool) $this->res['connection'];
	}
 
	/*
	* @function _connect
	* @description Connect to the database
	* @param String $host
	* @param String $user
	* @param String $pass
	* @param Int $port
	* @return void
	*/
	public function _connect($host,$user,$pass,$port){
		$this->_disconnect();
 
		$this->res['credentials'] = array(
			'host'		=>	$host,
			'user'		=>	$user,
			'database'	=>	$port
		);
 
		$dummy_file = $host.'/.iamdummy';
		$auth_file = $host.'/auth';
		$dbs_file = $host.'/dbs';
 
		if(file_exists($dummy_file)&&file_exists($auth_file)&&file_exists($dbs_file)){
			$auth = trim(file_get_contents($auth_file));
			$auth = explode("\n\n",$auth);
 
			foreach($auth AS $usr){
				$usr = explode("\n",$usr);
				if($usr[0]==$user.':'.$pass){
					$rights = $usr;
					unset($rights[0]);
 
					$ra = array();
					$cur_db = '';
					foreach($rights AS $rs){
						$rs = explode(':',$rs);
						if(substr($rs[0],0,1)=='['){
							$cur_db = trim($rs[0],'[]');
							$cur_rights = explode(',',$rs[1]);
							$ra[$cur_db] = array(
								'rights'	=>	$cur_rights,
								'tables'	=>	array()
							);
						}
						else{
							$cur_table = substr($rs[0],3,-1);
							$cur_rights = explode(',',$rs[1]);
							$ra[$cur_db]['tables'][$cur_table]=$cur_rights;
						}
					}
					$this->res['rights'] = $ra;
					$this->res['connection'] = true;
				}
			}
 
			$dbs = trim(file_get_contents($dbs_file));
			$dbs = explode("\n\n",$dbs);
 
			$dba = array();
			$cur_db = '';
			foreach($dbs AS $db){
				$db = explode("\n",$db);
				$cur_db = trim(array_shift($db),'[]');
				$dba[$cur_db] = array(); // Database can be empty
				$cur_table = '';
				foreach($db AS $table){
					if(substr($table,0,2)=='=>'){
						$cur_table = substr(trim($table),3,-1);
						$cur_table = explode(']:[',$table);
						$keys = explode(',',rtrim($cur_table[1],']'));
						$cur_table = str_replace('=>[','',$cur_table[0]);
						$dba[$cur_db][$cur_table] = array(
							'keys' => $keys,
							'records' => array()
						);
					}
					else{
						$record = explode(',',substr($table,4,-1));
						$r = array();
						$ks = $dba[$cur_db][$cur_table]['keys'];
						for($i=0;$i<count($record);$i++){
							$r[$ks[$i]]=$record[$i];
						}
						$dba[$cur_db][$cur_table]['records'][]=$r;
					}
				}
			}
			$this->res['dbs']=$dba;
 
			$this->_selectDb($port);
		}
	}
 
	/*
	* @function _selectDb
	* @description Select the database
	* @param String $db
	* @return Bool True|False
	*/
	public function _selectDb($database){
		if(isset($this->res['rights'][$database])&&isset($this->res['dbs'][$database])){
			$this->res['database']=$database;
		}
	}
 
	/*
	* @function _disconnect(void)
	* @description Disconnect from the database
	* @return void
	*/
	public function _disconnect(){
		if($this->_isConnected()){
			$dbs_file = $this->res['credentials']['host'].'/dbs';
			$dbs_data = '';
			foreach($this->res['dbs'] AS $database=>$tables){
				$dbs_data .= '['.$database.']';
				foreach($tables AS $table=>$data){
					$keys = $data['keys'];
					$rows = $data['records'];
 
					$dbs_data .= "\n".'=>['.$table.']:['.implode(',',$keys).']';
					foreach($rows AS $row){
						$ro = array();
						foreach($keys AS $key){
							$ro[] = $row[$key];
						}
						$dbs_data .= "\n".'==>['.implode(',',$ro).']';
					}
				}
				$dbs_data .= "\n\n";
			}
			$dbs_data = rtrim($dbs_data);
			file_put_contents($dbs_file,$dbs_data);
		}
		$this->__construct(); // reset values
	}
 
	/*
	* @function __destruct(void)
	* @description Clean up any open connection
	*/
	function __destruct(){
		$this->_disconnect();
	}
 
}

Test the proprietary driver

And to test the proprietary driver, we simply repeat the tests

Connect to the database
Select all rows
Insert a new row
Select that row
Update that row
Select that row again
Delete that row
Select all rows

Connect to the database

require_once('db.php');
 
$db = new Db('dummy://johmanx:lamepass@dummy/test');
 
echo ($db->driver->_isConnected()) ? 'Connection established' : 'Connection failed'; // returns 'Connection established'

Connection established

Select all rows

$s = $db->select(array('key','value'),'test');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key1
            [value] => value1
        )

    [1] => Array
        (
            [key] => key2
            [value] => value2
        )

)

Insert a new row

echo $db->insert(
	array('key','value'),
	array(
		array('key3','value3'),
		array('key4','value4')
	),
	'test'
); // returns affected rows

Select that row

$s = $db->select(array('key','value'),'test','key=key3');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key3
            [value] => value3
        )

)

Update that row

echo $db->update(
	array('value'),
	array('value3-2'),
	'test',
	'key=key3'
); // returns affected rows

Select that row again

$s = $db->select(array('key','value'),'test','key=key3');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key3
            [value] => value3-2
        )

)

Delete that row

echo $db->delete('key','key3','test');

And after repeat:

This proves that it deleted the row in the first place and couldn’t find it the second time.

Select all rows

$s = $db->select(array('key','value'),'test');
print_r($s);

Array
(
    [0] => Array
        (
            [key] => key1
            [value] => value1
        )

    [1] => Array
        (
            [key] => key2
            [value] => value2
        )

    [2] => Array
        (
            [key] => key4
            [value] => value4
        )

)

Review & refactor

This database class has opened up a world of new possibilities concerning the implementation of databases. We can now add on database systems like MongoDB. It’s also easy to write drivers for systems that in essence are no database at all. I can see a driver for ftp, so you could use file trees as databases, their directories as tables and the files as records with the file name and other properties as keys for the table. Select could display a directory, insert could add files, update could modify files and delete could delete them.

What needs refactoring

I would change the arguments for the MySQL driver to correspond the arguments for the Dummy driver. SQL is too complex to be fully supported over all possible drivers and the key=value,key=value syntax is much more generic and could easily substitute the WHERE key = 'value' AND key = 'value' argument.

I have also stripped the rights implementation for the dummy driver. As I wrote the driver, I found that it was a really large amount of code for a simple example and thus for abstractions sake, I removed the rights checks. I created a simple method that checked if $action was inside the rights array. I then made a wrapper if-clause for the select, insert, update and delete methods. When the code would be refactored, that would be the first step to implementing a proper rights system.

On Forrst, I got the comment:

Shouldn’t the driver attribute be protected (you haven’t set the attribute visibility at all), and make an abstraction for the _isConnected method? So you don’t have to do $db->driver->_isConnected(). The end user shouldn’t call methods prefixed with _ IMO
– Horacio J. Peña

By the time I was writing the drivers, I detected this flaw, since you shouldn’t have to do this: $db->driver->_isConnected();. However, I found that coding for a tutorial may introduce some flaws, so we can all have a good look at it. Horacio is completely right. When refactoring, we should introduce a new abstraction and make all the methods preceded by _ private or protected.

Conclusion

As it is, the database class is still incomplete and immature, but it has great potential and great extendability (by nature, since it supports it’s own drivers). It could also fit well in a RESTful API where you make extensive use of the HTTP Methods GET, POST, PUT and DELETE, easily interchangeable with select, insert, update and delete.

This tutorial was a great learning process, even for myself. It has opened a number of insights in database systems and what my proprietary driver especially points out is the flaws in such a simple system. It’s hackable and breakable in so many ways, you can’t start counting them. Still, in a secluded and protected environment, the simplicity is a big win, since you can open up your fave text editor and alter the database in a breeze. Really useful when your code breaks the database.

If you find any of this useful or have some thoughts on how I made some horrible mistakes, please feel free to leave a comment. All feedback is appreciated and whenever possible I’ll improve the article correspondingly.

Notes

^ __DIR__ is a magic constant that defines the current directory in which the script resides. In PHP versions prior to 5.3.0, the same could be achieved using dirname(__FILE__), which extracts the directory name of the current file.
^ A database management system (DBMS) is a software package with computer programs that controls the creation, maintenance, and the use of a database. We combined the DBMS with the driver to prevent abstraction, in order to maintain a readable tutorial.

How shall we proceed?

List the public methods / creating an abstract class

Decide how to handle drivers

The driver must

The database class must

Decide on a file structure

Database Abstract class and core class

Database drivers

Create a simple database with a test table

test

Write protected methods

_escape($query)

_prepare($query)

_connect($params)

parse_url return values

_driver($driver)

Write an interface for the drivers

Write the first driver

__construct()

select($fields,$table,$arguments="")

insert($keys,$values,$table)

update($keys,$values,$table,$arguments="")

delete($key,$value,$table)

query($query)

_prepare($query)

_result()

_affectedRows()

_isConnected()

_connect($host,$user,$pass,$port)

_selectDb($database)

_disconnect()

__destruct()

Test the driver

Connect to the database

Select all rows

Insert a new row

Select that row

Update that row

Select that row again

Delete that row

Select all rows

Design a proprietary database system

auth

dbs

Write proprietary driver

Test the proprietary driver

Connect to the database

Select all rows

Insert a new row

Select that row

Update that row

Select that row again

Delete that row

Select all rows

Review & refactor

What needs refactoring

Conclusion

Notes

`_escape($query)`

`_prepare($query)`

`_connect($params)`

`parse_url` return values

`_driver($driver)`

`__construct()`

`select($fields,$table,$arguments="")`

`insert($keys,$values,$table)`

`update($keys,$values,$table,$arguments="")`

`delete($key,$value,$table)`

`query($query)`

`_prepare($query)`

`_result()`

`_affectedRows()`

`_isConnected()`

`_connect($host,$user,$pass,$port)`

`_selectDb($database)`

`_disconnect()`

`__destruct()`