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); 



    Sams Teach Yourself PHP in 10 Minutes
    Sams Teach Yourself PHP in 10 Minutes
    ISBN: 0672327627
    EAN: 2147483647
    Year: 2005
    Pages: 151
    Authors: Chris Newman

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net