PageRenderTime 71ms CodeModel.GetById 25ms RepoModel.GetById 0ms app.codeStats 1ms

/classphp/flourish/fORMDatabase.php

https://github.com/jsuarez/Lexer
PHP | 1170 lines | 730 code | 175 blank | 265 comment | 145 complexity | 02f91e81dfdcc5fcd588784bcaf73921 MD5 | raw file
  1. <?php
  2. /**
  3. * Holds a single instance of the fDatabase class and provides database manipulation functionality for ORM code
  4. *
  5. * @copyright Copyright (c) 2007-2009 Will Bond, others
  6. * @author Will Bond [wb] <will@flourishlib.com>
  7. * @author Craig Ruksznis, iMarc LLC [cr-imarc] <craigruk@imarc.net>
  8. * @license http://flourishlib.com/license
  9. *
  10. * @package Flourish
  11. * @link http://flourishlib.com/fORMDatabase
  12. *
  13. * @version 1.0.0b15
  14. * @changes 1.0.0b15 Streamlined intersection operator SQL and added support for the second value being NULL [wb, 2009-09-21]
  15. * @changes 1.0.0b14 Added support for the intersection operator `><` to ::createWhereClause() [wb, 2009-07-13]
  16. * @changes 1.0.0b13 Added support for the `AND LIKE` operator `&~` to ::createWhereClause() [wb, 2009-07-09]
  17. * @changes 1.0.0b12 Added support for the `NOT LIKE` operator `!~` to ::createWhereClause() [wb, 2009-07-08]
  18. * @changes 1.0.0b11 Added support for concatenated columns to ::escapeBySchema() [cr-imarc, 2009-06-19]
  19. * @changes 1.0.0b10 Updated ::createWhereClause() to properly handle NULLs for arrays of values when doing = and != comparisons [wb, 2009-06-17]
  20. * @changes 1.0.0b9 Changed replacement values in preg_replace() calls to be properly escaped [wb, 2009-06-11]
  21. * @changes 1.0.0b8 Fixed a bug with ::creatingWhereClause() where a null value would not be escaped property [wb, 2009-05-12]
  22. * @changes 1.0.0b7 Fixed a bug where an OR condition in ::createWhereClause() could not have one of the values be an array [wb, 2009-04-22]
  23. * @changes 1.0.0b6 ::insertFromAndGroupByClauses() will no longer wrap ungrouped columns if in a CAST or CASE statement for ORDER BY clauses of queries with a GROUP BY clause [wb, 2009-03-23]
  24. * @changes 1.0.0b5 Fixed ::parseSearchTerms() to include stop words when they are the only thing in the search string [wb, 2008-12-31]
  25. * @changes 1.0.0b4 Fixed a bug where loading a related record in the same table through a one-to-many relationship caused recursion [wb, 2008-12-24]
  26. * @changes 1.0.0b3 Fixed a bug from 1.0.0b2 [wb, 2008-12-05]
  27. * @changes 1.0.0b2 Added support for != and <> to ::createWhereClause() and ::createHavingClause() [wb, 2008-12-04]
  28. * @changes 1.0.0b The initial implementation [wb, 2007-08-04]
  29. */
  30. class fORMDatabase
  31. {
  32. // The following constants allow for nice looking callbacks to static methods
  33. const addTableToKeys = 'fORMDatabase::addTableToKeys';
  34. const addTableToValues = 'fORMDatabase::addTableToValues';
  35. const attach = 'fORMDatabase::attach';
  36. const createFromClauseFromJoins = 'fORMDatabase::createFromClauseFromJoins';
  37. const createHavingClause = 'fORMDatabase::createHavingClause';
  38. const createOrderByClause = 'fORMDatabase::createOrderByClause';
  39. const createPrimaryKeyWhereClause = 'fORMDatabase::createPrimaryKeyWhereClause';
  40. const createWhereClause = 'fORMDatabase::createWhereClause';
  41. const escapeBySchema = 'fORMDatabase::escapeBySchema';
  42. const escapeByType = 'fORMDatabase::escapeByType';
  43. const insertFromAndGroupByClauses = 'fORMDatabase::insertFromAndGroupByClauses';
  44. const reset = 'fORMDatabase::reset';
  45. const retrieve = 'fORMDatabase::retrieve';
  46. const splitHavingConditions = 'fORMDatabase::splitHavingConditions';
  47. /**
  48. * The instance of fDatabase
  49. *
  50. * @var fDatabase
  51. */
  52. static private $database_object = NULL;
  53. /**
  54. * Prepends the table to the keys of the array
  55. *
  56. * @internal
  57. *
  58. * @param string $table The table to prepend
  59. * @param array $array The array to modify
  60. * @return array The modified array
  61. */
  62. static public function addTableToKeys($table, $array)
  63. {
  64. $modified_array = array();
  65. foreach ($array as $key => $value) {
  66. if (preg_match('#^\w+$#D', $key)) {
  67. $modified_array[$table . '.' . $key] = $value;
  68. } else {
  69. $modified_array[$key] = $value;
  70. }
  71. }
  72. return $modified_array;
  73. }
  74. /**
  75. * Prepends the table to the values of the array
  76. *
  77. * @internal
  78. *
  79. * @param string $table The table to prepend
  80. * @param array $array The array to modify
  81. * @return array The modified array
  82. */
  83. static public function addTableToValues($table, $array)
  84. {
  85. $modified_array = array();
  86. foreach ($array as $key => $value) {
  87. if (preg_match('#^\w+$#D', $value)) {
  88. $modified_array[$key] = $table . '.' . $value;
  89. } else {
  90. $modified_array[$key] = $value;
  91. }
  92. }
  93. return $modified_array;
  94. }
  95. /**
  96. * Allows attaching an fDatabase-compatible object as the database singleton for ORM code
  97. *
  98. * @param fDatabase $database An object that is compatible with fDatabase
  99. * @return void
  100. */
  101. static public function attach($database)
  102. {
  103. self::$database_object = $database;
  104. }
  105. /**
  106. * Translated the where condition for a single column into a SQL clause
  107. *
  108. * @param string $table The table to create the condition for
  109. * @param string $column The column to store the value in, may also be shorthand column name like `table.column` or `table=>related_table.column`
  110. * @param mixed $values The value(s) to escape
  111. * @param string $operator Should be `'='`, `'!='`, `'!'`, `'<>'`, `'<'`, `'<='`, `'>'`, `'>='`, `'IN'`, `'NOT IN'`
  112. * @return string The SQL clause for the column, values and operator specified
  113. */
  114. static private function createColumnCondition($table, $column, $values, $operator)
  115. {
  116. settype($values, 'array');
  117. // More than one value
  118. if (sizeof($values) > 1) {
  119. switch ($operator) {
  120. case '=':
  121. $condition = array();
  122. $has_null = FALSE;
  123. foreach ($values as $value) {
  124. if ($value === NULL) {
  125. $has_null = TRUE;
  126. continue;
  127. }
  128. $condition[] = self::escapeBySchema($table, $column, $value);
  129. }
  130. $sql = $column . ' IN (' . join(', ', $condition) . ')';
  131. if ($has_null) {
  132. $sql = '(' . $column . ' IS NULL OR ' . $sql . ')';
  133. }
  134. break;
  135. case '!':
  136. $condition = array();
  137. $has_null = FALSE;
  138. foreach ($values as $value) {
  139. if ($value === NULL) {
  140. $has_null = TRUE;
  141. continue;
  142. }
  143. $condition[] = self::escapeBySchema($table, $column, $value);
  144. }
  145. $sql = $column . ' NOT IN (' . join(', ', $condition) . ')';
  146. if ($has_null) {
  147. $sql = '(' . $column . ' IS NOT NULL AND ' . $sql . ')';
  148. }
  149. break;
  150. case '~':
  151. $condition = array();
  152. foreach ($values as $value) {
  153. $condition[] = $column . self::retrieve()->escape(' LIKE %s', '%' . $value . '%');
  154. }
  155. $sql = '(' . join(' OR ', $condition) . ')';
  156. break;
  157. case '&~':
  158. $condition = array();
  159. foreach ($values as $value) {
  160. $condition[] = $column . self::retrieve()->escape(' LIKE %s', '%' . $value . '%');
  161. }
  162. $sql = '(' . join(' AND ', $condition) . ')';
  163. break;
  164. case '!~':
  165. $condition = array();
  166. foreach ($values as $value) {
  167. $condition[] = $column . self::retrieve()->escape(' NOT LIKE %s', '%' . $value . '%');
  168. }
  169. $sql = '(' . join(' AND ', $condition) . ')';
  170. break;
  171. default:
  172. throw new fProgrammerException(
  173. 'An invalid array comparison operator, %s, was specified for an array of values',
  174. $operator
  175. );
  176. break;
  177. }
  178. // A single value
  179. } else {
  180. if ($values === array()) {
  181. $value = NULL;
  182. } else {
  183. $value = current($values);
  184. }
  185. switch ($operator) {
  186. case '=':
  187. case '<':
  188. case '<=':
  189. case '>':
  190. case '>=':
  191. $sql = $column . self::escapeBySchema($table, $column, $value, $operator);
  192. break;
  193. case '!':
  194. if ($value !== NULL) {
  195. $sql = '(' . $column . self::escapeBySchema($table, $column, $value, '<>') . ' OR ' . $column . ' IS NULL)';
  196. } else {
  197. $sql = $column . self::escapeBySchema($table, $column, $value, '<>');
  198. }
  199. break;
  200. case '~':
  201. $sql = $column . self::retrieve()->escape(' LIKE %s', '%' . $value . '%');
  202. break;
  203. case '!~':
  204. $sql = $column . self::retrieve()->escape(' NOT LIKE %s', '%' . $value . '%');
  205. break;
  206. default:
  207. throw new fProgrammerException(
  208. 'An invalid comparison operator, %s, was specified for a single value',
  209. $operator
  210. );
  211. break;
  212. }
  213. }
  214. return $sql;
  215. }
  216. /**
  217. * Creates a `FROM` clause from a join array
  218. *
  219. * @internal
  220. *
  221. * @param array $joins The joins to create the `FROM` clause out of
  222. * @return string The from clause (does not include the word `'FROM'`)
  223. */
  224. static public function createFromClauseFromJoins($joins)
  225. {
  226. $sql = '';
  227. foreach ($joins as $join) {
  228. // Here we handle the first table in a join
  229. if ($join['join_type'] == 'none') {
  230. $sql .= $join['table_name'];
  231. if ($join['table_alias'] != $join['table_name']) {
  232. $sql .= ' AS ' . $join['table_alias'];
  233. }
  234. // Here we handle all other joins
  235. } else {
  236. $sql .= ' ' . strtoupper($join['join_type']) . ' ' . $join['table_name'];
  237. if ($join['table_alias'] != $join['table_name']) {
  238. $sql .= ' AS ' . $join['table_alias'];
  239. }
  240. if (isset($join['on_clause_type'])) {
  241. if ($join['on_clause_type'] == 'simple_equation') {
  242. $sql .= ' ON ' . $join['on_clause_fields'][0] . ' = ' . $join['on_clause_fields'][1];
  243. } else {
  244. $sql .= ' ON ' . $join['on_clause'];
  245. }
  246. }
  247. }
  248. }
  249. return $sql;
  250. }
  251. /**
  252. * Creates a `HAVING` clause from an array of conditions
  253. *
  254. * @internal
  255. *
  256. * @param array $conditions The array of conditions - see fRecordSet::build() for format
  257. * @return string The SQL `HAVING` clause
  258. */
  259. static public function createHavingClause($conditions)
  260. {
  261. $sql = array();
  262. foreach ($conditions as $expression => $value) {
  263. if (in_array(substr($expression, -2), array('<=', '>=', '!=', '<>'))) {
  264. $operator = strtr(
  265. substr($expression, -2),
  266. array(
  267. '<>' => '!',
  268. '!=' => '!'
  269. )
  270. );
  271. $expression = substr($expression, 0, -2);
  272. } else {
  273. $operator = substr($expression, -1);
  274. $expression = substr($expression, 0, -1);
  275. }
  276. if (is_object($value)) {
  277. if (is_callable(array($value, '__toString'))) {
  278. $value = $value->__toString();
  279. } else {
  280. $value = (string) $value;
  281. }
  282. }
  283. if (is_array($value)) {
  284. switch ($operator) {
  285. case '=':
  286. $condition = array();
  287. foreach ($values as $value) {
  288. $condition[] = self::escapeByType($value);
  289. }
  290. $sql[] = $expression . ' IN (' . join(', ', $condition) . ')';
  291. break;
  292. case '!':
  293. $condition = array();
  294. foreach ($values as $value) {
  295. $condition[] = self::escapeByType($value);
  296. }
  297. $sql[] = $expression . ' NOT IN (' . join(', ', $condition) . ')';
  298. break;
  299. default:
  300. throw new fProgrammerException(
  301. 'An invalid array comparison operator, %s, was specified',
  302. $operator
  303. );
  304. break;
  305. }
  306. } else {
  307. if (!in_array($operator, array('=', '!', '~', '<', '<=', '>', '>='))) {
  308. throw new fProgrammerException(
  309. 'An invalid comparison operator, %s, was specified',
  310. $operator
  311. );
  312. }
  313. $sql[] = $expression . self::escapeByType($value, $operator);
  314. }
  315. }
  316. return join(' AND ', $sql);
  317. }
  318. /**
  319. * Creates join information for the table shortcut provided
  320. *
  321. * @internal
  322. *
  323. * @param string $table The primary table
  324. * @param string $table_alias The primary table alias
  325. * @param string $related_table The related table
  326. * @param string $route The route to the related table
  327. * @param array &$joins The names of the joins that have been created
  328. * @param array &$used_aliases The aliases that have been used
  329. * @return string The name of the significant join created
  330. */
  331. static private function createJoin($table, $table_alias, $related_table, $route, &$joins, &$used_aliases)
  332. {
  333. $routes = fORMSchema::getRoutes($table, $related_table);
  334. if (!isset($routes[$route])) {
  335. throw new fProgrammerException(
  336. 'An invalid route, %1$s, was specified for the relationship from %2$s to %3$s',
  337. $route,
  338. $table,
  339. $related_table
  340. );
  341. }
  342. if (isset($joins[$table . '_' . $related_table . '{' . $route . '}'])) {
  343. return $table . '_' . $related_table . '{' . $route . '}';
  344. }
  345. // If the route uses a join table
  346. if (isset($routes[$route]['join_table'])) {
  347. $join = array(
  348. 'join_type' => 'LEFT JOIN',
  349. 'table_name' => $routes[$route]['join_table'],
  350. 'table_alias' => self::createNewAlias($routes[$route]['join_table'], $used_aliases),
  351. 'on_clause_type' => 'simple_equation',
  352. 'on_clause_fields' => array()
  353. );
  354. $join2 = array(
  355. 'join_type' => 'LEFT JOIN',
  356. 'table_name' => $related_table,
  357. 'table_alias' => self::createNewAlias($related_table, $used_aliases),
  358. 'on_clause_type' => 'simple_equation',
  359. 'on_clause_fields' => array()
  360. );
  361. if ($table != $related_table) {
  362. $join['on_clause_fields'][] = $table_alias . '.' . $routes[$route]['column'];
  363. $join['on_clause_fields'][] = $join['table_alias'] . '.' . $routes[$route]['join_column'];
  364. $join2['on_clause_fields'][] = $join['table_alias'] . '.' . $routes[$route]['join_related_column'];
  365. $join2['on_clause_fields'][] = $join2['table_alias'] . '.' . $routes[$route]['related_column'];
  366. } else {
  367. $join['on_clause_fields'][] = $table_alias . '.' . $routes[$route]['column'];
  368. $join['on_clause_fields'][] = $join['table_alias'] . '.' . $routes[$route]['join_related_column'];
  369. $join2['on_clause_fields'][] = $join['table_alias'] . '.' . $routes[$route]['join_column'];
  370. $join2['on_clause_fields'][] = $join2['table_alias'] . '.' . $routes[$route]['related_column'];
  371. }
  372. $joins[$table . '_' . $related_table . '{' . $route . '}_join'] = $join;
  373. $joins[$table . '_' . $related_table . '{' . $route . '}'] = $join2;
  374. // If the route is a direct join
  375. } else {
  376. $join = array(
  377. 'join_type' => 'LEFT JOIN',
  378. 'table_name' => $related_table,
  379. 'table_alias' => self::createNewAlias($related_table, $used_aliases),
  380. 'on_clause_type' => 'simple_equation',
  381. 'on_clause_fields' => array()
  382. );
  383. $join['on_clause_fields'][] = $table_alias . '.' . $routes[$route]['column'];
  384. $join['on_clause_fields'][] = $join['table_alias'] . '.' . $routes[$route]['related_column'];
  385. $joins[$table . '_' . $related_table . '{' . $route . '}'] = $join;
  386. }
  387. return $table . '_' . $related_table . '{' . $route . '}';
  388. }
  389. /**
  390. * Creates a new table alias
  391. *
  392. * @internal
  393. *
  394. * @param string $table The table to create an alias for
  395. * @param array &$used_aliases The aliases that have been used
  396. * @return string The alias to use for the table
  397. */
  398. static private function createNewAlias($table, &$used_aliases)
  399. {
  400. if (!in_array($table, $used_aliases)) {
  401. $used_aliases[] = $table;
  402. return $table;
  403. }
  404. $i = 1;
  405. while(in_array($table . $i, $used_aliases)) {
  406. $i++;
  407. }
  408. $used_aliases[] = $table . $i;
  409. return $table . $i;
  410. }
  411. /**
  412. * Creates an `ORDER BY` clause from an array of columns/expressions and directions
  413. *
  414. * @internal
  415. *
  416. * @param string $table The table any ambigious column references will refer to
  417. * @param array $order_bys The array of order bys to use - see fRecordSet::build() for format
  418. * @return string The SQL `ORDER BY` clause
  419. */
  420. static public function createOrderByClause($table, $order_bys)
  421. {
  422. $order_bys = self::addTableToKeys($table, $order_bys);
  423. $sql = array();
  424. foreach ($order_bys as $column => $direction) {
  425. if ((!is_string($column) && !is_object($column) && !is_numeric($column)) || !strlen(trim($column))) {
  426. throw new fProgrammerException(
  427. 'An invalid sort column, %s, was specified',
  428. $column
  429. );
  430. }
  431. $direction = strtoupper($direction);
  432. if (!in_array($direction, array('ASC', 'DESC'))) {
  433. throw new fProgrammerException(
  434. 'An invalid direction, %s, was specified',
  435. $direction
  436. );
  437. }
  438. if (preg_match('#^(?:\w+(?:\{\w+\})?=>)?(\w+)(?:\{\w+\})?\.(\w+)$#D', $column, $matches)) {
  439. $column_type = fORMSchema::retrieve()->getColumnInfo($matches[1], $matches[2], 'type');
  440. if (in_array($column_type, array('varchar', 'char', 'text'))) {
  441. $sql[] = 'LOWER(' . $column . ') ' . $direction;
  442. } else {
  443. $sql[] = $column . ' ' . $direction;
  444. }
  445. } else {
  446. $sql[] = $column . ' ' . $direction;
  447. }
  448. }
  449. return join(', ', $sql);
  450. }
  451. /**
  452. * Creates a `WHERE` clause condition for primary keys of the table specified
  453. *
  454. * This method requires the `$primary_keys` parameter to be one of:
  455. *
  456. * - A scalar value for a single-column primary key
  457. * - An array of values for a single-column primary key
  458. * - An associative array of values for a multi-column primary key (`column => value`)
  459. * - An array of associative arrays of values for a multi-column primary key (`key => array(column => value)`)
  460. *
  461. * If you are looking to build a primary key where clause from the `$values`
  462. * and `$old_values` arrays, please see ::createPrimaryKeyWhereClause()
  463. *
  464. * @internal
  465. *
  466. * @param string $table The table to build the where clause for
  467. * @param string $table_alias The alias for the table
  468. * @param array &$values The values array for the fActiveRecord object
  469. * @param array &$old_values The old values array for the fActiveRecord object
  470. * @return string The `WHERE` clause that will specify the fActiveRecord as it currently exists in the database
  471. */
  472. static public function createPrimaryKeyWhereClause($table, $table_alias, &$values, &$old_values)
  473. {
  474. $primary_keys = fORMSchema::retrieve()->getKeys($table, 'primary');
  475. $sql = '';
  476. foreach ($primary_keys as $primary_key) {
  477. if ($sql) { $sql .= " AND "; }
  478. $value = (isset($old_values[$primary_key])) ? $old_values[$primary_key][0] : $values[$primary_key];
  479. $sql .= $table . '.' . $primary_key . fORMDatabase::escapeBySchema($table, $primary_key, $value, '=');
  480. }
  481. return $sql;
  482. }
  483. /**
  484. * Creates a `WHERE` clause from an array of conditions
  485. *
  486. * @internal
  487. *
  488. * @param string $table The table any ambigious column references will refer to
  489. * @param array $conditions The array of conditions - see fRecordSet::build() for format
  490. * @return string The SQL `WHERE` clause
  491. */
  492. static public function createWhereClause($table, $conditions)
  493. {
  494. $sql = array();
  495. foreach ($conditions as $column => $values) {
  496. if (in_array(substr($column, -2), array('<=', '>=', '!=', '<>', '!~', '&~', '><'))) {
  497. $operator = strtr(
  498. substr($column, -2),
  499. array(
  500. '<>' => '!',
  501. '!=' => '!'
  502. )
  503. );
  504. $column = substr($column, 0, -2);
  505. } else {
  506. $operator = substr($column, -1);
  507. $column = substr($column, 0, -1);
  508. }
  509. if (!is_object($values)) {
  510. settype($values, 'array');
  511. } else {
  512. $values = array($values);
  513. }
  514. if (!$values) { $values = array(NULL); }
  515. $new_values = array();
  516. foreach ($values as $value) {
  517. if (is_object($value) && is_callable(array($value, '__toString'))) {
  518. $value = $value->__toString();
  519. } elseif (is_object($value)) {
  520. $value = (string) $value;
  521. }
  522. $new_values[] = $value;
  523. }
  524. $values = $new_values;
  525. // Multi-column condition
  526. if (preg_match('#(?<!\|)\|(?!\|)#', $column)) {
  527. $columns = explode('|', $column);
  528. $operators = array();
  529. foreach ($columns as &$_column) {
  530. if (in_array(substr($_column, -2), array('<=', '>=', '!=', '<>', '!~', '&~'))) {
  531. $operators[] = strtr(
  532. substr($_column, -2),
  533. array(
  534. '<>' => '!',
  535. '!=' => '!'
  536. )
  537. );
  538. $_column = substr($_column, 0, -2);
  539. } elseif (!ctype_alnum(substr($_column, -1))) {
  540. $operators[] = substr($_column, -1);
  541. $_column = substr($_column, 0, -1);
  542. }
  543. }
  544. $operators[] = $operator;
  545. $columns = self::addTableToValues($table, $columns);
  546. if (sizeof($operators) == 1) {
  547. // Handle fuzzy searches
  548. if ($operator == '~') {
  549. // If the value to search is a single string value, parse it for search terms
  550. if (sizeof($values) == 1 && is_string($values[0])) {
  551. $values = self::parseSearchTerms($values[0], TRUE);
  552. }
  553. $condition = array();
  554. foreach ($values as $value) {
  555. $sub_condition = array();
  556. foreach ($columns as $column) {
  557. $sub_condition[] = $column . self::retrieve()->escape(' LIKE %s', '%' . $value . '%');
  558. }
  559. $condition[] = '(' . join(' OR ', $sub_condition) . ')';
  560. }
  561. $sql[] = ' (' . join(' AND ', $condition) . ') ';
  562. // Handle intersection
  563. } elseif ($operator == '><') {
  564. if (sizeof($columns) != 2 || sizeof($values) != 2) {
  565. throw new fProgrammerException(
  566. 'The intersection operator, %s, requires exactly two columns and two values',
  567. $operator
  568. );
  569. }
  570. if ($values[1] === NULL) {
  571. $part_1 = '(' . $columns[1] . ' IS NULL AND ' . $columns[0] . ' = ' . self::escapeBySchema($table, $columns[0], $values[0]) . ')';
  572. $part_2 = '(' . $columns[1] . ' IS NOT NULL AND ' . $columns[0] . ' <= ' . self::escapeBySchema($table, $columns[0], $values[0]) . ' AND ' . $columns[1] . ' >= ' . self::escapeBySchema($table, $columns[1], $values[0]) . ')';
  573. } else {
  574. $part_1 = '(' . $columns[0] . ' <= ' . self::escapeBySchema($table, $columns[0], $values[0]) . ' AND ' . $columns[1] . ' >= ' . self::escapeBySchema($table, $columns[1], $values[0]) . ')';
  575. $part_2 = '(' . $columns[0] . ' >= ' . self::escapeBySchema($table, $columns[0], $values[0]) . ' AND ' . $columns[0] . ' <= ' . self::escapeBySchema($table, $columns[0], $values[1]) . ')';
  576. }
  577. $sql[] = ' (' . $part_1 . ' OR ' . $part_2 . ') ';
  578. } else {
  579. throw new fProgrammerException(
  580. 'An invalid comparison operator, %s, was specified for multiple columns',
  581. $operator
  582. );
  583. }
  584. // Handle OR combos
  585. } else {
  586. if (sizeof($columns) != sizeof($values)) {
  587. throw new fProgrammerException(
  588. 'When creating an %1$s where clause there must be an equal number of columns and values, however there are not',
  589. 'OR',
  590. sizeof($columns),
  591. sizeof($values)
  592. );
  593. }
  594. if (sizeof($columns) != sizeof($operators)) {
  595. throw new fProgrammerException(
  596. 'When creating an %s where clause there must be a comparison operator for each column, however one or more is missing',
  597. 'OR'
  598. );
  599. }
  600. $conditions = array();
  601. $iterations = sizeof($columns);
  602. for ($i=0; $i<$iterations; $i++) {
  603. $conditions[] = self::createColumnCondition($table, $columns[$i], $values[$i], $operators[$i]);
  604. }
  605. $sql[] = ' (' . join(' OR ', $conditions) . ') ';
  606. }
  607. // Single column condition
  608. } else {
  609. $columns = self::addTableToValues($table, array($column));
  610. $column = $columns[0];
  611. $sql[] = self::createColumnCondition($table, $column, $values, $operator);
  612. }
  613. }
  614. return join(' AND ', $sql);
  615. }
  616. /**
  617. * Escapes a value for a DB call based on database schema
  618. *
  619. * @internal
  620. *
  621. * @param string $table The table to store the value
  622. * @param string $column The column to store the value in, may also be shorthand column name like `table.column` or `table=>related_table.column` or concatenated column names like `table.column||table.other_column`
  623. * @param mixed $value The value to escape
  624. * @param string $comparison_operator Optional: should be `'='`, `'!='`, `'!'`, `'<>'`, `'<'`, `'<='`, `'>'`, `'>='`, `'IN'`, `'NOT IN'`
  625. * @return string The SQL-ready representation of the value
  626. */
  627. static public function escapeBySchema($table, $column, $value, $comparison_operator=NULL)
  628. {
  629. // handle concatenated column names
  630. if (preg_match('#\|\|#', $column)) {
  631. if (is_object($value) && is_callable(array($value, '__toString'))) {
  632. $value = $value->__toString();
  633. } elseif (is_object($value)) {
  634. $value = (string) $value;
  635. }
  636. $column_info = array(
  637. 'not_null' => FALSE,
  638. 'default' => NULL,
  639. 'type' => 'varchar'
  640. );
  641. } else {
  642. // Handle shorthand column names like table.column and table=>related_table.column
  643. if (preg_match('#(\w+)(?:\{\w+\})?\.(\w+)$#D', $column, $match)) {
  644. $table = $match[1];
  645. $column = $match[2];
  646. }
  647. $column_info = fORMSchema::retrieve()->getColumnInfo($table, $column);
  648. // Some of the tables being escaped for are linking tables that might break with classize()
  649. if (is_object($value)) {
  650. $class = fORM::classize($table);
  651. $value = fORM::scalarize($class, $column, $value);
  652. }
  653. }
  654. if ($comparison_operator !== NULL) {
  655. $comparison_operator = strtr($comparison_operator, array('!' => '<>', '!=' => '<>'));
  656. }
  657. $valid_comparison_operators = array('=', '!=', '!', '<>', '<=', '<', '>=', '>', 'IN', 'NOT IN');
  658. if ($comparison_operator !== NULL && !in_array(strtoupper($comparison_operator), $valid_comparison_operators)) {
  659. throw new fProgrammerException(
  660. 'The comparison operator specified, %1$s, is invalid. Must be one of: %2$s.',
  661. $comparison_operator,
  662. join(', ', $valid_comparison_operators)
  663. );
  664. }
  665. $co = (is_null($comparison_operator)) ? '' : ' ' . strtoupper($comparison_operator) . ' ';
  666. if ($column_info['not_null'] && $value === NULL && $column_info['default'] !== NULL) {
  667. $value = $column_info['default'];
  668. }
  669. if (is_null($value)) {
  670. $prepared_value = 'NULL';
  671. } else {
  672. $prepared_value = self::retrieve()->escape($column_info['type'], $value);
  673. }
  674. if ($prepared_value == 'NULL') {
  675. if ($co) {
  676. if (in_array(trim($co), array('=', 'IN'))) {
  677. $co = ' IS ';
  678. } elseif (in_array(trim($co), array('<>', 'NOT IN'))) {
  679. $co = ' IS NOT ';
  680. }
  681. }
  682. }
  683. return $co . $prepared_value;
  684. }
  685. /**
  686. * Escapes a value for a DB call based on variable type
  687. *
  688. * @internal
  689. *
  690. * @param mixed $value The value to escape
  691. * @param string $comparison_operator Optional: should be `'='`, `'!='`, `'!'`, `'<>'`, `'<'`, `'<='`, `'>'`, `'>='`, `'IN'`, `'NOT IN'`
  692. * @return string The SQL-ready representation of the value
  693. */
  694. static public function escapeByType($value, $comparison_operator=NULL)
  695. {
  696. if ($comparison_operator !== NULL) {
  697. $comparison_operator = strtr($comparison_operator, array('!' => '<>', '!=' => '<>'));
  698. }
  699. $valid_comparison_operators = array('=', '<>', '<=', '<', '>=', '>', 'IN', 'NOT IN');
  700. if ($comparison_operator !== NULL && !in_array(strtoupper($comparison_operator), $valid_comparison_operators)) {
  701. throw new fProgrammerException(
  702. 'The comparison operator specified, %1$s, is invalid. Must be one of: %2$s.',
  703. $comparison_operator,
  704. join(', ', $valid_comparison_operators)
  705. );
  706. }
  707. $co = (is_null($comparison_operator)) ? '' : ' ' . strtoupper($comparison_operator) . ' ';
  708. if (is_int($value)) {
  709. $prepared_value = self::retrieve()->escape('integer', $value);
  710. } elseif (is_float($value)) {
  711. $prepared_value = self::retrieve()->escape('float', $value);
  712. } elseif (is_bool($value)) {
  713. $prepared_value = self::retrieve()->escape('boolean', $value);
  714. } elseif (is_null($value)) {
  715. if ($co) {
  716. if (in_array(trim($co), array('=', 'IN'))) {
  717. $co = ' IS ';
  718. } elseif (in_array(trim($co), array('<>', 'NOT IN'))) {
  719. $co = ' IS NOT ';
  720. }
  721. }
  722. $prepared_value = 'NULL';
  723. } else {
  724. $prepared_value = self::retrieve()->escape('string', $value);
  725. }
  726. return $co . $prepared_value;
  727. }
  728. /**
  729. * Finds all of the table names in the SQL and creates the appropriate `FROM` and `GROUP BY` clauses with all necessary joins
  730. *
  731. * The SQL string should contain two placeholders, `:from_clause` and
  732. * `:group_by_clause`. All columns should be qualified with their full table
  733. * name. Here is an example SQL string to pass in presumming that the
  734. * tables users and groups are in a relationship:
  735. *
  736. * {{{
  737. * SELECT users.* FROM :from_clause WHERE groups.group_id = 5 :group_by_clause ORDER BY lower(users.first_name) ASC
  738. * }}}
  739. *
  740. * @internal
  741. *
  742. * @param string $table The main table to be queried
  743. * @param string $sql The SQL to insert the `FROM` clause into
  744. * @return string The SQL `FROM` clause
  745. */
  746. static public function insertFromAndGroupByClauses($table, $sql)
  747. {
  748. $joins = array();
  749. if (strpos($sql, ':from_clause') === FALSE) {
  750. throw new fProgrammerException(
  751. "No %1\$s placeholder was found in:%2\$s",
  752. ':from_clause',
  753. "\n" . $sql
  754. );
  755. }
  756. if (strpos($sql, ':group_by_clause') === FALSE && !preg_match('#group\s+by#i', $sql)) {
  757. throw new fProgrammerException(
  758. "No %1\$s placeholder was found in:%2\$s",
  759. ':group_by_clause',
  760. "\n" . $sql
  761. );
  762. }
  763. $has_group_by_placeholder = (strpos($sql, ':group_by_clause') !== FALSE) ? TRUE : FALSE;
  764. // Separate the SQL from quoted values
  765. preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\])*')|(?:[^']+)#", $sql, $matches);
  766. $table_alias = $table;
  767. $used_aliases = array();
  768. $table_map = array();
  769. // If we are not passing in existing joins, start with the specified table
  770. if (!$joins) {
  771. $joins[] = array(
  772. 'join_type' => 'none',
  773. 'table_name' => $table,
  774. 'table_alias' => $table_alias
  775. );
  776. }
  777. $used_aliases[] = $table_alias;
  778. foreach ($matches[0] as $match) {
  779. if ($match[0] != "'") {
  780. preg_match_all('#\b((?:(\w+)(?:\{(\w+)\})?=>)?(\w+)(?:\{(\w+)\})?)\.\w+\b#m', $match, $table_matches, PREG_SET_ORDER);
  781. foreach ($table_matches as $table_match) {
  782. if (!isset($table_match[5])) {
  783. $table_match[5] = NULL;
  784. }
  785. // This is a related table that is going to join to a once-removed table
  786. if (!empty($table_match[2])) {
  787. $related_table = $table_match[2];
  788. $route = fORMSchema::getRouteName($table, $related_table, $table_match[3]);
  789. $join_name = $table . '_' . $related_table . '{' . $route . '}';
  790. self::createJoin($table, $table_alias, $related_table, $route, $joins, $used_aliases);
  791. $once_removed_table = $table_match[4];
  792. $route = fORMSchema::getRouteName($related_table, $once_removed_table, $table_match[5]);
  793. $join_name = self::createJoin($related_table, $joins[$join_name]['table_alias'], $once_removed_table, $route, $joins, $used_aliases);
  794. $table_map[$table_match[1]] = $joins[$join_name]['table_alias'];
  795. // This is a related table
  796. } elseif (($table_match[4] != $table || fORMSchema::getRoutes($table, $table_match[4])) && $table_match[1] != $table) {
  797. $related_table = $table_match[4];
  798. $route = fORMSchema::getRouteName($table, $related_table, $table_match[5]);
  799. // If the related table is the current table and it is a one-to-many we don't want to join
  800. if ($table_match[4] == $table) {
  801. $one_to_many_routes = fORMSchema::getRoutes($table, $related_table, 'one-to-many');
  802. if (isset($one_to_many_routes[$route])) {
  803. $table_map[$table_match[1]] = $table_alias;
  804. continue;
  805. }
  806. }
  807. $join_name = self::createJoin($table, $table_alias, $related_table, $route, $joins, $used_aliases);
  808. $table_map[$table_match[1]] = $joins[$join_name]['table_alias'];
  809. }
  810. }
  811. }
  812. }
  813. // Determine if we joined a *-to-many relationship
  814. $joined_to_many = FALSE;
  815. foreach ($joins as $name => $join) {
  816. if (is_numeric($name)) {
  817. continue;
  818. }
  819. if (substr($name, -5) == '_join') {
  820. $joined_to_many = TRUE;
  821. break;
  822. }
  823. $main_table = preg_replace('#_' . $join['table_name'] . '{\w+}$#iD', '', $name);
  824. $second_table = $join['table_name'];
  825. $route = preg_replace('#^[^{]+{(\w+)}$#D', '\1', $name);
  826. $routes = fORMSchema::getRoutes($main_table, $second_table, '*-to-many');
  827. if (isset($routes[$route])) {
  828. $joined_to_many = TRUE;
  829. break;
  830. }
  831. }
  832. $found_order_by = FALSE;
  833. $from_clause = self::createFromClauseFromJoins($joins);
  834. // If we are joining on a *-to-many relationship we need to group by the
  835. // columns in the main table to prevent duplicate entries
  836. if ($joined_to_many) {
  837. $column_info = fORMSchema::retrieve()->getColumnInfo($table);
  838. $group_by_clause = ' GROUP BY ';
  839. $columns = array();
  840. foreach ($column_info as $column => $info) {
  841. $columns[] = $table . '.' . $column;
  842. }
  843. $group_by_columns = join(', ', $columns) . ' ';
  844. $group_by_clause .= $group_by_columns;
  845. } else {
  846. $group_by_clause = ' ';
  847. $group_by_columns = '';
  848. }
  849. // Put the SQL back together
  850. $new_sql = '';
  851. foreach ($matches[0] as $match) {
  852. $temp_sql = $match;
  853. // Get rid of the => notation and the :from_clause placeholder
  854. if ($match[0] !== "'") {
  855. foreach ($table_map as $arrow_table => $alias) {
  856. $temp_sql = str_replace($arrow_table, $alias, $temp_sql);
  857. }
  858. // In the ORDER BY clause we need to wrap columns in
  859. if ($found_order_by && $joined_to_many) {
  860. $temp_sql = preg_replace('#(?<!avg\(|count\(|max\(|min\(|sum\(|cast\(|case |when )\b((?!' . preg_quote($table, '#') . '\.)\w+\.\w+)\b#i', 'max(\1)', $temp_sql);
  861. }
  862. if ($joined_to_many && preg_match('#order\s+by#i', $temp_sql)) {
  863. $order_by_found = TRUE;
  864. $parts = preg_split('#(order\s+by)#i', $temp_sql, -1, PREG_SPLIT_DELIM_CAPTURE);
  865. $parts[2] = $temp_sql = preg_replace('#(?<!avg\(|count\(|max\(|min\(|sum\(|cast\(|case |when )\b((?!' . preg_quote($table, '#') . '\.)\w+\.\w+)\b#i', 'max(\1)', $parts[2]);
  866. $temp_sql = join('', $parts);
  867. }
  868. $temp_sql = str_replace(':from_clause', $from_clause, $temp_sql);
  869. if ($has_group_by_placeholder) {
  870. $temp_sql = preg_replace('#\s:group_by_clause\s#', strtr($group_by_clause, array('\\' => '\\\\', '$' => '\\$')), $temp_sql);
  871. } elseif ($group_by_columns) {
  872. $temp_sql = preg_replace('#(\sGROUP\s+BY\s((?!HAVING|ORDER\s+BY).)*)\s#i', '\1, ' . strtr($group_by_columns, array('\\' => '\\\\', '$' => '\\$')), $temp_sql);
  873. }
  874. }
  875. $new_sql .= $temp_sql;
  876. }
  877. return $new_sql;
  878. }
  879. /**
  880. * Parses a search string into search terms, supports quoted phrases and removes extra punctuation
  881. *
  882. * @internal
  883. *
  884. * @param string $terms A text string from a form input to parse into search terms
  885. * @param boolean $ignore_stop_words If stop words should be ignored, this setting will be ignored if all words are stop words
  886. * @return void
  887. */
  888. static public function parseSearchTerms($terms, $ignore_stop_words=FALSE)
  889. {
  890. $stop_words = array(
  891. 'i', 'a', 'an', 'are', 'as', 'at', 'be',
  892. 'by', 'de', 'en', 'en', 'for', 'from', 'how',
  893. 'in', 'is', 'it', 'la', 'of', 'on', 'or',
  894. 'that', 'the', 'this', 'to', 'was', 'what', 'when',
  895. 'where', 'who', 'will'
  896. );
  897. preg_match_all('#(?:"[^"]+"|[^\s]+)#', $terms, $matches);
  898. $good_terms = array();
  899. $ignored_terms = array();
  900. foreach ($matches[0] as $match) {
  901. // Remove phrases from quotes
  902. if ($match[0] == '"' && substr($match, -1)) {
  903. $match = substr($match, 1, -1);
  904. // Trim any punctuation off of the beginning and end of terms
  905. } else {
  906. $match = preg_replace('#(^[^a-z0-9]+|[^a-z0-9]+$)#iD', '', $match);
  907. }
  908. if ($ignore_stop_words && in_array(strtolower($match), $stop_words)) {
  909. $ignored_terms[] = $match;
  910. continue;
  911. }
  912. $good_terms[] = $match;
  913. }
  914. // If no terms were parsed, that means all words were stop words
  915. if ($ignored_terms && !$good_terms) {
  916. $good_terms = $ignored_terms;
  917. }
  918. return $good_terms;
  919. }
  920. /**
  921. * Removed aggregate function calls from where conditions array and puts them in a having conditions array
  922. *
  923. * @internal
  924. *
  925. * @param array &$where_conditions The where conditions to look through for aggregate functions
  926. * @return array The conditions to be put in a `HAVING` clause
  927. */
  928. static public function splitHavingConditions(&$where_conditions)
  929. {
  930. $having_conditions = array();
  931. foreach ($where_conditions as $column => $value)
  932. {
  933. if (preg_match('#^(count\(|max\(|avg\(|min\(|sum\()#i', $column)) {
  934. $having_conditions[$column] = $value;
  935. unset($where_conditions[$column]);
  936. }
  937. }
  938. return $having_conditions;
  939. }
  940. /**
  941. * Resets the configuration of the class
  942. *
  943. * @internal
  944. *
  945. * @return void
  946. */
  947. static public function reset()
  948. {
  949. self::$database_object = NULL;
  950. }
  951. /**
  952. * Return the instance of the fDatabase class
  953. *
  954. * @return fDatabase The database instance
  955. */
  956. static public function retrieve()
  957. {
  958. if (!self::$database_object) {
  959. throw new fProgrammerException(
  960. 'The method %1$s needs to be called before %2$s',
  961. 'attach()',
  962. 'retrieve()'
  963. );
  964. }
  965. return self::$database_object;
  966. }
  967. /**
  968. * Forces use as a static class
  969. *
  970. * @return fORMDatabase
  971. */
  972. private function __construct() { }
  973. }
  974. /**
  975. * Copyright (c) 2007-2009 Will Bond <will@flourishlib.com>, others
  976. *
  977. * Permission is hereby granted, free of charge, to any person obtaining a copy
  978. * of this software and associated documentation files (the "Software"), to deal
  979. * in the Software without restriction, including without limitation the rights
  980. * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  981. * copies of the Software, and to permit persons to whom the Software is
  982. * furnished to do so, subject to the following conditions:
  983. *
  984. * The above copyright notice and this permission notice shall be included in
  985. * all copies or substantial portions of the Software.
  986. *
  987. * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  988. * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  989. * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  990. * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  991. * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  992. * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  993. * THE SOFTWARE.
  994. */