Is this a good PHP OOP structure for a Database Implementation?

Question

I have a series of Classes:

abstract class Database extends PDO {} abstract class OracleDatabase extends Database {} abstract class MySQLDatabase extends Database {} abstract class MSSQLDatabase extends Database {}

Then when I want an instance of a database connection, I create a new class that extends either OracleDatabase, MySQLDatabase or MSSQLDatabase, depending on where the database lives... e.g.

class MyAppDatabase extends OracleDatabase {}

First of all, is this a good design? I've read that it's better to use Interfaces rather than extending abstract classes, but I'm not sure how to do that without duplicating code.

I do realize the whole point of PDO is to get away from DB-specific code, but I do still need different functionality when for things like DB object bracketing (e.g. in Oracle, you use double quotes; in MySQL you use backticks), data type detection (user_tab_columns vs INFORMATION_SCHEMA), etc.

And - what if I want to create a class called Updater which lets the client create a set of multiple "updates" to a specific table - using the same fields for SET and WHERE clauses, but different values? I don't think I'd be able to inherit from the Database class, so would I just make it an independent class that "HAS" a Database object?

class Updater { private $db; private $table; private $setFields; private $whereFields; private $updates; function __construct($db, $table, $setFields, $whereFields) { if (!($db instanceof Database)) { if (is_scalar($db)) { $type = gettype($db); } else { $type = get_class($db); } throw new Exception("Expected Database object; " . $type . " found."); } $this->db = $db; // ensure $table is a table in the database // ensure $setFields and $whereFields are columns in the table $this->table = $table; $this->setFields = $setFields; $this->whereFields = $whereFields; $this->updates = array(); } function addUpdate($setValues, $whereValues) { // ensure $setValues is an array and has the same cardinality as // $this->setFields // ensure $whereValues is an array and has the same cardinality as // $this->whereFields array_push($this->updates, array( 'setValues'=>$setValues, 'whereValues' => $whereValues ) ); } function doUpdate() { // without error handling $escTable = $this->db->bracket($table); $setTemplate = array(); foreach ($this->setFields as $setField) { $escField = $this->db->bracket($setField); $colonField = makeColonField($setField); // :fieldName $setting = "$escField = $colonField"; array_push($setTemplate, $setting); } $csvSetTemplate = implode(", ", $setTemplate); $whereTemplate = array(); foreach ($this->whereFields as $whereField) { $escField = $this->db->bracket($whereField); $colonField = makeColonField($setField); // :fieldName $setting = "$escField = $colonField"; array_push($whereTemplate, $setting); } $andedWhereTemplates = implode(" AND ", $whereTemplate); $sql = "UPDATE $escTable SET $csvSetTemplate WHERE $andedWhereTemplates"; $sth = $this->db->prepare($sql); foreach ($this->updates as $update) { $setValues = $update['setValues']; $whereValues = $update['whereValues']; $params = array(); for ($i=0; $i<count($setValues); $i++) { $setField = $this->setFields[$i]; $setValue = $setValues[$i]; $colonField = makeColonField($setField); $params[$colonField] = $setValue; } for ($i=0; $i<count($whereValues); $i++) { $whereField = $this->whereFields[$i]; $whereValue = $whereValues[$i]; $colonField = makeColonField($whereField); $params[$colonField] = $whereValue; } $sth->execute($params); } } }

Is this a good solution?

Is it necessary to declare Oracle/MySQL/MSSQLDatabase as abstract? Seems they could be concrete, rather than declaring a new class to declare an instance of each. — Pudge601
– Pudge601, Commented Jun 6, 2013 at 16:08
@YourCommonSense, not sure. I want the flexibility of being able to switch DB implementations without affecting the application code. — livefree75
– livefree75, Commented Jun 6, 2013 at 16:24

tess3ract · Accepted Answer · 2013-06-06 16:12:33Z

First, I suggest that abstract classes are better in this case because there are usually many thing in common between different RDBMSs, with which you can write some common methods to solve the problem more clearly.

There is another way to do this:

class Database extends PDO { private $engine; //this is a DBEngine } interface DBEngine { } class MySQLEngine implements DBEngine { } class MSSQLEngine implements DBEngine { } ...

In this case, you can use interface because common methods are implemented in Database, and each engine just implement methods which have different behaviors between RDBMSs.

This is called adapter mode, you can read some about this in the source code of CodeIgniter: http://ellislab.com/codeigniter.

And for the second question, you may implement the update method just in class Database.

In another word, make "update" a public member of class Database.

Just looked into codeigniter. Looks great. I may use that for my next project. :)

Martin Bean · Accepted Answer · 2015-04-14 09:09:14Z

I’d be more inclined to have a database wrapper class, and then inject a “provider” (a provider being MySQL, Oracle or some other database engine), and then interact with your database through your wrapper class.

You’d have a bootstrap file that sets these up, and then passes your newly-created database object instance to your application.

This is called dependency injection. The theory being, you can switch your database engine for another one with ease, or even inject a test database and use the same application.

An quick, written-off-the-cuff example:

<?php class Database { private $database; public function setProvider(DatabaseProvider $database) { $this->database = $database; } public function select($table, $fields = array(), $conditions = array(), $order = array()) { $this->database->select($table, $fields, $conditions, $order); } public function insert($table, $values) { $this->database->insert($table, $values); } public function delete($table, $conditions = array()) { $this->database->delete($table, $conditions); } }

And then an example provider:

<?php class MySQL implements DatabaseProvider { public function __construct($config) { // create PDO instance with settings in $config $this->connection = new PDO(); } public function select($table, $fields = array(), $conditions = array(), $order = array()) { // build SELECT statement based on table, and other parameters passed // return result set } // and so on... }

Your instance of MySQL would be created in your bootstrap file, where you would also pass in your connection settings.

Obviously the above isn’t a complete example, but should be enough to get you started on fleshing out your own solution.

Bonus

Sample directory structure, heavily inspired by Symfony 2.

/app /config database.xml /src /VendorName /AppName /Database /Provider DatabaseInterface.php MSSQL.php MySQL.php Oracle.php Database.php Application.php Bootstrap.php

Collectives™ on Stack Overflow

Is this a good PHP OOP structure for a Database Implementation?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related