mirror of
https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress.git
synced 2026-01-25 16:02:19 +01:00
291 lines
9.9 KiB
PHP
291 lines
9.9 KiB
PHP
<?php
|
|
|
|
class AlterTableSQLRewriter extends AbstractSQLRewriter
|
|
{
|
|
private $stringReplacements = [
|
|
' tinytext' => ' text',
|
|
' mediumtext' => ' text',
|
|
' longtext' => ' text',
|
|
' unsigned' => ' ',
|
|
'gmt datetime NOT NULL default \'0000-00-00 00:00:00\'' => 'gmt timestamp NOT NULL DEFAULT timezone(\'gmt\'::text, now())',
|
|
'default \'0000-00-00 00:00:00\'' => 'DEFAULT now()',
|
|
'\'0000-00-00 00:00:00\'' => 'now()',
|
|
' datetime' => ' timestamp',
|
|
' DEFAULT CHARACTER SET utf8mb4' => '',
|
|
' DEFAULT CHARACTER SET utf8' => '',
|
|
|
|
// For WPMU (starting with WP 3.2)
|
|
" enum('0','1')" => ' smallint',
|
|
' COLLATE utf8mb4_unicode_520_ci' => '',
|
|
' COLLATE utf8_general_ci' => '',
|
|
' CHARACTER SET utf8' => '',
|
|
' DEFAULT CHARSET=utf8' => '',
|
|
|
|
// For flash-album-gallery plugin
|
|
' tinyint' => ' smallint'
|
|
];
|
|
|
|
public function rewrite(): string
|
|
{
|
|
$sql = $this->original();
|
|
|
|
$sql = $this->rewrite_numeric_type($sql);
|
|
$sql = $this->rewrite_columns_with_protected_names($sql);
|
|
|
|
if (str_contains($sql, 'ADD INDEX') || str_contains($sql, 'ADD UNIQUE INDEX')) {
|
|
$sql = $this->rewriteAddIndex($sql);
|
|
return $sql;
|
|
}
|
|
if (str_contains($sql, 'CHANGE COLUMN')) {
|
|
$sql = $this->rewriteChangeColumn($sql);
|
|
return $sql;
|
|
}
|
|
if (str_contains($sql, 'ALTER COLUMN')) {
|
|
$sql = $this->rewriteAlterColumn($sql);
|
|
return $sql;
|
|
}
|
|
if (str_contains($sql, 'ADD COLUMN')) {
|
|
$sql = $this->rewriteAddColumn($sql);
|
|
return $sql;
|
|
}
|
|
if (str_contains($sql, 'ADD KEY') || str_contains($sql, 'ADD UNIQUE KEY')) {
|
|
$sql = $this->rewriteAddKey($sql);
|
|
return $sql;
|
|
}
|
|
if (str_contains($sql, 'DROP INDEX')) {
|
|
$sql = $this->rewriteDropIndex($sql);
|
|
return $sql;
|
|
}
|
|
if (str_contains($sql, 'DROP PRIMARY KEY')) {
|
|
$sql = $this->rewriteDropPrimaryKey($sql);
|
|
return $sql;
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteAddIndex(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+ADD (UNIQUE |)INDEX\s+([^\s]+)\s+\(((?:[^\(\)]+|\([^\(\)]+\))+)\)/';
|
|
|
|
if(1 === preg_match($pattern, $sql, $matches)) {
|
|
$table = $matches[1];
|
|
$unique = $matches[2];
|
|
$index = $matches[3];
|
|
$columns = $matches[4];
|
|
|
|
// Remove prefix indexing
|
|
// Rarely used and apparently unnecessary for current uses
|
|
$columns = preg_replace('/\([^\)]*\)/', '', $columns);
|
|
|
|
// Workaround for index name duplicate
|
|
$index = $table . '_' . $index;
|
|
|
|
// Add backticks around index name and column name, and include IF NOT EXISTS clause
|
|
$sql = "CREATE {$unique}INDEX IF NOT EXISTS `{$index}` ON `{$table}` (`{$columns}`)";
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteChangeColumn(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+CHANGE COLUMN\s+([^\s]+)\s+([^\s]+)\s+([^ ]+)( unsigned|)\s*(NOT NULL|)\s*(default (.+)|)/';
|
|
|
|
if(1 === preg_match($pattern, $sql, $matches)) {
|
|
$table = $matches[1];
|
|
$col = $matches[2];
|
|
$newname = $matches[3];
|
|
$type = strtolower($matches[4]);
|
|
if(isset($this->stringReplacements[$type])) {
|
|
$type = $this->stringReplacements[$type];
|
|
}
|
|
$unsigned = $matches[5];
|
|
$notnull = $matches[6];
|
|
$default = $matches[7];
|
|
$defval = $matches[8];
|
|
if(isset($this->stringReplacements[$defval])) {
|
|
$defval = $this->stringReplacements[$defval];
|
|
}
|
|
$newq = "ALTER TABLE $table ALTER COLUMN $col TYPE $type";
|
|
if(!empty($notnull)) {
|
|
$newq .= ", ALTER COLUMN $col SET NOT NULL";
|
|
}
|
|
if(!empty($default)) {
|
|
$newq .= ", ALTER COLUMN $col SET DEFAULT $defval";
|
|
}
|
|
if($col != $newname) {
|
|
$newq .= ";ALTER TABLE $table RENAME COLUMN $col TO $newcol;";
|
|
}
|
|
$sql = $newq;
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteAlterColumn(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+ALTER COLUMN\s+/';
|
|
|
|
if(1 === preg_match($pattern, $sql)) {
|
|
// Translate default values
|
|
$sql = str_replace(
|
|
array_keys($this->stringReplacements),
|
|
array_values($this->stringReplacements),
|
|
$sql
|
|
);
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteAddColumn(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+ADD COLUMN\s+([^\s]+)\s+([^ ]+)( unsigned|)\s+(NOT NULL|)\s*(default (.+)|)/';
|
|
|
|
if(1 === preg_match($pattern, $sql, $matches)) {
|
|
$table = $matches[1];
|
|
$col = $matches[2];
|
|
$type = strtolower($matches[3]);
|
|
if(isset($this->stringReplacements[$type])) {
|
|
$type = $this->stringReplacements[$type];
|
|
}
|
|
$unsigned = $matches[4];
|
|
$notnull = $matches[5];
|
|
$default = $matches[6];
|
|
$defval = $matches[7];
|
|
if(isset($this->stringReplacements[$defval])) {
|
|
$defval = $this->stringReplacements[$defval];
|
|
}
|
|
$newq = "ALTER TABLE $table ADD COLUMN $col $type";
|
|
if(!empty($default)) {
|
|
$newq .= " DEFAULT $defval";
|
|
}
|
|
if(!empty($notnull)) {
|
|
$newq .= " NOT NULL";
|
|
}
|
|
$sql = $newq;
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteAddKey(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+ADD (UNIQUE |)KEY\s+([^\s]+)\s+\(((?:[^\(\)]+|\([^\(\)]+\))+)\)/';
|
|
|
|
if(1 === preg_match($pattern, $sql, $matches)) {
|
|
$table = $matches[1];
|
|
$unique = $matches[2];
|
|
$index = $matches[3];
|
|
$columns = $matches[4];
|
|
|
|
// Remove prefix indexing
|
|
// Rarely used and apparently unnecessary for current uses
|
|
$columns = preg_replace('/\([^\)]*\)/', '', $columns);
|
|
|
|
// Workaround for index name duplicate
|
|
$index = $table . '_' . $index;
|
|
$sql = "CREATE {$unique}INDEX $index ON $table ($columns)";
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteDropIndex(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+DROP INDEX\s+([^\s]+)/';
|
|
|
|
if(1 === preg_match($pattern, $sql, $matches)) {
|
|
$table = $matches[1];
|
|
$index = $matches[2];
|
|
$sql = "DROP INDEX {$table}_{$index}";
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewriteDropPrimaryKey(string $sql): string
|
|
{
|
|
$pattern = '/ALTER TABLE\s+(\w+)\s+DROP PRIMARY KEY/';
|
|
|
|
if(1 === preg_match($pattern, $sql, $matches)) {
|
|
$table = $matches[1];
|
|
$sql = "ALTER TABLE {$table} DROP CONSTRAINT {$table}_pkey";
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewrite_numeric_type($sql)
|
|
{
|
|
// Numeric types in MySQL which need to be rewritten
|
|
$numeric_types = ["bigint", "int", "integer", "smallint", "mediumint", "tinyint", "double", "decimal"];
|
|
$numeric_types_imploded = implode('|', $numeric_types);
|
|
|
|
// Prepare regex pattern to match 'type(x)'
|
|
$pattern = "/(" . $numeric_types_imploded . ")\(\d+\)/";
|
|
|
|
// Execute type find & replace
|
|
$sql = preg_replace_callback($pattern, function ($matches) {
|
|
return $matches[1];
|
|
}, $sql);
|
|
|
|
// bigint
|
|
$pattern = '/bigint(\(\d+\))?([ ]*NOT NULL)?[ ]*auto_increment/i';
|
|
preg_match($pattern, $sql, $matches);
|
|
if($matches) {
|
|
$sql = preg_replace($pattern, 'bigserial', $sql);
|
|
}
|
|
|
|
// int
|
|
$pattern = '/int(\(\d+\))?([ ]*NOT NULL)?[ ]*auto_increment/i';
|
|
preg_match($pattern, $sql, $matches);
|
|
if($matches) {
|
|
$sql = preg_replace($pattern, 'serial', $sql);
|
|
}
|
|
|
|
// smallint
|
|
$pattern = '/smallint(\(\d+\))?([ ]*NOT NULL)?[ ]*auto_increment/i';
|
|
preg_match($pattern, $sql, $matches);
|
|
if($matches) {
|
|
$sql = preg_replace($pattern, 'smallserial', $sql);
|
|
}
|
|
|
|
// Handle for numeric and decimal -- being replaced with serial
|
|
$numeric_patterns = ['/numeric(\(\d+\))?([ ]*NOT NULL)?[ ]*auto_increment/i', '/decimal(\(\d+\))?([ ]*NOT NULL)?[ ]*auto_increment/i'];
|
|
foreach($numeric_patterns as $pattern) {
|
|
preg_match($pattern, $sql, $matches);
|
|
if($matches) {
|
|
$sql = preg_replace($pattern, 'serial', $sql);
|
|
}
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
private function rewrite_columns_with_protected_names($sql)
|
|
{
|
|
// Splitting the SQL statement into parts before "(", inside "(", and after ")"
|
|
if (preg_match('/^(CREATE TABLE IF NOT EXISTS|CREATE TABLE|ALTER TABLE)\s+([^\s]+)\s*\((.*)\)(.*)$/is', $sql, $matches)) {
|
|
$prefix = $matches[1] . ' ' . $matches[2] . ' (';
|
|
$columnsAndKeys = $matches[3];
|
|
$suffix = ')' . $matches[4];
|
|
|
|
$regex = '/(?:^|\s*,\s*)(\b(?:timestamp|date|time|default)\b)\s*(?=\s+\w+)/i';
|
|
|
|
// Callback function to add quotes around protected column names
|
|
$callback = function($matches) {
|
|
$whitespace = str_replace($matches[1], "", $matches[0]);
|
|
return $whitespace . '"' . $matches[1] . '"';
|
|
};
|
|
|
|
// Replace protected column names with quoted versions within columns and keys part
|
|
$columnsAndKeys = preg_replace_callback($regex, $callback, $columnsAndKeys);
|
|
return $prefix . $columnsAndKeys . $suffix;
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
}
|