sqlquery.class.inc.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455
  1. <?php
  2. // Copyright (C) 2010 Combodo SARL
  3. //
  4. // This program is free software; you can redistribute it and/or modify
  5. // it under the terms of the GNU General Public License as published by
  6. // the Free Software Foundation; version 3 of the License.
  7. //
  8. // This program is distributed in the hope that it will be useful,
  9. // but WITHOUT ANY WARRANTY; without even the implied warranty of
  10. // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  11. // GNU General Public License for more details.
  12. //
  13. // You should have received a copy of the GNU General Public License
  14. // along with this program; if not, write to the Free Software
  15. // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
  16. /**
  17. * SQLQuery
  18. * build an mySQL compatible SQL query
  19. *
  20. * @author Erwan Taloc <erwan.taloc@combodo.com>
  21. * @author Romain Quetiez <romain.quetiez@combodo.com>
  22. * @author Denis Flaven <denis.flaven@combodo.com>
  23. * @license http://www.opensource.org/licenses/gpl-3.0.html LGPL
  24. */
  25. /**
  26. * SQLQuery
  27. * build an mySQL compatible SQL query
  28. *
  29. * @package iTopORM
  30. */
  31. require_once('cmdbsource.class.inc.php');
  32. class SQLExpression extends BinaryExpression
  33. {
  34. }
  35. class ScalarSQLExpression extends ScalarExpression
  36. {
  37. }
  38. class TrueSQLExpression extends TrueExpression
  39. {
  40. }
  41. class FieldSQLExpression extends FieldExpression
  42. {
  43. }
  44. class VariableSQLExpression extends VariableExpression
  45. {
  46. }
  47. class SQLQuery
  48. {
  49. private $m_sTable = '';
  50. private $m_sTableAlias = '';
  51. private $m_aFields = array();
  52. private $m_oConditionExpr = null;
  53. private $m_aFullTextNeedles = array();
  54. private $m_bToDelete = true; // The current table must be listed for deletion ?
  55. private $m_aValues = array(); // Values to set in case of an update query
  56. private $m_aJoinSelects = array();
  57. public function __construct($sTable, $sTableAlias, $aFields, $oConditionExpr, $aFullTextNeedles, $bToDelete = true, $aValues = array())
  58. {
  59. // This check is not needed but for developping purposes
  60. //if (!CMDBSource::IsTable($sTable))
  61. //{
  62. // throw new CoreException("Unknown table '$sTable'");
  63. //}
  64. // $aFields must be an array of "alias"=>"expr"
  65. // $oConditionExpr must be a condition tree
  66. // $aValues is an array of "alias"=>value
  67. $this->m_sTable = $sTable;
  68. $this->m_sTableAlias = $sTableAlias;
  69. $this->m_aFields = $aFields;
  70. $this->m_oConditionExpr = $oConditionExpr;
  71. if (is_null($oConditionExpr))
  72. {
  73. $this->m_oConditionExpr = new TrueExpression;
  74. }
  75. else if (!$oConditionExpr instanceof Expression)
  76. {
  77. throw new CoreException('Invalid type for condition, expecting an Expression', array('class' => get_class($oConditionExpr)));
  78. }
  79. $this->m_aFullTextNeedles = $aFullTextNeedles;
  80. $this->m_bToDelete = $bToDelete;
  81. $this->m_aValues = $aValues;
  82. }
  83. public function DisplayHtml()
  84. {
  85. if (count($this->m_aFields) == 0) $sFields = "";
  86. else
  87. {
  88. $aFieldDesc = array();
  89. foreach ($this->m_aFields as $sAlias => $oExpression)
  90. {
  91. $aFieldDesc[] = $oExpression->Render()." as <em>$sAlias</em>";
  92. }
  93. $sFields = " =&gt; ".implode(', ', $aFieldDesc);
  94. }
  95. echo "<b>$this->m_sTable</b>$sFields<br/>\n";
  96. // #@# todo - display html of an expression tree
  97. //$this->m_oConditionExpr->DisplayHtml()
  98. if (count($this->m_aFullTextNeedles) > 0)
  99. {
  100. echo "Full text criteria...<br/>\n";
  101. echo "<ul class=\"treeview\">\n";
  102. foreach ($this->m_aFullTextNeedles as $sFTNeedle)
  103. {
  104. echo "<li>$sFTNeedle</li>\n";
  105. }
  106. echo "</ul>";
  107. }
  108. if (count($this->m_aJoinSelects) > 0)
  109. {
  110. echo "Joined to...<br/>\n";
  111. echo "<ul class=\"treeview\">\n";
  112. foreach ($this->m_aJoinSelects as $aJoinInfo)
  113. {
  114. $sJoinType = $aJoinInfo["jointype"];
  115. $oSQLQuery = $aJoinInfo["select"];
  116. $sLeftField = $aJoinInfo["leftfield"];
  117. $sRightField = $aJoinInfo["rightfield"];
  118. $sRightTableAlias = $aJoinInfo["righttablealias"];
  119. echo "<li>Join '$sJoinType', $sLeftField, $sRightTableAlias.$sRightField".$oSQLQuery->DisplayHtml()."</li>\n";
  120. }
  121. echo "</ul>";
  122. }
  123. $aFrom = array();
  124. $aFields = array();
  125. $oCondition = null;
  126. $aDelTables = array();
  127. $aSetValues = array();
  128. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues);
  129. echo "From ...<br/>\n";
  130. echo "<pre style=\"font-size: smaller;\">\n";
  131. print_r($aFrom);
  132. echo "</pre>";
  133. }
  134. public function SetCondition($oConditionExpr)
  135. {
  136. $this->m_oConditionExpr = $oConditionExpr;
  137. }
  138. public function AddCondition($oConditionExpr)
  139. {
  140. $this->m_oConditionExpr->LogAnd($oConditionExpr);
  141. }
  142. private function AddJoin($sJoinType, $oSQLQuery, $sLeftField, $sRightField, $sRightTableAlias = '')
  143. {
  144. assert((get_class($oSQLQuery) == __CLASS__) || is_subclass_of($oSQLQuery, __CLASS__));
  145. // No need to check this here but for development purposes
  146. //if (!CMDBSource::IsField($this->m_sTable, $sLeftField))
  147. //{
  148. // throw new CoreException("Unknown field '$sLeftField' in table '".$this->m_sTable);
  149. //}
  150. if (empty($sRightTableAlias))
  151. {
  152. $sRightTableAlias = $oSQLQuery->m_sTableAlias;
  153. }
  154. // #@# Could not be verified here because the namespace is unknown - do we need to check it there?
  155. //
  156. // if (!CMDBSource::IsField($sRightTable, $sRightField))
  157. // {
  158. // throw new CoreException("Unknown field '$sRightField' in table '".$sRightTable."'");
  159. // }
  160. $this->m_aJoinSelects[] = array(
  161. "jointype" => $sJoinType,
  162. "select" => $oSQLQuery,
  163. "leftfield" => $sLeftField,
  164. "rightfield" => $sRightField,
  165. "righttablealias" => $sRightTableAlias
  166. );
  167. }
  168. public function AddInnerJoin($oSQLQuery, $sLeftField, $sRightField, $sRigthtTable = '')
  169. {
  170. $this->AddJoin("inner", $oSQLQuery, $sLeftField, $sRightField, $sRigthtTable);
  171. }
  172. public function AddLeftJoin($oSQLQuery, $sLeftField, $sRightField)
  173. {
  174. return $this->AddJoin("left", $oSQLQuery, $sLeftField, $sRightField);
  175. }
  176. // Interface, build the SQL query
  177. public function RenderDelete($aArgs = array())
  178. {
  179. // The goal will be to complete the list as we build the Joins
  180. $aFrom = array();
  181. $aFields = array();
  182. $oCondition = null;
  183. $aDelTables = array();
  184. $aSetValues = array();
  185. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues);
  186. // Target: DELETE myAlias1, myAlias2 FROM t1 as myAlias1, t2 as myAlias2, t3 as topreserve WHERE ...
  187. $sDelete = self::ClauseDelete($aDelTables);
  188. $sFrom = self::ClauseFrom($aFrom);
  189. // #@# safety net to redo ?
  190. /*
  191. if ($this->m_oConditionExpr->IsAny())
  192. -- if (count($aConditions) == 0) --
  193. {
  194. throw new CoreException("Building a request wich will delete every object of a given table -looks suspicious- please use truncate instead...");
  195. }
  196. */
  197. $sWhere = self::ClauseWhere($oCondition, $aArgs);
  198. return "DELETE $sDelete FROM $sFrom WHERE $sWhere";
  199. }
  200. // Interface, build the SQL query
  201. public function RenderUpdate($aArgs = array())
  202. {
  203. // The goal will be to complete the list as we build the Joins
  204. $aFrom = array();
  205. $aFields = array();
  206. $oCondition = null;
  207. $aDelTables = array();
  208. $aSetValues = array();
  209. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues);
  210. $sFrom = self::ClauseFrom($aFrom);
  211. $sValues = self::ClauseValues($aSetValues);
  212. $sWhere = self::ClauseWhere($oCondition, $aArgs);
  213. return "UPDATE $sFrom SET $sValues WHERE $sWhere";
  214. }
  215. // Interface, build the SQL query
  216. public function RenderSelect($aOrderBy = array(), $aArgs = array(), $iLimitCount = 0, $iLimitStart = 0, $bGetCount = false)
  217. {
  218. // The goal will be to complete the lists as we build the Joins
  219. $aFrom = array();
  220. $aFields = array();
  221. $oCondition = null;
  222. $aDelTables = array();
  223. $aSetValues = array();
  224. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues);
  225. $sFrom = self::ClauseFrom($aFrom);
  226. $sWhere = self::ClauseWhere($oCondition, $aArgs);
  227. if ($bGetCount)
  228. {
  229. $sSQL = "SELECT COUNT(*) AS COUNT FROM $sFrom WHERE $sWhere";
  230. }
  231. else
  232. {
  233. $sSelect = self::ClauseSelect($aFields);
  234. $sOrderBy = self::ClauseOrderBy($aOrderBy);
  235. if (!empty($sOrderBy))
  236. {
  237. $sOrderBy = "ORDER BY $sOrderBy";
  238. }
  239. if ($iLimitCount > 0)
  240. {
  241. $sLimit = 'LIMIT '.$iLimitStart.', '.$iLimitCount;
  242. }
  243. else
  244. {
  245. $sLimit = '';
  246. }
  247. $sSQL = "SELECT DISTINCT $sSelect FROM $sFrom WHERE $sWhere $sOrderBy $sLimit";
  248. }
  249. return $sSQL;
  250. }
  251. private static function ClauseSelect($aFields)
  252. {
  253. $aSelect = array();
  254. foreach ($aFields as $sFieldAlias => $sSQLExpr)
  255. {
  256. $aSelect[] = "$sSQLExpr AS $sFieldAlias";
  257. }
  258. $sSelect = implode(', ', $aSelect);
  259. return $sSelect;
  260. }
  261. private static function ClauseDelete($aDelTableAliases)
  262. {
  263. $aDelTables = array();
  264. foreach ($aDelTableAliases as $sTableAlias)
  265. {
  266. $aDelTables[] = "$sTableAlias";
  267. }
  268. $sDelTables = implode(', ', $aDelTables);
  269. return $sDelTables;
  270. }
  271. private static function ClauseFrom($aFrom)
  272. {
  273. $sFrom = "";
  274. foreach ($aFrom as $sTableAlias => $aJoinInfo)
  275. {
  276. switch ($aJoinInfo["jointype"])
  277. {
  278. case "first":
  279. $sFrom .= "`".$aJoinInfo["tablename"]."` AS `$sTableAlias`";
  280. $sFrom .= " ".self::ClauseFrom($aJoinInfo["subfrom"]);
  281. break;
  282. case "inner":
  283. $sFrom .= " INNER JOIN (`".$aJoinInfo["tablename"]."` AS `$sTableAlias`";
  284. $sFrom .= " ".self::ClauseFrom($aJoinInfo["subfrom"]);
  285. $sFrom .= ") ON ".$aJoinInfo["joincondition"];
  286. break;
  287. case "left":
  288. $sFrom .= " LEFT JOIN (`".$aJoinInfo["tablename"]."` AS `$sTableAlias`";
  289. $sFrom .= " ".self::ClauseFrom($aJoinInfo["subfrom"]);
  290. $sFrom .= ") ON ".$aJoinInfo["joincondition"];
  291. break;
  292. default:
  293. throw new CoreException("Unknown jointype: '".$aJoinInfo["jointype"]."'");
  294. }
  295. }
  296. return $sFrom;
  297. }
  298. private static function ClauseValues($aValues)
  299. {
  300. $aSetValues = array();
  301. foreach ($aValues as $sFieldSpec => $value)
  302. {
  303. $aSetValues[] = "$sFieldSpec = ".CMDBSource::Quote($value);
  304. }
  305. $sSetValues = implode(', ', $aSetValues);
  306. return $sSetValues;
  307. }
  308. private static function ClauseWhere($oConditionExpr, $aArgs = array())
  309. {
  310. return $oConditionExpr->Render($aArgs);
  311. }
  312. private static function ClauseOrderBy($aOrderBy)
  313. {
  314. $aOrderBySpec = array();
  315. foreach($aOrderBy as $sFieldAlias => $bAscending)
  316. {
  317. $aOrderBySpec[] = '`'.$sFieldAlias.'`'.($bAscending ? " ASC" : " DESC");
  318. }
  319. $sOrderBy = implode(", ", $aOrderBySpec);
  320. return $sOrderBy;
  321. }
  322. // Purpose: prepare the query data, once for all
  323. private function privRender(&$aFrom, &$aFields, &$oCondition, &$aDelTables, &$aSetValues)
  324. {
  325. $sTableAlias = $this->privRenderSingleTable($aFrom, $aFields, $aDelTables, $aSetValues);
  326. // Add the full text search condition, based on each and every requested field
  327. //
  328. // To be updated with a real full text search based on the mySQL settings
  329. // (then it might move somewhere else !)
  330. //
  331. $oCondition = $this->m_oConditionExpr;
  332. if ((count($aFields) > 0) && (count($this->m_aFullTextNeedles) > 0))
  333. {
  334. $aFieldExp = array();
  335. foreach ($aFields as $sField)
  336. {
  337. // This is TEMPORARY (that's why it is weird, actually)
  338. // Full text match will be done as an expression in the filter condition
  339. // $sField is already a string `table`.`column`
  340. // Let's make an expression out of it (again !)
  341. $aFieldExp[] = Expression::FromOQL($sField);
  342. }
  343. $oFullTextExpr = new CharConcatExpression($aFieldExp);
  344. // The cast is necessary because the CONCAT result in a binary string:
  345. // if any of the field is a binary string => case sensitive comparison
  346. //
  347. foreach($this->m_aFullTextNeedles as $sFTNeedle)
  348. {
  349. $oNewCond = new BinaryExpression($oFullTextExpr, 'LIKE', new ScalarExpression("%$sFTNeedle%"));
  350. $oCondition = $oCondition->LogAnd($oNewCond);
  351. }
  352. }
  353. return $sTableAlias;
  354. }
  355. private function privRenderSingleTable(&$aFrom, &$aFields, &$aDelTables, &$aSetValues, $sJoinType = 'first', $sCallerAlias = '', $sLeftField = '', $sRightField = '', $sRightTableAlias = '')
  356. {
  357. $aActualTableFields = CMDBSource::GetTableFieldsList($this->m_sTable);
  358. $aTranslationTable[$this->m_sTable]['*'] = $this->m_sTableAlias;
  359. // Handle the various kinds of join (or first table in the list)
  360. //
  361. if (empty($sRightTableAlias))
  362. {
  363. $sRightTableAlias = $this->m_sTableAlias;
  364. }
  365. $sJoinCond = "`$sCallerAlias`.`$sLeftField` = `$sRightTableAlias`.`$sRightField`";
  366. switch ($sJoinType)
  367. {
  368. case "first":
  369. $aFrom[$this->m_sTableAlias] = array("jointype"=>"first", "tablename"=>$this->m_sTable, "joincondition"=>"");
  370. break;
  371. case "inner":
  372. case "left":
  373. // table or tablealias ???
  374. $aFrom[$this->m_sTableAlias] = array("jointype"=>$sJoinType, "tablename"=>$this->m_sTable, "joincondition"=>"$sJoinCond");
  375. break;
  376. }
  377. // Given the alias, modify the fields and conditions
  378. // before adding them into the current lists
  379. //
  380. foreach($this->m_aFields as $sAlias => $oExpression)
  381. {
  382. $sTable = $oExpression->GetParent();
  383. $sColumn = $oExpression->GetName();
  384. $aFields["`$sAlias`"] = $oExpression->Render();
  385. }
  386. if ($this->m_bToDelete)
  387. {
  388. $aDelTables[] = "`{$this->m_sTableAlias}`";
  389. }
  390. foreach($this->m_aValues as $sFieldName=>$value)
  391. {
  392. $aSetValues["`{$this->m_sTableAlias}`.`$sFieldName`"] = $value; // quoted further!
  393. }
  394. // loop on joins, to complete the list of tables/fields/conditions
  395. //
  396. $aTempFrom = array(); // temporary subset of 'from' specs, to be grouped in the final query
  397. foreach ($this->m_aJoinSelects as $aJoinData)
  398. {
  399. $sJoinType = $aJoinData["jointype"];
  400. $oRightSelect = $aJoinData["select"];
  401. $sLeftField = $aJoinData["leftfield"];
  402. $sRightField = $aJoinData["rightfield"];
  403. $sRightTableAlias = $aJoinData["righttablealias"];
  404. $sJoinTableAlias = $oRightSelect->privRenderSingleTable($aTempFrom, $aFields, $aDelTables, $aSetValues, $sJoinType, $this->m_sTableAlias, $sLeftField, $sRightField, $sRightTableAlias);
  405. }
  406. $aFrom[$this->m_sTableAlias]['subfrom'] = $aTempFrom;
  407. return $this->m_sTableAlias;
  408. }
  409. }
  410. ?>