1<?php 2/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */ 3 4/** 5 * Contains the DB_QueryTool_EasyJoin class 6 * 7 * PHP versions 4 and 5 8 * 9 * LICENSE: Redistribution and use in source and binary forms, with or without 10 * modification, are permitted provided that the following conditions are met: 11 * 1. Redistributions of source code must retain the above copyright 12 * notice, this list of conditions and the following disclaimer. 13 * 2. Redistributions in binary form must reproduce the above copyright 14 * notice, this list of conditions and the following disclaimer in the 15 * documentation and/or other materials provided with the distribution. 16 * 3. The name of the author may not be used to endorse or promote products 17 * derived from this software without specific prior written permission. 18 * 19 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED 20 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 21 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 22 * IN NO EVENT SHALL THE FREEBSD PROJECT OR CONTRIBUTORS BE LIABLE FOR ANY 23 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 24 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 25 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 26 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 27 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF 28 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 29 * 30 * @category Database 31 * @package DB_QueryTool 32 * @author Wolfram Kriesing <wk@visionp.de> 33 * @author Paolo Panto <wk@visionp.de> 34 * @copyright 2003-2007 Wolfram Kriesing, Paolo Panto 35 * @license http://www.debian.org/misc/bsd.license BSD License (3 Clause) 36 * @version CVS: $Id: EasyJoin.php,v 1.11 2007/11/16 20:33:49 quipo Exp $ 37 * @link http://pear.php.net/package/DB_QueryTool 38 */ 39 40/** 41 * require the DB_QueryTool_Query class 42 */ 43require_once 'DB/QueryTool/Query.php'; 44 45/** 46 * DB_QueryTool_EasyJoin class 47 * 48 * @category Database 49 * @package DB_QueryTool 50 * @author Wolfram Kriesing <wk@visionp.de> 51 * @copyright 2003-2007 Wolfram Kriesing 52 * @license http://www.debian.org/misc/bsd.license BSD License (3 Clause) 53 * @link http://pear.php.net/package/DB_QueryTool 54 */ 55class DB_QueryTool_EasyJoin extends DB_QueryTool_Query 56{ 57 // {{{ class vars 58 59 /** 60 * This is the regular expression that shall be used to find a table's shortName 61 * in a column name, the string found by using this regular expression will be removed 62 * from the column name and it will be checked if it is a table name 63 * i.e. the default '/_id$/' would find the table name 'user' from the column name 'user_id' 64 * 65 * @var string regexp 66 */ 67 var $_tableNamePreg = '/_id$/'; 68 69 /** 70 * This is to find the column name that is referred by it, so the default find 71 * from 'user_id' the column 'id' which will be used to refer to the 'user' table 72 * 73 * @var string regexp 74 */ 75 var $_columnNamePreg = '/^.*_/'; 76 77 // }}} 78 // {{{ __construct() 79 80 /** 81 * call parent constructor 82 * 83 * @param mixed $dsn DSN string, DSN array or DB object 84 * @param array $options database options 85 */ 86 function __construct($dsn = false, $options = array()) 87 { 88 parent::DB_QueryTool_Query($dsn, $options); 89 } 90 91 // }}} 92 // {{{ autoJoin() 93 94 /** 95 * Join the given tables, using the column names, to find out how to join the tables; 96 * i.e., if table1 has a column named "table2_id", this method will join 97 * "WHERE table1.table2_id=table2.id". 98 * All joins made here are only concatenated via AND. 99 * 100 * @param array $tables tables to join 101 * 102 * @return void 103 */ 104 function autoJoin($tables) 105 { 106 // FIXXME if $tables is empty autoJoin all available tables that have a relation 107 // to $this->table, starting to search in $this->table 108 settype($tables, 'array'); 109 // add this->table to the tables array, so we go thru the current table first 110 $tables = array_merge(array($this->table), $tables); 111 112 $shortNameIndexed = $this->getTableSpec(true, $tables); 113 $nameIndexed = $this->getTableSpec(false, $tables); 114 115 //print_r($shortNameIndexed); 116 //print_r($tables); print '<br><br>'; 117 if (sizeof($shortNameIndexed) != sizeof($tables)) { 118 $this->_errorLog("autoJoin-ERROR: not all the tables are in the tableSpec!<br />"); 119 } 120 121 $joinTables = array(); 122 $joinConditions = array(); 123 foreach ($tables as $aTable) { // go through $this->table and all the given tables 124 if ($metadata = $this->metadata($aTable)) 125 foreach ($metadata as $aCol => $x) { // go through each row to check which might be related to $aTable 126 $possibleTableShortName = preg_replace($this->_tableNamePreg, '', $aCol); 127 $possibleColumnName = preg_replace($this->_columnNamePreg, '', $aCol); 128 //print "$aTable.$aCol .... possibleTableShortName=$possibleTableShortName .... possibleColumnName=$possibleColumnName<br />"; 129 if (isset($shortNameIndexed[$possibleTableShortName])) { 130 // are the tables given in the tableSpec? 131 if (!$shortNameIndexed[$possibleTableShortName]['name'] || 132 !$nameIndexed[$aTable]['name']) { 133 // its an error of the developer, so log the error, dont show it to the end user 134 $this->_errorLog("autoJoin-ERROR: '$aTable' is not given in the tableSpec!<br />"); 135 } else { 136 // do only join different table.col combination, 137 // we should not join stuff like 'question.question=question.question' 138 // this would be quite stupid, but it used to be :-( 139 if ($shortNameIndexed[$possibleTableShortName]['name'] != $aTable || 140 $possibleColumnName != $aCol 141 ) { 142 $where = $shortNameIndexed[$possibleTableShortName]['name'].".$possibleColumnName=$aTable.$aCol"; 143 $this->addJoin($nameIndexed[$aTable]['name'], $where); 144 $this->addJoin($shortNameIndexed[$possibleTableShortName]['name'], $where); 145 } 146 } 147 } 148 } 149 } 150 } 151 152 // }}} 153} 154?>