cvs: phpdoc /en/reference/sdodasrel book.xml constants.xml examples.xml limitations.xml reference.xml setup.xml

From: Date: Wed, 26 Dec 2007 00:07:09 +0000
Subject: cvs: phpdoc /en/reference/sdodasrel book.xml constants.xml examples.xml limitations.xml reference.xml setup.xml
Groups: php.doc.cvs 
Request: Send a blank email to doc-cvs+get-807@lists.php.net to get a copy of this message
bjori Wed Dec 26 00:07:09 2007 UTC Modified files: /phpdoc/en/reference/sdodasrel book.xml examples.xml limitations.xml setup.xml constants.xml reference.xml Log: MFB: Upgrade to the new-reference-structure - (Created missing setup sections in setup.xml, if any) - Moved the intro to book.xml - Changed the intro ID from <extname>.intro to intro.<extname> - Moved (and enabled) the constants entity to book.xml - Changed constants.xml to be an appendix - Moved the examples into its own chapter (examples.xml) NOTE: The sdo.das.rel.examples section was renamed to sdo.das.rel.examples-crud - Moved sdo.das.rel.limitations into its own chapter (limitations.xml) - Moved the installation & requirements sections to setup.xml NOTE: Removed the dots from the IDs

http://cvs.php.net/viewvc.cgi/phpdoc/en/reference/sdodasrel/book.xml?r1=1.1&r2=1.2&diff_format=u Index: phpdoc/en/reference/sdodasrel/book.xml diff -u /dev/null phpdoc/en/reference/sdodasrel/book.xml:1.2 --- /dev/null Wed Dec 26 00:07:09 2007 +++ phpdoc/en/reference/sdodasrel/book.xml Wed Dec 26 00:07:08 2007 @@ -0,0 +1,162 @@ +<?xml version="1.0" encoding="utf-8"?> +<!-- $Revision: 1.2 $ --> +<!-- Purpose: database.abstract --> +<!-- Membership: pecl --> +<!-- State: experimental --> + +<book xml:id="book.sdodasrel" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink"> + <title>SDO Relational Data Access Service</title> + + <!-- {{{ preface --> + <preface xml:id="intro.sdodasrel"> + &reftitle.intro; + <para> + <!-- This warns that the extension is experimental --> + &warn.experimental; + </para> + <para> + In order to use the Relational Data Access Service for + Service Data Objects, + you will need to understand some of the concepts behind SDO: + the data graph, the data object, the disconnected way of working, + the change summary, XPath and property expressions, and so on. + If you are not familiar with these ideas, you might want to look first at + <link linkend='ref.sdo'>the section on SDO</link>. + In addition, the Relational DAS makes use of the PDO extension to + isolate itself from specifics of different back-end relational databases. + In order to use the Relational DAS you will need to be able to + create and pass a PDO database connection; + for this reason you might also want to take a look at + <link linkend='ref.pdo'>the section on PDO</link>. + </para> + + <para> + The job of the Relational DAS is to move data between the application + and a relational database. In order to do this it needs to be told + the mapping between the database entities + - tables, columns, primary keys and foreign keys - + and the elements of the SDO model + - types, properties, containment relationships and so on. + You specify this information as metadata when you + construct the Relational DAS. + </para> + + <procedure xml:id='overview'> + <title>Overview of Operation</title> + <step> + <para> + The first step is to call the Relational DAS's constructor, + passing the metadata that defines the mapping between + database and SDO model. + There are examples of this below. + </para> + </step> + + <step> + <para> + The next step might be to call the + <function>executeQuery</function> + or + <function>executePreparedQuery</function> + methods on the Relational DAS, passing either a literal SQL statement + for the DAS to prepare and execute, or a prepared statement with + placeholders and a list of values to be inserted. + You may also need to specify a small amount of metadata + about the query itself, so that the Relational DAS knows + exactly what columns will be returned from the database and + in what order. You will also need to pass a PDO database connection. + </para> + + <para> + The return value from + <function>executeQuery</function> + or + <function>executePreparedQuery</function> + is a normalised data graph containing all the data from the result set. + For a query that returns data obtained from a number of tables, + this graph will contain a number of data objects, + linked by SDO containment relationships. + There may also be SDO non-containment references within the data. + </para> + + <para> + Once the query has been executed and the data graph constructed, + there is no need for either that instance of the the Relational DAS or + the database connection. There are no locks held on the database. + Both the Relational DAS and the PDO database connection can be + garbage collected. + </para> + </step> + <step> + <para> + Quite possibly the data in the data graph will go through + a number of modifications. The data graph can be serialised + into the PHP session and so may have a lifetime beyond just + one client-server interaction. Data objects can be created + and added to the graph, the data objects already in the graph + can be deleted, and data objects in the graph can be modified. + </para> + </step> + <step> + <para> + Finally, the changes made to the data graph can be applied + back to the database using the + <function>applyChanges</function> + method of the Relational DAS. For this, another instance + of the Relational DAS must be constructed, using the + same metadata, and another connection to the database obtained. + The connection and the data graph are passed to + <function>applyChanges</function>. + At this point the Relational DAS examines the change summary + and generates the necessary INSERT, UPDATE and DELETE SQL statements + to apply the changes. Any UPDATE and DELETE statements are qualified + with the original values of the data so that should the data have + changed in the database in the meantime this will be detected. + Assuming no such collisions have occurred the changes will be + committed to the database. The application can then continue to work + with the data graph, make more changes and apply them, or can discard + it. + </para> + </step> + </procedure> + <para> + There are other ways of working with the data in the database: + it is possible to just create data objects and write them to the + database without a preliminary call to + <function>executeQuery</function>, + for example. This scenario and others are explored in the + <link linkend="sdo.das.rel.examples">Examples</link> + section below. + </para> + </preface> + <!-- }}} --> + + &reference.sdodasrel.setup; + &reference.sdodasrel.constants; + &reference.sdodasrel.examples; + &reference.sdodasrel.limitations; + &reference.sdodasrel.reference; + +</book> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +indent-tabs-mode:nil +sgml-parent-document:nil +sgml-default-dtd-file:"../../../manual.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:nil +sgml-local-ecat-files:nil +End: +vim600: syn=xml fen fdm=syntax fdl=2 si +vim: et tw=78 syn=sgml +vi: ts=1 sw=1 +--> + http://cvs.php.net/viewvc.cgi/phpdoc/en/reference/sdodasrel/examples.xml?r1=1.1&r2=1.2&diff_format=u Index: phpdoc/en/reference/sdodasrel/examples.xml diff -u /dev/null phpdoc/en/reference/sdodasrel/examples.xml:1.2 --- /dev/null Wed Dec 26 00:07:09 2007 +++ phpdoc/en/reference/sdodasrel/examples.xml Wed Dec 26 00:07:08 2007 @@ -0,0 +1,1161 @@ +<?xml version="1.0" encoding="utf-8"?> +<!-- $Revision: 1.2 $ --> + +<chapter xml:id="sdo.das.rel.examples" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink"> + &reftitle.examples; + <section xml:id='sdo.das.rel.examples-crud'> + <title>Creating, retrieveing, updating and deleting data</title> + <para> + This section illustrates how the Relational DAS can be used to create, + retrieve, update and delete data in a relational database. + Many of the examples are illustrated with a three-table database that + contains companies, departments within those companies, and employees + that work in those departments. This example is used in a number of + places within the SDO literature. See the examples section of the + <link xlink:href='&url.ibm.sdo.spec;'>Service Data Objects specification</link> + or the + <link linkend="sdo.examples">Examples</link> + section of the documentation for the SDO extension. + </para> + + <para> + The Relational DAS is constructed with metadata that defines the + relational database and how it should be mapped to SDO. + The long section that follows describes this metadata and how to + construct the Relational DAS. The examples that follow it all assume that + this metadata is in an included php file. + </para> + + <para> + The examples below and others can all be found in the + <filename>Scenarios</filename> + directory in the Relational DAS package. + </para> + + <para> + The Relational DAS throws exceptions in the event that it finds errors + in the metadata or errors when executing SQL statements against the + database. For brevity the examples below all omit the use of try/catch + blocks around the calls to the Relational DAS. + </para> + + <para> + These examples all differ from the expected use of SDO in two + important respects. + </para> + <para> + First, they show all interactions with the database completed within + one script. In this respect these scenarios are not realistic but are + chosen to illustrate just the use of the Relational DAS. + It is expected that interactions with the database will be separated + in time and the data graph serialised and deserialised into the PHP + session one or more times as the application interacts with an end user. + </para> + <para> + Second, all queries executed against the database use hard-coded + queries with no variables substituted. In this case it is safe to + use the simple + <function>executeQuery</function> + call, and this is what the examples illustrate. + In practice, though, it is unlikely that the SQL statement is known + entirely ahead of time. In order to allow variables to be safely + substituted into the SQL queries, without running the risk of + injecting SQL with unknown effects, it is safer to use the + <function>executePreparedQuery</function> + which takes a prepared SQL statement containing placeholders + and a list of values to be substituted. + </para> + </section> + + <section xml:id='sdo.das.rel.metadata'> + <title>Specifying the metadata</title> + <para> + This first long section describes in detail how the metadata describing + the database and the required SDO model is supplied to the + Relational DAS. + </para> + <para> + 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 normalised into a graph. + </para> + + <section xml:id='sdo.das.rel.metadata.database'> + <title>Database metadata</title> + <para> + The first argument to the constructor describes the target + relational database. + </para> + + <para> + Each table is described by an associative array with up to four keys. + <informaltable> + <tgroup cols='2'> + <thead> + <row> + <entry>Key</entry> + <entry>Value</entry> + </row> + </thead> + <tbody> + <row> + <entry>name</entry> + <entry>The name of the table.</entry> + </row> + <row> + <entry>columns</entry> + <entry> + An array listing the names of the columns, in any order. + </entry> + </row> + <row> + <entry>PK</entry> + <entry>The name of the column containing the primary key.</entry> + </row> + <row> + <entry>FK</entry> + <entry>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. + </entry> + </row> + </tbody> + </tgroup> + </informaltable> + </para> + + <programlisting role="php"> +<![CDATA[ +<?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); +?> +]]> + </programlisting> + + <para> + This metadata corresponds to a relational database that might have + been defined to MySQL as: + </para> + <programlisting role="sql"> +<![CDATA[ +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) +); +]]> + </programlisting> + <para> + or to DB2 as: + </para> + <programlisting role="sql"> +<![CDATA[ +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) ) +]]> + </programlisting> + + <para> + 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 + <varname>co_id</varname> + column on the department table and the + <varname>dept_id</varname> + 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. + </para> + + <para> + There is a third foreign key in this example, that from the + <varname>employee_of_the_month</varname> + 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 + <varname>employee_of_the_month</varname> + column represents a single-valued relationship: there can be only + one employee of the month for a given company. + The + <varname>co_id</varname> + and + <varname>dept_id</varname> + 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. + </para> + + <para> + There are a few simple rules to be followed when constructing the + database metadata: + </para> + + <itemizedlist> + <listitem> + <para> + 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. + </para> + </listitem> + + <listitem> + <para> + 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. + </para> + </listitem> + + <listitem> + <para> + 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. + </para> + </listitem> + </itemizedlist> + + <para> + Given these rules, and given the SQL statements that define the + database, the database metadata should be easy to construct. + </para> + + <section xml:id='sdo.das.rel.metadata.database.model'> + <title>What the Relational DAS does with the metadata</title> + <para> + 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. + </para> + <para> + 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. + </para> + + <para> + 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 + <link linkend="sdo.das.rel.metadata.crels"> + SDO containment relationships + </link> + below. + </para> + </section> + + <section xml:id='sdo.das.rel.metadata.approottype'> + <title>Specifying the application root type</title> + <para> + 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. + </para> + </section> + + <section xml:id='sdo.das.rel.metadata.crels'> + <title>Specifying the SDO containment relationships</title> + + <para> + 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. + </para> + + <para> + 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. + + </para> + <programlisting role="php"> +<![CDATA[ +<?php +$department_containment = array( 'parent' => 'company', 'child' => 'department'); +$employee_containment = array( 'parent' => 'department', 'child' => 'employee'); + +$SDO_containment_metadata = array($department_containment, $employee_containment); +?> +]]> + </programlisting> + + <para> + 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 + <varname>co_id</varname> + column in the department table) + and from employee to department (the + <varname>dept_id</varname> + 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 + <varname>co_id</varname> + 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 + <varname>department</varname> + 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. + </para> + + <para> + The third foreign key in this example, the + <varname>employee_of_the_month</varname> + , + 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. + </para> + </section> + </section> <!--specifying the metadata sdo.das.rel.metadata.database.model --> + </section> <!-- sdo.das.rel.metadata --> + + <section xml:id='sdo.das.rel.examples.one-table'> + <title>One-table examples</title> + <para> + The following set of examples all use the Relational DAS to work with + a data graph containing just one application data object, a single + company and the data just to be found the company table. These examples + do not exercise the power of SDO or the Relational DAS and of course + the same result could be achieved more economically with direct SQL + statements but they are intended to illustrate how to work with the + Relational DAS. + </para> + + <para> + For this very simple scenario it would be possible to simplify the + database metadata to include just the company table - if that were done + the second and third arguments to the constructor and the column + specifier used in the query example would become optional. + </para> + + <para> + <example> + <title>Creating a data object</title> + <para> + The simplest example is that of creating a single data object and + writing it to the database. In this example a single company object + is created, its name is set to 'Acme', and the Relational DAS is + called to write the changes to the database. The company name is + set here using the property name method. See the + <link linkend="sdo.examples">Examples</link> + section on the SDO extension for other ways of accessing the + properties of an object. + </para> + + <para> + Data objects can only be created when you have a data object to + start with, however. It is for that reason that the first call + to the Relational DAS here is to obtain a root object. This is + in effect how to ask for an empty data graph - the special root + object is the true root of the tree. The company data object is + then created with a call to + <function>createDataObject</function> + on the root object. This creates the company data object and inserts + it in the graph by inserting into a multi-valued containment property + on the root object called 'company'. + </para> + <para> + When the Relational DAS is called to apply the changes a simple + insert statement 'INSERT INTO company (name) VALUES ("Acme");' + will be constructed and executed. The auto-generated primary key + will be set into the data object and the change summary will be reset, + so that it would be possible to continue working with the same data + object, modify it, and apply the newer changes a second time. + </para> + + <programlisting role="php" xml:id="sdo.das.rel.examples.1c-C"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************** +* Construct the DAS with the metadata +***************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); + +/************************************************************** +* Obtain a root object and create a company object underneath. +* Make a simple change to the data object. +***************************************************************/ +$root = $das -> createRootDataObject(); +$acme = $root -> createDataObject('company'); + +$acme->name = "Acme"; + +/************************************************************** +* Get a database connection and write the object to the database +***************************************************************/ +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); +$das -> applyChanges($dbh, $root); +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>Retrieving a data object</title> + <para> + In this example a single data object is retrieved from the database + - or possibly more than one if there is more than one company + called 'Acme'. For each company returned, the + <varname>name</varname> + and + <varname>id</varname> + properties are echoed. + </para> + <para> + In this example the third argument to + <function>executeQuery</function>, + the column specifier is needed as there are other tables in the + metadata with column names of + <varname>name</varname> + and + <varname>id</varname>. + If there were no possible ambiguity it could be omitted. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1c-R"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************** +* Construct the DAS with the metadata +***************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); + +/************************************************************** +* Get a database connection +***************************************************************/ +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); + +/************************************************************** +* Issue a query to obtain a company object - possibly more if they exist +***************************************************************/ +$root = $das->executeQuery($dbh, + 'select name, id from company where name="Acme"', + array('company.name', 'company.id') ); + +/************************************************************** +* Echo name and id +***************************************************************/ +foreach ($root['company'] as $company) { + echo "Company obtained from the database has name = " . + $company['name'] . " and id " . $company['id'] . "\n"; +} +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>Updating a data object</title> + <para> + This example combines the previous two, in the sense that in order + to be updated the object must first be retrieved. The application + code reverses the company name (so 'Acme' becomes 'emcA') and then the + changes are written back to the database in the same way that they + were when the object was created. Because the query searches for + the name both ways round the program can be run repeatedly to find + the company and reverse its name each time. + </para> + <para> + In this example the same instance of the Relational DAS is reused + for the + <function>applyChanges</function>, + as is the PDO database handle. This is quite alright; it also + alright to allow the previous instances to be garbage collected + and to obtain new instances. No state data regarding the graph + is held the Relational DAS once it has returned a data graph to + the application. All necessary data is either within the graph itself, + or can be reconstructed from the metadata. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1c-RU"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************** +* Construct the DAS with the metadata +***************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); + +/************************************************************** +* Get a database connection +***************************************************************/ +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); + +/************************************************************** +* Issue a query to obtain a company object - possibly more if they exist +***************************************************************/ +$root = $das->executeQuery($dbh, + 'select name, id from company where name="Acme" or name="emcA"', + array('company.name', 'company.id') ); + +/************************************************************** +* Alter the name of just the first company +***************************************************************/ +$company = $root['company'][0]; +echo "obtained a company with name of " . $company->name . "\n"; +$company->name = strrev($company->name); + +/************************************************************** +* Write the change back +***************************************************************/ +$das->applyChanges($dbh,$root); +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>Deleting a data object</title> + <para> + Any companies called 'Acme' or its reverse 'emcA' are retrieved. + They are then all deleted from the graph with unset. + </para> + + <para> + In this example they are all deleted in one go by unsetting the + containing property (the property defining the containment + relationship). It is also possible to delete them individually. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1c-RD"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************** +* Construct the DAS with the metadata +***************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); + +/************************************************************** +* Get a database connection +***************************************************************/ +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); + +/************************************************************** +* Issue a query to obtain a company object - possibly more if they exist +***************************************************************/ +$root = $das->executeQuery($dbh, + 'select name, id from company where name="Acme" or name="emcA"', + array('company.name', 'company.id') ); + +/************************************************************** +* Delete any companies found from the data graph +***************************************************************/ +unset($root['company']); + +/************************************************************** +* Write the change(s) back +***************************************************************/ +$das->applyChanges($dbh,$root); +?> +]]> + </programlisting> + </example> + </para> + + </section> <!-- one-table --> + + <section xml:id='sdo.das.rel.examples.two-table'> + <title>Two-table examples</title> + <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. + </para> + <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. + </para> + <para> + You can find these examples combined into one script called + <filename>1cd-CRUD</filename> + in the + <filename>Scenarios</filename> + directory in the Relational DAS package. + </para> + + <para> + <example> + <title>One company, one department - Create</title> + <para> + As in the earlier example of creating just one company data object, + the first action after constructing the Relational DAS is to call + <function>createRootDataObject</function> + 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. + </para> + <para> + 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 + <varname>id</varname> + in the company table and the + <varname>co_id</varname> + 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 + <function>getLastInsertId</function> + method to obtain the auto-generated primary key, then add that as + the value of the + <varname>co_id</varname> + column when inserting the department row. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-C"> +<![CDATA[ +<?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); + +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>One company, one department - Retrieve and Update</title> + + <para> + In this case the SQL query passed to + <function>executeQuery</function> + 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 + <function>executeQuery</function> + call enabling the Relational DAS to know which column is which in + the result set. + </para> + <para> + Note that the + <varname>co_id</varname> + 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. + </para> + <para> + 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. + </para> + + <para> + In this way the Relational DAS can retrieve and renormalise data + for multiple companies and multiple departments underneath them. + </para> + + <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-RU"> +<![CDATA[ +<?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); +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>One company, two departments - Retrieve and Delete</title> + <para> + 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. + </para> + <para> + 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: + <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-CRUD.good-delete"> +<![CDATA[ +<?php +unset($root['company'][0]); +?> +]]> + </programlisting> + and not: + <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-CRUD.bad-delete"> +<![CDATA[ +<?php +unset($acme); //WRONG +?> +]]> + </programlisting> + Simply unsetting + <varname>$acme</varname> + would destroy the variable but leave the data in the data + graph untouched. + </para> + + <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-RD"> +<![CDATA[ +<?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); + +?> +]]> + </programlisting> + </example> + </para> + </section> + + <section xml:id='sdo.das.rel.examples.three-table'> + <title>Three-table example</title> + <para> + The following examples use all three tables from the company database: + the company, department, and employee tables. These introduce the final + piece of function not exercised by the examples above: the + non-containment reference + <varname>employee_of_the_month</varname>. + </para> + <para> + Like the examples above for company and department, this set of examples + is intended to illustrate the full lifecycle of such a data graph. + </para> + + + <para> + <example> + <title>One company, one department, one employee - Create</title> + <para> + In this example a company is created containing one department and + just one employee. Note that this example clears out all three tables + at the start so that the exact results of the queries can be known. + </para> + <para> + Note how once the company, department and employee have been created, + the + <varname>employee_of_the_month</varname> + property of the company can be made to point at the new employee. + As this is a non-containment reference, this cannot be done until + the employee object has been created within the graph. + Non-containment references need to be managed carefully. + For example if the employee were now deleted from under the department, + it would not be correct to try to save the graph without + first clearing or re-assigning the + <varname>employee_of_the_month</varname> + property. + The closure rule for SDO data graphs requires that any object pointed + at by a non-containment reference must also be reachable by + containment relationships. + </para> + <para> + When it comes to inserting the graph into the database, the procedure + is similar to the example of inserting the company and department, + but + <varname>employee_of_the_month</varname> + introduces an extra complexity. + The Relational DAS needs to insert the objects working down the tree + formed by containment relationships, so company, then department, then + employee. This is necessary so that it always has the auto-generated + primary key of a parent on hand to include in a child row. But when + the company row is inserted the employee who is employee of the month + has not yet been inserted and the primary key is not known. The + procedure is that after the employee record is inserted and its + primary key known, a final step is performed in which the the + company record is updated with the employee's primary key. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-C"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************************************* +* Empty out the three 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(); +$pdo_stmt = $dbh->prepare('DELETE FROM EMPLOYEE;'); +$rows_affected = $pdo_stmt->execute(); + +/************************************************************************************* +* Create a tiny but complete company. +* The company name is Acme. +* There is one department, Shoe. +* There is one employee, Sue. +* The employee of the month is Sue. +*************************************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); + +$root = $das -> createRootDataObject(); +$acme = $root -> createDataObject('company'); +$acme -> name = "Acme"; +$shoe = $acme -> createDataObject('department'); +$shoe -> name = 'Shoe'; +$shoe -> location = 'A-block'; +$sue = $shoe -> createDataObject('employee'); +$sue -> name = 'Sue'; +$acme -> employee_of_the_month = $sue; + +$das -> applyChanges($dbh, $root); + +echo "Wrote back Acme with one department and one employee\n"; +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>One company, one department, one employee - Retrieve and update</title> + <para> + The SQL statement passed to the Relational DAS is this time an inner + join that retrieves data from all three tables. Otherwise this example + introduces nothing that has not appeared in a previous example. + </para> + <para> + The graph is updated by the addition of a new department and employee + and some alterations to the name properties of the existing objects + in the graph. The combined changes are then written back. The + Relational DAS will process and apply an arbitrary mixture of + additions, modifications and deletions to and from the data graph. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-RU"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************************************* +* Find the company again and change various aspects. +* Change the name of the company, department and employee. +* Add a second department and a new employee. +* Change the employee of the month. +*************************************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); + +$root = $das->executeQuery($dbh, + "select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " . + "from company c, department d, employee e " . + "where e.dept_id = d.id and d.co_id = c.id and c.name='Acme'", + array('company.id','company.name','company.employee_of_the_month', + 'department.id','department.name','employee.id','employee.name')); +$acme = $root['company'][0]; + +$shoe = $acme->department[0]; +$sue = $shoe -> employee[0]; + +$it = $acme->createDataObject('department'); +$it->name = 'IT'; +$it->location = 'G-block'; +$billy = $it->createDataObject('employee'); +$billy->name = 'Billy'; + +$acme->name = 'MegaCorp'; +$shoe->name = 'Footwear'; +$sue->name = 'Susan'; + +$acme->employee_of_the_month = $billy; +$das -> applyChanges($dbh, $root); +echo "Wrote back company with extra department and employee and all the names changed (Megacorp/Footwear/Susan)\n"; + +?> +]]> + </programlisting> + </example> + </para> + + <para> + <example> + <title>One company, two departments, two employees - Retrieve and delete</title> + <para> + The company is retrieved as a complete data graph containing five + data objects - the company, two departments and two employees. + They are all deleted by deleting the company object. Deleting an + object from the graph deletes all the object beneath it in the graph. + Five SQL DELETE statements will be generated and executed. As always + they will be qualified with a WHERE clause that contains all of the + fields that were retrieved, so that any updates to the data in the + database in the meantime by another process will be detected. + </para> + <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-RD"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +require_once 'company_metadata.inc.php'; + +/************************************************************************************* +* Now read it one more time and delete it. +* You can delete part, apply the changes, then carry on working with the same graph but +* care is needed to keep closure - you cannot delete the employee who is eotm without +* reassigning. For safety here we delete the company all in one go. +*************************************************************************************/ +$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); +$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); + +$root = $das->executeQuery($dbh, + "select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " . + "from company c, department d, employee e " . + "where e.dept_id = d.id and d.co_id = c.id and c.name='MegaCorp';", + array('company.id','company.name','company.employee_of_the_month', + 'department.id','department.name','employee.id','employee.name')); +$megacorp = $root['company'][0]; + +unset($root['company']); +$das -> applyChanges($dbh, $root); + +echo "Deleted the company, departments and employees all in one go.\n"; + +?> +]]> + </programlisting> + </example> + </para> + </section> +</chapter> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +indent-tabs-mode:nil +sgml-parent-document:nil +sgml-default-dtd-file:"../../../manual.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:nil +sgml-local-ecat-files:nil +End: +vim600: syn=xml fen fdm=syntax fdl=2 si +vim: et tw=78 syn=sgml +vi: ts=1 sw=1 +--> + http://cvs.php.net/viewvc.cgi/phpdoc/en/reference/sdodasrel/limitations.xml?r1=1.1&r2=1.2&diff_format=u Index: phpdoc/en/reference/sdodasrel/limitations.xml diff -u /dev/null phpdoc/en/reference/sdodasrel/limitations.xml:1.2 --- /dev/null Wed Dec 26 00:07:09 2007 +++ phpdoc/en/reference/sdodasrel/limitations.xml Wed Dec 26 00:07:08 2007 @@ -0,0 +1,82 @@ +<?xml version="1.0" encoding="utf-8"?> +<!-- $Revision: 1.2 $ --> + +<chapter xml:id="sdo.das.rel.limitations" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink"> + <title>Limitations</title> + <para> + There are the following limitations in the current release of the + Relational DAS: + <itemizedlist> + <listitem> + <para> + No support for nulls. There is no support for SQL NULL type. + It is not legal to assign PHP NULL to a data object property + and the Relational DAS will not write that back as a NULL to the + database. If nulls are found in the database on a query, + the property will remain unset. + </para> + </listitem> + <listitem> + <para> + Only two types of SDO relationship. The metadata described below + allows the Relational DAS to model just two types of SDO relationship: + multi-valued containment relationships and single-valued + non-containment references. In SDO, whether a property describes a + single- or multi-valued relationship, and whether it is containment + or non-containment, are independent. The full range of possibilities + that SDO allows cannot all be defined. There may be relationships + that it would be useful to model but which the current implementation + cannot manage. + One example is a single-valued containment relationship. + </para> + </listitem> + <listitem> + <para> + No support for the full range of SDO data types. The Relational + DAS defines all primitive properties in the SDO model as being + of type string. SDO defines a richer set of types containing various + integer, float, boolean and data and time types. String is adequate + for the purposes of the Relational DAS since the combination of PHP, + PDO and the database will ensure that values passed as strings + will be converted to the proper type before being put in the database. + This does affect some scenarios in which the Relational DAS has + to work with a data graph that has come from or will go to a + different DAS. + </para> + </listitem> + <listitem> + <para> + Only one foreign key per table. + The metadata only provides the means to specify one foreign key + per table. This foreign key may be mapped to one of the two types of + SDO relationship supported. Obviously there are some scenarios + that cannot be described under this limitation - it is not possible + to have two non-containment references from one table to another + for example. + </para> + </listitem> + </itemizedlist> + </para> +</chapter> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +indent-tabs-mode:nil +sgml-parent-document:nil +sgml-default-dtd-file:"../../../manual.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:nil +sgml-local-ecat-files:nil +End: +vim600: syn=xml fen fdm=syntax fdl=2 si +vim: et tw=78 syn=sgml +vi: ts=1 sw=1 +--> + http://cvs.php.net/viewvc.cgi/phpdoc/en/reference/sdodasrel/setup.xml?r1=1.1&r2=1.2&diff_format=u Index: phpdoc/en/reference/sdodasrel/setup.xml diff -u /dev/null phpdoc/en/reference/sdodasrel/setup.xml:1.2 --- /dev/null Wed Dec 26 00:07:09 2007 +++ phpdoc/en/reference/sdodasrel/setup.xml Wed Dec 26 00:07:08 2007 @@ -0,0 +1,157 @@ +<?xml version="1.0" encoding="utf-8"?> +<!-- $Revision: 1.2 $ --> + +<chapter xml:id="sdodasrel.setup" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink"> + &reftitle.setup; + + <!-- {{{ Requirements --> + <section xml:id="sdodasrel.requirements"> + &reftitle.required; + <para> + The Relational DAS requires that the SDO extension be installed. + The SDO extension requires a version of PHP 5.1, and the + Relational DAS requires a recent version that contains + an important fix for PDO. The most up-to-date information about + required levels of PHP should be found in the changelog for the + package on PECL. At the time of writing, though, the Relational + DAS requires the most recent beta level of PHP 5.1, that is + PHP 5.1.0. + </para> + <para> + The Relational DAS uses PDO to access the relational database, + and so should run with a variety of different relational databases. + At the time of writing it has been tested in the following configurations + <itemizedlist> + <listitem> + <para> + MySQL 4.1.14, on Windows. + The Relational DAS operates correctly with the php_pdo_mysql + driver that comes with the pre-built binaries for PHP 5.1.0. + </para> + </listitem> + <listitem> + <para> + MySQL 4.1.13, on Linux. It is necessary to have the most up-to-date + PDO driver for MySQL, which comes built in to PHP 5.1.0. + It may be necessary to uninstall the usual driver that would have + come from PECL using + <command>pear uninstall pdo_mysql</command> + . You will need to configure PHP with the + <command>--with-pdo-mysql</command> option. + </para> + </listitem> + <listitem> + <para> + DB2 8.2 Personal Edition, on Windows. + The Relational DAS operates correctly with the + php_pdo_odbc driver that comes with the pre-built binaries for + PHP 5.1.0. + </para> + </listitem> + <listitem> + <para> + DB2 8.2 Personal Developer's Edition, on Linux. + The Developer's Edition is needed because it + contains the include files needed when PHP is configured + and built. You will need to configure PHP with the + <command>--with-pdo-odbc=ibm-db2</command> option. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + The Relational DAS applies changes to the database within + a user-delimited transaction: that is, it issues a call to + <function>PDO::beginTransaction</function> + before beginning to apply changes, and + <function>PDO::commit</function> + or + <function>PDO::rollback</function> + on completion. + Whichever database is chosen, the database and the PDO driver + for the database must support these calls. + </para> + </section> + <!-- }}} --> + + <!-- {{{ Installation --> + <section xml:id="sdodasrel.installation"> + &reftitle.install; + <para> + The installation instructions for all the SDO components + are in the SDO + <link linkend="sdo.installation">install</link> + section of the SDO documentation. + </para> + <para> + In any case, the essential facts are that + the Relational DAS is written in PHP and it + should be placed somewhere on the PHP + <link linkend="ini.include-path">include_path</link>. + </para> + + <para> + Your application will of course need to include the Relational DAS + with a statement like this: + <programlisting role="php"> +<![CDATA[ +<?php +require_once 'SDO/DAS/Relational.php'; +?> +]]> + </programlisting> + </para> + </section> + <!-- }}} --> + + <!-- {{{ Configuration --> + <section xml:id="sdodasrel.configuration"> + &reftitle.runtime; + &no.config; + <section xml:id='sdo.das.rel.tracing'> + <title>Tracing</title> + <para> + You may be interested in seeing the SQL statements that are generated + in order to apply changes back to the database. At the top of the + <filename>SDO/DAS/Relational.php</filename> + you will find a number of constants which control whether the process + of constructing and executing the SQL statements is to be traced. + Try setting + <varname>DEBUG_EXECUTE_PLAN</varname> + to &true; to see the generated SQL statements. + </para> + </section> + </section> + <!-- }}} --> + + <!-- {{{ Resources --> + <section xml:id="sdodasrel.resources"> + &reftitle.resources; + &no.resource; + </section> + <!-- }}} --> + +</chapter> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +indent-tabs-mode:nil +sgml-parent-document:nil +sgml-default-dtd-file:"../../../manual.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:nil +sgml-local-ecat-files:nil +End: +vim600: syn=xml fen fdm=syntax fdl=2 si +vim: et tw=78 syn=sgml +vi: ts=1 sw=1 +--> + http://cvs.php.net/viewvc.cgi/phpdoc/en/reference/sdodasrel/constants.xml?r1=1.3&r2=1.4&diff_format=u Index: phpdoc/en/reference/sdodasrel/constants.xml diff -u phpdoc/en/reference/sdodasrel/constants.xml:1.3 phpdoc/en/reference/sdodasrel/constants.xml:1.4 --- phpdoc/en/reference/sdodasrel/constants.xml:1.3 Wed Jun 20 22:25:19 2007 +++ phpdoc/en/reference/sdodasrel/constants.xml Wed Dec 26 00:07:08 2007 @@ -1,14 +1,13 @@ <?xml version='1.0' encoding='iso-8859-1'?> -<!-- $Revision: 1.3 $ --> -<!-- Generated by xml_proto.php v2.1. Found in /scripts directory of phpdoc. --> -<section xml:id="sdodasrel.constants" xmlns="http://docbook.org/ns/docbook"> - <!-- &reftitle.constants; - &extension.constants; - --> +<!-- $Revision: 1.4 $ --> + +<appendix xml:id="sdodasrel.constants" xmlns="http://docbook.org/ns/docbook"> + &reftitle.constants; + &extension.constants; <para> The Relational DAS introduces no predefined constants. </para> -</section> +</appendix> <!-- Keep this comment at the end of the file Local variables: http://cvs.php.net/viewvc.cgi/phpdoc/en/reference/sdodasrel/reference.xml?r1=1.23&r2=1.24&diff_format=u Index: phpdoc/en/reference/sdodasrel/reference.xml diff -u phpdoc/en/reference/sdodasrel/reference.xml:1.23 phpdoc/en/reference/sdodasrel/reference.xml:1.24 --- phpdoc/en/reference/sdodasrel/reference.xml:1.23 Mon Dec 10 17:06:09 2007 +++ phpdoc/en/reference/sdodasrel/reference.xml Wed Dec 26 00:07:08 2007 @@ -1,1442 +1,12 @@ <?xml version='1.0' encoding='iso-8859-1'?> -<!-- $Revision: 1.23 $ --> -<!-- Purpose: database.abstract --> -<!-- Membership: pecl --> -<!-- State: experimental --> +<!-- $Revision: 1.24 $ --> <reference xml:id="ref.sdo.das.rel" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink"> <title>SDO Relational Data Access Service Functions</title> <titleabbrev>SDO-DAS-Relational</titleabbrev> <partintro> - <section xml:id="sdo.das.rel.intro"> - &reftitle.intro; - <para> - <!-- This warns that the extension is experimental --> - &warn.experimental; - </para> - <para> - In order to use the Relational Data Access Service for - Service Data Objects, - you will need to understand some of the concepts behind SDO: - the data graph, the data object, the disconnected way of working, - the change summary, XPath and property expressions, and so on. - If you are not familiar with these ideas, you might want to look first at - <link linkend='ref.sdo'>the section on SDO</link>. - In addition, the Relational DAS makes use of the PDO extension to - isolate itself from specifics of different back-end relational databases. - In order to use the Relational DAS you will need to be able to - create and pass a PDO database connection; - for this reason you might also want to take a look at - <link linkend='ref.pdo'>the section on PDO</link>. - </para> - - <para> - The job of the Relational DAS is to move data between the application - and a relational database. In order to do this it needs to be told - the mapping between the database entities - - tables, columns, primary keys and foreign keys - - and the elements of the SDO model - - types, properties, containment relationships and so on. - You specify this information as metadata when you - construct the Relational DAS. - </para> - - <procedure xml:id='overview'> - <title>Overview of Operation</title> - <step> - <para> - The first step is to call the Relational DAS's constructor, - passing the metadata that defines the mapping between - database and SDO model. - There are examples of this below. - </para> - </step> - - <step> - <para> - The next step might be to call the - <function>executeQuery</function> - or - <function>executePreparedQuery</function> - methods on the Relational DAS, passing either a literal SQL statement - for the DAS to prepare and execute, or a prepared statement with - placeholders and a list of values to be inserted. - You may also need to specify a small amount of metadata - about the query itself, so that the Relational DAS knows - exactly what columns will be returned from the database and - in what order. You will also need to pass a PDO database connection. - </para> - - <para> - The return value from - <function>executeQuery</function> - or - <function>executePreparedQuery</function> - is a normalised data graph containing all the data from the result set. - For a query that returns data obtained from a number of tables, - this graph will contain a number of data objects, - linked by SDO containment relationships. - There may also be SDO non-containment references within the data. - </para> - - <para> - Once the query has been executed and the data graph constructed, - there is no need for either that instance of the the Relational DAS or - the database connection. There are no locks held on the database. - Both the Relational DAS and the PDO database connection can be - garbage collected. - </para> - </step> - <step> - <para> - Quite possibly the data in the data graph will go through - a number of modifications. The data graph can be serialised - into the PHP session and so may have a lifetime beyond just - one client-server interaction. Data objects can be created - and added to the graph, the data objects already in the graph - can be deleted, and data objects in the graph can be modified. - </para> - </step> - <step> - <para> - Finally, the changes made to the data graph can be applied - back to the database using the - <function>applyChanges</function> - method of the Relational DAS. For this, another instance - of the Relational DAS must be constructed, using the - same metadata, and another connection to the database obtained. - The connection and the data graph are passed to - <function>applyChanges</function>. - At this point the Relational DAS examines the change summary - and generates the necessary INSERT, UPDATE and DELETE SQL statements - to apply the changes. Any UPDATE and DELETE statements are qualified - with the original values of the data so that should the data have - changed in the database in the meantime this will be detected. - Assuming no such collisions have occurred the changes will be - committed to the database. The application can then continue to work - with the data graph, make more changes and apply them, or can discard - it. - </para> - </step> - </procedure> - <para> - There are other ways of working with the data in the database: - it is possible to just create data objects and write them to the - database without a preliminary call to - <function>executeQuery</function>, - for example. This scenario and others are explored in the - <link linkend="sdo.das.rel.examples">Examples</link> - section below. - </para> - </section> <!-- intro --> - - <section xml:id="sdo.das.rel.installation"> - &reftitle.install; - <para> - The installation instructions for all the SDO components - are in the SDO - <link linkend="sdo.installation">install</link> - section of the SDO documentation. - </para> - <para> - In any case, the essential facts are that - the Relational DAS is written in PHP and it - should be placed somewhere on the PHP - <link linkend="ini.include-path">include_path</link> - . - </para> - - <para> - Your application will of course need to include the Relational DAS - with a statement like this: - <programlisting role="php"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -?> -]]> - </programlisting> - </para> - </section> - - <section xml:id="sdo.das.rel.requirements"> - &reftitle.required; - <para> - The Relational DAS requires that the SDO extension be installed. - The SDO extension requires a version of PHP 5.1, and the - Relational DAS requires a recent version that contains - an important fix for PDO. The most up-to-date information about - required levels of PHP should be found in the changelog for the - package on PECL. At the time of writing, though, the Relational - DAS requires the most recent beta level of PHP 5.1, that is - PHP 5.1.0. - </para> - <para> - The Relational DAS uses PDO to access the relational database, - and so should run with a variety of different relational databases. - At the time of writing it has been tested in the following configurations - <itemizedlist> - <listitem> - <para> - MySQL 4.1.14, on Windows. - The Relational DAS operates correctly with the php_pdo_mysql - driver that comes with the pre-built binaries for PHP 5.1.0. - </para> - </listitem> - <listitem> - <para> - MySQL 4.1.13, on Linux. It is necessary to have the most up-to-date - PDO driver for MySQL, which comes built in to PHP 5.1.0. - It may be necessary to uninstall the usual driver that would have - come from PECL using - <command>pear uninstall pdo_mysql</command> - . You will need to configure PHP with the - <command>--with-pdo-mysql</command> option. - </para> - </listitem> - <listitem> - <para> - DB2 8.2 Personal Edition, on Windows. - The Relational DAS operates correctly with the - php_pdo_odbc driver that comes with the pre-built binaries for - PHP 5.1.0. - </para> - </listitem> - <listitem> - <para> - DB2 8.2 Personal Developer's Edition, on Linux. - The Developer's Edition is needed because it - contains the include files needed when PHP is configured - and built. You will need to configure PHP with the - <command>--with-pdo-odbc=ibm-db2</command> option. - </para> - </listitem> - </itemizedlist> - </para> - - <para> - The Relational DAS applies changes to the database within - a user-delimited transaction: that is, it issues a call to - <function>PDO::beginTransaction</function> - before beginning to apply changes, and - <function>PDO::commit</function> - or - <function>PDO::rollback</function> - on completion. - Whichever database is chosen, the database and the PDO driver - for the database must support these calls. - </para> - </section> - - <section xml:id="sdo.das.rel.limitations"> - <title>Limitations</title> - <para> - There are the following limitations in the current release of the - Relational DAS: - <itemizedlist> - <listitem> - <para> - No support for nulls. There is no support for SQL NULL type. - It is not legal to assign PHP NULL to a data object property - and the Relational DAS will not write that back as a NULL to the - database. If nulls are found in the database on a query, - the property will remain unset. - </para> - </listitem> - <listitem> - <para> - Only two types of SDO relationship. The metadata described below - allows the Relational DAS to model just two types of SDO relationship: - multi-valued containment relationships and single-valued - non-containment references. In SDO, whether a property describes a - single- or multi-valued relationship, and whether it is containment - or non-containment, are independent. The full range of possibilities - that SDO allows cannot all be defined. There may be relationships - that it would be useful to model but which the current implementation - cannot manage. - One example is a single-valued containment relationship. - </para> - </listitem> - <listitem> - <para> - No support for the full range of SDO data types. The Relational - DAS defines all primitive properties in the SDO model as being - of type string. SDO defines a richer set of types containing various - integer, float, boolean and data and time types. String is adequate - for the purposes of the Relational DAS since the combination of PHP, - PDO and the database will ensure that values passed as strings - will be converted to the proper type before being put in the database. - This does affect some scenarios in which the Relational DAS has - to work with a data graph that has come from or will go to a - different DAS. - </para> - </listitem> - <listitem> - <para> - Only one foreign key per table. - The metadata only provides the means to specify one foreign key - per table. This foreign key may be mapped to one of the two types of - SDO relationship supported. Obviously there are some scenarios - that cannot be described under this limitation - it is not possible - to have two non-containment references from one table to another - for example. - </para> - </listitem> - </itemizedlist> - </para> - </section> - - <section xml:id='sdo.das.rel.examples'> - &reftitle.examples; - <para> - This section illustrates how the Relational DAS can be used to create, - retrieve, update and delete data in a relational database. - Many of the examples are illustrated with a three-table database that - contains companies, departments within those companies, and employees - that work in those departments. This example is used in a number of - places within the SDO literature. See the examples section of the - <link xlink:href='&url.ibm.sdo.spec;'>Service Data Objects specification</link> - or the - <link linkend="sdo.examples">Examples</link> - section of the documentation for the SDO extension. - </para> - - <para> - The Relational DAS is constructed with metadata that defines the - relational database and how it should be mapped to SDO. - The long section that follows describes this metadata and how to - construct the Relational DAS. The examples that follow it all assume that - this metadata is in an included php file. - </para> - - <para> - The examples below and others can all be found in the - <filename>Scenarios</filename> - directory in the Relational DAS package. - </para> - - <para> - The Relational DAS throws exceptions in the event that it finds errors - in the metadata or errors when executing SQL statements against the - database. For brevity the examples below all omit the use of try/catch - blocks around the calls to the Relational DAS. - </para> - - <para> - These examples all differ from the expected use of SDO in two - important respects. - </para> - <para> - First, they show all interactions with the database completed within - one script. In this respect these scenarios are not realistic but are - chosen to illustrate just the use of the Relational DAS. - It is expected that interactions with the database will be separated - in time and the data graph serialised and deserialised into the PHP - session one or more times as the application interacts with an end user. - </para> - <para> - Second, all queries executed against the database use hard-coded - queries with no variables substituted. In this case it is safe to - use the simple - <function>executeQuery</function> - call, and this is what the examples illustrate. - In practice, though, it is unlikely that the SQL statement is known - entirely ahead of time. In order to allow variables to be safely - substituted into the SQL queries, without running the risk of - injecting SQL with unknown effects, it is safer to use the - <function>executePreparedQuery</function> - which takes a prepared SQL statement containing placeholders - and a list of values to be substituted. - </para> - - <section xml:id='sdo.das.rel.metadata'> - <title>Specifying the metadata</title> - <para> - This first long section describes in detail how the metadata describing - the database and the required SDO model is supplied to the - Relational DAS. - </para> - <para> - 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 normalised into a graph. - </para> - - <section xml:id='sdo.das.rel.metadata.database'> - <title>Database metadata</title> - <para> - The first argument to the constructor describes the target - relational database. - </para> - - <para> - Each table is described by an associative array with up to four keys. - <informaltable> - <tgroup cols='2'> - <thead> - <row> - <entry>Key</entry> - <entry>Value</entry> - </row> - </thead> - <tbody> - <row> - <entry>name</entry> - <entry>The name of the table.</entry> - </row> - <row> - <entry>columns</entry> - <entry> - An array listing the names of the columns, in any order. - </entry> - </row> - <row> - <entry>PK</entry> - <entry>The name of the column containing the primary key.</entry> - </row> - <row> - <entry>FK</entry> - <entry>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. - </entry> - </row> - </tbody> - </tgroup> - </informaltable> - </para> - - <programlisting role="php"> -<![CDATA[ -<?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); -?> -]]> - </programlisting> - - <para> - This metadata corresponds to a relational database that might have - been defined to MySQL as: - </para> - <programlisting role="sql"> -<![CDATA[ - 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) - ); -]]> - </programlisting> - <para> - or to DB2 as: - </para> - <programlisting role="sql"> -<![CDATA[ -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) ) -]]> - </programlisting> - - <para> - 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 - <varname>co_id</varname> - column on the department table and the - <varname>dept_id</varname> - 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. - </para> - - <para> - There is a third foreign key in this example, that from the - <varname>employee_of_the_month</varname> - 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 - <varname>employee_of_the_month</varname> - column represents a single-valued relationship: there can be only - one employee of the month for a given company. - The - <varname>co_id</varname> - and - <varname>dept_id</varname> - 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. - </para> - - <para> - There are a few simple rules to be followed when constructing the - database metadata: - </para> - - <itemizedlist> - <listitem> - <para> - 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. - </para> - </listitem> - - <listitem> - <para> - 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. - </para> - </listitem> - - <listitem> - <para> - 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. - </para> - </listitem> - </itemizedlist> - - <para> - Given these rules, and given the SQL statements that define the - database, the database metadata should be easy to construct. - </para> - - <section xml:id='sdo.das.rel.metadata.database.model'> - <title>What the Relational DAS does with the metadata</title> - <para> - 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. - </para> - <para> - 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. - </para> - - <para> - 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 - <link linkend="sdo.das.rel.metadata.crels"> - SDO containment relationships - </link> - below. - </para> - </section> - </section> - - <section xml:id='sdo.das.rel.metadata.approottype'> - <title>Specifying the application root type</title> - <para> - 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. - </para> - </section> - - <section xml:id='sdo.das.rel.metadata.crels'> - <title>Specifying the SDO containment relationships</title> - - <para> - 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. - </para> - - <para> - 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. - - </para> - <programlisting role="php"> -<![CDATA[ -<?php -$department_containment = array( 'parent' => 'company', 'child' => 'department'); -$employee_containment = array( 'parent' => 'department', 'child' => 'employee'); - -$SDO_containment_metadata = array($department_containment, $employee_containment); -?> -]]> - </programlisting> - - <para> - 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 - <varname>co_id</varname> - column in the department table) - and from employee to department (the - <varname>dept_id</varname> - 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 - <varname>co_id</varname> - 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 - <varname>department</varname> - 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. - </para> - - <para> - The third foreign key in this example, the - <varname>employee_of_the_month</varname> - , - 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. - </para> - </section> - </section> <!--specifying the metadata --> - - <section xml:id='sdo.das.rel.examples.one-table'> - <title>One-table examples</title> - <para> - The following set of examples all use the Relational DAS to work with - a data graph containing just one application data object, a single - company and the data just to be found the company table. These examples - do not exercise the power of SDO or the Relational DAS and of course - the same result could be achieved more economically with direct SQL - statements but they are intended to illustrate how to work with the - Relational DAS. - </para> - - <para> - For this very simple scenario it would be possible to simplify the - database metadata to include just the company table - if that were done - the second and third arguments to the constructor and the column - specifier used in the query example would become optional. - </para> - - <para> - <example> - <title>Creating a data object</title> - <para> - The simplest example is that of creating a single data object and - writing it to the database. In this example a single company object - is created, its name is set to 'Acme', and the Relational DAS is - called to write the changes to the database. The company name is - set here using the property name method. See the - <link linkend="sdo.examples">Examples</link> - section on the SDO extension for other ways of accessing the - properties of an object. - </para> - - <para> - Data objects can only be created when you have a data object to - start with, however. It is for that reason that the first call - to the Relational DAS here is to obtain a root object. This is - in effect how to ask for an empty data graph - the special root - object is the true root of the tree. The company data object is - then created with a call to - <function>createDataObject</function> - on the root object. This creates the company data object and inserts - it in the graph by inserting into a multi-valued containment property - on the root object called 'company'. - </para> - <para> - When the Relational DAS is called to apply the changes a simple - insert statement 'INSERT INTO company (name) VALUES ("Acme");' - will be constructed and executed. The auto-generated primary key - will be set into the data object and the change summary will be reset, - so that it would be possible to continue working with the same data - object, modify it, and apply the newer changes a second time. - </para> - - <programlisting role="php" xml:id="sdo.das.rel.examples.1c-C"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************** - * Construct the DAS with the metadata - ***************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); - -/************************************************************** - * Obtain a root object and create a company object underneath. - * Make a simple change to the data object. - ***************************************************************/ -$root = $das -> createRootDataObject(); -$acme = $root -> createDataObject('company'); - -$acme->name = "Acme"; - -/************************************************************** - * Get a database connection and write the object to the database - ***************************************************************/ -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); -$das -> applyChanges($dbh, $root); -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>Retrieving a data object</title> - <para> - In this example a single data object is retrieved from the database - - or possibly more than one if there is more than one company - called 'Acme'. For each company returned, the - <varname>name</varname> - and - <varname>id</varname> - properties are echoed. - </para> - <para> - In this example the third argument to - <function>executeQuery</function>, - the column specifier is needed as there are other tables in the - metadata with column names of - <varname>name</varname> - and - <varname>id</varname>. - If there were no possible ambiguity it could be omitted. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1c-R"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************** - * Construct the DAS with the metadata - ***************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); - -/************************************************************** - * Get a database connection - ***************************************************************/ -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); - -/************************************************************** - * Issue a query to obtain a company object - possibly more if they exist - ***************************************************************/ -$root = $das->executeQuery($dbh, - 'select name, id from company where name="Acme"', - array('company.name', 'company.id') ); - -/************************************************************** - * Echo name and id - ***************************************************************/ -foreach ($root['company'] as $company) { - echo "Company obtained from the database has name = " . - $company['name'] . " and id " . $company['id'] . "\n"; -} -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>Updating a data object</title> - <para> - This example combines the previous two, in the sense that in order - to be updated the object must first be retrieved. The application - code reverses the company name (so 'Acme' becomes 'emcA') and then the - changes are written back to the database in the same way that they - were when the object was created. Because the query searches for - the name both ways round the program can be run repeatedly to find - the company and reverse its name each time. - </para> - <para> - In this example the same instance of the Relational DAS is reused - for the - <function>applyChanges</function>, - as is the PDO database handle. This is quite alright; it also - alright to allow the previous instances to be garbage collected - and to obtain new instances. No state data regarding the graph - is held the Relational DAS once it has returned a data graph to - the application. All necessary data is either within the graph itself, - or can be reconstructed from the metadata. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1c-RU"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************** - * Construct the DAS with the metadata - ***************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); - -/************************************************************** - * Get a database connection - ***************************************************************/ -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); - -/************************************************************** - * Issue a query to obtain a company object - possibly more if they exist - ***************************************************************/ -$root = $das->executeQuery($dbh, - 'select name, id from company where name="Acme" or name="emcA"', - array('company.name', 'company.id') ); - -/************************************************************** - * Alter the name of just the first company - ***************************************************************/ -$company = $root['company'][0]; -echo "obtained a company with name of " . $company->name . "\n"; -$company->name = strrev($company->name); - -/************************************************************** - * Write the change back - ***************************************************************/ -$das->applyChanges($dbh,$root); -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>Deleting a data object</title> - <para> - Any companies called 'Acme' or its reverse 'emcA' are retrieved. - They are then all deleted from the graph with unset. - </para> - - <para> - In this example they are all deleted in one go by unsetting the - containing property (the property defining the containment - relationship). It is also possible to delete them individually. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1c-RD"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************** - * Construct the DAS with the metadata - ***************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); - -/************************************************************** - * Get a database connection - ***************************************************************/ -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); - -/************************************************************** - * Issue a query to obtain a company object - possibly more if they exist - ***************************************************************/ -$root = $das->executeQuery($dbh, - 'select name, id from company where name="Acme" or name="emcA"', - array('company.name', 'company.id') ); - -/************************************************************** - * Delete any companies found from the data graph - ***************************************************************/ -unset($root['company']); - -/************************************************************** - * Write the change(s) back - ***************************************************************/ -$das->applyChanges($dbh,$root); -?> -]]> - </programlisting> - </example> - </para> - - </section> <!-- one-table --> - - <section xml:id='sdo.das.rel.examples.two-table'> - <title>Two-table examples</title> - <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. - </para> - <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. - </para> - <para> - You can find these examples combined into one script called - <filename>1cd-CRUD</filename> - in the - <filename>Scenarios</filename> - directory in the Relational DAS package. - </para> - - <para> - <example> - <title>One company, one department - Create</title> - <para> - As in the earlier example of creating just one company data object, - the first action after constructing the Relational DAS is to call - <function>createRootDataObject</function> - 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. - </para> - <para> - 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 - <varname>id</varname> - in the company table and the - <varname>co_id</varname> - 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 - <function>getLastInsertId</function> - method to obtain the auto-generated primary key, then add that as - the value of the - <varname>co_id</varname> - column when inserting the department row. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-C"> -<![CDATA[ -<?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); - -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>One company, one department - Retrieve and Update</title> - - <para> - In this case the SQL query passed to - <function>executeQuery</function> - 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 - <function>executeQuery</function> - call enabling the Relational DAS to know which column is which in - the result set. - </para> - <para> - Note that the - <varname>co_id</varname> - 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. - </para> - <para> - 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. - </para> - - <para> - In this way the Relational DAS can retrieve and renormalise data - for multiple companies and multiple departments underneath them. - </para> - - <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-RU"> -<![CDATA[ -<?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); -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>One company, two departments - Retrieve and Delete</title> - <para> - 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. - </para> - <para> - 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: - <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-CRUD.good-delete"> -<![CDATA[ -<?php -unset($root['company'][0]); -?> -]]> - </programlisting> - and not: - <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-CRUD.bad-delete"> -<![CDATA[ -<?php -unset($acme); //WRONG -?> -]]> - </programlisting> - Simply unsetting - <varname>$acme</varname> - would destroy the variable but leave the data in the data - graph untouched. - </para> - - <programlisting role="php" xml:id="sdo.das.rel.examples.1cd-RD"> -<![CDATA[ -<?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); - -?> -]]> - </programlisting> - </example> - </para> - </section> - - <section xml:id='sdo.das.rel.examples.three-table'> - <title>Three-table example</title> - <para> - The following examples use all three tables from the company database: - the company, department, and employee tables. These introduce the final - piece of function not exercised by the examples above: the - non-containment reference - <varname>employee_of_the_month</varname>. - </para> - <para> - Like the examples above for company and department, this set of examples - is intended to illustrate the full lifecycle of such a data graph. - </para> - - - <para> - <example> - <title>One company, one department, one employee - Create</title> - <para> - In this example a company is created containing one department and - just one employee. Note that this example clears out all three tables - at the start so that the exact results of the queries can be known. - </para> - <para> - Note how once the company, department and employee have been created, - the - <varname>employee_of_the_month</varname> - property of the company can be made to point at the new employee. - As this is a non-containment reference, this cannot be done until - the employee object has been created within the graph. - Non-containment references need to be managed carefully. - For example if the employee were now deleted from under the department, - it would not be correct to try to save the graph without - first clearing or re-assigning the - <varname>employee_of_the_month</varname> - property. - The closure rule for SDO data graphs requires that any object pointed - at by a non-containment reference must also be reachable by - containment relationships. - </para> - <para> - When it comes to inserting the graph into the database, the procedure - is similar to the example of inserting the company and department, - but - <varname>employee_of_the_month</varname> - introduces an extra complexity. - The Relational DAS needs to insert the objects working down the tree - formed by containment relationships, so company, then department, then - employee. This is necessary so that it always has the auto-generated - primary key of a parent on hand to include in a child row. But when - the company row is inserted the employee who is employee of the month - has not yet been inserted and the primary key is not known. The - procedure is that after the employee record is inserted and its - primary key known, a final step is performed in which the the - company record is updated with the employee's primary key. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-C"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************************************* -* Empty out the three 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(); -$pdo_stmt = $dbh->prepare('DELETE FROM EMPLOYEE;'); -$rows_affected = $pdo_stmt->execute(); - -/************************************************************************************* -* Create a tiny but complete company. -* The company name is Acme. -* There is one department, Shoe. -* There is one employee, Sue. -* The employee of the month is Sue. -*************************************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); - -$root = $das -> createRootDataObject(); -$acme = $root -> createDataObject('company'); -$acme -> name = "Acme"; -$shoe = $acme -> createDataObject('department'); -$shoe -> name = 'Shoe'; -$shoe -> location = 'A-block'; -$sue = $shoe -> createDataObject('employee'); -$sue -> name = 'Sue'; -$acme -> employee_of_the_month = $sue; - -$das -> applyChanges($dbh, $root); - -echo "Wrote back Acme with one department and one employee\n"; -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>One company, one department, one employee - Retrieve and update</title> - <para> - The SQL statement passed to the Relational DAS is this time an inner - join that retrieves data from all three tables. Otherwise this example - introduces nothing that has not appeared in a previous example. - </para> - <para> - The graph is updated by the addition of a new department and employee - and some alterations to the name properties of the existing objects - in the graph. The combined changes are then written back. The - Relational DAS will process and apply an arbitrary mixture of - additions, modifications and deletions to and from the data graph. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-RU"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************************************* - * Find the company again and change various aspects. - * Change the name of the company, department and employee. - * Add a second department and a new employee. - * Change the employee of the month. - *************************************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); - -$root = $das->executeQuery($dbh, - "select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " . - "from company c, department d, employee e " . - "where e.dept_id = d.id and d.co_id = c.id and c.name='Acme'", - array('company.id','company.name','company.employee_of_the_month', - 'department.id','department.name','employee.id','employee.name')); -$acme = $root['company'][0]; - -$shoe = $acme->department[0]; -$sue = $shoe -> employee[0]; - -$it = $acme->createDataObject('department'); -$it->name = 'IT'; -$it->location = 'G-block'; -$billy = $it->createDataObject('employee'); -$billy->name = 'Billy'; - -$acme->name = 'MegaCorp'; -$shoe->name = 'Footwear'; -$sue->name = 'Susan'; - -$acme->employee_of_the_month = $billy; -$das -> applyChanges($dbh, $root); -echo "Wrote back company with extra department and employee and all the names changed (Megacorp/Footwear/Susan)\n"; - -?> -]]> - </programlisting> - </example> - </para> - - <para> - <example> - <title>One company, two departments, two employees - Retrieve and delete</title> - <para> - The company is retrieved as a complete data graph containing five - data objects - the company, two departments and two employees. - They are all deleted by deleting the company object. Deleting an - object from the graph deletes all the object beneath it in the graph. - Five SQL DELETE statements will be generated and executed. As always - they will be qualified with a WHERE clause that contains all of the - fields that were retrieved, so that any updates to the data in the - database in the meantime by another process will be detected. - </para> - <programlisting role="php" xml:id="sdo.das.rel.examples.1cde-RD"> -<![CDATA[ -<?php -require_once 'SDO/DAS/Relational.php'; -require_once 'company_metadata.inc.php'; - -/************************************************************************************* - * Now read it one more time and delete it. - * You can delete part, apply the changes, then carry on working with the same graph but - * care is needed to keep closure - you cannot delete the employee who is eotm without - * reassigning. For safety here we delete the company all in one go. - *************************************************************************************/ -$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata); -$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD); - -$root = $das->executeQuery($dbh, - "select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " . - "from company c, department d, employee e " . - "where e.dept_id = d.id and d.co_id = c.id and c.name='MegaCorp';", - array('company.id','company.name','company.employee_of_the_month', - 'department.id','department.name','employee.id','employee.name')); -$megacorp = $root['company'][0]; - -unset($root['company']); -$das -> applyChanges($dbh, $root); - -echo "Deleted the company, departments and employees all in one go.\n"; - -?> -]]> - </programlisting> - </example> - </para> - - - </section> - </section> - - <section xml:id='sdo.das.rel.tracing'> - <title>Tracing</title> - <para> - You may be interested in seeing the SQL statements that are generated - in order to apply changes back to the database. At the top of the - <filename>SDO/DAS/Relational.php</filename> - you will find a number of constants which control whether the process - of constructing and executing the SQL statements is to be traced. - Try setting - <varname>DEBUG_EXECUTE_PLAN</varname> - to &true; to see the generated SQL statements. - </para> - </section> - + <!-- FIXME: The rest should be reworked to follow our OOskeletons --> <!-- class definition section --> <section xml:id='sdo.das.rel.classes'> &reftitle.classes; @@ -1535,11 +105,6 @@ </section> - <!-- Include the stuff from constants.xml? --> - <!-- -&reference.sdodasrel.constants; ---> - </partintro> <!-- This seems to create the table of contents --> @@ -1566,3 +131,4 @@ vim: et tw=78 syn=sgml vi: ts=1 sw=1 --> +
« previous php.doc.cvs (#807) next »