function update_contact($root)
{
require_once './contacts_schema.inc.php';
require_once './db_config.inc.php';
require_once 'SDO/DAS/Relational.php';
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($table_schema, 'contact', array($address_reference));
$das->applyChanges($dbh, $root);
}
/*************************************************************************************
* Create the root data object then a single company object underneath it.
* Set the company name to 'Acme'.
*************************************************************************************/
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$root = $das->createRootDataObject();
$company = $root->createDataObject('company');
$company->name = "Acme";
$company->employee_of_the_month = null;
$das->applyChanges($dbh, $root);
echo "Created a company with name Acme and wrote it to the database\n";
/*************************************************************************************
* Find it again (with its autogenerated id this time).
*************************************************************************************/
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$name = 'Acme';
$pdo_stmt = $dbh->prepare('select name, id, employee_of_the_month from company where name=?');
$root = $das->executePreparedQuery($dbh, $pdo_stmt, array($name), array('company.name', 'company.id', 'company.employee_of_the_month'));
$company2 = $root['company'][0];
echo "Looked for Acme and found company with name = " . $company2->name . " and id " . $company2->id . "\n";
assert($company2->name == 'Acme');
assert($company2->employee_of_the_month === null);
/*************************************************************************************
* Force a change in the databse behind the back of SDO
*************************************************************************************/
$dbh2 = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$count = $dbh2->exec("update company set name='ForcedNameChange' where name='Acme';");
echo "Update the company name behind the scenes\n";
echo "Number of rows updated = " . $count . "\n";
| Author: Matthew Peters |
+----------------------------------------------------------------------+
$Id$
*/
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/*************************************************************************************
* Use SDO to retrieve then delete row of the company table.
*
* See companydb_mysql.sql and companydb_db2.sql for examples of defining the database
*************************************************************************************/
/*************************************************************************************
* Get and initialise a DAS with the metadata
*************************************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/*************************************************************************************
* Find the company or companies and delete
*************************************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
try {
$root = $das->executeQuery($dbh, "select name, id from company where name='Acme'", array('company.name', 'company.id', 'company.employee_of_the_month'));
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to retrieve data from the database.";
echo "Probably something wrong with the SQL query.";
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**
* Scenario - Create multiple companies
*
* Create multiple company rows in the company table.
*
* See companydb_mysql.sql and companydb_db2.sql for examples of defining the database
*/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$count = $dbh->exec('DELETE FROM company;');
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Create three company data objects under the same root
***************************************************************/
$root = $das->createRootDataObject();
$acme = $root->createDataObject('company');
$acme->name = "Acme";
$megacorp = $root->createDataObject('company');
$megacorp->name = 'MegaCorp';
$ultracorp = $root->createDataObject('company');
require_once 'company_metadata.inc.php';
/*************************************************************************************
* Use SDO to perform create, retrieve and update operations on a row of the company table.
*
* See companydb_mysql.sql and companydb_db2.sql for examples of defining the database
*************************************************************************************/
/*************************************************************************************
* Empty out the company table
*************************************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$count = $dbh->exec('DELETE FROM company;');
/*************************************************************************************
* Get and initialise a DAS with the metadata
*************************************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/*************************************************************************************
* Create the root data object then a single company object underneath it.
* Set the company name to 'Acme'.
*************************************************************************************/
$root = $das->createRootDataObject();
$company = $root->createDataObject('company');
$company->name = "Acme";
writeBack($das, $company);
echo "Created a company with name Acme and wrote it to the database\n";
+----------------------------------------------------------------------+
$Id$
*/
echo "executing scenario one-company-retrieve\n";
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**
* Scenario - Retrieve one company
*
* Retrieve one company row in the company table.
*/
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Get a database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Issue a query to obtain a company data object
***************************************************************/
try {
$root = $das->executeQuery($dbh, "select name, id from company where name = 'Acme'", array('company.name', 'company.id'));
$Id: interop-rdbms-mysql.php 219957 2006-09-14 12:16:19Z slaws $
*/
require_once 'SDO/DAS/Relational.php';
// Describe the structure of the alltypeparent table
$alltypeparent_table = array('name' => 'alltypeparent', 'columns' => array('parentid', 'description'), 'PK' => 'parentid');
// Describe the structure of the alltype table
$alltype_table = array('name' => 'alltype', 'columns' => array('abit', 'atinyint', 'aboolean', 'asmallint', 'amediumint', 'ainteger', 'abigint', 'afloat', 'adouble', 'adoubleprecision', 'areal', 'adecimal', 'adate', 'adatetime', 'atimestamp', 'atime', 'ayear', 'achar', 'avarchar', 'parentid'), 'PK' => 'asmallint', 'FK' => array('from' => 'parentid', 'to' => 'alltypeparent'));
// create the meta data structure for the single table
$table_metadata = array($alltypeparent_table, $alltype_table);
// describe the cross table reference
$parent_reference = array('parent' => 'alltypeparent', 'child' => 'alltype');
$reference_metadata = array($parent_reference);
// Create the Relational Data Access Service telling it the database
// schema, that table should be considered the root of the graph,
// and finally the additional information for the object model.
$das = new SDO_DAS_Relational($table_metadata, 'alltypeparent', $reference_metadata);
// no security on my locate database so access control strings are empty
$user = "";
$password = "";
try {
// connect to the mysql database. This connection will be released when the
// $dbh variable is cleaned up.
$dbh = new PDO("mysql:host=localhost;dbname=interop", $user, $password);
// construct the SQL query for contact retrieval
$stmt = "select p.parentid, p.description, a.abit, a.atinyint, a.aboolean, a.asmallint, a.amediumint, a.ainteger, a.abigint, a.afloat, a.adouble, a.adoubleprecision, a.areal, a.adecimal, a.adate, a.adatetime, a.atimestamp, a.atime, a.ayear, a.achar, a.avarchar, a.parentid from alltypeparent p, alltype a where p.parentid = a.parentid";
// execute the query to retrieve the departments
$root = $das->executeQuery($dbh, $stmt, array('alltypeparent.parentid', 'alltypeparent.description', 'alltype.abit', 'alltype.atinyint', 'alltype.aboolean', 'alltype.asmallint', 'alltype.amediumint', 'alltype.ainteger', 'alltype.abigint', 'alltype.afloat', 'alltype.adouble', 'alltype.adoubleprecision', 'alltype.areal', 'alltype.adecimal', 'alltype.adate', 'alltype.adatetime', 'alltype.atimestamp', 'alltype.atime', 'alltype.ayear', 'alltype.achar', 'alltype.avarchar'));
echo "\nprint_r root \n";
print_r($root);
echo "\n";
// get each alltype object and print location
| Author: Matthew Peters |
+----------------------------------------------------------------------+
$Id$
*/
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**
* Scenario - Retrieve multiple companies
*
* Retrieve multiple company rows from the company table.
*/
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Get a PDO database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Issue a query to obtain the company data objects
***************************************************************/
try {
$root = $das->executeQuery($dbh, 'select name, id from company', array('company.name', 'company.id'));
$das->applyChanges($dbh, $root);
echo "\nCompany Acme has been written to the database\n";
/**************************************************************
* Retrieve the company and Shoe department, then delete Shoe and add IT
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_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];
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$shoe = $acme['department'][0];
echo "Looked for Shoe department and found department with name = " . $shoe->name . " and id " . $shoe->id . "\n";
unset($acme['department'][0]);
$it = $acme->createDataObject('department');
$it->name = 'IT';
$das->applyChanges($dbh, $root);
echo "Deleted a department and added one\n";
/**************************************************************
* Retrieve the company and IT department, then delete the whole company
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_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];
echo "Looked for IT department and found department with name = " . $it->name . " and id " . $it->id . "\n";
unset($root['company'][0]);
$das->applyChanges($dbh, $root);
echo "Deleted Company and IT department\n";
| Author: Matthew Peters |
+----------------------------------------------------------------------+
$Id$
*/
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**
* Scenario - Retrieve and update multiple companies
*
* Retrieve multiple company rows from the company table.
*/
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Get a PDO database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Issue a query to obtain the company data object
***************************************************************/
try {
$root = $das->executeQuery($dbh, 'select name, id from company', array('company.name', 'company.id'));
public function testPreparedExecuteQuery_ValueListMustBeNullOrAnArray()
{
$company_table = array('name' => 'company', 'columns' => array('id', 'name', 'employee_of_the_month'), 'PK' => 'id');
$database_metadata = array($company_table);
$exception_thrown = false;
$das = new SDO_DAS_Relational($database_metadata);
try {
$company = $das->executePreparedQuery('dummy PDO handle', 'dummy PDO statement', 'a string, not an array or null');
} catch (SDO_DAS_Relational_Exception $e) {
$exception_thrown = true;
$msg = $e->getMessage();
}
$this->assertTrue($exception_thrown, 'Exception was never thrown');
$this->assertTrue(strpos($msg, 'third argument') != 0, 'Wrong message issued: ' . $msg);
$this->assertTrue(strpos($msg, 'must be null or an array') != 0, 'Wrong message issued: ' . $msg);
}
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$shoe = $acme['department'][0];
echo "Looked for Shoe department and found department with name = " . $shoe->name . " and id " . $shoe->id . "\n";
echo "location is ";
var_dump($shoe->location);
assert($shoe->location === null);
$shoe->location = 'Top floor';
$das->applyChanges($dbh, $root);
echo "Wrote back company with changed location for shoe department\n";
/**************************************************************
* Retrieve the company and check the location of the shoe department; further set it back to null again
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$root = $das->executeQuery($dbh, 'select c.id, c.name, d.id, d.name, d.location from company c, department d where d.co_id = c.id', array('company.id', 'company.name', 'department.id', 'department.name', 'department.location'));
$acme = $root['company'][0];
$shoe = $acme['department'][0];
assert($shoe->location == 'Top floor');
$shoe->location = null;
$das->applyChanges($dbh, $root);
/**************************************************************
* Retrieve the company and check the location of the shoe department is back to null
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$root = $das->executeQuery($dbh, 'select c.id, c.name, d.id, d.name, d.location from company c, department d where d.co_id = c.id', array('company.id', 'company.name', 'department.id', 'department.name', 'department.location'));
$acme = $root['company'][0];
$shoe = $acme['department'][0];
assert($shoe->location === null);
* The SDO will contain company, department, and employee objects in one graph.
*
* See companydb_mysql.sql and companydb_db2.sql for examples of defining the database
*************************************************************************************/
/*************************************************************************************
* Empty out the three tables
*************************************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$count = $dbh->exec('DELETE FROM company');
$count = $dbh->exec('DELETE FROM department');
$count = $dbh->exec('DELETE FROM employee');
/*************************************************************************************
* Get a Data Access Service
*************************************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/*************************************************************************************
* Create the root data object then a tiny but complete company.
* The company name is Acme.
* There are two departments, Shoe and IT.
* There are two employees, Sue and Billy.
* The employee of the month is Sue.
*************************************************************************************/
$root = $das->createRootDataObject();
$acme = $root->createDataObject('company');
* Create the root data object then a single company object underneath it.
* Set the company name to 'Acme'.
*************************************************************************************/
$das = new SDO_DAS_Relational($database_metadata);
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$root = $das->createRootDataObject();
$employee = $root->createDataObject('employee');
$employee->name = "Sue";
$employee->SN = "001";
$das->applyChanges($dbh, $root);
echo "Created an employee with name Sue and wrote it to the database\n";
/*************************************************************************************
* Find it again (with its autogenerated id this time).
* Then update it and write it back again.
*************************************************************************************/
$das = new SDO_DAS_Relational($database_metadata);
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$name = 'Sue';
$pdo_stmt = $dbh->prepare('select id, name, SN from employee where name=?');
$root = $das->executePreparedQuery($dbh, $pdo_stmt, array($name));
$sue = $root['employee'][0];
echo "Looked for Sue and found employee with name = " . $sue->name . " and SN " . $sue->SN . "\n";
$sue->name = 'Susan';
$sue->SN = 2;
$das->applyChanges($dbh, $root);
echo "Wrote back employee with name changed to Susan\n";
/*************************************************************************************
* Find it again under its new name, and delete it.
* Reuse the prepared query
*************************************************************************************/
$name = 'Susan';
// keep sue but we no longer have an employee of the month
$acme->employee_of_the_month = null;
$das->applyChanges($dbh, $root);
echo "Wrote back company with null for employee of the month\n";
/**************************************************************
* Retrieve the company and check the employee of the month is null; then set it back to Sue again
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$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', array('company.id', 'company.name', 'company.employee_of_the_month', 'department.id', 'department.name', 'employee.id', 'employee.name'));
$acme = $root['company'][0];
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$shoe = $acme['department'][0];
echo "Looked for Shoe department and found department with name = " . $shoe->name . " and id " . $shoe->id . "\n";
$sue = $shoe['employee'][0];
echo "Looked for Employee Sue and found employee with name = " . $sue->name . " and id " . $sue->id . "\n";
assert($acme->employee_of_the_month === null);
$acme->employee_of_the_month = $sue;
$das->applyChanges($dbh, $root);
/**************************************************************
* Retrieve the company and check the eotm is sue
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$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', array('company.id', 'company.name', 'company.employee_of_the_month', 'department.id', 'department.name', 'employee.id', 'employee.name'));
$acme = $root['company'][0];
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$sue = $acme->employee_of_the_month;
assert($sue->name == 'Sue');
| Author: Matthew Peters |
+----------------------------------------------------------------------+
$Id$
*/
require_once 'SDO/DAS/Relational.php';
require_once 'company_metadata.inc.php';
/**
* Scenario - Retrieve one company
*
* Retrieve one company row in the company table.
*/
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Get a database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Issue a query to obtain a company data object
***************************************************************/
try {
$root = $das->executeQuery($dbh, 'select name, id from company where name="Acme" or name="emcA"', array('company.name', 'company.id'));
