Rows and Columns

// Take a provided query and rewrite the syntax in SA format
function rewrite_query($query) {          
  global $wpdb;	
 
  //Replace the column delimiter " with "
  $query = preg_replace( "/"/", '"', $query );
 
  //Rewrite INSERT/UPDATE to INSERT/UPDATE INTO tbl ON EXISTING SKIP  
  $pattern = "/INSERT\s*IGNORE\s*INTO\s*/i";
  $pattern2 = "/\)\s*VALUES\s*\(/i";
  $pattern3 = "/UPDATE\s*IGNORE\s*/i";
  if( preg_match( $pattern, $query, $insmatches ) ) {
      $query = preg_replace( $pattern, 'INSERT INTO ', $query );
      $query = preg_replace( $pattern2, ') ON EXISTING SKIP VALUES (', $query );
      //Replace all empty strings with DEFAULT
	    $query = preg_replace( "/''/", "DEFAULT", $query );
  }else if( preg_match( $pattern3, $query, $insmatches ) ) {
      $query = preg_replace( $pattern3, 'UPDATE ', $query );
  }elseif( preg_match( "/SHOW TABLES\s*;/i", $query) > 0 ) {
      //Replace SHOW TABLES with SELECT table_name FROM SYSTABLE
      $query = "SELECT table_name FROM SYSTABLE;";
  } elseif( preg_match( "/FOUND_ROWS\s*\(\s*\)/i", $query) > 0 ) {
       //Replace FOUND_ROWS() with @@rowcount  - it is an imperfect solution, but shouldn't break anything
       $query = "SELECT @@rowcount FROM DUMMY;";
  } elseif( preg_match( "/DESCRIBE\s*(\w+)\s*;/i", $query, $tablename ) > 0 ) {
       //Replace DESCRIBE <table> with SELECT statement    
       $query = "SELECT sc.column_name as \"Field\",  
               IF( sd.domain_name = 'char' OR  sd.domain_name = 'varchar' ) THEN 
                   sd.domain_name ||'(' || cast( sc.width as char(5) ) || ')' 
               ELSE sd.domain_name 
               ENDIF as \"Type\", 
               IF( sc.nulls = 'N' ) THEN 'NO' ELSE 'YES' ENDIF as \"Null\",
               IF( sc.pkey = 'Y' ) THEN 'PRI' ELSE 'NO' ENDIF as \"Key\",
               sc.\"default\" as \"Default\"
             FROM SYSCOLUMN sc, SYSDOMAIN sd, SYSTABLE st 
             WHERE sc.domain_id = sd.domain_id
               AND st.table_id = sc.table_id
               AND st.table_name = '".$tablename[1]."';";		
    } elseif( preg_match( "/SHOW\s*INDEX\s*FROM\s*(\w+)\s*;/i", $query, $tablename ) > 0 ) {
       //Replace SHOW INDEX FROM <table> with SELECT statement    
       $query = "SELECT st.table_name as \"Table\",
               IF( si.\"unique\" = 'Y' ) THEN 0 ELSE 1 ENDIF as \"Non-unique\",
               si.index_name as \"key_name\",
               sic.\"sequence\"+1 as \"Seq_in_index\",
               sc.column_name as \"Column_name\",
               IF( sic.\"order\" = 'A' ) THEN 'A' ELSE NULL ENDIF as \"Collation\",
               0 as \"Cardinality\",
               NULL as \"Sub_part\",
               NULL as \"Packed\",
               IF( si.\"unique\" = 'U' ) THEN 'YES' ELSE '' ENDIF as \"NULL\",
               '' as \"Index_type\",
               si.\"remarks\" as \"Comment\"
             FROM SYSINDEX si, SYSTABLE  st, SYSIXCOL sic, SYSCOLUMN sc 
             WHERE 
               si.table_id = st.\"table_id\"
               AND sic.index_id = si.\"index_id\" 
               AND sic.table_id = si.\"table_id\"
               AND sc.table_id = sic.\"table_id\"
               AND sc.column_id = sic.\"column_id\"
               AND st.\"table_name\" = '".$tablename[1]."';";				
   } elseif( preg_match( "/^ALTER\s*TABLE\s*(\w+)\s*/i", $query ) > 0 ) {
       //Currently, this has only been tested on an upgrade from 1.5 to 2.3
        if( preg_match( "/ALTER\s*TABLE\s*(\w+)\s*ADD\s*COLUMN\s*/i", $query, $alter ) > 0 ) {
           //Remove COLUMN keyword from ALTER TABLE <table> ADD COLUMN
           $query = preg_replace( '/\s*COLUMN\s*/i', " ", $query);		
       }elseif( preg_match( "/ALTER\s*TABLE\s*(\w+)\s*CHANGE\s*COLUMN\s*/i", $query, $alter ) > 0 ) {
           //Remove COLUMN keyword from ALTER TABLE CHANGE COLUMN and change CHANGE to MODIFY
           $query = preg_replace( '/\s*CHANGE\s*COLUMN\s*\w+\s*/i', " MODIFY ", $query);
       }elseif( preg_match( "/ALTER\s*TABLE\s*(\w+)\s*ALTER\s*COLUMN\s*(\w+)\s*SET\s*/i", $query, $alter ) > 0 ) {
           //Remove COLUMN keyword from ALTER TABLE CHANGE COLUMN and change CHANGE to MODIFY
           $query = preg_replace( '/\s*ALTER\s*COLUMN\s*\w+\s*SET\s*/i', " MODIFY ".$alter[2]." ", $query);
       }
 
       //Replace default date with corrected date
      	$query = preg_replace( "/0000-00-00 00:00:00/", "1970-01-01 00:00:00", $query );
 
       //Remove size specs for bigint and int columns
       $query = preg_replace( "/int\(\d+\)\s*/i", "int ", $query );		
 
       //Replace enum( a, b, c ) with check constraint check( @col IN( a, b, c ) )
      	if( preg_match( "/\s*enum\s*\(/i", $query, $alter ) > 0 ) {
           $query = preg_replace( "/\)/i", " ))", $query );
           $query = preg_replace( "/\s*enum\s*\(/i", " varchar(128) CHECK( @col IN (", $query );
       }
 
       //Replace longtext/text with long varchar
       $query = preg_replace( "/\s*longtext\s*/i", " long varchar ", $query );		
       $query = preg_replace( "/\s*text\s*/i", " long varchar ", $query );		
 
       //Replace auto_increment with default autoincrement
       $query = preg_replace( "/\s*auto_increment\s*/i", " default autoincrement ", $query );
 
       //Fix poor preg_replace in upgrade.php for DEFAULT CHECK constraints
       //if( preg_match( "/\s*MODIFY\s*\w+\s*DEFAULT\s*'\w+'\s*CHECK\(/i", $query, $alter ) > 0 ) {
       //    $query = $query.') )';
       //}
 
       //Replace bigint unsigned with unsigned bigint
       $query = preg_replace( "/\s*bigint\s*unsigned\s*/i", " unsigned bigint ", $query );
 
   } elseif( preg_match( "/^ALTER\s*TABLE\s*(\w+)\s*ADD\s*KEY\s*(\w+)/i", $query, $alter ) > 0 ) {
       //REPLACE ALTER TABLE ADD KEY with CREATE INDEX
      $query = preg_replace( "/ALTER\s*TABLE\s*\w+\s*ADD\s*KEY\s*\w+/i", "CREATE INDEX ".$alter[2]." ON ".$alter[1], $query );
  } else {
       //Find LIMIT clause integer arguments and replace with TOP N
       //  START AT m
      //MySQL syntax for LIMIT is:
       //  LIMIT {[offset,] row_count | row_count OFFSET offset}]
       //Note that the LIMIT clause is at the end of the query
 
       // LIMIT followed by an integer, possibly followed by a comma and another integer
       $pattern = "/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)/i";  
       $matched = preg_match( $pattern, $query, $limitmatches );
    	if( $matched == 1 ) {
           //Remove the LIMIT statement, replace offset 0 with 1
           $query = preg_replace( $pattern, '', $query);
           if( count( $limitmatches ) == 5 ) {
               if( $limitmatches[1] == '0' ) {
                   $limitmatches[1] = '1';
               }
               $replacement = 'SELECT TOP '.$limitmatches[4].' START AT '.$limitmatches[1].' ';
          } else {
       	      $replacement = 'SELECT TOP '.$limitmatches[1].' ';
          }	
       	  $query = preg_replace( '/^\s*SELECT\s*/i', $replacement, $query );
   		}
 
       //Replace date_add with DATEADD with translated INTERVAL arguments
       //MySQL date_add syntax: 
    	//  DATE_ADD(date,INTERVAL expr unit)
    	//SQL Anywhere syntax:
    	//  DATEADD( unit, expr, date )
    	//Match a date: (19|20)\d\d([- /.])(0[1-9]|1[012])\2(0[1-9]|[12][0-9]|3[01])  - \2 doesn't work as a back reference to the date separator	    
    	//This will match a date string in the first parameter of the date_add function	    
       //    $pattern = "/(DATE_ADD\s*\(\s*')((19|20)\d\d([- \/.])(0[1-9]|1[012])([- \/.])(0[1-9]|[12][0-9]|3[01]))('\s*,\s*INTERVAL\s*)(\d+)(\:*)(\d*)(\s*HOUR_MINUTE\s*|\s*MINUTE\s*|\s*SECOND\s*|\s*DAY\s*|\s*HOUR\s*)(\s*\))/i";
 
       //This will match a variable in the first parameter of the date_add function
    	$pattern = "/(DATE_ADD\s*\(\s*)(\w+)(\s*,\s*INTERVAL\s*)(\d+)(\:*)(\d*)(\s*HOUR_MINUTE\s*|\s*MINUTE\s*|\s*SECOND\s*|\s*DAY\s*|\s*HOUR\s*)(\s*\))/i";
           	$matched = preg_match( $pattern, $query, $dateaddmatches );
    	if( $matched == 1 ) {
           if( preg_match('/\s*hour_minute\s*/i', $dateaddmatches[7]) > 0 ) {
               $addminutes = ($dateaddmatches[4]*60) + $dateaddmatches[6];
               $replacement = 'DATEADD( minute, '.$addminutes.', \''.$dateaddmatches[2].'\' )';
           } else {
             $replacement = 'DATEADD( '.$dateaddmatches[7].', '.$dateaddmatches[4].', '.$dateaddmatches[2].' )';
           }
       	  $query = preg_replace( $pattern, $replacement, $query );
    	}
 
       //Replace SQL_CALC_FOUND_ROWS with nothing - it is an imperfect solution, but shouldn't break anything
      $query = preg_replace( "/\s*SQL_CALC_FOUND_ROWS\s*/i", ' ', $query);
 
    	//fix some GROUP BY issues with queries that MySQL allows
    	$pattern = "/SELECT\s*DISTINCT\s*YEAR\(post_date\)/i";
    	if( preg_match( $pattern, $query, $foo ) ) {
           $pattern = "/GROUP\s*BY\s*YEAR\s*\(\s*post_date\s*\), MONTH\(post_date\), DAYOFMONTH\(post_date\)/i";
           $pattern2 = "/GROUP\s*BY\s*YEAR\s*\(\s*post_date\s*\), MONTH\(post_date\)/i";
           $pattern3 = "/GROUP\s*BY\s*YEAR\s*\(\s*post_date\s*\)/i";
           $orderbypattern = "/ORDER\s*BY\s*post_date\s*/i";
           if( preg_match( $orderbypattern, $query ) ) {
               if( preg_match( $pattern, $query ) ) {
                   $query = preg_replace( $orderbypattern, "ORDER BY YEAR(post_date) DESC, MONTH(post_date) DESC, DAYOFMONTH(post_date)", $query );
               }else if( preg_match( $pattern2, $query ) ) {
                   $query = preg_replace( $orderbypattern, "ORDER BY YEAR(post_date) DESC, MONTH(post_date)", $query );
               }else if( preg_match( $pattern3, $query ) ) {
                   $query = preg_replace( $orderbypattern, "ORDER BY YEAR(post_date)", $query );
               } else {
                   $query = preg_replace( $orderbypattern, "ORDER BY YEAR(post_date)", $query );
               }
           }		   
      }
       // Remove GROUP BY wpdb->posts.id because ID is a pkey, so grouping by it makes no sense
       $pattern = "/\s*GROUP\s*BY\s*".$wpdb->posts.".ID\s*/i";
       $query = preg_replace( $pattern, " ", $query );
 
    	//Rewrite a specific IF( <condition>, t, f ) to IF <condition> THEN t ELSE f ENDIF
    	$pattern = "/SELECT\s*\*\s*,\s*IF\s*\(\s*(DATEADD\s*\(\s*MINUTE\s*,\s*\d+\s*,\s*\w+\s*\)\s*\>=\s*NOW\(\))\s*,\s*(\w+)\s*,\s*(\w+)\)/i"; 
       if( preg_match( $pattern, $query, $ifmatches ) ) {
           $replacement = 'SELECT *, IF '.$ifmatches[1].' THEN '.$ifmatches[2].' ELSE '.$ifmatches[3].' ENDIF';
       	  $query = preg_replace( $pattern, $replacement, $query );
    	}
 
       //Remove order by meta_id from select...meta_key...order by meta_id 
       // to avoid syntax error (can;t sort by a column not in the select list...
       $pattern = '/ORDER\s*BY\s*meta_id\s*DESC\s*/i';
       $query = preg_replace( $pattern, ' ', $query);
   }	
   return $query; 
}