<?php

[0] Fix | Delete

[1] Fix | Delete

if (!defined('UPDRAFTPLUS_DIR')) die('No direct access allowed');

[2] Fix | Delete

[3] Fix | Delete

class UpdraftPlus_Database_Utility {

[4] Fix | Delete

[5] Fix | Delete

private $whichdb;

[6] Fix | Delete

[7] Fix | Delete

private $table_prefix_raw;

[8] Fix | Delete

[9] Fix | Delete

private $dbhandle;

[10] Fix | Delete

[11] Fix | Delete

/**

[12] Fix | Delete

* Constructor

[13] Fix | Delete

*/

[14] Fix | Delete

/**

[15] Fix | Delete

* Constructor

[16] Fix | Delete

*

[17] Fix | Delete

* @param String $whichdb - which database is being backed up

[18] Fix | Delete

* @param String $table_prefix_raw - the base table prefix

[19] Fix | Delete

* @param Object $dbhandle - WPDB object

[20] Fix | Delete

*/

[21] Fix | Delete

public function __construct($whichdb, $table_prefix_raw, $dbhandle) {

[22] Fix | Delete

$this->whichdb = $whichdb;

[23] Fix | Delete

$this->table_prefix_raw = $table_prefix_raw;

[24] Fix | Delete

$this->dbhandle = $dbhandle;

[25] Fix | Delete

}

[26] Fix | Delete

[27] Fix | Delete

/**

[28] Fix | Delete

* The purpose of this function is to make sure that the options table is put in the database first, then the users table, then the site + blogs tables (if present - multisite), then the usermeta table; and after that the core WP tables - so that when restoring we restore the core tables first

[29] Fix | Delete

*

[30] Fix | Delete

* @param Array $a_arr the first array

[31] Fix | Delete

* @param Array $b_arr the second array

[32] Fix | Delete

*

[33] Fix | Delete

* @return Integer - the sort result, according to the rules of PHP custom sorting functions

[34] Fix | Delete

*/

[35] Fix | Delete

public function backup_db_sorttables($a_arr, $b_arr) {

[36] Fix | Delete

[37] Fix | Delete

$a = $a_arr['name'];

[38] Fix | Delete

$a_table_type = $a_arr['type'];

[39] Fix | Delete

$b = $b_arr['name'];

[40] Fix | Delete

$b_table_type = $b_arr['type'];

[41] Fix | Delete

[42] Fix | Delete

// Views must always go after tables (since they can depend upon them)

[43] Fix | Delete

if ('VIEW' == $a_table_type && 'VIEW' != $b_table_type) return 1;

[44] Fix | Delete

if ('VIEW' == $b_table_type && 'VIEW' != $a_table_type) return -1;

[45] Fix | Delete

[46] Fix | Delete

if ('wp' != $this->whichdb) return strcmp($a, $b);

[47] Fix | Delete

[48] Fix | Delete

global $updraftplus;

[49] Fix | Delete

if ($a == $b) return 0;

[50] Fix | Delete

$our_table_prefix = $this->table_prefix_raw;

[51] Fix | Delete

if ($a == $our_table_prefix.'options') return -1;

[52] Fix | Delete

if ($b == $our_table_prefix.'options') return 1;

[53] Fix | Delete

if ($a == $our_table_prefix.'site') return -1;

[54] Fix | Delete

if ($b == $our_table_prefix.'site') return 1;

[55] Fix | Delete

if ($a == $our_table_prefix.'blogs') return -1;

[56] Fix | Delete

if ($b == $our_table_prefix.'blogs') return 1;

[57] Fix | Delete

if ($a == $our_table_prefix.'users') return -1;

[58] Fix | Delete

if ($b == $our_table_prefix.'users') return 1;

[59] Fix | Delete

if ($a == $our_table_prefix.'usermeta') return -1;

[60] Fix | Delete

if ($b == $our_table_prefix.'usermeta') return 1;

[61] Fix | Delete

[62] Fix | Delete

if (empty($our_table_prefix)) return strcmp($a, $b);

[63] Fix | Delete

[64] Fix | Delete

try {

[65] Fix | Delete

$core_tables = array_merge($this->dbhandle->tables, $this->dbhandle->global_tables, $this->dbhandle->ms_global_tables);

[66] Fix | Delete

} catch (Exception $e) {

[67] Fix | Delete

$updraftplus->log($e->getMessage());

[68] Fix | Delete

}

[69] Fix | Delete

[70] Fix | Delete

if (empty($core_tables)) $core_tables = array('terms', 'term_taxonomy', 'termmeta', 'term_relationships', 'commentmeta', 'comments', 'links', 'postmeta', 'posts', 'site', 'sitemeta', 'blogs', 'blogversions', 'blogmeta');

[71] Fix | Delete

[72] Fix | Delete

$na = UpdraftPlus_Manipulation_Functions::str_replace_once($our_table_prefix, '', $a);

[73] Fix | Delete

$nb = UpdraftPlus_Manipulation_Functions::str_replace_once($our_table_prefix, '', $b);

[74] Fix | Delete

if (in_array($na, $core_tables) && !in_array($nb, $core_tables)) return -1;

[75] Fix | Delete

if (!in_array($na, $core_tables) && in_array($nb, $core_tables)) return 1;

[76] Fix | Delete

return strcmp($a, $b);

[77] Fix | Delete

}

[78] Fix | Delete

[79] Fix | Delete

/**

[80] Fix | Delete

* Detect if the table has a composite primary key (composed from multiple columns)

[81] Fix | Delete

*

[82] Fix | Delete

* @param String $table - table to examine

[83] Fix | Delete

* @param Object|Null $wpdb_obj - WPDB-like object (requires the get_results() method), or null to use the global default

[84] Fix | Delete

*

[85] Fix | Delete

* @return Boolean

[86] Fix | Delete

*/

[87] Fix | Delete

public static function table_has_composite_private_key($table, $wpdb_obj = null) {

[88] Fix | Delete

[89] Fix | Delete

$wpdb = (null === $wpdb_obj) ? $GLOBALS['wpdb'] : $wpdb_obj;

[90] Fix | Delete

[91] Fix | Delete

$table_structure = $wpdb->get_results("DESCRIBE ".UpdraftPlus_Manipulation_Functions::backquote($table));

[92] Fix | Delete

if (!$table_structure) return false;

[93] Fix | Delete

[94] Fix | Delete

$primary_key_columns_found = 0;

[95] Fix | Delete

[96] Fix | Delete

foreach ($table_structure as $struct) {

[97] Fix | Delete

if (isset($struct->Key) && 'PRI' == $struct->Key) {

[98] Fix | Delete

$primary_key_columns_found++;

[99] Fix | Delete

if ($primary_key_columns_found > 1) return true;

[100] Fix | Delete

}

[101] Fix | Delete

}

[102] Fix | Delete

[103] Fix | Delete

return false;

[104] Fix | Delete

}

[105] Fix | Delete

[106] Fix | Delete

/**

[107] Fix | Delete

* Set MySQL server system variable

[108] Fix | Delete

*

[109] Fix | Delete

* @param String $variable The name of the system variable

[110] Fix | Delete

* @param String $value The variable value

[111] Fix | Delete

* @param Resource|Object $db_handle The database link identifier(resource) given by mysqli_init or mysql_connect

[112] Fix | Delete

* @return Boolean Returns true on success, false otherwise

[113] Fix | Delete

*/

[114] Fix | Delete

public static function set_system_variable($variable, $value, $db_handle) {

[115] Fix | Delete

[116] Fix | Delete

$is_mysqli = is_a($db_handle, 'mysqli');

[117] Fix | Delete

if (!is_resource($db_handle) && !$is_mysqli) return false;

[118] Fix | Delete

[119] Fix | Delete

$sql = "SET SESSION %s='%s'";

[120] Fix | Delete

if ($is_mysqli) {

[121] Fix | Delete

// @codingStandardsIgnoreLine

[122] Fix | Delete

$res = @mysqli_query($db_handle, sprintf($sql, mysqli_real_escape_string($db_handle, $variable), mysqli_real_escape_string($db_handle, $value)));

[123] Fix | Delete

} else {

[124] Fix | Delete

// @codingStandardsIgnoreLine

[125] Fix | Delete

$res = @mysql_query(sprintf($sql, mysql_real_escape_string($variable, $db_handle), mysql_real_escape_string($value, $db_handle)), $db_handle);

[126] Fix | Delete

}

[127] Fix | Delete

[128] Fix | Delete

return $res;

[129] Fix | Delete

}

[130] Fix | Delete

[131] Fix | Delete

/**

[132] Fix | Delete

* Get MySQL server system variable.

[133] Fix | Delete

*

[134] Fix | Delete

* @param String $variable The name of the system variable

[135] Fix | Delete

* @param Resource|Object $db_handle The database link identifier(resource) given by mysqli_init or mysql_connect

[136] Fix | Delete

* @return String|Boolean|Null Returns value of the system variable, false on query failure or null if there is no result for the corresponding variable

[137] Fix | Delete

*/

[138] Fix | Delete

public static function get_system_variable($variable, $db_handle) {

[139] Fix | Delete

[140] Fix | Delete

$is_mysqli = is_a($db_handle, 'mysqli');

[141] Fix | Delete

if (!is_resource($db_handle) && !$is_mysqli) return false;

[142] Fix | Delete

[143] Fix | Delete

$sql = 'SELECT @@SESSION.%s';

[144] Fix | Delete

[145] Fix | Delete

if ($is_mysqli) {

[146] Fix | Delete

// @codingStandardsIgnoreLine

[147] Fix | Delete

$res = @mysqli_query($db_handle, sprintf($sql, mysqli_real_escape_string($db_handle, $variable)));

[148] Fix | Delete

} else {

[149] Fix | Delete

// @codingStandardsIgnoreLine

[150] Fix | Delete

$res = @mysql_query(sprintf($sql, mysql_real_escape_string($variable, $db_handle)), $db_handle);

[151] Fix | Delete

}

[152] Fix | Delete

if (false === $res) {

[153] Fix | Delete

return $res;

[154] Fix | Delete

}

[155] Fix | Delete

if ($is_mysqli) {

[156] Fix | Delete

// @codingStandardsIgnoreLine

[157] Fix | Delete

$res = mysqli_fetch_array($res);

[158] Fix | Delete

return isset($res[0]) ? $res[0] : null;

[159] Fix | Delete

} else {

[160] Fix | Delete

// @codingStandardsIgnoreLine

[161] Fix | Delete

$res = mysql_result($res, 0);

[162] Fix | Delete

return false === $res ? null : $res;

[163] Fix | Delete

}

[164] Fix | Delete

}

[165] Fix | Delete

[166] Fix | Delete

/**

[167] Fix | Delete

*

[168] Fix | Delete

* This function is adapted from the set_sql_mode() method in WordPress wpdb class but with few modifications applied, this can be used to switch between different sets of SQL modes.

[169] Fix | Delete

*

[170] Fix | Delete

* @see https://developer.wordpress.org/reference/classes/wpdb/set_sql_mode/

[171] Fix | Delete

* @see https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

[172] Fix | Delete

* @see https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

[173] Fix | Delete

* @see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

[174] Fix | Delete

* @see https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_mode

[175] Fix | Delete

* @see https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode

[176] Fix | Delete

* @see https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode

[177] Fix | Delete

* @see https://mariadb.com/kb/en/library/sql-mode/#strict-mode

[178] Fix | Delete

* @see https://mariadb.com/kb/en/library/sql-mode/#setting-sql_mode

[179] Fix | Delete

*

[180] Fix | Delete

* @param Array $modes - Optional. A list of SQL modes to set.

[181] Fix | Delete

* @param Array $remove_modes - modes to remove if they are currently active

[182] Fix | Delete

* @param Resource|Object|NULL $db_handle - Optional. If specified, it should either the valid database link identifier(resource) given by mysql(i) or null to instead use the global WPDB object, or a WPDB-compatible object.

[183] Fix | Delete

*/

[184] Fix | Delete

public static function set_sql_mode($modes = array(), $remove_modes = array(), $db_handle = null) {

[185] Fix | Delete

[186] Fix | Delete

global $updraftplus, $wpdb;

[187] Fix | Delete

[188] Fix | Delete

$wpdb_handle_if_used = (null !== $db_handle && is_a($db_handle, 'WPDB')) ? $db_handle : $wpdb;

[189] Fix | Delete

[190] Fix | Delete

// If any of these are set, they will be unset

[191] Fix | Delete

$strict_modes = array(

[192] Fix | Delete

// according to mariadb and mysql docs, strict mode can be one of these or both

[193] Fix | Delete

'STRICT_TRANS_TABLES',

[194] Fix | Delete

'STRICT_ALL_TABLES',

[195] Fix | Delete

);

[196] Fix | Delete

[197] Fix | Delete

$incompatible_modes = array_unique(array_merge(array(

[198] Fix | Delete

'NO_ZERO_DATE',

[199] Fix | Delete

'ONLY_FULL_GROUP_BY',

[200] Fix | Delete

'TRADITIONAL',

[201] Fix | Delete

), $strict_modes));

[202] Fix | Delete

[203] Fix | Delete

$class = get_class();

[204] Fix | Delete

[205] Fix | Delete

if (is_null($db_handle) || is_a($db_handle, 'WPDB')) {

[206] Fix | Delete

$initial_modes_str = $wpdb_handle_if_used->get_var('SELECT @@SESSION.sql_mode');

[207] Fix | Delete

} else {

[208] Fix | Delete

$initial_modes_str = call_user_func_array(array($class, 'get_system_variable'), array('sql_mode', $db_handle));

[209] Fix | Delete

}

[210] Fix | Delete

if (is_scalar($initial_modes_str) && !is_bool($initial_modes_str)) {

[211] Fix | Delete

$modes = array_unique(array_merge($modes, array_change_key_case(explode(',', $initial_modes_str), CASE_UPPER)));

[212] Fix | Delete

} else {

[213] Fix | Delete

$updraftplus->log("Couldn't get the sql_mode value (".serialize($initial_modes_str)."); will not attempt any adjustment");

[214] Fix | Delete

return;

[215] Fix | Delete

}

[216] Fix | Delete

[217] Fix | Delete

$modes = array_change_key_case($modes, CASE_UPPER);

[218] Fix | Delete

[219] Fix | Delete

$unwanted_modes = array_merge($incompatible_modes, $remove_modes);

[220] Fix | Delete

[221] Fix | Delete

foreach ($modes as $i => $mode) {

[222] Fix | Delete

if (in_array($mode, $unwanted_modes)) {

[223] Fix | Delete

unset($modes[$i]);

[224] Fix | Delete

}

[225] Fix | Delete

}

[226] Fix | Delete

[227] Fix | Delete

$modes_str = implode(',', $modes);

[228] Fix | Delete

[229] Fix | Delete

if (is_null($db_handle) || is_a($db_handle, 'WPDB')) {

[230] Fix | Delete

$res = $wpdb_handle_if_used->query($wpdb_handle_if_used->prepare("SET SESSION sql_mode = %s", $modes_str));

[231] Fix | Delete

} else {

[232] Fix | Delete

$res = call_user_func_array(array($class, 'set_system_variable'), array('sql_mode', $modes_str, $db_handle));

[233] Fix | Delete

}

[234] Fix | Delete

[235] Fix | Delete

if (isset($initial_modes_str) && false == array_diff(explode(',', $initial_modes_str), $modes)) {

[236] Fix | Delete

$updraftplus->log("SQL compatibility mode is: $modes_str");

[237] Fix | Delete

} else {

[238] Fix | Delete

$updraftplus->log("SQL compatibility mode".((false === $res) ? " not" : "")." successfully changed".(isset($initial_modes_str) ? " from $initial_modes_str" : "")." to $modes_str");

[239] Fix | Delete

}

[240] Fix | Delete

}

[241] Fix | Delete

[242] Fix | Delete

/**

[243] Fix | Delete

* Parse the SQL "create table" column definition (non validating) and check whether it's a generated column and retrieve its column options

[244] Fix | Delete

*

[245] Fix | Delete

* @see https://dev.mysql.com/doc/refman/8.0/en/create-table.html

[246] Fix | Delete

* @see https://mariadb.com/kb/en/create-table/

[247] Fix | Delete

*

[248] Fix | Delete

* @param String $table_column_definition the column definition statement in which the generated column needs to be identified

[249] Fix | Delete

* @param Integer $starting_offset the string position of the column definition in a "create table" statement

[250] Fix | Delete

* @return Array|False an array of generated column fragment (column definition, column name, generated column type, etc); false otherwise

[251] Fix | Delete

*

[252] Fix | Delete

* Example input:

[253] Fix | Delete

*

[254] Fix | Delete

* $column_definition = "fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) VIRTUAL NOT NULL COMMENT 'this is the comment',"

[255] Fix | Delete

*

[256] Fix | Delete

* Corresponding result:

[257] Fix | Delete

*

[258] Fix | Delete

* [

[259] Fix | Delete

* "column_definition" => "fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) VIRTUAL NOT NULL COMMENT 'this is the comment',",

[260] Fix | Delete

* "column_name" => "fullname",

[261] Fix | Delete

* "column_data_type_definition" => [

[262] Fix | Delete

* [

[263] Fix | Delete

* "GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name))",

[264] Fix | Delete

* 90

[265] Fix | Delete

* ],

[266] Fix | Delete

* [

[267] Fix | Delete

* "VIRTUAL NOT NULL",

[268] Fix | Delete

* 123 // string position

[269] Fix | Delete

* ],

[270] Fix | Delete

* [

[271] Fix | Delete

* "COMMENT 'this is the comment'",

[272] Fix | Delete

* 345 // string position

[273] Fix | Delete

* ]

[274] Fix | Delete

* ],

[275] Fix | Delete

* "is_virtual" => true

[276] Fix | Delete

* ]

[277] Fix | Delete

*/

[278] Fix | Delete

public static function get_generated_column_info($table_column_definition, $starting_offset) {

[279] Fix | Delete

[280] Fix | Delete

// check whether or not the column definition ($table_column_definition) is a generated column, if so then get all the column definitions

[281] Fix | Delete

// https://regex101.com/r/Fy2Bkd/12

[282] Fix | Delete

if (preg_match_all('/^\s*\`((?:[^`]|``)+)\`([^,\'"]+?)(?:((?:GENERATED\s*ALWAYS\s*)?AS\s*\(.+\))([\w\s]*)(COMMENT\s*(?:\'(?:[^\']|\'\')*\'|\"(?:[^"]|"")*\"))([\w\s]*)|((?:GENERATED\s*ALWAYS\s*)?AS\s*\(.+\)([\w\s]*)))/i', $table_column_definition, $column_definitions, PREG_SET_ORDER | PREG_OFFSET_CAPTURE)) {

[283] Fix | Delete

[284] Fix | Delete

if (empty($column_definitions)) return false;

[285] Fix | Delete

[286] Fix | Delete

/**

[287] Fix | Delete

* If the above preg_match_all function succeed, it returns an array with the following format:

[288] Fix | Delete

*

[289] Fix | Delete

* Array(3) {

[290] Fix | Delete

* [0]=> // 1st set of the matched/captured string

[291] Fix | Delete

* array(6) {

[292] Fix | Delete

* [0]=> // 1st index represents a full column definition

[293] Fix | Delete

* array(2) {

[294] Fix | Delete

* [0]=> string(131) ", `full_name` char(41) GENERATED ALWAYS AS (concat(`firstname`,'()`)(()',`lastname`)) VIRTUAL NOT NULL COMMENT 'fu(ll)'_name'' COLUMN_FORMAT DEFAULT"

[295] Fix | Delete

* [1]=> int(541)

[296] Fix | Delete

* }

[297] Fix | Delete

* [1]=> // 2nd index represents a column name

[298] Fix | Delete

* array(2) {

[299] Fix | Delete

* [0]=> string(9) "full_name"

[300] Fix | Delete

* [1]=> int(547)

[301] Fix | Delete

* }

[302] Fix | Delete

* [2]=> // 3rd index represents data type option that is captured before "generated always as"

[303] Fix | Delete

* array(2) {

[304] Fix | Delete

* [0]=> string(18) " char(41) "

[305] Fix | Delete

* [1]=> int(555)

[306] Fix | Delete

* }

[307] Fix | Delete

* [3]=> // 4rd index represents data type option which is specific for "generated always as"

[308] Fix | Delete

* array(2) {

[309] Fix | Delete

* [0]=> string(18) "GENERATED ALWAYS AS (concat(`firstname`,'()`)(()',`lastname`))"

[310] Fix | Delete

* [1]=> int(629) // this is the position or starting offset of the captured data type's option, this can later be used to help with the unsupported keyword replacement stuff among db server

[311] Fix | Delete

* }

[312] Fix | Delete

* [4]=> // 5th index represents data type option that is captured before COMMENT keyword and after "generated alwasy as"

[313] Fix | Delete

* array(2) {

[314] Fix | Delete

* [0]=> string(13) " VIRTUAL NOT NULL " // this is the comment string that could be filled with any word even the reserved keyword (e.g. not null, virtual, stored, etc..)

[315] Fix | Delete

* [1]=> int(656) // this is the position or starting offset of the captured data type's option, this can later be used to help with the unsupported keyword replacement stuff among db server

[316] Fix | Delete

* }

[317] Fix | Delete

* [5]=> // 6th index represents the comment

[318] Fix | Delete

* array(2) {

[319] Fix | Delete

* [0]=> string(2) "COMMENT 'fu(ll)'_name''"

[320] Fix | Delete

* [1]=> int(670) // this is the position or starting offset of the captured comment's string

[321] Fix | Delete

* }

[322] Fix | Delete

* [6]=> // 7th index represents data type option that is captured after the COMMENT keyword

[323] Fix | Delete

* array(2) {

[324] Fix | Delete

* [0]=> string(2) "COLUMN_FORMAT DEFAULT"

[325] Fix | Delete

* [1]=> int(670)

[326] Fix | Delete

* }

[327] Fix | Delete

* }

[328] Fix | Delete

* array(8) { // 2nd set

[329] Fix | Delete

* [0]=>

[330] Fix | Delete

* array(2) {

[331] Fix | Delete

* [0]=> string(95) ", `full_name6` char(41) GENERATED ALWAYS AS (concat(`firstname`,' ',`lastname2`))STORED NULL"

[332] Fix | Delete

* [1]=> int(1121)

[333] Fix | Delete

* }

[334] Fix | Delete

* [1]=>

[335] Fix | Delete

* array(2) {

[336] Fix | Delete

* [0]=> string(10) "full_name6"

[337] Fix | Delete

* [1]=> int(1127)

[338] Fix | Delete

* }

[339] Fix | Delete

* [2]=>

[340] Fix | Delete

* array(2) {

[341] Fix | Delete

* [0]=> string(0) " char(41) "

[342] Fix | Delete

* [1]=> int(1139)

[343] Fix | Delete

* }

[344] Fix | Delete

* [3]=>

[345] Fix | Delete

* array(2) {

[346] Fix | Delete

* [0]=> string(0) ""

[347] Fix | Delete

* [1]=> int(-1)

[348] Fix | Delete

* }

[349] Fix | Delete

* [4]=>

[350] Fix | Delete

* array(2) {

[351] Fix | Delete

* [0]=> string(0) ""

[352] Fix | Delete

* [1]=> int(-1)

[353] Fix | Delete

* }

[354] Fix | Delete

* [5]=>

[355] Fix | Delete

* array(2) {

[356] Fix | Delete

* [0]=> string(0) "" // an empty string of this captured token indicates that the column definition doesn't have COMMENT keyword

[357] Fix | Delete

* [1]=> int(-1)

[358] Fix | Delete

* }

[359] Fix | Delete

* [6]=>

[360] Fix | Delete

* array(2) {

[361] Fix | Delete

* [0]=> string(0) ""

[362] Fix | Delete

* [1]=> int(-1)

[363] Fix | Delete

* }

[364] Fix | Delete

* [7]=> // 8th index will appear if there's no COMMENT keyword found in the column definition and it represents data type option that is specific for "generated always as"

[365] Fix | Delete

* array(2) {

[366] Fix | Delete

* [0]=> string(11) "GENERATED ALWAYS AS (concat(`firstname`,' ',`lastname2`))"

[367] Fix | Delete

* [1]=> int(1205)

[368] Fix | Delete

* }

[369] Fix | Delete

* [8]=> // 9th index will appear if there's no COMMENT keyword found in the column definition and it represents the captured data type options

[370] Fix | Delete

* array(2) {

[371] Fix | Delete

* [0]=> string(11) "STORED NULL"

[372] Fix | Delete

* [1]=> int(1270)

[373] Fix | Delete

* }

[374] Fix | Delete

* }

[375] Fix | Delete

* }

[376] Fix | Delete

*/

[377] Fix | Delete

[378] Fix | Delete

foreach ($column_definitions as $column_definition) {

[379] Fix | Delete

$data_type_definition = (!empty($column_definition[4][0]) ? $column_definition[4][0] : '').(!empty($column_definition[6][0]) ? $column_definition[6][0] : '').(!empty($column_definition[8][0]) ? $column_definition[8][0] : '');

[380] Fix | Delete

// if no virtual, stored or persistent option is specified then it's virtual by default. It's not possible having two generated columns type in the column definition e.g fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) VIRTUAL STORED NOT NULL COMMENT 'comment text', both MySQL and MariaDB will produces an error

[381] Fix | Delete

$is_virtual = preg_match('/\bvirtual\b/i', $data_type_definition) || (!preg_match('/\bstored\b/i', $data_type_definition) && !preg_match('/\bpersistent\b/i', $data_type_definition));

[382] Fix | Delete

[383] Fix | Delete

$fragment = array(

[384] Fix | Delete

// full syntax of the column definition

[385] Fix | Delete

"column_definition" => $column_definition[0][0],

[386] Fix | Delete

// the extracted column name

[387] Fix | Delete

"column_name" => $column_definition[1][0],

[388] Fix | Delete

'column_data_type_definition' => array(),

[389] Fix | Delete

"is_virtual" => $is_virtual,

[390] Fix | Delete

);

[391] Fix | Delete

if (!empty($column_definition[2])) {

[392] Fix | Delete

$fragment['column_data_type_definition']['DATA_TYPE_TOKEN'] = $column_definition[2];

[393] Fix | Delete

$fragment['column_data_type_definition']['DATA_TYPE_TOKEN'][1] = (int) $starting_offset + (int) $fragment['column_data_type_definition']['DATA_TYPE_TOKEN'][1];

[394] Fix | Delete

}

[395] Fix | Delete

if (!empty($column_definition[3])) {

[396] Fix | Delete

$fragment['column_data_type_definition']['GENERATED_ALWAYS_TOKEN'] = $column_definition[3];

[397] Fix | Delete

if (empty($fragment['column_data_type_definition'][1]) && !empty($column_definition[7][0])) $fragment['column_data_type_definition']['GENERATED_ALWAYS_TOKEN'] = $column_definition[7];

[398] Fix | Delete

$fragment['column_data_type_definition']['GENERATED_ALWAYS_TOKEN'][1] = (int) $starting_offset + (int) $fragment['column_data_type_definition']['GENERATED_ALWAYS_TOKEN'][1];

[399] Fix | Delete

}

[400] Fix | Delete

if (!empty($column_definition[4])) {

[401] Fix | Delete

$fragment['column_data_type_definition'][2] = $column_definition[4];

[402] Fix | Delete

$fragment['column_data_type_definition'][2][1] = (int) $starting_offset + (int) $fragment['column_data_type_definition'][2][1];

[403] Fix | Delete

}

[404] Fix | Delete

if (!empty($column_definition[5])) {

[405] Fix | Delete

$fragment['column_data_type_definition']['COMMENT_TOKEN'] = $column_definition[5];

[406] Fix | Delete

$fragment['column_data_type_definition']['COMMENT_TOKEN'][1] = (int) $starting_offset + (int) $fragment['column_data_type_definition']['COMMENT_TOKEN'][1];

[407] Fix | Delete

}

[408] Fix | Delete

if (!empty($column_definition[6])) {

[409] Fix | Delete

$fragment['column_data_type_definition'][4] = $column_definition[6];

[410] Fix | Delete

$fragment['column_data_type_definition'][4][1] = (int) $starting_offset + (int) $fragment['column_data_type_definition'][4][1];

[411] Fix | Delete

}

[412] Fix | Delete

if (!empty($column_definition[8])) {

[413] Fix | Delete

$fragment['column_data_type_definition'][5] = $column_definition[8];

[414] Fix | Delete

$fragment['column_data_type_definition'][5][1] = (int) $starting_offset + (int) $fragment['column_data_type_definition'][5][1];

[415] Fix | Delete

}

[416] Fix | Delete

}

[417] Fix | Delete

}

[418] Fix | Delete

return isset($fragment) ? $fragment : false;

[419] Fix | Delete

}

[420] Fix | Delete

[421] Fix | Delete

/**

[422] Fix | Delete

* Retrieve information concerning whether the currently running database server supports generated columns (VIRTUAL, STORED, PERSISTENT)

[423] Fix | Delete

*

[424] Fix | Delete

* @param String $engine Optional. If specified, it should either a well-known database engine like InnoDB, MyISAM, etc or an empty string to instead use database default storage engine; e.g. 'MyISAM'

[425] Fix | Delete

* @return Array|Boolean an array of supported generated column syntax options (whether or not persistent type, not null, virtual index are supported) or false if generated column isn't supported

[426] Fix | Delete

*

[427] Fix | Delete

* The return value is structured thus:

[428] Fix | Delete

*

[429] Fix | Delete

* [

[430] Fix | Delete

* // InnoDB supports PERSISTENT generated columns type, whereas MyISAM does not

[431] Fix | Delete

* "is_persistent_supported" => false,

[432] Fix | Delete

* // InnoDB supports NOT NULL constraint, whereas MyISAM does not

[433] Fix | Delete

* "is_not_null_supported" => true,

[434] Fix | Delete

* // if it's on MariaDB, you can use insert ignore statement to prevent generated columns errors but not on MySQL

[435] Fix | Delete

* "can_insert_ignore_to_generated_column" => true,

[436] Fix | Delete

* // No matter what the database engine you use, MySQL doesn't yet support indexing on generated columns

[437] Fix | Delete

* "is_virtual_index_supported" => false

[438] Fix | Delete

* ]

[439] Fix | Delete

*/

[440] Fix | Delete

public static function is_generated_column_supported($engine = '') {

[441] Fix | Delete

[442] Fix | Delete

global $table_prefix, $wpdb;

[443] Fix | Delete

[444] Fix | Delete

$random_table_name = $table_prefix.'updraft_tmp_'.rand(0, 9999999).md5(microtime(true));

[445] Fix | Delete

[446] Fix | Delete

$drop_statement = "DROP TABLE IF EXISTS `$random_table_name`;";

[447] Fix | Delete

[448] Fix | Delete

// both mysql and mariadb support generated column, virtual is the default type and the other option type is called stored, mariadb has an alias for stored type which is called persistent, whereas mysql doesn't have such thing.

[449] Fix | Delete

// MySQL supports NULL and NOT NULL constraints. On the other hand, MariaDB doesn't support it.

[450] Fix | Delete

$sql = array(

[451] Fix | Delete

"CREATE TABLE `$random_table_name` (`virtual_column` varchar(17) GENERATED ALWAYS AS ('virtual_column') VIRTUAL COMMENT 'virtual_column')".(!empty($engine) ? " ENGINE=$engine" : "").";",

[452] Fix | Delete

"ALTER TABLE `$random_table_name` ADD `persistent_column` VARCHAR(17) AS ('persistent_column') PERSISTENT COMMENT 'generated_column';",

[453] Fix | Delete

"ALTER TABLE `$random_table_name` ADD `virtual_column_not_null` VARCHAR(17) AS ('virtual_column_not_null') VIRTUAL NOT NULL COMMENT 'virtual_column_not_null';",

[454] Fix | Delete

// check if we can get through this: Error Code: 3105. The value specified for generated column 'generated_column' in table 'wp_generated_column_test' is not allowed.

[455] Fix | Delete

// DEFAULT is the only allowed value for virtual and stored type (i.e INSERT IGNORE INTO `wp_generated_column_test` (`virtual_column`) VALUES(DEFAULT)), other than that will produce an error, luckily insert ignore works fine on MariaDB but not on MySQL

[456] Fix | Delete

"INSERT IGNORE INTO `$random_table_name` (`virtual_column`) VALUES('virtual_column');",

[457] Fix | Delete

// MySQL does not support the create option 'Index on virtual generated column' on MyISAM storage engine

[458] Fix | Delete

"CREATE INDEX `idx_wp_udp_generated_column_test_generated_column` ON `$random_table_name` (virtual_column) COMMENT 'virtual_column' ALGORITHM DEFAULT LOCK DEFAULT;",

[459] Fix | Delete

);

[460] Fix | Delete

[461] Fix | Delete

$old_val = $wpdb->suppress_errors();

[462] Fix | Delete

$wpdb->query($drop_statement);

[463] Fix | Delete

$is_generated_column_supported = $wpdb->query($sql[0]);

[464] Fix | Delete

if ($is_generated_column_supported) {

[465] Fix | Delete

$is_generated_column_supported = array(

[466] Fix | Delete

'is_persistent_supported' => $wpdb->query($sql[1]),

[467] Fix | Delete

'is_not_null_supported' => $wpdb->query($sql[2]),

[468] Fix | Delete

'can_insert_ignore_to_generated_column' => (bool) $wpdb->query($sql[3]),

[469] Fix | Delete

'is_virtual_index_supported' => $wpdb->query($sql[4])

[470] Fix | Delete

);

[471] Fix | Delete

} else {

[472] Fix | Delete

$is_generated_column_supported = false;

[473] Fix | Delete

}

[474] Fix | Delete

$wpdb->query($drop_statement);

[475] Fix | Delete

$wpdb->suppress_errors($old_val);

[476] Fix | Delete

[477] Fix | Delete

return $is_generated_column_supported;

[478] Fix | Delete

}

[479] Fix | Delete

[480] Fix | Delete

/**

[481] Fix | Delete

* Parse the "insert into" statement, capture the column names (if any) and check whether one of the captured columns matches the given list of the "$generated_columns"

[482] Fix | Delete

*

[483] Fix | Delete

* @see https://regex101.com/r/JZiJqH/2

[484] Fix | Delete

*

[485] Fix | Delete

* @param String $insert_statement the insert statement in which the generated columns will be checked

[486] Fix | Delete

* @param Array $generated_columns the list of the available "generated columns"

[487] Fix | Delete

* @return Boolean|Null True if "generated columns" exist in the "insert into" statement, false otherwise, null on empty or unmatched insert statement

[488] Fix | Delete

*/

[489] Fix | Delete

public static function generated_columns_exist_in_the_statement($insert_statement, $generated_columns) {

[490] Fix | Delete

[491] Fix | Delete

$exist = null;

[492] Fix | Delete

if (preg_match('/\s*insert.+?into(?:\s*`(?:[^`]|`)+?`|[^\(]+)(?:\s*\((.+?)\))?\s*values.+/i', $insert_statement, $matches)) {

[493] Fix | Delete

/**

[494] Fix | Delete

* the reqex above will search for matches of either the insert statement gives data based on the specified column names (i.e INSERT INTO `table_name`(`col1`,'col2`,`virtual_column`,`stored_column`,`col5`) values('1','2','3','4','5')) or not (i.e INSERT INTO `table_name` values('1',',2','3','4','5')), and if the above preg_match function succeed, it returns an array with the following format:

[495] Fix | Delete

*

[496] Fix | Delete

* Array(2) {

[497] Fix | Delete

* [0]=> "INSERT INTO `table_name`(`col1`,'col2`,`virtual_column`,`stored_column`,`col5`) values('1','2','3','4','5')"

[498] Fix | Delete

* [1]=> "`col1`,`col2`,`virtual_column`,`col4`,`stored_column`"

[499] Fix | Delete