Tony Marston's Blog About software development, PHP and OOP

Creating an Oracle driver for the Radicore Development Infrastructure

Posted on 15th January 2007 by Tony Marston

Amended on 1st November 2015

Introduction
Administrative programs
Connecting to a Database
- CONFIG.INC
- connect() method
CREATE TABLE syntax
- Number Formats
- Date Formats
- Time Formats
Loading data
- CREATE TABLE scripts
- INSERT data scripts
SQL queries
- CONCAT() function
- FROM table AS alias
- backslashes in WHERE
- escaping single quotes in data
- LIMIT and OFFSET (pagination)
- INSERT syntax
- AUTO_INCREMENT
- CLOB and BLOB columns
- VARRAY datatype
- GROUP BY clause
- HAVING clause
- Multi-table deletes
Conclusion
Amendment History
Comments

Introduction

The software that I write is not intended for just one customer who is locked into a single RDBMS. Instead it can be used by virtually any customer using the RDBMS of their choice. How is this possible? I deliberately designed and built my infrastructure around the 3 Tier Architecture which breaks down application code into three distinct areas of responsibility:

It is only the Data Access layer which communicates with the database, therefore it should be possible to communicate with another database simply by changing the component which exists within this layer. I have already produced the following data access classes:

The purpose of this article is to document my experiences while producing a DAO for a totally different RDBMS, in this case for Oracle Database 10g Express Edition (XE).

Administrative programs

Although both MySQL and Oracle come with command line interfaces I think that as we are now in the 21st century the vast majority of developers would insist on a set of proper GUI tools. I know I do.

MySQL has the following:

Oracle has the following:

These are all freeware, although there are also commercial products available for both databases.

I did try to use the web-based administration tool that came with XE, but I found it too slow, too clunky, and too limited. I got so frustrated with it that I ditched it in favour of TOAD which I have used on past projects, but as I was using the freeware version I got annoyed with the features that I wanted to use which are only available in the commercial version. I later found out about Oracle SQL Developer which is a freeware product from Oracle, and I have been using it ever since.

Connecting to a Database

MySQL has tables within databases, PostgreSQL has tables within schemas within databases, and Oracle has tables within schemas/users within databases. Each schema within Oracle belongs to a user with the same name, so to create a new schema you must actually create a new user. This mechanism allows the same table name to exist within different schemas within the same database.

With MySQL once you have established a connection to a server instance you may access any table within any database. One database is nominated as the "default" or "current" database by using the mysqli_select_db function, in which case any table names within that database do not have to be qualified. Tables in other databases can only be accessed by qualifying them with the database name, as in dbname.tablename.

With Oracle a connection is limited to a single database, and you may only access tables that belong to schemas within that particular database. You can make a particular schema "current" by issuing the following SQL query:

ALTER SESSION SET CURRENT_SCHEMA="<schema>"

Tables in other schemas can only be accessed by qualifying them with the schema name, as in schema.tablename.

I personally like to create a separate database for each of my software subsystems as it makes backups and restores that much easier, so the ability to swap a MySQL "database" for an Oracle "schema" is very convenient.

In order to make my code access database tables through the Oracle OCI8 interface I made the following changes to my code:

1. CONFIG.INC (DB.INC in the small sample application)

This is a small include file which exists outside the web root, and which contains the details necessary to connect to the database. It has been changed from this:

<?php 
$dbms       = 'mysql';
$dbhost     = 'localhost';
$dbusername = '****';
$dbuserpass = '****';
?> 
to this:
<?php 
$dbms       = 'oracle'; 
$dbhost     = '//localhost/xe';
$dbusername = '****';
$dbuserpass = '****';
?> 

The global variable $dbms identifies which class file to access before creating the Data Access Object that will be responsible for all communication with the database. This is documented in my FAQ.

The global variable $dbhost identifies the database name to be used in the oci_connect function.

2. Database Class (dml.<engine>.class.inc)

