Two-table examples
Two-table examples
Two-table examples
The following set of examples all use two tables
from the company database: the company and department tables. These
examples exercise more of the function of the Relational DAS.
In this series of examples a company and department
are created, retrieved, updated, and finally deleted. This
illustrates the lifecycle for a data graph containing more than one
object. Note that this example clears out the company and
department tables at the start so that the exact results of the
queries can be known.
You can find these examples combined into one
script called 1cd-CRUD in the
Scenarios directory in the Relational
DAS package.
Example #1 One company, one department –
Create
As in the earlier example of creating just one company data
object, the first action after constructing the Relational DAS is
to call createRootDataObject() to obtain
the special root object of the otherwise empty data graph. The
company object is then created as a child of the root object, and
the department object as a child of the company object.
When it comes to applying the changes, the Relational DAS has to
perform special processing to maintain the foreign keys that
support the containment relationships, especially if auto-generated
primary keys are involved. In this example, the relationship
between the auto-generated primary key id in the company table
and the co_id
column in the department table must be maintained. When inserting a
company and department for the first time the Relational DAS has to
first insert the company row, then call PDO’s getLastInsertId() method to
obtain the auto-generated primary key, then add that as the value
of the co_id
column when inserting the department row.
<?php
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/*************************************************************************************
* Empty out the two tables
*************************************************************************************/
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
$pdo_stmt = $dbh->prepare('DELETE FROM COMPANY;');
$rows_affected = $pdo_stmt->execute();
$pdo_stmt = $dbh->prepare('DELETE FROM DEPARTMENT;');
$rows_affected = $pdo_stmt->execute();
/**************************************************************
* Create a company with name Acme and one department, the Shoe department
***************************************************************/
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
$root = $das -> createRootDataObject();
$acme = $root -> createDataObject('company');
$acme -> name = "Acme";
$shoe = $acme->createDataObject('department');
$shoe->name = 'Shoe';
$das -> applyChanges($dbh, $root);
?>
Example #2 One company, one department – Retrieve and
Update
In this case the SQL query passed to executeQuery() performs an inner
join to join the data from the company and department tables.
Primary keys for both the company and department tables must be
included in the query. The result set is re-normalised to form a
normalised data graph. Note that a column specifier is passed as
the third argument to the executeQuery() call enabling the
Relational DAS to know which column is which in the result set.
Note that the co_id column although used in the query is
not needed in the result set. In order to understand what the
Relational DAS is doing when it builds the data graph it may be
helpful to visualise what the result set looks like. Although the
data in the database is normalised, so that multiple department
rows can point through their foreign key to one company row, the
data in the result set is non-normalised: that is, if there is one
company and multiple departments, the values for the company are
repeated in each row. The Relational DAS has to reverse this
process and turn the result set back into a normalised data graph,
with just one company object.
In this example the Relational DAS will examine the result set
and column specifier, find data for both the company and department
tables, find primary keys for both, and interpret each row as
containing data for a department and its parent company. If it has
not seen data for that company before (it uses the primary key to
check) it creates a company object and then a department object
underneath it. If it has seen data for that company before and has
already created the company object it just creates the department
object underneath.
In this way the Relational DAS can retrieve and renormalise data
for multiple companies and multiple departments underneath
them.
<?php
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**************************************************************
* Retrieve the company and Shoe department, then delete Shoe and add IT
***************************************************************/
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
$root = $das->executeQuery($dbh,
'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id',
array('company.id','company.name','department.id','department.name'));
$acme = $root['company'][0]; // get the first company - will be 'Acme'
$shoe = $acme['department'][0]; // get the first department underneath - will be 'Shoe'
unset($acme['department'][0]);
$it = $acme->createDataObject('department');
$it->name = 'IT';
$das -> applyChanges($dbh, $root);
?>
Example #3 One company, two departments – Retrieve and
Delete
In this example the company and department are retrieved and
then deleted. It is not necessary to delete them individually
(although that would be possible) – deleting the company object
from the data graph also deletes any departments underneath it.
Note the way that the company object is actually deleted using
the PHP unset call. The unset has to be performed on the containing
property which in this case is the company property on the special
root object. You must use:
<?php
unset($root['company'][0]);
?>and not:
<?php
unset($acme); //WRONG
?>Simply unsetting $acme would destroy the variable but leave
the data in the data graph untouched.
<?php
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**************************************************************
* Retrieve the company and IT department, then delete the whole company
***************************************************************/
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
$root = $das->executeQuery($dbh,
'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id',
array('company.id','company.name','department.id','department.name'));
$acme = $root['company'][0];
$it = $acme['department'][0];
unset(
$root['company'][0]);
$das -> applyChanges($dbh, $root);
?>