To connect with a specific database, a singleton class is built that includes code to obtain the credentials needed to connect to the database. It is a subclass of aliroExtendedDatabase. For example, here is the class for the Aliro general database, stripped of inessentials:

class aliroDatabase extends aliroExtendedDatabase {
protected static $instance = null;
protected function __construct () {
$credentials = aliroCore::getConfigData('credentials.php');
parent::__construct ($credentials['dbhost'], $credentials['dbusername'], $credentials['dbpassword'], $credentials['dbname'], $credentials['dbprefix']);
if (aliro::getInstance()->installed) aliroCore::set('dbprefix', $credentials['dbprefix']);
}
public static function getInstance () {
return is_object(self::$instance) ? self::$instance : (self::$instance = new self());
}

The actual database class aliroDatabase involves the usual singleton technique of providing a getInstance method, and the class is as much as anything a convenient way to make a particular database readily available throughout the CMS code. The constructor obtains the database details from the configuration handler class, aliroCore.

Some designers object that database access should not be provided in this global way on account of a general aversion to globals, but the alternative is unappealing. From an object point of view, the caller of a method may not have good reasons for knowing about the method's need to access a database, or to know which database it should be. Requiring every such object to be passed as a parameter is likely to make an API fragile, and decidedly cumbersome. It is not correct, as sometimes stated, to say that the singleton technique prevents the returned object from being of a different class, either for testing or on account of a change in design. The balance of advantage seems to me in favor of singletons.

As websites become more complex, there is often a need for multiple software systems to share a database. A common mechanism for ensuring that this does not cause table name clashes is to prefix every table name with a standard prefix for a particular software system. Aliro continues with the convention established by Mambo and Joomla! that SQL should include table names prefixed by #__.

For example, a table used by Aliro is referred to as #__session_data. Someone implementing Aliro can choose anything as the prefix, but if the default is accepted, the prefix will be Aliro. The actual name of the example table in the database will therefore be aliro_session_data, but in the PHP code of Aliro, it is always referred to as #__session_data.

Aliro does not demand that table names are written in this form, but it is a recommended practice. Not following it is likely to create problems for some users. All sample code in this book follows the convention.

Given the prefixing convention, almost all methods that accept SQL assume that the SQL needs to be translated so that any table name beginning with #__ is changed to the proper prefix.

Translation code is not shown here. It is simple in concept, but the fact that #__ may occur within quoted strings complicates matters. Within strings, #__ should not be translated. The text in quotes may, for example, be a message in a forum discussing code, and it is thus completely wrong for any translation to take place. This issue makes the translation code quite tedious. Like all of Aliro, it is readily available for download.

The simplest method provided for invoking SQL operations is setQuery. It does nothing more than translate any table name prefix codes, and then store the SQL as follows:

// Replaces # by the chosen database prefix and saves the query public function setQuery( $sql, $cached=false, $prefix='# ' )
{
$this->_sql = $this->replacePrefix($sql, $prefix);
$this->_cached = $cached;
}

Note that the last parameter allows for the possibility of using an alternative code instead of #__ to indicate the prefix when writing SQL. This might well be an unnecessary complication, but has been retained for compatibility with Aliro's predecessor systems, discussed earlier in Chapter 1, CMS Architecture. Although there is provision for specifying that the result of the query should be cached, at the time of writing, this particular form of cache is not implemented in Aliro. Reasons for this are discussed in Chapter 6 on caches.

For debugging, it is very useful to be able to get the SQL back again in its translated form. The result can be displayed, and a developer can feed it directly into the database system to check out what happens.

// Returns stored SQL with replacements, ready to display public function getQuery ($sql='')
{
if ($sql == '') $sql = $this->_sql;
return "<pre>" . htmlspecialchars( $sql ) . "</pre>";
}

Having stored a SQL statement, the method query makes a call to the database to execute the SQL. The result is a cursor that is linked to the result set, but the cursor is kept internal to the database class and the various result retrieval methods are used to obtain information about the effects of the SQL. The query method's code is not of particular interest. Later, methods are described that avoid the need to use the basic setQuery, and query methods.

The simplest way to handle the results from a query is to put the whole result set into a suitable PHP variable for processing, especially as memory is now available in generous quantities. This assumes that care is being taken to retrieve only the data needed for a particular display, such as a single page of items. Different kinds of queries can be handled in different ways. To make implementation efficient, the various data access methods all rely on a single internal general purpose method:

public function retrieveResults ($key='', $max=0, $result_type='row') {
$results = array();
if (!in_array($result_type, array ('row', 'object', 'assoc'))) {
$this->_errorMsg = sprintf($this->T_('Unexpected result type of %s in call to database'), $result_type).” SQL=$sql”;
throw new databaseException ($this->DBname, $this->_errorMsg, $this->_sql, $this->_errorNum, aliroBase::trace());
}
$sql_function = $this->interface->getFetchFunc().$result_type;
$cur = $this->query();
if ($cur) {
while ($row = $sql_function($cur)) {
if ($key != '') $results[(is_array($row) ? $row[$key] : $row->$key)] = $row;
else $results[] = $row;
if ($max AND count($results) >= $max) break;
}
$this->interface->freeResultSet($cur);
}
return $results;
}

If the name of a key is specified, then the results are provided as an array with the key values as the array subscripts. Otherwise, the results are a simple array with numeric subscripts. The maximum number of results can be specified, and there are three options for the kind of result set: row, object, or assoc corresponding to the available PHP interface functions. The base method is only intended for use within the database classes, and is used by a series of public methods. It has to be public because it is there in more than one class in the database class framework. The simplest method intended for general use is loadResult:

public function loadResult()
{
$results = $this->retrieveResults(‘’, 1, ‘row’);
if (count($results)) return $results[0][0];
else return null;
}

It uses the method retrieveResults to obtain a single database row as an array, and then returns the first value of that first row. Typically, it is used with SQL that is expected to yield only a single value. When the SQL is expected to yield a number of rows, but only a single value is wanted, the loadResultArray method is suitable:

public function loadResultArray($numinarray = 0)
{
$results = $this->retrieveResults(‘’, 0, ‘row’);
foreach ($results as $result) $values[] = $result[$numinarray];
return isset($values) ? $values : null;
}

The return of null when there are no results is often an unhelpful choice. A zero-sized array would usually be much more convenient. Null is returned only to maintain backwards compatibility. By default, the first data value is returned, although it is possible by giving a value to the parameter $numinarray to specify a different data value. However, in most cases, the SQL is written to return a set of rows, each of which has only one value.

Most common is the case where a set of database rows is needed. This can be handled by the loadObjectList method:

public function loadObjectList( $key=’’ )
{
$results = $this->retrieveResults($key, 0, ‘object’);
return count($results) ? $results : null;
}

The method retrieveResults does pretty much all of the work here again! The extra test for zero result items is, as shown, somewhat perverse but retained for compatibility. Apart from this case, what is returned is an array of objects of the rudimentary stdClass provided by PHP. By default, the array has numeric subscripts in whatever order was specified by the SQL, if any. If a key name is specified, then the key values from the rows will be the array subscripts.

Ironically, it is harder to implement the loading of a single object. This is because it is done in a more powerful way. The code for the loadObject method is:

public function loadObject(&$object=null) {
if (!is_object($object)) $results = $this->retrieveResults(‘’, 1, ‘object’);
else $results = $this->retrieveResults(‘’, 1, ‘assoc’);
if (0 == count($results)) return false;
if (!is_object($object)) $object = $results[0];
else {
if ($object instanceof aliroDBGeneralRow) $object->bind($results[0], ‘’, false);
else foreach (get_object_vars($object) as $k => $v) {
if ($k[0] != ‘_’ AND isset($results[0][$k])) $object->$k = $results[0][$k];
}
}
return true;
}

If the parameter is an existing object, then the data from the first row returned by the SQL is loaded into that object. If the given object is not subclassed from aliroDBGeneralRow (this would include objects subclassed from either mosDBTable or aliroDatabaseRow) the loading is done on the basis of matching field names from the database to property names in the object. Any property that begins with underscore is assumed to be internal, and is therefore ignored.

Wherever the given object is subclassed from aliroDBGeneralRow, then the filling of the object with data can be done using the bind method. It is described in a later section on data objects. Instead of driving the data transfer using the properties of the object, it is driven by the fields of the table that relates to the object. The mechanism will soon be explained more fully.

In the case where the parameter is not already an object, then the parameter becomes a PHP stdClass object, and takes its values from the first row retrieved by the SQL.

Two methods are provided to further ease the handling of SQL. The first is doSQL, which simply combines setQuery and query into a single call. It is particularly suitable for INSERT, UPDATE, or DELETE operations that do not return any results. The more powerful method is doSQLget. It accepts SQL as its parameter, and returns an array of objects that are the rows resulting from the SQL:

public function doSQLget ($sql, $classname=’stdClass’, $key=’’, $max=0) {
$this->database->setQuery($sql);
$rows = $this->retrieveResults ($key, $max, ‘object’);
if (‘stdClass’ == $classname) return $rows;
foreach ($rows as $sub=>$row) {
$next = new $classname();
foreach (get_object_vars($row) as $field=>$value) $next->$field = $value;
$result[$sub] = $next;
}
unset($rows);
return isset($result) ? $result : array();
}

Although this method will by default return objects belonging to stdClass, just like loadObjectList, it also supports the option of specifying a class for the results. This makes it a good deal more powerful, since the specified class will typically have methods, not merely the set of properties that are found in a stdClass object. Other optional parameters allow for the results array to be keyed by a field from the table, and for the number of results to be restricted.

The Aliro database class includes methods to make update or insertion easy, provided there is an object in existence that contains all the desired values. The methods are insertObject and updateObject. The code can be found in the full code, which is included in the download for this book.

For some database operations, there is valuable information available afterwards. The previous section illustrated the use of the insertid method to find out the value allocated to an auto-increment primary key. Other useful methods are getNumRows, and getAffectedRows.

When the SQL operation produces a result set, calling the method getNumRows will return, as you might expect, the number of rows that were returned from the query. The getAffectedRows method is relevant where there is an update or deletion. It is important to take care when relying on the number of affected rows for an update. The answer given is not the number of rows that were identified by whatever WHERE clause is included in the SQL, the answer is the number of rows actually made different. Thus, if the UPDATE part of the SQL does not cause a change for a particular row, that row will not be counted as being affected.

Both getNumRows and getAffectedRows use directly corresponding PHP functions for access to MySQL. The reason for providing them is to offer a comprehensive interface, and to be prepared for greater database independence in the future.

First, we can exploit the rich metadata that is automatically available with a relational database. We can find out what tables are available in the database. The constructor in the abstract database class first tries to get the information from cache. If that fails, the constructor gets the information from the database, using the getTableInfo method:

protected function getTableInfo () {
if (count($this->DBInfo->DBTables) == 0) {
$this->database->setQuery (“SHOW TABLES”);
$results = $this->database->loadResultArray();
if ($results) foreach ($results as $result) $this->DBInfo- >DBTables[] = $this->restoreOnePrefix($result);
$this->saveCache();
}
}

Our basic functions always assume that SQL is provided using table names that start with #__. This group of characters is automatically changed to the chosen prefix for the site. Because of this, it is convenient for callers to always provide table names for database methods in this form. That way, there is no need for a program to find out the actual prefix, since the standard coding can be used instead. To achieve this situation, the information taken directly from the database is translated by method restoreOnePrefix, and the actual site prefix is changed to #__. The data obtained adds to the collection of information of continuing value, so the cache is updated.

With the benefit of a stored list of the tables from the database, it is easy to implement a method to find out if a particular table is present:

public function tableExists ($tablename)
{
return in_array($tablename, $this->DBInfo->DBTables);
}

Again, it is worth noting that because of the processing of the table names just described, the caller can specify the table name using #__ rather than needing to find out what prefix is in use for the system.

To find out more about the data in our database, the following pair of methods can be implemented:

protected function storeFields ($tablename) {
if ($this->tableExists($tablename) AND !isset($this->DBInfo->DBFields[$tablename])) {
$this->DBInfo->DBFields[$tablename] = $this->doSQLget(“SHOW FIELDS FROM `$tablename`”);
$this->DBInfo->DBFieldsByName[$tablename] = array();
foreach ($this->DBInfo->DBFields[$tablename] as $field) $this->DBInfo->DBFieldsByName[$tablename][$field->Field] = $field;
$this->saveCache();
}
}
public function getAllFieldInfo ($tablename) {
$this->storeFields($tablename);
return isset($this->DBInfo->DBFields[$tablename]) ? $this->DBInfo->DBFields[$tablename] : array();
}

The private method storeFields does the hard work, although use of cache will often mean that the hard work isn't hard at all. The method only accesses the database if the fields of the table specified by the parameter are not already known. If they are, there is nothing to do. But if they are not, the general purpose retrieval method doSQLget is used to obtain a set of objects, each of which fully describes a field in the table.

Each of these arrays of objects is stored as an element in a larger array, keyed by the name of the table.

Although it is possible to search a list of field objects, often it is more convenient to access field information directly using the name of a field. So the data is reorganized into another array of objects, this time keyed on the name of the field $field->Field. Just in case the table name given has no fields (perhaps it doesn't exist) the new information is set to a null array to make sure it is never left totally unset.

Then everything is stored into a cache so the same processing will not be needed the next time, at least up to the time when the cached data expires.

With the aid of the method storeFields, the public method getAllFieldInfo is easy to implement. The answer is simply returned from the database object's data, held in $this->DBInfo->DBFields.

Often, we don't need all of the information about the fields in a table, but just the names of the fields.

public function getAllFieldNames ($tablename) {
$this->storeFields($tablename);
return isset($this->DBInfo->DBFieldsByName[$tablename]) ? array_keys($this->DBInfo->DBFieldsByName[$tablename]) : array();
}

Once again, storeFields can be relied on to do the hard work, if any is needed. Since that method stored the field data, keyed by the name of the fields, all that is needed to obtain an array of field names is to apply the PHP array_keys function to the stored information. Wherever possible, it is good to use PHP functions to avoid writing code. The fact that functions are implemented as compiled code means that even quite complex operations can be carried out more quickly than executing a number of PHP statements.

Or, it may be that we want all the field metadata for one particular field, and so another useful method is:

public function getFieldInfo ($tablename, $fieldname) {
$this->storeFields($tablename);
return isset($this->DBInfo->DBFieldsByName[$tablename][$fieldname]) ? $this->DBInfo->DBFieldsByName[$tablename][$fieldname] : null;
}

This checks that the requested field description object actually exists, and if so, returns it. Otherwise it returns null. The caller should make appropriate checks.

However carefully software is designed, it frequently changes during its lifetime. That may include changes to the database tables that are used. Assuming a situation where updates to the CMS framework or to the extensions are to be achieved through a simple installation procedure, it is likely that database amendments will be made through PHP code. After all, the idea of a CMS involves the likelihood of implementation on multiple sites, and a manual upgrade procedure would be tiresome. With the help of the methods already shown, we can make the upgrade task a lot easier.

public function addFieldIfMissing ($tablename, $fieldname, $fieldspec, $alterIfPresent=false) {
if (in_array($fieldname, $this->getAllFieldNames($tablename))) {
if ($alterIfPresent) return $this->alterField($tablename, $fieldname, $fieldspec);
return false;
}
if ($this->tableExists($tablename)) {
$this->doSQL(“ALTER TABLE `$tablename` ADD `$fieldname` “.$fieldspec);
$this->clearCache();
}
return true;
}

One problem with changing database tables is that it is not always easy to be sure that the upgrade operation will not run more than once. In fact, it is often easier to write the upgrade so that it can be run many times than to figure out how to make it run only once.

The addFieldIfMissing method will add an extra field to a particular table using the information provided in $fieldspec. If the field already exists in the table, then nothing is done, unless the optional $alterIfPresent parameter is true, in which case the data specification is changed using $fieldspec. Otherwise, the field is added using the general purpose method doSQL. Bearing in mind our reliance on cache, it is important to clear the cache after any change to the database, otherwise the change will not take effect until the cache expires. Delay is likely to cause errors, so cannot be allowed.

Aliro also includes a similar method alterField, which takes the same first three parameters, but works on an existing field. Its use is seen in the last code snippet.

With an example to hand, let us start looking at the abstract class that is the basis for data objects. With some minor detail stripped out, it begins as follows:

abstract class aliroDBGeneralRow
{
public function getDatabase()
{
return call_user_func(array($this->DBclass, ‘getInstance’));
}
public function getNumRows( $cur=null )
{
return $this->getDatabase()->getNumRows($cur);
}
public function getAffectedRows ()
{
return $this->getDatabase()->getAffectedRows();
}
public function insert ()
{
return $this->getDatabase()->insertObject($this->tableName,
$this, $this->rowKey);
}
public function update ($updateNulls=true)
{
return $this->getDatabase()->updateObject($this->tableName,
$this, $this->rowKey, $updateNulls);
}

The getDatabase method is used extensively throughout the database object class, and relies on the singleton subclass setting a value for the name of the database class. It could almost be a protected method, but in a small number of cases, the database is needed outside the data object classes, mainly for backwards compatibility.

The remaining methods shown simply refer to the database class for the actual processing. This makes sense, though, because it is usually better to present a comprehensive set of behaviors in a class rather than require developers to grasp the inter-dependencies of various objects more than is necessary.

Now, we find that the first method that does anything substantial is load, the method provided for getting data from the database into a bare object.

public function load( $key=null )
{
$k = $this->rowKey;
if (null !== $key) $this->$k = $key;
if (empty($this->$k)) return false;
$this->getDatabase()->setQuery(“SELECT * FROM $this->tableName
WHERE $this->rowKey=’{$this->$k}’” );
return $this->getDatabase()->loadObject($this);
}

This is typically used when a data object has just been created and the key value has been set, perhaps from user input. The method can be used in either of two ways: the key value can be preset in the object, or it can be passed as a parameter to the load method. The name of the key field is placed into a simple variable so that it can be used as a property of the data object. If a key value has been passed, it is assigned into the key field of the object. At this point, the key field must have a value or any attempt at database retrieval is bound to fail. An SQL statement is constructed using the table name that was set in the derived class, and the result returned.

A specific database object class such as aliroComponent could override the load method, for example where building an object with data requires more than just taking data from a database table row.

With a load method now available, the next logical step is a store method, and this draws on the data object handling methods that are actually implemented in the database class:

public function store( $updateNulls=false )
{
$k = $this->rowKey;
$ret = $this->$k ? $this->update($updateNulls) : $this->insert();
if (!$ret) $this->_error = strtolower(get_class( $this )). “::store failed <br />” .
$this->getDatabase()->getErrorMsg();
return $ret;
}

An option is provided as to whether any fields in the data object that are null should affect the data in the database. By default, any null values in the data object are simply ignored. The choice between insert and update is made on the assumption, mentioned earlier, of a single auto-increment key. A new database row will not have a value set for the key field, since it is always allocated by the database. Contrarily, a data object with a key field value must relate to an existing row, and is therefore an update.

Following similar lines to methods already shown, it is easy to implement a delete method:

function delete( $key=null )
{
$k = $this->rowKey;
if ($key) $this->$k = intval( $key );
$this->getDatabase()->doSQL( “DELETE FROM $this->tableName WHERE
$this->rowKey = ‘”.$this->$k.”’” );
return true;
}

Often, data objects need to be filled with data from user input, which typically exists in one of the PHP super-globals, such as $_POST. A group of methods is provided to easily handle this need:

public function bind( $objectorarray, $ignore=’’, $strip=true ) {
$fields = $this->getDatabase()->getAllFieldNames ($this->tableName);
foreach ($fields as $key=>$field) if (false !== strpos($ignore, $field)) unset($fields[$key]);
return $this->bindDoWork ($objectorarray, $fields, $strip);
}
public function bindOnly ($objectorarray, $accept=’’, $strip=true) {
$fields = $this->getDatabase()->getAllFieldNames ($this->tableName);
foreach ($fields as $key=>$field) if (false === strpos($accept, $field)) unset($fields[$key]);
return $this->bindDoWork ($objectorarray, $fields, $strip);
}
private function bindDoWork ($objectorarray, $fields, $strip) {
if (is_array($objectorarray) OR is_object($objectorarray)) {
foreach ($fields as $field) {
$data = is_array($objectorarray) ? @$objectorarray[$field] : @$objectorarray->$field;
if (is_string($data)) {
$this->$field = $strip ? $this->stripMagicQuotes($data) : $data;
if (‘params’ != $field AND (false !== strpos($this->$field, ‘&’) OR false !== strpos($this->$field, ‘<’))) {
$this->$field = $this->doPurify($this->$field);
}
}
}
return true;
}
$this->_error = strtolower(get_class($this)).$this->T_(‘::bind failed, parameter not an array’);
return false;
}

The basic bind method accepts an array of values, such as $_POST. Optionally, it accepts a comma-separated list of field names to be ignored. The processing is driven by the field names obtained from the database for the relevant table, less any that appear in the ignore list. Actual work is done by internal method bindDoWork. It first checks whether an array has really been passed.

The option to strip slashes out of the data is provided for backwards compatibility. Slashes typically appear in data as a result of the PHP option called magic quotes. As quotes in strings can cause problems, especially in database operations, PHP has the option to automatically put backslashes in front of all quotes in GPC input. GPC stands for get, put, and cookies. Many developers feel that the magic quotes feature creates worse problems than the one it sets out to solve. Aliro prefers to run without magic quotes, and extensions built using Aliro base classes will always remove any magic quotes early in the processing. For output to the browser, backslashes are not wanted and are effectively a corruption. The main requirement for backslashes is to escape quotes in SQL statements, but this is better handled using the database methods provided for the purpose. The magic quotes feature is removed from PHP in version 6.

An alternative bindOnly method is provided. It is exactly the same as bind except that the comma-separated list defines the field names to be processed instead of those to be excluded.

Building a content management system often leads to a need to put information in a particular sequence. Some of the code to do this is uninteresting, but it is worth discussing the resequencing method that sets new ordering values for a set of database rows. This method may be about as efficient as it is possible to get for an operation that is alien to relational databases, where ordering of rows is assumed to arise naturally out of data that can be sorted. The method used here always requires only two database operations, one read and one write, to achieve the resequencing:

public function updateOrder ($where=’’, $sequence=’’, $orders=array()) {
if ($this->lacks(‘ordering’)) return false;
$sql = “SELECT $this->rowKey, ordering FROM $this->tableName”
.($where ? “\n WHERE $where” : ‘’)
.”\n ORDER BY ordering”
.($sequence ? ‘,’.$sequence : ‘’);
$rows = $this->getDatabase()->doSQLget($sql, ‘stdClass’, $this->rowKey);
$allrows = array();
foreach ($rows as $key=>$row) $allrows[(isset($orders[$key]) ? $orders[$key] : $row->ordering)] = $key;
ksort($allrows);
$cases = ‘’;
$order = 10;
foreach ($allrows as $ordering=>$id) {
if ($order != $rows[$id]->ordering) $cases .= “ WHEN $this->rowKey = $id THEN $order “;
$order += 10;
}
if ($cases) $this->getDatabase()->doSQL(“UPDATE $this->tableName SET ordering = CASE “.$cases.’ ELSE ordering END’);
return true;
}

The optional parameter $where is a SQL condition that will identify a subset of rows from the table that are to be sequenced. If it is not provided, then the whole table will be resequenced. The rows must include a field called ordering that defines the sequence. There can be exceptional cases where additional sequencing fields are to be considered, so a supplementary ordering specification can be included in the $sequence parameter.

The $orders parameter allows for rows to be given a different ordering from the one presently found in the database. If present, it must be an array whose subscripts are ID numbers for database rows, and whose values are to be assigned to the ordering field in the identified row. Such an array might well be derived from user input.

Getting existing data from the database is the first task, and all we need is the ID and ordering for each relevant row. Rows are returned as an array with the row ID as the subscript. Next an array is constructed out of the database row information, ready for sorting. The subscripts for the $allrows array are either the value of ordering taken from the database, or if it matches, a value taken from the $orders parameter. Each value in $allrows is the row ID. The array can then be sorted on the keys, which are the desired ordering values.

Armed with a sorted array, it is possible to compute new ordering values, starting at 10, and incrementing by 10. For any row where the existing ordering does not match the desired ordering, part of an SQL CASE statement is constructed. If any SQL was generated, it is combined into a full SQL UPDATE statement incorporating a CASE statement to set all the values in a single SQL operation. The only time no SQL will be generated is when the ordering values are already correct.

Note that if a very large number of items are sequenced, the write SQL will become lengthy, and may become an excessive overhead. Although it takes an extremely long SQL statement to break the absolute limits on length imposed by the database system.