<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Specifying the metadata</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="sdodasrel.examples-crud.html">Creating, retrieving, updating and deleting data</a></div>
<div class="next" style="text-align: right; float: right;"><a href="sdodasrel.examples.one-table.html">One-table examples</a></div>
<div class="up"><a href="sdodasrel.examples.html">Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="sdodasrel.metadata" class="section">
<h2 class="title">Specifying the metadata</h2>
<p class="para">
This first long section describes in detail how the metadata describing
the database and the required SDO model is supplied to the
Relational DAS.
</p>
<p class="para">
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.
</p>
<div class="section" id="sdodasrel.metadata.database">
<h2 class="title">Database metadata</h2>
<p class="para">
The first argument to the constructor describes the target
relational database.
</p>
<p class="para">
Each table is described by an associative array with up to four keys.
<table class="doctable informaltable">
<thead>
<tr>
<th>Key</th>
<th>Value</th>
</tr>
</thead>
<tbody class="tbody">
<tr>
<td>name</td>
<td>The name of the table.</td>
</tr>
<tr>
<td>columns</td>
<td>
An array listing the names of the columns, in any order.
</td>
</tr>
<tr>
<td>PK</td>
<td>The name of the column containing the primary key.</td>
</tr>
<tr>
<td>FK</td>
<td>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.
</td>
</tr>
</tbody>
</table>
</p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #FF8000">/*****************************************************************<br />* METADATA DEFINING THE DATABASE<br />******************************************************************/<br /></span><span style="color: #0000BB">$company_table </span><span style="color: #007700">= array (<br /> </span><span style="color: #DD0000">'name' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'company'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'columns' </span><span style="color: #007700">=> array(</span><span style="color: #DD0000">'id'</span><span style="color: #007700">, </span><span style="color: #DD0000">'name'</span><span style="color: #007700">, </span><span style="color: #DD0000">'employee_of_the_month'</span><span style="color: #007700">),<br /> </span><span style="color: #DD0000">'PK' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'id'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'FK' </span><span style="color: #007700">=> array (<br /> </span><span style="color: #DD0000">'from' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'employee_of_the_month'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'to' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'employee'</span><span style="color: #007700">,<br /> ),<br /> );<br /></span><span style="color: #0000BB">$department_table </span><span style="color: #007700">= array (<br /> </span><span style="color: #DD0000">'name' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'department'</span><span style="color: #007700">, <br /> </span><span style="color: #DD0000">'columns' </span><span style="color: #007700">=> array(</span><span style="color: #DD0000">'id'</span><span style="color: #007700">, </span><span style="color: #DD0000">'name'</span><span style="color: #007700">, </span><span style="color: #DD0000">'location'</span><span style="color: #007700">, </span><span style="color: #DD0000">'number'</span><span style="color: #007700">, </span><span style="color: #DD0000">'co_id'</span><span style="color: #007700">),<br /> </span><span style="color: #DD0000">'PK' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'id'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'FK' </span><span style="color: #007700">=> array (<br /> </span><span style="color: #DD0000">'from' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'co_id'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'to' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'company'</span><span style="color: #007700">,<br /> )<br /> );<br /></span><span style="color: #0000BB">$employee_table </span><span style="color: #007700">= array (<br /> </span><span style="color: #DD0000">'name' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'employee'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'columns' </span><span style="color: #007700">=> array(</span><span style="color: #DD0000">'id'</span><span style="color: #007700">, </span><span style="color: #DD0000">'name'</span><span style="color: #007700">, </span><span style="color: #DD0000">'SN'</span><span style="color: #007700">, </span><span style="color: #DD0000">'manager'</span><span style="color: #007700">, </span><span style="color: #DD0000">'dept_id'</span><span style="color: #007700">),<br /> </span><span style="color: #DD0000">'PK' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'id'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'FK' </span><span style="color: #007700">=> array (<br /> </span><span style="color: #DD0000">'from' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'dept_id'</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">'to' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'department'</span><span style="color: #007700">,<br /> )<br /> );<br /></span><span style="color: #0000BB">$database_metadata </span><span style="color: #007700">= array(</span><span style="color: #0000BB">$company_table</span><span style="color: #007700">, </span><span style="color: #0000BB">$department_table</span><span style="color: #007700">, </span><span style="color: #0000BB">$employee_table</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<p class="para">
This metadata corresponds to a relational database that might have
been defined to MySQL as:
</p>
<div class="example-contents">
<div class="sqlcode"><pre class="sqlcode">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)
);</pre>
</div>
</div>
<p class="para">
or to DB2 as:
</p>
<div class="example-contents">
<div class="sqlcode"><pre class="sqlcode">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) )</pre>
</div>
</div>
<p class="para">
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
<var class="varname"><var class="varname">co_id</var></var>
column on the department table and the
<var class="varname"><var class="varname">dept_id</var></var>
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.
</p>
<p class="para">
There is a third foreign key in this example, that from the
<var class="varname"><var class="varname">employee_of_the_month</var></var>
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
<var class="varname"><var class="varname">employee_of_the_month</var></var>
column represents a single-valued relationship: there can be only
one employee of the month for a given company.
The
<var class="varname"><var class="varname">co_id</var></var>
and
<var class="varname"><var class="varname">dept_id</var></var>
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.
</p>
<p class="para">
There are a few simple rules to be followed when constructing the
database metadata:
</p>
<ul class="itemizedlist">
<li class="listitem">
<p class="para">
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.
</p>
</li>
<li class="listitem">
<p class="para">
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.
</p>
</li>
<li class="listitem">
<p class="para">
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.
</p>
</li>
</ul>
<p class="para">
Given these rules, and given the SQL statements that define the
database, the database metadata should be easy to construct.
</p>
<div class="section" id="sdodasrel.metadata.database.model">
<h2 class="title">What the Relational DAS does with the metadata</h2>
<p class="para">
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.
</p>
<p class="para">
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.
</p>
<p class="para">
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
<a href="sdodasrel.metadata.html#sdodasrel.metadata.crels" class="link">
SDO containment relationships
</a>
below.
</p>
</div>
<div class="section" id="sdodasrel.metadata.approottype">
<h2 class="title">Specifying the application root type</h2>
<p class="para">
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.
</p>
</div>
<div class="section" id="sdodasrel.metadata.crels">
<h2 class="title">Specifying the SDO containment relationships</h2>
<p class="para">
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.
</p>
<p class="para">
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.
</p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$department_containment </span><span style="color: #007700">= array( </span><span style="color: #DD0000">'parent' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'company'</span><span style="color: #007700">, </span><span style="color: #DD0000">'child' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'department'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$employee_containment </span><span style="color: #007700">= array( </span><span style="color: #DD0000">'parent' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'department'</span><span style="color: #007700">, </span><span style="color: #DD0000">'child' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'employee'</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$SDO_containment_metadata </span><span style="color: #007700">= array(</span><span style="color: #0000BB">$department_containment</span><span style="color: #007700">, </span><span style="color: #0000BB">$employee_containment</span><span style="color: #007700">); <br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<p class="para">
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
<var class="varname"><var class="varname">co_id</var></var>
column in the department table)
and from employee to department (the
<var class="varname"><var class="varname">dept_id</var></var>
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
<var class="varname"><var class="varname">co_id</var></var>
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
<var class="varname"><var class="varname">department</var></var>
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.
</p>
<p class="para">
The third foreign key in this example, the
<var class="varname"><var class="varname">employee_of_the_month</var></var>
,
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.
</p>
</div>
</div>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="sdodasrel.examples-crud.html">Creating, retrieving, updating and deleting data</a></div>
<div class="next" style="text-align: right; float: right;"><a href="sdodasrel.examples.one-table.html">One-table examples</a></div>
<div class="up"><a href="sdodasrel.examples.html">Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
