PageRenderTime 52ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 1ms

/classphp/flourish/fSQLTranslation.php

https://github.com/jsuarez/Lexer
PHP | 1258 lines | 732 code | 201 blank | 325 comment | 141 complexity | e91542351d8467afc58e54131ba1321a MD5 | raw file
    

  1. <?php

    2. 

  2. /**

    3. 

  3.  * Takes a subset of SQL from MySQL, PostgreSQL, Oracle, SQLite and MSSQL and translates into the various dialects allowing for cross-database code

    4. 

  4.  * 

    5. 

  5.  * @copyright  Copyright (c) 2007-2009 Will Bond

    6. 

  6.  * @author     Will Bond [wb] <will@flourishlib.com>

    7. 

  7.  * @license    http://flourishlib.com/license

    8. 

  8.  * 

    9. 

  9.  * @package    Flourish

    10. 

  10.  * @link       http://flourishlib.com/fSQLTranslation

    11. 

  11.  * 

    12. 

  12.  * @version    1.0.0b11

    13. 

  13.  * @changes    1.0.0b11  Fixed a bug with translating MSSQL national columns over an ODBC connection [wb, 2009-09-18]

    14. 

  14.  * @changes    1.0.0b10  Changed last bug fix to support PHP 5.1.6 [wb, 2009-09-18]

    15. 

  15.  * @changes    1.0.0b9   Fixed another bug with parsing table aliases for MSSQL national columns [wb, 2009-09-18]

    16. 

  16.  * @changes    1.0.0b8   Fixed a bug with parsing table aliases that occurs when handling MSSQL national columns [wb, 2009-09-09] 

    17. 

  17.  * @changes    1.0.0b7   Fixed a bug with translating `NOT LIKE` operators in PostgreSQL [wb, 2009-07-15]

    18. 

  18.  * @changes    1.0.0b6   Changed replacement values in preg_replace() calls to be properly escaped [wb, 2009-06-11]

    19. 

  19.  * @changes    1.0.0b5   Update code to only translate data types inside of `CREATE TABLE` queries [wb, 2009-05-22]

    20. 

  20.  * @changes    1.0.0b4   Added the missing ::__get() method for callback support [wb, 2009-05-06]

    21. 

  21.  * @changes    1.0.0b3   Added Oracle and caching support, various bug fixes [wb, 2009-05-04]

    22. 

  22.  * @changes    1.0.0b2   Fixed a notice with SQLite foreign key constraints having no `ON` clauses [wb, 2009-02-21]

    23. 

  23.  * @changes    1.0.0b    The initial implementation [wb, 2007-09-25]

    24. 

  24.  */

    25. 

  25. class fSQLTranslation

    26. 

  26. {

    27. 

  27. 	// The following constants allow for nice looking callbacks to static methods

    28. 

  28. 	const sqliteCotangent    = 'fSQLTranslation::sqliteCotangent';

    29. 

  29. 	const sqliteLogBaseFirst = 'fSQLTranslation::sqliteLogBaseFirst';

    30. 

  30. 	const sqliteSign         = 'fSQLTranslation::sqliteSign';

    31. 

  31. 	

    32. 

  32. 	

    33. 

  33. 	/**

    34. 

  34. 	 * Composes text using fText if loaded

    35. 

  35. 	 * 

    36. 

  36. 	 * @param  string  $message    The message to compose

    37. 

  37. 	 * @param  mixed   $component  A string or number to insert into the message

    38. 

  38. 	 * @param  mixed   ...

    39. 

  39. 	 * @return string  The composed and possible translated message

    40. 

  40. 	 */

    41. 

  41. 	static protected function compose($message)

    42. 

  42. 	{

    43. 

  43. 		$args = array_slice(func_get_args(), 1);

    44. 

  44. 		

    45. 

  45. 		if (class_exists('fText', FALSE)) {

    46. 

  46. 			return call_user_func_array(

    47. 

  47. 				array('fText', 'compose'),

    48. 

  48. 				array($message, $args)

    49. 

  49. 			);

    50. 

  50. 		} else {

    51. 

  51. 			return vsprintf($message, $args);

    52. 

  52. 		}

    53. 

  53. 	}

    54. 

  54. 	

    55. 

  55. 		

    56. 

  56. 	/**

    57. 

  57. 	 * Takes the `FROM` clause from ::parseSelectSQL() and returns all of the tables and each one's alias

    58. 

  58. 	 * 

    59. 

  59. 	 * @param  string $clause  The SQL `FROM` clause to parse

    60. 

  60. 	 * @return array  The tables in the `FROM` clause, in the format `{table_alias} => {table_name}`

    61. 

  61. 	 */

    62. 

  62. 	static private function parseTableAliases($sql)

    63. 

  63. 	{

    64. 

  64. 		$aliases = array();

    65. 

  65. 		

    66. 

  66. 		// Turn comma joins into cross joins

    67. 

  67. 		if (preg_match('#^(?:\w+(?:\s+(?:as\s+)?(?:\w+))?)(?:\s*,\s*(?:\w+(?:\s+(?:as\s+)?(?:\w+))?))*$#isD', $sql)) {

    68. 

  68. 			$sql = str_replace(',', ' CROSS JOIN ', $sql);

    69. 

  69. 		}

    70. 

  70. 		

    71. 

  71. 		$tables = preg_split('#\s+((?:(?:CROSS|INNER|OUTER|LEFT|RIGHT)?\s+)*?JOIN)\s+#i', $sql);

    72. 

  72. 		

    73. 

  73. 		foreach ($tables as $table) {

    74. 

  74. 			// This grabs the table name and alias (if there is one)

    75. 

  75. 			preg_match('#^\s*([\w.]+|\(((?:[^()]+|\((?2)\))*)\))(?:\s+(?:as\s+)?((?!ON|USING)[\w.]+))?\s*(?:(?:ON|USING)\s+(.*))?\s*$#im', $table, $parts);

    76. 

  76. 			

    77. 

  77. 			$table_name  = $parts[1];

    78. 

  78. 			$table_alias = (!empty($parts[3])) ? $parts[3] : $parts[1]; 

    79. 

  79. 			

    80. 

  80. 			$aliases[$table_alias] = $table_name;

    81. 

  81. 		}

    82. 

  82. 		

    83. 

  83. 		return $aliases;

    84. 

  84. 	}

    85. 

  85. 	

    86. 

  86. 	

    87. 

  87. 	/**

    88. 

  88. 	 * Callback for custom SQLite function; calculates the cotangent of a number

    89. 

  89. 	 * 

    90. 

  90. 	 * @internal

    91. 

  91. 	 * 

    92. 

  92. 	 * @param  numeric $x  The number to calculate the cotangent of

    93. 

  93. 	 * @return numeric  The contangent of `$x`

    94. 

  94. 	 */

    95. 

  95. 	static public function sqliteCotangent($x)

    96. 

  96. 	{

    97. 

  97. 		return 1/tan($x);

    98. 

  98. 	}

    99. 

  99. 	

    100. 

  100. 	

    101. 

  101. 	/**

    102. 

  102. 	 * Callback for custom SQLite function; returns the current date

    103. 

  103. 	 * 

    104. 

  104. 	 * @internal

    105. 

  105. 	 * 

    106. 

  106. 	 * @return string  The current date

    107. 

  107. 	 */

    108. 

  108. 	static public function sqliteDate()

    109. 

  109. 	{

    110. 

  110. 		return date('Y-m-d');

    111. 

  111. 	}

    112. 

  112. 	

    113. 

  113. 	

    114. 

  114. 	/**

    115. 

  115. 	 * Callback for custom SQLite function; calculates the log to a specific base of a number

    116. 

  116. 	 * 

    117. 

  117. 	 * @internal

    118. 

  118. 	 * 

    119. 

  119. 	 * @param  integer $base  The base for the log calculation

    120. 

  120. 	 * @param  numeric $num   The number to calculate the logarithm of

    121. 

  121. 	 * @return numeric  The logarithm of `$num` to `$base`

    122. 

  122. 	 */

    123. 

  123. 	static public function sqliteLogBaseFirst($base, $num)

    124. 

  124. 	{

    125. 

  125. 		return log($num, $base);

    126. 

  126. 	}

    127. 

  127. 	

    128. 

  128. 	

    129. 

  129. 	/**

    130. 

  130. 	 * Callback for custom SQLite function; returns the sign of the number

    131. 

  131. 	 * 

    132. 

  132. 	 * @internal

    133. 

  133. 	 * 

    134. 

  134. 	 * @param  numeric $x  The number to change the sign of

    135. 

  135. 	 * @return numeric  `-1` if a negative sign, `0` if zero, `1` if positive sign

    136. 

  136. 	 */

    137. 

  137. 	static public function sqliteSign($x)

    138. 

  138. 	{

    139. 

  139. 		if ($x == 0) {

    140. 

  140. 			return 0;

    141. 

  141. 		}

    142. 

  142. 		if ($x > 0) {

    143. 

  143. 			return 1;

    144. 

  144. 		}

    145. 

  145. 		return -1;

    146. 

  146. 	}

    147. 

  147. 	

    148. 

  148. 	

    149. 

  149. 	/**

    150. 

  150. 	 * Callback for custom SQLite function; returns the current time

    151. 

  151. 	 * 

    152. 

  152. 	 * @internal

    153. 

  153. 	 * 

    154. 

  154. 	 * @return string  The current time

    155. 

  155. 	 */

    156. 

  156. 	static public function sqliteTime()

    157. 

  157. 	{

    158. 

  158. 		return date('H:i:s');

    159. 

  159. 	}

    160. 

  160. 	

    161. 

  161. 	

    162. 

  162. 	/**

    163. 

  163. 	 * Callback for custom SQLite function; returns the current timestamp

    164. 

  164. 	 * 

    165. 

  165. 	 * @internal

    166. 

  166. 	 * 

    167. 

  167. 	 * @return string  The current date

    168. 

  168. 	 */

    169. 

  169. 	static public function sqliteTimestamp()

    170. 

  170. 	{

    171. 

  171. 		return date('Y-m-d H:i:s');

    172. 

  172. 	}

    173. 

  173. 	

    174. 

  174. 	

    175. 

  175. 	/**

    176. 

  176. 	 * The fCache object to cache schema info and, optionally, translated queries to

    177. 

  177. 	 * 

    178. 

  178. 	 * @var fCache

    179. 

  179. 	 */

    180. 

  180. 	private $cache;

    181. 

  181. 	

    182. 

  182. 	/**

    183. 

  183. 	 * The fDatabase instance

    184. 

  184. 	 * 

    185. 

  185. 	 * @var fDatabase

    186. 

  186. 	 */

    187. 

  187. 	private $database;

    188. 

  188. 	

    189. 

  189. 	/**

    190. 

  190. 	 * If debugging is enabled

    191. 

  191. 	 * 

    192. 

  192. 	 * @var boolean

    193. 

  193. 	 */

    194. 

  194. 	private $debug;

    195. 

  195. 	

    196. 

  196. 	/**

    197. 

  197. 	 * Database-specific schema information needed for translation

    198. 

  198. 	 * 

    199. 

  199. 	 * @var array

    200. 

  200. 	 */

    201. 

  201. 	private $schema_info;

    202. 

  202. 	

    203. 

  203. 	

    204. 

  204. 	/**

    205. 

  205. 	 * Sets up the class and creates functions for SQLite databases

    206. 

  206. 	 * 

    207. 

  207. 	 * @param  fDatabase $database    The database being translated for

    208. 

  208. 	 * @param  mixed     $connection  The connection resource or PDO object

    209. 

  209. 	 * @return fSQLTranslation

    210. 

  210. 	 */

    211. 

  211. 	public function __construct($database)

    212. 

  212. 	{

    213. 

  213. 		$this->database = $database;

    214. 

  214. 		$this->database->inject($this);

    215. 

  215. 		

    216. 

  216. 		if ($database->getType() == 'sqlite') {

    217. 

  217. 			$this->createSQLiteFunctions();

    218. 

  218. 		}

    219. 

  219. 		

    220. 

  220. 		$this->schema_info = array();

    221. 

  221. 	}

    222. 

  222. 	

    223. 

  223. 	

    224. 

  224. 	/**

    225. 

  225. 	 * All requests that hit this method should be requests for callbacks

    226. 

  226. 	 * 

    227. 

  227. 	 * @internal

    228. 

  228. 	 * 

    229. 

  229. 	 * @param  string $method  The method to create a callback for

    230. 

  230. 	 * @return callback  The callback for the method requested

    231. 

  231. 	 */

    232. 

  232. 	public function __get($method)

    233. 

  233. 	{

    234. 

  234. 		return array($this, $method);		

    235. 

  235. 	}

    236. 

  236. 	

    237. 

  237. 	

    238. 

  238. 	/**

    239. 

  239. 	 * Clears all of the schema info out of the object and, if set, the fCache object

    240. 

  240. 	 * 

    241. 

  241. 	 * @return void

    242. 

  242. 	 */

    243. 

  243. 	public function clearCache()

    244. 

  244. 	{

    245. 

  245. 		$this->schema_info = array();

    246. 

  246. 		if ($this->cache) {

    247. 

  247. 			$prefix = $this->makeCachePrefix();

    248. 

  248. 			$this->cache->delete($prefix . 'schema_info');

    249. 

  249. 		}

    250. 

  250. 	}

    251. 

  251. 	

    252. 

  252. 	

    253. 

  253. 	/**

    254. 

  254. 	 * Creates a trigger for SQLite that handles an on delete clause

    255. 

  255. 	 * 

    256. 

  256. 	 * @param  array  &$extra_statements   An array of extra SQL statements to be added to the SQL

    257. 

  257. 	 * @param  string $referencing_table   The table that contains the foreign key

    258. 

  258. 	 * @param  string $referencing_column  The column the foriegn key constraint is on

    259. 

  259. 	 * @param  string $referenced_table    The table the foreign key references

    260. 

  260. 	 * @param  string $referenced_column   The column the foreign key references

    261. 

  261. 	 * @param  string $delete_clause       What is to be done on a delete

    262. 

  262. 	 * @return string  The trigger

    263. 

  263. 	 */

    264. 

  264. 	private function createSQLiteForeignKeyTriggerOnDelete(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $delete_clause)

    265. 

  265. 	{

    266. 

  266. 		switch (strtolower($delete_clause)) {

    267. 

  267. 			case 'no action':

    268. 

  268. 			case 'restrict':

    269. 

  269. 				$extra_statements[] = "CREATE TRIGGER fkd_res_" . $referencing_table . "_" . $referencing_column . "

    270. 

  270. 							 BEFORE DELETE ON " . $referenced_table . "

    271. 

  271. 							 FOR EACH ROW BEGIN

    272. 

  272. 								 SELECT RAISE(ROLLBACK, 'delete on table \"" . $referenced_table . "\" can not be executed because it would violate the foreign key constraint on column \"" . $referencing_column . "\" of table \"" . $referencing_table . "\"')

    273. 

  273. 								 WHERE (SELECT " . $referencing_column . " FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_table . ") IS NOT NULL;

    274. 

  274. 							 END";

    275. 

  275. 				break;

    276. 

  276. 			

    277. 

  277. 			case 'set null':

    278. 

  278. 				$extra_statements[] = "CREATE TRIGGER fkd_nul_" . $referencing_table . "_" . $referencing_column . "

    279. 

  279. 							 BEFORE DELETE ON " . $referenced_table . "

    280. 

  280. 							 FOR EACH ROW BEGIN

    281. 

  281. 								 UPDATE " . $referencing_table . " SET " . $referencing_column . " = NULL WHERE " . $referencing_column . " = OLD." . $referenced_column . ";

    282. 

  282. 							 END";

    283. 

  283. 				break;

    284. 

  284. 				

    285. 

  285. 			case 'cascade':

    286. 

  286. 				$extra_statements[] = "CREATE TRIGGER fkd_cas_" . $referencing_table . "_" . $referencing_column . "

    287. 

  287. 							 BEFORE DELETE ON " . $referenced_table . "

    288. 

  288. 							 FOR EACH ROW BEGIN

    289. 

  289. 								 DELETE FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_column . ";

    290. 

  290. 							 END";

    291. 

  291. 				break;

    292. 

  292. 		}

    293. 

  293. 	}

    294. 

  294. 	

    295. 

  295. 	

    296. 

  296. 	/**

    297. 

  297. 	 * Creates a trigger for SQLite that handles an on update clause

    298. 

  298. 	 * 

    299. 

  299. 	 * @param  array  &$extra_statements   An array of extra SQL statements to be added to the SQL

    300. 

  300. 	 * @param  string $referencing_table   The table that contains the foreign key

    301. 

  301. 	 * @param  string $referencing_column  The column the foriegn key constraint is on

    302. 

  302. 	 * @param  string $referenced_table    The table the foreign key references

    303. 

  303. 	 * @param  string $referenced_column   The column the foreign key references

    304. 

  304. 	 * @param  string $update_clause       What is to be done on an update

    305. 

  305. 	 * @return string  The trigger

    306. 

  306. 	 */

    307. 

  307. 	private function createSQLiteForeignKeyTriggerOnUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $update_clause)

    308. 

  308. 	{

    309. 

  309. 		switch (strtolower($update_clause)) {

    310. 

  310. 			case 'no action':

    311. 

  311. 			case 'restrict':

    312. 

  312. 				$extra_statements[] = "\nCREATE TRIGGER fku_res_" . $referencing_table . "_" . $referencing_column . "

    313. 

  313. 							 BEFORE UPDATE ON " . $referenced_table . "

    314. 

  314. 							 FOR EACH ROW BEGIN

    315. 

  315. 								 SELECT RAISE(ROLLBACK, 'update on table \"" . $referenced_table . "\" can not be executed because it would violate the foreign key constraint on column \"" . $referencing_column . "\" of table \"" . $referencing_table . "\"')

    316. 

  316. 								 WHERE (SELECT " . $referencing_column . " FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_column . ") IS NOT NULL;

    317. 

  317. 							 END";

    318. 

  318. 				break;

    319. 

  319. 			

    320. 

  320. 			case 'set null':

    321. 

  321. 				$extra_statements[] = "\nCREATE TRIGGER fku_nul_" . $referencing_table . "_" . $referencing_column . "

    322. 

  322. 							 BEFORE UPDATE ON " . $referenced_table . "

    323. 

  323. 							 FOR EACH ROW BEGIN

    324. 

  324. 								 UPDATE " . $referencing_table . " SET " . $referencing_column . " = NULL WHERE OLD." . $referenced_column . " <> NEW." . $referenced_column . " AND " . $referencing_column . " = OLD." . $referenced_column . ";

    325. 

  325. 							 END";

    326. 

  326. 				break;

    327. 

  327. 				

    328. 

  328. 			case 'cascade':

    329. 

  329. 				$extra_statements[] = "\nCREATE TRIGGER fku_cas_" . $referencing_table . "_" . $referencing_column . "

    330. 

  330. 							 BEFORE UPDATE ON " . $referenced_table . "

    331. 

  331. 							 FOR EACH ROW BEGIN

    332. 

  332. 								 UPDATE " . $referencing_table . " SET " . $referencing_column . " = NEW." . $referenced_column . " WHERE OLD." . $referenced_column . " <> NEW." . $referenced_column . " AND " . $referencing_column . " = OLD." . $referenced_column . ";

    333. 

  333. 							 END";

    334. 

  334. 				break;

    335. 

  335. 		}

    336. 

  336. 	}

    337. 

  337. 	

    338. 

  338. 	

    339. 

  339. 	/**

    340. 

  340. 	 * Creates a trigger for SQLite that prevents inserting or updating to values the violate a `FOREIGN KEY` constraint

    341. 

  341. 	 * 

    342. 

  342. 	 * @param  array  &$extra_statements   An array of extra SQL statements to be added to the SQL

    343. 

  343. 	 * @param  string  $referencing_table     The table that contains the foreign key

    344. 

  344. 	 * @param  string  $referencing_column    The column the foriegn key constraint is on

    345. 

  345. 	 * @param  string  $referenced_table      The table the foreign key references

    346. 

  346. 	 * @param  string  $referenced_column     The column the foreign key references

    347. 

  347. 	 * @param  boolean $referencing_not_null  If the referencing columns is set to not null

    348. 

  348. 	 * @return string  The trigger

    349. 

  349. 	 */

    350. 

  350. 	private function createSQLiteForeignKeyTriggerValidInsertUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $referencing_not_null)

    351. 

  351. 	{

    352. 

  352. 		// Verify key on inserts

    353. 

  353. 		$sql  = "\nCREATE TRIGGER fki_ver_" . $referencing_table . "_" . $referencing_column . "

    354. 

  354. 					  BEFORE INSERT ON " . $referencing_table . "

    355. 

  355. 					  FOR EACH ROW BEGIN

    356. 

  356. 						  SELECT RAISE(ROLLBACK, 'insert on table \"" . $referencing_table . "\" violates foreign key constraint on column \"" . $referencing_column . "\"')

    357. 

  357. 							  WHERE ";

    358. 

  358. 		if (!$referencing_not_null) {

    359. 

  359. 			$sql .= "NEW." . $referencing_column . " IS NOT NULL AND ";

    360. 

  360. 		}

    361. 

  361. 		$sql .= " (SELECT " . $referenced_column . " FROM " . $referenced_table . " WHERE " . $referenced_column . " = NEW." . $referencing_column . ") IS NULL;

    362. 

  362. 					  END";

    363. 

  363. 					  

    364. 

  364. 		$extra_statements[] = $sql;

    365. 

  365. 					

    366. 

  366. 		// Verify key on updates

    367. 

  367. 		$sql = "\nCREATE TRIGGER fku_ver_" . $referencing_table . "_" . $referencing_column . "

    368. 

  368. 					  BEFORE UPDATE ON " . $referencing_table . "

    369. 

  369. 					  FOR EACH ROW BEGIN

    370. 

  370. 						  SELECT RAISE(ROLLBACK, 'update on table \"" . $referencing_table . "\" violates foreign key constraint on column \"" . $referencing_column . "\"')

    371. 

  371. 							  WHERE ";

    372. 

  372. 		if (!$referencing_not_null) {

    373. 

  373. 			$sql .= "NEW." . $referencing_column . " IS NOT NULL AND ";

    374. 

  374. 		}

    375. 

  375. 		$sql .= " (SELECT " . $referenced_column . " FROM " . $referenced_table . " WHERE " . $referenced_column . " = NEW." . $referencing_column . ") IS NULL;

    376. 

  376. 					  END";

    377. 

  377. 		

    378. 

  378. 		$extra_statements[] = $sql;

    379. 

  379. 	}

    380. 

  380. 	

    381. 

  381. 	

    382. 

  382. 	/**

    383. 

  383. 	 * Adds a number of math functions to SQLite that MSSQL, MySQL and PostgreSQL have by default

    384. 

  384. 	 * 

    385. 

  385. 	 * @return void

    386. 

  386. 	 */

    387. 

  387. 	private function createSQLiteFunctions()

    388. 

  388. 	{

    389. 

  389. 		$function = array();

    390. 

  390. 		$functions[] = array('acos',     'acos',                                         1);

    391. 

  391. 		$functions[] = array('asin',     'asin',                                         1);

    392. 

  392. 		$functions[] = array('atan',     'atan',                                         1);

    393. 

  393. 		$functions[] = array('atan2',    'atan2',                                        2);

    394. 

  394. 		$functions[] = array('ceil',     'ceil',                                         1);

    395. 

  395. 		$functions[] = array('ceiling',  'ceil',                                         1);

    396. 

  396. 		$functions[] = array('cos',      'cos',                                          1);

    397. 

  397. 		$functions[] = array('cot',      array('fSQLTranslation', 'sqliteCotangent'),    1);

    398. 

  398. 		$functions[] = array('degrees',  'rad2deg',                                      1);

    399. 

  399. 		$functions[] = array('exp',      'exp',                                          1);

    400. 

  400. 		$functions[] = array('floor',    'floor',                                        1);

    401. 

  401. 		$functions[] = array('ln',       'log',                                          1);

    402. 

  402. 		$functions[] = array('log',      array('fSQLTranslation', 'sqliteLogBaseFirst'), 2);

    403. 

  403. 		$functions[] = array('ltrim',    'ltrim',                                        1);

    404. 

  404. 		$functions[] = array('pi',       'pi',                                           0);

    405. 

  405. 		$functions[] = array('power',    'pow',                                          2);

    406. 

  406. 		$functions[] = array('radians',  'deg2rad',                                      1);

    407. 

  407. 		$functions[] = array('rtrim',    'rtrim',                                        1);

    408. 

  408. 		$functions[] = array('sign',     array('fSQLTranslation', 'sqliteSign'),         1);

    409. 

  409. 		$functions[] = array('sqrt',     'sqrt',                                         1);

    410. 

  410. 		$functions[] = array('sin',      'sin',                                          1);

    411. 

  411. 		$functions[] = array('tan',      'tan',                                          1);

    412. 

  412. 		$functions[] = array('trim',     'trim',                                         1);

    413. 

  413. 		

    414. 

  414. 		if ($this->database->getExtension() == 'sqlite') {

    415. 

  415. 			$functions[] = array('current_date',      array('fSQLTranslation', 'sqliteDate'), 0);

    416. 

  416. 			$functions[] = array('current_time',      array('fSQLTranslation', 'sqliteTime'), 0);

    417. 

  417. 			$functions[] = array('current_timestamp', array('fSQLTranslation', 'sqliteTimestamp'), 0);	

    418. 

  418. 		}

    419. 

  419. 		

    420. 

  420. 		foreach ($functions as $function) {

    421. 

  421. 			if ($this->database->getExtension() == 'pdo') {

    422. 

  422. 				$this->database->getConnection()->sqliteCreateFunction($function[0], $function[1], $function[2]);

    423. 

  423. 			} else {

    424. 

  424. 				sqlite_create_function($this->database->getConnection(), $function[0], $function[1], $function[2]);

    425. 

  425. 			}

    426. 

  426. 		}

    427. 

  427. 	}

    428. 

  428. 	

    429. 

  429. 	

    430. 

  430. 	/**

    431. 

  431. 	 * Sets the schema info to be cached to the fCache object specified

    432. 

  432. 	 * 

    433. 

  433. 	 * @param  fCache  $cache  The cache to cache to

    434. 

  434. 	 * @return void

    435. 

  435. 	 */

    436. 

  436. 	public function enableCaching($cache)

    437. 

  437. 	{

    438. 

  438. 		$this->cache       = $cache;

    439. 

  439. 		$this->schema_info = $this->cache->get($this->makeCachePrefix() . 'schema_info', array());

    440. 

  440. 	}

    441. 

  441. 	

    442. 

  442. 	

    443. 

  443. 	/**

    444. 

  444. 	 * Sets if debug messages should be shown

    445. 

  445. 	 *

    446. 

  446. 	 * @param  boolean $flag  If debugging messages should be shown

    447. 

  447. 	 * @return void

    448. 

  448. 	 */

    449. 

  449. 	public function enableDebugging($flag)

    450. 

  450. 	{

    451. 

  451. 		$this->debug = (boolean) $flag;

    452. 

  452. 	}

    453. 

  453. 	

    454. 

  454. 	

    455. 

  455. 	/**

    456. 

  456. 	 * Fixes pulling unicode data out of national data type MSSQL columns

    457. 

  457. 	 * 

    458. 

  458. 	 * @param  string $sql  The SQL to fix

    459. 

  459. 	 * @return string  The fixed SQL

    460. 

  460. 	 */

    461. 

  461. 	private function fixMSSQLNationalColumns($sql)

    462. 

  462. 	{

    463. 

  463. 		if (!preg_match_all('#select((?:(?:(?!\sfrom\s)[^()])+|\(((?:[^()]+|\((?2)\))*)\))*\s)from((?:(?:(?!\sunion\s|\swhere\s|\sgroup by\s|\slimit\s|\sorder by\s)[^()])+|\(((?:[^()]+|\((?4)\))*)\))*)(?=\swhere\s|\sgroup by\s|\slimit\s|\sorder by\s|\sunion\s|\)|$)#i', $sql, $matches, PREG_SET_ORDER)) {

    464. 

  464. 			return $sql;

    465. 

  465. 		}

    466. 

  466. 		

    467. 

  467. 		if (!isset($this->schema_info['national_columns'])) {

    468. 

  468. 			$result = $this->database->query(

    469. 

  469. 				"SELECT

    470. 

  470. 						c.table_name  AS 'table',						

    471. 

  471. 						c.column_name AS 'column',

    472. 

  472. 						c.data_type   AS 'type'

    473. 

  473. 					FROM

    474. 

  474. 						INFORMATION_SCHEMA.COLUMNS AS c

    475. 

  475. 					WHERE

    476. 

  476. 						(c.data_type = 'nvarchar' OR

    477. 

  477. 						 c.data_type = 'ntext' OR

    478. 

  478. 						 c.data_type = 'nchar') AND

    479. 

  479. 						c.table_catalog = DB_NAME()

    480. 

  480. 					ORDER BY

    481. 

  481. 						lower(c.table_name) ASC,

    482. 

  482. 						lower(c.column_name) ASC"

    483. 

  483. 			);

    484. 

  484. 			

    485. 

  485. 			$national_columns = array();

    486. 

  486. 			$national_types   = array();

    487. 

  487. 			

    488. 

  488. 			foreach ($result as $row) {

    489. 

  489. 				if (!isset($national_columns[$row['table']])) {

    490. 

  490. 					$national_columns[$row['table']] = array();	

    491. 

  491. 					$national_types[$row['table']]   = array();

    492. 

  492. 				}	

    493. 

  493. 				$national_columns[$row['table']][] = $row['column'];

    494. 

  494. 				$national_types[$row['table']][$row['column']] = $row['type'];

    495. 

  495. 			}

    496. 

  496. 			

    497. 

  497. 			$this->schema_info['national_columns'] = $national_columns;

    498. 

  498. 			$this->schema_info['national_types']   = $national_types;

    499. 

  499. 			

    500. 

  500. 			if ($this->cache) {

    501. 

  501. 				$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);		

    502. 

  502. 			}

    503. 

  503. 			

    504. 

  504. 		} else {

    505. 

  505. 			$national_columns = $this->schema_info['national_columns'];

    506. 

  506. 			$national_types   = $this->schema_info['national_types'];	

    507. 

  507. 		}

    508. 

  508. 		

    509. 

  509. 		$additions = array();

    510. 

  510. 		

    511. 

  511. 		foreach ($matches as $select) {

    512. 

  512. 			$select_clause = trim($select[1]);

    513. 

  513. 			$from_clause   = trim($select[3]);

    514. 

  514. 			

    515. 

  515. 			// This recursively fixes sub-selects

    516. 

  516. 			if (preg_match('#\bselect\b#', $from_clause)) {

    517. 

  517. 				$from_clause = $this->fixMSSQLNationalColumns($from_clause);	

    518. 

  518. 			}

    519. 

  519. 			

    520. 

  520. 			$table_aliases = self::parseTableAliases($from_clause);

    521. 

  521. 			

    522. 

  522. 			preg_match_all('#([^,()]+|\((?:(?1)|,)*\))+#i', $select_clause, $selections);

    523. 

  523. 			$selections    = array_map('trim', $selections[0]);

    524. 

  524. 			$to_fix        = array();

    525. 

  525. 			

    526. 

  526. 			foreach ($selections as $selection) {

    527. 

  527. 				// We just skip CASE statements since we can't really do those reliably

    528. 

  528. 				if (preg_match('#^case#i', $selection)) {

    529. 

  529. 					continue;	

    530. 

  530. 				}

    531. 

  531. 				

    532. 

  532. 				if (preg_match('#(\w+)\.\*#i', $selection, $match)) {

    533. 

  533. 					$table = $table_aliases[$match[1]];

    534. 

  534. 					if (empty($national_columns[$table])) {

    535. 

  535. 						continue;	

    536. 

  536. 					}

    537. 

  537. 					if (!isset($to_fix[$table])) {

    538. 

  538. 						$to_fix[$table] = array();	

    539. 

  539. 					}

    540. 

  540. 					$to_fix[$table] = array_merge($to_fix[$table], $national_columns[$table]);

    541. 

  541. 						

    542. 

  542. 				} elseif (preg_match('#\*#', $selection, $match)) {

    543. 

  543. 					foreach ($table_aliases as $alias => $table) {

    544. 

  544. 						if (empty($national_columns[$table])) {

    545. 

  545. 							continue;	

    546. 

  546. 						}

    547. 

  547. 						if (!isset($to_fix[$table])) {

    548. 

  548. 							$to_fix[$table] = array();	

    549. 

  549. 						}

    550. 

  550. 						$to_fix[$table] = array_merge($to_fix[$table], $national_columns[$table]); 		

    551. 

  551. 					}

    552. 

  552. 					

    553. 

  553. 				} elseif (preg_match('#^(?:(\w+)\.(\w+)|((?:min|max|trim|rtrim|ltrim|substring|replace)\((\w+)\.(\w+).*?\)))(?:\s+as\s+(\w+))?$#iD', $selection, $match)) {

    554. 

  554. 					$table = $match[1] . ((isset($match[4])) ? $match[4] : '');

    555. 

  555. 					$table = $table_aliases[$table];

    556. 

  556. 					

    557. 

  557. 					$column = $match[2] . ((isset($match[5])) ? $match[5] : '');;

    558. 

  558. 					

    559. 

  559. 					if (empty($national_columns[$table]) || !in_array($column, $national_columns[$table])) {

    560. 

  560. 						continue;	

    561. 

  561. 					}

    562. 

  562. 					

    563. 

  563. 					if (!isset($to_fix[$table])) {

    564. 

  564. 						$to_fix[$table] = array();	

    565. 

  565. 					}

    566. 

  566. 					

    567. 

  567. 					// Handle column aliasing

    568. 

  568. 					if (!empty($match[6])) {

    569. 

  569. 						$column = array('column' => $column, 'alias' => $match[6]);	

    570. 

  570. 					}

    571. 

  571. 					

    572. 

  572. 					if (!empty($match[3])) {

    573. 

  573. 						if (!is_array($column)) {

    574. 

  574. 							$column = array('column' => $column);

    575. 

  575. 						}	

    576. 

  576. 						$column['expression'] = $match[3];

    577. 

  577. 					}

    578. 

  578. 					

    579. 

  579. 					$to_fix[$table] = array_merge($to_fix[$table], array($column));

    580. 

  580. 				

    581. 

  581. 				// Match unqualified column names

    582. 

  582. 				} elseif (preg_match('#^(?:(\w+)|((?:min|max|trim|rtrim|ltrim|substring|replace)\((\w+).*?\)))(?:\s+as\s+(\w+))?$#iD', $selection, $match)) {

    583. 

  583. 					$column = $match[1] . ((isset($match[3])) ? $match[3] : '');

    584. 

  584. 					foreach ($table_aliases as $alias => $table) {

    585. 

  585. 						if (empty($national_columns[$table])) {

    586. 

  586. 							continue;	

    587. 

  587. 						}

    588. 

  588. 						if (!in_array($column, $national_columns[$table])) {

    589. 

  589. 							continue;

    590. 

  590. 						}

    591. 

  591. 						if (!isset($to_fix[$table])) {

    592. 

  592. 							$to_fix[$table] = array();	

    593. 

  593. 						}

    594. 

  594. 						

    595. 

  595. 						// Handle column aliasing

    596. 

  596. 						if (!empty($match[4])) {

    597. 

  597. 							$column = array('column' => $column, 'alias' => $match[4]);	

    598. 

  598. 						}

    599. 

  599. 						

    600. 

  600. 						if (!empty($match[2])) {

    601. 

  601. 							if (!is_array($column)) {

    602. 

  602. 								$column = array('column' => $column);

    603. 

  603. 							}	

    604. 

  604. 							$column['expression'] = $match[2];

    605. 

  605. 						}

    606. 

  606. 						

    607. 

  607. 						$to_fix[$table] = array_merge($to_fix[$table], array($column)); 		

    608. 

  608. 					}

    609. 

  609. 				}

    610. 

  610. 			}

    611. 

  611. 			

    612. 

  612. 			$reverse_table_aliases = array_flip($table_aliases);

    613. 

  613. 			foreach ($to_fix as $table => $columns) {

    614. 

  614. 				$columns = array_unique($columns);

    615. 

  615. 				$alias   = $reverse_table_aliases[$table];

    616. 

  616. 				foreach ($columns as $column) {

    617. 

  617. 					if (is_array($column)) {

    618. 

  618. 						if (isset($column['alias'])) {

    619. 

  619. 							$as = ' AS fmssqln__' . $column['alias'];

    620. 

  620. 						} else {

    621. 

  621. 							$as = ' AS fmssqln__' . $column['column']; 	

    622. 

  622. 						}

    623. 

  623. 						if (isset($column['expression'])) {

    624. 

  624. 							$expression = $column['expression'];	

    625. 

  625. 						} else {

    626. 

  626. 							$expression = $alias . '.' . $column['column'];

    627. 

  627. 						}

    628. 

  628. 						$column = $column['column'];

    629. 

  629. 					} else {

    630. 

  630. 						$as     = ' AS fmssqln__' . $column;

    631. 

  631. 						$expression = $alias . '.' . $column;

    632. 

  632. 					}

    633. 

  633. 					if ($national_types[$table][$column] == 'ntext') {

    634. 

  634. 						$cast = 'CAST(' . $expression . ' AS IMAGE)';	

    635. 

  635. 					} else {

    636. 

  636. 						$cast = 'CAST(' . $expression . ' AS VARBINARY(MAX))';

    637. 

  637. 					}

    638. 

  638. 					$additions[] = $cast . $as;

    639. 

  639. 				}		

    640. 

  640. 			}

    641. 

  641. 			

    642. 

  642. 			$replace = preg_replace('#\bselect\s+' . preg_quote($select_clause, '#') . '#i', 'SELECT ' . strtr(join(', ', array_merge($selections, $additions)), array('\\' => '\\\\', '$' => '\\$')), $select);

    643. 

  643. 			$sql = str_replace($select, $replace, $sql);	

    644. 

  644. 		}

    645. 

  645. 		

    646. 

  646. 		return $sql;

    647. 

  647. 	}

    648. 

  648. 	

    649. 

  649. 	

    650. 

  650. 	/**

    651. 

  651. 	 * Fixes pulling unicode data out of national data type MSSQL columns

    652. 

  652. 	 * 

    653. 

  653. 	 * @param  string $sql       The SQL to fix

    654. 

  654. 	 * @param  array  &$strings  The strings from the SQL

    655. 

  655. 	 * @return string  The fixed SQL

    656. 

  656. 	 */

    657. 

  657. 	private function fixOracleEmptyStrings($sql, &$strings)

    658. 

  658. 	{

    659. 

  659. 		if (preg_match('#^(UPDATE\s+(?:\w+\.)?\w+\s+)(SET((?:(?:(?!\bwhere\b|\breturning\b)[^()])+|\(((?:[^()]+|\((?3)\))*)\))*))(.*)$#i', $sql, $set_match)) {

    660. 

  660. 			$sql        = $set_match[1] . ':set_clause ' . $set_match[5];

    661. 

  661. 			$set_clause = $set_match[2];

    662. 

  662. 		} else {

    663. 

  663. 			$set_clause = FALSE;

    664. 

  664. 		}	

    665. 

  665. 		

    666. 

  666. 		foreach ($strings as $number => &$string) {

    667. 

  667. 			if ($string == "''") {

    668. 

  668. 				$sql = preg_replace('#(\s)=(?=\s+:string_' . $number . '\b)#',       '\1IS',     $sql, 1, $count_equal);

    669. 

  669. 				$sql = preg_replace('#(\s)(!=|<>)(?=\s+:string_' . $number . '\b)#', '\1IS NOT', $sql, 1, $count_not_equal);

    670. 

  670. 				if ($count_equal || $count_not_equal) {

    671. 

  671. 					$string = 'NULL';

    672. 

  672. 				}	

    673. 

  673. 			}

    674. 

  674. 		}

    675. 

  675. 		

    676. 

  676. 		if ($set_clause) {

    677. 

  677. 			$sql = preg_replace('#:set_clause\b#', strtr($set_clause, array('\\' => '\\\\', '$' => '\\$')), $sql, 1);	

    678. 

  678. 		}

    679. 

  679. 		

    680. 

  680. 		return $sql;

    681. 

  681. 	}

    682. 

  682. 	

    683. 

  683. 	

    684. 

  684. 	/**

    685. 

  685. 	 * Creates a unique cache prefix to help prevent cache conflicts

    686. 

  686. 	 * 

    687. 

  687. 	 * @return void

    688. 

  688. 	 */

    689. 

  689. 	private function makeCachePrefix()

    690. 

  690. 	{

    691. 

  691. 		$prefix  = 'fSQLTranslation::' . $this->database->getType() . '::';

    692. 

  692. 		if ($this->database->getHost()) {

    693. 

  693. 			$prefix .= $this->database->getHost() . '::';	

    694. 

  694. 		}

    695. 

  695. 		if ($this->database->getPort()) {

    696. 

  696. 			$prefix .= $this->database->getPort() . '::';	

    697. 

  697. 		}

    698. 

  698. 		$prefix .= $this->database->getDatabase() . '::';

    699. 

  699. 		if ($this->database->getUsername()) {

    700. 

  700. 			$prefix .= $this->database->getUsername() . '::';	

    701. 

  701. 		}

    702. 

  702. 		return $prefix;	

    703. 

  703. 	}

    704. 

  704. 	

    705. 

  705. 	

    706. 

  706. 	/**

    707. 

  707. 	 * Translates Flourish SQL into the dialect for the current database

    708. 

  708. 	 * 

    709. 

  709. 	 * @internal

    710. 

  710. 	 * 

    711. 

  711. 	 * @param  array $statements  The SQL statements to translate

    712. 

  712. 	 * @param  array $strings     The strings to interpolate back into the SQL statements

    713. 

  713. 	 * @return array  The translated SQL statements all ready for execution. Statements that have been translated will have string key of the original SQL, all other will have a numeric key.

    714. 

  714. 	 */

    715. 

  715. 	public function translate($statements, $strings)

    716. 

  716. 	{

    717. 

  717. 		$output = array();

    718. 

  718. 		

    719. 

  719. 		foreach ($statements as $number => $sql) {

    720. 

  720. 			

    721. 

  721. 			// These fixes don't need to know about strings

    722. 

  722. 			$new_sql = $this->translateBasicSyntax($sql);

    723. 

  723. 			

    724. 

  724. 			if ($this->database->getType() == 'mssql' || $this->database->getType() == 'oracle') {

    725. 

  725. 				$new_sql = $this->translateLimitOffsetToRowNumber($new_sql);	

    726. 

  726. 			}

    727. 

  727. 			

    728. 

  728. 			// SQL Server does not like to give unicode results back to PHP without some coersion

    729. 

  729. 			if ($this->database->getType() == 'mssql') {

    730. 

  730. 				$new_sql = $this->fixMSSQLNationalColumns($new_sql);	

    731. 

  731. 			}

    732. 

  732. 			

    733. 

  733. 			// Oracle has this nasty habit of silently translating empty strings to null

    734. 

  734. 			if ($this->database->getType() == 'oracle') {

    735. 

  735. 				$new_sql = $this->fixOracleEmptyStrings($new_sql, $strings[$number]);	

    736. 

  736. 			}

    737. 

  737. 			

    738. 

  738. 			// Unescape literal semicolons in the queries

    739. 

  739. 			$sql     = preg_replace('#(?<!\\\\)\\\\;#', ';', $sql);

    740. 

  740. 			$new_sql = preg_replace('#(?<!\\\\)\\\\;#', ';', $new_sql);

    741. 

  741. 				

    742. 

  742. 			$extra_statements = array();

    743. 

  743. 			$new_sql = $this->translateCreateTableStatements($new_sql, $extra_statements);

    744. 

  744. 				

    745. 

  745. 			// Put the strings back into the SQL

    746. 

  746. 			foreach ($strings[$number] as $index => $string) {

    747. 

  747. 				$string  = strtr($string, array('\\' => '\\\\', '$' => '\\$'));

    748. 

  748. 				$sql     = preg_replace('#:string_' . $index . '\b#', $string, $sql, 1);

    749. 

  749. 				$new_sql = preg_replace('#:string_' . $index . '\b#', $string, $new_sql, 1);	

    750. 

  750. 			}

    751. 

  751. 			

    752. 

  752. 			$new_sql = $this->translateDateFunctions($new_sql);

    753. 

  753. 			

    754. 

  754. 			if ($sql != $new_sql || $extra_statements) {

    755. 

  755. 				fCore::debug(

    756. 

  756. 					self::compose(

    757. 

  757. 						"Original SQL:%s",

    758. 

  758. 						"\n" . $sql

    759. 

  759. 					),

    760. 

  760. 					$this->debug

    761. 

  761. 				);

    762. 

  762. 				$translated_sql = $new_sql;

    763. 

  763. 				if ($extra_statements) {

    764. 

  764. 					$translated_sql .= '; ' . join('; ', $extra_statements);	

    765. 

  765. 				}

    766. 

  766. 				fCore::debug(

    767. 

  767. 					self::compose(

    768. 

  768. 						"Translated SQL:%s",

    769. 

  769. 						"\n" . $translated_sql

    770. 

  770. 					),

    771. 

  771. 					$this->debug

    772. 

  772. 				);

    773. 

  773. 			}

    774. 

  774. 			

    775. 

  775. 			$output = array_merge($output, array($sql => $new_sql), $extra_statements);	

    776. 

  776. 		}

    777. 

  777. 		

    778. 

  778. 		return $output;

    779. 

  779. 	}

    780. 

  780. 	

    781. 

  781. 	

    782. 

  782. 	/**

    783. 

  783. 	 * Translates basic syntax differences of the current database

    784. 

  784. 	 * 

    785. 

  785. 	 * @param  string $sql  The SQL to translate

    786. 

  786. 	 * @return string  The translated SQL

    787. 

  787. 	 */

    788. 

  788. 	private function translateBasicSyntax($sql)

    789. 

  789. 	{

    790. 

  790. 		if ($this->database->getType() == 'mssql') {

    791. 

  791. 			$regex = array(

    792. 

  792. 				'#\bbegin\s*(?!tran)#i' => 'BEGIN TRANSACTION ',

    793. 

  793. 				'#\brandom\(#i'         => 'RAND(',

    794. 

  794. 				'#\batan2\(#i'          => 'ATN2(',

    795. 

  795. 				'#\bceil\(#i'           => 'CEILING(',

    796. 

  796. 				'#\bln\(#i'             => 'LOG(',

    797. 

  797. 				'#\blength\(#i'         => 'LEN(',

    798. 

  798. 				'#\bsubstr\(#i'			=> 'SUBSTRING(',

    799. 

  799. 				'#\btrue\b#i'           => "'1'",

    800. 

  800. 				'#\bfalse\b#i'          => "'0'",

    801. 

  801. 				'#\|\|#i'               => '+',

    802. 

  802. 				'#\btrim\(\s*((?>[^(),]+|\((?1)\)|\(\))+)\s*\)#i'  => 'RTRIM(LTRIM(\1))',

    803. 

  803. 				'#\bround\(\s*((?>[^(),]+|\((?1)\)|\(\))+)\s*\)#i' => 'round(\1, 0)',

    804. 

  804. 				'#\blog\(\s*((?>[^(),]+|\((?1)(?:,(?1))?\)|\(\))+)\s*,\s*((?>[^(),]+|\((?2)(?:,(?2))?\)|\(\))+)\s*\)#i' => '(LOG(\2)/LOG(\1))'

    805. 

  805. 			);

    806. 

  806. 		

    807. 

  807. 		

    808. 

  808. 		} elseif ($this->database->getType() == 'mysql') {

    809. 

  809. 			$regex = array(

    810. 

  810. 				'#\brandom\(#i' => 'rand(',

    811. 

  811. 				'#\bpi\(\)#i'   => '(pi()+0.0000000000000)'

    812. 

  812. 			);

    813. 

  813. 		

    814. 

  814. 		

    815. 

  815. 		} elseif ($this->database->getType() == 'oracle') {

    816. 

  816. 			$regex = array(

    817. 

  817. 				'#\btrue\b#i'     => '1',

    818. 

  818. 				'#\bfalse\b#i'    => '0',

    819. 

  819. 				'#\bceiling\(#i'  => 'CEIL(',

    820. 

  820. 				'#\brandom\(\)#i' => '(ABS(DBMS_RANDOM.RANDOM)/2147483647)',

    821. 

  821. 				'#\bpi\(\)#i'     => '3.14159265358979',

    822. 

  822. 				'#\bcot\(\s*((?>[^()\s]+|\(((?:[^()]+|\((?2)\))*)\))+)\s*\)#i'     => '(1/TAN(\1))',

    823. 

  823. 				'#\bdegrees\(\s*((?>[^()\s]+|\(((?:[^()]+|\((?2)\))*)\))+)\s*\)#i' => '(\1 * 57.295779513083)',

    824. 

  824. 				'#\bradians\(\s*((?>[^()\s]+|\(((?:[^()]+|\((?2)\))*)\))+)\s*\)#i' => '(\1 * 0.017453292519943)',

    825. 

  825. 				'#(?:\b|^)((?>[^()%\s]+|\(((?:[^()]+|\((?2)\))*)\))+)\s*%\s*((?>[^()\s]+|\(((?:[^()]+|\((?4)\))*)\))+)(?:\b|$)#i'   => 'MOD(\1, \3)',

    826. 

  826. 				'#(?:\b|^)((?>[^()\s]+|\(((?:[^()]+|\((?2)\))*)\))+)\s+LIKE\s+((?>[^()\s]+|\(((?:[^()]+|\((?4)\))*)\))+)(?:\b|$)#i' => 'LOWER(\1) LIKE LOWER(\3)'

    827. 

  827. 			);

    828. 

  828. 		

    829. 

  829. 		

    830. 

  830. 		} elseif ($this->database->getType() == 'postgresql') {

    831. 

  831. 			$regex = array(

    832. 

  832. 				'#\b([\w.]+)\s+(not\s+)?like\b#i' => 'CAST(\1 AS VARCHAR) \2ILIKE',

    833. 

  833. 				'#\blike\b#i'                     => 'ILIKE'

    834. 

  834. 			);

    835. 

  835. 		

    836. 

  836. 		

    837. 

  837. 		} elseif ($this->database->getType() == 'sqlite') {

    838. 

  838. 			

    839. 

  839. 			if ($this->database->getExtension() == 'pdo') {

    840. 

  840. 				$regex = array(

    841. 

  841. 					'#\bcurrent_timestamp\b#i' => "datetime(CURRENT_TIMESTAMP, 'localtime')",

    842. 

  842. 					'#\btrue\b#i'              => "'1'",

    843. 

  843. 					'#\bfalse\b#i'             => "'0'",

    844. 

  844. 					'#\brandom\(\)#i'          => '(ABS(RANDOM())/9223372036854775807)'

    845. 

  845. 				);

    846. 

  846. 			} else {

    847. 

  847. 				$regex = array(

    848. 

  848. 					'#\bcurrent_timestamp\b#i' => "CURRENT_TIMESTAMP()",

    849. 

  849. 					'#\bcurrent_time\b#i'      => "CURRENT_TIME()",

    850. 

  850. 					'#\bcurrent_date\b#i'      => "CURRENT_DATE()",

    851. 

  851. 					'#\btrue\b#i'              => "'1'",

    852. 

  852. 					'#\bfalse\b#i'             => "'0'",

    853. 

  853. 					'#\brandom\(\)#i'          => '(ABS(RANDOM())/9223372036854775807)',

    854. 

  854. 					// SQLite 2 doesn't support CAST, but is also type-less, so we remove it

    855. 

  855. 					'#\bcast\(\s*((?:[^()\s]+|\(((?:[^()]+|\((?2)\))*)\))+)\s+as\s+(?:[^()\s]+|\(((?:[^()]+|\((?3)\))*)\))+\s*\)#i'	=> '\1'

    856. 

  856. 				);

    857. 

  857. 			}

    858. 

  858. 		}

    859. 

  859. 		

    860. 

  860. 		return preg_replace(array_keys($regex), array_values($regex), $sql);

    861. 

  861. 	}

    862. 

  862. 	

    863. 

  863. 	

    864. 

  864. 	/**

    865. 

  865. 	 * Translates the structure of `CREATE TABLE` statements to the database specific syntax

    866. 

  866. 	 * 

    867. 

  867. 	 * @param  string $sql                The SQL to translate

    868. 

  868. 	 * @param  array  &$extra_statements  Any extra SQL statements that need to be added

    869. 

  869. 	 * @return string  The translated SQL

    870. 

  870. 	 */

    871. 

  871. 	private function translateCreateTableStatements($sql, &$extra_statements)

    872. 

  872. 	{

    873. 

  873. 		if (!preg_match('#^\s*CREATE\s+TABLE\s+(\w+)#i', $sql, $table_matches) ) {

    874. 

  874. 			return $sql;	

    875. 

  875. 		}

    876. 

  876. 		

    877. 

  877. 		$table = $table_matches[1];

    878. 

  878. 		

    879. 

  879. 		if ($this->database->getType() == 'mssql') {

    880. 

  880. 			

    881. 

  881. 			// Data type translation

    882. 

  882. 			$regex = array(

    883. 

  883. 				'#\bblob\b#i'                                => 'IMAGE',

    884. 

  884. 				'#\btimestamp\b#i'                           => 'DATETIME',

    885. 

  885. 				'#\btime\b#i'                                => 'DATETIME',

    886. 

  886. 				'#\bdate\b#i'                                => 'DATETIME',

    887. 

  887. 				'#\binteger(?:\(\d+\))?\s+autoincrement\b#i' => 'INTEGER IDENTITY(1,1)',

    888. 

  888. 				'#\bboolean\b#i'                             => 'BIT',

    889. 

  889. 				'#\bvarchar\b#i'                             => 'NVARCHAR',

    890. 

  890. 				'#\bchar\b#i'                                => 'NCHAR',

    891. 

  891. 				'#\btext\b#i'                                => 'NTEXT'

    892. 

  892. 			);

    893. 

  893. 			

    894. 

  894. 			$sql = preg_replace(array_keys($regex), array_values($regex), $sql);	

    895. 

  895. 		

    896. 

  896. 		

    897. 

  897. 		} elseif ($this->database->getType() == 'mysql') {

    898. 

  898. 			

    899. 

  899. 			// Data type translation

    900. 

  900. 			$regex = array(

    901. 

  901. 				'#\btext\b#i'                                => 'MEDIUMTEXT',

    902. 

  902. 				'#\bblob\b#i'                                => 'LONGBLOB',

    903. 

  903. 				'#\btimestamp\b#i'                           => 'DATETIME',

    904. 

  904. 				'#\binteger(?:\(\d+\))?\s+autoincrement\b#i' => 'INTEGER AUTO_INCREMENT'

    905. 

  905. 			);

    906. 

  906. 			

    907. 

  907. 			$sql = preg_replace(array_keys($regex), array_values($regex), $sql);

    908. 

  908. 			

    909. 

  909. 			// Make sure MySQL uses InnoDB tables, translate check constraints to enums and fix column-level foreign key definitions

    910. 

  910. 			preg_match_all('#(?<=,|\()\s*(\w+)\s+(?:[a-z]+)(?:\(\d+\))?(?:\s+unsigned|\s+zerofill|\s+character\s+set\s+[^ ]+|\s+collate\s+[^ ]+|\s+NULL|\s+NOT\s+NULL|(\s+DEFAULT\s+(?:[^, \']*|\'(?:\'\'|[^\']+)*\'))|\s+UNIQUE|\s+PRIMARY\s+KEY|(\s+CHECK\s*\(\w+\s+IN\s+(\(\s*(?:(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\s*,\s*)*\s*(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\))\)))*(\s+REFERENCES\s+\w+\s*\(\s*\w+\s*\)\s*(?:\s+(?:ON\s+DELETE|ON\s+UPDATE)\s+(?:CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL|SET\s+DEFAULT))*(?:\s+(?:DEFERRABLE|NOT\s+DEFERRABLE))?)?\s*(,|\s*(?=\)))#mi', $sql, $matches, PREG_SET_ORDER);

    911. 

  911. 			

    912. 

  912. 			foreach ($matches as $match) {

    913. 

  913. 				// MySQL has the enum data type, so we switch check constraints to that

    914. 

  914. 				if (!empty($match[3])) {

    915. 

  915. 					$replacement = "\n " . $match[1] . ' enum' . $match[4] . $match[2] . $match[5] . $match[6];

    916. 

  916. 					$sql = str_replace($match[0], $replacement, $sql);

    917. 

  917. 					// This allows us to do a str_replace below for converting foreign key syntax

    918. 

  918. 					$match[0] = $replacement;

    919. 

  919. 				}

    920. 

  920. 				

    921. 

  921. 				// Even InnoDB table types don't allow specify foreign key constraints in the column

    922. 

  922. 				// definition, so we move it to its own definition on the next line

    923. 

  923. 				if (!empty($match[5])) {

    924. 

  924. 					$updated_match_0 = str_replace($match[5], ",\nFOREIGN KEY (" . $match[1] . ') ' . $match[5], $match[0]);

    925. 

  925. 					$sql = str_replace($match[0], $updated_match_0, $sql);	

    926. 

  926. 				}

    927. 

  927. 			}

    928. 

  928. 			

    929. 

  929. 			$sql = preg_replace('#\)\s*;?\s*$#D', ')ENGINE=InnoDB', $sql);

    930. 

  930. 		

    931. 

  931. 		

    932. 

  932. 		} elseif ($this->database->getType() == 'oracle') {

    933. 

  933. 		

    934. 

  934. 			// Data type translation

    935. 

  935. 			$regex = array(

    936. 

  936. 				'#\bbigint\b#i'                  => 'INTEGER',

    937. 

  937. 				'#\bboolean\b#i'                 => 'NUMBER(1)',

    938. 

  938. 				'#\btext\b#i'                    => 'CLOB',

    939. 

  939. 				'#\bvarchar\b#i'                 => 'VARCHAR2',

    940. 

  940. 				'#\btime\b#i'                    => 'TIMESTAMP'

    941. 

  941. 			);

    942. 

  942. 			

    943. 

  943. 			$sql = preg_replace(array_keys($regex), array_values($regex), $sql);

    944. 

  944. 			

    945. 

  945. 			// Create sequences and triggers for Oracle

    946. 

  946. 			if (stripos($sql, 'autoincrement') !== FALSE && preg_match('#(?<=,|\()\s*(\w+)\s+(?:[a-z]+)(?:\((?:\d+)\))?.*?\bAUTOINCREMENT\b[^,\)]*(?:,|\s*(?=\)))#mi', $sql, $matches)) {

    947. 

  947. 				$column        = $matches[1];

    948. 

  948. 				

    949. 

  949. 				$table_column  = substr($table . '_' . $column, 0, 26);

    950. 

  950. 				

    951. 

  951. 				$sequence_name = $table_column . '_seq';

    952. 

  952. 				$trigger_name  = $table_column . '_trg';

    953. 

  953. 				

    954. 

  954. 				$sequence = 'CREATE SEQUENCE ' . $sequence_name;

    955. 

  955. 				

    956. 

  956. 				$trigger  = 'CREATE OR REPLACE TRIGGER '. $trigger_name . "\n";

    957. 

  957. 				$trigger .= "BEFORE INSERT ON " . $table . "\n";

    958. 

  958. 				$trigger .= "FOR EACH ROW\n";

    959. 

  959. 				$trigger .= "BEGIN\n";

    960. 

  960. 				$trigger .= "  IF :new." . $column . " IS NULL THEN\n";

    961. 

  961. 				$trigger .= "	SELECT " . $sequence_name . ".nextval INTO :new." . $column . " FROM dual;\n";

    962. 

  962. 				$trigger .= "  END IF;\n";

    963. 

  963. 				$trigger .= "END;";

    964. 

  964. 				

    965. 

  965. 				$extra_statements[] = $sequence;

    966. 

  966. 				$extra_statements[] = $trigger;	

    967. 

  967. 				

    968. 

  968. 				$sql = preg_replace('#\s+autoincrement\b#i', '', $sql);

    969. 

  969. 			}

    970. 

  970. 		

    971. 

  971. 					

    972. 

  972. 		} elseif ($this->database->getType() == 'postgresql') {

    973. 

  973. 			

    974. 

  974. 			// Data type translation

    975. 

  975. 			$regex = array(

    976. 

  976. 				'#\bblob\b#i'                                => 'BYTEA',

    977. 

  977. 				'#\binteger(?:\(\d+\))?\s+autoincrement\b#i' => 'SERIAL'

    978. 

  978. 			);

    979. 

  979. 			

    980. 

  980. 			$sql = preg_replace(array_keys($regex), array_values($regex), $sql);

    981. 

  981. 		

    982. 

  982. 			

    983. 

  983. 		} elseif ($this->database->getType() == 'sqlite') {

    984. 

  984. 		

    985. 

  985. 			// Data type translation

    986. 

  986. 			if ($this->database->getExtension() == 'pdo') {

    987. 

  987. 				$sql = preg_replace('#\binteger(?:\(\d+\))?\s+autoincrement\s+primary\s+key\b#i', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql);

    988. 

  988. 			} else {

    989. 

  989. 				$sql = preg_replace('#\binteger(?:\(\d+\))?\s+autoincrement\s+primary\s+key\b#i', 'INTEGER PRIMARY KEY', $sql);

    990. 

  990. 			}

    991. 

  991. 			

    992. 

  992. 			// Create foreign key triggers for SQLite

    993. 

  993. 			if (stripos($sql, 'REFERENCES') !== FALSE) {

    994. 

  994. 				

    995. 

  995. 				preg_match_all('#(?:(?<=,|\()\s*(\w+)\s+(?:[a-z]+)(?:\((?:\d+)\))?(?:(\s+NOT\s+NULL)|(?:\s+DEFAULT\s+(?:[^, \']*|\'(?:\'\'|[^\']+)*\'))|(?:\s+UNIQUE)|(?:\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)|(?:\s+CHECK\s*\(\w+\s+IN\s+\(\s*(?:(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\s*,\s*)*\s*(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\)\)))*(\s+REFERENCES\s+(\w+)\s*\(\s*(\w+)\s*\)\s*(?:\s+(?:ON\s+DELETE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL)))?(?:\s+(?:ON\s+UPDATE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL)))?)?\s*(?:,|\s*(?=\)))|(?<=,|\()\s*FOREIGN\s+KEY\s*(?:(\w+)|\((\w+)\))\s+REFERENCES\s+(\w+)\s*\(\s*(\w+)\s*\)\s*(?:\s+(?:ON\s+DELETE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL)))?(?:\s+(?:ON\s+UPDATE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL)))?\s*(?:,|\s*(?=\))))#mi', $sql, $matches, PREG_SET_ORDER);

    996. 

  996. 				

    997. 

  997. 				$not_null_columns = array();

    998. 

  998. 				foreach ($matches as $match) {

    999. 

  999. 					// Find all of the not null columns

    1000. 

  1000. 					if (!empty($match[2])) {

    1001. 

  1001. 						$not_null_columns[] = $match[1];

    1002. 

  1002. 					}

    1003. 

  1003. 					

    1004. 

  1004. 					// If neither of these fields is matched, we don't have a foreign key

    1005. 

  1005. 					if (empty($match[3]) && empty($match[10])) {

    1006. 

  1006. 						continue;

    1007. 

  1007. 					}

    1008. 

  1008. 					

    1009. 

  1009. 					// 8 and 9 will be an either/or set, so homogenize

    1010. 

  1010. 					if (empty($match[9]) && !empty($match[8])) { $match[9] = $match[8]; }

    1011. 

  1011. 					

    1012. 

  1012. 					// Handle column level foreign key inserts/updates

    1013. 

  1013. 					if ($match[1]) {

    1014. 

  1014. 						$this->createSQLiteForeignKeyTriggerValidInsertUpdate($extra_statements, $table, $match[1], $match[4], $match[5], in_array($match[1], $not_null_columns));

    1015. 

  1015. 					

    1016. 

  1016. 					// Handle table level foreign key inserts/update

    1017. 

  1017. 					} elseif ($match[9]) {

    1018. 

  1018. 						$this->createSQLiteForeignKeyTriggerValidInsertUpdate($extra_statements, $table, $match[9], $match[10], $match[11], in_array($match[9], $not_null_columns));

    1019. 

  1019. 					}

    1020. 

  1020. 					

    1021. 

  1021. 					// If none of these fields is matched, we don't have on delete or on update clauses

    1022. 

  1022. 					if (empty($match[6]) && empty($match[7]) && empty($match[12]) && empty($match[13])) {

    1023. 

  1023. 						continue;

    1024. 

  1024. 					}

    1025. 

  1025. 					

    1026. 

  1026. 					// Handle column level foreign key delete/update clauses

    1027. 

  1027. 					if (!empty($match[3])) {

    1028. 

  1028. 						if ($match[6]) {

    1029. 

  1029. 							$this->createSQLiteForeignKeyTriggerOnDelete($extra_statements, $table, $match[1], $match[4], $match[5], $match[6]);

    1030. 

  1030. 						}

    1031. 

  1031. 						if (!empty($match[7])) {

    1032. 

  1032. 							$this->createSQLiteForeignKeyTriggerOnUpdate($extra_statements, $table, $match[1], $match[4], $match[5], $match[7]);

    1033. 

  1033. 						}

    1034. 

  1034. 						continue;

    1035. 

  1035. 					}

    1036. 

  1036. 					

    1037. 

  1037. 					// Handle table level foreign key delete/update clauses

    1038. 

  1038. 					if ($match[12]) {

    1039. 

  1039. 						$this->createSQLiteForeignKeyTriggerOnDelete($extra_statements, $table, $match[9], $match[10], $match[11], $match[12]);

    1040. 

  1040. 					}

    1041. 

  1041. 					if ($match[13]) {

    1042. 

  1042. 						$this->createSQLiteForeignKeyTriggerOnUpdate($extra_statements, $table, $match[9], $match[10], $match[11], $match[13]);

    1043. 

  1043. 					}

    1044. 

  1044. 				}

    1045. 

  1045. 			}

    1046. 

  1046. 		}

    1047. 

  1047. 		

    1048. 

  1048. 		

    1049. 

  1049. 		return $sql;

    1050. 

  1050. 	}

    1051. 

  1051. 	

    1052. 

  1052. 	

    1053. 

  1053. 	/**

    1054. 

  1054. 	 * Translates custom date/time functions to the current database

    1055. 

  1055. 	 * 

    1056. 

  1056. 	 * @param  string $sql  The SQL to translate

    1057. 

  1057. 	 * @return string  The translated SQL

    1058. 

  1058. 	 */

    1059. 

  1059. 	private function translateDateFunctions($sql)

    1060. 

  1060. 	{

    1061. 

  1061. 		// Handle diff_seconds()

    1062. 

  1062. 		// diff_seconds() accepts two parameters, the two dates to get the difference between

    1063. 

  1063. 		// Regex matches:

    1064. 

  1064. 		// 1 - The first date

    1065. 

  1065. 		// 2 - The second date

    1066. 

  1066. 		preg_match_all("#diff_seconds\(((?>(?:[^()',]+|'[^']+')|\((?1)(?:,(?1))?\)|\(\))+)\s*,\s*((?>(?:[^()',]+|'[^']+')|\((?2)(?:,(?2))?\)|\(\))+)\)#ims", $sql, $diff_matches, PREG_SET_ORDER);

    1067. 

  1067. 		foreach ($diff_matches as $match) {

    1068. 

  1068. 			

    1069. 

  1069. 			// SQLite

    1070. 

  1070. 			if ($this->database->getType() == 'sqlite') {

    1071. 

  1071. 				$sql = str_replace($match[0], "round((julianday(" . $match[2] . ") - julianday('1970-01-01 00:00:00')) * 86400) - round((julianday(" . $match[1] . ") - julianday('1970-01-01 00:00:00')) * 86400)", $sql);

    1072. 

  1072. 			

    1073. 

  1073. 			// PostgreSQL

    1074. 

  1074. 			} elseif ($this->database->getType() == 'postgresql') {

    1075. 

  1075. 				$sql = str_replace($match[0], "extract(EPOCH FROM age(" . $match[2] . ", " . $match[1] . "))", $sql);

    1076. 

  1076. 			

    1077. 

  1077. 			// Oracle

    1078. 

  1078. 			} elseif ($this->database->getType() == 'oracle') {

    1079. 

  1079. 				if (substr($match[1], 0, 1) == "'") {

    1080. 

  1080. 					$match[1] = 'CAST(' . $match[1] . " AS TIMESTAMP)";	

    1081. 

  1081. 				}

    1082. 

  1082. 				if (substr($match[2], 0, 1) == "'") {

    1083. 

  1083. 					$match[2] = 'CAST(' . $match[2] . " AS TIMESTAMP)";

    1084. 

  1084. 				}

    1085. 

  1085. 				$sql = str_replace($match[0], "((TO_NUMBER(TO_CHAR(" . $match[2] . ", 'J')) - TO_NUMBER(TO_CHAR(" . $match[1] . ", 'J'))) * 86400) + (TO_NUMBER(TO_CHAR(" . $match[2] . ", 'SSSSS')) - TO_NUMBER(TO_CHAR(" . $match[1] . ", 'SSSSS')))", $sql);

    1086. 

  1086. 			

    1087. 

  1087. 			// MySQL

    1088. 

  1088. 			} elseif ($this->database->getType() == 'mysql') {

    1089. 

  1089. 				$sql = str_replace($match[0], "(UNIX_TIMESTAMP(" . $match[2] . ") - UNIX_TIMESTAMP(" . $match[1] . "))", $sql);

    1090. 

  1090. 				

    1091. 

  1091. 			// MSSQL

    1092. 

  1092. 			} elseif ($this->database->getType() == 'mssql') {

    1093. 

  1093. 				$sql = str_replace($match[0], "DATEDIFF(second, " . $match[1] . ", " . $match[2] . ")", $sql);

    1094. 

  1094. 			}

    1095. 

  1095. 		}

    1096. 

  1096. 		

    1097. 

  1097. 		// Handle add_interval()

    1098. 

  1098. 		// add_interval() accepts two parameters, the date to modify and the interval to add

    1099. 

  1099. 		// Regex matches:

    1100. 

  1100. 		// 1 - The first parameter

    1101. 

  1101. 		// 2 - The second parameter

    1102. 

  1102. 		preg_match_all("#add_interval\(((?>(?:[^()',]+|'[^']+')|\((?1)(?:,(?1))?\)|\(\))+)\s*,\s*'([^']+)'\s*\)#i", $sql, $add_matches, PREG_SET_ORDER);

    1103. 

  1103. 		foreach ($add_matches as $match) {

    1104. 

  1104. 			

    1105. 

  1105. 			// SQLite

    1106. 

  1106. 			if ($this->database->getType() == 'sqlite') {

    1107. 

  1107. 				

    1108. 

  1108. 				// Regex matches:

    1109. 

  1109. 				// 0 - The adjustment in the form: +/- number units

    1110. 

  1110. 				preg_match_all("#(?:\\+|\\-)\\d+\\s+(?:year|month|day|hour|minute|second)(?:s)?#i", $match[2], $individual_matches);

    1111. 

  1111. 				$strings = "'" . join("', '", $individual_matches[0]) . "'";

    1112. 

  1112. 				$sql = str_replace($match[0], "datetime(" . $match[1] . ", " . $strings . ")", $sql);

    1113. 

  1113. 			

    1114. 

  1114. 			// PostgreSQL

    1115. 

  1115. 			} elseif ($this->database->getType() == 'postgresql') {

    1116. 

  1116. 				if (substr($match[1], 0, 1) == "'") {

    1117. 

  1117. 					if (preg_match('#^\'\d{4}-\d{2}-\d{2}\'$#', $match[1])) {

    1118. 

  1118. 						$match[1] = 'DATE ' . $match[1];

    1119. 

  1119. 					} elseif (preg_match('#^\'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\'$#', $match[1])) {

    1120. 

  1120. 						$match[1] = 'TIMESTAMP ' . $match[1];

    1121. 

  1121. 					} elseif (preg_match('#^\'\d{2}:\d{2}:\d{2}\'$#', $match[1])) {

    1122. 

  1122. 						$match[1] = 'TIME ' . $match[1];

    1123. 

  1123. 					}	

    1124. 

  1124. 				}

    1125. 

  1125. 				$sql = str_replace($match[0], "(" . $match[1] . " + INTERVAL '" . $match[2] . "')", $sql);

    1126. 

  1126. 			

    1127. 

  1127. 			// MySQL and Oracle

    1128. 

  1128. 			} elseif ($this->database->getType() == 'mysql' || $this->database->getType() == 'oracle') {

    1129. 

  1129. 				

    1130. 

  1130. 				// Regex matches:

    1131. 

  1131. 				// 1 - The sign, +/-

    1132. 

  1132. 				// 2 - The number

    1133. 

  1133. 				// 3 - The units, hour, minute, etc

    1134. 

  1134. 				preg_match_all("#(\\+|\\-)(\\d+)\\s+(year|month|day|hour|minute|second)(?:s)?#i", $match[2], $individual_matches, PREG_SET_ORDER);

    1135. 

  1135. 				$intervals_string = '';

    1136. 

  1136. 				foreach ($individual_matches as $individual_match) {

    1137. 

  1137. 					$intervals_string .= ' ' . $individual_match[1] . " INTERVAL '" . $individual_match[2] . "' " . strtoupper($individual_match[3]);

    1138. 

  1138. 				}

    1139. 

  1139. 				

    1140. 

  1140. 				if ($this->database->getType() == 'oracle' && substr($match[1], 0, 1) == "'") {

    1141. 

  1141. 					if (preg_match('#^\'\d{4}-\d{2}-\d{2}\'$#', $match[1])) {

    1142. 

  1142. 						$match[1] = 'CAST(' . $match[1] . ' AS DATE)';

    1143. 

  1143. 					} elseif (preg_match('#^\'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\'$#', $match[1])) {

    1144. 

  1144. 						$match[1] = 'CAST(' . $match[1] . ' AS TIMESTAMP)';

    1145. 

  1145. 					}	

    1146. 

  1146. 				}

    1147. 

  1147. 				

    1148. 

  1148. 				$sql = str_replace($match[0], "(" . $match[1] . $intervals_string . ")", $sql);

    1149. 

  1149. 			

    1150. 

  1150. 			// MSSQL

    1151. 

  1151. 			} elseif ($this->database->getType() == 'mssql') {

    1152. 

  1152. 				

    1153. 

  1153. 				// Regex matches:

    1154. 

  1154. 				// 1 - The sign, +/-

    1155. 

  1155. 				// 2 - The number

    1156. 

  1156. 				// 3 - The units, hour, minute, etc

    1157. 

  1157. 				preg_match_all("#(\\+|\\-)(\\d+)\\s+(year|month|day|hour|minute|second)(?:s)?#i", $match[2], $individual_matches, PREG_SET_ORDER);

    1158. 

  1158. 				$date_add_string = '';

    1159. 

  1159. 				$stack = 0;

    1160. 

  1160. 				foreach ($individual_matches as $individual_match) {

    1161. 

  1161. 					$stack++;

    1162. 

  1162. 					$date_add_string .= 'DATEADD(' . $individual_match[3] . ', ' . $individual_match[1] . $individual_match[2] . ', ';

    1163. 

  1163. 				}

    1164. 

  1164. 				$sql = str_replace($match[0], $date_add_string . $match[1] . str_pad('', $stack, ')'), $sql);

    1165. 

  1165. 				

    1166. 

  1166. 			}

    1167. 

  1167. 		}

    1168. 

  1168. 		

    1169. 

  1169. 		return $sql;

    1170. 

  1170. 	}

    1171. 

  1171. 	

    1172. 

  1172. 	

    1173. 

  1173. 	/**

    1174. 

  1174. 	 * Translates `LIMIT x OFFSET x` to `ROW_NUMBER() OVER (ORDER BY)` syntax

    1175. 

  1175. 	 * 

    1176. 

  1176. 	 * @param  string $sql  The SQL to translate

    1177. 

  1177. 	 * @return string  The translated SQL

    1178. 

  1178. 	 */

    1179. 

  1179. 	private function translateLimitOffsetToRowNumber($sql)

    1180. 

  1180. 	{

    1181. 

  1181. 		// Regex details:

    1182. 

  1182. 		// 1 - The SELECT clause

    1183. 

  1183. 		// 2 - () recursion handler

    1184. 

  1184. 		// 3 - FROM clause

    1185. 

  1185. 		// 4 - () recursion handler

    1186. 

  1186. 		// 5 - ORDER BY clause

    1187. 

  1187. 		// 6 - () recursion handler

    1188. 

  1188. 		// 7 - LIMIT number

    1189. 

  1189. 		// 8 - OFFSET number

    1190. 

  1190. 		preg_match_all('#select((?:(?:(?!\sfrom\s)[^()])+|\(((?:[^()]+|\((?2)\))*)\))*\s)(from(?:(?:(?!\slimit\s|\sorder by\s)[^()])+|\(((?:[^()]+|\((?4)\))*)\))*\s)(order by(?:(?:(?!\slimit\s)[^()])+|\(((?:[^()]+|\((?6)\))*)\))*\s)?limit\s+(\d+)(?:\s+offset\s+(\d+))?#i', $sql, $matches, PREG_SET_ORDER);

    1191. 

  1191. 		

    1192. 

  1192. 		foreach ($matches as $match) {

    1193. 

  1193. 			if ($this->database->getType() == 'mssql') {

    1194. 

  1194. 				

    1195. 

  1195. 				// This means we have an offset clause

    1196. 

  1196. 				if (!empty($match[8])) {

    1197. 

  1197. 					

    1198. 

  1198. 					if ($match[5] === '') {

    1199. 

  1199. 						$match[5] = "ORDER BY rand(1) ASC";	

    1200. 

  1200. 					}

    1201. 

  1201. 					

    1202. 

  1202. 					$select  = $match[1] . ', ROW_NUMBER() OVER (';

    1203. 

  1203. 					$select .= $match[5];

    1204. 

  1204. 					$select .= ') AS flourish__row__num ';

    1205. 

  1205. 					$select .= $match[3];

    1206. 

  1206. 					

    1207. 

  1207. 					$replacement = 'SELECT * FROM (SELECT ' . trim($match[1]) . ', ROW_NUMBER() OVER (' . $match[5] . ') AS flourish__row__num ' . $match[3] . ') AS original_query WHERE flourish__row__num > ' . $match[8] . ' AND flourish__row__num <= ' . ($match[7] + $match[8]) . ' ORDER BY flourish__row__num';

    1208. 

  1208. 				

    1209. 

  1209. 				// Otherwise we just have a limit

    1210. 

  1210. 				} else {

    1211. 

  1211. 					$replacement = 'SELECT TOP ' . $match[7] . ' ' . trim($match[1] . $match[3] . $match[5]);

    1212. 

  1212. 				}

    1213. 

  1213. 				

    1214. 

  1214. 			// While Oracle has the row_number() construct, the rownum pseudo-column is better

    1215. 

  1215. 			} elseif ($this->database->getType() == 'oracle') {

    1216. 

  1216. 				

    1217. 

  1217. 				 // This means we have an offset clause

    1218. 

  1218. 				if (!empty($match[8])) {

    1219. 

  1219. 					

    1220. 

  1220. 					$replacement = 'SELECT * FROM (SELECT flourish__sq.*, rownum flourish__row__num FROM (SELECT' . $match[1] . $match[3] . $match[5] . ') flourish__sq WHERE rownum <= ' . ($match[7] + $match[8]) . ') WHERE flourish__row__num > ' . $match[8];

    1221. 

  1221. 				

    1222. 

  1222. 				// Otherwise we just have a limit

    1223. 

  1223. 				} else {

    1224. 

  1224. 					$replacement = 'SELECT * FROM (SELECT' . $match[1] . $match[3] . $match[5] . ') WHERE rownum <= ' . $match[7];

    1225. 

  1225. 				}

    1226. 

  1226. 					

    1227. 

  1227. 			}

    1228. 

  1228. 			

    1229. 

  1229. 			$sql = str_replace($match[0], $replacement, $sql);

    1230. 

  1230. 		}

    1231. 

  1231. 		

    1232. 

  1232. 		return $sql;

    1233. 

  1233. 	}

    1234. 

  1234. }

    1235. 

  1235. 

    1236. 

  1236. 

    1237. 

  1237. 

    1238. 

  1238. /**

    1239. 

  1239.  * Copyright (c) 2007-2009 Will Bond <will@flourishlib.com>

    1240. 

  1240.  * 

    1241. 

  1241.  * Permission is hereby granted, free of charge, to any person obtaining a copy

    1242. 

  1242.  * of this software and associated documentation files (the "Software"), to deal

    1243. 

  1243.  * in the Software without restriction, including without limitation the rights

    1244. 

  1244.  * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

    1245. 

  1245.  * copies of the Software, and to permit persons to whom the Software is

    1246. 

  1246.  * furnished to do so, subject to the following conditions:

    1247. 

  1247.  * 

    1248. 

  1248.  * The above copyright notice and this permission notice shall be included in

    1249. 

  1249.  * all copies or substantial portions of the Software.

    1250. 

  1250.  * 

    1251. 

  1251.  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

    1252. 

  1252.  * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

    1253. 

  1253.  * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

    1254. 

  1254.  * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

    1255. 

  1255.  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

    1256. 

  1256.  * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN

    1257. 

  1257.  * THE SOFTWARE.

    1258. 

  1258.  */
    1259.