Specifying the metadata
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.