PHP Manual: Specifying the metadata

Specifying the metadata

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 normalized into a graph.

Database metadata

The first argument to the constructor describes the target relational database.

Each table is described by an associative array with up to four keys.

Key	Value
name	The name of the table.
columns	An array listing the names of the columns, in any order.
PK	The name of the column containing the primary key.
FK	An array with two entries, 'from' and 'to', which define a column containing a foreign key, and a table to which the foreign key points. If there are no foreign keys in the table then the 'FK' entry does not need to be specified. Only one foreign key can be specified. Only a foreign key pointing to the primary key of a table can be specified.


<?php
/*****************************************************************
* METADATA DEFINING THE DATABASE
******************************************************************/
$company_table = array (
  'name' => 'company',
  'columns' => array('id', 'name',  'employee_of_the_month'),
  'PK' => 'id',
  'FK' => array (
      'from' => 'employee_of_the_month',
      'to' => 'employee',
      ),
  );
$department_table = array (
  'name' => 'department', 
  'columns' => array('id', 'name', 'location', 'number', 'co_id'),
  'PK' => 'id',
  'FK' => array (
      'from' => 'co_id',
      'to' => 'company',
      )
  );
$employee_table = array (
  'name' => 'employee',
  'columns' => array('id', 'name', 'SN', 'manager', 'dept_id'),
  'PK' => 'id',
  'FK' => array (
      'from' => 'dept_id',
      'to' => 'department',
      )
  );
$database_metadata = array($company_table, $department_table, $employee_table);
?>

This metadata corresponds to a relational database that might have been defined to MySQL as:

create table company (
 id integer auto_increment,
 name char(20),
 employee_of_the_month integer,
 primary key(id)
);
create table department (
 id integer auto_increment,
 name char(20),
 location char(10),
 number integer(3),
 co_id integer,
 primary key(id)
);
create table employee (
 id integer auto_increment,
 name char(20),
 SN char(4),
 manager tinyint(1),
 dept_id integer,
 primary key(id)
);

or to DB2 as:

create table company ( \
  id integer not null generated by default as identity,  \
  name varchar(20), \
  employee_of_the_month integer, \
  primary key(id) )
create table department ( \
  id integer not null generated by default as identity, \
  name varchar(20), \
  location varchar(10), \
  number integer, \
  co_id integer, \
  primary key(id) )
create table employee ( \
  id integer not null generated by default as identity, \
  name varchar(20), \
  SN char(4), \
  manager smallint, \
  dept_id integer, \
  primary key(id) )

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 co_id column on the department table and the dept_id column on the employee table is they should contain the primary key of their containing company or department record, respectively. So these two columns are acting as foreign keys.

There is a third foreign key in this example, that from the employee_of_the_month column of the company record to a single row of the employee table. Note the difference in intent between this foreign key and the other two. The employee_of_the_month column represents a single-valued relationship: there can be only one employee of the month for a given company. The co_id and dept_id columns represent multi-valued relationships: a company can contain many departments and a department can contain many employees. This distinction will become evident when the remainder of the metadata picks out the company-department and department-employee relationships as containment relationships.

There are a few simple rules to be followed when constructing the database metadata:

All tables must have primary keys, and the primary keys must be specified in the metadata. Without primary keys it is not possible to keep track of object identities. As you can see from the SQL statements that create the tables, primary keys can be auto-generated, that is, generated and assigned by the database when a record is inserted. In this case the auto-generated primary key is obtained from the database and inserted into the data object immediately after the row is inserted into the database.
It is not necessary to specify in the metadata all the columns that exist in the database, only those that will be used. For example, if the company table had another column that the application did not want to access with SDO, this need not be specified in the metadata. On the other hand it would have done no harm to specify it: if specified in the metadata but never retrieved, or assigned to by the application, then the unused column will not affect anything.
In the database metadata note that the foreign key definitions identify not the destination column in the table which is pointed to, but the table name itself. Strictly, the relational model permits the destination of a foreign key to be a non-primary key. Only foreign keys that point to a primary key are useful for constructing the SDO model, so the metadata specifies the table name. It is understood that the foreign key points to the primary key of the given table.

Given these rules, and given the SQL statements that define the database, the database metadata should be easy to construct.

What the Relational DAS does with the metadata

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.

Specifying the application root type

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.

Specifying the SDO containment relationships

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.


<?php
$department_containment = array( 'parent' => 'company', 'child' => 'department');
$employee_containment = array( 'parent' => 'department', 'child' => 'employee');

$SDO_containment_metadata = array($department_containment, $employee_containment);           
?>

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 co_id column in the department table) and from employee to department (the dept_id column in the employee table) are interpreted as supporting the SDO containment relationships. Each containment relationship mentioned in the SDO containment relationships metadata must have a corresponding foreign key present in the database and defined in the database metadata. The values of the foreign key columns for containment relationships do not appear in the data objects, instead each is represented by a containment relationship from the parent to the child. So the co_id column in the department row in the database, for example, does not appear as a property on the department type, but instead as a containment relationship called department on the company type. Note that the foreign key and the parent-child relationship appear to have opposite senses: the foreign key points from the department to the company, but the parent-child relationship points from company to department.

The third foreign key in this example, the employee_of_the_month , is handled differently. This is not mentioned in the SDO containment relationships metadata. As a consequence this is interpreted in the second way: it becomes a single-valued non-containment reference on the company object, to which can be assigned references to SDO data objects of the employee type. It does appear as a property on the company type. The way to assign a value to it in the SDO data graph is to have a graph that contains an employee object through the containment relationships, and to assign the object to it. This is illustrated in the later examples below.