sdodasrel-php-metadata-8

  • Examples
  • Specifying the metadata

  • 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.