Each database class has a function which connects to the relevant server. The code in the MySQL class is as follows:

    function connect ($dbname=null)
    // establish a connection to the database
    {
        global $dbhost, $dbusername, $dbuserpass;
        
        $this->errors = array();
        $this->dbname = $dbname;
        
        static $dbconn;
        
        if (!$dbconn) {
            $dbconn = mysqli_connect($dbhost, $dbusername, $dbuserpass);
            $this->dbconnect = $dbconn;
        } // if
        
        if (!$dbconn) {
            return FALSE;
        } elseif ($dbname) {
            if (!mysqli_select_db($dbconn, $dbname)) {
                return FALSE;
            } // if
        } // if
        
        return TRUE;
   
    } // connect

The code in the Oracle class is as follows:

    function connect ($schema=null)
    // establish a connection to the database
    {
        global $dbhost, $dbusername, $dbuserpass;
        
        $this->errors = array();
        
        static $dbconn;
        
        if (!$dbconn) {
            $dbconn = oci_connect($dbusername, $dbuserpass, $dbhost, 'UTF-8');
            if ($dbconn) {
                // change format for DATE datatype
                $this->query = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";
                $this->stmt  = oci_parse($dbconn, $this->query);
                $result = oci_execute($this->stmt) or trigger_error('SQL', E_USER_ERROR);
                // change format for TIMESTAMP datatype
                $this->query = "ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";
                $this->stmt  = oci_parse($dbconn, $this->query);
                $result = oci_execute($this->stmt) or trigger_error('SQL', E_USER_ERROR);
            } // if
        } // if
        
        if (!$dbconn) {
            return FALSE;
        } // if
        
        if (!empty($schema)) {
            $this->query = 'ALTER SESSION SET CURRENT_SCHEMA = "' .strtoupper($schema) .'"';
            $this->stmt  = oci_parse($dbconn, $this->query);
            $result = oci_execute($this->stmt) or trigger_error('SQL', E_USER_ERROR);
        } // if
        
        $this->query     = '';
        $this->schema    = $schema;
        $this->dbconnect = $dbconn;
        return TRUE;
   
    } // connect

As you can see a schema in Oracle maps easily to a database in MySQL.

I also take this opportunity to alter the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT to switch from the Oracle default to the same format used in MySQL and PostgreSQL.

CREATE TABLE syntax

Although both MySQL and Oracle claim to be SQL compliant, there are some differences in the syntax of the CREATE TABLE command:

This means that the following in MySQL:

CREATE TABLE IF NOT EXISTS `tablename` (
  `field1` varchar(8) NOT NULL default '',
  `field2` tinyint(4) unsigned default NULL,
  `field3` smallint(3) unsigned default NULL,
  `field4` mediumint(8) unsigned default NULL,
  `field5` double default NULL,
  `field6` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`field1`),
  UNIQUE KEY `field2` (`field2`),
  KEY `field3` (`field3`)
);

will have to be changed to work in Oracle, as follows:

CREATE TABLE tablename (
  field1 varchar2(8) NOT NULL,
  field2 number(3),
  field3 number(5),
  field4 number(10),
  field5 number,
  field6 timestamp default '2000-01-01 00:00:00' NOT NULL ,
  PRIMARY KEY (field1)
);
CREATE UNIQUE INDEX tablename_idx1 ON tablename (field2);
CREATE INDEX tablename_idx2 ON tablename (field3);

Number formats

Although earlier versions of Oracle supported the INTEGER, DECIMAL and FLOAT data types, this version covers them all with the single NUMBER data type by specifying different combinations of precision and scale, as in the following:

number(p,s) If both precision (p) and scale (s) are supplied then this is treated as a number with (s) decimal places. Precision can be up to 39 or 40 (depending on the position of the decimal point) with scale ranging from -84 to +127.
number(p) If only precision (p) is supplied then this is treated as an integer (a whole number with no decimal places).
number If neither precision (p) and scale (s) are supplied then this is treated as a floating point number. These are stored with decimal precision. If binary precision is required then one of the alternative data types BINARY_FLOAT or BINARY_DOUBLE can be used instead.

