PageRenderTime 65ms CodeModel.GetById 51ms app.highlight 5ms RepoModel.GetById 1ms app.codeStats 1ms

/en/reference/sdodasrel/examples.xml

https://github.com/ifeghali/phpdocs
XML | 1161 lines | 1034 code | 106 blank | 21 comment | 0 complexity | 53b3ab71def18b1eef05fe18901a6253 MD5 | raw file
   1<?xml version="1.0" encoding="utf-8"?>
   2<!-- $Revision: 1.4 $ -->
   3
   4<chapter xml:id="sdo.das.rel.examples" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink">
   5 &reftitle.examples;
   6 <section xml:id='sdo.das.rel.examples-crud'>
   7  <title>Creating, retrieving, updating and deleting data</title>
   8  <para>
   9   This section illustrates how the Relational DAS can be used to create, 
  10   retrieve, update and delete data in a relational database.
  11   Many of the examples are illustrated with a three-table database that 
  12   contains companies, departments within those companies, and employees 
  13   that work in those departments. This example is used in a number of 
  14   places within the SDO literature. See the examples section of the
  15   <link xlink:href='&url.ibm.sdo.spec;'>Service Data Objects specification</link>
  16   or the
  17   <link linkend="sdo.examples">Examples</link>
  18   section of the documentation for the SDO extension.
  19  </para>
  20
  21  <para>
  22   The Relational DAS is constructed with metadata that defines the 
  23   relational database and how it should be mapped to SDO.
  24   The long section that follows describes this metadata and how to 
  25   construct the Relational DAS. The examples that follow it all assume that
  26   this metadata is in an included php file.
  27  </para>
  28
  29  <para>
  30   The examples below and others can all be found in the
  31   <filename>Scenarios</filename>
  32   directory in the Relational DAS package.
  33  </para>
  34
  35  <para>
  36   The Relational DAS throws exceptions in the event that it finds errors 
  37   in the metadata or errors when executing SQL statements against the 
  38   database. For brevity the examples below all omit the use of try/catch 
  39   blocks around the calls to the Relational DAS.
  40  </para>
  41
  42  <para>
  43   These examples all differ from the expected use of SDO in two
  44   important respects.
  45  </para>
  46  <para>
  47   First, they show all interactions with the database completed within 
  48   one script. In this respect these scenarios are not realistic but are 
  49   chosen to illustrate just the use of the Relational DAS.
  50   It is expected that interactions with the database will be separated 
  51   in time and the data graph serialized and deserialized into the PHP 
  52   session one or more times as the application interacts with an end user.
  53  </para>
  54  <para>
  55   Second, all queries executed against the database use hard-coded 
  56   queries with no variables substituted. In this case it is safe to 
  57   use the simple 
  58   <function>executeQuery</function>
  59   call, and this is what the examples illustrate. 
  60   In practice, though, it is unlikely that the SQL statement is known 
  61   entirely ahead of time. In order to allow variables to be safely 
  62   substituted into the SQL queries, without running the risk of 
  63   injecting SQL with unknown effects, it is safer to use the 
  64   <function>executePreparedQuery</function>
  65   which takes a prepared SQL statement containing placeholders
  66   and a list of values to be substituted. 
  67  </para>
  68 </section>
  69
  70 <section xml:id='sdo.das.rel.metadata'>
  71  <title>Specifying the metadata</title>
  72  <para>
  73   This first long section describes in detail how the metadata describing 
  74   the database and the required SDO model is supplied to the 
  75   Relational DAS.
  76  </para>
  77  <para>
  78   When the constructor for the Relational DAS is invoked, it needs to be 
  79   passed several pieces of information. The bulk of the information, 
  80   passed as an associative array in the first argument to the constructor, 
  81   tells the Relational DAS what it needs to know about the relational 
  82   database. It describes the names of the tables, columns, primary keys 
  83   and foreign keys. It should be fairly easy to understand what is 
  84   required, and once written it can be placed in a php file and included 
  85   when needed. The remainder of the information, passed in the second 
  86   and third arguments to the constructor, tells the Relational DAS what 
  87   it needs to know about the relationships between objects and the shape 
  88   of the data graph; it ultimately determines how the data from the 
  89   database is to be normalized into a graph.
  90  </para>
  91
  92  <section xml:id='sdo.das.rel.metadata.database'>
  93   <title>Database metadata</title>
  94   <para>
  95    The first argument to the constructor describes the target 
  96    relational database.
  97   </para>
  98
  99   <para>
 100    Each table is described by an associative array with up to four keys.
 101    <informaltable>
 102     <tgroup cols='2'>
 103      <thead>
 104       <row>
 105        <entry>Key</entry>
 106        <entry>Value</entry>
 107       </row>
 108      </thead>
 109      <tbody>
 110       <row>
 111        <entry>name</entry>
 112        <entry>The name of the table.</entry>
 113       </row>
 114       <row>
 115        <entry>columns</entry>
 116        <entry>
 117         An array listing the names of the columns, in any order.
 118        </entry>
 119       </row>
 120       <row>
 121        <entry>PK</entry>
 122        <entry>The name of the column containing the primary key.</entry>
 123       </row>
 124       <row>
 125        <entry>FK</entry>
 126        <entry>An array with two entries, 'from' and 'to', which define 
 127         a column containing a foreign key, and a table to which the foreign
 128         key points. If there are no foreign keys in the table then the 
 129         'FK' entry does not need to be specified. Only one foreign key 
 130         can be specified. Only a foreign key pointing to the primary key 
 131         of a table can be specified.
 132        </entry>
 133       </row>
 134      </tbody>
 135     </tgroup>
 136    </informaltable>
 137   </para>
 138
 139   <programlisting role="php">
 140<![CDATA[
 141<?php
 142/*****************************************************************
 143* METADATA DEFINING THE DATABASE
 144******************************************************************/
 145$company_table = array (
 146  'name' => 'company',
 147  'columns' => array('id', 'name',  'employee_of_the_month'),
 148  'PK' => 'id',
 149  'FK' => array (
 150      'from' => 'employee_of_the_month',
 151      'to' => 'employee',
 152      ),
 153  );
 154$department_table = array (
 155  'name' => 'department', 
 156  'columns' => array('id', 'name', 'location', 'number', 'co_id'),
 157  'PK' => 'id',
 158  'FK' => array (
 159      'from' => 'co_id',
 160      'to' => 'company',
 161      )
 162  );
 163$employee_table = array (
 164  'name' => 'employee',
 165  'columns' => array('id', 'name', 'SN', 'manager', 'dept_id'),
 166  'PK' => 'id',
 167  'FK' => array (
 168      'from' => 'dept_id',
 169      'to' => 'department',
 170      )
 171  );
 172$database_metadata = array($company_table, $department_table, $employee_table);
 173?>
 174]]>
 175   </programlisting>
 176
 177   <para>
 178    This metadata corresponds to a relational database that might have 
 179    been defined to MySQL as:
 180   </para>
 181   <programlisting role="sql">
 182<![CDATA[
 183create table company (
 184 id integer auto_increment,
 185 name char(20),
 186 employee_of_the_month integer,
 187 primary key(id)
 188);
 189create table department (
 190 id integer auto_increment,
 191 name char(20),
 192 location char(10),
 193 number integer(3),
 194 co_id integer,
 195 primary key(id)
 196);
 197create table employee (
 198 id integer auto_increment,
 199 name char(20),
 200 SN char(4),
 201 manager tinyint(1),
 202 dept_id integer,
 203 primary key(id)
 204);
 205]]>
 206   </programlisting>
 207   <para>
 208   or to DB2 as:
 209   </para>
 210   <programlisting role="sql">
 211<![CDATA[
 212create table company ( \
 213  id integer not null generated by default as identity,  \
 214  name varchar(20), \
 215  employee_of_the_month integer, \
 216  primary key(id) )
 217create table department ( \
 218  id integer not null generated by default as identity, \
 219  name varchar(20), \
 220  location varchar(10), \
 221  number integer, \
 222  co_id integer, \
 223  primary key(id) )
 224create table employee ( \
 225  id integer not null generated by default as identity, \
 226  name varchar(20), \
 227  SN char(4), \
 228  manager smallint, \
 229  dept_id integer, \
 230  primary key(id) )
 231]]>
 232   </programlisting>
 233   
 234   <para>
 235    Note that although in this example there are no foreign keys specified 
 236    to the database and so the database is not expected to enforce 
 237    referential integrity, the intention behind the
 238    <varname>co_id</varname>
 239    column on the department table and the
 240    <varname>dept_id</varname>
 241    column on the employee table is they should contain the primary key 
 242    of their containing company or department record, respectively.
 243    So these two columns are acting as foreign keys.
 244   </para>
 245                  
 246   <para>
 247    There is a third foreign key in this example, that from the
 248    <varname>employee_of_the_month</varname>
 249    column of the company record to a single row of the employee table.
 250    Note the difference in intent between this foreign key and the other 
 251    two. The
 252    <varname>employee_of_the_month</varname>
 253    column represents a single-valued relationship: there can be only 
 254    one employee of the month for a given company.
 255    The
 256    <varname>co_id</varname>
 257    and
 258    <varname>dept_id</varname>
 259    columns represent multi-valued relationships: a company can contain 
 260    many departments and a department can contain many employees.
 261    This distinction will become evident when the remainder of the metadata 
 262    picks out the company-department and department-employee relationships 
 263    as containment relationships.
 264   </para>
 265
 266   <para>
 267    There are a few simple rules to be followed when constructing the 
 268    database metadata:
 269   </para>
 270
 271   <itemizedlist>
 272    <listitem>
 273     <para>
 274      All tables must have primary keys, and the primary keys must be
 275      specified in the metadata. Without primary keys it is not possible 
 276      to keep track of object identities. As you can see from the SQL 
 277      statements that create the tables, primary keys can be 
 278      auto-generated, that is, generated and assigned by the database when
 279      a record is inserted. In this case the auto-generated primary key 
 280      is obtained from the database and inserted into the data object 
 281      immediately after the row is inserted into the database.
 282     </para>
 283    </listitem>
 284
 285    <listitem>
 286     <para>
 287      It is not necessary to specify in the metadata all the columns 
 288      that exist in the database, only those that will be used. 
 289      For example, if the company table had another column that the 
 290      application did not want to access with SDO, this need not be 
 291      specified in the metadata. On the other hand it would have done 
 292      no harm to specify it: if specified in the metadata but never 
 293      retrieved, or assigned to by the application, then the unused column 
 294      will not affect anything.
 295     </para>
 296    </listitem>
 297
 298    <listitem>
 299     <para>
 300      In the database metadata note that the foreign key definitions 
 301      identify not the destination column in the table which is pointed 
 302      to, but the table name itself. Strictly, the relational model 
 303      permits the destination of a foreign key to be a non-primary key.
 304      Only foreign keys that point to a primary key are useful for 
 305      constructing the SDO model, so the metadata specifies the table name.
 306      It is understood that the foreign key points to the primary key of 
 307      the given table.
 308     </para>
 309    </listitem>
 310   </itemizedlist>
 311
 312   <para>
 313    Given these rules, and given the SQL statements that define the 
 314    database, the database metadata should be easy to construct.
 315   </para>
 316
 317   <section xml:id='sdo.das.rel.metadata.database.model'>
 318    <title>What the Relational DAS does with the metadata</title>
 319    <para>
 320     The Relational DAS uses the database metadata to form most of the 
 321     SDO model. For each table in the database metadata, an SDO type 
 322     is defined. Each column which can represent a primitive value 
 323     (columns which are not defined as foreign keys) are added 
 324     as properties to the SDO type.
 325    </para>
 326    <para>
 327     All primitive properties are given a type of string in the SDO model, 
 328     regardless of their SQL type. When writing values back to the 
 329     database the Relational DAS will create SQL statements that treat 
 330     the values as strings, and the database will convert them to the 
 331     appropriate type.
 332    </para>
 333
 334    <para>
 335     Foreign keys are interpreted in one of two ways, depending on the 
 336     metadata in the third argument to the constructor that defines
 337     the SDO containment relationships.
 338     A discussion of this is therefore deferred until the section on
 339     <link linkend="sdo.das.rel.metadata.crels">
 340      SDO containment relationships
 341     </link>
 342     below.
 343    </para>
 344   </section>
 345
 346   <section xml:id='sdo.das.rel.metadata.approottype'>
 347    <title>Specifying the application root type</title>
 348    <para>
 349     The second argument to the constructor is the application root type.
 350     The true root of each data graph is an object of a special root type 
 351     and all application data objects come somewhere below that. Of the 
 352     various application types in the SDO model, one has to be the 
 353     application type immediately below the root of the data graph.
 354     If there is only one table in the database metadata, the application 
 355     root type can be inferred, and this argument can be omitted.
 356    </para>
 357   </section>
 358
 359   <section xml:id='sdo.das.rel.metadata.crels'>
 360    <title>Specifying the SDO containment relationships</title>
 361
 362    <para>
 363     The third argument to the constructor defines how the types in the 
 364     model are to be linked together to form a graph. It identifies the 
 365     parent-child relationships between the types which collectively form a 
 366     graph. The relationships need to be supported by foreign keys to be 
 367     found in the data, in a way shortly to be described.
 368    </para>
 369
 370    <para>
 371     The metadata is an array containing one or more associative arrays, 
 372     each of which identifies a parent and a child. The example below shows 
 373     a parent-child relationship from company to department, and another 
 374     from department to employee. Each of these will become an SDO property 
 375     defining a multi-valued containment relationship in the SDO model.
 376
 377    </para>
 378    <programlisting role="php">
 379<![CDATA[
 380<?php
 381$department_containment = array( 'parent' => 'company', 'child' => 'department');
 382$employee_containment = array( 'parent' => 'department', 'child' => 'employee');
 383
 384$SDO_containment_metadata = array($department_containment, $employee_containment);           
 385?>
 386]]>
 387    </programlisting>
 388
 389    <para>
 390     Foreign keys in the database metadata are interpreted as properties 
 391     with either multi-valued containment relationships or single-valued 
 392     non-containment references, depending on whether they have a 
 393     corresponding SDO containment relationship specified in the metadata. 
 394     In the example here, the foreign keys from department to company (the 
 395     <varname>co_id</varname>
 396     column in the department table)
 397     and from employee to department (the
 398     <varname>dept_id</varname>
 399     column in the employee table) are interpreted as supporting the
 400     SDO containment relationships.
 401     Each containment relationship mentioned in the SDO containment relationships
 402     metadata must have a corresponding foreign key present in the
 403     database and defined in the database metadata. The values of the 
 404     foreign key columns for containment relationships do not appear in the 
 405     data objects, instead each is represented by a containment relationship 
 406     from the parent to the child. So the
 407     <varname>co_id</varname>
 408     column in the department row in the database, for example, does not 
 409     appear as a property on the department type, but instead as a 
 410     containment relationship called
 411     <varname>department</varname>
 412     on the company type.
 413     Note that the foreign key and the parent-child relationship appear to 
 414     have opposite senses: the foreign key points from the department to 
 415     the company, but the parent-child relationship points from company to 
 416     department.
 417    </para>
 418
 419    <para>
 420     The third foreign key in this example, the
 421     <varname>employee_of_the_month</varname>
 422     ,
 423     is handled differently.
 424     This is not mentioned in the SDO containment relationships metadata.
 425     As a consequence this is interpreted in the second way: it becomes 
 426     a single-valued non-containment reference on the company object, to
 427     which can be assigned references to SDO data objects of the employee 
 428     type. It does appear as a property on the company type. The way to 
 429     assign a value to it in the SDO data graph is to have a graph that 
 430     contains an employee object through the containment relationships, and 
 431     to assign the object to it. This is illustrated in the later examples 
 432     below.
 433    </para>
 434   </section>
 435  </section> <!--specifying the metadata sdo.das.rel.metadata.database.model -->
 436 </section> <!-- sdo.das.rel.metadata -->
 437
 438 <section xml:id='sdo.das.rel.examples.one-table'>
 439  <title>One-table examples</title>
 440  <para>
 441   The following set of examples all use the Relational DAS to work with 
 442   a data graph containing just one application data object, a single 
 443   company and the data just to be found the company table. These examples 
 444   do not exercise the power of SDO or the Relational DAS and of course 
 445   the same result could be achieved more economically with direct SQL 
 446   statements but they are intended to illustrate how to work with the 
 447   Relational DAS.
 448  </para>
 449
 450  <para>
 451   For this very simple scenario it would be possible to simplify the 
 452   database metadata to include just the company table - if that were done
 453   the second and third arguments to the constructor and the column 
 454   specifier used in the query example would become optional.
 455  </para>
 456
 457  <para>
 458   <example>
 459    <title>Creating a data object</title>
 460    <para>
 461     The simplest example is that of creating a single data object and 
 462     writing it to the database. In this example a single company object 
 463     is created, its name is set to 'Acme', and the Relational DAS is 
 464     called to write the changes to the database. The company name is 
 465     set here using the property name method. See the
 466     <link linkend="sdo.examples">Examples</link>
 467     section on the SDO extension for other ways of accessing the 
 468     properties of an object.
 469    </para>
 470
 471    <para>
 472     Data objects can only be created when you have a data object to 
 473     start with, however. It is for that reason that the first call 
 474     to the Relational DAS here is to obtain a root object. This is 
 475     in effect how to ask for an empty data graph - the special root 
 476     object is the true root of the tree. The company data object is 
 477     then created with a call to
 478     <function>createDataObject</function>
 479     on the root object. This creates the company data object and inserts 
 480     it in the graph by inserting into a multi-valued containment property 
 481     on the root object called 'company'.
 482    </para>
 483    <para>
 484     When the Relational DAS is called to apply the changes a simple 
 485     insert statement 'INSERT INTO company (name) VALUES ("Acme");' 
 486     will be constructed and executed. The auto-generated primary key 
 487     will be set into the data object and the change summary will be reset, 
 488     so that it would be possible to continue working with the same data 
 489     object, modify it, and apply the newer changes a second time.
 490    </para>
 491                      
 492    <programlisting role="php" xml:id="sdo.das.rel.examples.1c-C">
 493<![CDATA[
 494<?php
 495require_once 'SDO/DAS/Relational.php';
 496require_once 'company_metadata.inc.php';
 497
 498/**************************************************************
 499* Construct the DAS with the metadata
 500***************************************************************/
 501$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 502
 503/**************************************************************
 504* Obtain a root object and create a company object underneath.
 505* Make a simple change to the data object. 
 506***************************************************************/
 507$root = $das  -> createRootDataObject();
 508$acme = $root -> createDataObject('company');
 509
 510$acme->name = "Acme";
 511
 512/**************************************************************
 513* Get a database connection and write the object to the database
 514***************************************************************/
 515$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 516$das -> applyChanges($dbh, $root);
 517?>
 518]]>
 519    </programlisting>
 520   </example>
 521  </para>
 522
 523  <para>
 524   <example>
 525    <title>Retrieving a data object</title>
 526    <para>
 527     In this example a single data object is retrieved from the database 
 528     - or possibly more than one if there is more than one company 
 529     called 'Acme'. For each company returned, the
 530     <varname>name</varname>
 531     and
 532     <varname>id</varname>
 533     properties are echoed.
 534    </para>
 535    <para>
 536     In this example the third argument to
 537     <function>executeQuery</function>,
 538     the column specifier is needed as there are other tables in the 
 539     metadata with column names of
 540     <varname>name</varname>
 541     and
 542     <varname>id</varname>.
 543     If there were no possible ambiguity it could be omitted.
 544    </para>
 545    <programlisting role="php" xml:id="sdo.das.rel.examples.1c-R">
 546<![CDATA[
 547<?php
 548require_once 'SDO/DAS/Relational.php';
 549require_once 'company_metadata.inc.php';
 550
 551/**************************************************************
 552* Construct the DAS with the metadata
 553***************************************************************/
 554$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 555
 556/**************************************************************
 557* Get a database connection
 558***************************************************************/
 559$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 560
 561/**************************************************************
 562* Issue a query to obtain a company object - possibly more if they exist
 563***************************************************************/
 564$root = $das->executeQuery($dbh,
 565         'select name, id from company where name="Acme"',
 566          array('company.name', 'company.id') );
 567
 568/**************************************************************
 569* Echo name and id 
 570***************************************************************/
 571foreach ($root['company'] as $company) {
 572  echo "Company obtained from the database has name = " . 
 573  $company['name'] . " and id " . $company['id'] . "\n";
 574}
 575?>
 576]]>
 577    </programlisting>
 578   </example>
 579  </para>
 580
 581  <para>
 582   <example>
 583    <title>Updating a data object</title>
 584    <para>
 585     This example combines the previous two, in the sense that in order 
 586     to be updated the object must first be retrieved. The application 
 587     code reverses the company name (so 'Acme' becomes 'emcA') and then the
 588     changes are written back to the database in the same way that they 
 589     were when the object was created. Because the query searches for 
 590     the name both ways round the program can be run repeatedly to find 
 591     the company and reverse its name each time.
 592    </para>
 593    <para>
 594     In this example the same instance of the Relational DAS is reused 
 595     for the
 596     <function>applyChanges</function>,
 597     as is the PDO database handle. This is quite alright; it also 
 598     alright to allow the previous instances to be garbage collected 
 599     and to obtain new instances. No state data regarding the graph 
 600     is held the Relational DAS once it has returned a data graph to 
 601     the application. All necessary data is either within the graph itself, 
 602     or can be reconstructed from the metadata.
 603    </para>
 604    <programlisting role="php" xml:id="sdo.das.rel.examples.1c-RU">
 605<![CDATA[
 606<?php
 607require_once 'SDO/DAS/Relational.php';
 608require_once 'company_metadata.inc.php';
 609
 610/**************************************************************
 611* Construct the DAS with the metadata
 612***************************************************************/
 613$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 614
 615/**************************************************************
 616* Get a database connection
 617***************************************************************/
 618$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 619
 620/**************************************************************
 621* Issue a query to obtain a company object - possibly more if they exist
 622***************************************************************/
 623$root = $das->executeQuery($dbh,
 624  'select name, id from company where name="Acme" or name="emcA"',
 625  array('company.name', 'company.id') );
 626
 627/**************************************************************
 628* Alter the name of just the first company
 629***************************************************************/
 630$company = $root['company'][0];
 631echo "obtained a company with name of " . $company->name . "\n";
 632$company->name = strrev($company->name);
 633
 634/**************************************************************
 635* Write the change back
 636***************************************************************/
 637$das->applyChanges($dbh,$root);
 638?>
 639]]>
 640    </programlisting>
 641   </example>
 642  </para>
 643
 644  <para>
 645   <example>
 646    <title>Deleting a data object</title>
 647    <para>
 648     Any companies called 'Acme' or its reverse 'emcA' are retrieved.
 649     They are then all deleted from the graph with unset.
 650    </para>
 651
 652    <para>
 653     In this example they are all deleted in one go by unsetting the 
 654     containing property (the property defining the containment 
 655     relationship). It is also possible to delete them individually.
 656    </para>
 657    <programlisting role="php" xml:id="sdo.das.rel.examples.1c-RD">
 658<![CDATA[
 659<?php
 660require_once 'SDO/DAS/Relational.php';
 661require_once 'company_metadata.inc.php';
 662
 663/**************************************************************
 664* Construct the DAS with the metadata
 665***************************************************************/
 666$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 667
 668/**************************************************************
 669* Get a database connection
 670***************************************************************/
 671$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 672
 673/**************************************************************
 674* Issue a query to obtain a company object - possibly more if they exist
 675***************************************************************/
 676$root = $das->executeQuery($dbh,
 677  'select name, id from company where name="Acme" or name="emcA"',
 678  array('company.name', 'company.id') );
 679
 680/**************************************************************
 681* Delete any companies found from the data graph
 682***************************************************************/
 683unset($root['company']);
 684
 685/**************************************************************
 686* Write the change(s) back
 687***************************************************************/
 688$das->applyChanges($dbh,$root);
 689?>
 690]]>
 691    </programlisting>
 692   </example>
 693  </para>
 694
 695 </section> <!-- one-table -->
 696
 697 <section xml:id='sdo.das.rel.examples.two-table'>
 698  <title>Two-table examples</title>
 699  <para>
 700   The following set of examples all use two tables from the company 
 701   database: the company and department tables. These examples exercise 
 702   more of the function of the Relational DAS.
 703  </para>
 704  <para>
 705   In this series of examples a company and department are created, 
 706   retrieved, updated, and finally deleted. This illustrates the 
 707   lifecycle for a data graph containing more than one object. Note that 
 708   this example clears out the company and department tables at the start 
 709   so that the exact results of the queries can be known.
 710  </para>
 711  <para>
 712   You can find these examples combined into one script called
 713   <filename>1cd-CRUD</filename>
 714   in the
 715   <filename>Scenarios</filename>
 716   directory in the Relational DAS package.
 717  </para>
 718
 719  <para>
 720   <example>
 721    <title>One company, one department - Create</title>
 722    <para>
 723     As in the earlier example of creating just one company data object, 
 724     the first action after constructing the Relational DAS is to call
 725     <function>createRootDataObject</function>
 726     to obtain the special root object of the otherwise empty data graph.
 727     The company object is then created as a child of the root object, 
 728     and the department object as a child of the company object.
 729    </para>
 730    <para>
 731     When it comes to applying the changes, the Relational DAS has to 
 732     perform special processing to maintain the foreign keys that support 
 733     the containment relationships, especially if auto-generated primary 
 734     keys are involved. In this example, the relationship between the 
 735     auto-generated primary key 
 736     <varname>id</varname>
 737     in the company table and the
 738     <varname>co_id</varname>
 739     column in the department table must be maintained. When inserting a 
 740     company and department for the first time the Relational DAS has to 
 741     first insert the company row, then call PDO's
 742     <function>getLastInsertId</function>
 743     method to obtain the auto-generated primary key, then add that as 
 744     the value of the
 745     <varname>co_id</varname>
 746     column when inserting the department row.
 747    </para>
 748    <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-C">
 749<![CDATA[
 750<?php
 751require_once 'SDO/DAS/Relational.php';
 752require_once 'company_metadata.inc.php';
 753
 754/*************************************************************************************
 755* Empty out the two tables
 756*************************************************************************************/
 757$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 758$pdo_stmt = $dbh->prepare('DELETE FROM COMPANY;');
 759$rows_affected = $pdo_stmt->execute();
 760$pdo_stmt = $dbh->prepare('DELETE FROM DEPARTMENT;');
 761$rows_affected = $pdo_stmt->execute();
 762
 763/**************************************************************
 764* Create a company with name Acme and one department, the Shoe department
 765***************************************************************/
 766$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 767$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 768
 769$root = $das -> createRootDataObject();
 770
 771$acme = $root -> createDataObject('company');
 772$acme -> name = "Acme";
 773
 774$shoe = $acme->createDataObject('department');
 775$shoe->name = 'Shoe';
 776
 777$das -> applyChanges($dbh, $root);
 778
 779?>
 780]]>
 781    </programlisting>
 782   </example>
 783  </para>
 784
 785  <para>
 786   <example>
 787    <title>One company, one department - Retrieve and Update</title>
 788
 789    <para>
 790     In this case the SQL query passed to
 791     <function>executeQuery</function>
 792     performs an inner join to join the data from the company 
 793     and department tables. Primary keys for both the company and 
 794     department tables must be included in the query. The result set 
 795     is re-normalised to form a normalised data graph. Note that a 
 796     column specifier is passed as the third argument to the 
 797     <function>executeQuery</function>
 798     call enabling the Relational DAS to know which column is which in 
 799     the result set.
 800    </para>
 801    <para>
 802     Note that the
 803     <varname>co_id</varname>
 804     column although used in the query is not needed in the result set.
 805     In order to understand what the Relational DAS is doing when it builds 
 806     the data graph it may be helpful to visualise what the result set 
 807     looks like. Although the data in the database is normalised, so that 
 808     multiple department rows can point through their foreign key to one 
 809     company row, the data in the result set is non-normalised: that is, 
 810     if there is one company and multiple departments, the values for the 
 811     company are repeated in each row. The Relational DAS has to reverse 
 812     this process and turn the result set back into a normalised data graph, 
 813     with just one company object.
 814    </para>
 815    <para>
 816     In this example the Relational DAS will examine the result set and 
 817     column specifier, find data for both the company and department 
 818     tables, find primary keys for both, and interpret each row as 
 819     containing data for a department and its parent company. If it has 
 820     not seen data for that company before (it uses the primary key to 
 821     check) it creates a company object and then a department object 
 822     underneath it. If it has seen data for that company before and 
 823     has already created the company object it just creates the 
 824     department object underneath.
 825    </para>
 826
 827    <para>
 828     In this way the Relational DAS can retrieve and renormalise data 
 829     for multiple companies and multiple departments underneath them.
 830    </para>
 831
 832    <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-RU">
 833<![CDATA[
 834<?php
 835require_once 'SDO/DAS/Relational.php';
 836require_once 'company_metadata.inc.php';
 837
 838/**************************************************************
 839* Retrieve the company and Shoe department, then delete Shoe and add IT
 840***************************************************************/
 841$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 842$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 843
 844$root = $das->executeQuery($dbh,
 845'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id',
 846array('company.id','company.name','department.id','department.name'));
 847
 848$acme = $root['company'][0];            // get the first company - will be 'Acme'
 849$shoe = $acme['department'][0];         // get the first department underneath - will be 'Shoe'
 850
 851unset($acme['department'][0]);
 852
 853$it = $acme->createDataObject('department');
 854$it->name = 'IT';
 855
 856$das -> applyChanges($dbh, $root);
 857?>
 858]]>
 859    </programlisting>
 860   </example>
 861  </para>
 862
 863  <para>
 864   <example>
 865    <title>One company, two departments - Retrieve and Delete</title>
 866    <para>
 867     In this example the company and department are retrieved and 
 868     then deleted. It is not necessary to delete them individually 
 869     (although that would be possible) - deleting the company object 
 870     from the data graph also deletes any departments underneath it.
 871    </para>
 872    <para>
 873     Note the way that the company object is actually deleted using the 
 874     PHP unset call. The unset has to be performed on the containing 
 875     property which in this case is 
 876     the company property on the special 
 877     root object. You must use:
 878     <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-CRUD.good-delete">
 879<![CDATA[
 880<?php
 881unset($root['company'][0]);
 882?>
 883]]>
 884     </programlisting>
 885     and not:
 886     <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-CRUD.bad-delete">
 887<![CDATA[
 888<?php
 889unset($acme); //WRONG
 890?>
 891]]>
 892     </programlisting>
 893     Simply unsetting
 894     <varname>$acme</varname>
 895     would destroy the variable but leave the data in the data 
 896     graph untouched.
 897    </para>
 898
 899    <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-RD">
 900<![CDATA[
 901<?php
 902require_once 'SDO/DAS/Relational.php';
 903require_once 'company_metadata.inc.php';
 904
 905/**************************************************************
 906* Retrieve the company and IT department, then delete the whole company
 907***************************************************************/
 908$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 909$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
 910
 911$root = $das->executeQuery($dbh,
 912'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id',
 913array('company.id','company.name','department.id','department.name'));
 914
 915$acme = $root['company'][0];
 916$it = $acme['department'][0];
 917
 918unset($root['company'][0]);
 919
 920$das -> applyChanges($dbh, $root);
 921
 922?>
 923]]>
 924    </programlisting>
 925   </example>
 926  </para>
 927 </section>
 928
 929 <section xml:id='sdo.das.rel.examples.three-table'>
 930  <title>Three-table example</title>
 931  <para>
 932   The following examples use all three tables from the company database: 
 933   the company, department, and employee tables. These introduce the final 
 934   piece of function not exercised by the examples above: the 
 935   non-containment reference
 936   <varname>employee_of_the_month</varname>.
 937  </para>
 938  <para>
 939   Like the examples above for company and department, this set of examples 
 940   is intended to illustrate the full lifecycle of such a data graph.
 941  </para>
 942
 943
 944  <para>
 945   <example>
 946    <title>One company, one department, one employee - Create</title>
 947    <para>
 948     In this example a company is created containing one department and 
 949     just one employee. Note that this example clears out all three tables 
 950     at the start so that the exact results of the queries can be known.
 951    </para>
 952    <para>
 953     Note how once the company, department and employee have been created, 
 954     the
 955     <varname>employee_of_the_month</varname>
 956     property of the company can be made to point at the new employee.
 957     As this is a non-containment reference, this cannot be done until 
 958     the employee object has been created within the graph.
 959     Non-containment references need to be managed carefully.
 960     For example if the employee were now deleted from under the department, 
 961     it would not be correct to try to save the graph without
 962     first clearing or re-assigning the
 963     <varname>employee_of_the_month</varname>
 964     property.
 965     The closure rule for SDO data graphs requires that any object pointed 
 966     at by a non-containment reference must also be reachable by 
 967     containment relationships.
 968    </para>
 969    <para>
 970     When it comes to inserting the graph into the database, the procedure 
 971     is similar to the example of inserting the company and department,
 972     but
 973     <varname>employee_of_the_month</varname>
 974     introduces an extra complexity.
 975     The Relational DAS needs to insert the objects working down the tree 
 976     formed by containment relationships, so company, then department, then 
 977     employee. This is necessary so that it always has the auto-generated 
 978     primary key of a parent on hand to include in a child row. But when 
 979     the company row is inserted the employee who is employee of the month 
 980     has not yet been inserted and the primary key is not known. The 
 981     procedure is that after the employee record is inserted and its 
 982     primary key known, a final step is performed in which the the 
 983     company record is updated with the employee's primary key.
 984    </para>
 985    <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-C">
 986<![CDATA[
 987<?php
 988require_once 'SDO/DAS/Relational.php';
 989require_once 'company_metadata.inc.php';
 990
 991/*************************************************************************************
 992* Empty out the three tables
 993*************************************************************************************/
 994$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
 995$pdo_stmt = $dbh->prepare('DELETE FROM COMPANY;');
 996$rows_affected = $pdo_stmt->execute();
 997$pdo_stmt = $dbh->prepare('DELETE FROM DEPARTMENT;');
 998$rows_affected = $pdo_stmt->execute();
 999$pdo_stmt = $dbh->prepare('DELETE FROM EMPLOYEE;');
1000$rows_affected = $pdo_stmt->execute();
1001
1002/*************************************************************************************
1003* Create a tiny but complete company.
1004* The company name is Acme.
1005* There is one department, Shoe.
1006* There is one employee, Sue.
1007* The employee of the month is Sue.
1008*************************************************************************************/
1009$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
1010$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
1011
1012$root             = $das  -> createRootDataObject();
1013$acme             = $root -> createDataObject('company');
1014$acme -> name     = "Acme";
1015$shoe             = $acme -> createDataObject('department');
1016$shoe -> name     = 'Shoe';
1017$shoe -> location = 'A-block';
1018$sue              = $shoe -> createDataObject('employee');
1019$sue -> name      = 'Sue';
1020$acme -> employee_of_the_month = $sue;
1021
1022$das -> applyChanges($dbh, $root);
1023
1024echo "Wrote back Acme with one department and one employee\n";
1025?>
1026]]>
1027    </programlisting>
1028   </example>
1029  </para>
1030
1031  <para>
1032   <example>
1033    <title>One company, one department, one employee - Retrieve and update</title>
1034    <para>
1035     The SQL statement passed to the Relational DAS is this time an inner 
1036     join that retrieves data from all three tables. Otherwise this example 
1037     introduces nothing that has not appeared in a previous example. 
1038    </para>
1039    <para>
1040     The graph is updated by the addition of a new department and employee 
1041     and some alterations to the name properties of the existing objects 
1042     in the graph. The combined changes are then written back. The 
1043     Relational DAS will process and apply an arbitrary mixture of 
1044     additions, modifications and deletions to and from the data graph.
1045    </para>
1046    <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-RU">
1047<![CDATA[
1048<?php
1049require_once 'SDO/DAS/Relational.php';
1050require_once 'company_metadata.inc.php';
1051
1052/*************************************************************************************
1053* Find the company again and change various aspects.
1054* Change the name of the company, department and employee.
1055* Add a second department and a new employee.
1056* Change the employee of the month.
1057*************************************************************************************/
1058$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
1059$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
1060
1061$root = $das->executeQuery($dbh,
1062  "select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " .
1063  "from company c, department d, employee e " .
1064  "where e.dept_id = d.id and d.co_id = c.id and c.name='Acme'",
1065   array('company.id','company.name','company.employee_of_the_month',
1066   'department.id','department.name','employee.id','employee.name'));
1067$acme         = $root['company'][0];
1068
1069$shoe         = $acme->department[0];
1070$sue          = $shoe -> employee[0];
1071
1072$it           = $acme->createDataObject('department');
1073$it->name     = 'IT';
1074$it->location = 'G-block';
1075$billy        = $it->createDataObject('employee');
1076$billy->name  = 'Billy';
1077
1078$acme->name   = 'MegaCorp';
1079$shoe->name   = 'Footwear';
1080$sue->name    = 'Susan';
1081
1082$acme->employee_of_the_month = $billy;
1083$das -> applyChanges($dbh, $root);
1084echo "Wrote back company with extra department and employee and all the names changed (Megacorp/Footwear/Susan)\n";
1085
1086?>
1087]]>
1088    </programlisting>
1089   </example>
1090  </para>
1091
1092  <para>
1093   <example>
1094    <title>One company, two departments, two employees - Retrieve and delete</title>
1095    <para>
1096     The company is retrieved as a complete data graph containing five 
1097     data objects - the company, two departments and two employees.
1098     They are all deleted by deleting the company object. Deleting an 
1099     object from the graph deletes all the object beneath it in the graph.
1100     Five SQL DELETE statements will be generated and  executed. As always 
1101     they will be qualified with a WHERE clause that contains all of the 
1102     fields that were retrieved, so that any updates to the data in the 
1103     database in the meantime by another process will be detected.
1104    </para>
1105    <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-RD">
1106<![CDATA[
1107<?php
1108require_once 'SDO/DAS/Relational.php';
1109require_once 'company_metadata.inc.php';
1110
1111/*************************************************************************************
1112* Now read it one more time and delete it.
1113* You can delete part, apply the changes, then carry on working with the same graph but
1114* care is needed to keep closure - you cannot delete the employee who is eotm without
1115* reassigning. For safety here we delete the company all in one go. 
1116*************************************************************************************/
1117$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
1118$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
1119
1120$root = $das->executeQuery($dbh,
1121  "select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " .
1122  "from company c, department d, employee e " .
1123  "where e.dept_id = d.id and d.co_id = c.id and c.name='MegaCorp';",
1124   array('company.id','company.name','company.employee_of_the_month',
1125   'department.id','department.name','employee.id','employee.name'));
1126$megacorp = $root['company'][0];
1127
1128unset($root['company']);
1129$das -> applyChanges($dbh, $root);
1130
1131echo "Deleted the company, departments and employees all in one go.\n";
1132
1133?>
1134]]>
1135    </programlisting>
1136   </example>
1137  </para>
1138 </section>
1139</chapter>
1140
1141<!-- Keep this comment at the end of the file
1142Local variables:
1143mode: sgml
1144sgml-omittag:t
1145sgml-shorttag:t
1146sgml-minimize-attributes:nil
1147sgml-always-quote-attributes:t
1148sgml-indent-step:1
1149sgml-indent-data:t
1150indent-tabs-mode:nil
1151sgml-parent-document:nil
1152sgml-default-dtd-file:"../../../manual.ced"
1153sgml-exposed-tags:nil
1154sgml-local-catalogs:nil
1155sgml-local-ecat-files:nil
1156End:
1157vim600: syn=xml fen fdm=syntax fdl=2 si
1158vim: et tw=78 syn=sgml
1159vi: ts=1 sw=1
1160-->
1161