sqlquery.class.inc.php 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442
  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 SQLQuery
  33. {
  34. private $m_SourceOQL = '';
  35. private $m_sTable = '';
  36. private $m_sTableAlias = '';
  37. private $m_aFields = array();
  38. private $m_oConditionExpr = null;
  39. private $m_bToDelete = true; // The current table must be listed for deletion ?
  40. private $m_aValues = array(); // Values to set in case of an update query
  41. private $m_oSelectedIdField = null;
  42. private $m_aJoinSelects = array();
  43. public function __construct($sTable, $sTableAlias, $aFields, $bToDelete = true, $aValues = array(), $oSelectedIdField = null)
  44. {
  45. // This check is not needed but for developping purposes
  46. //if (!CMDBSource::IsTable($sTable))
  47. //{
  48. // throw new CoreException("Unknown table '$sTable'");
  49. //}
  50. // $aFields must be an array of "alias"=>"expr"
  51. // $oConditionExpr must be a condition tree
  52. // $aValues is an array of "alias"=>value
  53. $this->m_sTable = $sTable;
  54. $this->m_sTableAlias = $sTableAlias;
  55. $this->m_aFields = $aFields;
  56. $this->m_oConditionExpr = null;
  57. $this->m_bToDelete = $bToDelete;
  58. $this->m_aValues = $aValues;
  59. $this->m_oSelectedIdField = $oSelectedIdField;
  60. }
  61. public function SetSourceOQL($sOQL)
  62. {
  63. $this->m_SourceOQL = $sOQL;
  64. }
  65. public function GetSourceOQL()
  66. {
  67. return $this->m_SourceOQL;
  68. }
  69. public function DisplayHtml()
  70. {
  71. if (count($this->m_aFields) == 0) $sFields = "";
  72. else
  73. {
  74. $aFieldDesc = array();
  75. foreach ($this->m_aFields as $sAlias => $oExpression)
  76. {
  77. $aFieldDesc[] = $oExpression->Render()." as <em>$sAlias</em>";
  78. }
  79. $sFields = " =&gt; ".implode(', ', $aFieldDesc);
  80. }
  81. echo "<b>$this->m_sTable</b>$sFields<br/>\n";
  82. // #@# todo - display html of an expression tree
  83. //$this->m_oConditionExpr->DisplayHtml()
  84. if (count($this->m_aJoinSelects) > 0)
  85. {
  86. echo "Joined to...<br/>\n";
  87. echo "<ul class=\"treeview\">\n";
  88. foreach ($this->m_aJoinSelects as $aJoinInfo)
  89. {
  90. $sJoinType = $aJoinInfo["jointype"];
  91. $oSQLQuery = $aJoinInfo["select"];
  92. $sLeftField = $aJoinInfo["leftfield"];
  93. $sRightField = $aJoinInfo["rightfield"];
  94. $sRightTableAlias = $aJoinInfo["righttablealias"];
  95. echo "<li>Join '$sJoinType', $sLeftField, $sRightTableAlias.$sRightField".$oSQLQuery->DisplayHtml()."</li>\n";
  96. }
  97. echo "</ul>";
  98. }
  99. $aFrom = array();
  100. $aFields = array();
  101. $oCondition = null;
  102. $aDelTables = array();
  103. $aSetValues = array();
  104. $aSelectedIdFields = array();
  105. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues, $aSelectedIdFields);
  106. echo "From ...<br/>\n";
  107. echo "<pre style=\"font-size: smaller;\">\n";
  108. print_r($aFrom);
  109. echo "</pre>";
  110. }
  111. public function SetSelect($aExpressions)
  112. {
  113. $this->m_aFields = $aExpressions;
  114. }
  115. public function SetCondition($oConditionExpr)
  116. {
  117. $this->m_oConditionExpr = $oConditionExpr;
  118. }
  119. public function AddCondition($oConditionExpr)
  120. {
  121. if (is_null($this->m_oConditionExpr))
  122. {
  123. $this->m_oConditionExpr = $oConditionExpr;
  124. }
  125. else
  126. {
  127. $this->m_oConditionExpr->LogAnd($oConditionExpr);
  128. }
  129. }
  130. private function AddJoin($sJoinType, $oSQLQuery, $sLeftField, $sRightField, $sRightTableAlias = '')
  131. {
  132. assert((get_class($oSQLQuery) == __CLASS__) || is_subclass_of($oSQLQuery, __CLASS__));
  133. // No need to check this here but for development purposes
  134. //if (!CMDBSource::IsField($this->m_sTable, $sLeftField))
  135. //{
  136. // throw new CoreException("Unknown field '$sLeftField' in table '".$this->m_sTable);
  137. //}
  138. if (empty($sRightTableAlias))
  139. {
  140. $sRightTableAlias = $oSQLQuery->m_sTableAlias;
  141. }
  142. // #@# Could not be verified here because the namespace is unknown - do we need to check it there?
  143. //
  144. // if (!CMDBSource::IsField($sRightTable, $sRightField))
  145. // {
  146. // throw new CoreException("Unknown field '$sRightField' in table '".$sRightTable."'");
  147. // }
  148. $this->m_aJoinSelects[] = array(
  149. "jointype" => $sJoinType,
  150. "select" => $oSQLQuery,
  151. "leftfield" => $sLeftField,
  152. "rightfield" => $sRightField,
  153. "righttablealias" => $sRightTableAlias
  154. );
  155. }
  156. public function AddInnerJoin($oSQLQuery, $sLeftField, $sRightField, $sRigthtTable = '')
  157. {
  158. $this->AddJoin("inner", $oSQLQuery, $sLeftField, $sRightField, $sRigthtTable);
  159. }
  160. public function AddLeftJoin($oSQLQuery, $sLeftField, $sRightField)
  161. {
  162. return $this->AddJoin("left", $oSQLQuery, $sLeftField, $sRightField);
  163. }
  164. // Interface, build the SQL query
  165. public function RenderDelete($aArgs = array())
  166. {
  167. // The goal will be to complete the list as we build the Joins
  168. $aFrom = array();
  169. $aFields = array();
  170. $oCondition = null;
  171. $aDelTables = array();
  172. $aSetValues = array();
  173. $aSelectedIdFields = array();
  174. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues, $aSelectedIdFields);
  175. // Target: DELETE myAlias1, myAlias2 FROM t1 as myAlias1, t2 as myAlias2, t3 as topreserve WHERE ...
  176. $sDelete = self::ClauseDelete($aDelTables);
  177. $sFrom = self::ClauseFrom($aFrom);
  178. // #@# safety net to redo ?
  179. /*
  180. if ($this->m_oConditionExpr->IsAny())
  181. -- if (count($aConditions) == 0) --
  182. {
  183. throw new CoreException("Building a request wich will delete every object of a given table -looks suspicious- please use truncate instead...");
  184. }
  185. */
  186. if (is_null($oCondition))
  187. {
  188. // Delete all !!!
  189. }
  190. else
  191. {
  192. $sWhere = self::ClauseWhere($oCondition, $aArgs);
  193. return "DELETE $sDelete FROM $sFrom WHERE $sWhere";
  194. }
  195. }
  196. // Interface, build the SQL query
  197. public function RenderUpdate($aArgs = array())
  198. {
  199. // The goal will be to complete the list as we build the Joins
  200. $aFrom = array();
  201. $aFields = array();
  202. $oCondition = null;
  203. $aDelTables = array();
  204. $aSetValues = array();
  205. $aSelectedIdFields = array();
  206. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues, $aSelectedIdFields);
  207. $sFrom = self::ClauseFrom($aFrom);
  208. $sValues = self::ClauseValues($aSetValues);
  209. $sWhere = self::ClauseWhere($oCondition, $aArgs);
  210. return "UPDATE $sFrom SET $sValues WHERE $sWhere";
  211. }
  212. // Interface, build the SQL query
  213. public function RenderSelect($aOrderBy = array(), $aArgs = array(), $iLimitCount = 0, $iLimitStart = 0, $bGetCount = false)
  214. {
  215. // The goal will be to complete the lists as we build the Joins
  216. $aFrom = array();
  217. $aFields = array();
  218. $oCondition = null;
  219. $aDelTables = array();
  220. $aSetValues = array();
  221. $aSelectedIdFields = array();
  222. $this->privRender($aFrom, $aFields, $oCondition, $aDelTables, $aSetValues, $aSelectedIdFields);
  223. $sFrom = self::ClauseFrom($aFrom);
  224. $sWhere = self::ClauseWhere($oCondition, $aArgs);
  225. if ($bGetCount)
  226. {
  227. if (count($aSelectedIdFields) > 0)
  228. {
  229. $sIDFields = implode(', ', $aSelectedIdFields);
  230. $sSQL = "SELECT COUNT(DISTINCT $sIDFields) AS COUNT FROM $sFrom WHERE $sWhere";
  231. }
  232. else
  233. {
  234. $sSQL = "SELECT COUNT(*) AS COUNT FROM $sFrom WHERE $sWhere";
  235. }
  236. }
  237. else
  238. {
  239. $sSelect = self::ClauseSelect($aFields);
  240. $sOrderBy = self::ClauseOrderBy($aOrderBy);
  241. if (!empty($sOrderBy))
  242. {
  243. $sOrderBy = "ORDER BY $sOrderBy";
  244. }
  245. if ($iLimitCount > 0)
  246. {
  247. $sLimit = 'LIMIT '.$iLimitStart.', '.$iLimitCount;
  248. }
  249. else
  250. {
  251. $sLimit = '';
  252. }
  253. $sSQL = "SELECT DISTINCT $sSelect FROM $sFrom WHERE $sWhere $sOrderBy $sLimit";
  254. }
  255. return $sSQL;
  256. }
  257. private static function ClauseSelect($aFields)
  258. {
  259. $aSelect = array();
  260. foreach ($aFields as $sFieldAlias => $sSQLExpr)
  261. {
  262. $aSelect[] = "$sSQLExpr AS $sFieldAlias";
  263. }
  264. $sSelect = implode(', ', $aSelect);
  265. return $sSelect;
  266. }
  267. private static function ClauseDelete($aDelTableAliases)
  268. {
  269. $aDelTables = array();
  270. foreach ($aDelTableAliases as $sTableAlias)
  271. {
  272. $aDelTables[] = "$sTableAlias";
  273. }
  274. $sDelTables = implode(', ', $aDelTables);
  275. return $sDelTables;
  276. }
  277. private static function ClauseFrom($aFrom)
  278. {
  279. $sFrom = "";
  280. foreach ($aFrom as $sTableAlias => $aJoinInfo)
  281. {
  282. switch ($aJoinInfo["jointype"])
  283. {
  284. case "first":
  285. $sFrom .= "`".$aJoinInfo["tablename"]."` AS `$sTableAlias`";
  286. $sFrom .= " ".self::ClauseFrom($aJoinInfo["subfrom"]);
  287. break;
  288. case "inner":
  289. $sFrom .= " INNER JOIN (`".$aJoinInfo["tablename"]."` AS `$sTableAlias`";
  290. $sFrom .= " ".self::ClauseFrom($aJoinInfo["subfrom"]);
  291. $sFrom .= ") ON ".$aJoinInfo["joincondition"];
  292. break;
  293. case "left":
  294. $sFrom .= " LEFT JOIN (`".$aJoinInfo["tablename"]."` AS `$sTableAlias`";
  295. $sFrom .= " ".self::ClauseFrom($aJoinInfo["subfrom"]);
  296. $sFrom .= ") ON ".$aJoinInfo["joincondition"];
  297. break;
  298. default:
  299. throw new CoreException("Unknown jointype: '".$aJoinInfo["jointype"]."'");
  300. }
  301. }
  302. return $sFrom;
  303. }
  304. private static function ClauseValues($aValues)
  305. {
  306. $aSetValues = array();
  307. foreach ($aValues as $sFieldSpec => $value)
  308. {
  309. $aSetValues[] = "$sFieldSpec = ".CMDBSource::Quote($value);
  310. }
  311. $sSetValues = implode(', ', $aSetValues);
  312. return $sSetValues;
  313. }
  314. private static function ClauseWhere($oConditionExpr, $aArgs = array())
  315. {
  316. if (is_null($oConditionExpr))
  317. {
  318. return '1';
  319. }
  320. else
  321. {
  322. return $oConditionExpr->Render($aArgs);
  323. }
  324. }
  325. private static function ClauseOrderBy($aOrderBy)
  326. {
  327. $aOrderBySpec = array();
  328. foreach($aOrderBy as $sFieldAlias => $bAscending)
  329. {
  330. $aOrderBySpec[] = '`'.$sFieldAlias.'`'.($bAscending ? " ASC" : " DESC");
  331. }
  332. $sOrderBy = implode(", ", $aOrderBySpec);
  333. return $sOrderBy;
  334. }
  335. // Purpose: prepare the query data, once for all
  336. private function privRender(&$aFrom, &$aFields, &$oCondition, &$aDelTables, &$aSetValues, &$aSelectedIdFields)
  337. {
  338. $sTableAlias = $this->privRenderSingleTable($aFrom, $aFields, $aDelTables, $aSetValues, $aSelectedIdFields);
  339. $oCondition = $this->m_oConditionExpr;
  340. return $sTableAlias;
  341. }
  342. private function privRenderSingleTable(&$aFrom, &$aFields, &$aDelTables, &$aSetValues, &$aSelectedIdFields, $sJoinType = 'first', $sCallerAlias = '', $sLeftField = '', $sRightField = '', $sRightTableAlias = '')
  343. {
  344. $aActualTableFields = CMDBSource::GetTableFieldsList($this->m_sTable);
  345. $aTranslationTable[$this->m_sTable]['*'] = $this->m_sTableAlias;
  346. // Handle the various kinds of join (or first table in the list)
  347. //
  348. if (empty($sRightTableAlias))
  349. {
  350. $sRightTableAlias = $this->m_sTableAlias;
  351. }
  352. $sJoinCond = "`$sCallerAlias`.`$sLeftField` = `$sRightTableAlias`.`$sRightField`";
  353. switch ($sJoinType)
  354. {
  355. case "first":
  356. $aFrom[$this->m_sTableAlias] = array("jointype"=>"first", "tablename"=>$this->m_sTable, "joincondition"=>"");
  357. break;
  358. case "inner":
  359. case "left":
  360. // table or tablealias ???
  361. $aFrom[$this->m_sTableAlias] = array("jointype"=>$sJoinType, "tablename"=>$this->m_sTable, "joincondition"=>"$sJoinCond");
  362. break;
  363. }
  364. // Given the alias, modify the fields and conditions
  365. // before adding them into the current lists
  366. //
  367. foreach($this->m_aFields as $sAlias => $oExpression)
  368. {
  369. $aFields["`$sAlias`"] = $oExpression->Render();
  370. }
  371. if ($this->m_bToDelete)
  372. {
  373. $aDelTables[] = "`{$this->m_sTableAlias}`";
  374. }
  375. foreach($this->m_aValues as $sFieldName=>$value)
  376. {
  377. $aSetValues["`{$this->m_sTableAlias}`.`$sFieldName`"] = $value; // quoted further!
  378. }
  379. if (!is_null($this->m_oSelectedIdField))
  380. {
  381. $aSelectedIdFields[] = $this->m_oSelectedIdField->Render();
  382. }
  383. // loop on joins, to complete the list of tables/fields/conditions
  384. //
  385. $aTempFrom = array(); // temporary subset of 'from' specs, to be grouped in the final query
  386. foreach ($this->m_aJoinSelects as $aJoinData)
  387. {
  388. $sJoinType = $aJoinData["jointype"];
  389. $oRightSelect = $aJoinData["select"];
  390. $sLeftField = $aJoinData["leftfield"];
  391. $sRightField = $aJoinData["rightfield"];
  392. $sRightTableAlias = $aJoinData["righttablealias"];
  393. $sJoinTableAlias = $oRightSelect->privRenderSingleTable($aTempFrom, $aFields, $aDelTables, $aSetValues, $aSelectedIdFields, $sJoinType, $this->m_sTableAlias, $sLeftField, $sRightField, $sRightTableAlias);
  394. }
  395. $aFrom[$this->m_sTableAlias]['subfrom'] = $aTempFrom;
  396. return $this->m_sTableAlias;
  397. }
  398. }
  399. ?>