<!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>Two-table examples</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="sdodasrel.examples.one-table.html">One-table examples</a></div>
<div class="next" style="text-align: right; float: right;"><a href="sdodasrel.examples.three-table.html">Three-table example</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.examples.two-table" class="section">
<h2 class="title">Two-table examples</h2>
<p class="para">
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.
</p>
<p class="para">
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.
</p>
<p class="para">
You can find these examples combined into one script called
<var class="filename">1cd-CRUD</var>
in the
<var class="filename">Scenarios</var>
directory in the Relational DAS package.
</p>
<p class="para">
<div class="example" id="example-5471">
<p><strong>Example #1 One company, one department - Create</strong></p>
<div class="example-contents"><p>
As in the earlier example of creating just one company data object,
the first action after constructing the Relational DAS is to call
<span class="function"><strong>createRootDataObject()</strong></span>
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.
</p></div>
<div class="example-contents"><p>
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
<var class="varname"><var class="varname">id</var></var>
in the company table and the
<var class="varname"><var class="varname">co_id</var></var>
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
<span class="function"><strong>getLastInsertId()</strong></span>
method to obtain the auto-generated primary key, then add that as
the value of the
<var class="varname"><var class="varname">co_id</var></var>
column when inserting the department row.
</p></div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">require_once </span><span style="color: #DD0000">'SDO/DAS/Relational.php'</span><span style="color: #007700">;<br />require_once </span><span style="color: #DD0000">'company_metadata.inc.php'</span><span style="color: #007700">;<br /><br /></span><span style="color: #FF8000">/*************************************************************************************<br />* Empty out the two tables<br />*************************************************************************************/<br /></span><span style="color: #0000BB">$dbh </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO_DSN</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_USER</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_PASSWORD</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$pdo_stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">'DELETE FROM COMPANY;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$rows_affected </span><span style="color: #007700">= </span><span style="color: #0000BB">$pdo_stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$pdo_stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">'DELETE FROM DEPARTMENT;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$rows_affected </span><span style="color: #007700">= </span><span style="color: #0000BB">$pdo_stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /><br /></span><span style="color: #FF8000">/**************************************************************<br />* Create a company with name Acme and one department, the Shoe department<br />***************************************************************/<br /></span><span style="color: #0000BB">$dbh </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO_DSN</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_USER</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_PASSWORD</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">= new </span><span style="color: #0000BB">SDO_DAS_Relational </span><span style="color: #007700">(</span><span style="color: #0000BB">$database_metadata</span><span style="color: #007700">,</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,</span><span style="color: #0000BB">$SDO_containment_metadata</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$root </span><span style="color: #007700">= </span><span style="color: #0000BB">$das </span><span style="color: #007700">-> </span><span style="color: #0000BB">createRootDataObject</span><span style="color: #007700">();<br /><br /></span><span style="color: #0000BB">$acme </span><span style="color: #007700">= </span><span style="color: #0000BB">$root </span><span style="color: #007700">-> </span><span style="color: #0000BB">createDataObject</span><span style="color: #007700">(</span><span style="color: #DD0000">'company'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$acme </span><span style="color: #007700">-> </span><span style="color: #0000BB">name </span><span style="color: #007700">= </span><span style="color: #DD0000">"Acme"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$shoe </span><span style="color: #007700">= </span><span style="color: #0000BB">$acme</span><span style="color: #007700">-></span><span style="color: #0000BB">createDataObject</span><span style="color: #007700">(</span><span style="color: #DD0000">'department'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$shoe</span><span style="color: #007700">-></span><span style="color: #0000BB">name </span><span style="color: #007700">= </span><span style="color: #DD0000">'Shoe'</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">-> </span><span style="color: #0000BB">applyChanges</span><span style="color: #007700">(</span><span style="color: #0000BB">$dbh</span><span style="color: #007700">, </span><span style="color: #0000BB">$root</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-5472">
<p><strong>Example #2 One company, one department - Retrieve and Update</strong></p>
<div class="example-contents"><p>
In this case the SQL query passed to
<span class="function"><strong>executeQuery()</strong></span>
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
<span class="function"><strong>executeQuery()</strong></span>
call enabling the Relational DAS to know which column is which in
the result set.
</p></div>
<div class="example-contents"><p>
Note that the
<var class="varname"><var class="varname">co_id</var></var>
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.
</p></div>
<div class="example-contents"><p>
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.
</p></div>
<div class="example-contents"><p>
In this way the Relational DAS can retrieve and renormalise data
for multiple companies and multiple departments underneath them.
</p></div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">require_once </span><span style="color: #DD0000">'SDO/DAS/Relational.php'</span><span style="color: #007700">;<br />require_once </span><span style="color: #DD0000">'company_metadata.inc.php'</span><span style="color: #007700">;<br /><br /></span><span style="color: #FF8000">/**************************************************************<br />* Retrieve the company and Shoe department, then delete Shoe and add IT<br />***************************************************************/<br /></span><span style="color: #0000BB">$dbh </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO_DSN</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_USER</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_PASSWORD</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">= new </span><span style="color: #0000BB">SDO_DAS_Relational </span><span style="color: #007700">(</span><span style="color: #0000BB">$database_metadata</span><span style="color: #007700">,</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,</span><span style="color: #0000BB">$SDO_containment_metadata</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$root </span><span style="color: #007700">= </span><span style="color: #0000BB">$das</span><span style="color: #007700">-></span><span style="color: #0000BB">executeQuery</span><span style="color: #007700">(</span><span style="color: #0000BB">$dbh</span><span style="color: #007700">,<br /></span><span style="color: #DD0000">'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id'</span><span style="color: #007700">,<br />array(</span><span style="color: #DD0000">'company.id'</span><span style="color: #007700">,</span><span style="color: #DD0000">'company.name'</span><span style="color: #007700">,</span><span style="color: #DD0000">'department.id'</span><span style="color: #007700">,</span><span style="color: #DD0000">'department.name'</span><span style="color: #007700">));<br /><br /></span><span style="color: #0000BB">$acme </span><span style="color: #007700">= </span><span style="color: #0000BB">$root</span><span style="color: #007700">[</span><span style="color: #DD0000">'company'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">]; </span><span style="color: #FF8000">// get the first company - will be 'Acme'<br /></span><span style="color: #0000BB">$shoe </span><span style="color: #007700">= </span><span style="color: #0000BB">$acme</span><span style="color: #007700">[</span><span style="color: #DD0000">'department'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">]; </span><span style="color: #FF8000">// get the first department underneath - will be 'Shoe'<br /><br /></span><span style="color: #007700">unset(</span><span style="color: #0000BB">$acme</span><span style="color: #007700">[</span><span style="color: #DD0000">'department'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">]);<br /><br /></span><span style="color: #0000BB">$it </span><span style="color: #007700">= </span><span style="color: #0000BB">$acme</span><span style="color: #007700">-></span><span style="color: #0000BB">createDataObject</span><span style="color: #007700">(</span><span style="color: #DD0000">'department'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$it</span><span style="color: #007700">-></span><span style="color: #0000BB">name </span><span style="color: #007700">= </span><span style="color: #DD0000">'IT'</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">-> </span><span style="color: #0000BB">applyChanges</span><span style="color: #007700">(</span><span style="color: #0000BB">$dbh</span><span style="color: #007700">, </span><span style="color: #0000BB">$root</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-5473">
<p><strong>Example #3 One company, two departments - Retrieve and Delete</strong></p>
<div class="example-contents"><p>
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.
</p></div>
<div class="example-contents"><p>
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:
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">unset(</span><span style="color: #0000BB">$root</span><span style="color: #007700">[</span><span style="color: #DD0000">'company'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">]);<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
and not:
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">unset(</span><span style="color: #0000BB">$acme</span><span style="color: #007700">); </span><span style="color: #FF8000">//WRONG<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
Simply unsetting
<var class="varname"><var class="varname">$acme</var></var>
would destroy the variable but leave the data in the data
graph untouched.
</p></div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">require_once </span><span style="color: #DD0000">'SDO/DAS/Relational.php'</span><span style="color: #007700">;<br />require_once </span><span style="color: #DD0000">'company_metadata.inc.php'</span><span style="color: #007700">;<br /><br /></span><span style="color: #FF8000">/**************************************************************<br />* Retrieve the company and IT department, then delete the whole company<br />***************************************************************/<br /></span><span style="color: #0000BB">$dbh </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO_DSN</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_USER</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_PASSWORD</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">= new </span><span style="color: #0000BB">SDO_DAS_Relational </span><span style="color: #007700">(</span><span style="color: #0000BB">$database_metadata</span><span style="color: #007700">,</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,</span><span style="color: #0000BB">$SDO_containment_metadata</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$root </span><span style="color: #007700">= </span><span style="color: #0000BB">$das</span><span style="color: #007700">-></span><span style="color: #0000BB">executeQuery</span><span style="color: #007700">(</span><span style="color: #0000BB">$dbh</span><span style="color: #007700">,<br /></span><span style="color: #DD0000">'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id'</span><span style="color: #007700">,<br />array(</span><span style="color: #DD0000">'company.id'</span><span style="color: #007700">,</span><span style="color: #DD0000">'company.name'</span><span style="color: #007700">,</span><span style="color: #DD0000">'department.id'</span><span style="color: #007700">,</span><span style="color: #DD0000">'department.name'</span><span style="color: #007700">));<br /><br /></span><span style="color: #0000BB">$acme </span><span style="color: #007700">= </span><span style="color: #0000BB">$root</span><span style="color: #007700">[</span><span style="color: #DD0000">'company'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">];<br /></span><span style="color: #0000BB">$it </span><span style="color: #007700">= </span><span style="color: #0000BB">$acme</span><span style="color: #007700">[</span><span style="color: #DD0000">'department'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">];<br /><br />unset(</span><span style="color: #0000BB">$root</span><span style="color: #007700">[</span><span style="color: #DD0000">'company'</span><span style="color: #007700">][</span><span style="color: #0000BB">0</span><span style="color: #007700">]);<br /><br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">-> </span><span style="color: #0000BB">applyChanges</span><span style="color: #007700">(</span><span style="color: #0000BB">$dbh</span><span style="color: #007700">, </span><span style="color: #0000BB">$root</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="sdodasrel.examples.one-table.html">One-table examples</a></div>
<div class="next" style="text-align: right; float: right;"><a href="sdodasrel.examples.three-table.html">Three-table example</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>
