root/fSQLTranslation.php

Revision 265, 36.3 kB (checked in by wbond, 2 years ago)

Added class constants to all classes with static methods to allow for nicer callback syntax

LineHide Line Numbers
1 <?php
2 /**
3  * Takes a subset of SQL from MySQL, PostgreSQL, SQLite and MSSQL and translates into the various dialects allowing for cross-database code
4  *
5  * @copyright  Copyright (c) 2007-2008 William Bond
6  * @author     William Bond [wb] <will@flourishlib.com>
7  * @license    http://flourishlib.com/license
8  *
9  * @package    Flourish
10  * @link       http://flourishlib.com/fSQLTranslation
11  *
12  * @internal
13  *
14  * @version    1.0.0b
15  * @changes    1.0.0b  The initial implementation [wb, 2007-09-25]
16  */
17 class fSQLTranslation
18 {
19     const sqliteCotangent    = 'fSQLTranslation::sqliteCotangent';
20     const sqliteLogBaseFirst = 'fSQLTranslation::sqliteLogBaseFirst';
21     const sqliteSign         = 'fSQLTranslation::sqliteSign';
22    
23    
24     /**
25     * Takes a Flourish SQL SELECT query and parses it into clauses.
26     *
27     * The select statement must be of the format:
28     *
29     * SELECT [ table_name. | alias. ]*
30     * FROM table [ AS alias ] [ [ INNER | OUTER ] [ LEFT | RIGHT ] JOIN other_table ON condition | , ] ...
31     * [ WHERE condition [ , condition ]... ]
32     * [ GROUP BY conditions ]
33     * [ HAVING conditions ]
34     * [ ORDER BY [ column | expression ] [ ASC | DESC ] [ , [ column | expression ] [ ASC | DESC ] ] ... ]
35     * [ LIMIT integer [ OFFSET integer ] ]
36     *
37     * The returned array will contain the following keys, which may have a NULL or non-empty string value:
38     *  - 'SELECT'
39     *  - 'FROM'
40     *  - 'WHERE'
41     *  - 'GROUP BY'
42     *  - 'HAVING'
43     *  - 'ORDER BY'
44     *  - 'LIMIT'
45     *
46     * @param  string $sql  The SQL to parse
47     * @return array  The various clauses of the SELECT statement (see method descript for details)
48     */
49     static private function parseSelectSQL($sql)
50     {
51         // Split the strings out of the sql so parsing doesn't get messed up by quoted values
52         preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\]+)*')|(?:[^']+)#", $sql, $matches);
53        
54         $possible_clauses = array('SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT');
55         $found_clauses    = array();
56         foreach ($possible_clauses as $possible_clause) {
57             $found_clauses[$possible_clause] = NULL;
58         }
59        
60         $current_clause = 0;
61        
62         foreach ($matches[0] as $match) {
63             // This is a quoted string value, don't do anything to it
64             if ($match[0] == "'") {
65                 $found_clauses[$possible_clauses[$current_clause]] .= $match;
66            
67             // Non-quoted strings should be checked for clause markers
68             } else {
69                
70                 // Look to see if a new clause starts in this string
71                 $i = 1;
72                 while ($current_clause+$i < sizeof($possible_clauses)) {
73                     // If the next clause is found in this string
74                     if (stripos($match, $possible_clauses[$current_clause+$i]) !== FALSE) {
75                         list($before, $after) = preg_split('#\s*' . $possible_clauses[$current_clause+$i] . '\s*#i', $match);
76                         $found_clauses[$possible_clauses[$current_clause]] .= preg_replace('#\s*' . $possible_clauses[$current_clause] . '\s*#i', '', $before);
77                         $match = $after;
78                         $current_clause = $current_clause + $i;
79                         $i = 0;
80                     }
81                     $i++;
82                 }
83                
84                 // Otherwise just add on to the current clause
85                 if (!empty($match)) {
86                     $found_clauses[$possible_clauses[$current_clause]] .= preg_replace('#\s*' . $possible_clauses[$current_clause] . '\s*#i', '', $match);
87                 }
88             }
89         }
90        
91         return $found_clauses;
92     }
93    
94        
95     /**
96     * Takes the FROM clause from {@link parseSelectSQL()} and returns all of the tables and each one's alias
97     *
98     * @param  string $clause  The SQL clause to parse
99     * @return array  The tables in the from clause, with the table alias being the key and value being the name
100     */
101     static private function parseTableAliases($sql)
102     {
103         $aliases = array();
104        
105         preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\]+)*')|(?:[^']+)#", $sql, $matches);
106        
107         $sql = '';
108         // Replace strings with two single quotes
109         foreach ($matches[0] as $match) {
110             if ($match[0] == "'") {
111                 $match = "''";
112             }
113             $sql .= $match;
114         }
115        
116         // Turn comma joins into cross joins
117         if (preg_match('#^(?:\w+(?:\s+(?:as\s+)?(?:\w+))?)(?:\s*,\s*(?:\w+(?:\s+(?:as\s+)?(?:\w+))?))*$#is', $sql)) {
118             $sql = str_replace(',', ' CROSS JOIN ', $sql);
119         }
120        
121         // Error out if we can't figure out the join structure
122         if (!preg_match('#^(?:\w+(?:\s+(?:as\s+)?(?:\w+))?)(?:\s+(?:(?:CROSS|INNER|OUTER|LEFT|RIGHT)?\s+)*JOIN\s+(?:\w+(?:\s+(?:as\s+)?(?:\w+))?)(?:\s+ON\s+.*)?)*$#is', $sql)) {
123             fCore::toss(
124                 'fProgrammerException',
125                 fGrammar::compose(
126                     'Unable to parse FROM clause, does not appears to be in comma style or join style'
127                 )
128             );
129         }
130        
131         $tables = preg_split('#\s+((?:(?:CROSS|INNER|OUTER|LEFT|RIGHT)?\s+)*?JOIN)\s+#i', $sql);
132        
133         foreach ($tables as $table) {
134             // This grabs the table name and alias (if there is one)
135             preg_match('#\s*([\w.]+)(?:\s+(?:as\s+)?((?!ON)[\w.]+))?\s*(?:ON\s+(.*))?#im', $table, $parts);
136            
137             $table_name  = $parts[1];
138             $table_alias = (isset($parts[2])) ? $parts[2] : $parts[1];
139            
140             $aliases[$table_alias] = $table_name;
141         }
142        
143         return $aliases;
144     }
145    
146    
147     /**
148     * Callback for custom SQLite function; calculates the cotangent of a number
149     *
150     * @internal
151      *
152     * @param  numeric $x  The number to calculate the cotangent of
153     * @return numeric  The contangent of $x
154     */
155     static public function sqliteCotangent($x)
156     {
157         return 1/tan($x);
158     }
159    
160    
161     /**
162     * Callback for custom SQLite function; calculates the log to a specific base of a number
163     *
164     * @internal
165      *
166     * @param  integer $base  The base for the log calculation
167     * @param  numeric $num   The number to calculate the logarithm of
168     * @return numeric  The logarithm of $num to $base
169     */
170     static public function sqliteLogBaseFirst($base, $num)
171     {
172         return log($num, $base);
173     }
174    
175    
176     /**
177     * Callback for custom SQLite function; returns the sign of the number
178     *
179     * @internal
180      *
181     * @param  numeric $x  The number to change the sign of
182     * @return numeric  -1 if a negative sign, 0 if 0, 1 if positive sign
183     */
184     static public function sqliteSign($x)
185     {
186         if ($x == 0) {
187             return 0;
188         }
189         if ($x > 0) {
190             return 1;
191         }
192         return -1;
193     }
194    
195    
196     /**
197     * The database connection resource or PDO object
198     *
199     * @var mixed
200     */
201     private $connection;
202    
203     /**
204     * The fDatabase instance
205     *
206     * @var fDatabase
207     */
208     private $database;
209    
210     /**
211     * If debugging is enabled
212     *
213     * @var boolean
214     */
215     private $debug;
216    
217    
218     /**
219     * Sets up the class and creates functions for SQLite databases
220     *
221     * @internal
222      *
223     * @param  fDatabase $database    The database being translated for
224     * @param  mixed     $connection  The connection resource or PDO object
225     * @return fSQLTranslation
226     */
227     public function __construct(fDatabase $database, $connection)
228     {
229         if (!is_resource($connection) && !is_object($connection)) {
230             fCore::toss(
231                 'fProgrammerException',
232                 fGrammar::compose(
233                     'The connection specified, %s, is not a valid database connection',
234                     fCore::dump($connection)
235                 )
236             );
237         }
238        
239         $this->connection = $connection;
240         $this->database   = $database;
241        
242         if ($database->getType() == 'sqlite') {
243             $this->createSQLiteFunctions();
244         }
245     }
246    
247    
248     /**
249     * Creates a trigger for SQLite that handles an on delete clause
250     *
251     * @param  string $referencing_table   The table that contains the foreign key
252     * @param  string $referencing_column  The column the foriegn key constraint is on
253     * @param  string $referenced_table    The table the foreign key references
254     * @param  string $referenced_column   The column the foreign key references
255     * @param  string $delete_clause       What is to be done on a delete
256     * @return string  The trigger
257     */
258     private function createSQLiteForeignKeyTriggerOnDelete($referencing_table, $referencing_column, $referenced_table, $referenced_column, $delete_clause)
259     {
260         switch (strtolower($delete_clause)) {
261             case 'no action':
262             case 'restrict':
263                 $sql = "\nCREATE TRIGGER fkd_res_" . $referencing_table . "_" . $referencing_column . "
264                              BEFORE DELETE ON " . $referenced_table . "
265                              FOR EACH ROW BEGIN
266                                  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 . "\"')
267                                  WHERE (SELECT " . $referencing_column . " FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_table . ") IS NOT NULL;
268                              END;";
269                 break;
270            
271             case 'set null':
272                 $sql = "\nCREATE TRIGGER fkd_nul_" . $referencing_table . "_" . $referencing_column . "
273                              BEFORE DELETE ON " . $referenced_table . "
274                              FOR EACH ROW BEGIN
275                                  UPDATE " . $referencing_table . " SET " . $referencing_column . " = NULL WHERE " . $referencing_column . " = OLD." . $referenced_column . ";
276                              END;";
277                 break;
278                
279             case 'cascade':
280                 $sql = "\nCREATE TRIGGER fkd_cas_" . $referencing_table . "_" . $referencing_column . "
281                              BEFORE DELETE ON " . $referenced_table . "
282                              FOR EACH ROW BEGIN
283                                  DELETE FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_column . ";
284                              END;";
285                 break;
286         }
287         return $sql;
288     }
289    
290    
291     /**
292     * Creates a trigger for SQLite that handles an on update clause
293     *
294     * @param  string $referencing_table   The table that contains the foreign key
295     * @param  string $referencing_column  The column the foriegn key constraint is on
296     * @param  string $referenced_table    The table the foreign key references
297     * @param  string $referenced_column   The column the foreign key references
298     * @param  string $update_clause       What is to be done on an update
299     * @return string  The trigger
300     */
301     private function createSQLiteForeignKeyTriggerOnUpdate($referencing_table, $referencing_column, $referenced_table, $referenced_column, $update_clause)
302     {
303         switch (strtolower($update_clause)) {
304             case 'no action':
305             case 'restrict':
306                 $sql = "\nCREATE TRIGGER fku_res_" . $referencing_table . "_" . $referencing_column . "
307                              BEFORE UPDATE ON " . $referenced_table . "
308                              FOR EACH ROW BEGIN
309                                  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 . "\"')
310                                  WHERE (SELECT " . $referencing_column . " FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_column . ") IS NOT NULL;
311                              END;";
312                 break;
313            
314             case 'set null':
315                 $sql = "\nCREATE TRIGGER fku_nul_" . $referencing_table . "_" . $referencing_column . "
316                              BEFORE UPDATE ON " . $referenced_table . "
317                              FOR EACH ROW BEGIN
318                                  UPDATE " . $referencing_table . " SET " . $referencing_column . " = NULL WHERE OLD." . $referenced_column . " <> NEW." . $referenced_column . " AND " . $referencing_column . " = OLD." . $referenced_column . ";
319                              END;";
320                 break;
321                
322             case 'cascade':
323                 $sql = "\nCREATE TRIGGER fku_cas_" . $referencing_table . "_" . $referencing_column . "
324                              BEFORE UPDATE ON " . $referenced_table . "
325                              FOR EACH ROW BEGIN
326                                  UPDATE " . $referencing_table . " SET " . $referencing_column . " = NEW." . $referenced_column . " WHERE OLD." . $referenced_column . " <> NEW." . $referenced_column . " AND " . $referencing_column . " = OLD." . $referenced_column . ";
327                              END;";
328                 break;
329         }
330         return $sql;
331     }
332    
333    
334     /**
335     * Creates a trigger for SQLite that prevents inserting or updating to values the violate a foreign key constraint
336     *
337     * @param  string  $referencing_table     The table that contains the foreign key
338     * @param  string  $referencing_column    The column the foriegn key constraint is on
339     * @param  string  $referenced_table      The table the foreign key references
340     * @param  string  $referenced_column     The column the foreign key references
341     * @param  boolean $referencing_not_null  If the referencing columns is set to not null
342     * @return string  The trigger
343     */
344     private function createSQLiteForeignKeyTriggerValidInsertUpdate($referencing_table, $referencing_column, $referenced_table, $referenced_column, $referencing_not_null)
345     {
346         // Verify key on inserts
347         $sql  = "\nCREATE TRIGGER fki_ver_" . $referencing_table . "_" . $referencing_column . "
348                       BEFORE INSERT ON " . $referencing_table . "
349                       FOR EACH ROW BEGIN
350                           SELECT RAISE(ROLLBACK, 'insert on table \"" . $referencing_table . "\" violates foreign key constraint on column \"" . $referencing_column . "\"')
351                               WHERE ";
352         if (!$referencing_not_null) {
353             $sql .= "NEW." . $referencing_column . " IS NOT NULL AND ";
354         }
355         $sql .= " (SELECT " . $referenced_column . " FROM " . $referenced_table . " WHERE " . $referenced_column . " = NEW." . $referencing_column . ") IS NULL;
356                       END;";
357                    
358         // Verify key on updates
359         $sql .= "\nCREATE TRIGGER fku_ver_" . $referencing_table . "_" . $referencing_column . "
360                       BEFORE UPDATE ON " . $referencing_table . "
361                       FOR EACH ROW BEGIN
362                           SELECT RAISE(ROLLBACK, 'update on table \"" . $referencing_table . "\" violates foreign key constraint on column \"" . $referencing_column . "\"')
363                               WHERE ";
364         if (!$referencing_not_null) {
365             $sql .= "NEW." . $referencing_column . " IS NOT NULL AND ";
366         }
367         $sql .= " (SELECT " . $referenced_column . " FROM " . $referenced_table . " WHERE " . $referenced_column . " = NEW." . $referencing_column . ") IS NULL;
368                       END;";
369        
370         return $sql;
371     }
372    
373    
374     /**
375     * Adds a number of math functions to SQLite that MSSQL, MySQL and PostgreSQL have by default
376     *
377     * @return void
378     */
379     private function createSQLiteFunctions()
380     {
381         $function = array();
382         $functions[] = array('acos',     'acos',                                         1);
383         $functions[] = array('asin',     'asin',                                         1);
384         $functions[] = array('atan',     'atan',                                         1);
385         $functions[] = array('atan2',    'atan2',                                        2);
386         $functions[] = array('ceil',     'ceil',                                         1);
387         $functions[] = array('ceiling',  'ceil',                                         1);
388         $functions[] = array('cos',      'cos',                                          1);
389         $functions[] = array('cot',      array('fSQLTranslation', 'sqliteCotangent'),    1);
390         $functions[] = array('degrees',  'rad2deg',                                      1);
391         $functions[] = array('exp',      'exp',                                          1);
392         $functions[] = array('floor',    'floor',                                        1);
393         $functions[] = array('ln',       'log',                                          1);
394         $functions[] = array('log',      array('fSQLTranslation', 'sqliteLogBaseFirst'), 2);
395         $functions[] = array('pi',       'pi',                                           1);
396         $functions[] = array('power',    'pow',                                          1);
397         $functions[] = array('radians',  'deg2rad',                                      1);
398         $functions[] = array('sign',     array('fSQLTranslation', 'sqliteSign'),         1);
399         $functions[] = array('sqrt',     'sqrt',                                         1);
400         $functions[] = array('sin',      'sin',                                          1);
401         $functions[] = array('tan',      'tan',                                          1);
402        
403         foreach ($functions as $function) {
404             if ($this->database->getExtension() == 'pdo') {
405                 $this->connection->sqliteCreateFunction($function[0], $function[1], $function[2]);
406             } else {
407                 sqlite_create_function($this->connection, $function[0], $function[1], $function[2]);
408             }
409         }
410     }
411    
412    
413     /**
414     * Sets if debug messages should be shown
415     *
416     * @param  boolean $flag  If debugging messages should be shown
417     * @return void
418     */
419     public function enableDebugging($flag)
420     {
421         $this->debug = (boolean) $flag;
422     }
423    
424    
425     /**
426     * Fixes pulling unicode data out of national data type MSSQL columns
427     *
428     * @param  string $sql  The SQL to fix
429     * @return string  The fixed SQL
430     */
431     private function fixMSSQLNationalColumns($sql)
432     {
433         if (!preg_match_all('#^\s*(select.*)$|\(\s*(select(?:\s*(?:[^()\']+|\'(?:\'\'|\\\\\'|\\\\[^\']|[^\'\\\\]+)*\'|\((?2)\)|\(\))+\s*))\s*\)\s*(?= union)|\s+union(?:\s+all)?\s+\(\s*(select(?:\s*(?:[^()\']+|\'(?:\'\'|\\\\\'|\\\\[^\']|[^\'\\\\]+)*\'|\((?3)\)|\(\))+\s*))\s*\)#i', $sql, $matches)) {
434             return $sql;
435         }
436        
437         static $national_columns = NULL;
438         static $national_types   = NULL;
439        
440         if ($national_columns === NULL) {
441             $result = $this->database->query(
442                 "SELECT
443                         c.table_name  AS 'table',                       
444                         c.column_name AS 'column',
445                         c.data_type   AS 'type'
446                     FROM
447                         INFORMATION_SCHEMA.COLUMNS AS c
448                     WHERE
449                         (c.data_type = 'nvarchar' OR
450                          c.data_type = 'ntext' OR
451                          c.data_type = 'nchar') AND
452                         c.table_catalog = 'flourish'
453                     ORDER BY
454                         lower(c.table_name) ASC,
455                         lower(c.column_name) ASC"
456             );
457            
458             $national_columns = array();
459            
460             foreach ($result as $row) {
461                 if (!isset($national_columns[$row['table']])) {
462                     $national_columns[$row['table']] = array();   
463                     $national_types[$row['table']]   = array();
464                 }   
465                 $national_columns[$row['table']][] = $row['column'];
466                 $national_types[$row['table']][$row['column']] = $row['type'];
467             }
468         }
469        
470         $selects = array_merge(
471             array_filter($matches[1]),
472             array_filter($matches[2]),
473             array_filter($matches[3])
474         );
475        
476         $additions = array();
477        
478         foreach ($selects as $select) {
479             $clauses       = self::parseSelectSQL($select);
480             $table_aliases = self::parseTableAliases($clauses['FROM']);
481            
482             preg_match_all('#([^,()\']+|\'(?>\'\'|\\\\\'|\\\\[^\']|[^\'\\\\]+)*\'|\((?:(?1)|,)*\)|\(\))+#i', $clauses['SELECT'], $selections);
483             $selections    = array_map('trim', $selections[0]);
484             $to_fix        = array();
485            
486             foreach ($selections as $selection) {
487                 // We just skip CASE statements since we can't really do those reliably
488                 if (preg_match('#^case#i', $selection)) {
489                     continue;   
490                 }
491                
492                 if (preg_match('#(\w+)\.\*#i', $selection, $match)) {
493                     $table = $table_aliases[$match[1]];
494                     if (empty($national_columns[$table])) {
495                         continue;   
496                     }
497                     if (!isset($to_fix[$table])) {
498                         $to_fix[$table] = array();   
499                     }
500                     $to_fix[$table] = array_merge($to_fix[$table], $national_columns[$table]);
501                        
502                 } elseif (preg_match('#\*#', $selection, $match)) {
503                     foreach ($table_aliases as $alias => $table) {
504                         if (empty($national_columns[$table])) {
505                             continue;   
506                         }
507                         if (!isset($to_fix[$table])) {
508                             $to_fix[$table] = array();   
509                         }
510                         $to_fix[$table] = array_merge($to_fix[$table], $national_columns[$table]);         
511                     }
512                    
513                 } elseif (preg_match('#^(?:(\w+)\.(\w+)|((?:min|max|trim|rtrim|ltrim|substring|replace)\((\w+)\.(\w+).*?\)))(?:\s+as\s+(\w+))?$#i', $selection, $match)) {
514                     $table = $match[1] . ((isset($match[4])) ? $match[4] : '');
515                     $table = $table_aliases[$table];
516                    
517                     $column = $match[2] . ((isset($match[5])) ? $match[5] : '');;
518                    
519                     if (empty($national_columns[$table]) || !in_array($column, $national_columns[$table])) {
520                         continue;   
521                     }
522                    
523                     if (!isset($to_fix[$table])) {
524                         $to_fix[$table] = array();   
525                     }
526                    
527                     // Handle column aliasing
528                     if (!empty($match[6])) {
529                         $column = array('column' => $column, 'alias' => $match[6]);   
530                     }
531                    
532                     if (!empty($match[3])) {
533                         if (!is_array($column)) {
534                             $column = array('column' => $column);
535                         }   
536                         $column['expression'] = $match[3];
537                     }
538                    
539                     $to_fix[$table] = array_merge($to_fix[$table], array($column));
540                
541                 // Match unqualified column names
542                 } elseif (preg_match('#^(?:(\w+)|((?:min|max|trim|rtrim|ltrim|substring|replace)\((\w+).*?\)))(?:\s+as\s+(\w+))?$#i', $selection, $match)) {
543                     $column = $match[1] . ((isset($match[3])) ? $match[3] : '');
544                     foreach ($table_aliases as $alias => $table) {
545                         if (empty($national_columns[$table])) {
546                             continue;   
547                         }
548                         if (!in_array($column, $national_columns[$table])) {
549                             continue;
550                         }
551                         if (!isset($to_fix[$table])) {
552                             $to_fix[$table] = array();   
553                         }
554                        
555                         // Handle column aliasing
556                         if (!empty($match[4])) {
557                             $column = array('column' => $column, 'alias' => $match[4]);   
558                         }
559                        
560                         if (!empty($match[2])) {
561                             if (!is_array($column)) {
562                                 $column = array('column' => $column);
563                             }   
564                             $column['expression'] = $match[2];
565                         }
566                        
567                         $to_fix[$table] = array_merge($to_fix[$table], array($column));         
568                     }
569                 }
570             }
571            
572             $reverse_table_aliases = array_flip($table_aliases);
573             foreach ($to_fix as $table => $columns) {
574                 $columns = array_unique($columns);
575                 $alias   = $reverse_table_aliases[$table];
576                 foreach ($columns as $column) {
577                     if (is_array($column)) {
578                         if (isset($column['alias'])) {
579                             $as = ' AS __flourish_mssqln_' . $column['alias'];
580                         } else {
581                             $as = ' AS __flourish_mssqln_' . $column['column'];     
582                         }
583                         if (isset($column['expression'])) {
584                             $expression = $column['expression'];   
585                         } else {
586                             $expression = $alias . '.' . $column['column'];
587                         }
588                         $column = $column['column'];
589                     } else {
590                         $as     = ' AS __flourish_mssqln_' . $column;
591                         $expression = $alias . '.' . $column;
592                     }
593                     if ($national_types[$table][$column] == 'ntext') {
594                         $cast = 'CAST(' . $expression . ' AS IMAGE)';   
595                     } else {
596                         $cast = 'CAST(' . $expression . ' AS VARBINARY(8000))';
597                     }
598                     $additions[] = $cast . $as;
599                 }       
600             }
601            
602             $replace = preg_replace('#\bselect\s+' . preg_quote($clauses['SELECT'], '#') . '#i', 'SELECT ' . join(', ', array_merge($selections, $additions)), $select);
603             $sql = str_replace($select, $replace, $sql);   
604         }
605        
606         return $sql;
607     }
608    
609    
610     /**
611     * Translates FlourishSQL into the dialect for the current database
612     *
613     * @internal
614      *
615     * @param  string $sql  The SQL to translate
616     * @return string  The translated SQL
617     */
618     public function translate($sql)
619     {
620         // Separate the SQL from quoted values
621         preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\]+)*')|(?:[^']+)#", $sql, $matches);
622        
623         $new_sql = '';
624         foreach ($matches[0] as $match) {
625             // This is a quoted string value, don't do anything to it
626             if ($match[0] == "'") {
627                 $new_sql .= $match;
628            
629             // Raw SQL should be run through the fixes
630             } else {
631                 $new_sql .= $this->translateBasicSyntax($match);
632             }
633         }
634        
635         // Fix stuff that includes sql and quotes values
636         $new_sql = $this->translateDateFunctions($new_sql);
637         $new_sql = $this->translateComplicatedSyntax($new_sql);
638         $new_sql = $this->translateCreateTableStatements($new_sql);
639        
640         if ($this->database->getType() == 'mssql') {
641             $new_sql = $this->fixMSSQLNationalColumns($new_sql);   
642         }
643        
644         if ($sql != $new_sql) {
645             fCore::debug(
646                 fGrammar::compose(
647                     "Original SQL:%s",
648                     "\n" .$sql
649                 ),
650                 $this->debug
651             );
652             fCore::debug(
653                 fGrammar::compose(
654                     "Translated SQL:%s",
655                     "\n" . $new_sql
656                 ),
657                 $this->debug
658             );
659         }
660        
661         return $new_sql;
662     }
663    
664    
665     /**
666     * Translates basic syntax differences of the current database
667     *
668     * @param  string $sql  The SQL to translate
669     * @return string  The translated SQL
670     */
671     private function translateBasicSyntax($sql)
672     {
673         // SQLite fixes
674         if ($this->database->getType() == 'sqlite') {
675            
676             if ($this->database->getType() == 'sqlite' && $this->database->getExtension() == 'pdo') {
677                 static $regex_sqlite = array(
678                     '#\binteger\s+autoincrement\s+primary\s+key\b#i'  => 'INTEGER PRIMARY KEY AUTOINCREMENT',
679                     '#\bcurrent_timestamp\b#i'                        => "datetime(CURRENT_TIMESTAMP, 'localtime')",
680                     '#\btrue\b#i'                                     => "'1'",
681                     '#\bfalse\b#i'                                    => "'0'"
682                 );
683             } else {
684                 static $regex_sqlite = array(
685                     '#\binteger\s+autoincrement\s+primary\s+key\b#i'  => 'INTEGER PRIMARY KEY',
686                     '#\bcurrent_timestamp\b#i'       => "datetime(CURRENT_TIMESTAMP, 'localtime')",
687                     '#\btrue\b#i'                    => "'1'",
688                     '#\bfalse\b#i'                   => "'0'"
689                 );
690             }
691            
692             return preg_replace(array_keys($regex_sqlite), array_values($regex_sqlite), $sql);
693         }
694        
695         // PostgreSQL fixes
696         if ($this->database->getType() == 'postgresql') {
697             static $regex_postgresql = array(
698                 '#\blike\b#i'                    => 'ILIKE',
699                 '#\bblob\b#i'                    => 'bytea',
700                 '#\binteger\s+autoincrement\b#i' => 'serial'
701             );
702            
703             return preg_replace(array_keys($regex_postgresql), array_values($regex_postgresql), $sql);
704         }
705        
706         // MySQL fixes
707         if ($this->database->getType() == 'mysql') {
708             static $regex_mysql = array(
709                 '#\brandom\(#i'                  => 'rand(',
710                 '#\btext\b#i'                    => 'MEDIUMTEXT',
711                 '#\bblob\b#i'                    => 'LONGBLOB',
712                 '#\btimestamp\b#i'               => 'DATETIME',
713                 '#\binteger\s+autoincrement\b#i' => 'INTEGER AUTO_INCREMENT'
714             );
715        
716             return preg_replace(array_keys($regex_mysql), array_values($regex_mysql), $sql);
717         }
718        
719         // MSSQL fixes
720         if ($this->database->getType() == 'mssql') {
721             static $regex_mssql = array(
722                 '#\bbegin\s*(?!tran)#i'          => 'BEGIN TRANSACTION ',
723                 '#\brandom\(#i'                  => 'RAND(',
724                 '#\batan2\(#i'                   => 'ATN2(',
725                 '#\bceil\(#i'                    => 'CEILING(',
726                 '#\bln\(#i'                      => 'LOG(',
727                 '#\blength\(#i'                  => 'LEN(',
728                 '#\bsubstr\(#i'                     => 'SUBSTRING(',
729                 '#\bblob\b#i'                    => 'IMAGE',
730                 '#\btimestamp\b#i'               => 'DATETIME',
731                 '#\btime\b#i'                    => 'DATETIME',
732                 '#\bdate\b#i'                    => 'DATETIME',
733                 '#\binteger\s+autoincrement\b#i' => 'INTEGER IDENTITY(1,1)',
734                 '#\bboolean\b#i'                 => 'BIT',
735                 '#\btrue\b#i'                    => "'1'",
736                 '#\bfalse\b#i'                   => "'0'",
737                 '#\|\|#i'                      => '+'
738             );
739        
740             return preg_replace(array_keys($regex_mssql), array_values($regex_mssql), $sql);
741         }
742     }
743    
744    
745     /**
746     * Translates more complicated inconsistencies
747     *
748     * @param  string $sql  The SQL to translate
749     * @return string  The translated SQL
750     */
751     private function translateComplicatedSyntax($sql)
752     {
753         if ($this->database->getType() == 'mssql') {
754            
755             $sql = $this->translateLimitOffsetToRowNumber($sql);
756            
757             static $regex_mssql = array(
758                 // These wrap multiple mssql functions to accomplish another function
759                 '#\blog\(\s*((?>[^()\',]+|\'[^\']*\'|\((?1)(?:,(?1))?\)|\(\))+)\s*,\s*((?>[^()\',]+|\'[^\']*\'|\((?2)(?:,(?2))?\)|\(\))+)\s*\)#i' => '(LOG(\1)/LOG(\2))',
760                 '#\btrim\(\s*((?>[^()\',]+|\'(?:\'\'|\\\\\'|\\\\[^\']|[^\'\\\\]+)*\'|\((?1)\)|\(\))+)\s*\)#i' => 'RTRIM(LTRIM(\1))'
761             );
762        
763             $sql = preg_replace(array_keys($regex_mssql), array_values($regex_mssql), $sql);
764            
765             if (preg_match('#select(\s*(?:[^()\']+|\'(?>\'\'|\\\\\'|\\\\[^\']|[^\'\\\\]+)*\'|\((?1)\)|\(\))+\s*)\s+limit\s+(\d+)#i', $sql, $match)) {
766                 $sql = str_replace($match[0], 'SELECT TOP ' . $match[2] . $match[1], $sql);
767             }
768         }
769        
770         return $sql;
771     }
772    
773    
774     /**
775     * Translates the structure of create table statements to the database specific syntax
776     *
777     * @param  string $sql  The SQL to translate
778     * @return string  The translated SQL
779     */
780     private function translateCreateTableStatements($sql)
781     {
782         // Make sure MySQL uses InnoDB tables
783         if ($this->database->getType() == 'mysql' && stripos($sql, 'CREATE TABLE') !== FALSE) {
784             preg_match_all('#(?<=,|\()\s*(\w+)\s+(?:[a-z]+)(?:\(\d+\))?(?:(\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);
785            
786             foreach ($matches as $match) {
787                 if (!empty($match[6])) {
788                     $sql = str_replace($match[0], "\n " . $match[1] . ' enum' . $match[7] . $match[2] . $match[3] . $match[4] . $match[5] . $match[8] . ', ', $sql);
789                 }
790             }
791            
792             $sql = preg_replace('#\)\s*;?\s*$#', ')ENGINE=InnoDB', $sql);
793        
794        
795         // Create foreign key triggers for SQLite
796         } elseif ($this->database->getType() == 'sqlite' && preg_match('#CREATE\s+TABLE\s+(\w+)#i', $sql, $table_matches) !== FALSE && stripos($sql, 'REFERENCES') !== FALSE) {
797            
798             $referencing_table = $table_matches[1];
799            
800             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);
801            
802             // Make sure we have a semicolon so we can add triggers
803             $sql = trim($sql);
804             if (substr($sql, -1) != ';') {
805                 $sql .= ';';
806             }
807            
808             $not_null_columns = array();
809             foreach ($matches as $match) {
810                 // Find all of the not null columns
811                 if (!empty($match[2])) {
812                     $not_null_columns[] = $match[1];
813                 }
814                
815                 // If neither of these fields is matched, we don't have a foreign key
816                 if (empty($match[3]) && empty($match[10])) {
817                     continue;
818                 }
819                
820                 // 8 and 9 will be an either/or set, so homogenize
821                 if (empty($match[9])) { $match[9] = $match[8]; }
822                
823                 // Handle column level foreign key inserts/updates
824                 if ($match[1]) {
825                     $sql .= $this->createSQLiteForeignKeyTriggerValidInsertUpdate($referencing_table, $match[1], $match[4], $match[5], in_array($match[1], $not_null_columns));
826                
827                 // Handle table level foreign key inserts/update
828                 } elseif ($match[9]) {
829                     $sql .= $this->createSQLiteForeignKeyTriggerValidInsertUpdate($referencing_table, $match[9], $match[10], $match[11], in_array($match[9], $not_null_columns));
830                 }
831                
832                 // If none of these fields is matched, we don't have on delete or on update clauses
833                 if (empty($match[6]) && empty($match[7]) && empty($match[12]) && empty($match[13])) {
834                     continue;
835                 }
836                
837                 // Handle column level foreign key delete/update clauses
838                 if (!empty($match[3])) {
839                     if ($match[6]) {
840                         $sql .= $this->createSQLiteForeignKeyTriggerOnDelete($referencing_table, $match[1], $match[4], $match[5], $match[6]);
841                     }
842                     if ($match[7]) {
843                         $sql .= $this->createSQLiteForeignKeyTriggerOnUpdate($referencing_table, $match[1], $match[4], $match[5], $match[7]);
844                     }
845                     continue;
846                 }
847                
848                 // Handle table level foreign key delete/update clauses
849                 if ($match[12]) {
850                     $sql .= $this->createSQLiteForeignKeyTriggerOnDelete($referencing_table, $match[9], $match[10], $match[11], $match[12]);
851                 }
852                 if ($match[13]) {
853                     $sql .= $this->createSQLiteForeignKeyTriggerOnUpdate($referencing_table, $match[9], $match[10], $match[11], $match[13]);
854                 }
855             }
856         }
857        
858         return $sql;
859     }
860    
861    
862     /**
863     * Translates custom date/time functions to the current database
864     *
865     * @param  string $sql  The SQL to translate
866     * @return string  The translated SQL
867     */
868     private function translateDateFunctions($sql)
869     {
870         // fix diff_seconds()
871         preg_match_all("#diff_seconds\\(((?>(?:[^()',]+|'[^']+')|\\((?1)(?:,(?1))?\\)|\\(\\))+)\\s*,\\s*((?>(?:[^()',]+|'[^']+')|\\((?2)(?:,(?2))?\\)|\\(\\))+)\\)#ims", $sql, $diff_matches, PREG_SET_ORDER);
872         foreach ($diff_matches as $match) {
873            
874             // SQLite
875             if ($this->database->getType() == 'sqlite') {
876                 $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);
877            
878             // PostgreSQL
879             } elseif ($this->database->getType() == 'postgresql') {
880                 $sql = str_replace($match[0], "extract(EPOCH FROM age(" . $match[2] . ", " . $match[1] . "))", $sql);
881            
882             // MySQL
883             } elseif ($this->database->getType() == 'mysql') {
884                 $sql = str_replace($match[0], "(UNIX_TIMESTAMP(" . $match[2] . ") - UNIX_TIMESTAMP(" . $match[1] . "))", $sql);
885                
886             // MSSQL
887             } elseif ($this->database->getType() == 'mssql') {
888                 $sql = str_replace($match[0], "DATEDIFF(second, " . $match[1] . ", " . $match[2] . ")", $sql);
889             }
890         }
891        
892         // fix add_interval()
893         preg_match_all("#add_interval\\(((?>(?:[^()',]+|'[^']+')|\\((?1)(?:,(?1))?\\)|\\(\\))+)\\s*,\\s*'([^']+)'\\s*\\)#i", $sql, $add_matches, PREG_SET_ORDER);
894         foreach ($add_matches as $match) {
895            
896             // SQLite
897             if ($this->database->getType() == 'sqlite') {
898                 preg_match_all("#(?:\\+|\\-)\\d+\\s+(?:year|month|day|hour|minute|second)(?:s)?#i", $match[2], $individual_matches);
899                 $strings = "'" . join("', '", $individual_matches[0]) . "'";
900                 $sql = str_replace($match[0], "datetime(" . $match[1] . ", " . $strings . ")", $sql);
901            
902             // PostgreSQL
903             } elseif ($this->database->getType() == 'postgresql') {
904                 $sql = str_replace($match[0], "(" . $match[1] . " + INTERVAL '" . $match[2] . "')", $sql);
905            
906             // MySQL
907             } elseif ($this->database->getType() == 'mysql') {
908                 preg_match_all("#(\\+|\\-)(\\d+)\\s+(year|month|day|hour|minute|second)(?:s)?#i", $match[2], $individual_matches, PREG_SET_ORDER);
909                 $intervals_string = '';
910                 foreach ($individual_matches as $individual_match) {
911                     $intervals_string .= ' ' . $individual_match[1] . ' INTERVAL ' . $individual_match[2] . ' ' . strtoupper($individual_match[3]);
912                 }
913                 $sql = str_replace($match[0], "(" . $match[1] . $intervals_string . ")", $sql);
914            
915             // MSSQL
916             } elseif ($this->database->getType() == 'mssql') {
917                 preg_match_all("#(\\+|\\-)(\\d+)\\s+(year|month|day|hour|minute|second)(?:s)?#i", $match[2], $individual_matches, PREG_SET_ORDER);
918                 $date_add_string = '';
919                 $stack = 0;
920                 foreach ($individual_matches as $individual_match) {
921                     $stack++;
922                     $date_add_string .= 'DATEADD(' . $individual_match[3] . ', ' . $individual_match[1] . $individual_match[2] . ', ';
923                 }
924                 $sql = str_replace($match[0], $date_add_string . $match[1] . str_pad('', $stack, ')'), $sql);
925             }
926         }
927        
928         return $sql;
929     }
930    
931    
932     /**
933     * Translates limit x offset x to row_number() over (order by) syntax
934     *
935     * @param  string $sql  The SQL to translate
936     * @return string  The translated SQL
937     */
938     private function translateLimitOffsetToRowNumber($sql)
939     {
940         preg_match_all('#((select(?:\s*(?:[^()\']+|\'(?:\'\'|\\\\\'|\\\\[^\']|[^\'\\\\]+)*\'|\((?1)\)|\(\))+\s*))\s+limit\s+(\d+)\s+offset\s+(\d+))#i', $sql, $matches, PREG_SET_ORDER);
941        
942         foreach ($matches as $match) {
943             $clauses = self::parseSelectSQL($match[1]);
944            
945             if ($clauses['ORDER BY'] == NULL) {
946                 $clauses['ORDER BY'] = '1 ASC';
947             }
948            
949             $replacement = '';
950             foreach ($clauses as $key => $value) {
951                 if (empty($value)) {
952                     continue;
953                 }
954                
955                 if ($key == 'SELECT') {
956                     $replacement .= 'SELECT ' . $value . ', ROW_NUMBER() OVER (';
957                     $replacement .= 'ORDER BY ' . $clauses['ORDER BY'];
958                     $replacement .= ') AS __flourish_limit_offset_row_num ';
959                 } elseif ($key == 'LIMIT' || $key == 'ORDER BY') {
960                     // Skip this clause
961                 } else {
962                     $replacement .= $key . ' ' . $value . ' ';
963                 }
964             }
965            
966             $replacement = 'SELECT * FROM (' . trim($replacement) . ') AS original_query WHERE __flourish_limit_offset_row_num > ' . $match[4] . ' AND __flourish_limit_offset_row_num <= ' . ($match[3] + $match[4]) . ' ORDER BY __flourish_limit_offset_row_num';
967            
968             $sql = str_replace($match[1], $replacement, $sql);
969         }
970        
971         return $sql;
972     }
973 }
974  
975  
976  
977 /**
978  * Copyright (c) 2007-2008 William Bond <will@flourishlib.com>
979  *
980  * Permission is hereby granted, free of charge, to any person obtaining a copy
981  * of this software and associated documentation files (the "Software"), to deal
982  * in the Software without restriction, including without limitation the rights
983  * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
984  * copies of the Software, and to permit persons to whom the Software is
985  * furnished to do so, subject to the following conditions:
986  *
987  * The above copyright notice and this permission notice shall be included in
988  * all copies or substantial portions of the Software.
989  *
990  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
991  * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
992  * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
993  * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
994  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
995  * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
996  * THE SOFTWARE.
997  */