cvs: peardoc /en/packages/database db_dataobject.xml
| From: | Alan Knowles | Date: | Fri, 12 Jul 2002 04:25:24 +0000 |
| Subject: | cvs: peardoc /en/packages/database db_dataobject.xml | ||
| Groups: | php.pear.cvs | ||
| Request: | Send a blank email to pear-cvs+get-5059@lists.php.net to get a copy of this message | ||
alan_k Fri Jul 12 00:25:24 2002 EDT
Added files:
/peardoc/en/packages/database db_dataobject.xml
Log:
db dataobject added to manual
Index: peardoc/en/packages/database/db_dataobject.xml +++ peardoc/en/packages/database/db_dataobject.xml <?xml version="1.0" encoding="iso-8859-1" ?> <!-- $Revision: 1.1 $ --> <reference id="packages.database.db-dataobject"> <title>Database DataObjects : SQL Builder and Data Modeling Layer</title> <titleabbrev>DB_DataObject</titleabbrev> <partintro> <simpara> This chapter describes how to use the DB_DataObject SQL Builder and Data Modeling layer. </simpara> </partintro> <refentry id="packages.database.db-dataobject.Introduction"> <refnamediv> <refname>Introduction</refname> <refpurpose> What DB_DataObject can do </refpurpose> </refnamediv> <refsect1> <title>Introduction</title> <para> Db_DataObject is a SQL Builder and Data Modeling Layer built on top of PEAR DB, It main purpose is to </para> <para> <itemizedlist> <listitem> <simpara>Build SQL and execute statements based on the objects variables.</simpara> </listitem> <listitem> <simpara>Group source code around the Data that they relate to.</simpara> </listitem> <listitem> <simpara>Provide a simple consistent API to access and manipulate that data.</simpara> </listitem> </itemizedlist> </para> <para> The DB_DataObject is rarely accessed directly, it is intended to be the base class which you can extend to put the code specifically relating to a particular database table. Below is an example of how you can use the end result (and extended class), </para> <example> <title>Typical usage example for <classname>PEAR::DB_DataObject</classname></title> <programlisting role="php"> <![CDATA[ <? // initialize the DataObject Manager require_once('PEAR.php'); $options = &PEAR::getStaticProperty('DB_DataObject','options'); $config = parse_ini_file('example.ini',TRUE); $options = $config['DB_DataObject']; // use one of the extended classes. require_once('DataObjects/Person.php'); $person = new DataObjects_Person; $person->get(12); echo $person->name; ?> ]]> </programlisting> </example> <para> The above example illustrates the two components of the DataManager, By setting the options, all the core objects will be able to auto load the data definitions from a generated ini file, and know how to access the database. (multiple databases are supported - see section on configuration) </para> <para> The later half illustrates how to query and get the result for a single row. The $person->get() would connect to the database, perform the query, fetch the result and assign the objects variables to the data returned from the query. </para> <para> In the above example, this query would be performed. </para> <programlisting role="php"> SELECT * FROM person WHERE id=12; </programlisting> <para> To make a change to the Database you would just change the value of the objects variables and call the update method. </para> <programlisting role="php"> <![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->name = 'Fred'; $person->update(); ?> ]]> </programlisting> <para> The Package includes a number of tools to make building the extended classes and configuration file, Once you have made your extended classes, the idea is that any data manipulation/calculation that relates to this table, or how this table interacts with other data is placed inside the extended class. </para> <programlisting role="php"> <![CDATA[ <? require_once 'DB/DataObject.php'; class DataObjects_Person extends DB_DataObject { function &listMembership() { $members = new DataObjects_Members(); $members->person_id = $this->id; $members->find(); return $members; } } ?> ]]> </programlisting> <para> In this example the Person object asks the Members Object to list all records that have the column person_id equal to the current instance's id. </para> </refsect1> </refentry> <!-- ****************************************** Configuraton file ******************************************* --> <refentry id="packages.database.db-dataobject.configuration"> <refnamediv> <refname>Configuration Options</refname> <refpurpose> Setting the defaults for database Access </refpurpose> </refnamediv> <refsect1> <title>Configuration</title> <para> DB_DataObject needs to be configured before using it and auto generating classes and definitions. The easiest way to configure DB_DataObject is to use ini files (although you may also like to consider the PEAR::Config class, or your own configuration system) </para> <example> <title> This is a typical configuration file for <classname>DB_DataObject</classname></title> <programlisting role="ini"> [DB_DataObject] database = mysql://user:password@localhost/vending schema_location = /home/me/Projects/myapplication/DataObjects class_location = /home/me/Projects/myapplication/DataObjects require_prefix = DataObjects/ class_prefix = DataObjects_ </programlisting> </example> <para> To use this ini file with DB_DataObject, (and Possibly any other classes that use options like this) </para> <example> <title> Setting the default options</title> <programlisting role="php"><![CDATA[ <?php $config = parse_ini_file('example.ini',TRUE); foreach($config as $class=>$values) { $options = &PEAR::getStaticProperty($class,'options'); $options = $values; } ?> ]]> </programlisting> </example> </refsect1> <!-- Config Options --> <refsect1> <title>Configuration Options - Required</title> <variablelist> <varlistentry> <term> <parameter>database</parameter> <type>DSN</type> </term> <listitem> <para> This is the default DSN to connect to your database </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>schema_location</parameter> <type>directory</type> </term> <listitem> <para> The Directory where the DB_DataObject database schema file is store. </para> <para> DB_DataObject stores the description of the database (Tables and Columns) in an .ini file, in this directory. This information is used to determine if the column is a string and needs quotes, or should be a number (and is checked) at SQL building time. It is quite common to store the schema in the same directory as your DataObject Classes. </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>require_prefix</parameter> <type>directory</type> </term> <listitem> <para> The Path absolute, or relative to your default include path(s), where your extended classes can be found. </para> <para> This is used by the staticGet() method and the getLinks() method to auto load classes, </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>class_prefix</parameter> <type>string</type> </term> <listitem> <para> All the generated Classes are Named {class_prefix}ucfirst($table_name), Use this to alter the prefixed name, this is used by the staticGet() and getLinks() methods </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>debug</parameter> <type>integer</type> </term> <listitem> <para> The default debugging level (default 0=off), 1= basic sql logging,2=result logging, 3=everything </para> </listitem> </varlistentry> </variablelist> </refsect1> <!-- Config Options - Multi Database --> <refsect1> <title>Configuration Options - Multiple Databases (optional)</title> <variablelist> <varlistentry> <term> <parameter>database_*</parameter> <type>string</type> </term> <listitem> <para> When you have multiple databases you can use the database_* to specify the dsn for each database </para> <para> <example> <title> using multiple databases - database passwords</title> <programlisting role="ini"><![CDATA[ database_authentication = mysql://user:password@localhost/authentication database_sales = mysql://user:password@localhost/sales ]]></programlisting> </example> </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>table_*</parameter> <type>string</type> </term> <listitem> <para> When you have multiple databases you can use the table_* configuration variables to map individual tables to different databases, for example </para> <para> <example> <title> using multiple databases - table settings</title> <programlisting role="ini"> table_users = authentication table_saleslog = sales table_stock = sales </programlisting> </example> </para> </listitem> </varlistentry> </variablelist> </refsect1> <!-- Config Options - BUILDER --> <refsect1> <title>Configuration Options - Builder</title> <variablelist> <varlistentry> <term> <parameter>class_location</parameter> <type>directory</type> </term> <listitem> <para> The Directory where your DataObject extended Classes are. </para> <para> Used by the Class Auto Builder when updating/writing to your class definitions. </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>extends</parameter> <type>string</type> </term> <listitem> <para> The Name of your Base Class (usually DB_DataObject) is located. </para> <para> If you wish to add a common layer of useful methods for all classes, you can set the extends_location and extends settings to a different class. the default is 'DB_DataObject' </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>extends_location</parameter> <type>directory</type> </term> <listitem> <para> The Directory where your Base Class (usually DB_DataObject) is located. </para> <para> If you wish to add a common layer of useful methods for all classes, you can set the extends_location and extends settings to a different class. the default is 'DB/DataObject.php' </para> </listitem> </varlistentry> </variablelist> </refsect1> </refentry> <!-- ****************************************** Auto Building of Classes and Database schema ******************************************* --> <refentry id="packages.database.db-dataobject.autobuilding"> <refnamediv> <refname>Auto Building</refname> <refpurpose> creating the base Classes and Database schema </refpurpose> </refnamediv> <refsect1> <title>What the Auto Builder (createTables.php) Does</title> <para> One of the essential features of an SQL building tool is to to have some understanding of the database structure, So that Integers can be checked, and strings can be escaped. There a few ways that Querying the database for the table structure could be accomplished <itemizedlist> <listitem> <simpara>on every SQL query</simpara> </listitem> <listitem> <simpara>At the initialization of every web page.</simpara> </listitem> <listitem> <simpara>Once, while setting up the application, and store it in a file</simpara> </listitem> </itemizedlist> DB_DataObject uses the last of these, it utilizes the parse_ini_file function to read the file, so it should be reasonably fast. However this does involve a stage of setting up DB_DataObject prior to use. </para> <para> The other key concept of DB_DataObject is that you work with extended classes of DB_DataObject, which do all the 'table' related work. Setting up these classes for a large database can be time consuming, so the createTables.php file will automatically build the skeletons for all these class files. </para> <para> To start the auto builder simply go to the pear/DB/DataObject/ directory, and type <command>c:\php4\php.exe createTables.php myconfig.ini</command> this will read your configuration file and generate all the base classes, along with the data definition file. </para> </refsect1> <refsect1> <title>Default Class Definition</title> <para> The default generated class looks like this. <example> <title>an generated extended class</title> <programlisting role="php"><![CDATA[ <? /* * Table Definition for group */ class DataObjects_Grp extends DB_DataObject { ###START_AUTOCODE /* the code below is auto generated do not remove the above tag */ var $__table='group'; // table name var $id; // int primary_key var $name; // string var $grp_owner; // int var $official; // string var $street; // string var $postcode; // string var $city; // string var $homepage; // string var $email; // string var $extra; // blob /* Static get */ function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObjects_Grp',$k,$v); } /* the code above is auto generated do not remove the tag below */ ###END_AUTOCODE } ?>]]> </programlisting> </example> </para> <para> The class defines the table name, and comments some of table columns for your reference, It also adds the staticGet() method, which can be used to quickly get single rows as Objects. You should add your table related code after the ###END_AUTOCODE. </para> </refsect1> <refsect1> <title>Database Definition File</title> <para> The default generated database definition file looks like this. <example> <title>Database configuration file</title> <programlisting role="ini"><![CDATA[ [group] id = 1 name = 2 grp_owner = 1 official = 2 street = 2 postcode = 2 city = 2 homepage = 2 email = 2 extra = 2 [group__keys] id = 1 ]]> </programlisting> </example> </para> <para> The blocks indicate either tables and the type of field (1=integer,2=string), or a list of keys for each table. You should not need to edit file. </para> </refsect1> </refentry> <!-- ****************************************** Simple Finding and fetching ******************************************* --> <refentry id="packages.database.db-dataobject.find_fetch"> <refnamediv> <refname>Finding and fetching</refname> <refpurpose> Simple Querying methods ::staticGet() ::get() ::find() ::fetch() ::count() </refpurpose> </refnamediv> <refsect1> <title>::get()</title> <para> Simple Get (Select) request, are achieved using the get method </para> <funcsynopsis> <funcprototype> <funcdef><function>::get</function></funcdef> <paramdef>mixed <parameter>Key or Value</parameter></paramdef> <paramdef>mixed <parameter><optional>Value</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The basic get method for an extended class can be used in two ways, to get based on the primary key (one parameter) or based on a key and value (two parameters) <example> <title> Simple fetch of data based on Primary Key</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); print_r($person); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=12 ]]> </programlisting> </example> </para> <para> To use another field other than the primary key, you would use it with two parameters <example> <title> Simple fetch of data based on Key and Value</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get('email','test@example.com'); print_r($person); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE email='test@example.com' ]]> </programlisting> </example> </para> <para> The result of these two queries would be that all the objects variables would contain the matched row's data and the variable $person->N would contain the number of matches. <example> <title> Results of example code</title> <programlisting role="ini"><![CDATA[ Object (DataObjects_Person) => [N] => 1 [id] => 12 [group] => 5 [has_glasses] => 1 [name] => 'fred blogs' [password] => '**testing' [email] => 'test@example.com' ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::staticGet()</title> <para> Simple Get (Select) request, abbreviated get request. </para> <funcsynopsis> <funcprototype> <funcdef><function>::staticGet</function></funcdef> <paramdef>mixed <parameter>Key or Value</parameter></paramdef> <paramdef>mixed <parameter><optional>Value</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The static method is similar to the get request, however it does not require the initial instantiation of the class. staticGet also caches the result. <example> <title> Simple fetch of data based on Primary Key</title> <programlisting role="php"><![CDATA[ <? $person = DataObjects_Person::staticGet(12); print_r($person); ?> ]]> </programlisting> </example> </para> <para> You may also use the DB_DataObject staticGet method directly, however you need to specify which class you wish to use. In this case, the Class file will be automatically loaded. <example> <title> Simple fetch of data based on Primary Key</title> <programlisting role="php"><![CDATA[ <? $person = DB_DataObject::staticGet('DataObjects_Person',12); print_r($person); ?> ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::find()</title> <para> Searching for Items </para> <funcsynopsis> <funcprototype> <funcdef>integer <function>::find</function></funcdef> <paramdef>boolean <parameter><optional>AutoFetch</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The find method builds and executes the current Query, based on the object variables and any 'WhereAdd' conditions , If the AutoFetch is TRUE, then it will also call the fetch method automatically. <example> <title> Simple find of data based on Object Vars</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->hair = 'red'; $person->has_glasses = 1; $number_of_rows = $person->find(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE hair='red' and has_glasses = 1 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::fetch()</title> <para> Getting the data. </para> <funcsynopsis> <funcprototype> <funcdef>boolean <function>::fetch</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The fetch method gets the next row and sets the objects variables to the rows data. It returns true if data has been collected, and false when there is no more data. <example> <title> Simple find and fetch of data based on Object Vars</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->hair = 'red'; $person->has_glasses = 1; $number_of_rows = $person->find(); $person = array(); while ($person->fetch()) { $people[] = $person; echo "GOT {$person->name}<BR>"; } ?> ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::count()</title> <para> Performs a select count() request </para> <funcsynopsis> <funcprototype> <funcdef>int <function>::count</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Performs a select count() request on the tables key column, and returns the number of rows returned <example> <title> Simple find and fetch of data based on Object Vars</title> <programlisting role="php"><![CDATA[ $person = new DataObjects_Person; $person->whereAdd('name like '%test%'); $total = $person->count(); echo "There are {$total} people with a name like test"; ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT count(id) FROM person WHERE name like '%test%' ]]> </programlisting> </example> </para> </refsect1> </refentry> <!-- ****************************************** Updating inserting and deleting ******************************************* update, insert, delete, replace (TODO) , setFrom, --> <refentry id="packages.database.db-dataobject.updating"> <refnamediv> <refname>Modifying the database</refname> <refpurpose> ::update(), ::insert(), ::delete(), </refpurpose> </refnamediv> <refsect1> <title>::update()</title> <para> Updating the data. </para> <funcsynopsis> <funcprototype> <funcdef>boolean <function>::update</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> updates the database using the objects variables, returns TRUE on success <example> <title> Simple fetch and update</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->name='fred'; $person->update(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, both fetching and updating the database. <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id = 12 UPDATE person SET name='fred', age='21', eyes='blue' WHERE id = 12 ]]> </programlisting> </example> </para> <para> To perform a general update on a table, use the WhereAdd() method <example> <title> Simple fetch and updated</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->removed=1; $person->whereAdd('age > 21'); $person->update(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, both fetching and updating the database. <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id = 12; UPDATE person SET removed=1 WHERE age > 21 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::insert()</title> <para> Inserting data. </para> <funcsynopsis> <funcprototype> <funcdef>int <function>::insert</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Inserts data into the database and returns the key/ID if any. <example> <title> Simple insert </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->name='fred'; $id = $person->insert(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, (It may also do sequences on some databases, it uses mysql_next_id on mysql) <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ INSERT INTO person (name) VALUES ('fred'); ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::delete()</title> <para> Deleting data. </para> <funcsynopsis> <funcprototype> <funcdef><function>::delete</function></funcdef> <paramdef>boolean <parameter><optional>UseWhere</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Deletes data from the database, either using primary key or based on a WhereAdd() method call. by default the delete will base it's query on the set variables, however if you wish to use the WhereAdd method you should set the UseWhere parameter to TRUE <example> <title> Simple Delete </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->delete(); $person = new DataObjects_Person; $person->whereAdd('age < 21'); $person->delete(TRUE); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=12 DELETE FROM person WHERE id=12 AND name='test' AND age=21 DELETE FROM person WHERE age < 21 ]]> </programlisting> </example> </para> </refsect1> </refentry> <!-- ****************************************** Advanced Filters ******************************************* query, SelectAdd, whereAdd, Limit, OrderBy, GroupBy, --> <refentry id="packages.database.db-dataobject.filters"> <refnamediv> <refname>Advanced Filters</refname> <refpurpose> Advanced Filters - ::query(), ::SelectAdd(), ::whereAdd(), ::Limit(), ::OrderBy(), ::GroupBy(), </refpurpose> </refnamediv> <refsect1> <title>More advanced Querying</title> <para> The basic features allow most simple queries to be done very quickly, however building more complex queries can be done using the methods listed below, which append or set conditions that build the query. You will find that there is a fine balance between using these builder methods and just using raw SQL. </para> <para> The DB_DataObject class does not handle JOIN queries directly for the simple reason that it is designed to simplify the use of databases, and make the code that access them clear and easy to read. Having experimented with a Join Version, it became very clear that this did not fulfill either of these goals, and made the resulting code more difficult to read, debug and understand. So in reality it is best to use the raw query() method to do joined queries.. </para> </refsect1> <refsect1> <title>::query()</title> <para> Raw Database Queries </para> <funcsynopsis> <funcprototype> <funcdef><function>::query</function></funcdef> <paramdef>string <parameter>query</parameter></paramdef> </funcprototype> </funcsynopsis> <para> Sends a raw Query to the database <example> <title> raw Queries on the database </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->query("select * from {$this->__table} where id > 12"); while ($person->fetch()) { echo $person->name; } ?> ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::selectAdd()</title> <para> Adding and removing Items from select query </para> <funcsynopsis> <funcprototype> <funcdef><function>::select</function></funcdef> <paramdef>string <parameter><optional>Select Items</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> By default a select query will request all items '*', to change this behavior you can first call selectAdd() without any arguments to clear the current request and then add the specific items you require. <example> <title> Adding select statements</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->selectAdd(); $person->selectAdd('id,name'); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } $person = new DataObjects_Person; $person->selectAdd("birthday_formated as date('%d %M %y')"); $person->id = 12; $person->find(TRUE); echo "$person->name} {$person->birthday_formated}<BR>"; } ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT id,name FROM person SELECT *, birthday_formated as date('%d %M %y') FROM person WHERE id=12 ]]> </programlisting> </example> </para> <para> You can also set up a default selection query by adding SelectAdd() method calls in the object initialization method (the one with the same name as the class) </para> </refsect1> <refsect1> <title>::whereAdd()</title> <para> Adds conditions to the Where part of the query </para> <funcsynopsis> <funcprototype> <funcdef><function>::whereAdd</function></funcdef> <paramdef>string <parameter><optional>Where</optional></parameter></paramdef> <paramdef>string <parameter><optional>OR</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Adds items to the where part of a SQL query. Calling this without any arguments clears, the where condition. The default behavior is to add 'AND' queries, send the string 'OR' to append OR conditions. <example> <title> Adding Where Statements</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->whereAdd('age > 12'); $person->whereAdd('age < 30'); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } $person = new DataObjects_Person; $person->whereAdd('age < 12'); $person->whereAdd('age > 30', 'OR'); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE age > 12 AND age < 30 SELECT * FROM person WHERE age < 12 OR age > 30 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::limit()</title> <para> Sets the limit of a query </para> <funcsynopsis> <funcprototype> <funcdef><function>::limit</function></funcdef> <paramdef>int <parameter><optional>(Limit Range Start or Number)</optional></parameter></paramdef> <paramdef>int <parameter><optional>(Limit Range Number)</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Sets the limit range. Calling this without any arguments clears the range <example> <title> Setting the Limit</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->limit(2); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } $person = new DataObjects_Person; $person->limit(2,4); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example><title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person LIMIT 2 SELECT * FROM person LIMIT 2,4 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::orderBy()</title> <para> Appends to the 'order by' part of the query </para> <funcsynopsis> <funcprototype> <funcdef><function>::orderBy</function></funcdef> <paramdef>string <parameter><optional>Order</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Appends an Order condition. Calling this without any arguments clears the order <example> <title> setting the order by</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->orderBy('name'); $person->orderBy('age, eye'); $person->find(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person ORDER BY name, age, eye ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::groupBy()</title> <para> Appends a to the 'group by' part of the query </para> <funcsynopsis> <funcprototype> <funcdef><function>::groupBy</function></funcdef> <paramdef>string <parameter><optional>Group</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Appends a Group condition. Calling this without any arguments clears the Group Condition <example> <title> Setting the Group by </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->groupBy('name'); $person->groupBy('age, eye'); $person->find(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person GROUP BY name, age, eye ]]> </programlisting> </example> </para> </refsect1> </refentry> <!-- ****************************************** Links getLink(), getLinks(), getLinkArray() ******************************************* --> <refentry id="packages.database.db-dataobject.links"> <refnamediv> <refname>Automatic Table Linking</refname> <refpurpose> Automatic Table Linking - ::getLink(), ::getLinks() </refpurpose> </refnamediv> <refsect1> <title>Automating the collection of related data</title> <para> When designing a database, often some tables are related to others - a membership table would contain a reference to a person's id and the group id that they are a member of. Using the Link methods, you can automatically fetch objects into the parents variables. </para> <para> The Automated method of doing this is heavily dependent on naming columns relating to databases, for example in the membership described above, you would have to have a column called person_id, group_id for the automated Link methods to understand which table to fetch the data from. The principle is TABLENAME_ANYTHING, and it will always attempt to find primary keys that match the value of field, the getLinks() methods will auto load the related object into $object->_tablename_anything </para> <para> The other way of using getLink() is to use it with the second parameter which is the table name of the related table, it will then query the primary key on that table to see if it matches the value of the requested row. In this method however the getLinks() method will not work, as it will not know the related table names. </para> </refsect1> <refsect1> <title>::getLink()</title> <para> Gets the Related Row Object from the Related Table </para> <funcsynopsis> <funcprototype> <funcdef><function>::getLink</function></funcdef> <paramdef>string <parameter>Row </parameter></paramdef> <paramdef>string <parameter><optional>Table</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Gets the related Object <example> <title> Getting the related object </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $group = $person->getLink('group_owner'); echo $group->name; $group = $person->getLink('colourid','hair'); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, and fetch the data <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=12 SELECT * FROM group WHERE id=3 SELECT * FROM hair WHERE id=4 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::getLinks()</title> <para> Loads all the related Objects </para> <funcsynopsis> <funcprototype> <funcdef><function>::getLinks</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Loads the all the related objects into the main object, based on the naming of columns using the tablename_xxx naming method, and sets the main objects variables with the row name prefixed with a _ <example> <title> Two Example Tables</title> <programlisting role="sql"><![CDATA[ Person +---------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+----------------+ | id | mediumint(9) | | PRI | 0 | auto_increment | | first_name | varchar(80) | YES | | NULL | | | last_name | varchar(80) | | MUL | | | | middle_name | varchar(80) | YES | | NULL | | | badge_number | smallint(6) | YES | | NULL | | | street | varchar(80) | YES | | NULL | | | city | varchar(80) | YES | | NULL | | | state | varchar(80) | YES | | NULL | | | zip | varchar(15) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | | reg_type | varchar(80) | YES | | NULL | | | judge | varchar(10) | YES | | NULL | | | staff | varchar(10) | YES | | NULL | | | volunteer | varchar(10) | YES | | NULL | | | rpga_number | mediumint(9) | YES | | NULL | | | total_fee | float(10,2) | YES | | NULL | | | email_address | varchar(80) | YES | | NULL | | | country | varchar(30) | YES | | NULL | | | convention_id | int(11) | | | 0 | | | last_modified | timestamp(14) | YES | | NULL | | +---------------+---------------+------+-----+---------+----------------+ Convention +----------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | | name | varchar(50) | | | | | | sponsor_organization | varchar(50) | | | | | | rpga_convention_code | varchar(20) | | | | | | web_site_url | varchar(200) | | | | | | last_modified | timestamp(14) | YES | | NULL | | +----------------------+---------------+------+-----+---------+----------------+ ]]> </programlisting> </example> <example> <title> Loading all the related objects </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(1079); $person->getLinks(); print_r($person); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement, and fetch the data <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=1079 SELECT * FROM convention WHERE id=1 ]]> </programlisting> </example> And output the following data <example> <title> Resulting Output </title> <programlisting role="sql"><![CDATA[ Object:dataobjects_person Object ( [_DB_DataObject_version] => 1.0 [__table] => person [_database_dsn] => [_database_dsn_md5] => 3974043abbccdd6412fb156a1d10b98377 [_database] => testing [_condition] => [_group_by] => [_order_by] => [_limit] => [_data_select] => * [_link_loaded] => 1 [_lastError] => pear_error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -3 [message] => getLink:Could not find class for row last_modified, table last [userinfo] => [callback] => ) [id] => 1079 [N] => 1 [_DB_resultid] => 2 [first_name] => Tim [last_name] => White [middle_name] => [badge_number] => 123 [street] => 334411 N Washington [city] => Texas [state] => CO [zip] => 12345 [phone] => 343412323232 [reg_type] => Staff [judge] => [staff] => CHECKED [volunteer] => [rpga_number] => 1232323 [total_fee] => 0.00 [email_address] => tim@example.com [country] => USA [convention_id] => 1 [last_modified] => 20020711084539 [_first_name] => [_last_name] => [_middle_name] => [_badge_number] => [_reg_type] => [_rpga_number] => [_total_fee] => [_email_address] => [_convention_id] => dataobjects_convention Object ( [_DB_DataObject_version] => 1.0 [__table] => convention [_database_dsn] => [_database_dsn_md5] => 3974043abbcc86412fb156a1d10b98377 [_database] => testing [_condition] => [_group_by] => [_order_by] => [_limit] => [_data_select] => * [_link_loaded] => [_lastError] => [id] => 1 [N] => 1 [_DB_resultid] => 3 [name] => ABCD XYZ [sponsor_organization] => some sponser [rpga_convention_code] => ABCD_XYZ [web_site_url] => http://example.com [last_modified] => 20020703143828 ) [_last_modified] => ) ]]> </programlisting> </example> </para> <para> In the example above, alot of the columns have '_' underscores in and do not relate to tables, DB_DataObject will only flag it as an error in $object->_lastError. </para> </refsect1> </refentry> <!-- ****************************************** AutoForm Utilities and Debugging ******************************************* --> <refentry id="packages.database.db-dataobject.utils"> <refnamediv> <refname>AutoForm Utilities and Debugging</refname> <refpurpose> ::setFrom(), ::debugLevel() </refpurpose> </refnamediv> <refsect1> <title>::setFrom()</title> <para> Copies data from an Array or object into the current object </para> <funcsynopsis> <funcprototype> <funcdef><function>::setFrom</function></funcdef> <paramdef>(Array or Object) <parameter>from</parameter></paramdef> </funcprototype> </funcsynopsis> <para> copies all the data that relates to this table (Except keys) into the current object <example> <title> Copying data from post or object</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->setFrom($_POST['input']); $person->update(); // or copying from another object $personA = new DataObjects_Person; $personA->get(12); $personB = new DataObjects_Person; $personB->get(12); $personA->setFrom($personB); $person->update(); ?> ]]> </programlisting> </example> </para> <para> Copies all the data (with names the of this objects columns) from another object or array into this one. </para> </refsect1> <refsect1> <title>DB_DataObject::debugLevel()</title> <para> Sets the debugging level - so you can see the queries being built and executed </para> <funcsynopsis> <funcprototype> <funcdef><function>DB_DataObject::debugLevel</function></funcdef> <paramdef>int <parameter><optional>debug level</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Sets the debugging level (without any parameters it will disable the debugging output) See the configuration page for details on the various levels. <example> <title> Setting the debugging level </title> <programlisting role="php"><![CDATA[ <? // turn debugging high DB_DataObject::debugLevel(5); $person = new DataObjects_Person; $person->get(12); $person->setFrom($_POST['input']); $person->update(); // turn debugging off DB_DataObject::debugLevel(); ?> ]]> </programlisting> </example> </para> </refsect1> </refentry> </reference> <!-- 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 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 -->
Index: peardoc/en/packages/database/db_dataobject.xml +++ peardoc/en/packages/database/db_dataobject.xml <?xml version="1.0" encoding="iso-8859-1" ?> <!-- $Revision: 1.1 $ --> <reference id="packages.database.db-dataobject"> <title>Database DataObjects : SQL Builder and Data Modeling Layer</title> <titleabbrev>DB_DataObject</titleabbrev> <partintro> <simpara> This chapter describes how to use the DB_DataObject SQL Builder and Data Modeling layer. </simpara> </partintro> <refentry id="packages.database.db-dataobject.Introduction"> <refnamediv> <refname>Introduction</refname> <refpurpose> What DB_DataObject can do </refpurpose> </refnamediv> <refsect1> <title>Introduction</title> <para> Db_DataObject is a SQL Builder and Data Modeling Layer built on top of PEAR DB, It main purpose is to </para> <para> <itemizedlist> <listitem> <simpara>Build SQL and execute statements based on the objects variables.</simpara> </listitem> <listitem> <simpara>Group source code around the Data that they relate to.</simpara> </listitem> <listitem> <simpara>Provide a simple consistent API to access and manipulate that data.</simpara> </listitem> </itemizedlist> </para> <para> The DB_DataObject is rarely accessed directly, it is intended to be the base class which you can extend to put the code specifically relating to a particular database table. Below is an example of how you can use the end result (and extended class), </para> <example> <title>Typical usage example for <classname>PEAR::DB_DataObject</classname></title> <programlisting role="php"> <![CDATA[ <? // initialize the DataObject Manager require_once('PEAR.php'); $options = &PEAR::getStaticProperty('DB_DataObject','options'); $config = parse_ini_file('example.ini',TRUE); $options = $config['DB_DataObject']; // use one of the extended classes. require_once('DataObjects/Person.php'); $person = new DataObjects_Person; $person->get(12); echo $person->name; ?> ]]> </programlisting> </example> <para> The above example illustrates the two components of the DataManager, By setting the options, all the core objects will be able to auto load the data definitions from a generated ini file, and know how to access the database. (multiple databases are supported - see section on configuration) </para> <para> The later half illustrates how to query and get the result for a single row. The $person->get() would connect to the database, perform the query, fetch the result and assign the objects variables to the data returned from the query. </para> <para> In the above example, this query would be performed. </para> <programlisting role="php"> SELECT * FROM person WHERE id=12; </programlisting> <para> To make a change to the Database you would just change the value of the objects variables and call the update method. </para> <programlisting role="php"> <![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->name = 'Fred'; $person->update(); ?> ]]> </programlisting> <para> The Package includes a number of tools to make building the extended classes and configuration file, Once you have made your extended classes, the idea is that any data manipulation/calculation that relates to this table, or how this table interacts with other data is placed inside the extended class. </para> <programlisting role="php"> <![CDATA[ <? require_once 'DB/DataObject.php'; class DataObjects_Person extends DB_DataObject { function &listMembership() { $members = new DataObjects_Members(); $members->person_id = $this->id; $members->find(); return $members; } } ?> ]]> </programlisting> <para> In this example the Person object asks the Members Object to list all records that have the column person_id equal to the current instance's id. </para> </refsect1> </refentry> <!-- ****************************************** Configuraton file ******************************************* --> <refentry id="packages.database.db-dataobject.configuration"> <refnamediv> <refname>Configuration Options</refname> <refpurpose> Setting the defaults for database Access </refpurpose> </refnamediv> <refsect1> <title>Configuration</title> <para> DB_DataObject needs to be configured before using it and auto generating classes and definitions. The easiest way to configure DB_DataObject is to use ini files (although you may also like to consider the PEAR::Config class, or your own configuration system) </para> <example> <title> This is a typical configuration file for <classname>DB_DataObject</classname></title> <programlisting role="ini"> [DB_DataObject] database = mysql://user:password@localhost/vending schema_location = /home/me/Projects/myapplication/DataObjects class_location = /home/me/Projects/myapplication/DataObjects require_prefix = DataObjects/ class_prefix = DataObjects_ </programlisting> </example> <para> To use this ini file with DB_DataObject, (and Possibly any other classes that use options like this) </para> <example> <title> Setting the default options</title> <programlisting role="php"><![CDATA[ <?php $config = parse_ini_file('example.ini',TRUE); foreach($config as $class=>$values) { $options = &PEAR::getStaticProperty($class,'options'); $options = $values; } ?> ]]> </programlisting> </example> </refsect1> <!-- Config Options --> <refsect1> <title>Configuration Options - Required</title> <variablelist> <varlistentry> <term> <parameter>database</parameter> <type>DSN</type> </term> <listitem> <para> This is the default DSN to connect to your database </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>schema_location</parameter> <type>directory</type> </term> <listitem> <para> The Directory where the DB_DataObject database schema file is store. </para> <para> DB_DataObject stores the description of the database (Tables and Columns) in an .ini file, in this directory. This information is used to determine if the column is a string and needs quotes, or should be a number (and is checked) at SQL building time. It is quite common to store the schema in the same directory as your DataObject Classes. </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>require_prefix</parameter> <type>directory</type> </term> <listitem> <para> The Path absolute, or relative to your default include path(s), where your extended classes can be found. </para> <para> This is used by the staticGet() method and the getLinks() method to auto load classes, </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>class_prefix</parameter> <type>string</type> </term> <listitem> <para> All the generated Classes are Named {class_prefix}ucfirst($table_name), Use this to alter the prefixed name, this is used by the staticGet() and getLinks() methods </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>debug</parameter> <type>integer</type> </term> <listitem> <para> The default debugging level (default 0=off), 1= basic sql logging,2=result logging, 3=everything </para> </listitem> </varlistentry> </variablelist> </refsect1> <!-- Config Options - Multi Database --> <refsect1> <title>Configuration Options - Multiple Databases (optional)</title> <variablelist> <varlistentry> <term> <parameter>database_*</parameter> <type>string</type> </term> <listitem> <para> When you have multiple databases you can use the database_* to specify the dsn for each database </para> <para> <example> <title> using multiple databases - database passwords</title> <programlisting role="ini"><![CDATA[ database_authentication = mysql://user:password@localhost/authentication database_sales = mysql://user:password@localhost/sales ]]></programlisting> </example> </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>table_*</parameter> <type>string</type> </term> <listitem> <para> When you have multiple databases you can use the table_* configuration variables to map individual tables to different databases, for example </para> <para> <example> <title> using multiple databases - table settings</title> <programlisting role="ini"> table_users = authentication table_saleslog = sales table_stock = sales </programlisting> </example> </para> </listitem> </varlistentry> </variablelist> </refsect1> <!-- Config Options - BUILDER --> <refsect1> <title>Configuration Options - Builder</title> <variablelist> <varlistentry> <term> <parameter>class_location</parameter> <type>directory</type> </term> <listitem> <para> The Directory where your DataObject extended Classes are. </para> <para> Used by the Class Auto Builder when updating/writing to your class definitions. </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>extends</parameter> <type>string</type> </term> <listitem> <para> The Name of your Base Class (usually DB_DataObject) is located. </para> <para> If you wish to add a common layer of useful methods for all classes, you can set the extends_location and extends settings to a different class. the default is 'DB_DataObject' </para> </listitem> </varlistentry> <varlistentry> <term> <parameter>extends_location</parameter> <type>directory</type> </term> <listitem> <para> The Directory where your Base Class (usually DB_DataObject) is located. </para> <para> If you wish to add a common layer of useful methods for all classes, you can set the extends_location and extends settings to a different class. the default is 'DB/DataObject.php' </para> </listitem> </varlistentry> </variablelist> </refsect1> </refentry> <!-- ****************************************** Auto Building of Classes and Database schema ******************************************* --> <refentry id="packages.database.db-dataobject.autobuilding"> <refnamediv> <refname>Auto Building</refname> <refpurpose> creating the base Classes and Database schema </refpurpose> </refnamediv> <refsect1> <title>What the Auto Builder (createTables.php) Does</title> <para> One of the essential features of an SQL building tool is to to have some understanding of the database structure, So that Integers can be checked, and strings can be escaped. There a few ways that Querying the database for the table structure could be accomplished <itemizedlist> <listitem> <simpara>on every SQL query</simpara> </listitem> <listitem> <simpara>At the initialization of every web page.</simpara> </listitem> <listitem> <simpara>Once, while setting up the application, and store it in a file</simpara> </listitem> </itemizedlist> DB_DataObject uses the last of these, it utilizes the parse_ini_file function to read the file, so it should be reasonably fast. However this does involve a stage of setting up DB_DataObject prior to use. </para> <para> The other key concept of DB_DataObject is that you work with extended classes of DB_DataObject, which do all the 'table' related work. Setting up these classes for a large database can be time consuming, so the createTables.php file will automatically build the skeletons for all these class files. </para> <para> To start the auto builder simply go to the pear/DB/DataObject/ directory, and type <command>c:\php4\php.exe createTables.php myconfig.ini</command> this will read your configuration file and generate all the base classes, along with the data definition file. </para> </refsect1> <refsect1> <title>Default Class Definition</title> <para> The default generated class looks like this. <example> <title>an generated extended class</title> <programlisting role="php"><![CDATA[ <? /* * Table Definition for group */ class DataObjects_Grp extends DB_DataObject { ###START_AUTOCODE /* the code below is auto generated do not remove the above tag */ var $__table='group'; // table name var $id; // int primary_key var $name; // string var $grp_owner; // int var $official; // string var $street; // string var $postcode; // string var $city; // string var $homepage; // string var $email; // string var $extra; // blob /* Static get */ function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObjects_Grp',$k,$v); } /* the code above is auto generated do not remove the tag below */ ###END_AUTOCODE } ?>]]> </programlisting> </example> </para> <para> The class defines the table name, and comments some of table columns for your reference, It also adds the staticGet() method, which can be used to quickly get single rows as Objects. You should add your table related code after the ###END_AUTOCODE. </para> </refsect1> <refsect1> <title>Database Definition File</title> <para> The default generated database definition file looks like this. <example> <title>Database configuration file</title> <programlisting role="ini"><![CDATA[ [group] id = 1 name = 2 grp_owner = 1 official = 2 street = 2 postcode = 2 city = 2 homepage = 2 email = 2 extra = 2 [group__keys] id = 1 ]]> </programlisting> </example> </para> <para> The blocks indicate either tables and the type of field (1=integer,2=string), or a list of keys for each table. You should not need to edit file. </para> </refsect1> </refentry> <!-- ****************************************** Simple Finding and fetching ******************************************* --> <refentry id="packages.database.db-dataobject.find_fetch"> <refnamediv> <refname>Finding and fetching</refname> <refpurpose> Simple Querying methods ::staticGet() ::get() ::find() ::fetch() ::count() </refpurpose> </refnamediv> <refsect1> <title>::get()</title> <para> Simple Get (Select) request, are achieved using the get method </para> <funcsynopsis> <funcprototype> <funcdef><function>::get</function></funcdef> <paramdef>mixed <parameter>Key or Value</parameter></paramdef> <paramdef>mixed <parameter><optional>Value</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The basic get method for an extended class can be used in two ways, to get based on the primary key (one parameter) or based on a key and value (two parameters) <example> <title> Simple fetch of data based on Primary Key</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); print_r($person); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=12 ]]> </programlisting> </example> </para> <para> To use another field other than the primary key, you would use it with two parameters <example> <title> Simple fetch of data based on Key and Value</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get('email','test@example.com'); print_r($person); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE email='test@example.com' ]]> </programlisting> </example> </para> <para> The result of these two queries would be that all the objects variables would contain the matched row's data and the variable $person->N would contain the number of matches. <example> <title> Results of example code</title> <programlisting role="ini"><![CDATA[ Object (DataObjects_Person) => [N] => 1 [id] => 12 [group] => 5 [has_glasses] => 1 [name] => 'fred blogs' [password] => '**testing' [email] => 'test@example.com' ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::staticGet()</title> <para> Simple Get (Select) request, abbreviated get request. </para> <funcsynopsis> <funcprototype> <funcdef><function>::staticGet</function></funcdef> <paramdef>mixed <parameter>Key or Value</parameter></paramdef> <paramdef>mixed <parameter><optional>Value</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The static method is similar to the get request, however it does not require the initial instantiation of the class. staticGet also caches the result. <example> <title> Simple fetch of data based on Primary Key</title> <programlisting role="php"><![CDATA[ <? $person = DataObjects_Person::staticGet(12); print_r($person); ?> ]]> </programlisting> </example> </para> <para> You may also use the DB_DataObject staticGet method directly, however you need to specify which class you wish to use. In this case, the Class file will be automatically loaded. <example> <title> Simple fetch of data based on Primary Key</title> <programlisting role="php"><![CDATA[ <? $person = DB_DataObject::staticGet('DataObjects_Person',12); print_r($person); ?> ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::find()</title> <para> Searching for Items </para> <funcsynopsis> <funcprototype> <funcdef>integer <function>::find</function></funcdef> <paramdef>boolean <parameter><optional>AutoFetch</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The find method builds and executes the current Query, based on the object variables and any 'WhereAdd' conditions , If the AutoFetch is TRUE, then it will also call the fetch method automatically. <example> <title> Simple find of data based on Object Vars</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->hair = 'red'; $person->has_glasses = 1; $number_of_rows = $person->find(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE hair='red' and has_glasses = 1 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::fetch()</title> <para> Getting the data. </para> <funcsynopsis> <funcprototype> <funcdef>boolean <function>::fetch</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> The fetch method gets the next row and sets the objects variables to the rows data. It returns true if data has been collected, and false when there is no more data. <example> <title> Simple find and fetch of data based on Object Vars</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->hair = 'red'; $person->has_glasses = 1; $number_of_rows = $person->find(); $person = array(); while ($person->fetch()) { $people[] = $person; echo "GOT {$person->name}<BR>"; } ?> ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::count()</title> <para> Performs a select count() request </para> <funcsynopsis> <funcprototype> <funcdef>int <function>::count</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Performs a select count() request on the tables key column, and returns the number of rows returned <example> <title> Simple find and fetch of data based on Object Vars</title> <programlisting role="php"><![CDATA[ $person = new DataObjects_Person; $person->whereAdd('name like '%test%'); $total = $person->count(); echo "There are {$total} people with a name like test"; ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement and fetch the results into the objects variables <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT count(id) FROM person WHERE name like '%test%' ]]> </programlisting> </example> </para> </refsect1> </refentry> <!-- ****************************************** Updating inserting and deleting ******************************************* update, insert, delete, replace (TODO) , setFrom, --> <refentry id="packages.database.db-dataobject.updating"> <refnamediv> <refname>Modifying the database</refname> <refpurpose> ::update(), ::insert(), ::delete(), </refpurpose> </refnamediv> <refsect1> <title>::update()</title> <para> Updating the data. </para> <funcsynopsis> <funcprototype> <funcdef>boolean <function>::update</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> updates the database using the objects variables, returns TRUE on success <example> <title> Simple fetch and update</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->name='fred'; $person->update(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, both fetching and updating the database. <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id = 12 UPDATE person SET name='fred', age='21', eyes='blue' WHERE id = 12 ]]> </programlisting> </example> </para> <para> To perform a general update on a table, use the WhereAdd() method <example> <title> Simple fetch and updated</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->removed=1; $person->whereAdd('age > 21'); $person->update(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, both fetching and updating the database. <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id = 12; UPDATE person SET removed=1 WHERE age > 21 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::insert()</title> <para> Inserting data. </para> <funcsynopsis> <funcprototype> <funcdef>int <function>::insert</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Inserts data into the database and returns the key/ID if any. <example> <title> Simple insert </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->name='fred'; $id = $person->insert(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, (It may also do sequences on some databases, it uses mysql_next_id on mysql) <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ INSERT INTO person (name) VALUES ('fred'); ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::delete()</title> <para> Deleting data. </para> <funcsynopsis> <funcprototype> <funcdef><function>::delete</function></funcdef> <paramdef>boolean <parameter><optional>UseWhere</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Deletes data from the database, either using primary key or based on a WhereAdd() method call. by default the delete will base it's query on the set variables, however if you wish to use the WhereAdd method you should set the UseWhere parameter to TRUE <example> <title> Simple Delete </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->delete(); $person = new DataObjects_Person; $person->whereAdd('age < 21'); $person->delete(TRUE); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=12 DELETE FROM person WHERE id=12 AND name='test' AND age=21 DELETE FROM person WHERE age < 21 ]]> </programlisting> </example> </para> </refsect1> </refentry> <!-- ****************************************** Advanced Filters ******************************************* query, SelectAdd, whereAdd, Limit, OrderBy, GroupBy, --> <refentry id="packages.database.db-dataobject.filters"> <refnamediv> <refname>Advanced Filters</refname> <refpurpose> Advanced Filters - ::query(), ::SelectAdd(), ::whereAdd(), ::Limit(), ::OrderBy(), ::GroupBy(), </refpurpose> </refnamediv> <refsect1> <title>More advanced Querying</title> <para> The basic features allow most simple queries to be done very quickly, however building more complex queries can be done using the methods listed below, which append or set conditions that build the query. You will find that there is a fine balance between using these builder methods and just using raw SQL. </para> <para> The DB_DataObject class does not handle JOIN queries directly for the simple reason that it is designed to simplify the use of databases, and make the code that access them clear and easy to read. Having experimented with a Join Version, it became very clear that this did not fulfill either of these goals, and made the resulting code more difficult to read, debug and understand. So in reality it is best to use the raw query() method to do joined queries.. </para> </refsect1> <refsect1> <title>::query()</title> <para> Raw Database Queries </para> <funcsynopsis> <funcprototype> <funcdef><function>::query</function></funcdef> <paramdef>string <parameter>query</parameter></paramdef> </funcprototype> </funcsynopsis> <para> Sends a raw Query to the database <example> <title> raw Queries on the database </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->query("select * from {$this->__table} where id > 12"); while ($person->fetch()) { echo $person->name; } ?> ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::selectAdd()</title> <para> Adding and removing Items from select query </para> <funcsynopsis> <funcprototype> <funcdef><function>::select</function></funcdef> <paramdef>string <parameter><optional>Select Items</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> By default a select query will request all items '*', to change this behavior you can first call selectAdd() without any arguments to clear the current request and then add the specific items you require. <example> <title> Adding select statements</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->selectAdd(); $person->selectAdd('id,name'); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } $person = new DataObjects_Person; $person->selectAdd("birthday_formated as date('%d %M %y')"); $person->id = 12; $person->find(TRUE); echo "$person->name} {$person->birthday_formated}<BR>"; } ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT id,name FROM person SELECT *, birthday_formated as date('%d %M %y') FROM person WHERE id=12 ]]> </programlisting> </example> </para> <para> You can also set up a default selection query by adding SelectAdd() method calls in the object initialization method (the one with the same name as the class) </para> </refsect1> <refsect1> <title>::whereAdd()</title> <para> Adds conditions to the Where part of the query </para> <funcsynopsis> <funcprototype> <funcdef><function>::whereAdd</function></funcdef> <paramdef>string <parameter><optional>Where</optional></parameter></paramdef> <paramdef>string <parameter><optional>OR</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Adds items to the where part of a SQL query. Calling this without any arguments clears, the where condition. The default behavior is to add 'AND' queries, send the string 'OR' to append OR conditions. <example> <title> Adding Where Statements</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->whereAdd('age > 12'); $person->whereAdd('age < 30'); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } $person = new DataObjects_Person; $person->whereAdd('age < 12'); $person->whereAdd('age > 30', 'OR'); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE age > 12 AND age < 30 SELECT * FROM person WHERE age < 12 OR age > 30 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::limit()</title> <para> Sets the limit of a query </para> <funcsynopsis> <funcprototype> <funcdef><function>::limit</function></funcdef> <paramdef>int <parameter><optional>(Limit Range Start or Number)</optional></parameter></paramdef> <paramdef>int <parameter><optional>(Limit Range Number)</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Sets the limit range. Calling this without any arguments clears the range <example> <title> Setting the Limit</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->limit(2); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } $person = new DataObjects_Person; $person->limit(2,4); $person->find(); while ($person->fetch()) { echo "$person->id} {$person->name}<BR>"; } ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, <example><title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person LIMIT 2 SELECT * FROM person LIMIT 2,4 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::orderBy()</title> <para> Appends to the 'order by' part of the query </para> <funcsynopsis> <funcprototype> <funcdef><function>::orderBy</function></funcdef> <paramdef>string <parameter><optional>Order</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Appends an Order condition. Calling this without any arguments clears the order <example> <title> setting the order by</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->orderBy('name'); $person->orderBy('age, eye'); $person->find(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person ORDER BY name, age, eye ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::groupBy()</title> <para> Appends a to the 'group by' part of the query </para> <funcsynopsis> <funcprototype> <funcdef><function>::groupBy</function></funcdef> <paramdef>string <parameter><optional>Group</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Appends a Group condition. Calling this without any arguments clears the Group Condition <example> <title> Setting the Group by </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->groupBy('name'); $person->groupBy('age, eye'); $person->find(); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement, <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person GROUP BY name, age, eye ]]> </programlisting> </example> </para> </refsect1> </refentry> <!-- ****************************************** Links getLink(), getLinks(), getLinkArray() ******************************************* --> <refentry id="packages.database.db-dataobject.links"> <refnamediv> <refname>Automatic Table Linking</refname> <refpurpose> Automatic Table Linking - ::getLink(), ::getLinks() </refpurpose> </refnamediv> <refsect1> <title>Automating the collection of related data</title> <para> When designing a database, often some tables are related to others - a membership table would contain a reference to a person's id and the group id that they are a member of. Using the Link methods, you can automatically fetch objects into the parents variables. </para> <para> The Automated method of doing this is heavily dependent on naming columns relating to databases, for example in the membership described above, you would have to have a column called person_id, group_id for the automated Link methods to understand which table to fetch the data from. The principle is TABLENAME_ANYTHING, and it will always attempt to find primary keys that match the value of field, the getLinks() methods will auto load the related object into $object->_tablename_anything </para> <para> The other way of using getLink() is to use it with the second parameter which is the table name of the related table, it will then query the primary key on that table to see if it matches the value of the requested row. In this method however the getLinks() method will not work, as it will not know the related table names. </para> </refsect1> <refsect1> <title>::getLink()</title> <para> Gets the Related Row Object from the Related Table </para> <funcsynopsis> <funcprototype> <funcdef><function>::getLink</function></funcdef> <paramdef>string <parameter>Row </parameter></paramdef> <paramdef>string <parameter><optional>Table</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Gets the related Object <example> <title> Getting the related object </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $group = $person->getLink('group_owner'); echo $group->name; $group = $person->getLink('colourid','hair'); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statements, and fetch the data <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=12 SELECT * FROM group WHERE id=3 SELECT * FROM hair WHERE id=4 ]]> </programlisting> </example> </para> </refsect1> <refsect1> <title>::getLinks()</title> <para> Loads all the related Objects </para> <funcsynopsis> <funcprototype> <funcdef><function>::getLinks</function></funcdef> <paramdef><parameter></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Loads the all the related objects into the main object, based on the naming of columns using the tablename_xxx naming method, and sets the main objects variables with the row name prefixed with a _ <example> <title> Two Example Tables</title> <programlisting role="sql"><![CDATA[ Person +---------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+----------------+ | id | mediumint(9) | | PRI | 0 | auto_increment | | first_name | varchar(80) | YES | | NULL | | | last_name | varchar(80) | | MUL | | | | middle_name | varchar(80) | YES | | NULL | | | badge_number | smallint(6) | YES | | NULL | | | street | varchar(80) | YES | | NULL | | | city | varchar(80) | YES | | NULL | | | state | varchar(80) | YES | | NULL | | | zip | varchar(15) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | | reg_type | varchar(80) | YES | | NULL | | | judge | varchar(10) | YES | | NULL | | | staff | varchar(10) | YES | | NULL | | | volunteer | varchar(10) | YES | | NULL | | | rpga_number | mediumint(9) | YES | | NULL | | | total_fee | float(10,2) | YES | | NULL | | | email_address | varchar(80) | YES | | NULL | | | country | varchar(30) | YES | | NULL | | | convention_id | int(11) | | | 0 | | | last_modified | timestamp(14) | YES | | NULL | | +---------------+---------------+------+-----+---------+----------------+ Convention +----------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | | name | varchar(50) | | | | | | sponsor_organization | varchar(50) | | | | | | rpga_convention_code | varchar(20) | | | | | | web_site_url | varchar(200) | | | | | | last_modified | timestamp(14) | YES | | NULL | | +----------------------+---------------+------+-----+---------+----------------+ ]]> </programlisting> </example> <example> <title> Loading all the related objects </title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(1079); $person->getLinks(); print_r($person); ?> ]]> </programlisting> </example> </para> <para> This would execute the following SQL statement, and fetch the data <example> <title> Resulting SQL </title> <programlisting role="sql"><![CDATA[ SELECT * FROM person WHERE id=1079 SELECT * FROM convention WHERE id=1 ]]> </programlisting> </example> And output the following data <example> <title> Resulting Output </title> <programlisting role="sql"><![CDATA[ Object:dataobjects_person Object ( [_DB_DataObject_version] => 1.0 [__table] => person [_database_dsn] => [_database_dsn_md5] => 3974043abbccdd6412fb156a1d10b98377 [_database] => testing [_condition] => [_group_by] => [_order_by] => [_limit] => [_data_select] => * [_link_loaded] => 1 [_lastError] => pear_error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -3 [message] => getLink:Could not find class for row last_modified, table last [userinfo] => [callback] => ) [id] => 1079 [N] => 1 [_DB_resultid] => 2 [first_name] => Tim [last_name] => White [middle_name] => [badge_number] => 123 [street] => 334411 N Washington [city] => Texas [state] => CO [zip] => 12345 [phone] => 343412323232 [reg_type] => Staff [judge] => [staff] => CHECKED [volunteer] => [rpga_number] => 1232323 [total_fee] => 0.00 [email_address] => tim@example.com [country] => USA [convention_id] => 1 [last_modified] => 20020711084539 [_first_name] => [_last_name] => [_middle_name] => [_badge_number] => [_reg_type] => [_rpga_number] => [_total_fee] => [_email_address] => [_convention_id] => dataobjects_convention Object ( [_DB_DataObject_version] => 1.0 [__table] => convention [_database_dsn] => [_database_dsn_md5] => 3974043abbcc86412fb156a1d10b98377 [_database] => testing [_condition] => [_group_by] => [_order_by] => [_limit] => [_data_select] => * [_link_loaded] => [_lastError] => [id] => 1 [N] => 1 [_DB_resultid] => 3 [name] => ABCD XYZ [sponsor_organization] => some sponser [rpga_convention_code] => ABCD_XYZ [web_site_url] => http://example.com [last_modified] => 20020703143828 ) [_last_modified] => ) ]]> </programlisting> </example> </para> <para> In the example above, alot of the columns have '_' underscores in and do not relate to tables, DB_DataObject will only flag it as an error in $object->_lastError. </para> </refsect1> </refentry> <!-- ****************************************** AutoForm Utilities and Debugging ******************************************* --> <refentry id="packages.database.db-dataobject.utils"> <refnamediv> <refname>AutoForm Utilities and Debugging</refname> <refpurpose> ::setFrom(), ::debugLevel() </refpurpose> </refnamediv> <refsect1> <title>::setFrom()</title> <para> Copies data from an Array or object into the current object </para> <funcsynopsis> <funcprototype> <funcdef><function>::setFrom</function></funcdef> <paramdef>(Array or Object) <parameter>from</parameter></paramdef> </funcprototype> </funcsynopsis> <para> copies all the data that relates to this table (Except keys) into the current object <example> <title> Copying data from post or object</title> <programlisting role="php"><![CDATA[ <? $person = new DataObjects_Person; $person->get(12); $person->setFrom($_POST['input']); $person->update(); // or copying from another object $personA = new DataObjects_Person; $personA->get(12); $personB = new DataObjects_Person; $personB->get(12); $personA->setFrom($personB); $person->update(); ?> ]]> </programlisting> </example> </para> <para> Copies all the data (with names the of this objects columns) from another object or array into this one. </para> </refsect1> <refsect1> <title>DB_DataObject::debugLevel()</title> <para> Sets the debugging level - so you can see the queries being built and executed </para> <funcsynopsis> <funcprototype> <funcdef><function>DB_DataObject::debugLevel</function></funcdef> <paramdef>int <parameter><optional>debug level</optional></parameter></paramdef> </funcprototype> </funcsynopsis> <para> Sets the debugging level (without any parameters it will disable the debugging output) See the configuration page for details on the various levels. <example> <title> Setting the debugging level </title> <programlisting role="php"><![CDATA[ <? // turn debugging high DB_DataObject::debugLevel(5); $person = new DataObjects_Person; $person->get(12); $person->setFrom($_POST['input']); $person->update(); // turn debugging off DB_DataObject::debugLevel(); ?> ]]> </programlisting> </example> </para> </refsect1> </refentry> </reference> <!-- 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 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 -->