If you want to do it anyway, please set "PG4WP_INSECURE" to true in your "db.php" file.' ); // PostgreSQL must connect to a specific database (unlike MySQL) // Guess at one here and reconnect as required in wpsql_select_db $dbname = defined('DB_NAME') && DB_NAME ? DB_NAME : 'template1'; return pg_connect( $GLOBALS['pg4wp_connstr'].' dbname='.$dbname); } // The effective connection happens here function wpsql_select_db($dbname, $connection_id = 0) { $pg_connstr = $GLOBALS['pg4wp_connstr'].' dbname='.$dbname; // Note: pg_connect returns existing connection for same connstr $GLOBALS['pg4wp_conn'] = $conn = pg_connect($pg_connstr); if( !$conn) return $conn; $ver = pg_version($conn); if( isset($ver['server'])) $GLOBALS['pg4wp_version'] = $ver['server']; // Now we should be connected, we "forget" about the connection parameters (if this is not a "test" connection) if( !defined('WP_INSTALLING') || !WP_INSTALLING) $GLOBALS['pg4wp_connstr'] = ''; // Execute early transmitted commands if needed if( !empty($GLOBALS['pg4wp_pre_sql'])) foreach( $GLOBALS['pg4wp_pre_sql'] as $sql2run) wpsql_query( $sql2run); pg4wp_init($conn); return $conn; } function wpsql_fetch_array($result) { $res = pg_fetch_array($result); if( is_array($res) ) foreach($res as $v => $k ) $res[$v] = trim($k); return $res; } function wpsql_query($sql) { if( !$GLOBALS['pg4wp_conn']) { // Catch SQL to be executed as soon as connected $GLOBALS['pg4wp_pre_sql'][] = $sql; return true; } $initial = $sql; $sql = pg4wp_rewrite( $sql); $GLOBALS['pg4wp_result'] = pg_query($sql); if( (PG4WP_DEBUG || PG4WP_LOG_ERRORS) && $GLOBALS['pg4wp_result'] === false && $err = pg_last_error()) { $ignore = false; if( defined('WP_INSTALLING') && WP_INSTALLING) { global $table_prefix; $ignore = strpos($err, 'relation "'.$table_prefix); } if( ! $ignore ) error_log('['.microtime(true)."] Error running :\n$initial\n---- converted to ----\n$sql\n----> $err\n---------------------\n", 3, PG4WP_LOG.'pg4wp_errors.log'); } return $GLOBALS['pg4wp_result']; } function wpsql_insert_id($lnk = NULL) { global $wpdb; $ins_field = $GLOBALS['pg4wp_ins_field']; $table = $GLOBALS['pg4wp_ins_table']; $lastq = $GLOBALS['pg4wp_last_insert']; $seq = $table . '_seq'; // Table 'term_relationships' doesn't have a sequence if( $table == $wpdb->term_relationships) { $sql = 'NO QUERY'; $data = 0; } // When using WP_Import plugin, ID is defined in the query elseif('post_author' == $ins_field && false !== strpos($lastq,'ID')) { $sql = 'ID was in query '; $pattern = '/.+\'(\d+).+$/'; preg_match($pattern, $lastq, $matches); $data = $matches[1]; // We should update the sequence on the next non-INSERT query $GLOBALS['pg4wp_queued_query'] = "SELECT SETVAL('$seq',(SELECT MAX(\"ID\") FROM $table)+1);"; } else { $sql = "SELECT CURRVAL('$seq')"; $res = pg_query($sql); if( false !== $res) $data = pg_fetch_result($res, 0, 0); elseif( PG4WP_DEBUG || PG4WP_ERROR_LOG) { $log = '['.microtime(true)."] wpsql_insert_id() was called with '$table' and '$ins_field'". " and returned the error:\n".pg_last_error(). "\nFor the query:\n".$sql. "\nThe latest INSERT query was :\n'$lastq'\n"; error_log( $log, 3, PG4WP_LOG.'pg4wp_errors.log'); } } if( PG4WP_DEBUG && $sql) error_log( '['.microtime(true)."] Getting inserted ID for '$table' ('$ins_field') : $sql => $data\n", 3, PG4WP_LOG.'pg4wp_insertid.log'); return $data; } // Convert MySQL FIELD function to CASE statement // https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field // Other implementations: https://stackoverflow.com/q/1309624 function pg4wp_rewrite_field($matches) { $case = 'CASE ' . trim($matches[1]); $comparands = explode(',', $matches[2]); foreach($comparands as $i => $comparand) { $case .= ' WHEN ' . trim($comparand) . ' THEN ' . ($i + 1); } $case .= ' ELSE 0 END'; return $case; } function pg4wp_rewrite( $sql) { // Note: Can be called from constructor before $wpdb is set global $wpdb; $logto = 'queries'; // The end of the query may be protected against changes $end = ''; // Remove unusefull spaces $initial = $sql = trim($sql); if( 0 === strpos($sql, 'SELECT')) { $logto = 'SELECT'; // SQL_CALC_FOUND_ROWS doesn't exist in PostgreSQL but it's needed for correct paging if( false !== strpos($sql, 'SQL_CALC_FOUND_ROWS')) { $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql); $GLOBALS['pg4wp_numrows_query'] = $sql; if( PG4WP_DEBUG) error_log( '['.microtime(true)."] Number of rows required for :\n$sql\n---------------------\n", 3, PG4WP_LOG.'pg4wp_NUMROWS.log'); } elseif( false !== strpos($sql, 'FOUND_ROWS()')) { // Here we convert the latest query into a COUNT query $sql = $GLOBALS['pg4wp_numrows_query']; // Remove any LIMIT ... clause (this is the blocking part) $pattern = '/\s+LIMIT.+/'; $sql = preg_replace( $pattern, '', $sql); // Now add the COUNT() statement $pattern = '/SELECT\s+([^\s]+)\s+(FROM.+)/'; $sql = preg_replace( $pattern, 'SELECT COUNT($1) $2', $sql); } // Ensure that ORDER BY column appears in SELECT DISTINCT fields $pattern = '/^SELECT DISTINCT.*ORDER BY\s+(\S+)/'; if( preg_match( $pattern, $sql, $matches) && strpos( $sql, $matches[1]) > strpos( $sql, 'ORDER BY') && false === strpos( $sql, '*')) { if( false !== strpos( $sql, 'GROUP BY')) { $pattern = '/ FROM /'; $sql = preg_replace( $pattern, ', MIN('.$matches[1].') AS '.$matches[1].' FROM ', $sql, 1); } else { $pattern = '/ FROM /'; $sql = preg_replace( $pattern, ', '.$matches[1].' FROM ', $sql, 1); } } // Convert CONVERT to CAST $pattern = '/CONVERT\(([^()]*(\(((?>[^()]+)|(?-2))*\))?[^()]*),\s*([^\s]+)\)/x'; $sql = preg_replace( $pattern, 'CAST($1 AS $4)', $sql); // Handle CAST( ... AS CHAR) $sql = preg_replace( '/CAST\((.+) AS CHAR\)/', 'CAST($1 AS TEXT)', $sql); // Handle CAST( ... AS SIGNED) $sql = preg_replace( '/CAST\((.+) AS SIGNED\)/', 'CAST($1 AS INTEGER)', $sql); // Handle COUNT(*)...ORDER BY... $sql = preg_replace( '/COUNT(.+)ORDER BY.+/s', 'COUNT$1', $sql); // In order for users counting to work... $matches = array(); if( preg_match_all( '/COUNT[^C]+\),/',$sql, $matches)) { foreach( $matches[0] as $num => $one) { $sub = substr( $one, 0, -1); $sql = str_replace( $sub, $sub.' AS count'.$num, $sql); } } $pattern = '/LIMIT[ ]+(\d+),[ ]*(\d+)/'; $sql = preg_replace($pattern, 'LIMIT $2 OFFSET $1', $sql); $pattern = '/DATE_ADD[ ]*\(([^,]+),([^\)]+)\)/'; $sql = preg_replace( $pattern, '($1 + $2)', $sql); $pattern = '/FIELD[ ]*\(([^\),]+),([^\)]+)\)/'; $sql = preg_replace_callback( $pattern, 'pg4wp_rewrite_field', $sql); $pattern = '/GROUP_CONCAT\(([^()]*(\(((?>[^()]+)|(?-2))*\))?[^()]*)\)/x'; $sql = preg_replace( $pattern, "string_agg($1, ',')", $sql); // Convert MySQL RAND function to PostgreSQL RANDOM function $pattern = '/RAND[ ]*\([ ]*\)/'; $sql = preg_replace( $pattern, 'RANDOM()', $sql); // UNIX_TIMESTAMP in MYSQL returns an integer $pattern = '/UNIX_TIMESTAMP\(([^\)]+)\)/'; $sql = preg_replace( $pattern, 'ROUND(DATE_PART(\'epoch\',$1))', $sql); $date_funcs = array( 'DAYOFMONTH(' => 'EXTRACT(DAY FROM ', 'YEAR(' => 'EXTRACT(YEAR FROM ', 'MONTH(' => 'EXTRACT(MONTH FROM ', 'DAY(' => 'EXTRACT(DAY FROM ', ); $sql = str_replace( 'ORDER BY post_date DESC', 'ORDER BY YEAR(post_date) DESC, MONTH(post_date) DESC', $sql); $sql = str_replace( 'ORDER BY post_date ASC', 'ORDER BY YEAR(post_date) ASC, MONTH(post_date) ASC', $sql); $sql = str_replace( array_keys($date_funcs), array_values($date_funcs), $sql); $curryear = date( 'Y'); $sql = str_replace( 'FROM \''.$curryear, 'FROM TIMESTAMP \''.$curryear, $sql); // MySQL 'IF' conversion - Note : NULLIF doesn't need to be corrected $pattern = '/ (?prefix.'posts.ID', '' , $sql); } $sql = str_replace("!= ''", '<> 0', $sql); // MySQL 'LIKE' is case insensitive by default, whereas PostgreSQL 'LIKE' is $sql = str_replace( ' LIKE ', ' ILIKE ', $sql); // INDEXES are not yet supported if( false !== strpos( $sql, 'USE INDEX (comment_date_gmt)')) $sql = str_replace( 'USE INDEX (comment_date_gmt)', '', $sql); // HB : timestamp fix for permalinks $sql = str_replace( 'post_date_gmt > 1970', 'post_date_gmt > to_timestamp (\'1970\')', $sql); // Akismet sometimes doesn't write 'comment_ID' with 'ID' in capitals where needed ... if( isset($wpdb) && false !== strpos( $sql, $wpdb->comments)) $sql = str_replace(' comment_id ', ' comment_ID ', $sql); // MySQL treats a HAVING clause without GROUP BY like WHERE if( false !== strpos($sql, 'HAVING') && false === strpos($sql, 'GROUP BY')) { if( false === strpos($sql, 'WHERE')) $sql = str_replace('HAVING', 'WHERE', $sql); else { $pattern = '/WHERE\s+(.*?)\s+HAVING\s+(.*?)(\s*(?:ORDER|LIMIT|PROCEDURE|INTO|FOR|LOCK|$))/'; $sql = preg_replace( $pattern, 'WHERE ($1) AND ($2) $3', $sql); } } // MySQL allows integers to be used as boolean expressions // where 0 is false and all other values are true. // // Although this could occur anywhere with any number, so far it // has only been observed as top-level expressions in the WHERE // clause and only with 0. For performance, limit current // replacements to that. $pattern_after_where = '(?:\s*$|\s+(GROUP|HAVING|ORDER|LIMIT|PROCEDURE|INTO|FOR|LOCK))'; $pattern = '/(WHERE\s+)0(\s+AND|\s+OR|' . $pattern_after_where . ')/'; $sql = preg_replace( $pattern, '$1false$2', $sql); $pattern = '/(AND\s+|OR\s+)0(' . $pattern_after_where . ')/'; $sql = preg_replace( $pattern, '$1false$2', $sql); // MySQL supports strings as names, PostgreSQL needs identifiers. // Limit to after closing parenthesis to reduce false-positives // Currently only an issue for nextgen-gallery plugin $pattern = '/\) AS \'([^\']+)\'/'; $sql = preg_replace( $pattern, ') AS "$1"', $sql); } // SELECT elseif( 0 === strpos($sql, 'UPDATE')) { $logto = 'UPDATE'; $pattern = '/LIMIT[ ]+\d+/'; $sql = preg_replace($pattern, '', $sql); // For correct bactick removal $pattern = '/[ ]*`([^` ]+)`[ ]*=/'; $sql = preg_replace( $pattern, ' $1 =', $sql); // Those are used when we need to set the date to now() in gmt time $sql = str_replace( "'0000-00-00 00:00:00'", 'now() AT TIME ZONE \'gmt\'', $sql); // For correct ID quoting $pattern = '/(,|\s)[ ]*([^ \']*ID[^ \']*)[ ]*=/'; $sql = preg_replace( $pattern, '$1 "$2" =', $sql); // This will avoid modifications to anything following ' SET ' list($sql,$end) = explode( ' SET ', $sql, 2); $end = ' SET '.$end; } // UPDATE elseif( 0 === strpos($sql, 'INSERT')) { $logto = 'INSERT'; $sql = str_replace('(0,',"('0',", $sql); $sql = str_replace('(1,',"('1',", $sql); // Fix inserts into wp_categories if( false !== strpos($sql, 'INSERT INTO '.$wpdb->categories)) { $sql = str_replace('"cat_ID",', '', $sql); $sql = str_replace("VALUES ('0',", "VALUES(", $sql); } // Those are used when we need to set the date to now() in gmt time $sql = str_replace( "'0000-00-00 00:00:00'", 'now() AT TIME ZONE \'gmt\'', $sql); // Multiple values group when calling INSERT INTO don't always work if( false !== strpos( $sql, $wpdb->options) && false !== strpos( $sql, '), (')) { $pattern = '/INSERT INTO.+VALUES/'; preg_match($pattern, $sql, $matches); $insert = $matches[0]; $sql = str_replace( '), (', ');'.$insert.'(', $sql); } // Support for "INSERT ... ON DUPLICATE KEY UPDATE ..." is a dirty hack // consisting in deleting the row before inserting it if( false !== $pos = strpos( $sql, 'ON DUPLICATE KEY')) { // Get the elements we need (table name, first field, corresponding value) $pattern = '/INSERT INTO\s+([^\(]+)\(([^,]+)[^\(]+VALUES\s*\(([^,]+)/'; preg_match($pattern, $sql, $matches); $table = trim( $matches[1], ' `'); if( !in_array(trim($matches[1],'` '), array($wpdb->posts,$wpdb->comments))) { // Remove 'ON DUPLICATE KEY UPDATE...' and following $sql = substr( $sql, 0, $pos); // Add a delete query to handle the maybe existing data $sql = 'DELETE FROM '.$table.' WHERE '.$matches[2].' = '.$matches[3].';'.$sql; } } elseif( 0 === strpos($sql, 'INSERT IGNORE')) { // Note: Requires PostgreSQL 9.0 and USAGE privilege. // Could do query-specific rewrite using SELECT without FROM // as in http://stackoverflow.com/a/13342031 $sql = 'DO $$BEGIN INSERT'.substr($sql, 13).'; EXCEPTION WHEN unique_violation THEN END;$$;'; } // To avoid Encoding errors when inserting data coming from outside if( preg_match('/^.{1}/us',$sql,$ar) != 1) $sql = utf8_encode($sql); // This will avoid modifications to anything following ' VALUES' list($sql,$end) = explode( ' VALUES', $sql, 2); $end = ' VALUES'.$end; // When installing, the sequence for table terms has to be updated if( defined('WP_INSTALLING') && WP_INSTALLING && false !== strpos($sql, 'INSERT INTO `'.$wpdb->terms.'`')) $end .= ';SELECT setval(\''.$wpdb->terms.'_seq\', (SELECT MAX(term_id) FROM '.$wpdb->terms.')+1);'; } // INSERT elseif( 0 === strpos( $sql, 'DELETE' )) { $logto = 'DELETE'; // ORDER BY is not supported in DELETE queries, and not required // when LIMIT is not present if( false !== strpos( $sql, 'ORDER BY') && false === strpos( $sql, 'LIMIT')) { $pattern = '/ORDER BY \S+ (ASC|DESC)?/'; $sql = preg_replace( $pattern, '', $sql); } // LIMIT is not allowed in DELETE queries $sql = str_replace( 'LIMIT 1', '', $sql); $sql = str_replace( ' REGEXP ', ' ~ ', $sql); // This handles removal of duplicate entries in table options if( false !== strpos( $sql, 'DELETE o1 FROM ')) $sql = "DELETE FROM $wpdb->options WHERE option_id IN " . "(SELECT o1.option_id FROM $wpdb->options AS o1, $wpdb->options AS o2 " . "WHERE o1.option_name = o2.option_name " . "AND o1.option_id < o2.option_id)"; // Rewrite _transient_timeout multi-table delete query elseif( 0 === strpos( $sql, 'DELETE a, b FROM wp_options a, wp_options b')) { $where = substr( $sql, strpos($sql, 'WHERE ') + 6); $where = rtrim( $where, " \t\n\r;"); // Fix string/number comparison by adding check and cast $where = str_replace( 'AND b.option_value', 'AND b.option_value ~ \'^[0-9]+$\' AND CAST(b.option_value AS BIGINT)', $where); // Mirror WHERE clause to delete both sides of self-join. $where2 = strtr( $where, array('a.' => 'b.', 'b.' => 'a.')); $sql = 'DELETE FROM wp_options a USING wp_options b WHERE '. '('.$where.') OR ('.$where2.');'; } // Akismet sometimes doesn't write 'comment_ID' with 'ID' in capitals where needed ... if( false !== strpos( $sql, $wpdb->comments)) $sql = str_replace(' comment_id ', ' comment_ID ', $sql); } // Fix tables listing elseif( 0 === strpos($sql, 'SHOW TABLES')) { $logto = 'SHOWTABLES'; $sql = 'SELECT tablename FROM pg_tables WHERE schemaname = \'public\';'; } // Rewriting optimize table elseif( 0 === strpos($sql, 'OPTIMIZE TABLE')) { $logto = 'OPTIMIZE'; $sql = str_replace( 'OPTIMIZE TABLE', 'VACUUM', $sql); } // Handle 'SET NAMES ... COLLATE ...' elseif( 0 === strpos($sql, 'SET NAMES') && false !== strpos($sql, 'COLLATE')) { $logto = 'SETNAMES'; $sql = "SET NAMES 'utf8'"; } // Load up upgrade and install functions as required $begin = strtoupper( substr( $sql, 0, 3)); $search = array( 'SHO', 'ALT', 'DES', 'CRE', 'DRO'); if( in_array($begin, $search)) { require_once( PG4WP_ROOT.'/driver_pgsql_install.php'); $sql = pg4wp_installing( $sql, $logto); } // WP 2.9.1 uses a comparison where text data is not quoted $pattern = '/AND meta_value = (-?\d+)/'; $sql = preg_replace( $pattern, 'AND meta_value = \'$1\'', $sql); // Add type cast for meta_value field when it's compared to number $pattern = '/AND meta_value < (\d+)/'; $sql = preg_replace($pattern, 'AND meta_value::bigint < $1', $sql); // Generic "INTERVAL xx YEAR|MONTH|DAY|HOUR|MINUTE|SECOND" handler $pattern = '/INTERVAL[ ]+(\d+)[ ]+(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND)/'; $sql = preg_replace( $pattern, "'\$1 \$2'::interval", $sql); $pattern = '/DATE_SUB[ ]*\(([^,]+),([^\)]+)\)/'; $sql = preg_replace( $pattern, '($1::timestamp - $2)', $sql); // Remove illegal characters $sql = str_replace('`', '', $sql); // Field names with CAPITALS need special handling if( false !== strpos($sql, 'ID')) { $pattern = '/ID([^ ])/'; $sql = preg_replace($pattern, 'ID $1', $sql); $pattern = '/ID$/'; $sql = preg_replace($pattern, 'ID ', $sql); $pattern = '/\(ID/'; $sql = preg_replace($pattern, '( ID', $sql); $pattern = '/,ID/'; $sql = preg_replace($pattern, ', ID', $sql); $pattern = '/[0-9a-zA-Z_]+ID/'; $sql = preg_replace($pattern, '"$0"', $sql); $pattern = '/\.ID/'; $sql = preg_replace($pattern, '."ID"', $sql); $pattern = '/[\s]ID /'; $sql = preg_replace($pattern, ' "ID" ', $sql); $pattern = '/"ID "/'; $sql = preg_replace($pattern, ' "ID" ', $sql); } // CAPITALS // Empty "IN" statements are erroneous $sql = str_replace( 'IN (\'\')', 'IN (NULL)', $sql); $sql = str_replace( 'IN ( \'\' )', 'IN (NULL)', $sql); $sql = str_replace( 'IN ()', 'IN (NULL)', $sql); // Put back the end of the query if it was separated $sql .= $end; // For insert ID catching if( $logto == 'INSERT') { $pattern = '/INSERT INTO (\w+)\s+\([ a-zA-Z_"]+/'; preg_match($pattern, $sql, $matches); $GLOBALS['pg4wp_ins_table'] = $matches[1]; $match_list = explode(' ', $matches[0]); if( $GLOBALS['pg4wp_ins_table']) { $GLOBALS['pg4wp_ins_field'] = trim($match_list[3],' () '); if(! $GLOBALS['pg4wp_ins_field']) $GLOBALS['pg4wp_ins_field'] = trim($match_list[4],' () '); } $GLOBALS['pg4wp_last_insert'] = $sql; } elseif( isset($GLOBALS['pg4wp_queued_query'])) { pg_query($GLOBALS['pg4wp_queued_query']); unset($GLOBALS['pg4wp_queued_query']); } // Correct quoting for PostgreSQL 9.1+ compatibility $sql = str_replace( "\\'", "''", $sql); $sql = str_replace( '\"', '"', $sql); if( PG4WP_DEBUG) { if( $initial != $sql) error_log( '['.microtime(true)."] Converting :\n$initial\n---- to ----\n$sql\n---------------------\n", 3, PG4WP_LOG.'pg4wp_'.$logto.'.log'); else error_log( '['.microtime(true)."] $sql\n---------------------\n", 3, PG4WP_LOG.'pg4wp_unmodified.log'); } return $sql; } // Database initialization function pg4wp_init() { // Provide (mostly) MySQL-compatible field function // Note: MySQL accepts heterogeneous argument types. No easy fix. // Can define version with typed first arg to cover some cases. // Note: ROW_NUMBER+unnest doesn't guarantee order, but is simple/fast. // If it breaks, try https://stackoverflow.com/a/8767450 $result = pg_query(<<