performance-lab
/
modules
/
database
/
sqlite
/
wp-includes
/
sqlite
/
class-perflab-sqlite-create-query.php
class-perflab-sqlite-create-query.php in Performance Lab 2.3.0, at modules/database/sqlite/wp-includes/sqlite/class-perflab-sqlite-create-query.php
| 1 | <?php |
| 2 | /** |
| 3 | * Implementation to rewrite CREATE queries. |
| 4 | * |
| 5 | * @package performance-lab |
| 6 | * @since 1.8.0 |
| 7 | */ |
| 8 | |
| 9 | /** |
| 10 | * This class provides a function to rewrite CREATE query. |
| 11 | */ |
| 12 | class Perflab_SQLite_Create_Query { |
| 13 | |
| 14 | /** |
| 15 | * The query string to be rewritten in this class. |
| 16 | * |
| 17 | * @var string |
| 18 | * @access private |
| 19 | */ |
| 20 | private $_query = ''; |
| 21 | |
| 22 | /** |
| 23 | * The array to contain CREATE INDEX queries. |
| 24 | * |
| 25 | * @var array of strings |
| 26 | * @access private |
| 27 | */ |
| 28 | private $index_queries = array(); |
| 29 | |
| 30 | /** |
| 31 | * The array to contain error messages. |
| 32 | * |
| 33 | * @var array of string |
| 34 | * @access private |
| 35 | */ |
| 36 | private $_errors = array(); |
| 37 | |
| 38 | /** |
| 39 | * Variable to have the table name to be executed. |
| 40 | * |
| 41 | * @var string |
| 42 | * @access private |
| 43 | */ |
| 44 | private $table_name = ''; |
| 45 | |
| 46 | /** |
| 47 | * Variable to check if the query has the primary key. |
| 48 | * |
| 49 | * @var boolean |
| 50 | * @access private |
| 51 | */ |
| 52 | private $has_primary_key = false; |
| 53 | |
| 54 | /** |
| 55 | * Function to rewrite query. |
| 56 | * |
| 57 | * @param string $query The query being processed. |
| 58 | * @return string|array The processed (rewritten) query |
| 59 | */ |
| 60 | public function rewrite_query( $query ) { |
| 61 | $this->_query = $query; |
| 62 | $this->_errors [] = ''; |
| 63 | if ( preg_match( '/^CREATE\\s*(UNIQUE|FULLTEXT|)\\s*INDEX/ims', $this->_query, $match ) ) { |
| 64 | // We manipulate CREATE INDEX query in the Perflab_SQLite_PDO_Engine class. |
| 65 | // FULLTEXT index creation is simply ignored. |
| 66 | if ( isset( $match[1] ) && stripos( $match[1], 'fulltext' ) !== false ) { |
| 67 | return 'SELECT 1=1'; |
| 68 | } |
| 69 | return $this->_query; |
| 70 | } |
| 71 | if ( preg_match( '/^CREATE\\s*(TEMP|TEMPORARY|)\\s*TRIGGER\\s*/im', $this->_query ) ) { |
| 72 | // If WordPress comes to use foreign key constraint, trigger will be needed. |
| 73 | // We don't use it for now. |
| 74 | return $this->_query; |
| 75 | } |
| 76 | $this->strip_backticks(); |
| 77 | $this->quote_illegal_field(); |
| 78 | $this->get_table_name(); |
| 79 | $this->rewrite_comments(); |
| 80 | $this->rewrite_field_types(); |
| 81 | $this->rewrite_character_set(); |
| 82 | $this->rewrite_engine_info(); |
| 83 | $this->rewrite_unsigned(); |
| 84 | $this->rewrite_autoincrement(); |
| 85 | $this->rewrite_primary_key(); |
| 86 | $this->rewrite_foreign_key(); |
| 87 | $this->rewrite_unique_key(); |
| 88 | $this->rewrite_enum(); |
| 89 | $this->rewrite_set(); |
| 90 | $this->rewrite_key(); |
| 91 | $this->add_if_not_exists(); |
| 92 | |
| 93 | return $this->post_process(); |
| 94 | } |
| 95 | |
| 96 | /** |
| 97 | * Method to get table name from the query string. |
| 98 | * |
| 99 | * 'IF NOT EXISTS' clause is removed for the easy regular expression usage. |
| 100 | * It will be added at the end of the process. |
| 101 | * |
| 102 | * @access private |
| 103 | */ |
| 104 | private function get_table_name() { |
| 105 | // $pattern = '/^\\s*CREATE\\s*(TEMP|TEMPORARY)?\\s*TABLE\\s*(IF NOT EXISTS)?\\s*([^\(]*)/imsx'; |
| 106 | $pattern = '/^\\s*CREATE\\s*(?:TEMP|TEMPORARY)?\\s*TABLE\\s*(?:IF\\s*NOT\\s*EXISTS)?\\s*([^\(]*)/imsx'; |
| 107 | if ( preg_match( $pattern, $this->_query, $matches ) ) { |
| 108 | $this->table_name = trim( $matches[1] ); |
| 109 | } |
| 110 | } |
| 111 | |
| 112 | /** |
| 113 | * Method to change the MySQL field types to SQLite compatible types. |
| 114 | * |
| 115 | * If column name is the same as the key value, e.g. "date" or "timestamp", |
| 116 | * and the column is on the top of the line, we add a single quote and avoid |
| 117 | * to be replaced. But this doesn't work if that column name is in the middle |
| 118 | * of the line. |
| 119 | * Order of the key value is important. Don't change it. |
| 120 | * |
| 121 | * @access private |
| 122 | */ |
| 123 | private function rewrite_field_types() { |
| 124 | $array_types = array( |
| 125 | 'bit' => 'integer', |
| 126 | 'bool' => 'integer', |
| 127 | 'boolean' => 'integer', |
| 128 | 'tinyint' => 'integer', |
| 129 | 'smallint' => 'integer', |
| 130 | 'mediumint' => 'integer', |
| 131 | 'int' => 'integer', |
| 132 | 'integer' => 'integer', |
| 133 | 'bigint' => 'integer', |
| 134 | 'float' => 'real', |
| 135 | 'double' => 'real', |
| 136 | 'decimal' => 'real', |
| 137 | 'dec' => 'real', |
| 138 | 'numeric' => 'real', |
| 139 | 'fixed' => 'real', |
| 140 | 'date' => 'text', |
| 141 | 'datetime' => 'text', |
| 142 | 'timestamp' => 'text', |
| 143 | 'time' => 'text', |
| 144 | 'year' => 'text', |
| 145 | 'char' => 'text', |
| 146 | 'varchar' => 'text', |
| 147 | 'binary' => 'integer', |
| 148 | 'varbinary' => 'blob', |
| 149 | 'tinyblob' => 'blob', |
| 150 | 'tinytext' => 'text', |
| 151 | 'blob' => 'blob', |
| 152 | 'text' => 'text', |
| 153 | 'mediumblob' => 'blob', |
| 154 | 'mediumtext' => 'text', |
| 155 | 'longblob' => 'blob', |
| 156 | 'longtext' => 'text', |
| 157 | ); |
| 158 | foreach ( $array_types as $o => $r ) { |
| 159 | if ( preg_match( "/^\\s*(?<!')$o\\s+(.+$)/im", $this->_query, $match ) ) { |
| 160 | $ptrn = "/$match[1]/im"; |
| 161 | $replaced = str_ireplace( $ptrn, '#placeholder#', $this->_query ); |
| 162 | $replaced = str_ireplace( $o, "'{$o}'", $replaced ); |
| 163 | $this->_query = str_replace( '#placeholder#', $ptrn, $replaced ); |
| 164 | } |
| 165 | $pattern = "/\\b(?<!')$o\\b\\s*(\([^\)]*\)*)?\\s*/ims"; |
| 166 | if ( preg_match( "/^\\s*.*?\\s*\(.*?$o.*?\)/im", $this->_query ) ) { |
| 167 | // ; |
| 168 | } else { |
| 169 | $this->_query = preg_replace( $pattern, " $r ", $this->_query ); |
| 170 | } |
| 171 | } |
| 172 | } |
| 173 | |
| 174 | /** |
| 175 | * Method for stripping the comments from the SQL statement. |
| 176 | * |
| 177 | * @access private |
| 178 | */ |
| 179 | private function rewrite_comments() { |
| 180 | $this->_query = preg_replace( |
| 181 | '/# --------------------------------------------------------/', |
| 182 | '-- ******************************************************', |
| 183 | $this->_query |
| 184 | ); |
| 185 | $this->_query = preg_replace( '/#/', '--', $this->_query ); |
| 186 | } |
| 187 | |
| 188 | /** |
| 189 | * Method for stripping the engine and other stuffs. |
| 190 | * |
| 191 | * TYPE, ENGINE and AUTO_INCREMENT are removed here. |
| 192 | * |
| 193 | * @access private |
| 194 | */ |
| 195 | private function rewrite_engine_info() { |
| 196 | $this->_query = preg_replace( '/\\s*(TYPE|ENGINE)\\s*=\\s*.*(?<!;)/ims', '', $this->_query ); |
| 197 | $this->_query = preg_replace( '/ AUTO_INCREMENT\\s*=\\s*[0-9]*/ims', '', $this->_query ); |
| 198 | } |
| 199 | |
| 200 | /** |
| 201 | * Method for stripping unsigned. |
| 202 | * |
| 203 | * SQLite doesn't have unsigned int data type. So UNSIGNED INT(EGER) is converted |
| 204 | * to INTEGER here. |
| 205 | * |
| 206 | * @access private |
| 207 | */ |
| 208 | private function rewrite_unsigned() { |
| 209 | $this->_query = preg_replace( '/\\bunsigned\\b/ims', ' ', $this->_query ); |
| 210 | } |
| 211 | |
| 212 | /** |
| 213 | * Method for rewriting primary key auto_increment. |
| 214 | * |
| 215 | * If the field type is 'INTEGER PRIMARY KEY', it is automatically autoincremented |
| 216 | * by SQLite. There's a little difference between PRIMARY KEY and AUTOINCREMENT, so |
| 217 | * we may well convert to PRIMARY KEY only. |
| 218 | * |
| 219 | * @access private |
| 220 | */ |
| 221 | private function rewrite_autoincrement() { |
| 222 | $this->_query = preg_replace( |
| 223 | '/\\bauto_increment\\s*primary\\s*key\\s*(,)?/ims', |
| 224 | ' PRIMARY KEY AUTOINCREMENT \\1', |
| 225 | $this->_query, |
| 226 | -1, |
| 227 | $count |
| 228 | ); |
| 229 | $this->_query = preg_replace( |
| 230 | '/\\bauto_increment\\b\\s*(,)?/ims', |
| 231 | ' PRIMARY KEY AUTOINCREMENT $1', |
| 232 | $this->_query, |
| 233 | -1, |
| 234 | $count |
| 235 | ); |
| 236 | if ( $count > 0 ) { |
| 237 | $this->has_primary_key = true; |
| 238 | } |
| 239 | } |
| 240 | |
| 241 | /** |
| 242 | * Method for rewriting primary key. |
| 243 | * |
| 244 | * @access private |
| 245 | */ |
| 246 | private function rewrite_primary_key() { |
| 247 | if ( $this->has_primary_key ) { |
| 248 | $this->_query = preg_replace( '/\\s*primary key\\s*.*?\([^\)]*\)\\s*(,|)/i', ' ', $this->_query ); |
| 249 | } else { |
| 250 | // If primary key has an index name, we remove that name. |
| 251 | $this->_query = preg_replace( '/\\bprimary\\s*key\\s*.*?\\s*(\(.*?\))/im', 'PRIMARY KEY \\1', $this->_query ); |
| 252 | } |
| 253 | } |
| 254 | |
| 255 | /** |
| 256 | * Method for rewriting foreign key. |
| 257 | * |
| 258 | * @access private |
| 259 | */ |
| 260 | private function rewrite_foreign_key() { |
| 261 | $pattern = '/\\s*foreign\\s*key\\s*(|.*?)\([^\)]+?\)\\s*references\\s*.*/i'; |
| 262 | if ( preg_match_all( $pattern, $this->_query, $match ) ) { |
| 263 | if ( isset( $match[1] ) ) { |
| 264 | $this->_query = str_ireplace( $match[1], '', $this->_query ); |
| 265 | } |
| 266 | } |
| 267 | } |
| 268 | |
| 269 | /** |
| 270 | * Method for rewriting unique key. |
| 271 | * |
| 272 | * @access private |
| 273 | */ |
| 274 | private function rewrite_unique_key() { |
| 275 | $this->_query = preg_replace_callback( |
| 276 | '/\\bunique key\\b([^\(]*)(\(.*\))/im', |
| 277 | array( $this, '_rewrite_unique_key' ), |
| 278 | $this->_query |
| 279 | ); |
| 280 | } |
| 281 | |
| 282 | /** |
| 283 | * Callback method for rewrite_unique_key. |
| 284 | * |
| 285 | * @access private |
| 286 | * |
| 287 | * @param array $matches An array of matches from the Regex. |
| 288 | * @return string |
| 289 | */ |
| 290 | private function _rewrite_unique_key( $matches ) { |
| 291 | $index_name = trim( $matches[1] ); |
| 292 | $col_name = trim( $matches[2] ); |
| 293 | $tbl_name = $this->table_name; |
| 294 | if ( preg_match( '/\(\\d+?\)/', $col_name ) ) { |
| 295 | $col_name = preg_replace( '/\(\\d+?\)/', '', $col_name ); |
| 296 | } |
| 297 | $_wpdb = new Perflab_SQLite_DB(); |
| 298 | $results = $_wpdb->get_results( "SELECT name FROM sqlite_master WHERE type='index'" ); |
| 299 | $_wpdb = null; |
| 300 | if ( $results ) { |
| 301 | foreach ( $results as $result ) { |
| 302 | if ( $result->name === $index_name ) { |
| 303 | $r = rand( 0, 50 ); |
| 304 | $index_name = $index_name . "_$r"; |
| 305 | break; |
| 306 | } |
| 307 | } |
| 308 | } |
| 309 | $index_name = str_replace( ' ', '', $index_name ); |
| 310 | $this->index_queries[] = "CREATE UNIQUE INDEX $index_name ON " . $tbl_name . $col_name; |
| 311 | |
| 312 | return ''; |
| 313 | } |
| 314 | |
| 315 | /** |
| 316 | * Method for handling ENUM fields. |
| 317 | * |
| 318 | * SQLite doesn't support enum, so we change it to check constraint. |
| 319 | * |
| 320 | * @access private |
| 321 | */ |
| 322 | private function rewrite_enum() { |
| 323 | $pattern = '/(,|\))([^,]*)enum\((.*?)\)([^,\)]*)/ims'; |
| 324 | $this->_query = preg_replace_callback( $pattern, array( $this, '_rewrite_enum' ), $this->_query ); |
| 325 | } |
| 326 | |
| 327 | /** |
| 328 | * Call back method for rewrite_enum() and rewrite_set(). |
| 329 | * |
| 330 | * @access private |
| 331 | * |
| 332 | * @param array $matches An array of matches from the Regex. |
| 333 | * |
| 334 | * @return string |
| 335 | */ |
| 336 | private function _rewrite_enum( $matches ) { |
| 337 | $output = $matches[1] . ' ' . $matches[2] . ' TEXT ' . $matches[4] . ' CHECK (' . $matches[2] . ' IN (' . $matches[3] . ')) '; |
| 338 | |
| 339 | return $output; |
| 340 | } |
| 341 | |
| 342 | /** |
| 343 | * Method for rewriting usage of set. |
| 344 | * |
| 345 | * It is similar but not identical to enum. SQLite does not support either. |
| 346 | * |
| 347 | * @access private |
| 348 | */ |
| 349 | private function rewrite_set() { |
| 350 | $pattern = '/\b(\w)*\bset\\s*\((.*?)\)\\s*(.*?)(,)*/ims'; |
| 351 | $this->_query = preg_replace_callback( $pattern, array( $this, '_rewrite_enum' ), $this->_query ); |
| 352 | } |
| 353 | |
| 354 | /** |
| 355 | * Method for rewriting usage of key to create an index. |
| 356 | * |
| 357 | * SQLite cannot create non-unique indices as part of the create query, |
| 358 | * so we need to create an index by hand and append it to the create query. |
| 359 | * |
| 360 | * @access private |
| 361 | */ |
| 362 | private function rewrite_key() { |
| 363 | $this->_query = preg_replace_callback( |
| 364 | '/,\\s*(KEY|INDEX)\\s*(\\w+)?\\s*(\(.+\))/im', |
| 365 | array( $this, '_rewrite_key' ), |
| 366 | $this->_query |
| 367 | ); |
| 368 | } |
| 369 | |
| 370 | /** |
| 371 | * Callback method for rewrite_key. |
| 372 | * |
| 373 | * @param array $matches an array of matches from the Regex. |
| 374 | * |
| 375 | * @access private |
| 376 | * @return string |
| 377 | */ |
| 378 | private function _rewrite_key( $matches ) { |
| 379 | $index_name = trim( $matches[2] ); |
| 380 | $col_name = trim( $matches[3] ); |
| 381 | if ( preg_match( '/\([0-9]+?\)/', $col_name, $match ) ) { |
| 382 | $col_name = preg_replace_callback( '/\([0-9]+?\)/', array( $this, '_remove_length' ), $col_name ); |
| 383 | } |
| 384 | $tbl_name = $this->table_name; |
| 385 | $_wpdb = new Perflab_SQLite_DB(); |
| 386 | $results = $_wpdb->get_results( "SELECT name FROM sqlite_master WHERE type='index'" ); |
| 387 | $_wpdb = null; |
| 388 | if ( $results ) { |
| 389 | foreach ( $results as $result ) { |
| 390 | if ( $result->name === $index_name ) { |
| 391 | $r = rand( 0, 50 ); |
| 392 | $index_name = $index_name . "_$r"; |
| 393 | break; |
| 394 | } |
| 395 | } |
| 396 | } |
| 397 | $this->index_queries[] = 'CREATE INDEX ' . $index_name . ' ON ' . $tbl_name . $col_name; |
| 398 | |
| 399 | return ''; |
| 400 | } |
| 401 | |
| 402 | /** |
| 403 | * Call back method to remove unnecessary string. |
| 404 | * |
| 405 | * This method is deprecated. |
| 406 | * |
| 407 | * @param string $match The string to be removed. Not used. |
| 408 | * |
| 409 | * @return string whose length is zero |
| 410 | * @access private |
| 411 | */ |
| 412 | private function _remove_length( $match ) { |
| 413 | return ''; |
| 414 | } |
| 415 | |
| 416 | /** |
| 417 | * Method to assemble the main query and index queries into an array. |
| 418 | * |
| 419 | * It return the array of the queries to be executed separately. |
| 420 | * |
| 421 | * @return array |
| 422 | * @access private |
| 423 | */ |
| 424 | private function post_process() { |
| 425 | $mainquery = $this->_query; |
| 426 | do { |
| 427 | $count = 0; |
| 428 | $mainquery = preg_replace( '/,\\s*\)/imsx', ')', $mainquery, -1, $count ); |
| 429 | } while ( $count > 0 ); |
| 430 | do { |
| 431 | $count = 0; |
| 432 | $mainquery = preg_replace( '/\(\\s*?,/imsx', '(', $mainquery, -1, $count ); |
| 433 | } while ( $count > 0 ); |
| 434 | $return_val[] = $mainquery; |
| 435 | $return_val = array_merge( $return_val, $this->index_queries ); |
| 436 | |
| 437 | return $return_val; |
| 438 | } |
| 439 | |
| 440 | /** |
| 441 | * Method to add IF NOT EXISTS to query string. |
| 442 | * |
| 443 | * This adds IF NOT EXISTS to every query string, which prevent the exception |
| 444 | * from being thrown. |
| 445 | * |
| 446 | * @access private |
| 447 | */ |
| 448 | private function add_if_not_exists() { |
| 449 | $pattern_table = '/^\\s*CREATE\\s*(TEMP|TEMPORARY)?\\s*TABLE\\s*(IF NOT EXISTS)?\\s*/ims'; |
| 450 | $this->_query = preg_replace( $pattern_table, 'CREATE $1 TABLE IF NOT EXISTS ', $this->_query ); |
| 451 | $pattern_index = '/^\\s*CREATE\\s*(UNIQUE)?\\s*INDEX\\s*(IF NOT EXISTS)?\\s*/ims'; |
| 452 | for ( $i = 0; $i < count( $this->index_queries ); $i++ ) { |
| 453 | $this->index_queries[ $i ] = preg_replace( |
| 454 | $pattern_index, |
| 455 | 'CREATE $1 INDEX IF NOT EXISTS ', |
| 456 | $this->index_queries[ $i ] |
| 457 | ); |
| 458 | } |
| 459 | } |
| 460 | |
| 461 | /** |
| 462 | * Method to strip back quotes. |
| 463 | * |
| 464 | * @access private |
| 465 | */ |
| 466 | private function strip_backticks() { |
| 467 | $this->_query = str_replace( '`', '', $this->_query ); |
| 468 | foreach ( $this->index_queries as &$query ) { |
| 469 | $query = str_replace( '`', '', $query ); |
| 470 | } |
| 471 | } |
| 472 | |
| 473 | /** |
| 474 | * Method to remove the character set information from within mysql queries. |
| 475 | * |
| 476 | * This removes DEFAULT CHAR(ACTER) SET and COLLATE, which is meaningless for |
| 477 | * SQLite. |
| 478 | * |
| 479 | * @access private |
| 480 | */ |
| 481 | private function rewrite_character_set() { |
| 482 | $pattern_charset = '/\\b(default\\s*character\\s*set|default\\s*charset|character\\s*set)\\s*(?<!\()[^ ]*/im'; |
| 483 | $pattern_collate1 = '/\\s*collate\\s*[^ ]*(?=,)/im'; |
| 484 | $pattern_collate2 = '/\\s*collate\\s*[^ ]*(?<!;)/im'; |
| 485 | $patterns = array( $pattern_charset, $pattern_collate1, $pattern_collate2 ); |
| 486 | $this->_query = preg_replace( $patterns, '', $this->_query ); |
| 487 | } |
| 488 | |
| 489 | /** |
| 490 | * Method to quote illegal field name for SQLite |
| 491 | * |
| 492 | * @access private |
| 493 | */ |
| 494 | private function quote_illegal_field() { |
| 495 | $this->_query = preg_replace( "/^\\s*(?<!')(default|values)/im", "'\\1'", $this->_query ); |
| 496 | } |
| 497 | } |
| 498 |