SDO

1 SDO Core

1.1 ???

2 Relational DAS

2.1 How do I represent inheritance of data types?
2.2 How do I get round the limitations with relationship multiplicities?
2.3 How do I read tables that use composite keys?
2.4 How do I read a table that uses more than one foreign key?

3 XML DAS

3.1 ???

How do I represent inheritance of data types?

SDO does not use static PHP classes to represent rows being read to or written from a relational database. Instead it uses a generic class (SDO_DataObject) to represent any row that may be read an written. The SDO relational DAS configuration instructs SDO how to read and write tables to populate data from a database and commit changes back to a database. As such SDO does not concern itself with how to represent inheritance in PHP class hierarchies.

However the problem still remains of how to represent data that has a natural hierarchy where some data is generic across types being represented while other data is specific to a particular type. This is a subject of debate within object relational mapping (ORM) circles so here are some examples of how it can be done using SDO.

Imagine you have a Vehicle type and Car and Truck types that extend it to express features particular to each.

You could choose to represent all of the data from all three types in one big table. Columns specific to Truck will be NULL when a Car is being represented and vice versa. SDO can be used to represent this scenario by defining a single table definition for the large table. When data is read from the table a restricted set of columns can be specified to SDO to represent just that data that applies to Car or Truck.

For example, the SDO meta data would be;

$vehicleall_table = array('name' => 'vehicleall',
                              'columns' => array( 'vehicleid',
                                                  'name',
                                                  'numberofpassengers',
                                                  'loadinkgs'),
                                 'PK' => 'vehicleid');

You might use the following to retrieve all car entries;

// construct the SQL query for car retrieval
      $stmt = "select vehicleid, name, numberofpassengers from vehicleall where numberofpassengers is not null";

      // execute the query to retrieve the cars
      $root_car = $das->executeQuery($dbh, $stmt, array('vehicleall.vehicleid',
                                                        'vehicleall.name',
                                                        'vehicleall.numberofpassengers') );

Alternatively you could choose to create a table for each branch of the inheritance hierarchy. In this case a table is created for Car and Truck types and the Vehicle columns are represented in each table. This is also easy to implement using SDO. Here two table descriptions are constructed. One for Car and one for Truck matching the schema for these database tables. SDO data objects can be constructed to read and write data based on these descriptions.

For example, the SDO meta data would be;

$carall_table = array('name' => 'carall',
                              'columns' => array( 'vehicleid',
                                                  'name',
                                                  'numberofpassengers'),
                                 'PK' => 'vehicleid');

    $truckall_table = array('name' => 'truckall',
                              'columns' => array( 'vehicleid',
                                                  'name',
                                                  'loadinkgs'),
                                 'PK' => 'vehicleid');

You might use the following to retrieve all car entries;

// construct the SQL query for car retrieval
      $stmt = "select vehicleid, name, numberofpassengers from carall";

      // execute the query to retrieve the cars
      $root_car = $das->executeQuery($dbh, $stmt, array('carall.vehicleid',
                                                        'carall.name',
                                                        'carall.numberofpassengers') );

Another alternative is to create three tables to represent Vehicle, Car and Truck and use a foreign key relationship to associate Car rows with their respective Vehicle rows. The same holds for Truck of course. SDO can be used to represent this approach by describing the tables and the relationship between them to SDO. When a Car is read an SDO data object will be returned representing the Car data. This data object will contain another data object which represents the Vehicle data.

For example, the SDO meta data (for the Car side of the hierarchy) would be;

$vehicle_table = array('name' => 'vehicle',
                           'columns' => array( 'vehicleid',
                                                'name'),
                           'PK' => 'vehicleid');

    $car_table = array('name' => 'car',
                       'columns' => array( 'vehicleid',
                                           'numberofpassengers'),
                       'PK' => 'numberofpassengers',
	                   'FK' => array ('from' => 'vehicleid',
		                              'to' => 'vehicle' ) );

You might use the following to retrieve all car entries;

// construct the SQL query for car retrieval
      $stmt = "select v.vehicleid, v.name, c.numberofpassengers from vehicle v, car c where v.vehicleid = c.vehicleid";

      // execute the query to retrieve the cars
      $root_vehicle = $das->executeQuery($dbh, $stmt, array('vehicle.vehicleid',
                                                            'vehicle.name',
                                                            'car.numberofpassengers') );

How do I get round the limitations with relationship multiplicities?

The SDO relational DAS only supports a limited relationship multiplicity
http://uk.php.net/manual/en/ref.sdo.das.rel.php. In particular only the following are supported:

One to many (through an SDO containment relationship)
One to one (through an SDO non containment relationship)

There is not much room to manoeuvre here. Assume you have a many to many relationship such as Student - Enrolment - Class. SDO cannot read a Student record along with all the Class records they are enrolled in. Neither can it currently read a Class record and all the students that are enrolled in it. It can of course read and update Student and Class records independently but you loose the benefit of having relationships managed automatically by SDO. I think you could also read Enrolments and find how a Student is related to a Class.

How do I read tables that use composite keys?

Composite keys are not supported in the SDO relational DAS at present.

If you have tables that use a composite key as a foreign key then you cannot use SDO to read the relationship. As you might expect the way around this is to derive some single valued synthetic key, for example, a sequence, and use it to represent the relationship. This means making changes to the database schema of course.

These options are of much less use than support for many to many associations. I'm going to add a "wish list" to the web site I'm making and put this near the top.

How do I read a table that uses more than one foreign key?

Only one foreign key per table is supported in SDO at present. This is associated with the question about many to many relationships. If the multiple foreign keys are referencing a composite primary key in another table then see the answer to "How do I read tables that use composite keys?". If not see the answer to "How do I get round the limitations with relationship multiplicities?"