SDO Relational Data Access Service Functions
This extension is EXPERIMENTAL. The behaviour of this extension -- including the names of its functions and anything else documented about this extension -- may change without notice in a future release of PHP. Use this extension at your own risk.
In order to use the Relational Data Access Service for Service Data Objects, you will need to understand some of the concepts behind SDO: the data graph, the data object, the disconnected way of working, the change summary, XPath and property expressions, and so on. If you are not familiar with these ideas, you might want to look first at the section on SDO. In addition, the Relational DAS makes use of the PDO extension to isolate itself from specifics of different back-end relational databases. In order to use the Relational DAS you will need to be able to create and pass a PDO database connection; for this reason you might also want to take a look at the section on PDO.
The job of the Relational DAS is to move data between the application and a relational database. In order to do this it needs to be told the mapping between the database entities - tables, columns, primary keys and foreign keys - and the elements of the SDO model - types, properties, containment relationships and so on. You specify this information as metadata when you construct the Relational DAS.
There are other ways of working with the data in the database: it is possible to just create data objects and write them to the database without a preliminary call to executeQuery(), for example. This scenario and others are explored in the Examples section below.
The installation instructions for all the SDO components are in the SDO install section of the SDO documentation.
In any case, the essential facts are that the Relational DAS is written in PHP and it should be placed somewhere on the PHP include_path .
Your application will of course need to include the Relational DAS with a statement like this:
The Relational DAS requires that the SDO extension be installed. The SDO extension requires a version of PHP 5.1, and the Relational DAS requires a recent version that contains an important fix for PDO. The most up-to-date information about required levels of PHP should be found in the changelog for the package on PECL. At the time of writing, though, the Relational DAS requires the most recent beta level of PHP 5.1, that is PHP 5.1.0.
The Relational DAS uses PDO to access the relational database, and so should run with a variety of different relational databases. At the time of writing it has been tested in the following configurations
The Relational DAS applies changes to the database within a user-delimited transaction: that is, it issues a call to PDO::beginTransaction() before beginning to apply changes, and PDO::commit() or PDO::rollback() on completion. Whichever database is chosen, the database and the PDO driver for the database must support these calls.
There are the following limitations in the current release of the Relational DAS:
This section illustrates how the Relational DAS can be used to create, retrieve, update and delete data in a relational database. Many of the examples are illustrated with a three-table database that contains companies, departments within those companies, and employees that work in those departments. This example is used in a number of places within the SDO literature. See the examples section of the » Service Data Objects specification or the Examples section of the documentation for the SDO extension.
The Relational DAS is constructed with metadata that defines the relational database and how it should be mapped to SDO. The long section that follows describes this metadata and how to construct the Relational DAS. The examples that follow it all assume that this metadata is in an included php file.
The examples below and others can all be found in the
The Relational DAS throws exceptions in the event that it finds errors in the metadata or errors when executing SQL statements against the database. For brevity the examples below all omit the use of try/catch blocks around the calls to the Relational DAS.
These examples all differ from the expected use of SDO in two important respects.
First, they show all interactions with the database completed within one script. In this respect these scenarios are not realistic but are chosen to illustrate just the use of the Relational DAS. It is expected that interactions with the database will be separated in time and the data graph serialised and deserialised into the PHP session one or more times as the application interacts with an end user.
Second, all queries executed against the database use hard-coded queries with no variables substituted. In this case it is safe to use the simple executeQuery() call, and this is what the examples illustrate. In practice, though, it is unlikely that the SQL statement is known entirely ahead of time. In order to allow variables to be safely substituted into the SQL queries, without running the risk of injecting SQL with unknown effects, it is safer to use the executePreparedQuery() which takes a prepared SQL statement containing placeholders and a list of values to be substituted.
This first long section describes in detail how the metadata describing the database and the required SDO model is supplied to the Relational DAS.
When the constructor for the Relational DAS is invoked, it needs to be passed several pieces of information. The bulk of the information, passed as an associative array in the first argument to the constructor, tells the Relational DAS what it needs to know about the relational database. It describes the names of the tables, columns, primary keys and foreign keys. It should be fairly easy to understand what is required, and once written it can be placed in a php file and included when needed. The remainder of the information, passed in the second and third arguments to the constructor, tells the Relational DAS what it needs to know about the relationships between objects and the shape of the data graph; it ultimately determines how the data from the database is to be normalised into a graph.
The first argument to the constructor describes the target relational database.
Each table is described by an associative array with up to four keys.
This metadata corresponds to a relational database that might have been defined to MySQL as:
or to DB2 as:
Note that although in this example there are no foreign keys specified
to the database and so the database is not expected to enforce
referential integrity, the intention behind the
There is a third foreign key in this example, that from the
There are a few simple rules to be followed when constructing the database metadata:
Given these rules, and given the SQL statements that define the database, the database metadata should be easy to construct.
The Relational DAS uses the database metadata to form most of the SDO model. For each table in the database metadata, an SDO type is defined. Each column which can represent a primitive value (columns which are not defined as foreign keys) are added as properties to the SDO type.
All primitive properties are given a type of string in the SDO model, regardless of their SQL type. When writing values back to the database the Relational DAS will create SQL statements that treat the values as strings, and the database will convert them to the appropriate type.
Foreign keys are interpreted in one of two ways, depending on the metadata in the third argument to the constructor that defines the SDO containment relationships. A discussion of this is therefore deferred until the section on SDO containment relationships below.
The second argument to the constructor is the application root type. The true root of each data graph is an object of a special root type and all application data objects come somewhere below that. Of the various application types in the SDO model, one has to be the application type immediately below the root of the data graph. If there is only one table in the database metadata, the application root type can be inferred, and this argument can be omitted.
The third argument to the constructor defines how the types in the model are to be linked together to form a graph. It identifies the parent-child relationships between the types which collectively form a graph. The relationships need to be supported by foreign keys to be found in the data, in a way shortly to be described.
The metadata is an array containing one or more associative arrays, each of which identifies a parent and a child. The example below shows a parent-child relationship from company to department, and another from department to employee. Each of these will become an SDO property defining a multi-valued containment relationship in the SDO model.
Foreign keys in the database metadata are interpreted as properties
with either multi-valued containment relationships or single-valued
non-containment references, depending on whether they have a
corresponding SDO containment relationship specified in the metadata.
In the example here, the foreign keys from department to company (the
The third foreign key in this example, the
The following set of examples all use the Relational DAS to work with a data graph containing just one application data object, a single company and the data just to be found the company table. These examples do not exercise the power of SDO or the Relational DAS and of course the same result could be achieved more economically with direct SQL statements but they are intended to illustrate how to work with the Relational DAS.
For this very simple scenario it would be possible to simplify the database metadata to include just the company table - if that were done the second and third arguments to the constructor and the column specifier used in the query example would become optional.
Example 2194. Creating a data object
The simplest example is that of creating a single data object and writing it to the database. In this example a single company object is created, its name is set to 'Acme', and the Relational DAS is called to write the changes to the database. The company name is set here using the property name method. See the Examples section on the SDO extension for other ways of accessing the properties of an object.
Data objects can only be created when you have a data object to start with, however. It is for that reason that the first call to the Relational DAS here is to obtain a root object. This is in effect how to ask for an empty data graph - the special root object is the true root of the tree. The company data object is then created with a call to createDataObject() on the root object. This creates the company data object and inserts it in the graph by inserting into a multi-valued containment property on the root object called 'company'.
When the Relational DAS is called to apply the changes a simple insert statement 'INSERT INTO company (name) VALUES ("Acme");' will be constructed and executed. The auto-generated primary key will be set into the data object and the change summary will be reset, so that it would be possible to continue working with the same data object, modify it, and apply the newer changes a second time.
Example 2195. Retrieving a data object
In this example a single data object is retrieved from the database
- or possibly more than one if there is more than one company
called 'Acme'. For each company returned, the
In this example the third argument to
the column specifier is needed as there are other tables in the
metadata with column names of
Example 2196. Updating a data object
This example combines the previous two, in the sense that in order to be updated the object must first be retrieved. The application code reverses the company name (so 'Acme' becomes 'emcA') and then the changes are written back to the database in the same way that they were when the object was created. Because the query searches for the name both ways round the program can be run repeatedly to find the company and reverse its name each time.
In this example the same instance of the Relational DAS is reused for the applyChanges(), as is the PDO database handle. This is quite alright; it also alright to allow the previous instances to be garbage collected and to obtain new instances. No state data regarding the graph is held the Relational DAS once it has returned a data graph to the application. All necessary data is either within the graph itself, or can be reconstructed from the metadata.
Example 2197. Deleting a data object
Any companies called 'Acme' or its reverse 'emcA' are retrieved. They are then all deleted from the graph with unset.
In this example they are all deleted in one go by unsetting the containing property (the property defining the containment relationship). It is also possible to delete them individually.
The following set of examples all use two tables from the company database: the company and department tables. These examples exercise more of the function of the Relational DAS.
In this series of examples a company and department are created, retrieved, updated, and finally deleted. This illustrates the lifecycle for a data graph containing more than one object. Note that this example clears out the company and department tables at the start so that the exact results of the queries can be known.
You can find these examples combined into one script called
Example 2198. One company, one department - Create
As in the earlier example of creating just one company data object, the first action after constructing the Relational DAS is to call createRootDataObject() to obtain the special root object of the otherwise empty data graph. The company object is then created as a child of the root object, and the department object as a child of the company object.
When it comes to applying the changes, the Relational DAS has to
perform special processing to maintain the foreign keys that support
the containment relationships, especially if auto-generated primary
keys are involved. In this example, the relationship between the
auto-generated primary key
Example 2199. One company, one department - Retrieve and Update
In this case the SQL query passed to executeQuery() performs an inner join to join the data from the company and department tables. Primary keys for both the company and department tables must be included in the query. The result set is re-normalised to form a normalised data graph. Note that a column specifier is passed as the third argument to the executeQuery() call enabling the Relational DAS to know which column is which in the result set.
Note that the
In this example the Relational DAS will examine the result set and column specifier, find data for both the company and department tables, find primary keys for both, and interpret each row as containing data for a department and its parent company. If it has not seen data for that company before (it uses the primary key to check) it creates a company object and then a department object underneath it. If it has seen data for that company before and has already created the company object it just creates the department object underneath.
In this way the Relational DAS can retrieve and renormalise data for multiple companies and multiple departments underneath them.
Example 2200. One company, two departments - Retrieve and Delete
In this example the company and department are retrieved and then deleted. It is not necessary to delete them individually (although that would be possible) - deleting the company object from the data graph also deletes any departments underneath it.
Note the way that the company object is actually deleted using the PHP unset call. The unset has to be performed on the containing property which in this case is the company property on the special root object. You must use:
The following examples use all three tables from the company database:
the company, department, and employee tables. These introduce the final
piece of function not exercised by the examples above: the
Like the examples above for company and department, this set of examples is intended to illustrate the full lifecycle of such a data graph.
Example 2201. One company, one department, one employee - Create
In this example a company is created containing one department and just one employee. Note that this example clears out all three tables at the start so that the exact results of the queries can be known.
Note how once the company, department and employee have been created,
When it comes to inserting the graph into the database, the procedure
is similar to the example of inserting the company and department,
Example 2202. One company, one department, one employee - Retrieve and update
The SQL statement passed to the Relational DAS is this time an inner join that retrieves data from all three tables. Otherwise this example introduces nothing that has not appeared in a previous example.
The graph is updated by the addition of a new department and employee and some alterations to the name properties of the existing objects in the graph. The combined changes are then written back. The Relational DAS will process and apply an arbitrary mixture of additions, modifications and deletions to and from the data graph.
Example 2203. One company, two departments, two employees - Retrieve and delete
The company is retrieved as a complete data graph containing five data objects - the company, two departments and two employees. They are all deleted by deleting the company object. Deleting an object from the graph deletes all the object beneath it in the graph. Five SQL DELETE statements will be generated and executed. As always they will be qualified with a WHERE clause that contains all of the fields that were retrieved, so that any updates to the data in the database in the meantime by another process will be detected.
You may be interested in seeing the SQL statements that are generated
in order to apply changes back to the database. At the top of the
The Relational DAS provides two classes: the Relational DAS itself and the subclass of Exception that can be thrown. The Relational DAS has four publicly useful calls: the constructor, the createRootDataObject() call to obtain the root object of an empty data graph, the executeQuery() call to obtain a data graph containing data from a relational database, and the applyChanges() call to write changes made to a data graph back to the relational database.
The only object other than an SDO_DAS_Relational_Exception with which the application is expected to interact.
Table of Contents
Alternative PHP Cache
Advanced PHP debugger
Aspell functions [deprecated]
BCMath Arbitrary Precision Mathematics Functions
PHP bytecode Compiler
Bzip2 Compression Functions
CCVS API Functions [deprecated]
ClibPDF Functions [deprecated]
COM and .Net (Windows)
Character Type Functions
Cybercash Payment Functions
Credit Mutuel CyberMUT functions
Cyrus IMAP administration Functions
Date and Time Functions
Database (dbm-style) Abstraction Layer Functions
DBM Functions [deprecated]
Direct IO Functions
DOM XML Functions
Error Handling and Logging Functions
File Alteration Monitor Functions
Forms Data Format Functions
Firebird/Interbase Functions (PDO_FIREBIRD)
Function Handling Functions
Haru PDF Functions
Hyperwave API Functions
IBM Functions (PDO_IBM)
IIS Administration Functions
Imagick Image Library
Informix Functions (PDO_INFORMIX)
Ingres II Functions
IRC Gateway Functions
PHP / Java Integration
Lotus Notes Functions
MaxDB PHP Extension
Mcrypt Encryption Functions
MCVE (Monetra) Payment Functions
Ming functions for Flash
Microsoft SQL Server Functions
Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
Mohawk Software Session Handler Functions
Multibyte String Functions
MySQL Functions (PDO_MYSQL)
MySQL Improved Extension
Ncurses Terminal Screen Control Functions
Object Aggregation/Composition Functions
Object property and method call overloading
ODBC Functions (Unified)
ODBC and DB2 Functions (PDO_ODBC)
OpenAL Audio Bindings
Oracle Functions [deprecated]
Oracle Functions (PDO_OCI)
Output Control Functions
Ovrimos SQL Functions
Paradox File Access
Process Control Functions
Regular Expression Functions (Perl-Compatible)
Phar archive stream and classes
Regular Expression Functions (POSIX Extended)
PostgreSQL Functions (PDO_PGSQL)
Program Execution Functions
PostScript document creation
GNU Recode Functions
RPM Header Reading Functions
SAM - Simple Asynchronous Messaging
Satellite CORBA client extension [deprecated]
SDO XML Data Access Service Functions
SDO Relational Data Access Service Functions
SESAM Database Functions
PostgreSQL Session Save Handler
Session Handling Functions
Shared Memory Functions
Standard PHP Library (SPL) Functions
SQLite Functions (PDO_SQLITE)
Secure Shell2 Functions
Shockwave Flash Functions
TCP Wrappers Functions
Variable Handling Functions
Verisign Payflow Pro Functions
XML Parser Functions
Zip File Functions
Zlib Compression Functions