Professional PHP5 (Programmer to Programmer Series)
ISBN: N/A
EAN: N/A
EAN: N/A
Year: 2003
Pages: 182
Pages: 182
PEAR's DB package provides a lot of utility functions that you can take advantage of to build a fully customized database abstraction layer for use with any database backend. The class that follows is a complete tool that you can reuse on all your projects.
Create a configuration file called config.php to store the DSN:
$cfg['db']['dsn'] = 'pgsql://postgres:password@localhost/mydb'; Call your class Database and create a file called class.Database.php. In the constructor, you'll establish a connection, possibly throwing an error if there were any problems.
<?php require_once('config.php'); require_once('DB.php'); class Database { private $conn; function __construct($dsn = null) { global $conn; //If nothing was passed in, use the value from $cfg if($dsn == null) { $dsn = $cfg['db']['dsn']; } //Open a connection using the info in $dsn $this->conn = DB::connect($dsn); if(DB::isError($this->conn)) { //We're not connected. Throw an exception throw new Exception($this->conn->getMessage(), $this->conn->getCode()); } //Always fetch data as an associative array $this->conn->setFetchMode(DB_FETCHMODE_ASSOC); } function __destruct() { $this->conn->disconnect(); } } ?>
Under normal circumstances you'll be using the DSN from the configuration information, so you don't need to supply a parameter to the constructor; however, should you need to use some alternate connection string for some reason, the constructor will accept a DSN as its only parameter. Because no significant overhead is involved in fetching information from the database as an associative array rather than as a numerically indexed array, and because an associative array is so much easier to deal with, you should add the line of code found at the end of the constructor that makes all get*() and fetch*() methods return associative arrays.
Note that the constructor throws an exception if things go wrong. This will be the case for nearly every method of the Database class. The arguments to the Exception object's constructor are the string message and numeric code from the DB_error object returned by the DB::connect() method.
You should also create the destructor to ensure that the database connection is closed when this object is no longer needed. This is important, because without closing the connection properly you may well find that each subsequent request of your PHP script ties up yet another spare socket on your database server; it will quickly run out, and could easily fall over as a result.
Now let's add a few methods to your class for selecting chunks of data from the database. You'll provide select(), getAll(), getOne(), and getCol () methods. The select() method will return a DB_result object. This should be used whenever the recordset might be more than just a few records. The other methods will just pass through to the DB_common methods with the same name.
//returns a DB_result object function select($sql) { $result = $this->conn->query($sql); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } return $result; } //returns 2D assoc array function getAll($sql) { $result = $this->conn->getAll($sql); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } return $result; } //returns single scalar value from the first column, first record function getOne($sql) { $result = $this->conn->getOne($sql); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } return $result; } //returns numerically indexed 1D array of values from the first column function getColumn($sql) { $result = $this->conn->getCol($sql); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } return $result; }
All these methods will throw an exception if there are any problems. By throwing exceptions instead of just returning the value of the DB_common methods, you're able to properly encapsulate the use of the DB package. If at some point you decide you want to use a different database abstraction (such as the PEAR MDB package), you won't need to change code outside this class.
Now let's look at adding an update() method, so we can easily manipulate existing data in the database. The update() method takes the name of a table, an associative array of field names (as the key) and values (as the value) to be updated, and a string WHERE clause. The reason you don't construct the WHERE clause from an array of field names and values is that you will probably want to have the ability to use different operators (=, LIKE, IN, and so on.) and use regular expressions, if supported by your RDBMS. Creating a WHERE parameter that supports this effectively across multiple database backends is difficult, if not impossible, so the WHERE clause strings needs to be manually generated outside the class.
function update($tableName, $arUpdates, $sWhere = null) { $arSet = array(); foreach($arUpdates as $name => $value) { $arSet[] = $name.'='. $this->conn->quoteSmart($value); } $sSet = implode(', ', $arSet); //make sure the table name is properly escaped $tableName = $this->conn->quoteIdentifier($tableName); $sql = "UPDATE $tableName SET $sSet"; if($sWhere) { $sql .= " WHERE $sWhere"; } $result = $this->conn->query($sql); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } //return the number of rows affected return $this->conn->affectedRows(); }
Use the quoteSmart() method to ensure that the values you've supplied in the second parameter are properly escaped before executing the SQL statement. The quoteIdentifier() method is used for table names. The return value of update() is the number of records affected.
| Important | Be careful! If you don't supply a WHERE clause to this function, every row in the table will be altered. |
The insert() method works similarly to update(), except there is no WHERE clause.
A special value token in the insert() method allows you to fetch the id value for newly created records. If any value of the $arValues array is equal to the literal string '#id#', assume that the corresponding column is the primary key value for this table and replace that value with the nextID() from the sequence called $tableName_id_seq. If that token was encountered, the function returns the new ID value. If not, it returns the number of records affected by the insert statement (usually 1).
function insert($tableName, $arValues) { $id = null; $sFieldList = join(', ', array_keys($arValues)); $arValueList = array(); foreach($arValues as $value) { if(strtolower($value) == '#id#') { //we need to get the next value from this table's sequence $value = $id = $this->conn->nextID($tableName . "_id"); } $arValueList[] = $this->conn->quoteSmart($value); } $sValueList = implode(', ', $arValueList); //make sure the table name is properly escaped $tableName = $this->conn->quoteIdentifier($tableName); $sql = "INSERT INTO $tableName ($sFieldList) VALUES ($sValueList)"; $result = $this->conn->query($sql); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } //return the ID, if there was one, or the number of rows affected return $id ? $id : $this->conn->affectedRows(); }
If '#id#' was found as one of the values, the class returns the value of the new ID; otherwise, it returns the number of affected records. Make sure that you know which flavor of return value you're expecting when calling this function, or you might mistakenly assume that an ID was 1 when that was really the number of affected records.
This class is significantly more robust than the simple abstraction layer created at the beginning of the chapter, but there are still two more features this needs to support to be complete. The first is transactions.
Transactions are used to allow changes to a database to be grouped and proposed before they are committed. Such a technique is typically used when the changes to be made are useless in isolation from one another, and hence should one fail for either technical or logistical reasons, any previously proposed changes will be discarded.
With this in mind, when INSERT, UPDATE, and DELETE statements are issued within a transaction context, they aren't actually written to the database until you call COMMIT. If something goes wrong, all the changes are undone and no other users of the database are aware that anything ever changed. To support transactions in the Database class, you need to implement three methods: a method to declare the commencement of a transaction, a method to commit the proposed changes of the transaction, and a method to abort the transaction to date and undo any changes made since the transaction started.
The startTransaction () method establishes a transaction context. This means that the database backend is informed that you want to do everything inside a transaction. If your chosen backend does not support transactions, an error will be raised by the DB_common class.
function startTransaction() { //autoCommit returns true/false if the command succeeds return $this->conn->autoCommit(false); }
Normally, most RDBMSes ship out of the box with autoCommit turned on. This means that all INSERT, UPDATE, and DELETE statements automatically commit to the database. There is no need to explicitly commit all changes in this state. Accordingly, you can effectively enter a transaction context by setting autoCommit() to false, which will require all statements to be committed explicitly.
Having issued the series of changes that make up your transaction proposal, you have two choices. You can either commit() those changes, which writes them to the database, or you can abort() the transaction, which rolls everything back to the state it was before issuing the first statement.
function commit() { $result = $this->conn->commit(); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } $this->conn->autoCommit(true); return true; } function abort() { $result = $this->conn->rollback(); if(DB::isError($result)) { throw new Exception($result->getMessage(), $result->getCode()); } return true; }
Recent versions of most of the major database platforms support transactions. This includes PostgreSQL, MySQL, Oracle, Microsoft SQL Server, and several others. Any time your application needs to alter more than one table to accomplish a single function, you should use a transaction to ensure that a problem with one statement will not affect the referential integrity of your database that is, there are not references to entities in foreign tables which do not actually exist (for example, a column user_id with a value 235, when there is no user with id 235 in the user table). For more information on the transaction support provided by your preferred database, see the documentation provided by its vendor.
Previously, we said there were two more features to be implemented in our Database class. Transactions were one; the other concerns the way in which database connections are opened. In the next section we introduce you to an important design pattern called Singleton.
The Singleton design pattern provides a mechanism whereby the number of instances of a class is limited to one. You may have either zero instances of the class, or one instance never more than one.
Typical situations in which you might want to do this include the instantiation of classes that have particularly large overhead, or where having more than one copy is nonsensical. In this case, you have only one database that drives the application (specified in the config.php files as $cfg['db'][dsn']). Opening a connection to the database usually takes far more resources than all but the most complex queries. Because you have only one database to connect to and PHP is not a multithreaded programming language (the PHP engine itself is multithreaded, but applications written in PHP are not), there is no reason to open more than one database connection for a given page request, unless, for some reason, you are connecting to two separate databases.
As a result, you want to make sure that you suffer the overhead of establishing a database connection only once. Here's how this works.
First, add a static method to the Database class called instance(). This method will return an instantiated Database object. Second, to make sure that nothing can create new instances of the class, declare the constructor to be private. The instance() method should contain a static variable that holds a reference to an instantiated Database object. The first time the instance() method is called, this variable will be null. Subsequent calls should return only the object created during the first invocation.
private function __construct($dsn = null) { global $cfg; if($dsn == null) $dsn = $cfg['db']['dsn']; println("DSN: $dsn"); $this->conn = DB::connect($dsn); if(DB::isError($this->conn)) { //We're not connected. Throw an exception throw new Exception($this->conn->getMessage(), $this->conn->getCode()); } //Always fetch data as an associative array $this->conn->setFetchMode(DB_FETCHMODE_ASSOC); } static public function instance() { static $objDB; if(! isset($objDB)) { $objDB = new Database(); } return $objDB; }
You changed the constructor to be private. Any attempts to run the line $db = new Database() will now cause a fatal error. To get an instance of a Database object, call $db = Database::instance().
| Remember: | Static function variables retain their value between invocations of that function. |
The following code provides a good example of how to use the completed Database class.
<?php require_once('class.Database.php'); try { $db = Database::instance(); } catch (Exception $e) { // No point continuing... die("Unable to connect to the database."); } $sql = "SELECT count(1) FROM mytable"; $count = $db->getOne($sql); print "There are $count records in mytable!<br>\n"; // start a transaction $db->startTransaction(); // do an insert and an update try { $arValues = array(); $arValues['id'] = '#id#'; $arValues['myval'] = 'blah blah blah'; $newID = $db->insert('mytable', $arValues); print "The new record has the ID $newID<br>\n"; // update the record we just created $arUpdate = array(); $arUpdate['myval'] = 'foobar baz!'; $affected = $db->update('mytable', $arUpdate, "id = $newID"); print "Updated $affected records<br>\n"; // write the changes to the database $db->commit(); } catch (Exception $e) { // some sort of error happened - abort the transaction // and print the error message $db->abort(); print "An error occurred.<br>\n" . $e->getMessage(); } ?>
Be sure to read this example carefully to understand how everything works. Don't forget to wrap your code in try...catch blocks to make sure that any connection or query problems that might arise are caught and handled gracefully.