root

Changeset 744

Show
Ignore:
Timestamp:
12/18/09 13:08:26 (9 months ago)
Author:
wbond
Message:

Fixed ticket #351 - translated queries on MSSQL servers with sub-selects containing joins now work properly

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • fSQLTranslation.php

    r738 r744 Hide Line Numbers
    1010 * @link       http://flourishlib.com/fSQLTranslation 
    1111 *  
    12  * @version    1.0.0b14 
     12 * @version    1.0.0b15 
     13 * @changes    1.0.0b15  Fixed a bug with MSSQL national character conversion when running a SQL statement with a sub-select containing joins [wb, 2009-12-18] 
    1314 * @changes    1.0.0b14  Changed PostgreSQL to cast columns in LOWER() calls to VARCHAR to allow UUID columns (which are treated as a VARCHAR by fSchema) to work with default primary key ordering in fRecordSet [wb, 2009-12-16] 
    1415 * @changes    1.0.0b13  Added a parameter to ::enableCaching() to provide a key token that will allow cached values to be shared between multiple databases with the same schema [wb, 2009-10-28] 
    …… 
    6869         
    6970        // Turn comma joins into cross joins 
    70         if (preg_match('#^(?:"?\w+"?(?:\s+(?:as\s+)?(?:"?\w+"?))?)(?:\s*,\s*(?:"?\w+"?(?:\s+(?:as\s+)?(?:"?\w+"?))?))*$#isD', $sql)) { 
     71        if (preg_match('#^(?:"?:?\w+"?(?:\s+(?:as\s+)?(?:"?\w+"?))?)(?:\s*,\s*(?:"?\w+"?(?:\s+(?:as\s+)?(?:"?\w+"?))?))*$#isD', $sql)) { 
    7172            $sql = str_replace(',', ' CROSS JOIN ', $sql); 
    7273        } 
    …… 
    7677        foreach ($tables as $table) { 
    7778            // This grabs the table name and alias (if there is one) 
    78             preg_match('#^\s*(["\w.]+|\(((?:[^()]+|\((?2)\))*)\))(?:\s+(?:as\s+)?((?!ON|USING)["\w.]+))?\s*(?:(?:ON|USING)\s+(.*))?\s*$#im', $table, $parts); 
     79            preg_match('#^\s*([":\w.]+|\(((?:[^()]+|\((?2)\))*)\))(?:\s+(?:as\s+)?((?!ON|USING)["\w.]+))?\s*(?:(?:ON|USING)\s+(.*))?\s*$#im', $table, $parts); 
    7980             
    8081            $table_name  = $parts[1]; 
    …… 
    536537            $from_clause   = trim($select[3]); 
    537538             
    538             // This recursively fixes sub-selects 
    539             if (preg_match('#\bselect\b#', $from_clause)) { 
    540                 $from_clause = $this->fixMSSQLNationalColumns($from_clause);     
     539            $sub_selects = array(); 
     540            if (preg_match_all('#\((\s*SELECT\s+((?:[^()]+|\((?2)\))*))\)#i', $from_clause, $from_matches)) { 
     541                $sub_selects = $from_matches[0]; 
     542                foreach ($sub_selects as $i => $sub_select) { 
     543                    $from_clause = preg_replace('#' . preg_quote($sub_select, '#') . '#', ':sub_select_' . $i, $from_clause, 1); 
     544                } 
    541545            } 
    542546             
    …… 
    673677            } 
    674678             
    675             $replace = preg_replace('#\bselect\s+' . preg_quote($select_clause, '#') . '#i', 'SELECT ' . strtr(join(', ', array_merge($selections, $additions)), array('\\' => '\\\\', '$' => '\\$')), $select); 
     679            foreach ($sub_selects as $i => $sub_select) { 
     680                $sql = preg_replace( 
     681                    '#:sub_select_' . $i . '\b#', 
     682                    strtr( 
     683                        $this->fixMSSQLNationalColumns($sub_select), 
     684                        array('\\' => '\\\\', '$' => '\\$') 
     685                    ), 
     686                    $sql, 
     687                    1 
     688                );   
     689            } 
     690             
     691            $replace = preg_replace( 
     692                '#\bselect\s+' . preg_quote($select_clause, '#') . '#i', 
     693                'SELECT ' . strtr( 
     694                    join(', ', array_merge($selections, $additions)), 
     695                    array('\\' => '\\\\', '$' => '\\$') 
     696                ), 
     697                $select 
     698            ); 
    676699            $sql = str_replace($select, $replace, $sql);     
    677700        }