When column details are imported into the data dictionary the column type is adjusted to FLOAT, DECIMAL or INTEGER depending on the values defined for precision and scale.

Date formats

In the Oracle database the DATE data type stores both date and time, and there are no separate data types for date only and time only. This is very inconvenient as when validating and displaying a DATE field it is nice to know whether it needs to include both DATE and TIME or only one of the two. For this reason the data dictionary will present a choice of DATE, TIME and DATETIME in a dropdown list so that a specific choice can be made.

Time formats

ORACLE does not have a native TIME format, so you have to choose from one of the following types:

Loading data

Separate scripts are provided to create the tables and load the data for each subsystem. These scripts can be found in the radicore/<subsystem>/sql/oracle/ directories and processed using the administration program of your choice.

CREATE TABLE scripts

Each <subsystem>-schema.sql script starts by creating the user and associated schema, as shown in the following example:

CREATE USER "DICT" IDENTIFIED BY "dict";
GRANT CONNECT, RESOURCE TO "DICT";
GRANT CREATE DATABASE LINK TO "DICT";
GRANT CREATE MATERIALIZED VIEW TO "DICT";
GRANT CREATE PROCEDURE TO "DICT";
GRANT CREATE PUBLIC SYNONYM TO "DICT";
GRANT CREATE ROLE TO "DICT";
GRANT CREATE SEQUENCE TO "DICT";
GRANT CREATE SYNONYM TO "DICT";
GRANT CREATE TABLE TO "DICT";
GRANT CREATE TRIGGER TO "DICT";
GRANT CREATE TYPE TO "DICT";
GRANT CREATE VIEW TO "DICT";

ALTER SESSION SET CURRENT_SCHEMA = "DICT";

This will be followed by the code which creates each table within that schema, then issues the relevant GRANT statements to make them publicly accessible, as shown in the following example:

CREATE TABLE dict_database (
  database_id varchar2(64) NOT NULL,
  database_desc varchar2(255) NOT NULL,
  db_comment clob,
  subsys_id varchar2(8) NOT NULL,
  created_date timestamp NOT NULL,
  created_user varchar2(16) default 'UNKNOWN' NOT NULL,
  revised_date timestamp,
  revised_user varchar2(16),
  PRIMARY KEY  (database_id)
);

REVOKE ALL ON dict_database FROM PUBLIC;
GRANT SELECT,INSERT,DELETE,UPDATE ON dict_database TO PUBLIC;

INSERT data scripts

Each <subsystem>-data.sql script starts by overriding the Oracle defaults and setting the current schema, as shown in the following example:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SET SCAN OFF;

ALTER SESSION SET CURRENT_SCHEMA = "DICT";

The SET CAN OFF statement is required so that the '&' (ampersand) symbol will be treated as data instead of a substitution character.

This will be followed by the code which loads records into each table, as shown in the following example:

INSERT INTO tablename (column1, column2, ...) VALUES ('value1', 'value2', ...);
INSERT INTO tablename (column1, column2, ...) VALUES ('value1', 'value2', ...);

SQL queries

As well as differences in the syntax for creating tables there is also a difference in the syntax of various SQL queries. These differences can be handled by code within the Oracle DAO.

CONCAT() function

The following in MySQL:

SELECT CONCAT(first_name, ' ', last_name) AS person_name FROM ....

Although Oracle does have a CONCAT() function, for some reason this will not accept more than two arguments, so it has to be converted to the following:

SELECT first_name || ' ' || last_name AS person_name FROM ....

The 'select' string is therefore examined before the query is issued to replace the CONCAT() function with '||'.

Note that because of a difference in the way that double quotes are handled the following will work in MySQL but will fail in Oracle:

SELECT CONCAT(first_name, " ", last_name) AS person_name FROM ....

