/package/wp/2.9.2/wordpress/wp-includes/wp-db/sqlsrv/translations/translations.php
PHP | 1537 lines | 1037 code | 86 blank | 414 comment | 180 complexity | 6d6280ff07bf980cc35704e2591556dc MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- <?php
- require_once(dirname(__FILE__) . '/fields_map.php');
- /**
- * SQL Dialect Translations
- *
- * @category MSSQL
- * @package MySQL_Translations
- * @author A.Garcia & A.Gentile
- * */
- class SQL_Translations
- {
- /**
- * Field Mapping
- *
- * @since 2.7.1
- * @access private
- * @var array
- */
- var $fields_map = null;
- /**
- * Was this query prepared?
- *
- * @since 2.7.1
- * @access private
- * @var bool
- */
- var $prepared = false;
- /**
- * Update query?
- *
- * @since 2.7.1
- * @access private
- * @var bool
- */
- var $update_query = false;
- /**
- * Insert query?
- *
- * @since 2.7.1
- * @access private
- * @var bool
- */
- var $insert_query = false;
-
- /**
- * Delete query?
- *
- * @since 2.7.1
- * @access private
- * @var bool
- */
- var $delete_query = false;
- /**
- * Prepare arguments
- *
- * @since 2.7.1
- * @access private
- * @var array
- */
- var $prepare_args = array();
- /**
- * Update Data
- *
- * @since 2.7.1
- * @access private
- * @var array
- */
- var $update_data = array();
-
- /**
- * Limit Info
- *
- * @since 2.7.1
- * @access private
- * @var array
- */
- var $limit = array();
- /**
- * Update Data
- *
- * @since 2.7.1
- * @access private
- * @var array
- */
- var $translation_changes = array();
-
- /**
- * Azure
- * Are we dealing with a SQL Azure DB?
- *
- * @since 2.7.1
- * @access public
- * @var bool
- */
- var $azure = false;
-
- /**
- * Preceeding query
- * Sometimes we need to issue a query
- * before the original query
- *
- * @since 2.8.5
- * @access public
- * @var mixed
- */
- var $preceeding_query = false;
-
- /**
- * Following query
- * Sometimes we need to issue a query
- * right after the original query
- *
- * @since 2.8.5
- * @access public
- * @var mixed
- */
- var $following_query = false;
-
- /**
- * Should we verify update/insert queries?
- *
- * @since 2.8.5
- * @access public
- * @var mixed
- */
- var $verify = true;
-
- /**
- * php4 style call to constructor.
- *
- * @since 2.7.1
- *
- */
- function SQL_Translations()
- {
- return $this->__construct();
- }
-
- /**
- * Assign fields_map as a new Fields_map object
- *
- * PHP5 style constructor for compatibility with PHP5.
- *
- * @since 2.7.1
- */
- function __construct()
- {
- $this->fields_map = new Fields_map();
- }
- /**
- * MySQL > MSSQL Query Translation
- * Processes smaller translation sub-functions
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate($query)
- {
- $this->limit = array();
- $this->verify = true;
- $this->insert_query = false;
- $this->delete_query = false;
- $this->update_query = false;
- $this->preceeding_query = false;
- $this->following_query = false;
-
- if ( stripos($query, 'INSERT') === 0 ) {
- $this->insert_query = true;
- }
-
- if ( stripos($query, 'DELETE') === 0 ) {
- $this->delete_query = true;
- }
-
- // Was this query prepared?
- if ( strripos($query, '--PREPARE') !== FALSE ) {
- $query = str_replace('--PREPARE', '', $query);
- $this->prepared = TRUE;
- } else {
- $this->prepared = FALSE;
- }
- // Update Query?
- if ( stripos($query, 'UPDATE') === 0 ) {
- $this->update_query = true;
- }
- // Do we have serialized arguments?
- if ( strripos($query, '--SERIALIZED') !== FALSE ) {
- $query = str_replace('--SERIALIZED', '', $query);
- if ($this->insert_query) {
- $query = $this->on_duplicate_key($query);
- }
- $query = $this->translate_general($query);
- return $query;
- }
- $query = trim($query);
- $sub_funcs = array(
- 'translate_general',
- 'translate_date_add',
- 'translate_if_stmt',
- 'translate_sqlcalcrows',
- 'translate_limit',
- 'translate_now_datetime',
- 'translate_distinct_orderby',
- 'translate_sort_casting',
- 'translate_column_type',
- 'translate_remove_groupby',
- 'translate_insert_nulltime',
- 'translate_incompat_data_type',
- 'translate_create_queries',
- );
- // Perform translations and record query changes.
- $this->translation_changes = array();
- foreach ( $sub_funcs as $sub_func ) {
- $old_query = $query;
- $query = $this->$sub_func($query);
- if ( $old_query !== $query ) {
- $this->translation_changes[] = $sub_func;
- $this->translation_changes[] = $query;
- $this->translation_changes[] = $old_query;
- }
- }
- if ( $this->insert_query ) {
- $query = $this->on_duplicate_key($query);
- $query = $this->split_insert_values($query);
- }
- if ( $this->prepared && $this->insert_query && $this->verify ) {
- if ( is_array($query) ) {
- foreach ($query as $k => $v) {
- $query[$k] = $this->verify_insert($v);
- }
- } else {
- $query = $this->verify_insert($query);
- }
- }
- if ( $this->update_query && $this->verify ) {
- $query = $this->verify_update($query);
- }
- return $query;
- }
- /**
- * More generalized information gathering queries
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_general($query)
- {
- // SERVER VERSION
- if ( stripos($query, 'SELECT VERSION()' ) === 0) {
- $query = substr_replace($query, 'SELECT @@VERSION', 0, 16);
- }
- // SQL_MODE NO EQUIV
- if ( stripos($query, "SHOW VARIABLES LIKE 'sql_mode'" ) === 0) {
- $query = '';
- }
- // LAST INSERT ID
- if ( stripos($query, 'LAST_INSERT_ID()') > 0 ) {
- $start_pos = stripos($query, 'LAST_INSERT_ID()');
- $query = substr_replace($query, '@@IDENTITY', $start_pos, 16);
- }
- // SHOW TABLES
- if ( strtolower($query) === 'show tables;' ) {
- $query = str_ireplace('show tables',"select name from SYSOBJECTS where TYPE = 'U' order by NAME",$query);
- }
- if ( stripos($query, 'show tables like ') === 0 ) {
- $end_pos = strlen($query);
- $param = substr($query, 17, $end_pos - 17);
- $query = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ' . $param;
- }
- // SET NAMES doesn't exist in T-SQL
- if ( strtolower($query) == "set names 'utf8'" ) {
- $query = "";
- }
- // SHOW COLUMNS
- if ( stripos($query, 'SHOW COLUMNS FROM ') === 0 ) {
- $end_pos = strlen($query);
- $param = substr($query, 18, $end_pos - 18);
- $param = "'". trim($param, "'") . "'";
- $query = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' . $param;
- }
-
- // SHOW INDEXES - issue with sql azure trying to fix....sys.sysindexes is coming back as invalid onject name
- if ( stripos($query, 'SHOW INDEXES FROM ') === 0 ) {
- return $query;
- $table = substr($query, 18);
- $query = "SELECT sys.sysindexes.name AS IndexName
- FROM sysobjects
- JOIN sys.key_constraints ON parent_object_id = sys.sysobjects.id
- JOIN sys.sysindexes ON sys.sysindexes.id = sys.sysobjects.id and sys.key_constraints.unique_index_id = sys.sysindexes.indid
- JOIN sys.index_columns ON sys.index_columns.object_id = sys.sysindexes.id and sys.index_columns.index_id = sys.sysindexes.indid
- JOIN sys.syscolumns ON sys.syscolumns.id = sys.sysindexes.id AND sys.index_columns.column_id = sys.syscolumns.colid
- WHERE sys.sysobjects.type = 'u'
- AND sys.sysobjects.name = '{$table}'";
- }
-
- // USE INDEX
- if ( stripos($query, 'USE INDEX (') !== FALSE) {
- $start_pos = stripos($query, 'USE INDEX (');
- $end_pos = $this->get_matching_paren($query, $start_pos + 11);
- $params = substr($query, $start_pos + 11, $end_pos - ($start_pos + 11));
- $params = explode(',', $params);
- foreach ($params as $k => $v) {
- $params[$k] = trim($v);
- foreach ($this->fields_map->read() as $table => $fields) {
- if ( is_array($fields) ) {
- foreach ($fields as $field_name => $field_meta) {
- if ( $field_name == $params[$k] ) {
- $params[$k] = $table . '_' . $params[$k];
- }
- }
- }
- }
- }
- $params = implode(',', $params);
- $query = substr_replace($query, 'WITH (INDEX(' . $params . '))', $start_pos, ($end_pos + 1) - $start_pos);
- }
-
- // DESCRIBE - this is pretty darn close to mysql equiv, however it will need to have a flag to modify the result set
- // this and SHOW INDEX FROM are used in WP upgrading. The problem is that WP will see the different data types and try
- // to alter the table thinking an upgrade is necessary. So the result set from this query needs to be modified using
- // the field_mapping to revert column types back to their mysql equiv to fool WP.
- if ( stripos($query, 'DESCRIBE ') === 0 ) {
- return $query;
- $table = substr($query, 9);
- $query = $this->describe($table);
- }
- // DROP TABLES
- if ( stripos($query, 'DROP TABLE IF EXISTS ') === 0 ) {
- $table = substr($query, 21, strlen($query) - 21);
- $query = 'DROP TABLE ' . $table;
- } elseif ( stripos($query, 'DROP TABLE ') === 0 ) {
- $table = substr($query, 11, strlen($query) - 11);
- $query = 'DROP TABLE ' . $table;
- }
-
- // REGEXP - not supported in TSQL
- if ( stripos($query, 'REGEXP') > 0 ) {
- if ( $this->delete_query && stripos($query, '^rss_[0-9a-f]{32}(_ts)?$') > 0 ) {
- $start_pos = stripos($query, 'REGEXP');
- $query = substr_replace($query, "LIKE 'rss_'", $start_pos);
- }
- }
- // TICKS
- $query = str_replace('`', '', $query);
- // Computed
- // This is done as the SQLSRV driver doesn't seem to set a property value for computed
- // selected columns, thus WP doesn't have anything to work with.
- $query = str_ireplace('SELECT COUNT(*)', 'SELECT COUNT(*) as Computed', $query);
- $query = str_ireplace('SELECT COUNT(1)', 'SELECT COUNT(1) as Computed', $query);
-
- // Turn on IDENTITY_INSERT for Importing inserts or category/tag adds that are
- // trying to explicitly set and IDENTITY column (WPMU)
- if ($this->insert_query) {
- $tables = array(
- $this->prefix . 'posts' => 'id',
- $this->prefix . 'terms' => 'term_id',
- );
- foreach ($tables as $table => $pid) {
- if (stristr($query, 'INTO ' . $table) !== FALSE) {
- $strlen = strlen($table);
- $start_pos = stripos($query, $table) + $strlen;
- $start_pos = stripos($query, '(', $start_pos);
- $end_pos = $this->get_matching_paren($query, $start_pos + 1);
- $params = substr($query, $start_pos + 1, $end_pos - ($start_pos + 1));
- $params = explode(',', $params);
- $found = false;
- foreach ($params as $k => $v) {
- if (strtolower($v) === $pid) {
- $found = true;
- }
- }
-
- if ($found) {
- $this->preceeding_query = "SET IDENTITY_INSERT $table ON";
- $this->following_query = "SET IDENTITY_INSERT $table OFF";
- }
- }
- }
- }
-
- // UPDATE queries trying to change an IDENTITY column this happens
- // for cat/tag adds (WPMU) e.g. UPDATE wp_1_terms SET term_id = 5 WHERE term_id = 3330
- if ($this->update_query) {
- $tables = array(
- $this->prefix . 'terms' => 'term_id',
- );
- foreach ($tables as $table => $pid) {
- if (stristr($query, $table . ' SET ' . $pid) !== FALSE) {
- preg_match_all("^=\s\d+^", $query, $matches);
- if (!empty($matches) && count($matches[0]) == 2) {
- $to = trim($matches[0][0], '= ');
- $from = trim($matches[0][1], '= ');
- $this->preceeding_query = "SET IDENTITY_INSERT $table ON";
- // find a better way to get columns (field mapping doesn't grab all)
- $query = "INSERT INTO $table (term_id,name,slug,term_group) SELECT $to,name,slug,term_group FROM $table WHERE $pid = $from";
- $this->following_query = array("DELETE $table WHERE $pid = $from","SET IDENTITY_INSERT $table OFF");
- $this->verify = false;
- }
- }
- }
- }
-
- return $query;
- }
- /**
- * Changes for DATE_ADD and INTERVAL
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_date_add($query)
- {
- $query = preg_replace('/date_add\((.*?),.*?([0-9]+?) (.*?)\)/i', 'DATEADD(\3,\2,\1)', $query);
- $query = preg_replace('/date_sub\((.*?),.*?([0-9]+?) (.*?)\)/i', 'DATEADD(\3,-\2,\1)', $query);
- return $query;
- }
- /**
- * Removing Unnecessary IF statement that T-SQL doesn't play nice with
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_if_stmt($query)
- {
- if ( stripos($query, 'IF (DATEADD(') > 0 ) {
- $start_pos = stripos($query, 'DATEADD(');
- $end_pos = $this->get_matching_paren($query, $start_pos + 8);
- $stmt = substr($query, $start_pos, ($end_pos - $start_pos)) . ') >= getdate() THEN 1 ELSE 0 END)';
- $start_pos = stripos($query, 'IF (');
- $end_pos = $this->get_matching_paren($query, ($start_pos+6))+1;
- $query = substr_replace($query, '(CASE WHEN ' . $stmt, $start_pos, ($end_pos - $start_pos));
- }
- return $query;
- }
- /**
- * SQL_CALC_FOUND_ROWS does not exist in T-SQL
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_sqlcalcrows($query)
- {
- if (stripos($query, 'SQL_CALC_FOUND_ROWS') > 0 ) {
- $sql_calc_pos = stripos($query, 'SQL_CALC_FOUND_ROWS');
- $from_pos = stripos($query, 'FROM');
- $query = substr_replace($query,'* ', $sql_calc_pos, ($from_pos - $sql_calc_pos));
- }
- // catch the next query.
- if ( stripos($query, 'FOUND_ROWS()') > 0 ) {
- $from_pos = stripos($this->previous_query, 'FROM');
- $where_pos = stripos($this->previous_query, 'WHERE');
- $from_str = trim(substr($this->previous_query, $from_pos, ($where_pos - $from_pos)));
- $order_by_pos = stripos($this->previous_query, 'ORDER BY');
- $where_str = trim(substr($this->previous_query, $where_pos, ($order_by_pos - $where_pos)));
- $query = str_ireplace('FOUND_ROWS()', 'COUNT(1) as Computed ' . $from_str . ' ' . $where_str, $query);
- }
- return $query;
- }
- /**
- * Changing LIMIT to TOP...mimicking offset while possible with rownum, it has turned
- * out to be very problematic as depending on the original query, the derived table
- * will have a lot of problems with columns names, ordering and what not.
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_limit($query)
- {
- if ( (stripos($query,'SELECT') !== 0 && stripos($query,'SELECT') !== FALSE)
- && (stripos($query,'UPDATE') !== 0 && stripos($query,'UPDATE') !== FALSE) ) {
- return $query;
- }
- $pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)$/is';
- $matched = preg_match($pattern, $query, $limit_matches);
- if ( $matched == 0 ) {
- return $query;
- }
- // Remove the LIMIT statement
- $true_offset = false;
- $query = preg_replace($pattern, '', $query);
- if ( stripos($query,'DELETE') === 0 ) {
- return $query;
- }
- // Check for true offset
- if ( count($limit_matches) == 5 && $limit_matches[1] != '0' ) {
- $true_offset = true;
- } elseif ( count($limit_matches) == 5 && $limit_matches[1] == '0' ) {
- $limit_matches[1] = $limit_matches[4];
- }
- // Rewrite the query.
- if ( $true_offset === false ) {
- if ( stripos($query, 'DISTINCT') > 0 ) {
- $query = str_ireplace('DISTINCT', 'DISTINCT TOP ' . $limit_matches[1] . ' ', $query);
- } else {
- $query = str_ireplace('DELETE ', 'DELETE TOP ' . $limit_matches[1] . ' ', $query);
- $query = str_ireplace('SELECT ', 'SELECT TOP ' . $limit_matches[1] . ' ', $query);
- }
- } else {
- $limit_matches[1] = (int) $limit_matches[1];
- $limit_matches[4] = (int) $limit_matches[4];
- $this->limit = array(
- 'from' => $limit_matches[1],
- 'to' => $limit_matches[4]
- );
- }
- return $query;
- }
- /**
- * Replace From UnixTime and now()
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_now_datetime($query)
- {
- $replacement = 'getdate()';
- $query = preg_replace('/(from_unixtime|unix_timestamp)\s*\(([^\)]*)\)/i', $replacement, $query);
- $query = str_ireplace('NOW()', $replacement, $query);
- // REPLACE dayofmonth which doesn't exist in T-SQL
- $check = $query;
- $query = preg_replace('/dayofmonth\((.*?)\)/i', 'DATEPART(DD,\1)',$query);
- if ($check !== $query) {
- $as_array = $this->get_as_fields($query);
- if (empty($as_array)) {
- $query = str_ireplace('FROM','as dom FROM',$query);
- }
- }
- return $query;
- }
- /**
- * Order By within a Select Distinct needs to have an field for every alias
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_distinct_orderby($query)
- {
- if ( stripos($query, 'DISTINCT') > 0 ) {
- if ( stripos($query, 'ORDER') > 0 ) {
- $order_pos = stripos($query, 'ORDER');
- if ( stripos($query, 'BY', $order_pos) > $order_pos ) {
- $fields = $this->get_as_fields($query);
- $ob = stripos($query, 'BY', $order_pos);
- if ( stripos($query, ' ASC', $ob) > 0 ) {
- $ord = stripos($query, ' ASC', $ob);
- }
- if ( stripos($query, ' DESC', $ob) > 0 ) {
- $ord = stripos($query, ' DESC', $ob);
- }
- $str = 'BY ';
- $str .= implode(', ',$fields);
- $query = substr_replace($query, $str, $ob, ($ord-$ob));
- }
- }
- }
- return $query;
- }
- /**
- * To sort text fields they need to be first cast as varchar
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_sort_casting($query)
- {
- if ( in_array('translate_limit', $this->translation_changes)) {
- //return $query;
- }
- if ( stripos($query, 'ORDER') > 0 ) {
- $order_pos = stripos($query, 'ORDER');
- if ( stripos($query, 'BY', $order_pos) == ($order_pos + 6) && stripos($query, 'OVER(', $order_pos - 5) != ($order_pos - 5)) {
- $ob = stripos($query, 'BY', $order_pos);
- if ( stripos($query,' ASC', $ob) > 0 ) {
- $ord = stripos($query, ' ASC', $ob);
- }
- if ( stripos($query,' DESC', $ob) > 0 ) {
- $ord = stripos($query, ' DESC', $ob);
- }
- $params = substr($query, ($ob + 3), ($ord - ($ob + 3)));
- $params = preg_split('/[\s,]+/', $params);
- $p = array();
- foreach ( $params as $value ) {
- $value = str_replace(',', '', $value);
- if ( !empty($value) ) {
- $p[] = $value;
- }
- }
- $str = '';
- foreach ($p as $v ) {
- $match = false;
- foreach( $this->fields_map->read() as $table => $table_fields ) {
- if ( is_array($table_fields) ) {
- foreach ( $table_fields as $field => $field_meta) {
- if ($field_meta['type'] == 'text') {
- if ( $v == $table . '.' . $field || $v == $field) {
- $match = true;
- }
- }
- }
- }
- }
- if ( $match ) {
- $str .= 'cast(' . $v . ' as varchar(255)), ';
- } else {
- $str .= $v . ', ';
- }
- }
- $str = rtrim($str, ', ');
- $query = substr_replace($query, $str, ($ob + 3), ($ord - ($ob + 3)));
- }
- }
- return $query;
- }
- /**
- * Meta key fix. \_% to [_]%
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_column_type($query)
- {
- if ( stripos($query, "LIKE '\_%'") > 0 ) {
- $start_pos = stripos($query, "LIKE '\_%'");
- $end_pos = $start_pos + 10;
- $str = "LIKE '[_]%'";
- $query = substr_replace($query, $str, $start_pos, ($end_pos - $start_pos));
- }
- return $query;
- }
- /**
- * Remove group by stmt in certain queries as T-SQL will
- * want all column names to execute query properly
- *
- * FIXES: Column 'wp_posts.post_author' is invalid in the select list because
- * it is not contained in either an aggregate function or the GROUP BY clause.
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_remove_groupby($query)
- {
- $query = str_ireplace("GROUP BY {$this->prefix}posts.ID ", ' ', $query);
- // Fixed query for archives widgets.
- $query = str_ireplace(
- 'GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC',
- 'GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY month DESC, year DESC',
- $query
- );
- return $query;
- }
- /**
- * When INSERTING 0000-00-00 00:00:00 or '' for datetime SQL Server says wtf
- * because it's null value begins at 1900-01-01...so lets change this to current time.
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_insert_nulltime($query)
- {
- if ( stripos($query, 'INSERT') === 0 ) {
- // Lets grab the fields to be inserted into and their position
- // based on the csv.
- $first_paren = stripos($query, '(', 11) + 1;
- $last_paren = $this->get_matching_paren($query, $first_paren);
- $fields = explode(',',substr($query, $first_paren, ($last_paren - $first_paren)));
- $date_fields = array();
- $date_fields_map = $this->fields_map->by_type('date');
- foreach ($fields as $key => $field ) {
- $field = trim($field);
- if ( in_array($field, $date_fields_map) ) {
- $date_fields[] = array('pos' => $key, 'field' => $field);
- }
- }
- // we have date fields to check
- if ( count($date_fields) > 0 ) {
- // we need to get the values
- $values_pos = stripos($query, 'VALUES');
- $first_paren = stripos($query, '(', $values_pos);
- $last_paren = $this->get_matching_paren($query, ($first_paren + 1));
- $values = explode(',',substr($query, ($first_paren+1), ($last_paren-($first_paren+1))));
- foreach ( $date_fields as $df ) {
- $v = trim($values[$df['pos']]);
- $quote = ( stripos($v, "'0000-00-00 00:00:00'") === 0 || $v === "''" ) ? "'" : '';
- if ( stripos($v, '0000-00-00 00:00:00') === 0
- || stripos($v, "'0000-00-00 00:00:00'") === 0
- || $v === "''" ) {
- if ( stripos($df['field'], 'gmt') > 0 ) {
- $v = $quote.gmdate('Y-m-d H:i:s').$quote;
- } else {
- $v = $quote.date('Y-m-d H:i:s').$quote;
- }
- }
- $values[$df['pos']] = $v;
- }
- $str = implode(',', $values);
- $query = substr_replace($query, $str, ($first_paren+1), ($last_paren-($first_paren+1)));
- }
- }
- return $query;
- }
- /**
- * The data types text and varchar are incompatible in the equal to operator.
- * TODO: Have a check for the appropriate table of the field to avoid collision
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_incompat_data_type($query)
- {
- // Lets check to make sure this is a SELECT query.
- if ( stripos($query, 'SELECT') === 0 || stripos($query, 'DELETE') === 0 ) {
- $operators = array(
- '=' => 'LIKE',
- '!=' => 'NOT LIKE',
- '<>' => 'NOT LIKE'
- );
- foreach($this->fields_map->read() as $table => $table_fields) {
- if (is_array($table_fields)) {
- foreach ($table_fields as $field => $field_meta) {
- if ( $field_meta['type'] == 'text' ) {
- foreach($operators as $oper => $val) {
- $query = str_ireplace(
- $table . '.' . $field . ' ' . $oper,
- $table . '.' . $field . ' ' . $val,
- $query
- );
- $query = str_ireplace($field . ' ' . $oper, $field . ' ' . $val, $query);
- // check for integers to cast.
- $query = preg_replace('/\s+LIKE\s*(\d+)/i', " {$val} cast($1 as varchar(max))", $query);
- }
- }
- }
- }
- }
- }
- return $query;
- }
- /**
- * General create/alter query translations
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Translated Query
- */
- function translate_create_queries($query)
- {
- if ( stripos($query, 'CREATE') !== 0 && stripos($query, 'ALTER') !== 0 ) {
- return $query;
- }
- // deal with alters in a bit
- if (stripos($query, 'ALTER') === 0) {
- return $query;
- }
-
- // fix enum as it doesn't exist in T-SQL
- if (stripos($query, 'enum(') !== false) {
- $enums = array_reverse($this->stripos_all($query, 'enum('));
- foreach ($enums as $start_pos) {
- $end = $this->get_matching_paren($query, $start_pos + 5);
- // get values inside enum
- $values = substr($query, $start_pos + 5, ($end - ($start_pos + 5)));
- $values = explode(',', $values);
- $all_int = true;
- foreach ($values as $value) {
- $val = trim(str_replace("'", '', $value));
- if (!is_numeric($val) || (int) $val != $val) {
- $all_int = false;
- }
- }
- // if enum of ints create an appropriate int column otherwise create a varchar
- if ($all_int) {
- $query = substr_replace($query, 'smallint', $start_pos, ($end + 1) - $start_pos);
- } else {
- $query = substr_replace($query, 'varchar(255)', $start_pos, ($end + 1) - $start_pos);
- }
- }
- }
-
- // remove IF NOT EXISTS as that doesn't exist in T-SQL
- $query = str_ireplace(' IF NOT EXISTS', '', $query);
-
- //save array to file_maps
- $this->fields_map->update_for($query);
- // change auto increment to indentity
- $start_positions = array_reverse($this->stripos_all($query, 'auto_increment'));
- if( stripos($query, 'auto_increment') > 0 ) {
- foreach ($start_positions as $start_pos) {
- $query = substr_replace($query, 'IDENTITY(1,1)', $start_pos, 14);
- }
- }
- if(stripos($query, 'AFTER') > 0) {
- $start_pos = stripos($query, 'AFTER');
- $query = substr($query, 0, $start_pos);
- }
- // replacement of certain data types and functions
- $fields = array(
- 'int (',
- 'int(',
- 'index (',
- 'index(',
- );
- foreach ( $fields as $field ) {
- // reverse so that when we make changes it wont effect the next change.
- $start_positions = array_reverse($this->stripos_all($query, $field));
- foreach ($start_positions as $start_pos) {
- $first_paren = stripos($query, '(', $start_pos);
- $end_pos = $this->get_matching_paren($query, $first_paren + 1) + 1;
- if( $field == 'index(' || $field == 'index (' ) {
- $query = substr_replace($query, '', $start_pos, $end_pos - $start_pos);
- } else {
- $query = substr_replace($query, rtrim(rtrim($field,'('), ' '), $start_pos, ($end_pos - $start_pos));
- }
- }
- }
- $query = str_ireplace("'0000-00-00 00:00:00'", 'getdate()', $query);
- // strip unsigned
- $query = str_ireplace("unsigned ", '', $query);
- // strip collation, engine type, etc from end of query
- $pos = stripos($query, '(', stripos($query, 'TABLE '));
- $end = $this->get_matching_paren($query, $pos + 1);
- $query = substr_replace($query, ');', $end);
- $query = str_ireplace("DEFAULT CHARACTER SET utf8", '', $query);
- $query = str_ireplace(" CHARACTER SET utf8", '', $query);
-
- // add collation
- $ac_types = array('tinytext', 'longtext', 'mediumtext', 'varchar');
- foreach ($ac_types as $ac_type) {
- $start_positions = array_reverse($this->stripos_all($query, $ac_type));
- foreach ($start_positions as $start_pos) {
- if ($ac_type == 'varchar') {
- $end = $this->get_matching_paren($query, $start_pos + (strlen($ac_type) + 1));
- $sub = substr($query, $end + 2, 7);
- $end_pos = $end + 1;
- } else {
- $query = substr_replace($query, 'TEXT', $start_pos, strlen($ac_type));
- $sub = substr($query, $start_pos + 5, 7);
- $end_pos = $start_pos + 4;
- }
- if ($sub !== 'COLLATE') {
- $query = $this->add_collation($query, $end_pos);
- }
- }
- }
- $keys = array();
- $table_pos = stripos($query, ' TABLE ') + 6;
- $table = substr($query, $table_pos, stripos($query, '(', $table_pos) - $table_pos);
- $table = trim($table);
-
- $reserved_words = array('public');
- // get column names to check for reserved words to encapsulate with [ ]
- foreach($this->fields_map->read() as $table_name => $table_fields) {
- if ($table_name == $table && is_array($table_fields)) {
- foreach ($table_fields as $field => $field_meta) {
- if (in_array($field, $reserved_words)) {
- $query = str_ireplace($field, "[{$field}]", $query);
- }
- }
- }
- }
- // get primary key constraints
- if ( stripos($query, 'PRIMARY KEY') > 0) {
- $start_positions = $this->stripos_all($query, 'PRIMARY KEY');
- foreach ($start_positions as $start_pos) {
- $start = stripos($query, '(', $start_pos);
- $end_paren = $this->get_matching_paren($query, $start + 1);
- $field = explode(',', substr($query, $start + 1, $end_paren - ($start + 1)));
- foreach ($field as $k => $v) {
- if (stripos($v, '(') !== false) {
- $field[$k] = preg_replace('/\(.*\)/', '', $v);
- }
- }
- $keys[] = array('type' => 'PRIMARY KEY', 'pos' => $start_pos, 'field' => $field);
- }
- }
- // get unique key constraints
- if ( stripos($query, 'UNIQUE KEY') > 0) {
- $start_positions = $this->stripos_all($query, 'UNIQUE KEY');
- foreach ($start_positions as $start_pos) {
- $start = stripos($query, '(', $start_pos);
- $end_paren = $this->get_matching_paren($query, $start + 1);
- $field = explode(',', substr($query, $start + 1, $end_paren - ($start + 1)));
- foreach ($field as $k => $v) {
- if (stripos($v, '(') !== false) {
- $field[$k] = preg_replace('/\(.*\)/', '', $v);
- }
- }
- $keys[] = array('type' => 'UNIQUE KEY', 'pos' => $start_pos, 'field' => $field);
- }
- }
- // get key constraints
- if ( stripos($query, 'KEY') > 0) {
- $start_positions = $this->stripos_all($query, 'KEY');
- foreach ($start_positions as $start_pos) {
- if (substr($query, $start_pos - 7, 6) !== 'UNIQUE'
- && substr($query, $start_pos - 8, 7) !== 'PRIMARY'
- && (substr($query, $start_pos - 1, 1) == ' ' || substr($query, $start_pos - 1, 1) == "\n")) {
- $start = stripos($query, '(', $start_pos);
- $end_paren = $this->get_matching_paren($query, $start + 1);
- $field = explode(',', substr($query, $start + 1, $end_paren - ($start + 1)));
- foreach ($field as $k => $v) {
- if (stripos($v, '(') !== false) {
- $field[$k] = preg_replace('/\(.*\)/', '', $v);
- }
- }
- $keys[] = array('type' => 'KEY', 'pos' => $start_pos, 'field' => $field);
- }
- }
- }
- $count = count($keys);
- $add_primary = false;
- $key_str = '';
- $lowest_start_pos = false;
- $unwanted = array(
- 'slug',
- 'name',
- 'term_id',
- 'taxonomy',
- 'term_taxonomy_id',
- 'comment_approved',
- 'comment_post_ID',
- 'comment_approved',
- 'link_visible',
- 'post_id',
- 'meta_key',
- 'post_type',
- 'post_status',
- 'post_date',
- 'ID',
- 'post_name',
- 'post_parent',
- 'user_login',
- 'user_nicename',
- 'user_id',
- );
- for ($i = 0; $i < $count; $i++) {
- if ($keys[$i]['pos'] < $lowest_start_pos || $lowest_start_pos === false) {
- $lowest_start_pos = $keys[$i]['pos'];
- }
- if ($keys[$i]['type'] == 'PRIMARY KEY') {
- $add_primary = true;
- }
- switch ($keys[$i]['type']) {
- case 'PRIMARY KEY':
- $str = "CONSTRAINT [" . $table . "_" . implode('_', $keys[$i]['field']) . "] PRIMARY KEY CLUSTERED (" . implode(',', $keys[$i]['field']) . ") WITH (IGNORE_DUP_KEY = OFF)";
- if (!$this->azure ) {
- $str .= " ON [PRIMARY]";
- }
- break;
- case 'UNIQUE KEY':
- $check = true;
- foreach ($keys[$i]['field'] as $field) {
- if (in_array($field, $unwanted)) {
- $check = false;
- }
- }
- if ($check) {
- if ($this->azure) {
- $str = 'CONSTRAINT [' . $table . '_' . implode('_', $keys[$i]['field']) . '] UNIQUE NONCLUSTERED (' . implode(',', $keys[$i]['field']) . ')';
- } else {
- $str = 'CONSTRAINT [' . $table . '_' . implode('_', $keys[$i]['field']) . '] UNIQUE NONCLUSTERED (' . implode(',', $keys[$i]['field']) . ')';
- }
- } else {
- $str = '';
- }
- break;
- case 'KEY':
- // CREATE NONCLUSTERED INDEX index_name ON table(col1,col2)
- $check = true;
- $str = '';
- foreach ($keys[$i]['field'] as $field) {
- if (in_array($field, $unwanted)) {
- $check = false;
- }
- }
- if ($check) {
- if (!is_array($this->following_query) && $this->following_query === false) {
- $this->following_query = array();
- } elseif (!is_array($this->following_query)) {
- $this->following_query = array($this->following_query);
- }
- if ($this->azure) {
- $this->following_query[] = 'CREATE CLUSTERED INDEX ' .
- $table . '_' . implode('_', $keys[$i]['field']) .
- ' ON '.$table.'('.implode(',', $keys[$i]['field']).')';
- } else {
- $this->following_query[] = 'CREATE NONCLUSTERED INDEX ' .
- $table . '_' . implode('_', $keys[$i]['field']) .
- ' ON '.$table.'('.implode(',', $keys[$i]['field']).')';
- }
- }
- break;
- }
- if ($i !== $count - 1 && $str !== '') {
- $str .= ',';
- }
- $key_str .= $str . "\n";
- }
- if ($key_str !== '') {
- if ($add_primary && !$this->azure) {
- $query = substr_replace($query, $key_str . ") ON [PRIMARY];", $lowest_start_pos);
- } else {
- $query = substr_replace($query, $key_str . ");", $lowest_start_pos);
- }
- }
- return $query;
- }
- /**
- * Given a first parenthesis ( ...will find its matching closing paren )
- *
- * @since 2.7.1
- *
- * @param string $str given string
- * @param int $start_pos position of where desired starting paren begins+1
- *
- * @return int position of matching ending parenthesis
- */
- function get_matching_paren($str, $start_pos)
- {
- $count = strlen($str);
- $bracket = 1;
- for ( $i = $start_pos; $i < $count; $i++ ) {
- if ( $str[$i] == '(' ) {
- $bracket++;
- } elseif ( $str[$i] == ')' ) {
- $bracket--;
- }
- if ( $bracket == 0 ) {
- return $i;
- }
- }
- }
- /**
- * Get the Aliases in a query
- * E.G. Field1 AS yyear, Field2 AS mmonth
- * will return array with yyear and mmonth
- *
- * @since 2.7.1
- *
- * @param string $str a query
- *
- * @return array array of aliases in a query
- */
- function get_as_fields($query)
- {
- $arr = array();
- $tok = preg_split('/[\s,]+/', $query);
- $count = count($tok);
- for ( $i = 0; $i < $count; $i++ ) {
- if ( strtolower($tok[$i]) === 'as' ) {
- $arr[] = $tok[($i + 1)];
- }
- }
- return $arr;
- }
- /**
- * Fix for SQL Server returning null values with one space.
- * Fix for SQL Server returning datetime fields with milliseconds.
- * Fix for SQL Server returning integer fields as integer (mysql returns as string)
- *
- * @since 2.7.1
- *
- * @param array $result_set result set array of an executed query
- *
- * @return array result set array with modified fields
- */
- function fix_results($result_set)
- {
- // If empty bail early.
- if ( is_null($result_set)) {
- return false;
- }
- if (is_array($result_set) && empty($result_set)) {
- return array();
- }
- $map_fields = $this->fields_map->by_type('date');
- $fields = array_keys(get_object_vars(current($result_set)));
- foreach ( $result_set as $key => $result ) {
- // Remove milliseconds
- foreach ( $map_fields as $date_field ) {
- if ( isset($result->$date_field) ) {
- // date_format is a PHP5 function. sqlsrv is only PHP5 compat
- // the result set for datetime columns is a PHP DateTime object, to extract
- // the string we need to use date_format().
- if (is_object($result->$date_field)) {
- $result_set[$key]->$date_field = date_format($result->$date_field, 'Y-m-d H:i:s');
- }
- }
- }
- // Check for null values being returned as space and change integers to strings (to mimic mysql results)
- foreach ( $fields as $field ) {
- if ($field == 'crdate' || $field == 'refdate') {
- $result_set[$key]->$field = date_format($result->$field, 'Y-m-d H:i:s');
- }
- if ( $result->$field === ' ' ) {
- $result->$field = '';
- }
- if ( is_int($result->$field) ) {
- $result->$field = (string) $result->$field;
- }
- }
- }
- return $result_set;
- }
-
- /**
- * Check to see if INSERT has an ON DUPLICATE KEY statement
- * This is MySQL specific and will be removed and put into
- * a following_query UPDATE STATEMENT
- *
- * @param string $query Query coming in
- * @return string query without ON DUPLICATE KEY statement
- */
- function on_duplicate_key($query)
- {
- if ( stripos($query, 'ON DUPLICATE KEY UPDATE') > 0 ) {
- $table = substr($query, 12, (strpos($query, ' ', 12) - 12));
- // currently just deal with wp_options table
- if (stristr($table, 'options') !== FALSE) {
- $start_pos = stripos($query, 'ON DUPLICATE KEY UPDATE');
- $query = substr_replace($query, '', $start_pos);
- $values_pos = stripos($query, 'VALUES');
- $first_paren = stripos($query, '(', $values_pos);
- $last_paren = $this->get_matching_paren($query, $first_paren + 1);
- $values = explode(',', substr($query, ($first_paren + 1), ($last_paren-($first_paren + 1))));
- // change this to use mapped fields
- $update = 'UPDATE ' . $table . ' SET option_value = ' . $values[1] . ', autoload = ' . $values[2] .
- ' WHERE option_name = ' . $values[0];
- $this->following_query = $update;
- }
- }
- return $query;
- }
- /**
- * Check to see if an INSERT query has multiple VALUES blocks. If so we need create
- * seperate queries for each.
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return array array of insert queries
- */
- function split_insert_values($query)
- {
- $arr = array();
- if (stripos($query, 'INSERT') === 0) {
- $first = substr($query, 0, (stripos($query, 'VALUES') + 7));
- $values = substr($query, (stripos($query, 'VALUES') + 7));
- $arr = preg_split('/\),\s+\(/', $values);
- foreach ($arr as $k => $v) {
- if (substr($v, -1) !== ')') {
- $v = $v . ')';
- }
-
- if (substr($v, 0, 1) !== '(') {
- $v = '(' . $v;
- }
-
- $arr[$k] = $first . $v;
- }
- }
- if (count($arr) < 2) {
- return $query;
- }
- return $arr;
- }
- /**
- * Check query to make sure translations weren't made to INSERT query values
- * If so replace translation with original data.
- * E.G. INSERT INTO wp_posts (wp_title) VALUES ('SELECT * FROM wp_posts LIMIT 1');
- * The translations may change the value data to SELECT TOP 1 FROM wp_posts...in this case
- * we don't want that to happen.
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Verified Query
- */
- function verify_insert($query)
- {
- $values_pos = stripos($query, 'VALUES');
- $first_paren = stripos($query, '(', $values_pos);
- $last_paren = $this->get_matching_paren($query, $first_paren + 1);
- $values = explode(',', substr($query, ($first_paren + 1), ($last_paren-($first_paren + 1))));
- if ( count($this->prepare_args) !== count($values) ) {
- return $query;
- }
- $i = 0;
- foreach ( $values as $k => $value ) {
- $value = trim($value);
- foreach ($this->prepare_args as $r => $arg) {
- if ( $k == $i && $arg !== $value ) {
- if ( $arg !== '' && $arg !== '0000-00-00 00:00:00' ) {
- $values[$k] = "'" . $arg . "'";
- }
- }
- $i++;
- }
- }
- $str = implode(',', $values);
- $query = substr_replace($query, $str, ($first_paren + 1), ($last_paren - ($first_paren + 1)));
- return $query;
- }
- /**
- * Check query to make sure translations weren't made to UPDATE query values
- * If so replace translation with original data.
- * E.G. UPDATE wp_posts SET post_title = 'SELECT * FROM wp_posts LIMIT 1' WHERE post_id = 1;
- * The translations may change the value data to SELECT TOP 1 FROM wp_posts...in this case
- * we don't want that to happen
- *
- * @since 2.7.1
- *
- * @param string $query Query coming in
- *
- * @return string Verified Query
- */
- function verify_update($query)
- {
- if(empty($this->update_data)) {
- return $query;
- }
- $values = array();
- $keys = array_keys($this->update_data);
- $start = stripos($query, 'SET') + 3;
- $end = strripos($query, 'WHERE');
- $sub = substr($query, $start, $end - $start);
- $arr = explode(', ', $sub);
- foreach ( $arr as $k => $v ) {
- $v = trim($v);
- $st = stripos($v, ' =');
- $sv = substr($v, 0, $st);
- if ( in_array($sv, $keys) ) {
- $sp = substr($v, $st + 4, -1);
- $values[] = str_replace("'", "''", $sp);
- }
- }
- $update_data = array_values($this->update_data);
- if ( count($update_data) == count($values) ) {
- foreach ( $update_data as $key => $val ) {
- if ( $update_data[$key] !== $values[$key] ) {
- $values[$key] = str_replace("''", "'", $update_data[$key]);
- }
- }
- foreach ( $values as $y => $vt ) {
- $values[$y] = $keys[$y] . " = '" . $vt . "'";
- }
- $str = implode(', ', $values) . ' ';
- $query = substr_replace($query, $str, ($start+1), ($end-($start+1)));
- }
- return $query;
- }
- /**
- * Add collation for a field definition within a CREATE/ALTER query
- *
- * @since 2.8
- * @param $type
- *
- * @return string
- */
- function add_collation($query, $pos)
- {
- $query = substr_replace($query, ' COLLATE Latin1_General_BIN', $pos, 0);
- return $query;
- }
-
- /**
- * Describe wrapper
- *
- * @since 2.8.5
- * @param $table
- *
- * @return string
- */
- function describe($table)
- {
- $sql = "SELECT
- c.name AS Field
- ,t.name + t.length_string AS Type
- ,CASE c.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END AS [Null]
- ,CASE
- WHEN EXISTS (SELECT * FROM sys.key_constraints AS kc
- INNER JOIN sys.index_columns AS ic ON kc.unique_index_id = ic.index_id AND kc.parent_object_id = ic.object_id
- WHERE kc.type = 'PK' AND ic.column_id = c.column_id AND c.object_id = ic.object_id)
- THEN 'PRI'
- WHEN EXISTS (SELECT * FROM sys.key_constraints AS kc
- INNER JOIN sys.index_columns AS ic ON kc.unique_index_id = ic.index_id AND kc.parent_object_id = ic.object_id
- WHERE kc.type <> 'PK' AND ic.column_id = c.column_id AND c.object_id = ic.object_id)
- THEN 'UNI'
- ELSE ''
- END AS [Key]
- ,ISNULL((
- SELECT TOP(1)
- dc.definition
- FROM sys.default_constraints AS dc
- WHERE dc.parent_column_id = c.column_id AND c.object_id = dc.parent_object_id)
- ,'') AS [Default]
- ,CASE
- WHEN EXISTS (
- SELECT
- *
- FROM sys.identity_columns AS ic
- WHERE ic.column_id = c.column_id AND c.object_id = ic.object_id)
- THEN 'auto_increment'
- ELSE ''
- END AS Extra
- FROM sys.columns AS c
- CROSS APPLY (
- SELECT
- t.name AS n1
- ,CASE
- -- Types with length
- WHEN c.max_length > 0 AND t.name IN ('varchar', 'char', 'varbinary', 'binary') THEN '(' + CAST(c.max_length AS VARCHAR) + ')'
- WHEN c.max_length > 0 AND t.name IN ('nvarchar', 'nchar') THEN '(' + CAST(c.max_length/2 AS VARCHAR) + ')'
- WHEN c.max_length < 0 AND t.name IN ('nvarchar', 'varchar', 'varbinary') THEN '(max)'
- -- Types with precision & scale
- WHEN t.name IN ('decimal', 'numeric') THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
- -- Types with only precision
- WHEN t.name IN ('float') THEN '(' + CAST(c.precision AS VARCHAR) + ')'
- -- Types with only scale
- WHEN t.name IN ('datetime2', 'time', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR) + ')'
- -- The rest take no arguments
- ELSE ''
- END AS length_string
- ,*
- FROM sys.types AS t
- WHERE t.system_type_id = c.system_type_id AND t.system_type_id = t.user_type_id
- ) AS t
- WHERE object_id = OBJECT_ID('{$table}');";
- return $sql;
- }
- /**
- * Get all occurrences(positions) of a string within a string
- *
- * @since 2.8
- * @param $type
- *
- * @return array
- */
- function stripos_all($haystack, $needle, $offset = 0)
- {
- $arr = array();
- while ($offset !== false) {
- $pos = stripos($haystack, $needle, $offset);
- if ($pos !== false) {
- $arr[] = $pos;
- $pos = $pos + str…
Large files files are truncated, but you can click here to view the full file