Database Portability Issues

By using database abstraction, you can write database-driven code that should be able to work with a multitude of back ends, simply by changing the DSN used to connect to the database.

However, not all database systems are the same, so you need to consider the design of database tables and SQL statements in order to make sure that your code is as widely supported as possible.

The most important consideration is to make sure that your SQL is written for the lowest common subset of the SQL language available to all the database back ends you want to be compatible with. For example, SQL that contains subqueries will not work with MySQL 4.0 or earlier. Similarly, you should avoid SQL commands that are specific to certain database systems, such as LIMIT or CREATE SEQUENCE.

Portability Modes

The DB class includes some portability mode settings that can ease the transition from one database back end to another. These modes are indicated by a series of constants, shown in Table 20.3, that you can set by using the setOption method with the required options, combined with a logical OR operator. The following statement shows an example:

 $db->setOption('portability',      DB_PORTABILITY_ERRORS | DB_PORTABILITY_NUMROWS); 

Table 20.3. Portability Mode Constants

Constant	Mode
DB_PORTABILITY_ALL	Turns on all portability features
DB_PORTABILITY_NONE	Turns off all portability features
DB_PORTABILITY_DELETE_COUNT	Forces a count to take place in a DELETE statement with no WHERE clause, with WHERE 1=1 appended to the statement
DB_PORTABILITY_ERRORS	Increases consistency of error reporting between different database systems
DB_PORTABILITY_LOWERCASE	Forces conversion of names of tables and columns to lowercase
DB_PORTABILITY_NULL_TO_EMPTY	Converts fetched NULL values to empty strings; some databases do not distinguish these
DB_PORTABILITY_NUMROWS	Enables the numRows method to work correctly in Oracle
DB_PORTABILITY_RTRIM	Forces trailing whitespace to be trimmed from fetched data

Working with Quotes

You can use the DB method quoteSmart to enclose a value in quotation marks so that it can be safely inserted into a column. String values are enclosed in quotes, and any characters that need to be delimited are automatically taken care of.

The following example builds a SQL statement by using quoteSmart to ensure that the apostrophe in the string does not interfere:

 $sql    = "INSERT INTO phrases (phrase) " .           "VALUES ( " . $db->quoteSmart($text) . " )"; 

The following is the value of $sql when the previous statement is executed, using the MySQL driver:

 INSERT INTO phrases (phrase)  VALUES ( 'Let\'s get ready to rumble' ) 

The output and the delimiting rules used depend on the database you are connected to.

Sequences

The way sequences are implemented in different database engines varies considerably. In MySQL, for instance, you use the AUTO_INCREMENT attribute on a table column, and in SQL Server it is called an IDENTITY field. In Oracle you use CREATE SEQUENCE to create a database object that tracks the sequence value independently of any table.

The DB class uses its own set of functions to manage sequences so that using any kind of auto-incrementing field does not tie your code to one particular database back end.

Sequences If your back-end database supports CREATE SEQUENCE, that functionality will be used. Otherwise, the DB class emulates the sequence by using a table that holds the sequence value, and it performs an increment each time the sequence is accessed.

To create a new sequence, you use the createSequence method on a database object, with a unique sequence identifier. After the sequence has been created, the nextId method can be called with that identifier to return the next sequential value.

The following example creates a sequence called order_number and displays the first sequence value:

 $db->createSequence("order_number"); echo $db->nextId("order_number"); 

Subsequent calls to nextId for this sequence return incremental values.

To drop a sequence when you no longer have a use for it, you call the dropSequence method.

Query Limits

MySQL implements the LIMIT keyword in SQL statements, which you can use to restrict the number of rows returned by a query. This is nonstandard SQL, and other database systems do not include this feature.

The DB class includes the limitQuery method, which you can use to emulate the LIMIT clause in a SQL statement for maximum compatibility. This method is called in the same way as a query, but it takes two additional arguments: to specify the starting row and number of rows to be returned.

The following example returns five rows from the query's data set, beginning at row 11 (where row numbering begins at zero):

 $res = $db->limitQuery("SELECT * FROM mytable", 10, 5);