Singleton abstract DB class, with mysql, etc extends...

[Naez]

I'm working on a project with 2 other people, and our project has to be as "available" as possible.

 

Now I know nothing about dealing with postgresql or oracle databases, but the people I'm working with do.

 

 

So basically, there can only be 1 DB connection at any one time, so the main "dbconn" class will be a singleton.

 

Now, dbconn has a few simple things it does such as builds SQL query syntax from an array for INSERTs, UPDATEs, SELECTs, etc.  It also validates some data going in.

 

All the "extending" classes should have the same basic functions, but with the syntax for that kind of database (Such as whether we're working with Oracle or MySQL or PostgreSQL there should always be a function called sql_query and fetch_array, etc)

 

So, could someone please tell me the right way to get this working as it should...

 

Here's what I've unsuccessfully thrown together:

 

dbconn.class.php

<?php 

abstract class dbconn
{

static private $instance = null;
private $_conn;
private $_db;
public $num_queries;
public $result;

private function __construct(){

}

abstract static public function getInstance();
abstract public function connect($dbhost,$dbuser,$dbpass,$dbname=NULL);
abstract public function select_db($dbname);
abstract public function sql_query($query);
abstract public function fetch_array($res=NUlL);
abstract private function sql_escape($val);
abstract public function sql_close();

private function sql_validate_value($var)
{
	if (is_null($var))
	{
		return 'NULL';
	}
	else if (is_string($var))
	{
		return "'" . $this->sql_escape($var) . "'";
	}
	else
	{
		return (is_bool($var)) ? intval($var) : $var;
	}
}


public function sql_build_array($query, $assoc_ary = false)
{
	if (!is_array($assoc_ary))
	{
		return false;
	}
                 // snip, lots of array building here....

	return $query;
}	

}



?>

 

And the MySQL.class.php

 

<?php

require_once("dbconn.class.php");

class mysql extends dbconn
{

private function __construct(){

}

public function getInstance(){
	if(is_null(self::$instance)){
		self::$instance = new self();
	}
	return self::$instance;
}


public function connect($dbhost,$dbuser,$dbpass,$dbname=NULL)
{
	$this->_conn = @mysql_connect($dbhost,$dbuser,$dbpass) or die('Could not connect: ' . mysql_error());
	if ($dbname){	$this->select_db($dbname); }
}

public function select_db($dbname)
{
	$this->_db = @mysql_select_db($dbname, $this->_conn) or die('Could not select specified database: ' . mysql_error());
}

public function sql_query($query)
{
	if (!$query)
	{	
		return false;	
	}
	else
	{
		$this->num_queries++;
		$result = @mysql_query($query, $this->_conn) or die('Query was not valid: ' . mysql_error());
		$this->result = $result;
		return $result;
	}
}

public function fetch_array($res=NULL)
{
	if ($res){ $this->result = $res; }
	$row = @mysql_fetch_assoc($this->result);
	return $row;
}

public function sql_escape($msg)
{
	return @mysql_real_escape_string($msg);
}

public function sql_close()
{
	unset($this->_db);
	@mysql_close($this->_conn);
}

}

?>

 

 

Now, it won't let me use static or private stuff with this abstract class, really I'm at a loss of how to do this the right way.

 

 

The code that shoud call this stuff will be like

 

<?php
$dbtype = "mysql"; // from a config file

include("/includes/" . $dbtype . ".class.php";

$db = $dbtype::getInstance();

?>

 

yea wetfff I knoww

[keeB]

Define the interface..

<?php

interface Database {

        function connect($connectionString, $userName, $password);
        function select_db($db);
        function query($q);
        function num_rows();
        function next();

}

?>

 

Write a (concrete) implementation (wrote this a while ago, could be improved upon):

 

<?php
@include_once("database.interface.php");
@include_once("sql.exception.php");

class mysql implements Database {

        private $connection;
        private $resultSet = NULL;


        function mysql($host, $userName, $password) {
                if(!extension_loaded("mysql")) {
                        throw new SQLException("MySQL not loaded");
                        die();
                }

                try {
                        $this->connect($host, $userName, $password);
                } catch (SQLException $e) {
                        die($e->message());
                }

        }

        public function connect($host, $userName, $password) {
                $this->connection = @mysql_connect($host, $userName, $password);
                if (!$this->connection) {
                        throw new SQLException("Unable to connect." . mysql_error(), 1);
                }
        }

        public function select_db($database) {
                $selected = mysql_select_db($database);


        }

        public function query($q) {
                $this->resultSet = mysql_query($q);
                if (!$this->resultSet) {
                        throw new SQLException("Error executing SQL statement. Here's the error: " . mysql_error());
                        return false;
                }
                return $this->resultSet;
        }

        public function next() {
                //nothing yet
        }


        public function num_rows() {
                if($this->resultSet != NULL) {
                        return mysql_num_rows($this->resultSet);
                }
                throw new SQLException("No rows");
        }



}
?>

 

Write an Abstract Factory (singleton which gets a concrete implementation based on context)

 

<?php
class DatabaseFactory {

public static function create( DatabaseConfiguration $request){
       switch ($request->type()) {
                     case 'mysql':
                        return new MySQL($request->host(), $request->uname(), $request->pwd());
                     default:
                        throw new InstanceNotFoundException("Database type not supported");
               }
        }
}
?>

[Naez]

Problem with using interface is it won't let me define parent functions within the class.

 

For instance, no matter which database is being used, there are still functions I am using within the main "dbconn" class that they all use (such as the SQL syntax array builder I mentioned).

[Daniel0]

Declare the methods in dbconn as protected instead of private. Members which are declared as protected can be accesses by child classes as well, where as private members are exclusive to the specific class only.

 

In this case it would make sense to use the factory pattern on the dbconn class. I.e. dbconn::factory('mysql') would return an instance of your MySQL specific class and dbconn::factory('sqlite') would return an instance of your SQLite specific class.

[Naez]

Thanks guys I got it working like I wanted because of these suggestions.

[aschk]

Having done some work recently with PDO i can highly recommend it if you're using PHP5. It deals with all the different database systems for you (providing the driver is installed). And all you need to do is pass it a different DSN string if you want to connect to a different database system. Easy peasy...

[Naez]

PDO isn't standard yet though =/

 

I hope it does become someday it looks easy to manipulate.

[Daniel0]

What do you mean with standard? It's enabled by default in PHP5.

[Naez]

Oh, I haven't messed with it, but it looked like it needed extensions installed in order for it to work.  I see now that PDO comes standard with PHP5.1, but not standard with PHP5.0.  But nobody is using 5.0, so it's not a problem!

[Jenk]

But nobody is using 5.0, so it's not a problem!

If only that were true.

[Naez]

PHP should like automatically update like java but you can also turn it off or upgrade manually... something easier than it is now where hosts can fall behind in updating it.

[aschk]

Automatic updating wouldn't necessarily be a good thing. Imagine if a function method and number of params and param order were to change (as has been the case in the past), then if automatic updates is set on, one day you will find that your website starts having erratic behaviour... Also bear in mind that if you upgraded from 4->5 suddenly everything object wise would probably break

 

Like most other "auto-updates", they're not necessarily a good thing.