This is because Oracle uses double quotes around identifiers (names of tables, columns or other database objects) when they contain non-standard characters or are case-sensitive. MySQL on the other hand uses the backtick (`) for this purpose, leaving strings to be delimited by either single or double quotes.

FROM table AS alias

Oracle does not like the keyword 'AS' to specify alias names in the FROM clause, so every occurrence of 'tablename AS alias' has to be replaced with 'tablename alias'.

Backslashes in WHERE

Some parts of the framework automatically escape the WHERE clause with backslashes, but as Oracle does not use backslash as its escape character this has to be altered before the query is issued. This means that all occurrence of \' (backslash+quote) have to be replaced with '' (quote+quote), and all occurrences of \<char> (backslash+char) have to have the backslash removed.

Escaping single quotes in data

In order to have a quote character inside a quoted string in an SQL query it is necessary to escape each quote with another quote. There is no function such as mysqli_real_escape_string() or pg_escape_string(), so it has to be done with code.

LIMIT and OFFSET (pagination)

Unlike MySQL and PostgreSQL there is no LIMIT and OFFSET which can be used for pagination, but the same effect can be achieved using a totally different mechanism.

In the Radicore framework there are two variables which are passed into the DAO from above:

If no pagination is required (i.e. when $rows_per_page is zero) then the query can be issued as normal. If pagination is required then the normal query string must be enclosed with the following:

select * from ( select a.*, rownum as rnum from (
    ... normal query goes here ...
) a where rownum <= $max_rows ) where rnum >= $min_rows

The calculation for $max_rows and $min_rows is as follows:

    if ($rows_per_page > 0) {
        // count the rows that satisfy this query
        $this->query = "SELECT count(*) FROM $from_str $where_str $group_str $having_str";
        $this->numrows = $this->getCount($schema, $tablename, $this->query);
        // calculate the total number of pages from this query
        $this->lastpage = ceil($this->numrows/$rows_per_page);
    } else {
        $this->lastpage = 1;
        $this->numrows  = null;
    } // if
    
    // ensure pageno is within range
    if ($pageno < 1) {
        $pageno = 1;
    } elseif ($pageno > $this->lastpage) {
        $pageno = $this->lastpage;
    } // if
    $this->pageno = $pageno;
    
    $this->query = "SELECT $select_str FROM $from_str $where_str $group_str $having_str $sort_str";
    if ($rows_per_page > 0) {
        // insert code for pagination
        $min_rows = (($pageno - 1) * $rows_per_page) +1;
        $max_rows = ($min_rows + $rows_per_page) -1;
        $this->query = 'select * from ( select a.*, rownum as rnum from ( '
                     . $this->query
                     . ") a where rownum <= $max_rows ) where rnum >= $min_rows";;
    } // if
    
    $this->stmt = oci_parse($this->dbconnect, $this->query);
    $result = oci_execute($this->stmt) or trigger_error('SQL', E_USER_ERROR);

INSERT syntax

In standard SQL there are two forms of the INSERT command:

INSERT INTO tablename VALUES ('value1','value2',...) 
INSERT INTO tablename (col1,col2,...) VALUES ('value1','value2',...)

What I like about MySQL is that it also allows UPDATE-style syntax, as follows:

UPDATE tablename SET col1='value1', col2='value2', ...
INSERT INTO tablename SET col1='value1', col2='value2', ...

I prefer this approach as it keeps column names and values together instead of in two separate lists. This seems such a common sense idea that I'm surprised it is not part of the standard already. Why allow 2 formats for INSERT and a completely different 3rd one for UPDATE?

Even though Oracle is not so accommodating, the fact that in my infrastructure all QUERY statements are constructed and issued within the relevant database driver means that I can customise each driver according to the whims of the individual database. The remainder of my infrastructure remains totally oblivious to the internal workings of any individual driver.

AUTO_INCREMENT

MySQL makes it very easy to use a technical key in any table. All the DBA need do is include the AUTO_INCREMENT keyword in the table definition, as in the following:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `desc` varchar(40) default NULL,
  PRIMARY KEY  (`id`)
);

There is no AUTO_INCREMENT keyword in Oracle, but the same effect can be achieved by using a database sequence. The first step is to create a sequence for the table which has a name in the format <tablename>_SEQ, then to grant access to other users.

CREATE TABLE audit_logon_errors (
  id number(10) NOT NULL,
  err_timestamp timestamp NOT NULL,
  ip_address varchar2(16) NOT NULL,
  user_id varchar2(16) NOT NULL,
  user_password varchar2(16) NOT NULL,
  PRIMARY KEY  (id)
);
CREATE SEQUENCE audit_logon_errors_seq;

REVOKE ALL ON audit_logon_errors FROM PUBLIC;
GRANT SELECT,INSERT,DELETE,UPDATE ON audit_logon_errors TO PUBLIC;
REVOKE ALL ON audit_logon_errors_seq FROM PUBLIC;
GRANT SELECT,ALTER ON audit_logon_errors_seq TO PUBLIC;

Note that unlike MySQL and PostgreSQL there is no way to explicitly state that a particular column is to be filled from a particular sequence. The Radicore framework gets round this during the dictionary import function by making the following tests:

IF the primary key is an integer
AND a sequence with the name '<tablename>_seq' exists
THEN set the AUTO_INCREMENT flag to TRUE

Note that the AUTO_INCREMENT flag exists in the data dictionary and is made available to the application when the table structure is exported. If this flag is detected during an INSERT operation then when the SQL query is constructed the value for the primary key field is specified as <tablename>_seq.nextval, as in the following example:

INSERT INTO tablename (col1,col2,...) VALUES (tablename_seq.nextval,'value2',...)

The last part of the operation is to obtain the sequence number that has just been allocated so that it can be passed back to the application. This can be done with the following code:

    if (!empty($auto_increment)) {
        $this->query = "SELECT $tablename" .'_seq.currval FROM DUAL';
        $this->stmt  = oci_parse($this->dbconnect, $this->query);
        $result = oci_execute($this->stmt) or trigger_error('SQL', E_USER_ERROR);
        $row = oci_fetch_array ($this->stmt, OCI_NUM);
        $fieldarray[$auto_increment] = $row[0];
    } // if

CLOB and BLOB columns

Most databases do not have any difficulty in dealing with extra large columns - both MySQL and PostgreSQL support the TEXT datatype which can be up to 4GB in length.

With both MySQL and PostgreSQL there is no difference in the SQL syntax between inserting/updating large columns and other columns, as shown in the following example:

INSERT INTO tablename (field1, field2, large_text_field) 
               VALUES ('....', '....', 'very long string.....') 

Although in Oracle the VARCHAR2 datatype can hold up to 4000 characters, anything larger requires either a CLOB (character large object) or a BLOB (binary large object). In order to use a LOB field the technique is much more complicated as it requires several distinct steps, as shown in the following:

  1. Have the contents of the CLOB field available in its own variable.
  2. Construct the SQL query along the lines of:
    INSERT INTO tablename (field1, field2, clob_field) 
                   VALUES ('....', '....', EMPTY_CLOB()) 
                   RETURNING clob_field INTO :clob_field
    
  3. Execute oci_parse().
  4. For each CLOB field (there may be more than one) execute the following:
  5. Execute oci_execute()
  6. For each CLOB field execute the following:

VARRAY datatype

Although a database column is only supposed to contain a single value, most modern databases allow a column to contain an array of values. MySQL has the SET datatype which can be defined as follows:

CREATE TABLE `x_person` (
  ....,
  `favourite_food` set('1','2','3','4','5','6','7','8','9','10') default NULL,
  ....,
  PRIMARY KEY  (`person_id`)
);

The values in this array are not displayed to the user, they are merely keys to another array which exists in the language_array.inc file. This is part of the Internationalisation feature which allows text to be displayed in various languages. Sample contents are shown below:

$array['favourite_food'] = array('1' => 'Eggs',
                                 '2' => 'Bacon',
                                 '3' => 'Chips',
                                 '4' => 'Beans',
                                 '5' => 'Sausages',
                                 '6' => 'Mushrooms',
                                 '7' => 'Tomatoes',
                                 '8' => 'Hash Browns',
                                 '9' => 'Toast',
                                 '10' => 'Fried Bread');

PostgreSQL has the ARRAY datatype, and the same functionality can be implemented in Oracle with the VARRAY datatype.

Unlike the MySQL and PostgreSQL implementations, the Oracle VARRAY datatype has to be defined separately as a User Defined Datatype (UDT) before it can be included in a table. The following code creates the t_fav_food datatype which can contain an array of up to 10 elements of NUMBER(2).

CREATE OR REPLACE TYPE t_fav_food IS VARRAY(10) OF NUMBER(2);

This user defined type can then be used as a column type in a table, as in the following example:

CREATE TABLE x_person (
  ...,
  favourite_food t_fav_food,
  ...,
  PRIMARY KEY  (person_id)
);

Inserting values into the table requires a slightly different technique depending on which database is being used. For example, when inserting/updating the column with the values 2,4 and 5 the following variations are necessary:

With MySQL it is as simple as putting the values into a comma-delimited string, as in the following:

INSERT INTO x_person (..., favourite_food, ...) VALUES ('...', '2,4,5', '...');

With PostgreSQL each element has to be enclosed in double quotes and the entire array surrounded by curly braces, as in the following:

INSERT INTO x_person (..., favourite_food, ...) VALUES ('...', '{"2", "4", "5"}', '...');

With Oracle each element must be enclosed in single quotes, and the entire array must be treated as a function argument, with the UDT name being the function name, as in the following:

INSERT INTO x_person (..., favourite_food, ...) VALUES ('...', t_fav_food('2','4','5'), '...');

Unfortunately there is a serious fault in PHP's Oracle extension (see bug #40186) as it is incapable of reading a table which contains a VARRAY column. The ociFetchInto() function fails with the following error:

ORA-00932: inconsistent datatypes: expected CHAR got ARRAY 

Due to this problem I have had to remove the 'favourite_food' column from the 'x_person' table in the 'xample' schema for the Oracle database. This is the ONLY part of my entire application which works in both MySQL and PostgreSQL, but fails to work in Oracle.

GROUP BY clause

If the GROUP BY clause is not empty then Oracle will throw an error if it does not contain every column that also appears in the SELECT list. This was correct according to the SQL standard of 1991, but was changed in the 1999 standard so that any column in the GROUP BY clause which is functionally dependent on another column in the GROUP BY clause can be excluded. Relational theory states that within a relation (table) a non-key field is functionally dependent on the key, so that if the SELECT list contains a mixture of key and non-key fields then only the key fields need to be specified in the GROUP BY clause. This is the way that MySQL works.

This means that before the SQL query for Oracle is constructed from the separate SELECT, FROM, WHERE, GROUP-BY, HAVING and ORDER-BY strings the GROUP-BY string has to be inspected, and if it is non-empty it must be modified to ensure that it contains every field in the SELECT string (except for fields defined as <expression> AS aliasname).

HAVING clause

If the SELECT list contains an expression such as SUM(amount) AS amount_alias or (SELECT ...) AS select_alias, and the results need to be filtered based on the value of this expression, then this must be done using the HAVING clause instead of the WHERE clause. This is because the value of the expression might not be known until after the WHERE clause has been processed.

MySQL is intelligent because it allows the HAVING clause to refer to the expression by its aliasname instead of the function which produces the value, so it permissible to use the following:

SELECT user, MAX(salary) AS max_salary 
FROM users
GROUP BY user 
HAVING max_salary>10; 

Oracle is not so intelligent as it does not allow the HAVING clause to reference an aliasname, so the aggregate function must be used instead, as in:

SELECT user, MAX(salary) AS max_salary 
FROM users
GROUP BY user 
HAVING MAX(salary)>10; 

However, unlike MySQL this only works if the expression is an aggregation, so does not work for functions and subqueries.

The following query is valid in MySQL but not for Oracle:

SELECT table_id, table_desc, 
      (SELECT COUNT(table_id) FROM dict_column 
                             WHERE dict_column.database_id=dict_table.database_id 
                               AND dict_column.table_id=dict_table.table_id 
                          GROUP BY dict_table.table_id) AS column_count
FROM dict_table  
WHERE database_id='audit'  
HAVING column_count>0 
ORDER BY table_id

This will only work in Oracle if the query is enclosed in an outer query, with the HAVING clause switched to the WHERE clause of the outer query, as shown in the following example:

SELECT table_id, table_desc, column_count FROM (
	SELECT table_id, table_desc, 
	      (SELECT COUNT(table_id) FROM dict_column 
                               WHERE dict_column.database_id=dict_table.database_id 
                                 AND dict_column.table_id=dict_table.table_id 
                            GROUP BY dict_table.table_id) AS column_count
	FROM dict_table  
	WHERE dict_table.database_id='audit'  
) WHERE column_count>0 
ORDER BY table_id

This modification of the SQL statement is possible because the SELECT, FROM, WHERE, GROUP-BY, HAVING and ORDER-BY strings are delivered as separate parts, so they can be examined an manipulated individually before they are assembled into a final SQL query.

Multi-table deletes

In MySQL it is possible to issue a query such as the following:

DELETE FROM ssn, trn, tbl, fld 
USING audit_ssn AS ssn
LEFT JOIN audit_trn AS trn ON (trn.session_id=ssn.session_id)
LEFT JOIN audit_tbl AS tbl ON (tbl.session_id=trn.session_id 
                           AND tbl.tran_seq_no=trn.tran_seq_no)
LEFT JOIN audit_fld AS fld ON (fld.session_id=tbl.session_id 
                           AND fld.tran_seq_no=tbl.tran_seq_no
                           AND fld.table_seq_no=tbl.table_seq_no)
WHERE ssn.ssn_datetime < '2015-11-01 00:00:00';

This will delete qualifying entries from the audit_ssn table, and all related entries from the audit_trn, audit_tbl and audit_fld tables. This is particularly useful if you are using a storage engine which does not support foreign key constraints.

This form of DELETE statement is not supported in Oracle as the same functionality can be provided by deleting from the first table only, and have the database itself take care of the others by defining foreign key constraints such as the following:

ALTER TABLE audit_trn ADD CONSTRAINT fk_audit_trn_audit_ssn FOREIGN KEY (session_id) 
                                                            REFERENCES audit_ssn(session_id) 
                                                            ON DELETE CASCADE;

ALTER TABLE audit_tbl ADD CONSTRAINT fk_audit_tbl_audit_trn FOREIGN KEY (session_id, tran_seq_no) 
                                                            REFERENCES audit_trn(session_id, tran_seq_no) 
                                                            ON DELETE CASCADE;

ALTER TABLE audit_fld ADD CONSTRAINT fk_audit_fld_audit_tbl FOREIGN KEY (session_id, tran_seq_no, table_seq_no) 
                                                            REFERENCES audit_tbl(session_id, tran_seq_no, table_seq_no) 
                                                            ON DELETE CASCADE;

Conclusion

In theory it should be a relatively simple exercise to switch from one SQL database to another. As is usual in this universe there is a big difference between theory and practice. Although all the database vendors 'supposedly' follow the same standards, they all seem to have followed different interpretations of those standards, followed by different implementations, all of which are capped off with different proprietary extensions. This makes the developer's job of switching database vendors more complicated than it need be, but by following some simple steps it is possible to take that mountain and turn it back into a mole hill:

I have included a version of my Oracle driver with my sample application, so you now have the option of running it with either MySQL, PostgreSQL or Oracle.

NOTE: All testing was done against Oracle Database 10g Express Edition (XE) running on Windows XP using PHP versions 4.4.4 and 5.1.6. I did not use the php_oci8.dll file which came with the PHP downloads as these are out of date and unreliable. Instead I used the latest versions which are available from PECL4WIN.

Because PHP 4 and PHP 5 use a different set of APIs there are different versions of the class file, dml.oracle.php4.class.inc and dml.oracle.php5.class.inc. The framework will automatically determine which version to load.


Amendment history:

01 Nov 2015 Added section Multi-table deletes.
01 Jul 2009 Added GROUP BY clause and HAVING clause.

counter