Skip to content

Instantly share code, notes, and snippets.

@adamziel

adamziel/PostgreSQL driver for WordPress

Last active May 22, 2025 11:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save adamziel/dd065f946fdd1722897aced4d47d24be to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save adamziel/dd065f946fdd1722897aced4d47d24be to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL driver for WordPress
Raw
PostgreSQL driver for WordPress
Can you run WordPress on PostgreSQL?
Not yet, but this Gist gets you closer. This is a PostgreSQL driver
for https://github.com/Automattic/sqlite-database-integration. I've
generated it by asking AI to port the SQLite driver to PostgreSQL driver.
It parses a MySQL query and runs a corresponding PostgreSQL query. Some
queries work, some don't, the escaping is a mess, but it's a good first draft.
Raw
w_mysql_over_postgresql_driver.php
<?php
/**
* PostgreSQL driver for MySQL.
*
* This class emulates a MySQL database server on top of a PostgreSQL database.
* It translates queries written in MySQL SQL dialect to a PostgreSQL SQL dialect,
* maintains necessary metadata, and executes the translated queries in PostgreSQL.
*
* The driver requires PDO with the PostgreSQL driver (pgsql), and the PCRE engine.
*/
class WP_PostgreSQL_Driver {
/**
* The path to the MySQL SQL grammar file.
* NOTE: This is problematic for a single file solution.
* For this conversion, we assume the grammar is loaded by WP_Parser_Grammar stub.
*/
const MYSQL_GRAMMAR_PATH = __DIR__ . '/../mysql/mysql-grammar.php'; // This path will likely not exist as-is.
/**
* The minimum required version of PostgreSQL.
* Define a reasonable minimum, e.g., for common features like ON CONFLICT.
*/
const MINIMUM_POSTGRESQL_VERSION = '9.5'; // For INSERT ... ON CONFLICT
/**
* The default timeout in seconds for PostgreSQL. (Conceptual, pgsql driver uses connect_timeout)
*/
const DEFAULT_POSTGRESQL_TIMEOUT = 10; // Connection timeout
/**
* An identifier prefix for internal database objects.
*/
const RESERVED_PREFIX = '_wp_pgsql_';
/**
* A map of MySQL tokens to PostgreSQL data types.
*/
const DATA_TYPE_MAP = array(
// Numeric data types:
WP_MySQL_Lexer::BIT_SYMBOL => 'BIT', // Or BOOLEAN for BIT(1)
WP_MySQL_Lexer::BOOL_SYMBOL => 'BOOLEAN',
WP_MySQL_Lexer::BOOLEAN_SYMBOL => 'BOOLEAN',
WP_MySQL_Lexer::TINYINT_SYMBOL => 'SMALLINT', // PostgreSQL SMALLINT is -32768 to +32767
WP_MySQL_Lexer::SMALLINT_SYMBOL => 'SMALLINT',
WP_MySQL_Lexer::MEDIUMINT_SYMBOL => 'INTEGER',
WP_MySQL_Lexer::INT_SYMBOL => 'INTEGER',
WP_MySQL_Lexer::INTEGER_SYMBOL => 'INTEGER',
WP_MySQL_Lexer::BIGINT_SYMBOL => 'BIGINT',
WP_MySQL_Lexer::FLOAT_SYMBOL => 'REAL',
WP_MySQL_Lexer::DOUBLE_SYMBOL => 'DOUBLE PRECISION',
WP_MySQL_Lexer::REAL_SYMBOL => 'DOUBLE PRECISION', // MySQL REAL is DOUBLE
WP_MySQL_Lexer::DECIMAL_SYMBOL => 'NUMERIC',
WP_MySQL_Lexer::DEC_SYMBOL => 'NUMERIC',
WP_MySQL_Lexer::FIXED_SYMBOL => 'NUMERIC',
WP_MySQL_Lexer::NUMERIC_SYMBOL => 'NUMERIC',
// String data types:
WP_MySQL_Lexer::CHAR_SYMBOL => 'CHAR', // Needs length
WP_MySQL_Lexer::VARCHAR_SYMBOL => 'VARCHAR', // Needs length
WP_MySQL_Lexer::NCHAR_SYMBOL => 'CHAR', // Needs length, PG is Unicode aware
WP_MySQL_Lexer::NVARCHAR_SYMBOL => 'VARCHAR', // Needs length, PG is Unicode aware
WP_MySQL_Lexer::TINYTEXT_SYMBOL => 'TEXT',
WP_MySQL_Lexer::TEXT_SYMBOL => 'TEXT',
WP_MySQL_Lexer::MEDIUMTEXT_SYMBOL => 'TEXT',
WP_MySQL_Lexer::LONGTEXT_SYMBOL => 'TEXT',
WP_MySQL_Lexer::ENUM_SYMBOL => 'TEXT', // Or native ENUM TYPE
// Date and time data types:
WP_MySQL_Lexer::DATE_SYMBOL => 'DATE',
WP_MySQL_Lexer::TIME_SYMBOL => 'TIME WITHOUT TIME ZONE',
WP_MySQL_Lexer::DATETIME_SYMBOL => 'TIMESTAMP WITHOUT TIME ZONE',
WP_MySQL_Lexer::TIMESTAMP_SYMBOL => 'TIMESTAMP WITH TIME ZONE', // Stores in UTC
WP_MySQL_Lexer::YEAR_SYMBOL => 'SMALLINT', // Store as number
// Binary data types:
WP_MySQL_Lexer::BINARY_SYMBOL => 'BYTEA', // Needs length or different handling
WP_MySQL_Lexer::VARBINARY_SYMBOL => 'BYTEA',
WP_MySQL_Lexer::TINYBLOB_SYMBOL => 'BYTEA',
WP_MySQL_Lexer::BLOB_SYMBOL => 'BYTEA',
WP_MySQL_Lexer::MEDIUMBLOB_SYMBOL => 'BYTEA',
WP_MySQL_Lexer::LONGBLOB_SYMBOL => 'BYTEA',
// Spatial data types (assuming PostGIS is not used, map to TEXT for storage):
WP_MySQL_Lexer::GEOMETRY_SYMBOL => 'TEXT',
WP_MySQL_Lexer::POINT_SYMBOL => 'TEXT',
WP_MySQL_Lexer::LINESTRING_SYMBOL => 'TEXT',
WP_MySQL_Lexer::POLYGON_SYMBOL => 'TEXT',
WP_MySQL_Lexer::MULTIPOINT_SYMBOL => 'TEXT',
WP_MySQL_Lexer::MULTILINESTRING_SYMBOL => 'TEXT',
WP_MySQL_Lexer::MULTIPOLYGON_SYMBOL => 'TEXT',
WP_MySQL_Lexer::GEOMCOLLECTION_SYMBOL => 'TEXT',
WP_MySQL_Lexer::GEOMETRYCOLLECTION_SYMBOL => 'TEXT',
// SERIAL (handled in translation), SET (TEXT), and JSON (JSONB) types.
WP_MySQL_Lexer::JSON_SYMBOL => 'JSONB',
WP_MySQL_Lexer::SET_SYMBOL => 'TEXT', // Or TEXT[]
);
/**
* A map of normalized MySQL data types to PostgreSQL data types.
*/
const DATA_TYPE_STRING_MAP = array(
'bit' => 'BIT',
'bool' => 'BOOLEAN',
'boolean' => 'BOOLEAN',
'tinyint' => 'SMALLINT',
'smallint' => 'SMALLINT',
'mediumint' => 'INTEGER',
'int' => 'INTEGER',
'integer' => 'INTEGER',
'bigint' => 'BIGINT',
'float' => 'REAL',
'double' => 'DOUBLE PRECISION',
'real' => 'DOUBLE PRECISION',
'decimal' => 'NUMERIC',
'dec' => 'NUMERIC',
'fixed' => 'NUMERIC',
'numeric' => 'NUMERIC',
'char' => 'CHAR',
'varchar' => 'VARCHAR',
'nchar' => 'CHAR',
'nvarchar' => 'VARCHAR',
'tinytext' => 'TEXT',
'text' => 'TEXT',
'mediumtext' => 'TEXT',
'longtext' => 'TEXT',
'enum' => 'TEXT', // Or custom ENUM type
'set' => 'TEXT', // Or TEXT[]
'json' => 'JSONB',
'date' => 'DATE',
'time' => 'TIME WITHOUT TIME ZONE',
'datetime' => 'TIMESTAMP WITHOUT TIME ZONE',
'timestamp' => 'TIMESTAMP WITH TIME ZONE',
'year' => 'SMALLINT',
'binary' => 'BYTEA',
'varbinary' => 'BYTEA',
'tinyblob' => 'BYTEA',
'blob' => 'BYTEA',
'mediumblob' => 'BYTEA',
'longblob' => 'BYTEA',
'geometry' => 'TEXT',
'point' => 'TEXT',
'linestring' => 'TEXT',
'polygon' => 'TEXT',
'multipoint' => 'TEXT',
'multilinestring' => 'TEXT',
'multipolygon' => 'TEXT',
'geomcollection' => 'TEXT',
'geometrycollection' => 'TEXT',
);
/**
* A map of MySQL to PostgreSQL date format translation for TO_CHAR.
* MySQL: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
* PostgreSQL: https://www.postgresql.org/docs/current/functions-formatting.html
*/
const MYSQL_DATE_FORMAT_TO_PG_TO_CHAR_MAP = array(
'%a' => 'Dy', // Abbreviated weekday name (Sun..Sat)
'%b' => 'Mon', // Abbreviated month name (Jan..Dec)
'%c' => 'FMMM', // Month, numeric (0..12) -> PG FM removes leading zeros/spaces.
'%D' => 'FMDDth', // Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
'%d' => 'DD', // Day of the month, numeric (01..31)
'%e' => 'FMDD', // Day of the month, numeric (0..31) -> PG FM removes leading zero.
'%f' => 'US', // Microseconds (000000..999999)
'%H' => 'HH24',// Hour (00..23)
'%h' => 'HH12',// Hour (01..12)
'%I' => 'HH12',// Hour (01..12)
'%i' => 'MI', // Minutes, numeric (00..59)
'%j' => 'DDD', // Day of year (001..366)
'%k' => 'FMHH24',// Hour (0..23) -> PG FM removes leading zero.
'%l' => 'FMHH12',// Hour (1..12) -> PG FM removes leading zero.
'%M' => 'Month',// Month name (January..December)
'%m' => 'MM', // Month, numeric (01..12)
'%p' => 'AM', // AM or PM (Note: PG AM/PM is locale dependent, may need upper())
'%r' => 'HH12:MI:SS AM', // Time, 12-hour (hh:mm:ss followed by AM or PM)
'%S' => 'SS', // Seconds (00..59)
'%s' => 'SS', // Seconds (00..59)
'%T' => 'HH24:MI:SS', // Time, 24-hour (hh:mm:ss)
'%U' => 'WW', // Week (00..53), where Sunday is the first day of the week; WEEK for Start Monday
'%u' => 'WW', // Week (00..53), where Monday is the first day of the week
'%V' => 'IW', // Week (01..53), where Sunday is the first day of the week; used with %X
'%v' => 'IW', // Week (01..53), where Monday is the first day of the week; used with %x
'%W' => 'Day', // Weekday name (Sunday..Saturday)
'%w' => 'D', // Day of the week (0=Sunday..6=Saturday) -> PG D is 1 (Sun) to 7 (Sat)
'%X' => 'IYYY',// Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
'%x' => 'IYYY',// Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
'%Y' => 'YYYY',// Year, numeric, four digits
'%y' => 'YY', // Year, numeric (two digits)
// '%%' => '%' // A literal '%' character -> PG TO_CHAR doesn't need this, just use literal %
);
/**
* A map of MySQL data types to implicit default values for non-strict mode.
* PostgreSQL is generally stricter. Emulation of MySQL's non-strict behavior is complex.
* This map defines what MySQL *would* do. PostgreSQL will often error or use its own defaults.
*/
const DATA_TYPE_IMPLICIT_DEFAULT_MAP = array(
// Numeric data types:
'bit' => '0', // Or B'0'
'bool' => 'false',
'boolean' => 'false',
'tinyint' => '0',
'smallint' => '0',
'mediumint' => '0',
'int' => '0',
'integer' => '0',
'bigint' => '0',
'float' => '0',
'double' => '0',
'real' => '0',
'decimal' => '0',
'dec' => '0',
'fixed' => '0',
'numeric' => '0',
// String data types:
'char' => '',
'varchar' => '',
'nchar' => '',
'nvarchar' => '',
'tinytext' => '',
'text' => '',
'mediumtext' => '',
'longtext' => '',
'enum' => '', // In MySQL, first enum value if not nullable, '' if nullable. PG: error or ENUM default.
'set' => '',
'json' => 'null', // JSON 'null'
// Date and time data types:
'date' => '0000-00-00', // Invalid in PG, will error.
'time' => '00:00:00',
'datetime' => '0000-00-00 00:00:00', // Invalid in PG.
'timestamp' => '0000-00-00 00:00:00', // Invalid in PG.
'year' => '0000', // Invalid for SMALLINT.
// Binary data types:
'binary' => '', // \x
'varbinary' => '', // \x
'tinyblob' => '', // \x
'blob' => '', // \x
'mediumblob' => '', // \x
'longblob' => '', // \x
// Spatial data types (no implicit defaults in MySQL):
'geometry' => null,
// ...
);
/**
* The PostgreSQL server version.
*/
public $client_info; // Actually server_version for PostgreSQL
/**
* A MySQL query parser grammar.
*/
private static $mysql_grammar;
/**
* The main database name (MySQL concept). In PostgreSQL, this is the connected database.
*/
private $main_db_name;
/**
* The name of the current schema in use (PostgreSQL concept, analogous to MySQL USE db).
* Defaults to 'public'.
*/
private $current_schema_name;
/**
* An instance of the PDO object.
*/
public $pdo;
/**
* Service for managing MySQL INFORMATION_SCHEMA tables emulation in PostgreSQL.
*/
private $information_schema_builder;
/**
* Last executed MySQL query.
*/
private $last_mysql_query;
/**
* A list of PostgreSQL queries executed for the last MySQL query.
*/
private $last_pgsql_queries = array();
/**
* Results of the last emulated query.
*/
private $last_result;
/**
* Return value of the last emulated query.
*/
private $last_return_value;
/**
* Number of rows found by the last SQL_CALC_FOUND_ROW query.
*/
private $last_sql_calc_found_rows = null;
/**
* Whether the current MySQL query is read-only.
*/
private $is_readonly;
/**
* Transaction nesting level. PostgreSQL supports nested transactions via savepoints.
*/
private $transaction_level = 0;
/**
* The PDO fetch mode used for the emulated query.
*/
private $pdo_fetch_mode;
/**
* The currently active MySQL SQL modes (emulated).
* PostgreSQL does not have SQL modes in the same way. This will be for emulation logic.
*/
private $active_sql_modes = array(
// Defaults for MySQL 8.0
'ERROR_FOR_DIVISION_BY_ZERO', // PG default behavior if not dividing by zero literal
'NO_ENGINE_SUBSTITUTION', // N/A for PG
'NO_ZERO_DATE', // PG always errors on zero dates
'NO_ZERO_IN_DATE', // PG always errors on zero in dates
'ONLY_FULL_GROUP_BY', // PG default behavior
'STRICT_TRANS_TABLES', // PG is generally strict
);
/**
* Constructor.
*
* Set up a PostgreSQL connection and the MySQL-on-PostgreSQL driver.
*
* @param array $options {
* An array of options.
* @type string $dsn PDO DSN string for PostgreSQL.
* Example: "pgsql:host=localhost;dbname=mydb"
* @type string $username PostgreSQL username.
* @type string $password PostgreSQL password.
* @type array $pdo_options Optional. Additional PDO options.
* @type string $mysql_database_name The name of the emulated MySQL database (used for schema builder).
* @type string|null $default_schema Optional. Default PostgreSQL schema to use (e.g., 'public').
* }
*
* @throws WP_PostgreSQL_Driver_Exception When the driver initialization fails.
*/
public function __construct( array $options ) {
if ( ! isset( $options['dsn'] ) ) {
throw $this->new_driver_exception( 'Option "dsn" is required for PostgreSQL connection.' );
}
if ( ! isset( $options['mysql_database_name'] ) || ! is_string( $options['mysql_database_name'] ) ) {
throw $this->new_driver_exception( 'Option "mysql_database_name" (emulated) is required.' );
}
$this->main_db_name = $options['mysql_database_name']; // This is the *emulated* MySQL DB name.
$this->current_schema_name = $options['default_schema'] ?? 'public';
try {
$pdo_options = $options['pdo_options'] ?? array();
// Default PDO options for PostgreSQL
$pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
$pdo_options[PDO::ATTR_DEFAULT_FETCH_MODE] = PDO::FETCH_ASSOC; // Or as needed
// PostgreSQL returns numbers as numbers by default, stringify if MySQL behavior is strictly needed
// $pdo_options[PDO::ATTR_STRINGIFY_FETCHES] = true;
$this->pdo = new PDO(
$options['dsn'],
$options['username'] ?? null,
$options['password'] ?? null,
$pdo_options
);
} catch ( PDOException $e ) {
$code = $e->getCode();
throw $this->new_driver_exception( $e->getMessage(), is_int( $code ) ? $code : 0, $e );
}
// Check the PostgreSQL version.
$pgsql_version = $this->get_postgresql_version();
$this->client_info = $pgsql_version; // Store server version
// Set default schema path
$this->execute_pgsql_query( 'SET search_path TO ' . $this->quote_pgsql_identifier($this->current_schema_name) . ', public' );
// Register PostgreSQL specific functions if any were needed (less common than SQLite UDFs from PHP)
// WP_PostgreSQL_PDO_User_Defined_Functions::register_for( $this->pdo ); // Unlikely needed for PG
// Load MySQL grammar (using stubs as per problem description)
if ( null === self::$mysql_grammar ) {
self::$mysql_grammar = new WP_Parser_Grammar( require self::MYSQL_GRAMMAR_PATH );
}
// Initialize information schema builder
$this->information_schema_builder = new WP_PostgreSQL_Information_Schema_Builder(
$this->pdo,
$this->main_db_name, // Emulated MySQL DB name
$this->current_schema_name, // Actual PostgreSQL schema
self::RESERVED_PREFIX,
array( $this, 'execute_pgsql_query' )
);
$this->information_schema_builder->ensure_information_schema_tables();
}
public function get_pdo(): PDO {
return $this->pdo;
}
public function get_postgresql_version(): string {
return $this->pdo->query( 'SELECT version()' )->fetchColumn();
}
public function is_sql_mode_active( string $mode ): bool {
// PostgreSQL doesn't have direct SQL modes like MySQL. Emulation would be complex.
// This function would check against $this->active_sql_modes for emulated behavior.
return in_array( strtoupper( $mode ), $this->active_sql_modes, true );
}
public function get_last_mysql_query(): ?string {
return $this->last_mysql_query;
}
public function get_last_pgsql_queries(): array {
return $this->last_pgsql_queries;
}
/**
* Get the auto-increment value generated for the last query.
* For PostgreSQL, this often requires specifying the sequence name.
*
* @param string|null $sequence_name The name of the sequence (optional, but often needed for PostgreSQL).
* @return int|string
*/
public function get_insert_id(?string $sequence_name = null) {
// If insert query used RETURNING id, it would be in $this->last_result
// Otherwise, use PDO::lastInsertId with sequence name.
// Sequence name is typically table_column_seq, e.g. users_id_seq
// This needs to be determined or passed.
try {
$last_insert_id = $this->pdo->lastInsertId($sequence_name);
if (is_numeric($last_insert_id)) {
return (int) $last_insert_id;
}
return $last_insert_id;
} catch (PDOException $e) {
// lastInsertId() might fail if sequence name is wrong or not an auto-incrementing insert.
// Or if `RETURNING` was used and this method is called unnecessarily.
// Check $this->last_return_value if it contains the ID from RETURNING.
if (is_array($this->last_return_value) && isset($this->last_return_value[0]['id'])) { // Assuming 'id' is the common name
return (int) $this->last_return_value[0]['id'];
}
return 0; // Or throw error
}
}
public function query( string $query, $fetch_mode = PDO::FETCH_OBJ, ...$fetch_mode_args ) {
$this->flush();
$this->pdo_fetch_mode = $fetch_mode; // Note: PDOStatement::fetchAll can override this
$this->last_mysql_query = $query;
try {
// Parse the MySQL query (using stubs, so this part is mostly conceptual here)
$lexer = new WP_MySQL_Lexer( $query );
$tokens = $lexer->remaining_tokens();
$parser = new WP_MySQL_Parser( self::$mysql_grammar, $tokens );
$ast = $parser->parse();
if ( null === $ast ) {
// If stubs are used, AST will be a dummy. This check would fail for real input.
// For this conversion, assume AST generation is successful for structure.
// throw $this->new_driver_exception( 'Failed to parse the MySQL query.' );
}
// Handle transaction commands
$child = $ast->get_first_child();
if ( $child instanceof WP_Parser_Node && 'beginWork' === $child->rule_name ) {
$this->begin_transaction();
return true;
}
if ( $child instanceof WP_Parser_Node && 'simpleStatement' === $child->rule_name ) {
$subchild = $child->get_first_child_node( 'transactionOrLockingStatement' );
if ( null !== $subchild ) {
$tokens = $subchild->get_descendant_tokens();
$token1 = $tokens[0] ?? null;
$token2 = $tokens[1] ?? null;
if ( $token1 && WP_MySQL_Lexer::START_SYMBOL === $token1->id &&
$token2 && WP_MySQL_Lexer::TRANSACTION_SYMBOL === $token2->id ) {
$this->begin_transaction();
return true;
}
if ( $token1 && WP_MySQL_Lexer::BEGIN_SYMBOL === $token1->id ) {
$this->begin_transaction();
return true;
}
if ( $token1 && WP_MySQL_Lexer::COMMIT_SYMBOL === $token1->id ) {
$this->commit();
return true;
}
if ( $token1 && WP_MySQL_Lexer::ROLLBACK_SYMBOL === $token1->id ) {
$this->rollback();
return true;
}
}
}
// Perform all the queries in a nested transaction (PostgreSQL handles this with savepoints)
$this->begin_transaction();
$this->execute_mysql_query( $ast ); // This will translate and execute
$this->commit();
return $this->last_return_value;
} catch ( Throwable $e ) {
try {
$this->rollback();
} catch ( Throwable $rollback_exception ) {
// Ignore rollback errors if the main error is more important
}
$code = $e->getCode();
throw $this->new_driver_exception( $e->getMessage(), is_string($code) ? $code : 0, $e ); // SQLSTATE can be string
}
}
public function get_query_results() {
return $this->last_result;
}
public function get_last_return_value() {
return $this->last_return_value;
}
public function execute_pgsql_query( string $sql, array $params = array() ): PDOStatement {
$this->last_pgsql_queries[] = array(
'sql' => $sql,
'params' => $params,
);
$stmt = $this->pdo->prepare( $sql );
$stmt->execute( $params );
return $stmt;
}
public function begin_transaction(): void {
if ( 0 === $this->transaction_level ) {
$this->pdo->beginTransaction();
} else {
$this->execute_pgsql_query( 'SAVEPOINT LEVEL' . $this->transaction_level );
}
++$this->transaction_level;
}
public function commit(): void {
if ( 0 === $this->transaction_level ) {
return; // Or throw error: no transaction active
}
--$this->transaction_level;
if ( 0 === $this->transaction_level ) {
$this->pdo->commit();
} else {
$this->execute_pgsql_query( 'RELEASE SAVEPOINT LEVEL' . $this->transaction_level );
}
}
public function rollback(): void {
if ( 0 === $this->transaction_level ) {
return; // Or throw error: no transaction active
}
--$this->transaction_level;
if ( 0 === $this->transaction_level ) {
if ($this->pdo->inTransaction()) { // Check if transaction is actually active
$this->pdo->rollBack();
}
} else {
// Only rollback to savepoint if transaction is still active at PDO level
if ($this->pdo->inTransaction()) {
$this->execute_pgsql_query( 'ROLLBACK TO SAVEPOINT LEVEL' . $this->transaction_level );
}
}
}
private function execute_mysql_query( WP_Parser_Node $node ): void {
// This method structure remains similar, but implementations of execute_..._statement will change.
if ( 'query' !== $node->rule_name ) { // Assuming 'query' is the top-level rule name from parser
throw $this->new_driver_exception(
sprintf( 'Expected "query" node, got: "%s"', $node->rule_name )
);
}
$children = $node->get_child_nodes();
// The stub parser might not produce children correctly. This logic depends on real AST.
if (empty($children)) { // Adaptation for stub
// If AST is a stub, we can't proceed with rule-based dispatch.
// This indicates the limitation of not having the actual parser.
// We might try to guess based on raw query for very simple cases, or just fail.
// For this conversion, we'll assume the structure allows dispatch.
// If $node from stub parser is just a wrapper, get its 'simpleStatement' if that's how stub is made.
$simple_statement_node = $node->get_first_child_node('simpleStatement');
if (!$simple_statement_node) {
// Attempt to infer from query string for very basic commands if AST is too minimal
// This is a HACK due to parser stub.
if (preg_match('/^\s*SELECT/i', $this->last_mysql_query)) {
$this->is_readonly = true;
// Cannot call execute_select_statement without a proper AST node.
// Fallback: try to translate the whole query string if possible.
$query = $this->translate_raw_query($this->last_mysql_query);
$stmt = $this->execute_pgsql_query($query);
$this->set_results_from_fetched_data($stmt->fetchAll($this->pdo_fetch_mode));
return;
}
throw $this->new_driver_exception('Cannot process query due to parser stub limitations or invalid AST structure.');
}
$node_to_process = $simple_statement_node->get_first_child_node();
} else {
// Original logic assuming real AST
if ( count( $children ) !== 1 ) { // MySQL grammar might differ from SQLite one used before.
// This might need adjustment based on actual MySQL grammar structure.
// For now, assume similar top-level structure.
}
if ( 'simpleStatement' !== $children[0]->rule_name ) {
throw $this->new_driver_exception(
sprintf( 'Expected "simpleStatement" node, got: "%s"', $children[0]->rule_name )
);
}
$node_to_process = $children[0]->get_first_child_node();
}
if (!$node_to_process) {
// Handle cases where the AST structure from the stub might not match expectations
throw $this->new_driver_exception( 'Invalid AST structure for "simpleStatement".' );
}
switch ( $node_to_process->rule_name ) {
case 'selectStatement':
$this->is_readonly = true;
$this->execute_select_statement( $node_to_process );
break;
case 'insertStatement':
$this->execute_insert_statement( $node_to_process ); // แยก replace ออก
break;
case 'replaceStatement': // MySQL REPLACE is tricky. PG: INSERT ON CONFLICT or DELETE+INSERT
$this->execute_replace_statement( $node_to_process );
break;
case 'updateStatement':
$this->execute_update_statement( $node_to_process );
break;
case 'deleteStatement':
$this->execute_delete_statement( $node_to_process );
break;
case 'createStatement':
$subtree = $node_to_process->get_first_child_node();
if (!$subtree) break; // Stub protection
switch ( $subtree->rule_name ) {
case 'createDatabase':
// PG: CREATE DATABASE. But connections are per-DB. This might mean creating a new schema.
// Or, it's a no-op if we consider the current connection the "database".
$this->execute_create_database_statement( $subtree );
break;
case 'createTable':
$this->execute_create_table_statement( $node_to_process ); // Pass $node_to_process or $subtree
break;
case 'createIndex':
$this->execute_create_index_statement( $subtree );
break;
default:
throw $this->new_not_supported_exception(
sprintf( 'statement type: "%s" > "%s"', $node_to_process->rule_name, $subtree->rule_name )
);
}
break;
case 'alterStatement':
$subtree = $node_to_process->get_first_child_node();
if (!$subtree) break; // Stub protection
switch ( $subtree->rule_name ) {
case 'alterTable':
$this->execute_alter_table_statement( $node_to_process ); // Pass $node_to_process or $subtree
break;
default:
throw $this->new_not_supported_exception(
sprintf( 'statement type: "%s" > "%s"', $node_to_process->rule_name, $subtree->rule_name )
);
}
break;
case 'dropStatement':
$subtree = $node_to_process->get_first_child_node();
if (!$subtree) break; // Stub protection
switch ( $subtree->rule_name ) {
case 'dropDatabase':
$this->execute_drop_database_statement($subtree);
break;
case 'dropTable':
$this->execute_drop_table_statement( $node_to_process ); // Pass $node_to_process or $subtree
break;
case 'dropIndex':
$this->execute_drop_index_statement($subtree);
break;
default: // Generic fallback (less likely to work perfectly)
$query = $this->translate( $node_to_process );
$this->execute_pgsql_query( $query );
$this->set_result_from_affected_rows(); // May not be accurate for all DDL
}
break;
case 'truncateTableStatement':
$this->execute_truncate_table_statement( $node_to_process );
break;
case 'setStatement':
$this->execute_set_statement( $node_to_process );
break;
case 'showStatement':
$this->is_readonly = true;
$this->execute_show_statement( $node_to_process );
break;
case 'utilityStatement':
$subtree = $node_to_process->get_first_child_node();
if (!$subtree) break; // Stub protection
switch ( $subtree->rule_name ) {
case 'describeStatement': // DESCRIBE table;
$this->is_readonly = true;
$this->execute_describe_statement( $subtree );
break;
case 'useCommand': // USE database;
$this->execute_use_statement( $subtree );
break;
default:
throw $this->new_not_supported_exception(
sprintf( 'statement type: "%s" > "%s"', $node_to_process->rule_name, $subtree->rule_name )
);
}
break;
case 'tableAdministrationStatement': // ANALYZE, CHECK, OPTIMIZE, REPAIR
$this->execute_administration_statement( $node_to_process );
break;
default:
throw $this->new_not_supported_exception(
sprintf( 'statement type: "%s"', $node_to_process->rule_name )
);
}
}
// Translate a raw query string - basic keyword replacement for use with parser stubs
private function translate_raw_query(string $mysql_query): string {
$pgsql_query = $mysql_query;
// Basic backtick to double quote for identifiers
$pgsql_query = preg_replace_callback('/`([^`]+)`/', function($matches) {
return '"' . str_replace('""', '"', $matches[1]) . '"';
}, $pgsql_query);
// Add more simple, global replacements here if necessary for stubbed parsing
// e.g., NOW() -> CURRENT_TIMESTAMP (though PG supports NOW())
return $pgsql_query;
}
private function execute_select_statement( WP_Parser_Node $node ): void {
$query_expression_node = $node->get_first_child_node('queryExpression');
if (!$query_expression_node) { // Stub protection
// Fallback for stub, try to translate the whole node
$query = $this->translate($node);
$stmt = $this->execute_pgsql_query($query);
$this->set_results_from_fetched_data($stmt->fetchAll($this->pdo_fetch_mode));
return;
}
$has_sql_calc_found_rows = null !== $node->get_first_descendant_token(
WP_MySQL_Lexer::SQL_CALC_FOUND_ROWS_SYMBOL
);
if ( $has_sql_calc_found_rows ) {
// For PostgreSQL, use a window function COUNT(*) OVER()
// This requires modifying the SELECT list or using a CTE.
// Example: SELECT ... COUNT(*) OVER() AS total_rows ...
// The translation logic needs to inject this.
// For simplicity here, we'll do a separate count query, similar to original fallback.
// Find the main query body without LIMIT to count total rows
$count_expr_node = clone $query_expression_node; // Need a deep clone
// Remove LIMIT clause from $count_expr_node (complex AST manipulation)
// This is highly dependent on the parser's AST structure.
// For now, assume $this->translate can handle removing limit if a flag is set, or do it manually.
// A simpler approach: generate SQL for count and then for limited results.
$count_query_ast_parts = [];
foreach($query_expression_node->get_children() as $child) {
if ($child instanceof WP_Parser_Node && $child->rule_name === 'limitClause') {
continue;
}
$count_query_ast_parts[] = $child;
}
$count_query_sql_fragment = $this->translate_sequence($count_query_ast_parts);
$count_query = 'SELECT COUNT(*) AS cnt FROM (' . $count_query_sql_fragment . ') AS subquery_for_count';
$result = $this->execute_pgsql_query( $count_query );
$this->last_sql_calc_found_rows = (int) $result->fetchColumn();
// Now translate the original query with LIMIT
$query = $this->translate( $query_expression_node );
} else {
$this->last_sql_calc_found_rows = null;
$query = $this->translate( $query_expression_node );
}
$stmt = $this->execute_pgsql_query( $query );
$this->set_results_from_fetched_data(
$stmt->fetchAll( $this->pdo_fetch_mode ) // Fetch mode can be passed here too
);
}
private function execute_insert_statement( WP_Parser_Node $node ): void {
// MySQL: INSERT [IGNORE] INTO tbl [(cols)] VALUES (...) | SELECT ... [ON DUPLICATE KEY UPDATE ...]
// PG: INSERT INTO tbl [(cols)] VALUES (...) | SELECT ... [ON CONFLICT ... DO NOTHING | DO UPDATE SET ...]
$is_ignore = null !== $node->get_first_descendant_token( WP_MySQL_Lexer::IGNORE_SYMBOL );
$on_duplicate_key_update_node = $node->get_first_descendant_node('insertUpdateList'); // MySQL's ON DUPLICATE...
$parts = [];
$table_ref_node = $node->get_first_child_node('tableRef');
if (!$table_ref_node) { /* error or stub issue */ return; }
$parts[] = "INSERT INTO";
$parts[] = $this->translate($table_ref_node);
$columns_node = $node->get_first_child_node('uidList') ?? $node->get_first_child_node('columns'); // Adjust based on grammar for column list
if ($columns_node) {
$parts[] = $this->translate($columns_node);
}
// VALUES or SELECT part
$insert_source_node = $node->get_first_child_node('insertFromConstructor'); // For VALUES
if (!$insert_source_node) {
$insert_source_node = $node->get_first_child_node('insertQueryExpression'); // For SELECT
}
if ($insert_source_node) {
$parts[] = $this->translate($insert_source_node);
} else {
throw $this->new_driver_exception("Unsupported INSERT statement structure.");
}
if ($is_ignore) {
$parts[] = "ON CONFLICT DO NOTHING";
} elseif ($on_duplicate_key_update_node) {
// Need to identify conflict target (PRIMARY KEY or UNIQUE index columns)
// This requires schema introspection.
// $conflict_target = "ON CONFLICT (pk_column_or_unique_index_columns)"; // Placeholder
// For simplicity, assume a general conflict if target is not easily determined.
// $parts[] = "ON CONFLICT ON CONSTRAINT constraint_name"; // More robust
// $parts[] = "ON CONFLICT (col1, col2) WHERE index_predicate"; // For partial indexes
// A common case is conflict on primary key.
$table_name_str = $this->unquote_pgsql_identifier($this->translate($table_ref_node));
$pk_cols = $this->information_schema_builder->get_primary_key_columns($table_name_str);
if (!empty($pk_cols)) {
$conflict_target_cols = array_map([$this, 'quote_pgsql_identifier'], $pk_cols);
$parts[] = "ON CONFLICT (" . implode(', ', $conflict_target_cols) . ") DO UPDATE SET";
} else {
// Fallback if PK not found or complex unique index. This might fail.
// Consider querying for unique indexes if PK is not the only conflict source.
// For a generic solution, schema introspection is vital.
// Without it, ON CONFLICT (without target) might work if there's only one arbiter index.
// This part is hard to make universally correct without full schema awareness at translation time.
// Let's assume for now we can find PK or a simple unique index for conflict.
// If not, the query might need manual adjustment or this part improved.
$parts[] = "ON CONFLICT DO UPDATE SET"; // This is less specific and relies on PG finding an arbiter.
}
$update_list_node = $on_duplicate_key_update_node->get_first_child_node('updateList');
if ($update_list_node) {
// MySQL's VALUES(col) becomes EXCLUDED.col in PostgreSQL
$translated_update_list = $this->translate_update_list_for_conflict($update_list_node);
$parts[] = $translated_update_list;
} else {
throw $this->new_driver_exception("ON DUPLICATE KEY UPDATE clause is missing update assignments.");
}
}
// Add RETURNING for lastInsertId if it's an auto-increment table.
// This also requires schema introspection to find the serial/identity column.
// Example: $id_column = $this->information_schema_builder->get_identity_column($table_name_str);
// if ($id_column) { $parts[] = "RETURNING " . $this->quote_pgsql_identifier($id_column); }
$query = implode( ' ', $parts );
$stmt = $this->execute_pgsql_query( $query );
// If RETURNING was used, fetch the ID.
// if ($id_column) { $this->last_return_value = $stmt->fetchAll(PDO::FETCH_ASSOC); }
$this->set_result_from_affected_rows( $stmt->rowCount() );
}
private function execute_replace_statement( WP_Parser_Node $node ): void {
// MySQL REPLACE INTO:
// 1. If row exists (PK/UNIQUE conflict): DELETE old row, INSERT new row.
// 2. If row does not exist: INSERT new row.
// PostgreSQL: Can be emulated with ON CONFLICT (key) DO UPDATE or separate DELETE + INSERT.
// ON CONFLICT DO UPDATE is often preferred if applicable.
// The assignments in DO UPDATE must cover all columns to mimic REPLACE.
$table_ref_node = $node->get_first_child_node('tableRef');
if (!$table_ref_node) { /* error */ return; }
$table_name_str = $this->unquote_pgsql_identifier($this->translate($table_ref_node));
$parts = [];
$parts[] = "INSERT INTO";
$parts[] = $this->translate($table_ref_node);
$columns_node = $node->get_first_child_node('uidList') ?? $node->get_first_child_node('columns');
$column_names_for_set = [];
if ($columns_node) {
$parts[] = $this->translate($columns_node);
// Extract column names for the SET clause of ON CONFLICT DO UPDATE
foreach($columns_node->get_descendant_nodes('identifier') as $ident_node) { // Assuming 'identifier' is the rule for column names
$column_names_for_set[] = $this->unquote_pgsql_identifier($this->translate($ident_node));
}
}
$insert_source_node = $node->get_first_child_node('insertFromConstructor');
if (!$insert_source_node) {
$insert_source_node = $node->get_first_child_node('insertQueryExpression');
}
if ($insert_source_node) {
$parts[] = $this->translate($insert_source_node);
} else {
throw $this->new_driver_exception("Unsupported REPLACE statement structure.");
}
// Determine conflict target (PK or UNIQUE index columns)
$pk_cols = $this->information_schema_builder->get_primary_key_columns($table_name_str);
// Also consider other unique constraints if necessary
// $unique_constraints = $this->information_schema_builder->get_unique_constraints($table_name_str);
if (empty($pk_cols)) {
// REPLACE needs a unique key to function. If none, it's like INSERT.
// Or throw error as MySQL REPLACE relies on this.
throw $this->new_driver_exception("REPLACE INTO requires a PRIMARY or UNIQUE KEY on table '$table_name_str'.");
}
$conflict_target_cols_str = implode(', ', array_map([$this, 'quote_pgsql_identifier'], $pk_cols));
$parts[] = "ON CONFLICT (" . $conflict_target_cols_str . ") DO UPDATE SET";
// Construct SET assignments for DO UPDATE. All columns should be updated.
// If $columns_node was not present, we need to get all table columns from schema.
if (empty($column_names_for_set)) {
$all_table_columns = $this->information_schema_builder->get_table_columns($table_name_str);
// Exclude PK columns from SET if they are not meant to be updated (usually they are part of conflict target)
// However, REPLACE effectively updates all columns from the new row.
$column_names_for_set = $all_table_columns;
}
$set_clauses = [];
foreach($column_names_for_set as $col_name) {
// Don't update PK columns themselves in SET clause if they are part of conflict target (this is implicit)
// However, for REPLACE, all non-key columns are updated to values from the new row.
// if (in_array($col_name, $pk_cols)) continue; // Might be too restrictive for REPLACE
$quoted_col = $this->quote_pgsql_identifier($col_name);
$set_clauses[] = $quoted_col . " = EXCLUDED." . $quoted_col;
}
if (empty($set_clauses)) {
// This case means only PK columns, which doesn't make sense for update.
// If all columns are part of PK, effectively ON CONFLICT DO NOTHING (or error).
// MySQL's REPLACE might behave differently. A true REPLACE might mean DELETE then INSERT.
// For now, if SET is empty, it might be an issue.
// A common pattern for "replace all" is:
// ON CONFLICT (pk_col) DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2, ...
// where col1, col2 are all non-PK columns.
// If only PK columns are provided in INSERT, then there's nothing to update.
// MySQL's REPLACE would delete then insert the new row (with defaults for non-specified cols).
// This simple ON CONFLICT DO UPDATE SET might not fully emulate all nuances.
throw $this->new_driver_exception("Cannot form UPDATE SET clause for REPLACE on table '$table_name_str'. All columns might be part of the key.");
}
$parts[] = implode(', ', $set_clauses);
$query = implode( ' ', $parts );
$stmt = $this->execute_pgsql_query( $query );
$this->set_result_from_affected_rows( $stmt->rowCount() ); // rowCount for INSERT ON CONFLICT DO UPDATE can be 1 for insert, 1 for update. MySQL REPLACE reports 1 for insert, 2 for replace (delete+insert).
}
private function translate_update_list_for_conflict(WP_Parser_Node $update_list_node): string {
$assignments = [];
foreach ($update_list_node->get_child_nodes('updateElement') as $element_node) {
$column_ref_node = $element_node->get_first_child_node('columnRef');
$expr_node = $element_node->get_first_child_node('expr');
if (!$column_ref_node || !$expr_node) continue;
$column_name = $this->translate($column_ref_node); // Already quoted
// Check if expr_node is VALUES(col)
$simple_expr_node = $expr_node->get_first_child_node('simpleExpr');
$is_values_func = false;
if ($simple_expr_node) {
$func_call_node = $simple_expr_node->get_first_child_node('functionCall');
if ($func_call_node) {
$func_name_node = $func_call_node->get_first_child_node(); // Assuming function name identifier
if ($func_name_node && strtoupper($this->unquote_pgsql_identifier($this->translate($func_name_node))) === 'VALUES') {
$arg_node = $func_call_node->get_first_child_node('exprList')->get_first_child_node(); // Assuming one arg
$value_column_name = $this->translate($arg_node); // This is the col name inside VALUES()
$assignments[] = $column_name . ' = EXCLUDED.' . $value_column_name;
$is_values_func = true;
}
}
}
if (!$is_values_func) {
$assignments[] = $column_name . ' = ' . $this->translate($expr_node);
}
}
return implode(', ', $assignments);
}
private function execute_update_statement( WP_Parser_Node $node ): void {
// MySQL: UPDATE [IGNORE] tbl_references SET assignments [WHERE ...] [ORDER BY ...] [LIMIT ...]
// PG: UPDATE tbl SET assignments [FROM ...] [WHERE ...]
// PG UPDATE does not support ORDER BY or LIMIT directly.
// For UPDATE with LIMIT, use a subquery with ctid:
// UPDATE tbl SET ... WHERE ctid IN (SELECT ctid FROM tbl WHERE ... ORDER BY ... LIMIT ...);
$has_order = $node->has_child_node( 'orderClause' );
$has_limit = $node->has_child_node( 'simpleLimitClause' ); // MySQL specific rule name
$parts = [];
$parts[] = "UPDATE";
// Handle IGNORE - PG has no direct equivalent. May need complex error handling or be unsupported.
if ($node->has_child_token(WP_MySQL_Lexer::IGNORE_SYMBOL)) {
// This is hard to emulate perfectly. It means ignore errors like constraint violations.
// Could try to catch specific exceptions, but that's outside simple translation.
// For now, we can't directly translate IGNORE for UPDATE.
// Perhaps a plpgsql block with exception handling.
throw $this->new_not_supported_exception("UPDATE IGNORE");
}
$table_refs_node = $node->get_first_child_node('tableReferenceList'); // Or single tableReference
if (!$table_refs_node) { /* error */ return; }
// PG UPDATE syntax for multiple tables is different (uses FROM clause or CTEs)
// MySQL: UPDATE t1, t2 SET t1.c1 = ..., t2.c2 = ... WHERE ...
// This translation will focus on single-table updates first. Multi-table is complex.
$single_table_node = $table_refs_node->get_first_child_node('tableReference'); // Assuming single table for now
if (!$single_table_node) { /* error */ return; }
$table_name_translated = $this->translate($single_table_node);
$parts[] = $table_name_translated;
$parts[] = "SET";
$update_list_node = $node->get_first_child_node('updateList');
if (!$update_list_node) { /* error */ return; }
$parts[] = $this->translate($update_list_node);
$where_clause_node = $node->get_first_child_node('whereClause');
if ($has_limit || $has_order) {
// Use subquery for ctid (or primary key if table has no ctid/is partitioned)
// Assuming ctid is available and table is not partitioned in a way that breaks ctid uniqueness for the operation.
// A more robust way is to use the primary key.
$pk_cols = $this->information_schema_builder->get_primary_key_columns($this->unquote_pgsql_identifier($table_name_translated));
if (empty($pk_cols)) {
throw $this->new_driver_exception("UPDATE with ORDER BY/LIMIT requires a PRIMARY KEY on table for reliable emulation.");
}
$pk_col_quoted = $this->quote_pgsql_identifier($pk_cols[0]); // Assuming single col PK for simplicity
$sub_select_parts = [];
$sub_select_parts[] = "SELECT " . $pk_col_quoted; // Or ctid
$sub_select_parts[] = "FROM " . $table_name_translated;
if ($where_clause_node) {
$sub_select_parts[] = $this->translate($where_clause_node);
}
if ($has_order) {
$order_clause_node = $node->get_first_child_node('orderClause');
$sub_select_parts[] = $this->translate($order_clause_node);
}
if ($has_limit) {
$limit_clause_node = $node->get_first_child_node('simpleLimitClause'); // Or 'limitClause'
$sub_select_parts[] = $this->translate($limit_clause_node);
}
$parts[] = "WHERE " . $pk_col_quoted . " IN (" . implode(' ', $sub_select_parts) . ")";
} else {
if ($where_clause_node) {
$parts[] = $this->translate($where_clause_node);
}
}
$query = implode(' ', $parts);
$stmt = $this->execute_pgsql_query($query);
$this->set_result_from_affected_rows($stmt->rowCount());
}
private function execute_delete_statement( WP_Parser_Node $node ): void {
// MySQL: DELETE [IGNORE] [alias.*] FROM tbl_references [WHERE ...] [ORDER BY ...] [LIMIT ...]
// PG: DELETE FROM tbl [USING using_list] [WHERE ...]
// PG DELETE does not support ORDER BY or LIMIT directly. Subquery on ctid/PK needed.
// Multi-table delete in MySQL: DELETE t1, t2 FROM t1 JOIN t2 ...
// PG multi-table delete: DELETE FROM t1 USING t2 WHERE t1.id = t2.t1_id ...
$parts = [];
$parts[] = "DELETE FROM";
if ($node->has_child_token(WP_MySQL_Lexer::IGNORE_SYMBOL)) {
throw $this->new_not_supported_exception("DELETE IGNORE");
}
$table_alias_ref_list_node = $node->get_first_child_node('tableAliasRefList'); // For DELETE t1, t2 FROM ...
$table_name_node = $node->get_first_child_node('tableName'); // For DELETE FROM tbl
$table_reference_list_node = $node->get_first_child_node('tableReferenceList'); // For DELETE FROM tbl USING ...
$target_table_translated = "";
if ($table_alias_ref_list_node) {
// Multi-table delete: DELETE t1 FROM t1 JOIN t2 ...
// We need to identify the actual target table from the alias list.
// This is complex. Assuming only one alias for now for simplicity.
$first_alias_node = $table_alias_ref_list_node->get_first_child_node(); // tableAliasRef -> identifier
if (!$first_alias_node) { /* error */ return; }
$target_table_alias = $this->translate($first_alias_node); // This is an alias.
// We need to find the actual table name for this alias from the FROM/USING clause.
// The FROM clause is in tableReferenceList.
if (!$table_reference_list_node) { /* error */ return; }
// Find the table in tableReferenceList that matches target_table_alias
// This requires inspecting the structure of tableReferenceList (joins etc.)
// For now, this is a simplification.
// A full solution needs to map aliases to table names correctly.
// Let's assume the main table associated with the first alias is the delete target.
// This needs more robust alias resolution for complex joins.
$from_clause_str = $this->translate($table_reference_list_node);
// Find the table name corresponding to $target_table_alias
// This is non-trivial. The original code had logic for this with SQLite.
// For PG, if target_table_alias is "t1", and FROM is "real_table_name AS t1 JOIN ...",
// then DELETE FROM real_table_name AS t1 USING ...
// This needs proper AST traversal to map alias to actual table.
// For this translation, we'll assume the first table in the FROM clause is the target if aliased.
$first_table_in_from = $table_reference_list_node->get_first_descendant_node('tableRef'); // highly simplified
if ($first_table_in_from) {
$target_table_translated = $this->translate($first_table_in_from);
$parts[] = $target_table_translated . " AS " . $target_table_alias; // e.g. DELETE FROM real_table AS t1
$parts[] = "USING " . $from_clause_str; // The rest of the FROM becomes USING, excluding target. This needs to be massaged.
// The USING clause should not redefine the target table alias.
// E.g., if FROM is "t1 JOIN t2", and we delete from t1: DELETE FROM table1 AS t1 USING table2 AS t2 ...
// This is very hard to get right without full AST analysis.
throw $this->new_not_supported_exception("Complex multi-table DELETE translation.");
} else {
throw $this->new_driver_exception("Cannot determine target table for multi-table DELETE.");
}
} elseif ($table_name_node) { // Simple DELETE FROM tbl
$target_table_translated = $this->translate($table_name_node);
$parts[] = $target_table_translated;
} else {
throw $this->new_driver_exception("Unsupported DELETE statement structure: no target table identified.");
}
$where_clause_node = $node->get_first_child_node('whereClause');
$has_order = $node->has_child_node( 'orderClause' );
$has_limit = $node->has_child_node( 'limitClause' ); // MySQL rule name
if ($has_limit || $has_order) {
$pk_cols = $this->information_schema_builder->get_primary_key_columns($this->unquote_pgsql_identifier($target_table_translated));
if (empty($pk_cols)) {
throw $this->new_driver_exception("DELETE with ORDER BY/LIMIT requires a PRIMARY KEY on table for reliable emulation.");
}
$pk_col_quoted = $this->quote_pgsql_identifier($pk_cols[0]);
$sub_select_parts = [];
$sub_select_parts[] = "SELECT " . $pk_col_quoted;
$sub_select_parts[] = "FROM " . $target_table_translated; // If aliased, use alias if subquery refers to main query.
if ($where_clause_node) {
$sub_select_parts[] = $this->translate($where_clause_node);
}
if ($has_order) {
$order_clause_node = $node->get_first_child_node('orderClause');
$sub_select_parts[] = $this->translate($order_clause_node);
}
if ($has_limit) {
$limit_clause_node = $node->get_first_child_node('limitClause');
$sub_select_parts[] = $this->translate($limit_clause_node);
}
// If USING clause was added for multi-table delete, the WHERE for ctid needs to integrate that.
// This makes it even more complex.
$parts[] = "WHERE " . $pk_col_quoted . " IN (" . implode(' ', $sub_select_parts) . ")";
} else {
if ($where_clause_node) {
// If USING is present, the WHERE clause conditions might need to reference tables from both target and USING.
$parts[] = $this->translate($where_clause_node);
}
}
$query = implode(' ', $parts);
$stmt = $this->execute_pgsql_query($query);
$this->set_result_from_affected_rows($stmt->rowCount());
}
private function execute_create_database_statement(WP_Parser_Node $node): void {
$db_name_node = $node->get_first_child_node('identifier');
if (!$db_name_node) { /* error */ return; }
$db_name = $this->unquote_pgsql_identifier($this->translate($db_name_node));
// In PostgreSQL, CREATE DATABASE cannot be run inside a transaction block.
// Also, connections are per-database. "Creating a database" might mean
// creating a new SCHEMA within the current database for emulation.
// Or, if truly creating a new DB, it's an out-of-band operation.
// Current driver model is single PDO connection.
// Let's assume this means CREATE SCHEMA for emulation.
$if_not_exists_node = $node->get_first_child_node('ifNotExists');
$sql = "CREATE SCHEMA ";
if ($if_not_exists_node) {
$sql .= "IF NOT EXISTS ";
}
$sql .= $this->quote_pgsql_identifier($db_name);
try {
$this->execute_pgsql_query($sql);
$this->set_result_from_affected_rows(1); // Conventionally 1 for success
} catch (PDOException $e) {
// Handle "already exists" if IF NOT EXISTS wasn't used or supported for this version/context
if ($e->getCode() == '42P06' && !$if_not_exists_node) { // 42P06 is duplicate_schema
// MySQL CREATE DATABASE IF NOT EXISTS returns 0 rows affected if exists.
$this->set_result_from_affected_rows(0);
// Optionally, emit a warning like MySQL (1007: Can't create database ...; database exists)
} else {
throw $e;
}
}
}
private function execute_drop_database_statement(WP_Parser_Node $node): void {
$db_name_node = $node->get_first_child_node('identifier');
if (!$db_name_node) { /* error */ return; }
$db_name = $this->unquote_pgsql_identifier($this->translate($db_name_node));
// Similar to CREATE, map to DROP SCHEMA.
$if_exists_node = $node->get_first_child_node('ifExists');
$sql = "DROP SCHEMA ";
if ($if_exists_node) {
$sql .= "IF EXISTS ";
}
$sql .= $this->quote_pgsql_identifier($db_name);
$cascade_node = $node->get_first_child_node('dropBehavior'); // RESTRICT | CASCADE
if ($cascade_node && strtoupper($this->translate($cascade_node)) === 'CASCADE') {
$sql .= " CASCADE";
} else {
$sql .= " RESTRICT"; // Default for PG
}
try {
$this->execute_pgsql_query($sql);
$this->set_result_from_affected_rows(1);
} catch (PDOException $e) {
if ($e->getCode() == '3F000' && $if_exists_node) { // 3F000 invalid_schema_name (if IF EXISTS used)
$this->set_result_from_affected_rows(0);
} else {
throw $e;
}
}
}
private function execute_create_index_statement(WP_Parser_Node $node): void {
// MySQL: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (columns) [options]
// PG: CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] index_name ON tbl_name [USING method] (columns) [INCLUDE] [WITH storage_params] [TABLESPACE] [WHERE predicate]
$parts = ["CREATE"];
if ($node->has_child_token(WP_MySQL_Lexer::UNIQUE_SYMBOL)) $parts[] = "UNIQUE";
// FULLTEXT and SPATIAL indexes require specific PG extensions (e.g., RUM, PostGIS with GIST/GIN)
// and different USING method. This simple translation won't cover advanced index types.
if ($node->has_child_token(WP_MySQL_Lexer::FULLTEXT_SYMBOL)) {
// parts[] = "USING gin"; // or gist, depends on column type and pg_trgm or textsearch
// This needs translating the column to tsvector or using expression index.
throw $this->new_not_supported_exception("FULLTEXT index direct translation. Use PG text search features.");
}
if ($node->has_child_token(WP_MySQL_Lexer::SPATIAL_SYMBOL)) {
// parts[] = "USING gist"; // Typically for PostGIS geometry types
throw $this->new_not_supported_exception("SPATIAL index direct translation. Use PostGIS.");
}
$parts[] = "INDEX";
$index_name_node = $node->get_first_child_node('indexName'); // Or similar from grammar
if ($index_name_node) $parts[] = $this->translate($index_name_node);
$parts[] = "ON";
$table_name_node = $node->get_first_child_node('tableName'); // Or tableRef
if ($table_name_node) $parts[] = $this->translate($table_name_node);
$key_list_node = $node->get_first_child_node('keyListVariants') ?? $node->get_first_child_node('keyList'); // Grammar specific
if ($key_list_node) {
// Translate keyList (column names, lengths, ASC/DESC)
// PG supports ASC/DESC, NULLS FIRST/LAST. MySQL sub-part indexing (col(len)) needs expression index or is ignored.
$parts[] = $this->translate_index_columns_for_pg($key_list_node);
} else {
throw $this->new_driver_exception("CREATE INDEX missing column list.");
}
// Index type (USING BTREE, HASH, GIN, GIST) - MySQL index_type hint
$index_type_node = $node->get_first_child_node('indexType'); // grammar rule for USING type
if ($index_type_node) {
$mysql_index_type = strtoupper($this->translate($index_type_node));
$pg_method = "BTREE"; // Default
if ($mysql_index_type === "HASH") $pg_method = "HASH";
// GIN/GIST for FULLTEXT/SPATIAL are more complex.
$parts[] = "USING " . $pg_method;
}
$query = implode(' ', $parts);
$this->execute_pgsql_query($query);
// DDL often doesn't affect rows in the same way. Set to 0 or handle based on specific DDL.
$this->set_result_from_affected_rows(0);
}
private function translate_index_columns_for_pg(WP_Parser_Node $key_list_node): string {
$column_defs = [];
// This depends heavily on AST structure of keyList
// Example: keyList -> keyPart+, keyPart -> identifier [ (length) ] [ASC|DESC]
foreach($key_list_node->get_descendant_nodes('keyPart') as $key_part_node) {
$col_name_node = $key_part_node->get_first_child_node('identifier');
if (!$col_name_node) continue;
$col_def = $this->translate($col_name_node);
// MySQL col(length) prefix indexing: PG doesn't support this directly on standard types.
// Can use expression index on SUBSTRING(col, 1, length) or specific opclasses.
// For simplicity, ignore length for now or throw not_supported.
$length_node = $key_part_node->get_first_child_node('fieldLength'); // or similar
if ($length_node) {
// log warning or throw error: "Prefix indexing col(length) not directly supported, using full column."
}
$direction_node = $key_part_node->get_first_child_token(); // Assuming ASC/DESC token
if ($direction_node) {
if (strtoupper($direction_node->value) === "DESC") $col_def .= " DESC";
// ASC is default
}
$column_defs[] = $col_def;
}
return "(" . implode(', ', $column_defs) . ")";
}
private function execute_drop_index_statement(WP_Parser_Node $node): void {
// MySQL: DROP INDEX index_name ON tbl_name
// PG: DROP INDEX [CONCURRENTLY] [IF EXISTS] index_name [CASCADE | RESTRICT]
// Note: PG's DROP INDEX doesn't specify table name if index name is schema-qualified or unique.
// MySQL needs table name. If index name is globally unique in PG (within schema), table not needed.
// However, to be safe and if index names might not be unique across tables:
// We need to find the schema of the index.
$parts = ["DROP INDEX"];
// $if_exists_node = $node->get_first_child_node('ifExists'); // From grammar if it exists
// if ($if_exists_node) $parts[] = "IF EXISTS";
$index_name_node = $node->get_first_child_node('indexRef'); // Or indexName
if (!$index_name_node) { /* error */ return; }
$index_name = $this->translate($index_name_node); // This should be the qualified index name if possible
// MySQL's DROP INDEX includes ON tbl_name. PostgreSQL's does not.
// If the index name from MySQL is not schema-qualified, and we store it as table__indexname,
// then it is schema-qualified by current_schema.
// If index names are unique per schema in PG, then just the name is fine.
// For safety, if information_schema_builder mangles index names to be unique (e.g. tbl__idx), use that.
$parts[] = $index_name; // Potentially $this->current_schema_name . "." . $index_name if needed.
// Cascade/Restrict not typically in MySQL DROP INDEX syntax directly, default is like RESTRICT.
// $parts[] = "CASCADE"; // If needed for dependencies.
$query = implode(' ', $parts);
$this->execute_pgsql_query($query);
$this->set_result_from_affected_rows(0);
}
private function execute_create_table_statement( WP_Parser_Node $node ): void {
$subnode = $node->get_first_child_node(); // This should be 'createTable' node
$table_is_temporary = $subnode->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
$table_name_node = $subnode->get_first_child_node( 'tableName' );
if (!$table_name_node) { /* error */ return; }
$table_name = $this->unquote_pgsql_identifier( $this->translate( $table_name_node ) );
// Handle IF NOT EXISTS
if ( $subnode->has_child_node( 'ifNotExists' ) ) {
// Use information_schema_builder to check. Or rely on PG's IF NOT EXISTS.
$table_exists = $this->information_schema_builder->table_exists_in_emulated_schema($table_name, $table_is_temporary);
if ( $table_exists ) {
$this->set_result_from_affected_rows( 0 ); // MySQL behavior: 0 rows, possible warning.
// Optionally, issue a notice/warning similar to MySQL.
return;
}
}
// Record in our emulated MySQL information schema
$this->information_schema_builder->record_create_table( $node );
// Generate PostgreSQL CREATE TABLE statement from our emulated schema info
$queries = $this->get_pgsql_create_table_statement( $table_is_temporary, $table_name );
$create_table_query = $queries[0]; // Main CREATE TABLE
$constraint_and_index_queries = array_slice( $queries, 1 ); // Separate ALTER TABLE for constraints, CREATE INDEX
$this->execute_pgsql_query( $create_table_query );
foreach ( $constraint_and_index_queries as $query ) {
$this->execute_pgsql_query( $query );
}
$this->set_result_from_affected_rows(0); // DDL
}
private function execute_alter_table_statement( WP_Parser_Node $node ): void {
$alter_table_node = $node->get_first_child_node(); // This should be 'alterTable'
$table_ref_node = $alter_table_node->get_first_descendant_node( 'tableRef' );
if (!$table_ref_node) { /* error */ return; }
$table_name = $this->unquote_pgsql_identifier( $this->translate( $table_ref_node ) );
$table_is_temporary = $this->information_schema_builder->temporary_table_exists_in_emulated_schema( $table_name );
// PostgreSQL's ALTER TABLE is powerful but syntax differs from MySQL.
// Each alter spec (ADD COLUMN, DROP COLUMN, CHANGE COLUMN, etc.) needs translation.
// This is a very complex part.
// MySQK: ALTER TABLE tbl_name ADD COLUMN ..., CHANGE COLUMN ..., MODIFY COLUMN ..., DROP COLUMN ..., ADD INDEX ...
// PG: ALTER TABLE tbl_name ADD COLUMN ..., DROP COLUMN ..., ALTER COLUMN ... TYPE ..., RENAME COLUMN ... TO ..., ADD CONSTRAINT ..., CREATE INDEX ...
// First, record changes in our emulated schema
// This is crucial because the recreation logic relies on the emulated schema.
$column_map_for_recreate = $this->information_schema_builder->record_alter_table( $node );
// Simple ALTER TABLE actions might be translated directly.
// Complex ones (multiple changes, reordering, changing type that requires data rewrite)
// might force a table recreate, similar to SQLite driver's approach.
// PostgreSQL ALTER TABLE is more capable, so recreation is less often needed.
// However, to perfectly emulate MySQL behavior or if translation is too complex, recreation is fallback.
// Attempt direct translation of ALTER specs first.
// If a spec is too complex or requires full table rewrite not supported by single ALTER in PG,
// then fall back to recreate.
$alter_specs = $alter_table_node->get_descendant_nodes('alterListItem');
$pg_alter_clauses = [];
$needs_recreate = false;
foreach ($alter_specs as $spec_node) {
$translated_spec = $this->translate_alter_table_spec_for_pg($spec_node, $table_name);
if ($translated_spec === null) { // Indicates spec requires recreate or is unsupported
$needs_recreate = true;
break;
}
if (is_array($translated_spec)) { // Some specs might become multiple PG clauses or separate statements
foreach ($translated_spec as $clause) {
if (str_starts_with(strtoupper(trim($clause)), "CREATE INDEX")) {
// Handle CREATE INDEX separately after ALTER TABLE
$this->execute_pgsql_query($clause);
} else {
$pg_alter_clauses[] = $clause;
}
}
} else {
$pg_alter_clauses[] = $translated_spec;
}
}
if (!$needs_recreate && !empty($pg_alter_clauses)) {
$alter_query = "ALTER TABLE " . $this->quote_pgsql_identifier($table_name) . " " . implode(', ', $pg_alter_clauses);
$this->execute_pgsql_query($alter_query);
} elseif ($needs_recreate) {
// Fallback to recreate table (similar to SQLite driver but using PG specific recreation)
// $this->recreate_table_from_information_schema($table_is_temporary, $table_name, $column_map_for_recreate);
throw $this->new_not_supported_exception("Complex ALTER TABLE requires table recreation (not fully implemented for PG yet).");
}
$this->set_result_from_affected_rows(0); // DDL
}
private function translate_alter_table_spec_for_pg(WP_Parser_Node $spec_node, string $table_name): string|array|null {
// This is a massive undertaking. Here are a few examples:
$first_token = $spec_node->get_first_child_token();
if (!$first_token) return null;
switch ($first_token->id) {
case WP_MySQL_Lexer::ADD_SYMBOL:
$column_def_node = $spec_node->get_first_descendant_node('columnDefinition');
if ($column_def_node) { // ADD COLUMN col_def
$col_name_node = $column_def_node->get_first_child_node('fieldIdentifier');
$col_type_node = $column_def_node->get_first_child_node('dataType');
// ... extract other attributes (NOT NULL, DEFAULT)
if (!$col_name_node || !$col_type_node) return null;
$col_name = $this->translate($col_name_node);
$col_type = $this->translate_datatype_for_pg($col_type_node); // Special type translation
$clause = "ADD COLUMN " . $col_name . " " . $col_type;
// Add NOT NULL, DEFAULT etc. from $column_def_node
// ...
return $clause;
}
$constraint_def_node = $spec_node->get_first_descendant_node('tableConstraintDef');
if ($constraint_def_node) { // ADD CONSTRAINT / ADD INDEX
// If it's an index (KEY, INDEX), translate to CREATE INDEX (separate statement)
// If it's PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, translate to ADD CONSTRAINT
$keyword_node = $constraint_def_node->get_first_child_token(); // PRIMARY, UNIQUE, FOREIGN, KEY etc
if ($keyword_node && ($keyword_node->id == WP_MySQL_Lexer::KEY_SYMBOL || $keyword_node->id == WP_MySQL_Lexer::INDEX_SYMBOL)) {
// Build CREATE INDEX statement
$idx_name_node = $constraint_def_node->get_first_child_node('indexName');
$idx_name = $idx_name_node ? $this->translate($idx_name_node) : $this->quote_pgsql_identifier($table_name . "_" . uniqid("idx_"));
$idx_cols_node = $constraint_def_node->get_first_child_node('keyListVariants'); // Or keyList
if (!$idx_cols_node) return null;
$idx_cols_str = $this->translate_index_columns_for_pg($idx_cols_node);
return "CREATE INDEX " . $idx_name . " ON " . $this->quote_pgsql_identifier($table_name) . " " . $idx_cols_str;
}
// ... other ADD CONSTRAINT cases
}
return null; // Unsupported ADD variant
case WP_MySQL_Lexer::DROP_SYMBOL:
$column_ref_node = $spec_node->get_first_child_node('fieldIdentifier');
if ($column_ref_node) { // DROP COLUMN col
return "DROP COLUMN " . $this->translate($column_ref_node);
}
$index_ref_node = $spec_node->get_first_child_node('indexRef'); // For DROP INDEX
if ($index_ref_node) {
// PG: DROP INDEX index_name. MySQL: ALTER TABLE ... DROP INDEX index_name.
// This should be a separate DROP INDEX statement.
return "DROP INDEX " . $this->translate($index_ref_node); // Assuming index name is unique or schema qualified
}
// DROP PRIMARY KEY, DROP FOREIGN KEY ...
return null;
case WP_MySQL_Lexer::CHANGE_SYMBOL: // CHANGE [COLUMN] old_col new_col col_def
$old_col_node = $spec_node->get_first_child_node('fieldIdentifier');
$new_col_name_node = $spec_node->get_first_child_node('identifier'); // This is new name
$col_def_node = $spec_node->get_first_descendant_node('fieldDefinition');
if (!$old_col_node || !$new_col_name_node || !$col_def_node) return null;
$clauses = [];
$old_col_name = $this->translate($old_col_node);
$new_col_name = $this->translate($new_col_name_node);
// 1. RENAME
if ($old_col_name !== $new_col_name) {
$clauses[] = "RENAME COLUMN " . $old_col_name . " TO " . $new_col_name;
}
// 2. ALTER TYPE (if changed)
$new_type_node = $col_def_node->get_first_child_node('dataType');
if ($new_type_node) {
$new_type_str = $this->translate_datatype_for_pg($new_type_node);
// Compare with old type from schema. If different, add ALTER COLUMN ... TYPE.
// This might need USING clause for data conversion.
// $old_type_from_schema = $this->information_schema_builder->get_column_type(...);
// For simplicity, assume it can be changed or will be handled by recreation.
$clauses[] = "ALTER COLUMN " . $new_col_name . " TYPE " . $new_type_str; // Needs USING for non-castable
}
// 3. SET/DROP NOT NULL
// 4. SET/DROP DEFAULT
// ...
return $clauses;
case WP_MySQL_Lexer::MODIFY_SYMBOL: // MODIFY [COLUMN] col col_def
// Similar to CHANGE but no rename.
// Translate to ALTER COLUMN ... TYPE, SET/DROP NOT NULL, SET/DROP DEFAULT
return null; // Placeholder
}
return null; // Fallback for unsupported spec
}
private function translate_datatype_for_pg(WP_Parser_Node $datatype_node): string {
// This needs to look at $datatype_node structure (type name, length, precision, etc.)
// and map to a full PostgreSQL type string.
// Example: MySQL VARCHAR(255) -> PG VARCHAR(255)
// MySQL INT UNSIGNED -> PG INTEGER (PG INTEGER is signed, for UNSIGNED need CHECK constraint or BIGINT if range exceeds)
// MySQL SERIAL -> PG SERIAL
$type_token_node = $datatype_node->get_first_child_token(); // Highly simplified
if (!$type_token_node) return "TEXT"; // fallback
$mysql_base_type_token_id = $type_token_node->id;
$pg_base_type = self::DATA_TYPE_MAP[$mysql_base_type_token_id] ?? 'TEXT'; // Default to TEXT
if ($mysql_base_type_token_id === WP_MySQL_Lexer::SERIAL_SYMBOL) return "SERIAL"; // or BIGSERIAL
// Handle length/precision from AST (e.g., fieldLength, precision nodes)
$length_node = $datatype_node->get_first_descendant_node('fieldLength');
$precision_node = $datatype_node->get_first_descendant_node('precision');
$length_str = "";
if ($length_node) {
$length_str = $this->translate($length_node); // e.g. (255) or (10,2)
} elseif ($precision_node) {
$length_str = $this->translate($precision_node);
}
// Combine base type with length/precision
// E.g. if $pg_base_type is VARCHAR and $length_str is (255) -> VARCHAR(255)
if (in_array($pg_base_type, ['CHAR', 'VARCHAR', 'BIT', 'VARBIT']) && $length_str) {
return $pg_base_type . $length_str;
}
if (in_array($pg_base_type, ['NUMERIC', 'DECIMAL']) && $length_str) {
return $pg_base_type . $length_str;
}
// For types like INT, TEXT, DATE, length is not appended or handled differently.
// Handle UNSIGNED for integer types - PG has no direct unsigned.
// Typically map to next larger signed type or add a CHECK constraint.
// For simplicity in translation, often ignored or mapped to standard signed type.
if ($datatype_node->has_child_token(WP_MySQL_Lexer::UNSIGNED_SYMBOL)) {
if ($pg_base_type === 'SMALLINT' || $pg_base_type === 'INTEGER') {
// Could return BIGINT if MySQL unsigned int might exceed PG signed int max.
// Or add CHECK (col >= 0)
}
}
return $pg_base_type . $length_str; // May need refinement
}
private function execute_drop_table_statement( WP_Parser_Node $node ): void {
// MySQL: DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name ...] [RESTRICT | CASCADE]
// PG: DROP TABLE [IF EXISTS] tbl_name [, ...] [CASCADE | RESTRICT]
$drop_table_node = $node->get_first_child_node(); // the 'dropTable' rule node
$is_temporary = $drop_table_node->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
$if_exists = $drop_table_node->has_child_node( 'ifExists' );
// Record in our emulated schema first
$this->information_schema_builder->record_drop_table( $node );
$table_refs_list_node = $drop_table_node->get_first_child_node('tableRefList');
if (!$table_refs_list_node) { /* error */ return; }
$table_names_to_drop = [];
foreach ($table_refs_list_node->get_child_nodes('tableRef') as $table_ref_node) {
$table_names_to_drop[] = $this->translate($table_ref_node); // Already quoted
}
if (empty($table_names_to_drop)) return;
$sql = "DROP TABLE ";
if ($is_temporary) $sql = "DROP TABLE "; // PG TEMP tables are dropped automatically, or DROP TABLE temp_tbl_name
if ($if_exists) $sql .= "IF EXISTS ";
$sql .= implode(', ', $table_names_to_drop);
$behavior_node = $drop_table_node->get_first_child_node('dropBehavior'); // For RESTRICT/CASCADE
if ($behavior_node) {
$behavior = strtoupper($this->translate($behavior_node));
if ($behavior === "CASCADE") $sql .= " CASCADE";
// RESTRICT is default in PG
}
$this->execute_pgsql_query($sql);
$this->set_result_from_affected_rows(0); // DDL
}
private function execute_truncate_table_statement( WP_Parser_Node $node ): void {
// MySQL: TRUNCATE [TABLE] tbl_name
// PG: TRUNCATE [TABLE] [ONLY] tbl_name [*] [, ...] [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]
$table_ref_node = $node->get_first_child_node( 'tableRef' );
if (!$table_ref_node) { /* error */ return; }
$table_name = $this->translate( $table_ref_node ); // Already quoted
// MySQL TRUNCATE implicitly resets auto_increment. PG needs RESTART IDENTITY.
$sql = "TRUNCATE TABLE " . $table_name . " RESTART IDENTITY";
// Add CASCADE if dependencies should be truncated (e.g. foreign keys)
// MySQL TRUNCATE doesn't activate triggers. PG TRUNCATE does by default.
// To avoid triggers: ALTER TABLE tbl DISABLE TRIGGER USER; TRUNCATE...; ALTER TABLE tbl ENABLE TRIGGER USER;
// This is more complex than direct translation.
$this->execute_pgsql_query( $sql );
$this->set_result_from_affected_rows(0); // DDL
}
private function execute_set_statement( WP_Parser_Node $node ): void {
// MySQL SET var = value, SESSION var = value, GLOBAL var = value
// PG SET var TO value, SET SESSION var TO value, SET LOCAL var TO value
// GLOBAL settings in PG are usually via postgresql.conf or ALTER SYSTEM.
// $this->active_sql_modes emulation would be handled here.
// This is highly dependent on the variables being set.
// For sql_mode:
// Iterate through assignments like in original.
// If var_name is 'sql_mode' and scope is SESSION (default for MySQL SET without scope):
// $value = ... (parsed value of modes)
// $this->active_sql_modes = explode(',', strtoupper($value));
// $this->last_result = 0; // Or true
// return;
// For other variables like 'names', 'character_set_client', etc.
// SET client_encoding TO 'UTF8'; (PG equiv of SET NAMES 'utf8')
// This requires specific translation for each MySQL variable to its PG counterpart or behavior.
// For now, mostly a NO-OP or only handle sql_mode for emulation layer.
// Simplified: Only update emulated SQL modes.
// Complex SET statements (multiple vars, complex expressions) need full parsing from AST.
// This is a placeholder due to parser stub complexity.
$assignment_nodes = $node->get_descendant_nodes('optionValue'); // Or similar rule
foreach($assignment_nodes as $assign_node) {
$var_name_node = $assign_node->get_first_child_node('internalVariableName') ?? $assign_node->get_first_child_node('setSystemVariable');
$value_node = $assign_node->get_first_child_node('setExprOrDefault');
if ($var_name_node && $value_node) {
$var_name = strtolower($this->unquote_pgsql_identifier($this->translate($var_name_node->get_first_child_node('textOrIdentifier') ?? $var_name_node))); // Extract actual name string
$var_value_str = $this->translate($value_node); // This will be a string literal 'value'
$var_value_str = trim($var_value_str, "'"); // Remove quotes
if ($var_name === 'sql_mode') {
$scope_token = $var_name_node->get_first_child_node('setVarIdentType'); // GLOBAL/SESSION
$is_global = $scope_token && strtoupper($this->translate($scope_token)) === 'GLOBAL';
if (!$is_global) { // Assume SESSION if not GLOBAL
$this->active_sql_modes = explode(',', strtoupper($var_value_str));
} else {
// GLOBAL sql_mode not directly translatable to live PG session.
// Would require ALTER SYSTEM or config change.
throw $this->new_not_supported_exception("SET GLOBAL sql_mode");
}
} elseif (str_starts_with($var_name, 'character_set_') || $var_name === 'names') {
// Example: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
// PG: SET client_encoding TO 'UTF8'; (Collation is per-column/db, not session var like this)
if ($var_name === 'names' || $var_name === 'character_set_client' || $var_name === 'character_set_results' || $var_name === 'character_set_connection') {
$pg_encoding = $this->map_mysql_charset_to_pg_encoding($var_value_str);
$this->execute_pgsql_query("SET client_encoding TO " . $this->pdo->quote($pg_encoding));
}
} else {
// Other session variables might have PG equivalents.
// SET time_zone = '...'; -> SET TIME ZONE '...';
if ($var_name === 'time_zone') {
$this->execute_pgsql_query("SET TIME ZONE " . $this->pdo->quote($var_value_str));
}
}
}
}
$this->last_result = 0; // Or true depending on MySQL behavior
}
private function map_mysql_charset_to_pg_encoding(string $mysql_charset): string {
$mysql_charset = strtolower($mysql_charset);
$map = [
'utf8' => 'UTF8',
'utf8mb4' => 'UTF8',
'latin1' => 'LATIN1',
// Add more common mappings
];
return $map[$mysql_charset] ?? 'UTF8'; // Default to UTF8
}
private function execute_show_statement( WP_Parser_Node $node ): void {
$tokens = $node->get_child_tokens(); // Assuming direct children are tokens for SHOW type
if (empty($tokens) || count($tokens) < 2) {
// Fallback if AST is stubbed / different
$raw_show_query = $this->last_mysql_query; // The original MySQL query
if (preg_match('/SHOW\s+TABLES/i', $raw_show_query)) {
$this->execute_show_tables_statement($node); return;
}
if (preg_match('/SHOW\s+COLUMNS\s+FROM\s+(`?\w+`?)/i', $raw_show_query, $m)) {
// Create a fake node structure for execute_show_columns_statement
// This is a hack for stubbed parser.
$fake_table_ref_node = new WP_Parser_Node(0, 'tableRef');
$fake_table_ref_node->append_child(new WP_Parser_Node(0,'identifier')); // Add identifier child to tableRef
$fake_table_ref_node->get_first_child_node()->append_child(new WP_MySQL_Token(WP_MySQL_Lexer::IDENTIFIER, $m[1]));
$fake_show_columns_node = new WP_Parser_Node(0,'showStatement'); // Dummy node
$fake_show_columns_node->append_child($fake_table_ref_node); // Attach the faked tableRef
$this->execute_show_columns_statement($fake_show_columns_node); return;
}
throw $this->new_not_supported_exception("Complex SHOW statement with stubbed parser");
}
$keyword1 = $tokens[1]; // e.g. TABLES, COLUMNS, DATABASES, VARIABLES, STATUS, INDEX, CREATE
$keyword2 = $tokens[2] ?? null; // e.g. TABLE for SHOW CREATE TABLE
switch ( $keyword1->id ) {
case WP_MySQL_Lexer::COLUMNS_SYMBOL:
case WP_MySQL_Lexer::FIELDS_SYMBOL:
$this->execute_show_columns_statement( $node );
break;
case WP_MySQL_Lexer::CREATE_SYMBOL:
if ($keyword2 && WP_MySQL_Lexer::TABLE_SYMBOL === $keyword2->id) {
$this->execute_show_create_table_statement($node);
} elseif ($keyword2 && $keyword2->id == WP_MySQL_Lexer::DATABASE_SYMBOL) { // SHOW CREATE DATABASE
// $this->execute_show_create_database_statement($node);
throw $this->new_not_supported_exception("SHOW CREATE DATABASE");
} else {
throw $this->new_not_supported_exception("Unsupported SHOW CREATE variant.");
}
break;
case WP_MySQL_Lexer::INDEX_SYMBOL: // SHOW INDEX | INDEXES | KEYS
case WP_MySQL_Lexer::INDEXES_SYMBOL:
case WP_MySQL_Lexer::KEYS_SYMBOL:
$this->execute_show_index_statement( $node );
break;
case WP_MySQL_Lexer::DATABASES_SYMBOL: // SHOW DATABASES
// case WP_MySQL_Lexer::SCHEMAS_SYMBOL: // MySQL alias for DATABASES
$this->execute_show_databases_statement($node);
break;
case WP_MySQL_Lexer::GRANTS_SYMBOL: // SHOW GRANTS
// This is complex, depends on users. Emulate with fixed response or query pg_roles/pg_authid.
$this->set_results_from_fetched_data([
(object)['Grants for user' => 'GRANT ALL PRIVILEGES ON DATABASE ...'] // Simplified
]);
break;
case WP_MySQL_Lexer::TABLE_SYMBOL: // SHOW TABLE STATUS
if ($keyword2 && strtoupper($keyword2->value) === 'STATUS') { // Check AST node name if available
$this->execute_show_table_status_statement( $node );
} else {
throw $this->new_not_supported_exception("Unsupported SHOW TABLE variant.");
}
break;
case WP_MySQL_Lexer::TABLES_SYMBOL: // SHOW TABLES
$this->execute_show_tables_statement( $node );
break;
case WP_MySQL_Lexer::VARIABLES_SYMBOL: // SHOW VARIABLES
$this->execute_show_variables_statement($node);
break;
// case WP_MySQL_Lexer::STATUS_SYMBOL: // SHOW STATUS (global/session status vars)
// $this->execute_show_status_statement($node);
// break;
default:
throw $this->new_not_supported_exception(
sprintf('SHOW statement type: "%s"', $keyword1->value)
);
}
}
private function execute_show_tables_statement(WP_Parser_Node $node): void {
// MySQL: SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
// PG: SELECT tablename FROM pg_tables WHERE schemaname = '...'
// OR: SELECT table_name FROM information_schema.tables WHERE table_schema = '...' AND table_type = 'BASE TABLE'
$db_name_node = $node->get_first_child_node('inDb'); // Or fromDb
$schema_name = $this->current_schema_name;
if ($db_name_node) {
$ident_node = $db_name_node->get_first_child_node('identifier');
$schema_name = $this->unquote_pgsql_identifier($this->translate($ident_node));
if (strtolower($schema_name) === 'information_schema') {
// Allow querying PG's own information_schema directly
} else if ($schema_name !== $this->main_db_name && strtolower($schema_name) !== strtolower($this->current_schema_name)) {
// If emulating multiple MySQL DBs as PG schemas, this could be valid.
// For now, assume current_schema_name is the target unless it's specifically information_schema.
// $this->current_schema_name = $schema_name; // This would change context for future queries.
// Or just use it for this query.
}
}
$sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = :schema_name AND table_type = 'BASE TABLE'";
$params = [':schema_name' => $schema_name];
$like_or_where_node = $node->get_first_child_node('likeOrWhere');
if ($like_or_where_node) {
$like_clause_node = $like_or_where_node->get_first_child_node('likeClause');
if ($like_clause_node) {
$pattern_node = $like_clause_node->get_first_child_node('textStringLiteral');
$pattern = $this->unquote_string_literal($this->translate($pattern_node)); // unquote 'pattern'
$sql .= " AND table_name LIKE :pattern";
$params[':pattern'] = $pattern;
} else {
$where_expr_node = $like_or_where_node->get_first_child_node('whereClause')->get_first_child_node('expr');
// Translating arbitrary WHERE clause is complex. Assume it refers to `table_name`.
// This needs to be parsed and field names potentially remapped.
// Simplified: `AND ` . $this->translate($where_expr_node) (risky if expr is complex)
// For now, only support simple LIKE.
throw $this->new_not_supported_exception("SHOW TABLES WHERE (complex expr)");
}
}
$is_full = $node->get_first_descendant_token(WP_MySQL_Lexer::FULL_SYMBOL) !== null;
$key_column_name = "Tables_in_" . $this->main_db_name; // Emulated MySQL DB name
$stmt = $this->execute_pgsql_query($sql, $params);
$results = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$obj = new stdClass();
$obj->{$key_column_name} = $row['table_name'];
if ($is_full) {
// MySQL 'Table_type' can be BASE TABLE, VIEW, SYSTEM VIEW, TEMPORARY
// PG information_schema.tables.table_type is BASE TABLE or VIEW
$obj->Table_type = 'BASE TABLE'; // Or query and map for views
}
$results[] = $obj;
}
$this->set_results_from_fetched_data($results);
}
private function execute_show_columns_statement(WP_Parser_Node $node): void {
// MySQL: SHOW [FULL] COLUMNS FROM tbl [FROM db] [LIKE 'pattern' | WHERE expr]
// PG: Query information_schema.columns
$table_ref_node = $node->get_first_child_node('tableRef');
if (!$table_ref_node) { /* error */ return; }
$table_name = $this->unquote_pgsql_identifier($this->translate($table_ref_node->get_first_child_node('identifier'))); // Simplified
$db_name_node = $node->get_first_child_node('inDb');
$schema_name = $this->current_schema_name;
if ($db_name_node) {
// Handle schema context if provided
$schema_name = $this->unquote_pgsql_identifier($this->translate($db_name_node->get_first_child_node('identifier')));
}
$sql = "SELECT
column_name AS \"Field\",
COALESCE(domain_name, udt_name) AS \"Type_pg\", -- PG specific type
data_type AS \"Type_udt\", -- udt_name is more precise
is_nullable AS \"Null\",
column_default AS \"Default\",
'' AS \"Key\", -- Needs to be derived from constraints (pg_constraint, pg_index)
'' AS \"Extra\" -- e.g. auto_increment (is_identity, or sequence default)
FROM information_schema.columns
WHERE table_schema = :schema_name AND table_name = :table_name";
$params = [':schema_name' => $schema_name, ':table_name' => $table_name];
$like_or_where_node = $node->get_first_child_node('likeOrWhere');
if ($like_or_where_node) {
// Similar to SHOW TABLES, handle LIKE or simple WHERE on column_name
$like_clause_node = $like_or_where_node->get_first_child_node('likeClause');
if ($like_clause_node) {
$pattern_node = $like_clause_node->get_first_child_node('textStringLiteral');
$pattern = $this->unquote_string_literal($this->translate($pattern_node));
$sql .= " AND column_name LIKE :pattern";
$params[':pattern'] = $pattern;
} // Complex WHERE not supported here for brevity
}
$sql .= " ORDER BY ordinal_position";
$stmt = $this->execute_pgsql_query($sql, $params);
$raw_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Post-process to match MySQL SHOW COLUMNS output format
$results = [];
foreach($raw_results as $row) {
$obj = new stdClass();
$obj->Field = $row['Field'];
// Reconstruct MySQL-like type string (e.g., varchar(255), int(11) unsigned)
// This requires mapping PG types back to MySQL types and format.
// $obj->Type = $this->map_pg_type_to_mysql_type_string($row['Type_pg'], $row['Type_udt'], ...);
// For now, use a simplified version.
$obj->Type = strtolower($row['Type_udt']);
if ($row['character_maximum_length'] ?? null) {
$obj->Type .= '(' . $row['character_maximum_length'] . ')';
} elseif (($row['numeric_precision'] ?? null) && ($row['numeric_scale'] ?? null)) {
$obj->Type .= '(' . $row['numeric_precision'] . ',' . $row['numeric_scale'] . ')';
} elseif ($row['numeric_precision'] ?? null) {
$obj->Type .= '(' . $row['numeric_precision'] . ')';
}
// 'unsigned' part is harder, PG has no direct unsigned.
$obj->Null = ($row['Null'] === 'YES' ? 'YES' : 'NO');
$obj->Key = $row['Key']; // Needs to be populated by querying pg_constraint/pg_index
$obj->Default = $row['Default'];
$obj->Extra = $row['Extra']; // Needs to be populated (e.g. for identity columns)
// Populate Key and Extra by querying pg_catalog, this is complex
// Example for Key: Check if column is part of PK or UNIQUE constraint.
// Example for Extra: Check if column is_identity or has default nextval('sequence').
$column_key_extra = $this->information_schema_builder->get_column_key_and_extra_for_show($schema_name, $table_name, $row['Field']);
$obj->Key = $column_key_extra['key'];
$obj->Extra = $column_key_extra['extra'];
$results[] = $obj;
}
$this->set_results_from_fetched_data($results);
}
private function execute_show_index_statement(WP_Parser_Node $node): void {
// MySQL: SHOW INDEX[ES] | KEYS FROM tbl [FROM db] [WHERE expr]
// PG: Query pg_indexes or construct from pg_class, pg_index, pg_attribute
$table_ref_node = $node->get_first_child_node('tableRef'); // Could also be tableName
if (!$table_ref_node) { /* error */ return; }
$table_name = $this->unquote_pgsql_identifier($this->translate($table_ref_node->get_first_child_node('identifier')));
$db_name_node = $node->get_first_child_node('inDb');
$schema_name = $this->current_schema_name;
if ($db_name_node) {
$schema_name = $this->unquote_pgsql_identifier($this->translate($db_name_node->get_first_child_node('identifier')));
}
// Query pg_indexes for basic info
$sql = "SELECT
tablename AS \"Table\",
NOT indexdef ~* 'UNIQUE' AS \"Non_unique\", -- Approximation: 0 if unique, 1 if not
indexname AS \"Key_name\",
0 AS \"Seq_in_index\", -- Needs to be derived by parsing indexdef or querying pg_attribute/pg_index
'' AS \"Column_name\", -- Needs to be derived
'' AS \"Collation\", -- 'A' for Asc, 'D' for Desc. Needs parsing indexdef
0 AS \"Cardinality\", -- pg_stats.n_distinct, or estimate. MySQL cardinality is often estimate.
NULL AS \"Sub_part\", -- MySQL specific prefix indexing
NULL AS \"Packed\", -- MySQL specific
'' AS \"Null\", -- 'YES' if col can be null, '' if not. Needs column info.
CASE
WHEN indexdef ~* 'USING btree' THEN 'BTREE'
WHEN indexdef ~* 'USING hash' THEN 'HASH'
WHEN indexdef ~* 'USING gist' THEN 'GIST'
WHEN indexdef ~* 'USING gin' THEN 'GIN'
ELSE 'BTREE' -- Default
END AS \"Index_type\",
'' AS \"Comment\",
'' AS \"Index_comment\",
'YES' AS \"Visible\" -- PG indexes are visible
FROM pg_catalog.pg_indexes
WHERE schemaname = :schema_name AND tablename = :table_name";
$params = [':schema_name' => $schema_name, ':table_name' => $table_name];
// WHERE clause from MySQL SHOW INDEX (e.g., WHERE Key_name = 'PRIMARY')
// This requires translating the WHERE clause against the above column names.
// $where_expr_node = $node->get_first_child_node('whereClause');
// if ($where_expr_node) { ... complex translation ... }
$stmt = $this->execute_pgsql_query($sql, $params);
$raw_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Post-process to fill Seq_in_index, Column_name, Collation, Null
// This is very complex as it requires parsing `indexdef` or joining with other pg_catalog tables.
// For this conversion, this part will be incomplete.
$results = [];
foreach ($raw_results as $raw_row) {
// Defer to information_schema_builder for detailed index info from emulated schema
$detailed_indexes = $this->information_schema_builder->get_detailed_index_info_for_show(
$schema_name, $table_name, $raw_row['Key_name']
);
if (!empty($detailed_indexes)) {
foreach($detailed_indexes as $idx_part) {
$results[] = (object) $idx_part; // Assuming builder returns objects in correct format
}
} else {
// Fallback with raw data if builder doesn't have it or fails
$obj = new stdClass();
$obj->Table = $raw_row['Table'];
$obj->Non_unique = (int)$raw_row['Non_unique']; // Cast to int
$obj->Key_name = $raw_row['Key_name'];
// ... (other fields are hard to populate accurately without more logic)
$obj->Seq_in_index = 1; // Placeholder
$obj->Column_name = 'N/A'; // Placeholder
// ...
$results[] = $obj;
}
}
$this->set_results_from_fetched_data($results);
}
private function execute_show_databases_statement(WP_Parser_Node $node): void {
// MySQL: SHOW DATABASES [LIKE 'pattern' | WHERE expr]
// PG: SELECT datname FROM pg_database WHERE datistemplate = false;
// Or, if emulating MySQL DBs as PG schemas: SELECT schema_name FROM information_schema.schemata
// Assuming we emulate MySQL DBs as PG schemas.
$sql = "SELECT schema_name AS \"Database\" FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_catalog', 'information_schema')
AND schema_name NOT LIKE 'pg_toast%' AND schema_name NOT LIKE 'pg_temp_%'";
$like_or_where_node = $node->get_first_child_node('likeOrWhere');
if ($like_or_where_node) {
// Handle LIKE or simple WHERE on schema_name
$like_clause_node = $like_or_where_node->get_first_child_node('likeClause');
if ($like_clause_node) {
$pattern_node = $like_clause_node->get_first_child_node('textStringLiteral');
$pattern = $this->unquote_string_literal($this->translate($pattern_node));
$sql .= " AND schema_name LIKE " . $this->pdo->quote($pattern);
}
}
$stmt = $this->execute_pgsql_query($sql);
$this->set_results_from_fetched_data($stmt->fetchAll(PDO::FETCH_OBJ));
}
private function execute_show_variables_statement(WP_Parser_Node $node): void {
// MySQL: SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]
// PG: SELECT name, setting, short_desc, context FROM pg_settings
// Or: SHOW variable_name; SHOW ALL;
$is_global = $node->get_first_child_token(WP_MySQL_Lexer::GLOBAL_SYMBOL) !== null;
// PG `pg_settings` context shows if it's session, user, database, superuser etc.
// `SHOW <var>` uses current (session) setting. `SHOW ALL` shows all.
// Emulating GLOBAL/SESSION distinction for all MySQL vars is hard.
$sql = "SELECT name, setting AS \"Value\" FROM pg_settings"; // 'Value' is the MySQL column name
$params = [];
$like_or_where_node = $node->get_first_child_node('likeOrWhere');
if ($like_or_where_node) {
$like_clause_node = $like_or_where_node->get_first_child_node('likeClause');
if ($like_clause_node) {
$pattern_node = $like_clause_node->get_first_child_node('textStringLiteral');
$pattern = $this->unquote_string_literal($this->translate($pattern_node));
$sql .= " WHERE name LIKE :pattern";
$params[':pattern'] = $pattern;
} // WHERE expr is more complex
}
$stmt = $this->execute_pgsql_query($sql, $params);
$raw_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// MySQL output is 'Variable_name', 'Value'.
$results = [];
foreach($raw_results as $row) {
$obj = new stdClass();
$obj->Variable_name = $row['name'];
$obj->Value = $row['Value'];
$results[] = $obj;
}
$this->set_results_from_fetched_data($results);
}
private function execute_show_table_status_statement(WP_Parser_Node $node): void {
// MySQL: SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern' | WHERE expr]
// PG: No direct equivalent. Info needs to be pieced together from:
// information_schema.tables (name, type)
// pg_class (reltuples for approx row count, relpages for size estimation)
// pg_total_relation_size() for size.
// This is complex to fully emulate.
// For now, provide names and basic types from information_schema.tables.
$db_name_node = $node->get_first_child_node('inDb');
$schema_name = $this->current_schema_name;
if ($db_name_node) {
$schema_name = $this->unquote_pgsql_identifier($this->translate($db_name_node->get_first_child_node('identifier')));
}
$sql = "SELECT
table_name AS \"Name\",
'PostgreSQL' AS \"Engine\", -- PG doesn't have engines like MySQL
table_type AS \"Row_format_temp\", -- Temp placeholder, MySQL Row_format is different
0 AS \"Rows\", -- Placeholder, get from pg_class.reltuples
0 AS \"Avg_row_length\", -- Placeholder
0 AS \"Data_length\", -- Placeholder, pg_table_size()
0 AS \"Max_data_length\", -- N/A
0 AS \"Index_length\", -- Placeholder, pg_indexes_size()
0 AS \"Data_free\", -- N/A
NULL AS \"Auto_increment\", -- Placeholder, from sequence
NULL AS \"Create_time\", -- PG doesn't store this directly in info schema. From file system or logs.
NULL AS \"Update_time\", -- PG doesn't store this.
NULL AS \"Check_time\", -- N/A
NULL AS \"Collation\", -- From pg_database/pg_collation for default, or per-column.
NULL AS \"Checksum\", -- N/A
'' AS \"Create_options\", -- N/A
'' AS \"Comment\" -- From COMMENT ON TABLE ...
FROM information_schema.tables
WHERE table_schema = :schema_name";
$params = [':schema_name' => $schema_name];
$like_or_where_node = $node->get_first_child_node('likeOrWhere');
if ($like_or_where_node) {
// Handle LIKE on table_name
}
$stmt = $this->execute_pgsql_query($sql, $params);
$results = $stmt->fetchAll(PDO::FETCH_OBJ); // Fetch as objects directly if format matches
// Further enrich with data from pg_class etc. (complex)
foreach ($results as $row) {
// Example: Get actual row count and size
// $stats_sql = "SELECT reltuples::bigint, pg_total_relation_size(c.oid) as total_size, pg_table_size(c.oid) as data_size, pg_indexes_size(c.oid) as index_size
// FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
// WHERE n.nspname = :schema_name AND c.relname = :table_name AND c.relkind IN ('r', 'p', 'm') "; // r=table, p=partitioned table, m=materialized view
// $stats_stmt = $this->execute_pgsql_query($stats_sql, [':schema_name' => $schema_name, ':table_name' => $row->Name]);
// $table_stats = $stats_stmt->fetch(PDO::FETCH_ASSOC);
// if ($table_stats) {
// $row->Rows = $table_stats['reltuples'];
// $row->Data_length = $table_stats['data_size'];
// $row->Index_length = $table_stats['index_size'];
// // Avg_row_length can be estimated if Data_length and Rows are known
// if ($row->Rows > 0) $row->Avg_row_length = (int)($row->Data_length / $row->Rows);
// }
// This is simplified for now.
}
$this->set_results_from_fetched_data($results);
}
private function execute_show_create_table_statement(WP_Parser_Node $node): void {
// MySQL: SHOW CREATE TABLE tbl_name
// PG: No direct single command. Needs to be reconstructed.
// pg_get_functiondef, pg_get_viewdef, pg_get_triggerdef, etc.
// pg_dump -s -t tablename can show DDL.
// information_schema_builder should have the original MySQL DDL or enough info to reconstruct it.
$table_ref_node = $node->get_first_child_node('tableRef');
if (!$table_ref_node) { /* error */ return; }
$table_name = $this->unquote_pgsql_identifier($this->translate($table_ref_node->get_first_child_node('identifier')));
$is_temporary = $this->information_schema_builder->temporary_table_exists_in_emulated_schema($table_name);
$mysql_create_sql = $this->information_schema_builder->get_mysql_create_table_statement($is_temporary, $table_name);
if (null === $mysql_create_sql) {
$this->set_results_from_fetched_data(array());
} else {
$this->set_results_from_fetched_data(
array(
(object) array(
'Table' => $table_name, // MySQL output format
'Create Table' => $mysql_create_sql,
),
)
);
}
}
private function execute_describe_statement( WP_Parser_Node $node ): void {
// DESCRIBE is alias for SHOW COLUMNS FROM in MySQL.
// Re-route to execute_show_columns_statement by creating a compatible node structure if needed,
// or directly call with parameters.
// Original code passed $subtree which is 'describeStatement' node.
// It needs a 'tableRef' child.
$this->execute_show_columns_statement($node); // Assuming $node structure is compatible
}
private function execute_use_statement( WP_Parser_Node $node ): void {
// MySQL: USE db_name
// PG: SET search_path TO schema_name; OR new connection.
// This driver assumes one connection, so map to SET search_path.
$db_name_node = $node->get_first_child_node( 'identifier' );
if (!$db_name_node) { /* error */ return; }
$schema_name = $this->unquote_pgsql_identifier( $this->translate( $db_name_node ) );
if ( strtolower( $schema_name ) === 'information_schema' ) {
// Special handling for information_schema. PG's is always accessible.
// We might want to make our emulated one the primary if db_name is 'information_schema'.
// This part of the logic needs to be clear about which information_schema is being targeted.
// For now, this might mean setting search_path to PG's information_schema,
// or adjusting internal flags if we have our own set of tables for MySQL's IS.
// $this->current_schema_name = 'information_schema'; // Could be problematic.
// The original driver has a $this->db_name that can be 'information_schema'.
// Let's map MySQL's $this->db_name to $this->current_schema_name for PG.
$this->current_schema_name = $schema_name; // For internal logic/schema builder target.
} elseif ( $schema_name === $this->main_db_name || $schema_name === $this->current_schema_name) { // main_db_name is emulated MySQL DB. current_schema_name is actual PG schema.
$this->current_schema_name = $schema_name;
$this->execute_pgsql_query( 'SET search_path TO ' . $this->quote_pgsql_identifier($schema_name) . ', public' );
} else {
// Check if this schema exists, then set it.
// SELECT 1 FROM information_schema.schemata WHERE schema_name = ?
// For now, similar to original, restrict to main schema or information_schema.
throw $this->new_not_supported_exception(
sprintf(
"USE command for schema '%s' not fully supported. Current schema: '%s'. Main emulated DB: '%s'.",
$schema_name,
$this->current_schema_name,
$this->main_db_name
)
);
}
$this->set_result_from_affected_rows(0); // USE doesn't affect rows
}
private function execute_administration_statement( WP_Parser_Node $node ): void {
// MySQL: ANALYZE, CHECK, OPTIMIZE, REPAIR TABLE
// PG: ANALYZE tbl; VACUUM [FULL] [ANALYZE] tbl; REINDEX TABLE/DATABASE/SCHEMA tbl;
// CHECK TABLE is not directly available. VACUUM VERBOSE or other tools.
// REPAIR TABLE not applicable. PG is ACID compliant, corruption is rare and needs pg_repack/pg_dump restore.
$first_token = $node->get_first_child_token();
$table_ref_list_node = $node->get_first_child_node( 'tableRefList' );
if (!$first_token || !$table_ref_list_node) { /* error */ return; }
$results = array();
foreach ( $table_ref_list_node->get_child_nodes( 'tableRef' ) as $table_ref ) {
$table_name = $this->unquote_pgsql_identifier( $this->translate( $table_ref ) );
$quoted_table_name = $this->quote_pgsql_identifier( $table_name );
$operation = strtolower( $first_token->value );
$msg_type = 'status';
$msg_text = 'OK';
try {
switch ( $first_token->id ) {
case WP_MySQL_Lexer::ANALYZE_SYMBOL:
$this->execute_pgsql_query( "ANALYZE " . $quoted_table_name );
break;
case WP_MySQL_Lexer::OPTIMIZE_SYMBOL:
// OPTIMIZE TABLE in MySQL (InnoDB) often rebuilds table + analyze.
// PG: VACUUM FULL tbl; ANALYZE tbl; REINDEX TABLE tbl;
// VACUUM FULL requires exclusive lock, can be slow.
// Simpler: VACUUM ANALYZE tbl;
$this->execute_pgsql_query( "VACUUM ANALYZE " . $quoted_table_name );
// For full rebuild effect like OPTIMIZE:
// $this->execute_pgsql_query( "VACUUM FULL " . $quoted_table_name );
// $this->execute_pgsql_query( "REINDEX TABLE " . $quoted_table_name );
break;
case WP_MySQL_Lexer::CHECK_SYMBOL:
// No direct CHECK TABLE. Can try to select count(*) to see if readable.
// For more thorough check, AMCHECK extension or similar.
// $this->execute_pgsql_query("SELECT COUNT(*) FROM " . $quoted_table_name); // Simple check
$msg_text = 'CHECK TABLE not directly supported, basic read check performed.';
break;
case WP_MySQL_Lexer::REPAIR_SYMBOL:
// REPAIR TABLE not applicable/supported in PostgreSQL.
$msg_text = 'REPAIR TABLE is not applicable to PostgreSQL.';
$msg_type = 'note';
break;
default:
throw $this->new_not_supported_exception(
sprintf('administration statement type: "%s"', $operation)
);
}
} catch ( PDOException $e ) {
$msg_type = 'Error';
$msg_text = $e->getMessage();
if (str_contains(strtolower($e->getMessage()), "does not exist")) { // PG errors for non-existent table
$msg_text = "Table '$table_name' doesn't exist";
}
}
$results[] = (object) array(
'Table' => $this->current_schema_name . '.' . $table_name, // Use actual schema
'Op' => $operation,
'Msg_type' => $msg_type,
'Msg_text' => $msg_text,
);
}
$this->set_results_from_fetched_data( $results );
}
private function translate( $node ): ?string {
if ( null === $node ) return null;
if ( $node instanceof WP_MySQL_Token ) {
return $this->translate_token( $node );
}
if ( ! $node instanceof WP_Parser_Node ) {
throw $this->new_driver_exception(sprintf('Expected WP_Parser_Node or WP_MySQL_Token, got: %s', gettype($node)));
}
// Due to parser stubs, $node->rule_name might be unreliable.
// This entire section is highly dependent on a working parser and well-defined grammar rules.
// The translations below are conceptual based on typical MySQL AST structures.
$rule_name = $node->rule_name;
switch ( $rule_name ) {
case 'querySpecification': // Core of SELECT
// MySQL: SELECT [options] select_list FROM table_references [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]
// PG: Similar structure.
// Handle MySQL HAVING without GROUP BY (PG requires GROUP BY or aggregate context for HAVING)
if ( $node->has_child_node( 'havingClause' ) && ! $node->has_child_node( 'groupByClause' ) ) {
$parts = [];
foreach ( $node->get_children() as $child ) {
if ( $child instanceof WP_Parser_Node && 'havingClause' === $child->rule_name ) {
// This is problematic. If there are no aggregate functions in SELECT list,
// and no GROUP BY, HAVING on non-aggregate columns is like WHERE.
// If HAVING contains aggregates, it needs a GROUP BY.
// A simple "GROUP BY 1" might not always be correct or efficient.
// MySQL allows non-aggregated columns in SELECT list if not in GROUP BY (if ONLY_FULL_GROUP_BY is off).
// PG requires them to be in GROUP BY or aggregates.
// $parts[] = 'GROUP BY ???'; // Hard to determine dummy group.
// For now, let the havingClause translate and hope it's valid or PG errors.
}
$part = $this->translate( $child );
if ( null !== $part ) $parts[] = $part;
}
return implode( ' ', $parts );
}
return $this->translate_sequence( $node->get_children() );
// Identifier translations
case 'qualifiedIdentifier': // db.table or table.column or db.table.column (less common)
case 'tableRefWithWildcard': // db.table.* or table.*
case 'fieldIdentifier': // db.table.col or table.col or col
case 'simpleIdentifier': // col (when unambiguous)
case 'pureIdentifier': // Raw identifier token (quoted or unquoted)
case 'dotIdentifier': // `a`.`b`
case 'identifierKeyword': // keyword used as identifier, e.g. `COUNT`
return $this->translate_identifier_node_to_pgsql($node);
case 'textStringLiteral': // 'string' or "string"
return $this->translate_string_literal_for_pgsql( $node );
case 'dataType':
case 'nchar': // This rule from SQLite driver seems specific. MySQL grammar might differ.
// Translate MySQL data type definition to PostgreSQL.
return $this->translate_datatype_definition_for_pgsql($node);
case 'fromClause':
if ( $node->has_child_token( WP_MySQL_Lexer::DUAL_SYMBOL ) ) {
return null; // FROM DUAL means no table, omit in PG for simple SELECT expression
}
return $this->translate_sequence( $node->get_children() );
case 'insertUpdateList': // For ON DUPLICATE KEY UPDATE part
// This is specific to MySQL INSERT ... ON DUPLICATE KEY UPDATE
// Translated to ON CONFLICT (...) DO UPDATE SET ... in execute_insert_statement
// Here, we translate the SET assignments part.
// VALUES(col) becomes EXCLUDED.col
$update_list_node = $node->get_first_child_node('updateList');
if ($update_list_node) {
return $this->translate_update_list_for_conflict($update_list_node);
}
return ""; // Should not be called directly for full clause translation.
case 'simpleExpr':
// Handle MySQL VALUES(col) function if it appears in other contexts (less common)
$token = $node->get_first_child_token();
if ( $token && WP_MySQL_Lexer::VALUES_SYMBOL === $token->id ) {
// This syntax (VALUES(col) outside ON DUPLICATE KEY) is specific and needs context.
// In PG, for INSERT ... ON CONFLICT, it's EXCLUDED.col.
// If used elsewhere, it's likely an error or needs different translation.
$col_ident_node = $node->get_first_child_node('simpleIdentifier'); // Or fieldIdentifier
if ($col_ident_node) {
return 'EXCLUDED.' . $this->translate($col_ident_node);
}
throw $this->new_not_supported_exception("VALUES() function outside of ON DUPLICATE KEY context.");
}
return $this->translate_sequence( $node->get_children() );
case 'predicateOperations': // LIKE, REGEXP, BETWEEN, IN, IS NULL, etc.
$first_op_token = $node->get_first_child_token();
if ($first_op_token) {
if (WP_MySQL_Lexer::LIKE_SYMBOL === $first_op_token->id) {
// MySQL LIKE vs PG LIKE: case sensitivity depends on collation in MySQL.
// PG LIKE is case-sensitive by default. ILIKE is case-insensitive.
// MySQL backslash escaping: '\%' is literal '%'. PG: E'\\%' or standard_conforming_strings=off.
// For simplicity, assume standard PG LIKE. May need ILIKE for case-insensitivity.
// Translate `expr LIKE pattern ESCAPE '\'` (MySQL default)
return $this->translate_sequence($node->get_children()) . " ESCAPE '\\'";
} elseif (WP_MySQL_Lexer::REGEXP_SYMBOL === $first_op_token->id) {
// MySQL REGEXP/RLIKE uses C library regex. PG uses POSIX EREs.
// expr REGEXP pattern -> expr ~ pattern (case-sensitive) or expr ~* pattern (case-insensitive)
// MySQL REGEXP is case-insensitive by default unless used with BINARY.
$expr_node = $node->get_child_nodes()[0]; // Before REGEXP token
$pattern_node = $node->get_child_nodes()[2]; // After REGEXP token
$operator = $node->has_child_token(WP_MySQL_Lexer::BINARY_SYMBOL) ? " ~ " : " ~* ";
return $this->translate($expr_node) . $operator . $this->translate($pattern_node);
}
}
return $this->translate_sequence( $node->get_children() );
case 'runtimeFunctionCall': // NOW(), CURRENT_TIMESTAMP(), DATE_ADD(), etc.
return $this->translate_runtime_function_call_for_pgsql( $node );
case 'functionCall': // User-defined or other standard functions
return $this->translate_function_call_for_pgsql( $node );
case 'systemVariable': // @@varname
return $this->translate_system_variable_for_pgsql($node);
case 'castType': // For CAST(... AS type)
// MySQL CAST(... AS BINARY) -> PG CAST(... AS BYTEA)
if ( $node->has_child_token( WP_MySQL_Lexer::BINARY_SYMBOL ) ) {
// If it's CAST(string AS BINARY(N)), it's about fixed length binary string.
// CAST(string AS BINARY) is like VARBINARY. Both map to BYTEA in PG.
// Length modifier for BINARY type needs careful handling.
$length_node = $node->get_first_child_node('fieldLength');
$length = "";
if ($length_node) {
// PG BYTEA doesn't take length. If fixed length is critical, needs custom type or padding.
// For now, ignore length for BYTEA cast.
}
return 'BYTEA';
}
// Other types: UNSIGNED, SIGNED for integers
if ($node->has_child_token(WP_MySQL_Lexer::UNSIGNED_SYMBOL)) return 'BIGINT'; // Or INTEGER, depends on source
if ($node->has_child_token(WP_MySQL_Lexer::SIGNED_SYMBOL)) return 'BIGINT'; // Or INTEGER
// For standard types like CHAR, VARCHAR, DECIMAL, translate the data type node.
$data_type_node = $node->get_first_child_node('dataType');
if ($data_type_node) {
return $this->translate_datatype_definition_for_pgsql($data_type_node);
}
return $this->translate_sequence( $node->get_children() );
// MySQL specific clauses that might not have direct PG equivalents or need removal
case 'defaultCollation': // COLLATE clause for table/db default (PG has per-column/db)
case 'indexHint': // USE INDEX, IGNORE INDEX, FORCE INDEX (PG has no direct hints like this)
case 'indexHintList':
return null; // Remove/ignore these hints
case 'duplicateAsQueryExpression': // For CREATE TABLE ... AS SELECT ... ON DUPLICATE KEY ...
// This structure is MySQL specific. PG CREATE TABLE ... AS has no ON DUPLICATE.
// This would be handled by post-CREATE INDEX and then INSERT ... ON CONFLICT.
// Here, just translate the AS SELECT part.
return 'AS ' . $this->translate( $node->get_first_child_node() ); // Assuming first child is query expr
default:
return $this->translate_sequence( $node->get_children() );
}
}
private function translate_token( WP_MySQL_Token $token ): ?string {
switch ( $token->id ) {
case WP_MySQL_Lexer::EOF:
return null;
case WP_MySQL_Lexer::AUTO_INCREMENT_SYMBOL:
// This is a column attribute. In PG, SERIAL/IDENTITY handles this.
// If it appears elsewhere, it's context-dependent.
// For CREATE TABLE column def, it implies SERIAL.
// For now, return null as it's handled by datatype translation.
return null;
case WP_MySQL_Lexer::BINARY_SYMBOL:
// Keyword `BINARY` before a string literal forces binary collation/comparison.
// e.g. SELECT BINARY 'a' = 'A'; (false)
// In PG, this is achieved by casting to BYTEA or specific collations.
// `COLLATE "C"` or `COLLATE "POSIX"` often gives byte-wise comparison for text.
// `_binary` keyword in MySQL can also mean `VARBINARY` type.
// Its translation is highly contextual. If it's a prefix to a string, it affects comparison.
// If it's a type, handled by datatype translation.
// For now, if it's a standalone token, remove it and handle contextually.
return null;
case WP_MySQL_Lexer::SQL_CALC_FOUND_ROWS_SYMBOL:
// Handled in execute_select_statement. Remove from direct translation.
return null;
default:
// Default behavior for most keywords and operators if their names are same/similar.
// e.g. SELECT, FROM, WHERE, AND, OR, TRUE, FALSE, NULL, AS, etc.
// Quoting style for identifiers is handled by translate_identifier_node_to_pgsql.
return $token->value;
}
}
private function translate_sequence( array $nodes, string $separator = ' ' ): ?string {
$parts = array();
foreach ( $nodes as $node ) {
if ( null === $node ) continue;
$translated = $this->translate( $node );
if ( null === $translated || $translated === '' ) continue; // Skip empty translations
$parts[] = $translated;
}
if ( empty( $parts ) ) return null;
return implode( $separator, $parts );
}
private function translate_identifier_node_to_pgsql(WP_Parser_Node $node): string {
// Takes various identifier-related AST nodes and quotes them for PostgreSQL.
// MySQL `ident` -> PG "ident"
// MySQL `db`.`table` -> PG "db"."table" (if db is schema) or "table" (if db is current_database and implicit)
$raw_identifier_parts = [];
// This needs to correctly extract parts of a qualified name, e.g., schema.table.column
// The logic from original driver was specific to its `qualifiedIdentifier` etc. rules.
// For a generic approach with stubs:
foreach ($node->get_descendant_tokens() as $token) {
// Filter out dots, backticks if they are separate tokens in AST.
// This depends on how the (stubbed) parser creates identifier nodes.
if ($token->id === WP_MySQL_Lexer::IDENTIFIER ||
$token->id === WP_MySQL_Lexer::BACK_TICK_QUOTED_ID ||
$token->id === WP_MySQL_Lexer::DOUBLE_QUOTED_TEXT // MySQL can use " for ids if not in ANSI mode
) {
$raw_identifier_parts[] = $this->unquote_mysql_identifier_token($token);
}
}
if (empty($raw_identifier_parts)) {
// If no specific tokens, try to get value of node (less reliable with stubs)
// This is a fallback
$str_val = "";
foreach($node->get_children() as $child) { // Get raw text from children
if ($child instanceof WP_MySQL_Token) $str_val .= $child->value;
elseif ($child instanceof WP_Parser_Node) $str_val .= $this->translate_identifier_node_to_pgsql($child); // Recurse carefully
}
$str_val = str_replace('`', '', $str_val); // Remove backticks
$parts = explode('.', $str_val);
$raw_identifier_parts = array_map('trim', $parts);
}
$translated_parts = [];
$num_parts = count($raw_identifier_parts);
if ($num_parts === 0) return "";
// Handle schema.table.column or schema.table or table.column or table
$is_information_schema_target = false;
$first_part_lower = strtolower($raw_identifier_parts[0]);
if ($num_parts > 1 && ($first_part_lower === $this->main_db_name || $first_part_lower === $this->current_schema_name)) {
// If first part is current emulated DB or current PG schema, it's redundant if search_path includes it.
// Keep it for explicit qualification.
$translated_parts[] = $this->quote_pgsql_identifier($raw_identifier_parts[0]); // This will be schema
array_shift($raw_identifier_parts); // Consume first part
} elseif ($num_parts > 1 && $first_part_lower === 'information_schema') {
// Targeting PostgreSQL's information_schema.
$translated_parts[] = $this->quote_pgsql_identifier('information_schema');
array_shift($raw_identifier_parts);
$is_information_schema_target = true;
}
// Remaining parts are table/column
foreach($raw_identifier_parts as $part) {
$translated_parts[] = $this->quote_pgsql_identifier($part);
}
$final_identifier = implode('.', $translated_parts);
// Check for reserved prefix if not information_schema
if (!$is_information_schema_target && str_contains($final_identifier, self::RESERVED_PREFIX)) {
// This check needs to be careful. If final_identifier is "schema"."_wp_pgsql_table", it's fine.
// If it's "_wp_pgsql_schema"."table", also fine.
// The original check was on unquoted parts.
foreach ($raw_identifier_parts as $part) { // Check original unquoted parts
if (str_starts_with($part, self::RESERVED_PREFIX)) {
throw $this->new_driver_exception(
sprintf("Invalid identifier component %s, prefix '%s' is reserved", $part, self::RESERVED_PREFIX)
);
}
}
}
return $final_identifier;
}
private function unquote_mysql_identifier_token(WP_MySQL_Token $token): string {
$value = $token->value;
if ($token->id === WP_MySQL_Lexer::BACK_TICK_QUOTED_ID) {
return str_replace('``', '`', substr($value, 1, -1));
}
if ($token->id === WP_MySQL_Lexer::DOUBLE_QUOTED_TEXT) { // If MySQL uses " for identifiers
return str_replace('""', '"', substr($value, 1, -1));
}
return $value; // Already unquoted (IDENTIFIER token)
}
private function translate_string_literal_for_pgsql(WP_Parser_Node $node): string {
$token = $node->get_first_child_token(); // textStringLiteral usually has one token child
if (!$token) return "''"; // Empty string if node is malformed
$value = $token->value; // e.g. 'foo \' bar' or "foo \" bar"
// 1. Determine quote type and unquote MySQL way
$mysql_quote_char = $value[0];
$inner_value = substr($value, 1, -1);
if ($mysql_quote_char === "'") {
// Unescape MySQL specific: \' -> ', '' -> ' (already handled if parser gives ' as value for '')
// Standard SQL escaping \' is not a thing, '' is '
$inner_value = str_replace("\\'", "'", $inner_value);
$inner_value = str_replace("\\\"", "\"", $inner_value); // other escapes if any
} elseif ($mysql_quote_char === '"') {
// Unescape MySQL specific: \" -> ", "" -> "
$inner_value = str_replace("\\\"", "\"", $inner_value);
$inner_value = str_replace("\\'", "'", $inner_value);
}
// Unescape standard C-style escapes like \n, \t, \r, \\, \0
// MySQL processes these. PostgreSQL also processes them in E'' strings.
// If standard_conforming_strings = on (default), backslash has no special meaning in PG normal strings.
// Use E'' for safety to ensure backslashes are interpreted.
$inner_value = stripcslashes($inner_value); // This handles \n, \t, etc. and \\ -> \
// Re-quote for PostgreSQL using standard single quotes, escaping internal single quotes.
// $pgsql_escaped_value = str_replace("'", "''", $inner_value);
// return "'" . $pgsql_escaped_value . "'";
// Using E'' syntax for PostgreSQL to handle C-style escapes if standard_conforming_strings is on.
// Need to escape backslashes and single quotes for E''
$pgsql_escaped_value = str_replace("\\", "\\\\", $inner_value); // Escape backslashes first
$pgsql_escaped_value = str_replace("'", "''", $pgsql_escaped_value); // Escape single quotes
// MySQL's "zero date" handling:
// The original code had translate_datetime_literal. PG errors on '0000-00-00'.
// If $inner_value matches a zero date, we might need to translate it to NULL or throw error.
if ($inner_value === '0000-00-00' || $inner_value === '0000-00-00 00:00:00') {
if ($this->is_sql_mode_active('NO_ZERO_DATE') || $this->is_sql_mode_active('NO_ZERO_IN_DATE')) {
// This mode is for MySQL behavior. PG is always like this.
// Depending on context (e.g. INSERT vs SELECT literal), this could be NULL or error.
// For a literal, it might be better to let PG handle it (which will likely be an error if used as date/ts).
// Or translate to NULL explicitly if that's desired emulation.
// return 'NULL'; // Option: translate invalid dates to NULL
}
}
// @TODO: Proper quoting
return "E'" . $pgsql_escaped_value . "'";
}
// Helper to unquote string literals for use in LIKE patterns, etc.
private function unquote_string_literal(string $quoted_literal): string {
if (str_starts_with(strtoupper($quoted_literal), "E'")) {
$val = substr($quoted_literal, 2, -1);
// Unescape for E'' literals: '' -> ', \\ -> \
$val = str_replace("''", "'", $val);
$val = str_replace("\\\\", "\\", $val); // Basic unescaping for E strings. More might be needed.
return $val;
} elseif (str_starts_with($quoted_literal, "'")) {
$val = substr($quoted_literal, 1, -1);
return str_replace("''", "'", $val); // Standard SQL unquoting
}
return $quoted_literal; // Not a recognized quoted string
}
private function translate_datatype_definition_for_pgsql(WP_Parser_Node $node): string {
// This is for CREATE TABLE / ALTER TABLE column type definitions.
// Needs to parse the MySQL type from AST and map to PG equivalent.
// Example MySQL: VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
// Example MySQL: INT(11) UNSIGNED ZEROFILL
// PG: VARCHAR(100) (charset/collation is db/column specific, not usually in type def like this)
// PG: INTEGER (unsigned/zerofill are not direct PG features, need CHECK constraints or display handling)
// Simplified translation based on self::DATA_TYPE_MAP and potential length.
// Full translation needs to handle (M), (M,D), UNSIGNED, ZEROFILL, CHARACTER SET, COLLATE.
$data_type_token = $node->get_first_child_token(); // First token usually is the base type
$mysql_type_name_token_id = null;
if ($data_type_token) {
$mysql_type_name_token_id = $data_type_token->id;
} else {
// Fallback: try to get name from node if it's a wrapper like 'nchar' rule.
$child_node = $node->get_first_child_node();
if ($child_node) {
$data_type_token = $child_node->get_first_child_token();
if ($data_type_token) $mysql_type_name_token_id = $data_type_token->id;
}
}
if (!$mysql_type_name_token_id) {
throw $this->new_driver_exception("Could not determine base data type from AST node.");
}
$pg_type = self::DATA_TYPE_MAP[$mysql_type_name_token_id] ?? 'TEXT'; // Default
// Handle SERIAL alias specifically for CREATE TABLE
if ($mysql_type_name_token_id === WP_MySQL_Lexer::SERIAL_SYMBOL) {
// SERIAL in MySQL is BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
return 'BIGSERIAL'; // PG BIGSERIAL is BIGINT NOT NULL with sequence.
}
$length_str = "";
// Extract (M) or (M,D) from fieldLength / precision nodes if they exist in $node's children
$field_length_node = $node->get_first_descendant_node('fieldLength');
if ($field_length_node) {
$length_str = $this->translate($field_length_node); // Gets e.g. "(255)"
} else {
$precision_node = $node->get_first_descendant_node('precision');
if ($precision_node) {
$length_str = $this->translate($precision_node); // Gets e.g. "(10,2)"
}
}
$full_pg_type = $pg_type;
if (!empty($length_str) && ($pg_type === 'CHAR' || $pg_type === 'VARCHAR' || $pg_type === 'BIT' || $pg_type === 'NUMERIC' || $pg_type === 'DECIMAL')) {
$full_pg_type .= $length_str;
}
// UNSIGNED attribute: PG has no direct unsigned types.
// Typically map to a larger signed type or add a CHECK constraint.
// e.g., INT UNSIGNED (max 4 billion) -> BIGINT (max 9 quintillion) + CHECK (col >= 0)
if ($node->has_child_token(WP_MySQL_Lexer::UNSIGNED_SYMBOL)) {
if ($pg_type === 'SMALLINT') $full_pg_type = 'INTEGER'; // TINYINT UNSIGNED, SMALLINT UNSIGNED
else if ($pg_type === 'INTEGER') $full_pg_type = 'BIGINT'; // MEDIUMINT UNSIGNED, INT UNSIGNED
// BIGINT UNSIGNED is problematic as PG BIGINT is max.
// Add " CHECK (colname >= 0)" later if needed.
}
// ZEROFILL attribute: Display formatting, not a storage characteristic in PG. Ignored.
// CHARACTER SET / COLLATE: In PG, these are usually db-level or explicit COLLATE clause per column.
// The information_schema_builder should store these MySQL attributes, but for PG type def, they are often omitted.
// Can add `COLLATE "C"` for binary-like sorting if MySQL type implies it.
return $full_pg_type;
}
private function translate_runtime_function_call_for_pgsql(WP_Parser_Node $node): string {
$first_child = $node->get_first_child(); // Can be token (CURRENT_TIMESTAMP) or node (functionName)
$func_name_token = null;
if ($first_child instanceof WP_MySQL_Token) {
$func_name_token = $first_child;
} elseif ($first_child instanceof WP_Parser_Node) { // If it's a functionName node
$func_name_token = $first_child->get_first_child_token();
}
if (!$func_name_token) throw $this->new_driver_exception("Unknown runtime function structure.");
$mysql_func_name_upper = strtoupper($func_name_token->value);
$args_node = $node->get_first_child_node('exprList'); // Or similar for arguments
$translated_args = [];
if ($args_node) {
foreach($args_node->get_child_nodes() as $arg_expr_node) {
$translated_args[] = $this->translate($arg_expr_node);
}
}
switch ($mysql_func_name_upper) {
case 'CURRENT_TIMESTAMP':
case 'NOW':
return 'CURRENT_TIMESTAMP'; // PG CURRENT_TIMESTAMP includes timezone info based on session.
case 'DATE_ADD': // DATE_ADD(date, INTERVAL expr unit)
case 'ADDDATE': // Synonym
// PG: date + INTERVAL 'value unit' OR date + (value * INTERVAL '1 unit')
// MySQL unit: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
// PG unit: microseconds, seconds, minutes, hours, days, weeks, months, years
if (count($translated_args) === 2) { // date, interval_expr_node
$date_str = $translated_args[0];
// Argument 2 needs special parsing for INTERVAL expr unit
$interval_node = $args_node->get_child_nodes()[1]; // The expr node for interval
// Need to find 'INTERVAL_SYMBOL', 'expr', 'unit' from $interval_node. This is complex.
// Assuming interval_node is "INTERVAL 1 DAY" -> translate to PG "'1 day'::interval"
// This simplified translation below assumes direct mapping of value and unit.
// A full solution would parse the MySQL INTERVAL expression.
// $interval_value_str = ...
// $interval_unit_str = ...
// For now, let's assume $translated_args[1] is a string like '1 DAY' from some pre-translation.
// This is a placeholder for proper INTERVAL translation.
// return $date_str . " + " . $this->translate_mysql_interval_to_pg($translated_args[1]);
throw $this->new_not_supported_exception("DATE_ADD/DATE_SUB interval translation requires deeper AST parsing.");
}
break;
case 'DATE_SUB': // DATE_SUB(date, INTERVAL expr unit)
case 'SUBDATE':
if (count($translated_args) === 2) {
// return $translated_args[0] . " - " . $this->translate_mysql_interval_to_pg($translated_args[1]);
throw $this->new_not_supported_exception("DATE_ADD/DATE_SUB interval translation requires deeper AST parsing.");
}
break;
case 'LEFT': // LEFT(str, len)
if (count($translated_args) === 2) return "LEFT(" . $translated_args[0] . ", " . $translated_args[1] . ")";
break;
// Add more runtime functions here
default: // Fallback to use same name and translated args
return $mysql_func_name_upper . "(" . implode(", ", $translated_args) . ")";
}
throw $this->new_not_supported_exception("Runtime function: " . $mysql_func_name_upper);
}
private function translate_function_call_for_pgsql(WP_Parser_Node $node): string {
$func_name_node = $node->get_first_child_node(); // Assuming functionName node
if (!$func_name_node) throw $this->new_driver_exception("Invalid function call AST.");
$mysql_func_name_upper = strtoupper($this->unquote_pgsql_identifier($this->translate($func_name_node)));
$args_list_node = $node->get_first_child_node('udfExprList') ?? $node->get_first_child_node('exprList');
$translated_args = [];
if ($args_list_node) {
foreach ($args_list_node->get_child_nodes() as $arg_node) {
$translated_args[] = $this->translate($arg_node);
}
}
switch ($mysql_func_name_upper) {
case 'DATABASE': return 'CURRENT_DATABASE()';
case 'SCHEMA': return 'CURRENT_SCHEMA()'; // MySQL SCHEMA() is synonym for DATABASE()
case 'USER': return 'CURRENT_USER';
case 'SESSION_USER': return 'SESSION_USER';
case 'SYSTEM_USER': return 'SESSION_USER'; // Closest PG equivalent
case 'VERSION': return 'VERSION()'; // PG version() gives full string including platform
case 'CONCAT': // CONCAT(str1, str2, ...)
// PG CONCAT function ignores NULLs. MySQL CONCAT returns NULL if any arg is NULL.
// PG || operator propagates NULLs.
// To match MySQL, if any arg could be NULL, need COALESCE or ensure args are not NULL.
// Or, if strict NULL propagation is MySQL behavior:
// return "(" . implode(" || ", $translated_args) . ")";
return "CONCAT(" . implode(", ", $translated_args) . ")"; // PG CONCAT is often fine.
case 'CONCAT_WS': // CONCAT_WS(separator, str1, str2, ...)
// PG CONCAT_WS is similar.
if (count($translated_args) >= 1) {
return "CONCAT_WS(" . implode(", ", $translated_args) . ")";
}
break;
case 'IFNULL': // IFNULL(expr1, expr2)
case 'NVL': // Oracle synonym sometimes used
if (count($translated_args) === 2) return "COALESCE(" . $translated_args[0] . ", " . $translated_args[1] . ")";
break;
case 'IF': // IF(condition, val_if_true, val_if_false)
if (count($translated_args) === 3) {
return "CASE WHEN " . $translated_args[0] . " THEN " . $translated_args[1] . " ELSE " . $translated_args[2] . " END";
}
break;
case 'RAND': // RAND() or RAND(N)
// PG RANDOM() is 0.0 <= x < 1.0. MySQL RAND() is same.
// MySQL RAND(N) seed behavior: PG setseed() affects subsequent RANDOM() in same session.
if (!empty($translated_args)) { // RAND(N)
// $this->execute_pgsql_query("SELECT setseed(" . $translated_args[0] . ")"); // Set seed
// return "RANDOM()";
// This session-wide seed setting might be undesirable.
// For reproducible sequence with seed, need custom plpgsql function or different approach.
throw $this->new_not_supported_exception("RAND(N) with seed. Use PG setseed() + RANDOM() carefully.");
}
return "RANDOM()";
case 'DATE_FORMAT': // DATE_FORMAT(date, format_str)
if (count($translated_args) === 2) {
$date_expr = $translated_args[0];
$mysql_format_literal = $translated_args[1]; // Assume it's a literal string '...'
$mysql_format_str = $this->unquote_string_literal($mysql_format_literal);
$pg_format_str = "";
// Convert MySQL format specifiers to PG TO_CHAR specifiers
// This is complex due to differences.
// Example: %Y-%m-%d -> YYYY-MM-DD
$pg_format_str = strtr($mysql_format_str, self::MYSQL_DATE_FORMAT_TO_PG_TO_CHAR_MAP);
// Handle %% -> % literal if any
$pg_format_str = str_replace('%%', '%', $pg_format_str);
return "TO_CHAR(" . $date_expr . "::timestamp, " . $this->pdo->quote($pg_format_str) . ")";
}
break;
case 'STR_TO_DATE': // STR_TO_DATE(str, format_str)
if (count($translated_args) === 2) {
$str_expr = $translated_args[0];
$mysql_format_literal = $translated_args[1];
$mysql_format_str = $this->unquote_string_literal($mysql_format_literal);
$pg_format_str = strtr($mysql_format_str, self::MYSQL_DATE_FORMAT_TO_PG_TO_CHAR_MAP); // Re-use map, some overlap
$pg_format_str = str_replace('%%', '%', $pg_format_str);
// Determine if it's date or timestamp based on format.
// If format includes H, M, S -> TO_TIMESTAMP, else TO_DATE.
if (preg_match('/(HH|MI|SS|MS|US)/i', $pg_format_str)) {
return "TO_TIMESTAMP(" . $str_expr . ", " . $this->pdo->quote($pg_format_str) . ")";
} else {
return "TO_DATE(" . $str_expr . ", " . $this->pdo->quote($pg_format_str) . ")";
}
}
break;
case 'FOUND_ROWS': // FOUND_ROWS()
// Requires prior SQL_CALC_FOUND_ROWS. Value stored in $this->last_sql_calc_found_rows.
$found_rows_val = $this->last_sql_calc_found_rows ?? 0;
// If used in SELECT list, an alias is needed. If used in expression, just the value.
// return (string)$found_rows_val; // As a literal value
// If used as "SELECT FOUND_ROWS()", then:
return "(SELECT " . (int)$found_rows_val . ") AS FOUND_ROWS"; // For SELECT list compatibility
case 'LAST_INSERT_ID': // LAST_INSERT_ID() or LAST_INSERT_ID(expr)
// PG: CURRVAL(pg_get_serial_sequence('table', 'id_col'))
// This is hard to translate generally without knowing table/sequence.
// PDO::lastInsertId() is preferred way to get this after an INSERT.
// If used in a general expression, it's problematic.
// MySQL LAST_INSERT_ID(expr) sets the value for next LAST_INSERT_ID(). PG has no such concept.
if (!empty($translated_args)) throw $this->new_not_supported_exception("LAST_INSERT_ID(expr)");
// Rely on $this->get_insert_id() to have been called after an insert.
// This needs a mechanism to pass the sequence name if not using RETURNING.
// For now, assume it might be used in subquery after insert (bad practice).
// A placeholder that will likely fail or return 0 if not immediately after an insert.
return "(SELECT 0)"; // Placeholder - this needs context of prior INSERT.
case 'GROUP_CONCAT':
// GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY ...] [SEPARATOR str])
// PG: STRING_AGG(expression, delimiter [ORDER BY ...])
// PG STRING_AGG doesn't have DISTINCT directly inside. Use subquery for DISTINCT.
$separator = ","; // Default MySQL separator
$order_by_clause = "";
$expr_to_agg = "";
// This requires parsing the arguments of GROUP_CONCAT from $args_list_node
// For now, assume simple form: GROUP_CONCAT(col SEPARATOR ';')
$expr_to_agg = $translated_args[0]; // First arg is expression
if (count($translated_args) > 1) {
// Look for SEPARATOR
// This is a very simplified parsing of GROUP_CONCAT args
// A full parser for function args is needed.
// for ($i=1; $i < count($translated_args); $i++) {
// if (is_separator_clause($translated_args[$i])) $separator = extract_separator($translated_args[$i]);
// if (is_orderby_clause($translated_args[$i])) $order_by_clause = translate_orderby($translated_args[$i]);
// }
}
// For now, hardcode separator or assume it's passed if $translated_args[1] is the separator.
if (isset($translated_args[1])) $separator = $this->unquote_string_literal($translated_args[1]);
// Handle DISTINCT, ORDER BY from MySQL AST if available.
// $distinct_str = $node->has_distinct_keyword ? "DISTINCT " : "";
// $expr_to_agg = $distinct_str . $expr_to_agg;
return "STRING_AGG(" . $expr_to_agg . ", " . $this->pdo->quote($separator) . $order_by_clause . ")";
// Add more function translations... MD5, SHA1, etc.
case 'MD5':
if (count($translated_args) === 1) return "MD5(" . $translated_args[0] . ")"; // PG MD5 needs pgcrypto
break;
case 'SHA1': // MySQL SHA1 returns hex string. PG SHA1 digest function returns bytea.
if (count($translated_args) === 1) {
// return "ENCODE(SHA1(" . $translated_args[0] . "::bytea), 'hex')"; // Requires pgcrypto
throw $this->new_not_supported_exception("SHA1 (requires pgcrypto and careful type handling for input).");
}
break;
default: // Fallback: use MySQL name, hope PG has compatible function or it's an error.
return $mysql_func_name_upper . "(" . implode(", ", $translated_args) . ")";
}
throw $this->new_not_supported_exception("Function: " . $mysql_func_name_upper);
}
private function translate_system_variable_for_pgsql(WP_Parser_Node $node): string {
// MySQL: @@varname, @@GLOBAL.varname, @@SESSION.varname
// PG: SHOW varname; current_setting('varname');
// Or specific functions like current_database(), current_schema(), session_user.
// Extract varname and scope (GLOBAL/SESSION) from $node
$var_name_node = $node->get_first_child_node('textOrIdentifier'); // Or similar path in AST
if (!$var_name_node) throw $this->new_driver_exception("Cannot parse system variable name.");
$mysql_var_name = strtolower($this->unquote_pgsql_identifier($this->translate($var_name_node)));
$scope_node = $node->get_first_child_node('varIdentType'); // e.g. GLOBAL., SESSION.
$is_global = $scope_node && strtoupper($this->translate($scope_node)) === 'GLOBAL';
// Map common MySQL vars to PG settings or functions
switch ($mysql_var_name) {
case 'version': return "VERSION()";
case 'database': // Note: MySQL @@database is function DATABASE()
case 'schema': // MySQL @@schema is function SCHEMA()
return "CURRENT_SCHEMA()"; // Or CURRENT_DATABASE() depending on MySQL meaning
case 'session.tx_isolation':
case 'tx_isolation':
return "current_setting('transaction_isolation')";
case 'session.time_zone':
case 'time_zone':
return "current_setting('TimeZone')";
case 'character_set_client':
case 'character_set_connection':
case 'character_set_results':
return "current_setting('client_encoding')";
case 'collation_connection':
case 'collation_database':
case 'collation_server':
// PG collation is more complex. Default from DB, or per-column.
// current_setting('lc_collate') might be relevant for server/db default.
return "(SELECT datcollate FROM pg_database WHERE datname = current_database())"; // DB default collation
case 'sql_mode':
// Return emulated $this->active_sql_modes
return $this->pdo->quote(implode(',', $this->active_sql_modes));
case 'auto_increment_increment':
case 'auto_increment_offset':
// PG sequences have increment and start value. Not a direct session var.
// This usually applies to table def. If read as var, return dummy or typical MySQL value.
return '1'; // Default for MySQL usually
case 'last_insert_id': // @@last_insert_id
// This is tricky. In PG, it's sequence-specific (CURRVAL) or from RETURNING.
// It's a function call in MySQL (LAST_INSERT_ID()), not typically @@var.
// For now, return 0 or throw error.
return '0'; /* Placeholder */
default:
if ($is_global) {
// Reading global MySQL vars often maps to pg_settings with context 'postmaster' or 'superuser'
// For simplicity, many global vars won't map directly.
throw $this->new_not_supported_exception("Global variable @@GLOBAL." . $mysql_var_name);
}
// For session vars, try current_setting if PG has an equivalent.
// return "current_setting(" . $this->pdo->quote($mysql_var_name) . ", true)"; // true = missing_ok
// The above is too generic. Fallback to quoted var name for "AS" clause in SELECT.
// Or, if it's a known MySQL var without PG equivalent, return its typical default or NULL.
return $this->pdo->quote('Unsupported MySQL Variable: ' . $mysql_var_name); // As a string value
}
}
/**
* Get an array of PostgreSQL CREATE TABLE and related statements (indexes, constraints)
* based on data previously stored by the Information_Schema_Builder.
*/
private function get_pgsql_create_table_statement(
bool $table_is_temporary,
string $table_name,
?string $new_table_name = null // For ALTER TABLE emulation (rename during recreate)
): array {
$actual_table_name = $new_table_name ?? $table_name;
$quoted_actual_table_name = $this->quote_pgsql_identifier($actual_table_name);
$table_info = $this->information_schema_builder->get_emulated_table_info($table_name, $table_is_temporary);
if (!$table_info) {
throw $this->new_driver_exception("Table '$table_name' not found in emulated schema for CREATE statement generation.");
}
$column_infos = $this->information_schema_builder->get_emulated_column_info($table_name, $table_is_temporary);
$constraint_infos = $this->information_schema_builder->get_emulated_constraint_info($table_name, $table_is_temporary); // Includes indexes
$column_definitions = [];
$table_constraints = []; // For multi-column PK, UNIQUE, CHECK, FK
$create_index_queries = [];
$alter_col_sequences = []; // For SERIAL/IDENTITY columns after table creation
foreach ($column_infos as $col) {
$col_def_parts = [];
$col_def_parts[] = $this->quote_pgsql_identifier($col['COLUMN_NAME']);
// Map MySQL type string (from COLUMN_TYPE) to PG type string
$pg_type_str = $this->map_mysql_coltype_to_pgsql_coltype($col['COLUMN_TYPE'], $col['EXTRA']);
$col_def_parts[] = $pg_type_str;
if ($col['IS_NULLABLE'] === 'NO') {
$col_def_parts[] = "NOT NULL";
}
if ($col['COLUMN_DEFAULT'] !== null) {
// Handle CURRENT_TIMESTAMP, expressions vs literals
if (strtoupper($col['COLUMN_DEFAULT']) === 'CURRENT_TIMESTAMP' &&
in_array(strtolower($col['DATA_TYPE']), ['timestamp', 'datetime'])) {
$col_def_parts[] = "DEFAULT CURRENT_TIMESTAMP";
} else {
// Default value might need casting if it's a string for a numeric type, etc.
// $default_val_translated = $this->translate_default_value($col['COLUMN_DEFAULT'], $pg_type_str);
// For now, direct quote. This might be wrong for non-string types.
$col_def_parts[] = "DEFAULT " . $this->pdo->quote($col['COLUMN_DEFAULT']);
}
}
// MySQL AUTO_INCREMENT -> PG SERIAL/BIGSERIAL or IDENTITY
if (str_contains(strtolower($col['EXTRA']), 'auto_increment')) {
// Type mapping should already handle SERIAL. If not, this is fallback.
// If pg_type_str is INTEGER -> SERIAL, BIGINT -> BIGSERIAL.
// This logic is better in map_mysql_coltype_to_pgsql_coltype.
// If using IDENTITY: col_name INT GENERATED BY DEFAULT AS IDENTITY
}
$column_definitions[] = implode(' ', $col_def_parts);
}
// Process constraints (PK, UNIQUE from $constraint_infos)
$primary_key_cols = null;
foreach ($constraint_infos as $idx_name => $idx_data) {
if ($idx_data['type'] === 'PRIMARY KEY') {
$pk_col_names = array_map([$this, 'quote_pgsql_identifier'], $idx_data['columns']);
$primary_key_cols = $pk_col_names; // Store for later use if single col PK on SERIAL
if (count($pk_col_names) > 0) {
// If single column PK is on a SERIAL type, it's implicit.
// Otherwise, add explicit PRIMARY KEY constraint.
$is_serial_pk = false;
if (count($pk_col_names) === 1) {
$pk_col_name_unquoted = $this->unquote_pgsql_identifier($pk_col_names[0]);
foreach($column_infos as $ci) {
if ($ci['COLUMN_NAME'] === $pk_col_name_unquoted &&
str_contains(strtolower($this->map_mysql_coltype_to_pgsql_coltype($ci['COLUMN_TYPE'], $ci['EXTRA'])), 'serial')) {
$is_serial_pk = true;
break;
}
}
}
if (!$is_serial_pk) {
$constraint_name = $this->quote_pgsql_identifier($idx_name === 'PRIMARY' ? $actual_table_name . "_pkey" : $idx_name);
$table_constraints[] = "CONSTRAINT " . $constraint_name . " PRIMARY KEY (" . implode(', ', $pk_col_names) . ")";
}
}
} elseif ($idx_data['type'] === 'UNIQUE') {
$unique_col_names = array_map([$this, 'quote_pgsql_identifier'], $idx_data['columns']);
$constraint_name = $this->quote_pgsql_identifier($idx_name);
$table_constraints[] = "CONSTRAINT " . $constraint_name . " UNIQUE (" . implode(', ', $unique_col_names) . ")";
} elseif ($idx_data['type'] === 'INDEX') { // Normal index
$idx_col_names_with_opts = [];
foreach($idx_data['columns_with_options'] as $col_opt) { // Assuming columns_with_options: ['name'=>name, 'order'=>ASC/DESC]
$idx_col_names_with_opts[] = $this->quote_pgsql_identifier($col_opt['name']) . ($col_opt['order'] ? ' '.($col_opt['order'] === 'A' ? 'ASC' : 'DESC') : '');
}
$idx_create_name = $this->quote_pgsql_identifier($idx_name);
$create_index_queries[] = "CREATE INDEX " . $idx_create_name . " ON " . $quoted_actual_table_name . " (" . implode(', ', $idx_col_names_with_opts) . ")";
}
// FOREIGN KEY, CHECK constraints would be handled here too.
}
$all_definitions = array_merge($column_definitions, $table_constraints);
$create_table_sql = sprintf(
"CREATE %sTABLE %s (\n %s\n)",
$table_is_temporary ? 'TEMPORARY ' : '',
$quoted_actual_table_name,
implode( ",\n ", $all_definitions )
);
// PG does not have table-level STRICT mode. Column types are always strict.
return array_merge([$create_table_sql], $create_index_queries, $alter_col_sequences);
}
private function map_mysql_coltype_to_pgsql_coltype(string $mysql_column_type, string $mysql_extra): string {
// $mysql_column_type is like 'int(11) unsigned', 'varchar(255)', 'text'
// $mysql_extra is like 'auto_increment'
// This needs robust parsing of $mysql_column_type.
$type_lower = strtolower($mysql_column_type);
$extra_lower = strtolower($mysql_extra);
if (str_contains($extra_lower, 'auto_increment')) {
if (str_starts_with($type_lower, 'tinyint')) return 'SMALLSERIAL'; // PG SMALLSERIAL is smallint
if (str_starts_with($type_lower, 'smallint')) return 'SMALLSERIAL';
if (str_starts_with($type_lower, 'mediumint')) return 'SERIAL'; // PG SERIAL is integer
if (str_starts_with($type_lower, 'int')) return 'SERIAL';
if (str_starts_with($type_lower, 'bigint')) return 'BIGSERIAL';
}
// Basic mapping, then add length/precision.
// E.g. 'int(11) unsigned' -> base 'int', unsigned flag, length '11'
// 'varchar(255)' -> base 'varchar', length '255'
// 'decimal(10,2)' -> base 'decimal', precision '10', scale '2'
if (preg_match('/^([a-z]+)\s*\((\d+)\s*,\s*(\d+)\)/', $type_lower, $matches)) { // decimal(P,S), float(P,S), double(P,S)
$base_mysql_type = $matches[1];
$precision = $matches[2];
$scale = $matches[3];
$pg_base = self::DATA_TYPE_STRING_MAP[$base_mysql_type] ?? 'NUMERIC'; // Fallback
return $pg_base . "(" . $precision . "," . $scale . ")";
} elseif (preg_match('/^([a-z]+)\s*\((\d+)\)/', $type_lower, $matches)) { // int(L), varchar(L), char(L), bit(L), binary(L)
$base_mysql_type = $matches[1];
$length = $matches[2];
$pg_base = self::DATA_TYPE_STRING_MAP[$base_mysql_type] ?? 'TEXT';
if (in_array($pg_base, ['CHAR', 'VARCHAR', 'BIT', 'VARBIT'])) { // Types that take length
return $pg_base . "(" . $length . ")";
}
// For INT(L), display width (L) is ignored in PG.
// If $base_mysql_type is 'int', 'mediumint', etc., map to PG INTEGER, BIGINT.
if (str_contains($type_lower, 'unsigned')) {
if ($pg_base === 'SMALLINT') return 'INTEGER';
if ($pg_base === 'INTEGER') return 'BIGINT';
// BIGINT UNSIGNED needs check constraint or error
}
return $pg_base; // e.g. INTEGER, SMALLINT, BIGINT
} else { // Types without length in definition usually, e.g. TEXT, DATE, BLOB
$base_mysql_type = preg_replace('/\s*unsigned|\s*zerofill/i', '', $type_lower); // remove unsigned/zerofill
$base_mysql_type = trim($base_mysql_type);
$pg_base = self::DATA_TYPE_STRING_MAP[$base_mysql_type] ?? 'TEXT';
if (str_contains($type_lower, 'unsigned')) {
if ($pg_base === 'SMALLINT') return 'INTEGER';
if ($pg_base === 'INTEGER') return 'BIGINT';
}
return $pg_base;
}
}
// ... Other private helper methods from original WP_SQLite_Driver, adapted for PostgreSQL ...
// e.g., unquote_pgsql_identifier, quote_pgsql_identifier
// recreate_table_from_information_schema needs to be PG specific.
// translate_show_like_or_where_condition (if used by SHOW commands)
private function unquote_pgsql_identifier( string $quoted_identifier ): string {
if (str_starts_with($quoted_identifier, '"') && str_ends_with($quoted_identifier, '"')) {
$unquoted = substr( $quoted_identifier, 1, -1 );
return str_replace( '""', '"', $unquoted ); // PG escapes " with ""
}
// If not quoted, it might be case-folded by PG unless it was created with quotes.
// For internal consistency, assume identifiers passed here are either correctly quoted
// or are intended to be case-folded if unquoted.
return $quoted_identifier;
}
private function quote_pgsql_identifier( string $unquoted_identifier ): string {
// Only quote if it's not already quoted, or contains special chars/uppercase/keywords
// For safety, always quote unless it's a very simple lowercase identifier.
// But to match MySQL's backtick behavior (always quote in translation):
return '"' . str_replace( '"', '""', $unquoted_identifier ) . '"';
}
private function flush(): void {
$this->last_mysql_query = '';
$this->last_pgsql_queries = array();
$this->last_result = null;
$this->last_return_value = null;
$this->is_readonly = false;
// Transaction level reset needs care if a transaction was uncommitted.
// $this->transaction_level = 0; // Resetting here might be premature if flush is called mid-transaction.
}
private function set_results_from_fetched_data( array $data ): void {
$this->last_result = $data;
$this->last_return_value = $this->last_result; // For SELECT, result is the data.
}
private function set_result_from_affected_rows( ?int $affected_rows = null ): void {
// For PostgreSQL, PDOStatement::rowCount() works for INSERT, UPDATE, DELETE.
// If $affected_rows is passed (e.g. from stmt->rowCount()), use it.
// Otherwise, this method implies no rows were fetched as primary result.
if ($affected_rows === null) {
// This might be called for DDL where rowCount is not meaningful or 0.
// Or if last query was not DML.
// Default to 0 if not provided and not a SELECT.
$affected_rows = 0;
}
$this->last_result = $affected_rows;
$this->last_return_value = $affected_rows;
}
private function new_driver_exception( string $message, $code = 0, ?Throwable $previous = null ): WP_PostgreSQL_Driver_Exception {
return new WP_PostgreSQL_Driver_Exception( $this, $message, $code, $previous );
}
private function new_invalid_input_exception(): WP_PostgreSQL_Driver_Exception {
return new WP_PostgreSQL_Driver_Exception( $this, 'MySQL query syntax error (or invalid AST for PG translation).' );
}
private function new_not_supported_exception( string $cause ): WP_PostgreSQL_Driver_Exception {
return new WP_PostgreSQL_Driver_Exception( $this, sprintf( 'MySQL query construct not supported for PostgreSQL: %s', $cause ) );
}
}
/**
* Custom Exception class for the PostgreSQL Driver.
*/
class WP_PostgreSQL_Driver_Exception extends Exception {
protected $driver;
public function __construct( WP_PostgreSQL_Driver $driver, string $message, $code = 0, ?Throwable $previous = null ) {
$this->driver = $driver;
// Append query info to message if available
$last_mysql_query = $driver->get_last_mysql_query();
if ( $last_mysql_query ) {
$message .= "\nLast MySQL query: " . $last_mysql_query;
}
$last_pgsql_queries = $driver->get_last_pgsql_queries();
if (!empty($last_pgsql_queries)) {
$message .= "\nExecuted PostgreSQL queries:";
foreach($last_pgsql_queries as $q) {
$message .= "\n SQL: " . $q['sql'] . ( !empty($q['params']) ? " | Params: " . json_encode($q['params']) : "");
}
}
parent::__construct( $message, is_int($code) ? $code : 0, $previous );
if (is_string($code)) { // SQLSTATE
$this->code = $code;
}
}
}
/**
* PostgreSQL information schema builder for MySQL emulation.
*
* This class builds and maintains structures that help emulate MySQL's
* INFORMATION_SCHEMA behavior, if PostgreSQL's own schema is insufficient
* or needs to be augmented for precise MySQL DDL interpretation.
* It consumes the AST of MySQL DDL queries and records schema information.
*/
class WP_PostgreSQL_Information_Schema_Builder {
// SQL definitions for tables that emulate MySQL "information_schema".
// These will be created within the target PostgreSQL schema.
// PostgreSQL types should be used. `STRICT` keyword is SQLite-specific.
const CREATE_EMULATED_INFORMATION_SCHEMA_QUERIES = array(
// TABLES (emulated)
"CREATE TABLE IF NOT EXISTS <prefix>tables (
TABLE_CATALOG TEXT NOT NULL DEFAULT current_database(), -- PG current_database()
TABLE_SCHEMA TEXT NOT NULL, -- Emulated MySQL DB name, maps to PG schema
TABLE_NAME TEXT NOT NULL,
TABLE_TYPE TEXT NOT NULL, -- 'BASE TABLE' or 'VIEW' (MySQL concept)
ENGINE TEXT, -- MySQL engine (e.g., InnoDB, MyISAM) - for info only
VERSION INTEGER DEFAULT 10,
ROW_FORMAT TEXT, -- MySQL specific
TABLE_ROWS BIGINT DEFAULT 0,
AVG_ROW_LENGTH BIGINT DEFAULT 0,
DATA_LENGTH BIGINT DEFAULT 0,
MAX_DATA_LENGTH BIGINT DEFAULT 0,
INDEX_LENGTH BIGINT DEFAULT 0,
DATA_FREE BIGINT DEFAULT 0,
AUTO_INCREMENT BIGINT,
CREATE_TIME TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UPDATE_TIME TIMESTAMP WITH TIME ZONE,
CHECK_TIME TIMESTAMP WITH TIME ZONE,
TABLE_COLLATION TEXT, -- MySQL table collation
CHECKSUM INTEGER,
CREATE_OPTIONS TEXT DEFAULT '',
TABLE_COMMENT TEXT DEFAULT '',
PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME) -- Ensure uniqueness
)",
// COLUMNS (emulated)
"CREATE TABLE IF NOT EXISTS <prefix>columns (
TABLE_CATALOG TEXT NOT NULL DEFAULT current_database(),
TABLE_SCHEMA TEXT NOT NULL,
TABLE_NAME TEXT NOT NULL,
COLUMN_NAME TEXT NOT NULL,
ORDINAL_POSITION INTEGER NOT NULL,
COLUMN_DEFAULT TEXT,
IS_NULLABLE TEXT NOT NULL, -- 'YES' or 'NO'
DATA_TYPE TEXT NOT NULL, -- Normalized MySQL data type (e.g., 'varchar', 'int')
CHARACTER_MAXIMUM_LENGTH INTEGER,
CHARACTER_OCTET_LENGTH INTEGER,
NUMERIC_PRECISION INTEGER,
NUMERIC_SCALE INTEGER,
DATETIME_PRECISION INTEGER,
CHARACTER_SET_NAME TEXT, -- MySQL charset
COLLATION_NAME TEXT, -- MySQL collation
COLUMN_TYPE TEXT NOT NULL, -- Full MySQL column type string (e.g., 'varchar(255)')
COLUMN_KEY TEXT DEFAULT '', -- 'PRI', 'UNI', 'MUL'
EXTRA TEXT DEFAULT '', -- 'auto_increment', etc.
PRIVILEGES TEXT,
COLUMN_COMMENT TEXT DEFAULT '',
GENERATION_EXPRESSION TEXT DEFAULT '',
SRS_ID INTEGER,
PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
)",
// STATISTICS (indexes - emulated)
"CREATE TABLE IF NOT EXISTS <prefix>statistics (
TABLE_CATALOG TEXT NOT NULL DEFAULT current_database(),
TABLE_SCHEMA TEXT NOT NULL,
TABLE_NAME TEXT NOT NULL,
NON_UNIQUE INTEGER NOT NULL, -- 0 for unique/primary, 1 otherwise
INDEX_SCHEMA TEXT NOT NULL, -- Same as TABLE_SCHEMA
INDEX_NAME TEXT NOT NULL,
SEQ_IN_INDEX INTEGER NOT NULL,
COLUMN_NAME TEXT, -- Can be NULL for expression indexes
\"COLLATION\" TEXT, -- 'A' (asc), 'D' (desc), or NULL
CARDINALITY BIGINT,
SUB_PART INTEGER, -- For MySQL prefix indexes
PACKED TEXT,
NULLABLE TEXT NOT NULL, -- 'YES' if column can contain NULL, '' otherwise
INDEX_TYPE TEXT NOT NULL, -- MySQL index type (BTREE, HASH, FULLTEXT, SPATIAL)
COMMENT TEXT DEFAULT '',
INDEX_COMMENT TEXT DEFAULT '',
IS_VISIBLE TEXT DEFAULT 'YES',
EXPRESSION TEXT, -- For functional/expression indexes
PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX)
)",
// VIEWS (emulated)
"CREATE TABLE IF NOT EXISTS <prefix>views (
TABLE_CATALOG TEXT NOT NULL,
TABLE_SCHEMA TEXT NOT NULL,
TABLE_NAME TEXT NOT NULL,
VIEW_DEFINITION TEXT NOT NULL,
CHECK_OPTION TEXT NOT NULL,
IS_UPDATABLE TEXT NOT NULL,
DEFINER TEXT NOT NULL,
SECURITY_TYPE TEXT NOT NULL,
CHARACTER_SET_CLIENT TEXT NOT NULL,
COLLATION_CONNECTION TEXT NOT NULL,
ALGORITHM TEXT NOT NULL,
PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME)
)",
// TABLE_CONSTRAINTS (emulated)
"CREATE TABLE IF NOT EXISTS <prefix>table_constraints (
CONSTRAINT_CATALOG TEXT NOT NULL,
CONSTRAINT_SCHEMA TEXT NOT NULL,
CONSTRAINT_NAME TEXT NOT NULL,
TABLE_SCHEMA TEXT NOT NULL,
TABLE_NAME TEXT NOT NULL,
CONSTRAINT_TYPE TEXT NOT NULL, -- 'PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY', 'CHECK'
PRIMARY KEY (CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
)",
// KEY_COLUMN_USAGE (emulated for foreign keys mostly)
"CREATE TABLE IF NOT EXISTS <prefix>key_column_usage (
CONSTRAINT_CATALOG TEXT NOT NULL,
CONSTRAINT_SCHEMA TEXT NOT NULL,
CONSTRAINT_NAME TEXT NOT NULL,
TABLE_CATALOG TEXT NOT NULL,
TABLE_SCHEMA TEXT NOT NULL,
TABLE_NAME TEXT NOT NULL,
COLUMN_NAME TEXT NOT NULL,
ORDINAL_POSITION INTEGER NOT NULL,
POSITION_IN_UNIQUE_CONSTRAINT INTEGER,
REFERENCED_TABLE_SCHEMA TEXT,
REFERENCED_TABLE_NAME TEXT,
REFERENCED_COLUMN_NAME TEXT,
PRIMARY KEY (CONSTRAINT_SCHEMA, CONSTRAINT_NAME, ORDINAL_POSITION)
)",
// REFERENTIAL_CONSTRAINTS (emulated for foreign keys)
"CREATE TABLE IF NOT EXISTS <prefix>referential_constraints (
CONSTRAINT_CATALOG TEXT NOT NULL,
CONSTRAINT_SCHEMA TEXT NOT NULL,
CONSTRAINT_NAME TEXT NOT NULL,
UNIQUE_CONSTRAINT_CATALOG TEXT NOT NULL,
UNIQUE_CONSTRAINT_SCHEMA TEXT NOT NULL,
UNIQUE_CONSTRAINT_NAME TEXT,
MATCH_OPTION TEXT NOT NULL,
UPDATE_RULE TEXT NOT NULL,
DELETE_RULE TEXT NOT NULL,
TABLE_NAME TEXT NOT NULL, -- Table that has the foreign key
REFERENCED_TABLE_NAME TEXT NOT NULL, -- Table that is referenced
PRIMARY KEY (CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
)",
);
const TOKEN_TO_TYPE_MAP = array(
WP_MySQL_Lexer::INT_SYMBOL => 'int',
WP_MySQL_Lexer::TINYINT_SYMBOL => 'tinyint',
WP_MySQL_Lexer::SMALLINT_SYMBOL => 'smallint',
WP_MySQL_Lexer::MEDIUMINT_SYMBOL => 'mediumint',
WP_MySQL_Lexer::BIGINT_SYMBOL => 'bigint',
WP_MySQL_Lexer::REAL_SYMBOL => 'double',
WP_MySQL_Lexer::DOUBLE_SYMBOL => 'double',
WP_MySQL_Lexer::FLOAT_SYMBOL => 'float',
WP_MySQL_Lexer::DECIMAL_SYMBOL => 'decimal',
WP_MySQL_Lexer::NUMERIC_SYMBOL => 'decimal',
WP_MySQL_Lexer::FIXED_SYMBOL => 'decimal',
WP_MySQL_Lexer::BIT_SYMBOL => 'bit',
WP_MySQL_Lexer::BOOL_SYMBOL => 'tinyint', // MySQL BOOL is TINYINT(1)
WP_MySQL_Lexer::BOOLEAN_SYMBOL => 'tinyint', // MySQL BOOLEAN is TINYINT(1)
WP_MySQL_Lexer::BINARY_SYMBOL => 'binary',
WP_MySQL_Lexer::VARBINARY_SYMBOL => 'varbinary',
WP_MySQL_Lexer::YEAR_SYMBOL => 'year',
WP_MySQL_Lexer::DATE_SYMBOL => 'date',
WP_MySQL_Lexer::TIME_SYMBOL => 'time',
WP_MySQL_Lexer::TIMESTAMP_SYMBOL => 'timestamp',
WP_MySQL_Lexer::DATETIME_SYMBOL => 'datetime',
WP_MySQL_Lexer::TINYBLOB_SYMBOL => 'tinyblob',
WP_MySQL_Lexer::BLOB_SYMBOL => 'blob',
WP_MySQL_Lexer::MEDIUMBLOB_SYMBOL => 'mediumblob',
WP_MySQL_Lexer::LONGBLOB_SYMBOL => 'longblob',
WP_MySQL_Lexer::TINYTEXT_SYMBOL => 'tinytext',
WP_MySQL_Lexer::TEXT_SYMBOL => 'text',
WP_MySQL_Lexer::MEDIUMTEXT_SYMBOL => 'mediumtext',
WP_MySQL_Lexer::LONGTEXT_SYMBOL => 'longtext',
WP_MySQL_Lexer::ENUM_SYMBOL => 'enum',
WP_MySQL_Lexer::SET_SYMBOL => 'set',
WP_MySQL_Lexer::SERIAL_SYMBOL => 'bigint', // SERIAL is alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
WP_MySQL_Lexer::GEOMETRY_SYMBOL => 'geometry',
WP_MySQL_Lexer::GEOMETRYCOLLECTION_SYMBOL => 'geomcollection',
WP_MySQL_Lexer::POINT_SYMBOL => 'point',
WP_MySQL_Lexer::MULTIPOINT_SYMBOL => 'multipoint',
WP_MySQL_Lexer::LINESTRING_SYMBOL => 'linestring',
WP_MySQL_Lexer::MULTILINESTRING_SYMBOL => 'multilinestring',
WP_MySQL_Lexer::POLYGON_SYMBOL => 'polygon',
WP_MySQL_Lexer::MULTIPOLYGON_SYMBOL => 'multipolygon',
WP_MySQL_Lexer::JSON_SYMBOL => 'json',
);
const CHARSET_DEFAULT_COLLATION_MAP = array(
'armscii8' => 'armscii8_general_ci', 'ascii' => 'ascii_general_ci',
'big5' => 'big5_chinese_ci', 'binary' => 'binary',
'cp1250' => 'cp1250_general_ci', 'cp1251' => 'cp1251_general_ci',
'cp1256' => 'cp1256_general_ci', 'cp1257' => 'cp1257_general_ci',
'cp850' => 'cp850_general_ci', 'cp852' => 'cp852_general_ci',
'cp866' => 'cp866_general_ci', 'cp932' => 'cp932_japanese_ci',
'dec8' => 'dec8_swedish_ci', 'eucjpms' => 'eucjpms_japanese_ci',
'euckr' => 'euckr_korean_ci', 'gb18030' => 'gb18030_chinese_ci',
'gb2312' => 'gb2312_chinese_ci', 'gbk' => 'gbk_chinese_ci',
'geostd8' => 'geostd8_general_ci', 'greek' => 'greek_general_ci',
'hebrew' => 'hebrew_general_ci', 'hp8' => 'hp8_english_ci',
'keybcs2' => 'keybcs2_general_ci', 'koi8r' => 'koi8r_general_ci',
'koi8u' => 'koi8u_general_ci', 'latin1' => 'latin1_swedish_ci',
'latin2' => 'latin2_general_ci', 'latin5' => 'latin5_turkish_ci',
'latin7' => 'latin7_general_ci', 'macce' => 'macce_general_ci',
'macroman' => 'macroman_general_ci', 'sjis' => 'sjis_japanese_ci',
'swe7' => 'swe7_swedish_ci', 'tis620' => 'tis620_thai_ci',
'ucs2' => 'ucs2_general_ci', 'ujis' => 'ujis_japanese_ci',
'utf16' => 'utf16_general_ci', 'utf16le' => 'utf16le_general_ci',
'utf32' => 'utf32_general_ci', 'utf8' => 'utf8_general_ci', // utf8mb3
'utf8mb4' => 'utf8mb4_0900_ai_ci', // MySQL 8.0 default for utf8mb4
);
const CHARSET_MAX_BYTES_MAP = array(
'big5' => 2, 'cp932' => 2, 'eucjpms' => 3, 'euckr' => 2,
'gb18030' => 4, 'gb2312' => 2, 'gbk' => 2, 'sjis' => 2,
'ucs2' => 2, 'ujis' => 3, 'utf16' => 4, 'utf16le' => 4,
'utf32' => 4, 'utf8' => 3, 'utf8mb4' => 4,
);
private $emulated_db_name; // The MySQL database name being emulated
private $actual_pg_schema_name; // The PostgreSQL schema where tables are actually created/managed
private $table_name_prefix_regular; // Schema-qualified prefix for regular emulated info tables
private $table_name_prefix_temporary; // Schema-qualified prefix for temporary emulated info tables
private $query_callback;
private $temporary_information_schema_exists = false;
private $pdo;
public function __construct( $pdo, string $emulated_database, string $actual_pg_schema, string $reserved_prefix, callable $query_callback ) {
$this->emulated_db_name = $emulated_database;
$this->actual_pg_schema_name = $actual_pg_schema;
$this->query_callback = $query_callback;
// For PostgreSQL, identifiers are folded to lowercase unless quoted. To be safe, quote schema and prefix.
$quoted_schema = $this->actual_pg_schema_name;
$base_prefix = $reserved_prefix . 'mysql_info_';
$base_temp_prefix = $reserved_prefix . 'mysql_info_temp_';
$this->table_name_prefix_regular = $quoted_schema . "." . $base_prefix;
$this->table_name_prefix_temporary = $quoted_schema . "." . $base_temp_prefix; // Still in regular schema, but for temp table info.
}
public function get_emulated_info_table_name(string $base_info_table_name, bool $is_for_temp_mysql_table): string {
// @TODO: Quote the returned value
if ($is_for_temp_mysql_table) {
// If truly using PG temporary tables for this info schema (which has session scope issues for a persistent builder)
// Then prefix would be different, e.g. "pg_temp". For now, assume they are regular tables distinguished by prefix or internal flag.
return $this->table_name_prefix_temporary . $base_info_table_name;
}
return $this->table_name_prefix_regular . $base_info_table_name;
}
public function ensure_information_schema_tables(): void {
$this->query("CREATE SCHEMA IF NOT EXISTS " . '"' . str_replace('"', '""', $this->actual_pg_schema_name) . '"');
foreach ( self::CREATE_EMULATED_INFORMATION_SCHEMA_QUERIES as $query_template ) {
$query = str_replace( '<prefix>', $this->table_name_prefix_regular, $query_template );
$this->query( $query );
// For temporary info tables, if needed separately and not distinguished by a flag.
// $temp_query = str_replace( '<prefix>', $this->table_name_prefix_temporary, $query_template );
// $this->query( $temp_query ); // Potentially.
}
// This doesn't create temporary tables for the schema itself, but tables *about* temporary MySQL tables.
// If the schema tables themselves needed to be temporary, the CREATE TABLE would be `CREATE TEMPORARY TABLE`.
}
public function table_exists_in_emulated_schema(string $table_name, bool $is_temporary): bool {
$info_tables_table = $this->get_emulated_info_table_name('tables', $is_temporary);
try {
$stmt = $this->query(
"SELECT 1 FROM $info_tables_table WHERE table_schema = ? AND table_name = ?",
[$this->emulated_db_name, $table_name]
);
return $stmt->fetchColumn() === '1';
} catch (PDOException $e) {
// If table $info_tables_table itself doesn't exist yet (e.g. first run before ensure_... completed or in rolled-back tx)
if (str_contains($e->getMessage(), 'does not exist')) { // Check PG error code for "undefined table" (42P01)
return false;
}
throw $e;
}
}
public function temporary_table_exists_in_emulated_schema(string $table_name): bool {
return $this->table_exists_in_emulated_schema($table_name, true);
}
public function record_create_table( WP_Parser_Node $node ): void {
$create_table_node = $node->get_first_child_node(); // Assuming 'createTable' rule
if (!$create_table_node) return;
$table_name_str = $this->get_value($create_table_node->get_first_descendant_node('tableName'));
$is_temporary = $create_table_node->has_child_token(WP_MySQL_Lexer::TEMPORARY_SYMBOL);
if ($is_temporary && !$this->temporary_information_schema_exists) {
// If using separate tables for temp info and they aren't created yet.
// For now, assume ensure_information_schema_tables creates both sets if needed,
// or they are distinguished by a flag in regular info tables.
// This part of logic might need refinement based on how temp table info is stored.
}
$tables_info_table = $this->get_emulated_info_table_name('tables', $is_temporary);
$columns_info_table = $this->get_emulated_info_table_name('columns', $is_temporary);
$statistics_info_table = $this->get_emulated_info_table_name('statistics', $is_temporary);
$table_constraints_info_table = $this->get_emulated_info_table_name('table_constraints', $is_temporary);
$key_column_usage_info_table = $this->get_emulated_info_table_name('key_column_usage', $is_temporary);
// 1. Record in 'tables'
$table_engine = $this->get_table_engine_from_ast($create_table_node);
$table_collation = $this->get_table_collation_from_ast($create_table_node);
// Other table options (COMMENT, ROW_FORMAT, CREATE_OPTIONS)
$table_comment = $this->get_table_option_value($create_table_node, 'COMMENT');
$table_data = array(
'table_schema' => $this->emulated_db_name,
'table_name' => $table_name_str,
'table_type' => 'BASE TABLE', // Assuming not CREATE VIEW here
'engine' => $table_engine,
'table_collation' => $table_collation,
'table_comment' => $table_comment,
// CREATE_TIME is default CURRENT_TIMESTAMP
);
$this->insert_values($tables_info_table, $table_data);
// 2. Record in 'columns'
$ordinal_position = 1;
$column_definition_nodes = $create_table_node->get_descendant_nodes('columnDefinition');
foreach ($column_definition_nodes as $col_def_node) {
$column_name_str = $this->get_value($col_def_node->get_first_child_node('fieldIdentifier'));
$mysql_column_data = $this->extract_mysql_column_data_from_ast($table_name_str, $column_name_str, $col_def_node, $ordinal_position, $table_collation);
$this->insert_values($columns_info_table, $mysql_column_data);
// Inline constraints (PRIMARY KEY, UNIQUE on column def)
$inline_constraint_key = $mysql_column_data['column_key']; // PRI or UNI if defined inline
if ($inline_constraint_key === 'PRI' || $inline_constraint_key === 'UNI') {
$index_name = ($inline_constraint_key === 'PRI') ? 'PRIMARY' : $column_name_str; // MySQL default unique index name often column name
$this->insert_values($statistics_info_table, [
'table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str,
'non_unique' => 0, 'index_schema' => $this->emulated_db_name,
'index_name' => $index_name, 'seq_in_index' => 1,
'column_name' => $column_name_str, 'collation' => 'A', // Default collation
'nullable' => ($mysql_column_data['is_nullable'] === 'YES' && $inline_constraint_key !== 'PRI') ? 'YES' : '',
'index_type' => 'BTREE', // Default
]);
$this->insert_values($table_constraints_info_table, [
'constraint_catalog' => $this->actual_pg_schema_name, 'constraint_schema' => $this->emulated_db_name,
'constraint_name' => $index_name, // Or generate one like tbl_col_key
'table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str,
'constraint_type' => ($inline_constraint_key === 'PRI') ? 'PRIMARY KEY' : 'UNIQUE'
]);
$this->insert_values($key_column_usage_info_table, [
'constraint_catalog' => $this->actual_pg_schema_name, 'constraint_schema' => $this->emulated_db_name,
'constraint_name' => $index_name, 'table_catalog' => $this->actual_pg_schema_name,
'table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str,
'column_name' => $column_name_str, 'ordinal_position' => 1,
]);
}
$ordinal_position++;
}
// 3. Record table-level constraints (PRIMARY KEY, UNIQUE, INDEX/KEY, FOREIGN KEY, CHECK)
$table_constraint_nodes = $create_table_node->get_descendant_nodes('tableConstraintDef');
foreach ($table_constraint_nodes as $constraint_node) {
$this->record_table_constraint_def_from_ast($table_name_str, $constraint_node, $is_temporary, $statistics_info_table, $table_constraints_info_table, $key_column_usage_info_table);
}
$this->sync_column_key_info_after_change($table_name_str, $is_temporary);
}
public function record_alter_table(WP_Parser_Node $node): ?array {
$alter_table_node = $node->get_first_child_node(); // 'alterTable' rule
if (!$alter_table_node) return null;
$table_name_str = $this->get_value($alter_table_node->get_first_descendant_node('tableRef'));
$is_temporary = $this->temporary_table_exists_in_emulated_schema($table_name_str); // Check if info for this temp table exists
$columns_info_table = $this->get_emulated_info_table_name('columns', $is_temporary);
$statistics_info_table = $this->get_emulated_info_table_name('statistics', $is_temporary);
$table_constraints_info_table = $this->get_emulated_info_table_name('table_constraints', $is_temporary);
$key_column_usage_info_table = $this->get_emulated_info_table_name('key_column_usage', $is_temporary);
$current_columns_stmt = $this->query("SELECT column_name, ordinal_position FROM $columns_info_table WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position", [$this->emulated_db_name, $table_name_str]);
$current_columns_map = [];
while($row = $current_columns_stmt->fetch(PDO::FETCH_ASSOC)) {
$current_columns_map[$row['column_name']] = (int)$row['ordinal_position'];
}
$column_map_for_recreate = array_combine(array_keys($current_columns_map), array_keys($current_columns_map));
$alter_actions = $alter_table_node->get_descendant_nodes('alterListItem');
foreach ($alter_actions as $action_node) {
$first_token = $action_node->get_first_child_token();
if (!$first_token) continue;
switch ($first_token->id) {
case WP_MySQL_Lexer::ADD_SYMBOL:
$this->handle_alter_add_from_ast($action_node, $table_name_str, $is_temporary, $columns_info_table, $statistics_info_table, $table_constraints_info_table, $key_column_usage_info_table, $current_columns_map);
break;
case WP_MySQL_Lexer::DROP_SYMBOL:
$this->handle_alter_drop_from_ast($action_node, $table_name_str, $is_temporary, $columns_info_table, $statistics_info_table, $table_constraints_info_table, $key_column_usage_info_table, $column_map_for_recreate);
break;
case WP_MySQL_Lexer::CHANGE_SYMBOL:
$this->handle_alter_change_from_ast($action_node, $table_name_str, $is_temporary, $columns_info_table, $statistics_info_table, $table_constraints_info_table, $key_column_usage_info_table, $column_map_for_recreate);
break;
case WP_MySQL_Lexer::MODIFY_SYMBOL:
$this->handle_alter_modify_from_ast($action_node, $table_name_str, $is_temporary, $columns_info_table, $statistics_info_table, $table_constraints_info_table, $key_column_usage_info_table);
break;
case WP_MySQL_Lexer::ALTER_SYMBOL: // ALTER [COLUMN] col SET DEFAULT / DROP DEFAULT
$this->handle_alter_column_options_from_ast($action_node, $table_name_str, $is_temporary, $columns_info_table);
break;
case WP_MySQL_Lexer::RENAME_SYMBOL: // RENAME [COLUMN | INDEX | TO]
$this->handle_alter_rename_from_ast($action_node, $table_name_str, $is_temporary, $column_map_for_recreate /* more tables needed */);
break;
// Other ALTER actions: RENAME TABLE, ALTER TABLE options (ENGINE, CHARSET, COLLATE)
}
}
$this->sync_column_key_info_after_change($table_name_str, $is_temporary);
$this->resequence_ordinal_positions($table_name_str, $is_temporary, $columns_info_table);
return $column_map_for_recreate;
}
public function record_drop_table(WP_Parser_Node $node): void {
$drop_table_node = $node->get_first_child_node(); // 'dropTable' rule
if (!$drop_table_node) return;
$has_temporary_keyword = $drop_table_node->has_child_token(WP_MySQL_Lexer::TEMPORARY_SYMBOL);
$table_refs_list_node = $drop_table_node->get_first_child_node('tableRefList');
foreach ($table_refs_list_node->get_child_nodes('tableRef') as $table_ref_node) {
$table_name_str = $this->get_value($table_ref_node);
// Determine if it was a temporary table info based on keyword or prior existence in temp info.
$is_temporary = $has_temporary_keyword || $this->temporary_table_exists_in_emulated_schema($table_name_str);
$where_criteria = ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str];
$this->delete_values($this->get_emulated_info_table_name('tables', $is_temporary), $where_criteria);
$this->delete_values($this->get_emulated_info_table_name('columns', $is_temporary), $where_criteria);
$this->delete_values($this->get_emulated_info_table_name('statistics', $is_temporary), $where_criteria);
$this->delete_values($this->get_emulated_info_table_name('table_constraints', $is_temporary), $where_criteria);
$this->delete_values($this->get_emulated_info_table_name('key_column_usage', $is_temporary),
array_merge($where_criteria, ['constraint_schema' => $this->emulated_db_name])); // KCU also has constraint_schema
$this->delete_values($this->get_emulated_info_table_name('referential_constraints', $is_temporary),
['constraint_schema' => $this->emulated_db_name, 'table_name' => $table_name_str]);
}
}
// Retrieve full emulated schema info (used by WP_PostgreSQL_Driver for CREATE TABLE and SHOW CREATE TABLE)
public function get_emulated_table_info(string $table_name, bool $is_temporary): ?array {
$tables_info_table = $this->get_emulated_info_table_name('tables', $is_temporary);
$stmt = $this->query("SELECT * FROM $tables_info_table WHERE table_schema = ? AND table_name = ?", [$this->emulated_db_name, $table_name]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as &$row) {
$row = array_change_key_case($row, CASE_UPPER);
}
return $result;
}
public function get_emulated_column_info(string $table_name, bool $is_temporary): array {
$cols_info_table = $this->get_emulated_info_table_name('columns', $is_temporary);
$stmt = $this->query("SELECT * FROM $cols_info_table WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position", [$this->emulated_db_name, $table_name]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as &$row) {
$row = array_change_key_case($row, CASE_UPPER);
}
return $result;
}
public function get_emulated_constraint_info(string $table_name, bool $is_temporary): array {
// This should gather info from 'statistics', 'table_constraints', 'key_column_usage'
// and format it in a way that's useful for reconstructing MySQL DDL or PG DDL.
// For simplicity, let's focus on 'statistics' for PK, UNIQUE, INDEX
$stats_info_table = $this->get_emulated_info_table_name('statistics', $is_temporary);
$stmt = $this->query("SELECT * FROM $stats_info_table WHERE table_schema = ? AND table_name = ? ORDER BY index_name, seq_in_index", [$this->emulated_db_name, $table_name]);
$raw_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);
$constraints = [];
foreach ($raw_stats as $stat) {
$idx_name = $stat['index_name'];
if (!isset($constraints[$idx_name])) {
$constraints[$idx_name] = [
'type' => $stat['index_type'], // BTREE, HASH, etc. (MySQL types)
'non_unique' => (bool)$stat['non_unique'],
'columns' => [],
'columns_with_options' => [], // For PG index creation: name, order, sub_part (if translatable)
'is_primary' => ($idx_name === 'PRIMARY'),
'is_unique' => ($stat['non_unique'] == 0),
'index_comment' => $stat['index_comment'],
];
if ($idx_name === 'PRIMARY') $constraints[$idx_name]['type'] = 'PRIMARY KEY';
elseif ($stat['non_unique'] == 0) $constraints[$idx_name]['type'] = 'UNIQUE';
else $constraints[$idx_name]['type'] = 'INDEX'; // Or KEY
}
$constraints[$idx_name]['columns'][] = $stat['column_name'];
$constraints[$idx_name]['columns_with_options'][] = [
'name' => $stat['column_name'],
'order' => $stat['COLLATION'], // 'A' or 'D'
'sub_part' => $stat['sub_part'] // MySQL specific prefix length
];
}
// Further processing for foreign keys from referential_constraints and key_column_usage
return $constraints;
}
public function get_mysql_create_table_statement(bool $is_temporary, string $table_name): ?string {
$table_info = $this->get_emulated_table_info($table_name, $is_temporary);
if (!$table_info) return null;
$column_infos = $this->get_emulated_column_info($table_name, $is_temporary);
$constraint_infos = $this->get_emulated_constraint_info($table_name, $is_temporary); // This needs to be MySQL-centric structure
$sql_parts = [];
$sql_parts[] = "CREATE " . ($is_temporary ? "TEMPORARY " : "") . "TABLE " . $this->quote_mysql_identifier($table_name) . " (";
$col_defs = [];
foreach($column_infos as $col) {
$cdp = [];
$cdp[] = $this->quote_mysql_identifier($col['COLUMN_NAME']);
$cdp[] = $col['COLUMN_TYPE']; // Full MySQL type string from emulated schema: e.g., int(11) unsigned
if ($col['IS_NULLABLE'] === 'NO') $cdp[] = "NOT NULL";
if ($col['COLUMN_DEFAULT'] !== null) {
if (strtoupper($col['COLUMN_DEFAULT']) === 'CURRENT_TIMESTAMP' &&
in_array(strtolower($col['DATA_TYPE']), ['timestamp', 'datetime'])) {
$cdp[] = "DEFAULT CURRENT_TIMESTAMP";
} elseif (strtolower($col['DATA_TYPE']) === 'bit' && preg_match("/b'[01]+'/", $col['COLUMN_DEFAULT'])) {
$cdp[] = "DEFAULT " . $col['COLUMN_DEFAULT']; // bit literals like b'0101'
}
else {
// Default value needs to be quoted if it's a string type, not for numbers/boolean
// $this->pdo->quote() is for SQL literals, not DDL defaults.
// This needs a helper for DDL default quoting based on $col['DATA_TYPE']
$cdp[] = "DEFAULT " . $this->format_mysql_default_value($col['COLUMN_DEFAULT'], $col['DATA_TYPE']);
}
}
if (!empty($col['EXTRA'])) {
// EXTRA can be "AUTO_INCREMENT", "ON UPDATE CURRENT_TIMESTAMP", "VIRTUAL GENERATED", etc.
$extras = explode(' ', strtoupper($col['EXTRA']));
foreach($extras as $extra_part) {
if ($extra_part === 'DEFAULT_GENERATED') continue; // This is informational, not DDL keyword usually
if ($extra_part === 'VIRTUAL' || $extra_part === 'STORED') {
$cdp[] = "GENERATED ALWAYS AS (" . $col['GENERATION_EXPRESSION'] . ") " . $extra_part;
} elseif ($extra_part === 'ON') { // For "ON UPDATE"
$cdp[] = "ON UPDATE CURRENT_TIMESTAMP"; // Assuming this is the only ON UPDATE case for now
}
else {
$cdp[] = $extra_part; // e.g. AUTO_INCREMENT
}
}
}
if (!empty($col['COLUMN_COMMENT'])) $cdp[] = "COMMENT " . $this->pdo->quote($col['COLUMN_COMMENT']); // Use PDO quote for comments
$col_defs[] = " " . implode(" ", $cdp);
}
$constraint_defs = [];
foreach($constraint_infos as $idx_name => $idx_data) {
$cols_str = implode(', ', array_map(function($col_opt) {
$s = $this->quote_mysql_identifier($col_opt['name']);
if ($col_opt['sub_part']) $s .= "(" . $col_opt['sub_part'] . ")";
if ($col_opt['order'] === 'D') $s .= " DESC";
// ASC is default
return $s;
}, $idx_data['columns_with_options']));
$idx_def = " ";
if ($idx_data['is_primary']) {
$idx_def .= "PRIMARY KEY (" . $cols_str . ")";
} elseif ($idx_data['is_unique']) {
$idx_def .= "UNIQUE KEY " . $this->quote_mysql_identifier($idx_name) . " (" . $cols_str . ")";
} else { // Regular index
$idx_def .= "KEY " . $this->quote_mysql_identifier($idx_name) . " (" . $cols_str . ")";
}
// MySQL Index type (USING BTREE/HASH) and comments
if ($idx_data['type'] !== 'PRIMARY KEY' && $idx_data['type'] !== 'UNIQUE' && $idx_data['type'] !== 'BTREE' && !empty($idx_data['type'])) { // Don't add USING BTREE if it's default
if (in_array($idx_data['type'], ['BTREE', 'HASH'])) { // MySQL supports these for general indexes
$idx_def .= " USING " . $idx_data['type'];
}
// FULLTEXT/SPATIAL are keywords before INDEX/KEY
}
if (!empty($idx_data['index_comment'])) {
$idx_def .= " COMMENT " . $this->pdo->quote($idx_data['index_comment']);
}
$constraint_defs[] = $idx_def;
}
$all_defs_for_create = array_merge($col_defs, $constraint_defs);
$sql_parts[] = implode(",\n", $all_defs_for_create);
$sql_parts[] = ")";
// Table options: ENGINE, CHARSET, COLLATE, COMMENT
if (!empty($table_info['ENGINE'])) $sql_parts[] = "ENGINE=" . $table_info['ENGINE'];
if (!empty($table_info['TABLE_COLLATION'])) {
$charset = explode('_', $table_info['TABLE_COLLATION'])[0];
if ($charset === 'utf8mb3') $charset = 'utf8'; // Normalize for older MySQL versions
$sql_parts[] = "DEFAULT CHARACTER SET=" . $charset; // Or extract from table_info if stored separately
$sql_parts[] = "COLLATE=" . $table_info['TABLE_COLLATION'];
}
if (!empty($table_info['TABLE_COMMENT'])) $sql_parts[] = "COMMENT=" . $this->pdo->quote($table_info['TABLE_COMMENT']);
if (!empty($table_info['AUTO_INCREMENT'])) $sql_parts[] = "AUTO_INCREMENT=" . $table_info['AUTO_INCREMENT'];
return implode("\n ", $sql_parts);
}
private function format_mysql_default_value(string $value, string $mysql_data_type): string {
$type_cat = strtolower($mysql_data_type);
if (in_array($type_cat, ['int', 'tinyint', 'smallint', 'mediumint', 'bigint', 'float', 'double', 'decimal', 'year'])) {
return $value; // Numeric types don't quote default
}
if (in_array($type_cat, ['bool', 'boolean'])) {
return (strtolower($value) === 'true' || $value === '1') ? 'TRUE' : 'FALSE';
}
// For string, date, time, enum, set, json, text, blob, default values are quoted.
return $this->pdo->quote($value);
}
private function quote_mysql_identifier(string $identifier): string {
return '`' . str_replace('`', '``', $identifier) . '`';
}
private function get_value( WP_Parser_Node $node ): string {
$full_value = '';
foreach ( $node->get_children() as $child ) {
if ( $child instanceof WP_Parser_Node ) {
$value = $this->get_value( $child );
} elseif ( WP_MySQL_Lexer::BACK_TICK_QUOTED_ID === $child->id ) {
$value = substr( $child->value, 1, -1 );
$value = str_replace( '``', '`', $value );
} elseif ( WP_MySQL_Lexer::SINGLE_QUOTED_TEXT === $child->id ) {
$value = $child->value;
$value = substr( $value, 1, -1 );
$value = str_replace( '\"', '"', $value );
$value = str_replace( '""', '"', $value );
} elseif ( WP_MySQL_Lexer::DOUBLE_QUOTED_TEXT === $child->id ) {
$value = $child->value;
$value = substr( $value, 1, -1 );
$value = str_replace( '\"', '"', $value );
$value = str_replace( '""', '"', $value );
} else {
$value = $child->value;
}
$full_value .= $value;
}
return $full_value;
}
private function get_table_engine_from_ast(WP_Parser_Node $create_table_node): string {
$engine_option_node = $this->find_table_option_node($create_table_node, 'ENGINE');
if ($engine_option_node) {
$engine_ref_node = $engine_option_node->get_first_child_node('engineRef'); // Or identifier
if ($engine_ref_node) return strtoupper($this->get_value($engine_ref_node));
}
return 'InnoDB'; // MySQL default
}
private function get_table_collation_from_ast(WP_Parser_Node $create_table_node): ?string {
// Look for DEFAULT COLLATE or COLLATE option, or derive from CHARSET
$collate_option_node = $this->find_table_option_node($create_table_node, 'COLLATE');
if ($collate_option_node) {
$collation_name_node = $collate_option_node->get_first_child_node('collationName');
if ($collation_name_node) return strtolower($this->get_value($collation_name_node));
}
$charset_option_node = $this->find_table_option_node($create_table_node, 'CHARACTER SET') ??
$this->find_table_option_node($create_table_node, 'CHARSET');
if ($charset_option_node) {
$charset_name_node = $charset_option_node->get_first_child_node('charsetName');
if ($charset_name_node) {
$charset = strtolower($this->get_value($charset_name_node));
if ($charset === 'utf8mb3') $charset = 'utf8';
return self::CHARSET_DEFAULT_COLLATION_MAP[$charset] ?? $charset . '_general_ci';
}
}
// Global default if not specified (e.g. from DB settings, or hardcoded fallback)
return 'utf8mb4_0900_ai_ci'; // Sensible default for modern MySQL
}
private function get_table_option_value(WP_Parser_Node $create_table_node, string $option_name): ?string {
$option_node = $this->find_table_option_node($create_table_node, $option_name);
if ($option_node) {
// Assuming option value is a textLiteral or identifier after the option keyword and '='
$children = $option_node->get_children();
$value_node_found = false;
foreach($children as $child) {
if ($value_node_found && ($child instanceof WP_Parser_Node)) { // value node could be textLiteral, identifier, etc.
if ($child->rule_name === 'textStringLiteral') return $this->get_value($child);
if ($child->rule_name === 'identifier') return $this->get_value($child);
// Handle other literal types if necessary
break;
}
if ($child instanceof WP_MySQL_Token && strtoupper($child->value) === $option_name) {
$value_node_found = true; // Next node should be '=' or value
}
if ($value_node_found && $child instanceof WP_MySQL_Token && $child->value === '=') {
// Value is next node
continue;
}
}
}
return null;
}
private function find_table_option_node(WP_Parser_Node $create_table_node, string $option_name_upper): ?WP_Parser_Node {
$table_option_list_node = $create_table_node->get_first_descendant_node('tableOptionList');
if ($table_option_list_node) {
foreach ($table_option_list_node->get_child_nodes('tableOptionItem') as $option_item_node) {
// A tableOptionItem might start with ENGINE, CHARSET, COLLATE, COMMENT, etc.
// Check the first token(s) of option_item_node.
$tokens = $option_item_node->get_child_tokens(); // Get first few tokens
if (empty($tokens)) continue;
// Handle multi-word options like CHARACTER SET
$option_keyword_from_ast = "";
if (strtoupper($tokens[0]->value) === $option_name_upper) {
return $option_item_node;
}
if (count($tokens) > 1 && strtoupper($tokens[0]->value . " " . $tokens[1]->value) === $option_name_upper) {
return $option_item_node;
}
}
}
return null;
}
private function extract_mysql_column_data_from_ast(string $table_name_str, string $column_name_str, WP_Parser_Node $col_def_node, int $ordinal_position, ?string $table_collation): array {
$data_type_node = $col_def_node->get_first_descendant_node('dataType');
list($mysql_norm_type, $mysql_full_type) = $this->parse_mysql_datatype_from_ast($data_type_node);
$is_nullable = 'YES';
$column_key = '';
$extra = '';
$default_value = null;
$comment = '';
$gen_expr = '';
// SERIAL implies NOT NULL, AUTO_INCREMENT, UNIQUE (becomes PRI if only unique key)
if ($data_type_node->get_first_descendant_token(WP_MySQL_Lexer::SERIAL_SYMBOL)) {
$is_nullable = 'NO';
$extra = 'AUTO_INCREMENT';
$column_key = 'UNI'; // Could become PRI later
}
$attributes = $col_def_node->get_descendant_nodes('columnAttribute');
foreach ($attributes as $attr_node) {
$first_attr_token = $attr_node->get_first_child_token();
if (!$first_attr_token) continue;
switch ($first_attr_token->id) {
case WP_MySQL_Lexer::NOT_SYMBOL: // NOT NULL
if ($attr_node->get_first_child_node('nullLiteral')) $is_nullable = 'NO';
break;
case WP_MySQL_Lexer::NULL_SYMBOL: // NULL (explicitly, though default is nullable if NOT NULL absent)
// This might override NOT NULL if "NULL" appears after "NOT NULL", though unusual.
// Usually it's "NOT NULL" or just "NULL" (or implicit nullable).
// $is_nullable = 'YES';
break;
case WP_MySQL_Lexer::DEFAULT_SYMBOL:
$default_literal_node = $attr_node->get_first_child_node(); // signedLiteral, textLiteral, nullLiteral, NOW(), etc.
if ($default_literal_node && $default_literal_node->rule_name === 'nullLiteral') {
$default_value = null; // Explicit DEFAULT NULL
} elseif ($attr_node->has_child_token(WP_MySQL_Lexer::NOW_SYMBOL) || $attr_node->has_child_token(WP_MySQL_Lexer::CURRENT_TIMESTAMP_SYMBOL)) {
$default_value = 'CURRENT_TIMESTAMP';
} else {
// Handle expressions in default (e.g. (UUID()))
$expr_node = $attr_node->get_first_child_node('exprWithParentheses');
if ($expr_node) {
$default_value = $this->get_value_from_expression_node($expr_node); // Needs a robust expression serializer
if (!str_contains($extra, 'DEFAULT_GENERATED')) $extra = trim($extra . ' DEFAULT_GENERATED');
} else {
// Simple literal default
$val_node = $attr_node->get_first_child_node(); // Could be signedLiteral or textStringLiteral
if ($val_node) $default_value = $this->get_value($val_node);
}
}
break;
case WP_MySQL_Lexer::AUTO_INCREMENT_SYMBOL:
$extra = trim($extra . ' AUTO_INCREMENT');
$is_nullable = 'NO'; // Auto_increment columns are implicitly NOT NULL
break;
case WP_MySQL_Lexer::UNIQUE_SYMBOL: // UNIQUE [KEY]
$column_key = 'UNI';
break;
case WP_MySQL_Lexer::KEY_SYMBOL: // PRIMARY KEY (inline)
case WP_MySQL_Lexer::PRIMARY_SYMBOL: // PRIMARY KEY (if PRIMARY token first)
if ($attr_node->has_child_token(WP_MySQL_Lexer::KEY_SYMBOL) || $first_attr_token->id === WP_MySQL_Lexer::PRIMARY_SYMBOL) {
$column_key = 'PRI';
$is_nullable = 'NO';
}
break;
case WP_MySQL_Lexer::COMMENT_SYMBOL:
$comment_node = $attr_node->get_first_child_node('textStringLiteral');
if ($comment_node) $comment = $this->get_value($comment_node);
break;
case WP_MySQL_Lexer::ON_SYMBOL: // ON UPDATE CURRENT_TIMESTAMP
if ($attr_node->has_child_token(WP_MySQL_Lexer::UPDATE_SYMBOL) && $attr_node->has_child_token(WP_MySQL_Lexer::CURRENT_TIMESTAMP_SYMBOL)) {
$extra = trim($extra . ' ON UPDATE CURRENT_TIMESTAMP');
}
break;
// Other attributes: COLLATE, COLUMN_FORMAT, STORAGE, REFERENCES (inline FK)
}
}
// Generated columns
$generated_always_node = $col_def_node->get_first_child_node('generatedAlways');
if ($generated_always_node) {
$gen_expr_node = $generated_always_node->get_first_child_node('exprWithParentheses');
if ($gen_expr_node) $gen_expr = $this->get_value_from_expression_node($gen_expr_node);
if ($generated_always_node->has_child_token(WP_MySQL_Lexer::VIRTUAL_SYMBOL)) $extra = trim($extra . ' VIRTUAL GENERATED');
elseif ($generated_always_node->has_child_token(WP_MySQL_Lexer::STORED_SYMBOL)) $extra = trim($extra . ' STORED GENERATED');
}
list($charset, $collation) = $this->get_column_charset_collation_from_ast($col_def_node, $mysql_norm_type, $table_collation);
list($char_max_len, $char_octet_len) = $this->get_column_lengths_from_ast($data_type_node, $mysql_norm_type, $charset);
list($num_precision, $num_scale) = $this->get_column_numeric_attrs_from_ast($data_type_node, $mysql_norm_type);
$dt_precision = $this->get_column_datetime_precision_from_ast($data_type_node, $mysql_norm_type);
return array(
'table_schema' => $this->emulated_db_name,
'table_name' => $table_name_str,
'column_name' => $column_name_str,
'ordinal_position' => $ordinal_position,
'column_default' => $default_value,
'is_nullable' => $is_nullable,
'data_type' => $mysql_norm_type, // e.g. "varchar"
'character_maximum_length' => $char_max_len,
'character_octet_length' => $char_octet_len,
'numeric_precision' => $num_precision,
'numeric_scale' => $num_scale,
'datetime_precision' => $dt_precision,
'character_set_name' => $charset,
'collation_name' => $collation,
'column_type' => $mysql_full_type, // e.g. "varchar(255)"
'column_key' => $column_key,
'extra' => trim($extra),
'privileges' => 'select,insert,update,references', // Default MySQL privileges
'column_comment' => $comment,
'generation_expression' => $gen_expr,
'srs_id' => null, // Spatial Reference System ID, not handled yet
);
}
private function parse_mysql_datatype_from_ast(WP_Parser_Node $data_type_node): array {
// Input: AST node for dataType rule. Output: [normalized_mysql_type, full_mysql_type_string]
// Example: INT(11) UNSIGNED -> ['int', 'int(11) unsigned']
// Example: VARCHAR(255) CHARACTER SET utf8 -> ['varchar', 'varchar(255)'] (charset/collate handled separately for column)
$tokens = $data_type_node->get_descendant_tokens(); // Get all tokens within dataType
$base_type_token = $tokens[0]; // Usually the first token is the base type
// Handle multi-token types like `NATIONAL VARCHAR`, `DOUBLE PRECISION` (MySQL treats `REAL` and `DOUBLE PRECISION` as `DOUBLE`)
if (count($tokens) > 1) {
if ($base_type_token->id === WP_MySQL_Lexer::NATIONAL_SYMBOL && $tokens[1]->id === WP_MySQL_Lexer::VARCHAR_SYMBOL) {
$base_type_token = $tokens[1]; // Effective type is VARCHAR
} elseif ($base_type_token->id === WP_MySQL_Lexer::NATIONAL_SYMBOL && $tokens[1]->id === WP_MySQL_Lexer::CHAR_SYMBOL) {
$base_type_token = $tokens[1]; // Effective type is CHAR
} elseif ( ($base_type_token->id === WP_MySQL_Lexer::DOUBLE_SYMBOL || $base_type_token->id === WP_MySQL_Lexer::REAL_SYMBOL) &&
isset($tokens[1]) && strtoupper($tokens[1]->value) === 'PRECISION' ) {
// MySQL just takes DOUBLE for DOUBLE PRECISION
$base_type_token = new WP_MySQL_Token(WP_MySQL_Lexer::DOUBLE_SYMBOL, 'DOUBLE');
} // Add other multi-word types if necessary
}
$norm_type = self::TOKEN_TO_TYPE_MAP[$base_type_token->id] ?? strtolower($base_type_token->value);
$full_type_str = strtolower($base_type_token->value);
// Length/Precision: (M) or (M,D)
$length_node = $data_type_node->get_first_descendant_node('fieldLength'); // For (M)
$precision_node = $data_type_node->get_first_descendant_node('precision'); // For (M,D)
$datetime_precision_node = $data_type_node->get_first_descendant_node('typeDatetimePrecision'); // For TIME/DATETIME/TIMESTAMP (FSP)
if ($length_node) $full_type_str .= $this->get_value_from_expression_node($length_node); // Includes parens
if ($precision_node) $full_type_str .= $this->get_value_from_expression_node($precision_node); // Includes parens
if ($datetime_precision_node) $full_type_str .= $this->get_value_from_expression_node($datetime_precision_node); // Includes parens
// Special cases for default lengths if not specified (for COLUMN_TYPE string)
if (!$length_node && !$precision_node) {
if ($norm_type === 'char' || $norm_type === 'binary') $full_type_str .= '(1)';
if ($norm_type === 'bit') $full_type_str .= '(1)';
if ($norm_type === 'decimal') $full_type_str .= '(10,0)';
if ($norm_type === 'year' && $base_type_token->value === 'YEAR') $full_type_str .= '(4)'; // YEAR(4) is default
}
if ($norm_type === 'tinyint' && ($base_type_token->id === WP_MySQL_Lexer::BOOL_SYMBOL || $base_type_token->id === WP_MySQL_Lexer::BOOLEAN_SYMBOL)){
if (!str_contains($full_type_str, '(')) $full_type_str .= '(1)'; // For BOOL/BOOLEAN if length not specified
}
// UNSIGNED and ZEROFILL attributes
if ($data_type_node->get_first_descendant_token(WP_MySQL_Lexer::UNSIGNED_SYMBOL) ||
$data_type_node->get_first_descendant_token(WP_MySQL_Lexer::SERIAL_SYMBOL) // SERIAL implies UNSIGNED
) {
$full_type_str .= ' unsigned';
}
if ($data_type_node->get_first_descendant_token(WP_MySQL_Lexer::ZEROFILL_SYMBOL)) {
$full_type_str .= ' zerofill';
// ZEROFILL also implies UNSIGNED for numeric types
if (!str_contains($full_type_str, 'unsigned') &&
in_array($norm_type, ['tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'decimal', 'float', 'double'])) {
$full_type_str .= ' unsigned';
}
}
// ENUM / SET values
if ($norm_type === 'enum' || $norm_type === 'set') {
$string_list_node = $data_type_node->get_first_descendant_node('stringList');
if ($string_list_node) {
$enum_set_values = [];
foreach ($string_list_node->get_child_nodes('textStringLiteral') as $val_node) { // Or textString
$enum_set_values[] = $this->pdo->quote($this->get_value($val_node));
}
$full_type_str .= '(' . implode(',', $enum_set_values) . ')';
}
}
return [$norm_type, $full_type_str];
}
private function get_column_charset_collation_from_ast(WP_Parser_Node $col_def_node, string $norm_type, ?string $table_collation): array {
// String types: CHAR, VARCHAR, TEXT variants, ENUM, SET
$string_types = ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext', 'enum', 'set'];
if (!in_array($norm_type, $string_types)) {
return [null, null];
}
$charset_val = null;
$collation_val = null;
// Check for CHARACTER SET clause on column
$charset_attr_node = $this->find_column_attribute_node($col_def_node, 'CHARACTER SET') ??
$this->find_column_attribute_node($col_def_node, 'CHARSET');
if ($charset_attr_node) {
$charset_name_node = $charset_attr_node->get_first_child_node('charsetName'); // Or identifier
if ($charset_name_node) {
$charset_val = strtolower($this->get_value($charset_name_node));
if ($charset_val === 'utf8mb3') $charset_val = 'utf8';
}
}
// Check for COLLATE clause on column
$collate_attr_node = $this->find_column_attribute_node($col_def_node, 'COLLATE');
if ($collate_attr_node) {
$collation_name_node = $collate_attr_node->get_first_child_node('collationName'); // Or identifier
if ($collation_name_node) {
$collation_val = strtolower($this->get_value($collation_name_node));
}
}
// Binary attribute on string types (e.g. VARCHAR(50) BINARY) implies binary collation for that charset.
// The dataType node itself might have a BINARY token for some types e.g. CHAR BINARY
$data_type_node = $col_def_node->get_first_descendant_node('dataType');
$is_type_declared_binary = false; // Check if CHAR/VARCHAR was defined with BINARY keyword itself.
if ($data_type_node->has_child_token(WP_MySQL_Lexer::BINARY_SYMBOL) && ($norm_type === 'char' || $norm_type === 'varchar')) {
$is_type_declared_binary = true;
}
if ($charset_val && !$collation_val) {
// If charset specified, derive collation
$collation_val = ($is_type_declared_binary || str_ends_with($charset_val, '_bin')) ? $charset_val . '_bin' : (self::CHARSET_DEFAULT_COLLATION_MAP[$charset_val] ?? $charset_val . '_general_ci');
} elseif (!$charset_val && $collation_val) {
// If collation specified, derive charset
$charset_val = explode('_', $collation_val)[0];
if ($charset_val === 'utf8mb3') $charset_val = 'utf8';
} elseif (!$charset_val && !$collation_val) {
// Neither specified, inherit from table (or ultimately DB/server default)
if ($table_collation) {
$collation_val = $table_collation;
$charset_val = explode('_', $collation_val)[0];
if ($charset_val === 'utf8mb3') $charset_val = 'utf8';
} else {
// Fallback to hardcoded default if table collation also unknown
$charset_val = 'utf8mb4';
$collation_val = 'utf8mb4_0900_ai_ci';
}
}
// If CHAR/VARCHAR was defined with BINARY keyword, this implies the _bin collation for its charset.
if ($is_type_declared_binary && $charset_val && !str_ends_with($collation_val, '_bin')) {
$collation_val = $charset_val . '_bin';
}
return [$charset_val, $collation_val];
}
private function find_column_attribute_node(WP_Parser_Node $col_def_node, string $attr_name_upper): ?WP_Parser_Node {
foreach ($col_def_node->get_descendant_nodes('columnAttribute') as $attr_node) {
$tokens = $attr_node->get_child_tokens();
if (empty($tokens)) continue;
if (strtoupper($tokens[0]->value) === $attr_name_upper) return $attr_node;
if (count($tokens)>1 && strtoupper($tokens[0]->value . " " . $tokens[1]->value) === $attr_name_upper) return $attr_node; // For "CHARACTER SET"
}
return null;
}
// Placeholder for a method that gets value from a general expression AST node.
// This is complex and requires a mini-SQL-serializer for expressions.
private function get_value_from_expression_node(WP_Parser_Node $expr_node): string {
// Simplified: attempt to concatenate values of all child tokens.
// This will not correctly handle operator precedence, function calls, etc.
// A proper implementation would recursively build the string representation.
$str = "";
foreach($expr_node->get_descendant_tokens() as $token) {
$str .= $token->value . " ";
}
return trim($str);
}
// ... Other private helpers for parsing lengths, numeric attributes, datetime precision from AST ...
// These would be similar to the ones in the SQLite driver's InformationSchemaBuilder,
// but focused on extracting MySQL specific details from the AST.
private function get_column_lengths_from_ast(WP_Parser_Node $data_type_node, string $norm_type, ?string $charset): array {
// Returns [char_max_len, char_octet_len]
$len_m = null;
$field_length_node = $data_type_node->get_first_descendant_node('fieldLength');
if ($field_length_node) {
$len_m = (int) $this->get_value_from_expression_node($field_length_node); // extracts number from (M)
$len_m = abs($len_m); // M must be positive
}
$string_types = ['char', 'varchar'];
$text_blob_sizes = [
'tinytext' => 255, 'tinyblob' => 255,
'text' => 65535, 'blob' => 65535,
'mediumtext' => 16777215, 'mediumblob' => 16777215,
'longtext' => 4294967295, 'longblob' => 4294967295,
];
if (in_array($norm_type, $string_types)) {
$max_len = $len_m ?? (($norm_type === 'char') ? 1 : null); // VARCHAR has no default length in MySQL, error if not given. CHAR defaults to 1.
if ($max_len === null && $norm_type === 'varchar') {
// This should be an error during MySQL parsing if VARCHAR length is missing.
// Here, assume AST is valid, so length must be present for VARCHAR.
}
$bytes_per_char = ($charset && isset(self::CHARSET_MAX_BYTES_MAP[$charset])) ? self::CHARSET_MAX_BYTES_MAP[$charset] : 1;
return [$max_len, $max_len ? $max_len * $bytes_per_char : null];
} elseif (array_key_exists($norm_type, $text_blob_sizes)) {
$size = $text_blob_sizes[$norm_type];
return [$size, $size]; // For text/blob, char_max_len and octet_len are same fixed values.
} elseif ($norm_type === 'enum' || $norm_type === 'set') {
// Max length of an enum/set value string
$max_val_len = 0;
$string_list_node = $data_type_node->get_first_descendant_node('stringList');
if ($string_list_node) {
foreach ($string_list_node->get_child_nodes('textStringLiteral') as $val_node) {
$max_val_len = max($max_val_len, strlen($this->get_value($val_node)));
}
}
$bytes_per_char = ($charset && isset(self::CHARSET_MAX_BYTES_MAP[$charset])) ? self::CHARSET_MAX_BYTES_MAP[$charset] : 1;
return [$max_val_len, $max_val_len * $bytes_per_char];
}
return [null, null]; // Not applicable for non-string types
}
private function get_column_numeric_attrs_from_ast(WP_Parser_Node $data_type_node, string $norm_type): array {
// Returns [numeric_precision, numeric_scale]
$precision = null; $scale = null;
$length_node = $data_type_node->get_first_descendant_node('fieldLength'); // (M)
$precision_def_node = $data_type_node->get_first_descendant_node('precision'); // (M,D)
if ($precision_def_node) {
$parts = explode(',', trim($this->get_value_from_expression_node($precision_def_node), '()'));
$precision = isset($parts[0]) ? (int)$parts[0] : null;
$scale = isset($parts[1]) ? (int)$parts[1] : null;
} elseif ($length_node) {
$precision = (int)trim($this->get_value_from_expression_node($length_node), '()');
// For float/double, if only M is given, it's total digits. D is digits after decimal.
// MySQL docs say: if D is omitted, it's 0 for DECIMAL/NUMERIC. For FLOAT/DOUBLE, D is not fixed if omitted.
// The INFORMATION_SCHEMA for FLOAT/DOUBLE with only M specified is a bit murky.
// Often NUMERIC_PRECISION reflects M, and NUMERIC_SCALE is NULL or related to actual stored values.
if ($norm_type === 'decimal' || $norm_type === 'numeric') $scale = 0;
}
// Default precisions for integer types (display width, not storage precision)
$int_precisions = ['tinyint'=>3, 'smallint'=>5, 'mediumint'=>8, 'int'=>10, 'bigint'=>19]; // Signed max digits approx
if (array_key_exists($norm_type, $int_precisions)) {
$precision = $precision ?? $int_precisions[$norm_type]; // M is display width, not really precision
$scale = $scale ?? 0;
// If unsigned, bigint can be 20
if ($norm_type === 'bigint' && $data_type_node->get_first_descendant_token(WP_MySQL_Lexer::UNSIGNED_SYMBOL)) {
$precision = 20;
}
} elseif ($norm_type === 'float') {
$precision = $precision ?? 12; // Default approx based on single precision range
// Scale for float is variable if not specified.
} elseif ($norm_type === 'double' || $norm_type === 'real') {
$precision = $precision ?? 22; // Default approx based on double precision range
} elseif ($norm_type === 'decimal' || $norm_type === 'numeric') {
$precision = $precision ?? 10;
$scale = $scale ?? 0;
} elseif ($norm_type === 'bit') {
$precision = $precision ?? 1; // M in BIT(M) is bit length
$scale = null; // Not applicable
}
return [$precision, $scale];
}
private function get_column_datetime_precision_from_ast(WP_Parser_Node $data_type_node, string $norm_type): ?int {
// Fractional Seconds Precision (FSP) for TIME, DATETIME, TIMESTAMP
if (in_array($norm_type, ['time', 'datetime', 'timestamp'])) {
$fsp_node = $data_type_node->get_first_descendant_node('typeDatetimePrecision');
if ($fsp_node) {
return (int)trim($this->get_value_from_expression_node($fsp_node), '()');
}
return 0; // Default FSP is 0 if not specified
}
return null;
}
// --- CRUD helpers for emulated schema tables ---
private function insert_values( string $table_name, array $data ): void {
if (empty($data)) return;
$columns = implode(', ', array_map([$this, 'quote_pgsql_identifier_simple'], array_keys($data)));
$placeholders = implode(', ', array_fill(0, count($data), '?'));
$sql = "INSERT INTO $table_name ($columns) VALUES ($placeholders)";
$this->query($sql, array_values($data));
}
private function update_values( string $table_name, array $data, array $where_criteria ): void {
if (empty($data) || empty($where_criteria)) return;
$set_clauses = [];
$params = [];
foreach ($data as $col => $val) {
$set_clauses[] = $this->quote_pgsql_identifier_simple($col) . " = ?";
$params[] = $val;
}
$where_clauses = [];
foreach ($where_criteria as $col => $val) {
$where_clauses[] = $this->quote_pgsql_identifier_simple($col) . " = ?";
$params[] = $val;
}
$sql = "UPDATE $table_name SET " . implode(', ', $set_clauses) . " WHERE " . implode(' AND ', $where_clauses);
$this->query($sql, $params);
}
private function delete_values( string $table_name, array $where_criteria ): void {
if (empty($where_criteria)) return; // Safety: don't delete all rows
$where_clauses = [];
$params = [];
foreach ($where_criteria as $col => $val) {
$where_clauses[] = $this->quote_pgsql_identifier_simple($col) . " = ?";
$params[] = $val;
}
$sql = "DELETE FROM $table_name WHERE " . implode(' AND ', $where_clauses);
$this->query($sql, $params);
}
// Simplified quoter for known-safe column names in emulated schema tables
private function quote_pgsql_identifier_simple(string $name): string {
return '"' . str_replace('"', '""', $name) . '"';
}
private function query( string $query, array $params = array() ) {
return call_user_func($this->query_callback, $query, $params);
}
// --- Helper methods for handling specific ALTER TABLE actions and table constraint definitions ---
private function record_table_constraint_def_from_ast(string $table_name_str, WP_Parser_Node $constraint_node, bool $is_temporary, string $stats_table, string $tbl_constraints_table, string $kcu_table): void {
$constraint_name_node = $constraint_node->get_first_child_node('constraintName');
$constraint_name_str = $constraint_name_node ? $this->get_value($constraint_name_node) : null;
$constraint_type_str = '';
$index_name_for_stats = null;
$non_unique_for_stats = 1;
$index_type_for_stats = 'BTREE'; // Default
$key_list_node = $constraint_node->get_first_descendant_node('keyListVariants'); // Or keyList
if (!$key_list_node) return; // Cannot define constraint without columns
$key_parts_nodes = $key_list_node->get_descendant_nodes('keyPart'); // Or keyPartOrExpression for functional indexes
$columns_in_constraint = [];
foreach($key_parts_nodes as $kp_node) {
$col_name_node = $kp_node->get_first_child_node('identifier');
if ($col_name_node) {
$columns_in_constraint[] = [
'name' => $this->get_value($col_name_node),
'sub_part' => null, // Parse length if present: (M)
'order' => ($kp_node->has_child_token(WP_MySQL_Lexer::DESC_SYMBOL)) ? 'D' : 'A',
];
// TODO: Parse sub_part length from keyPart -> fieldLength if it exists
}
// TODO: Handle functional index expressions if keyPartOrExpression is used
}
if (empty($columns_in_constraint)) return;
$first_token_in_constraint = $constraint_node->get_first_child_token(); // e.g. PRIMARY, UNIQUE, FOREIGN, KEY, INDEX, CONSTRAINT
// Determine constraint type and name for stats
if ($constraint_node->has_child_token(WP_MySQL_Lexer::PRIMARY_SYMBOL) || ($first_token_in_constraint && $first_token_in_constraint->id === WP_MySQL_Lexer::PRIMARY_SYMBOL)) {
$constraint_type_str = 'PRIMARY KEY';
$index_name_for_stats = 'PRIMARY';
$non_unique_for_stats = 0;
if (!$constraint_name_str) $constraint_name_str = 'PRIMARY'; // MySQL names PK constraint 'PRIMARY'
} elseif ($constraint_node->has_child_token(WP_MySQL_Lexer::UNIQUE_SYMBOL) || ($first_token_in_constraint && $first_token_in_constraint->id === WP_MySQL_Lexer::UNIQUE_SYMBOL)) {
$constraint_type_str = 'UNIQUE';
$non_unique_for_stats = 0;
$index_name_for_stats = $constraint_name_str ?? $columns_in_constraint[0]['name']; // MySQL default unique key name
if (!$constraint_name_str) $constraint_name_str = $index_name_for_stats;
} elseif ($constraint_node->has_child_token(WP_MySQL_Lexer::FOREIGN_SYMBOL) || ($first_token_in_constraint && $first_token_in_constraint->id === WP_MySQL_Lexer::FOREIGN_SYMBOL)) {
$constraint_type_str = 'FOREIGN KEY';
// Foreign keys are not typically in 'statistics' table in MySQL in the same way as indexes.
// They are in referential_constraints and key_column_usage.
// Index might be auto-created by InnoDB for FK. If so, its name would differ.
// For now, skip adding to statistics table directly from FK def unless an explicit index is also part of AST.
$index_name_for_stats = null; // Or name of auto-created index if applicable.
if (!$constraint_name_str) $constraint_name_str = $table_name_str . '_ibfk_' . uniqid();
} elseif ($constraint_node->has_child_token(WP_MySQL_Lexer::INDEX_SYMBOL) || $constraint_node->has_child_token(WP_MySQL_Lexer::KEY_SYMBOL) ||
($first_token_in_constraint && ($first_token_in_constraint->id === WP_MySQL_Lexer::INDEX_SYMBOL || $first_token_in_constraint->id === WP_MySQL_Lexer::KEY_SYMBOL)) ) {
$constraint_type_str = 'INDEX'; // This is not a formal SQL constraint type but MySQL uses KEY/INDEX.
$non_unique_for_stats = 1;
$index_name_for_stats = $constraint_name_str ?? $columns_in_constraint[0]['name'];
if (!$constraint_name_str) $constraint_name_str = $index_name_for_stats; // This is for constraint name, index name for stats is index_name_for_stats
} elseif ($constraint_node->has_child_token(WP_MySQL_Lexer::CHECK_SYMBOL) || ($first_token_in_constraint && $first_token_in_constraint->id === WP_MySQL_Lexer::CHECK_SYMBOL)) {
$constraint_type_str = 'CHECK';
$index_name_for_stats = null; // CHECK constraints don't go into statistics.
if (!$constraint_name_str) $constraint_name_str = $table_name_str . '_chk_' . uniqid();
} else {
// Unidentified constraint type (e.g. just CONSTRAINT name ...) - less common for tableConstraintDef
return;
}
// For FULLTEXT/SPATIAL indexes, keyword appears before INDEX/KEY
if ($constraint_node->has_child_token(WP_MySQL_Lexer::FULLTEXT_SYMBOL)) $index_type_for_stats = 'FULLTEXT';
if ($constraint_node->has_child_token(WP_MySQL_Lexer::SPATIAL_SYMBOL)) $index_type_for_stats = 'SPATIAL';
// Record in table_constraints
$this->insert_values($tbl_constraints_table, [
'constraint_catalog' => $this->actual_pg_schema_name, // Or appropriate catalog source
'constraint_schema' => $this->emulated_db_name,
'constraint_name' => $constraint_name_str,
'table_schema' => $this->emulated_db_name,
'table_name' => $table_name_str,
'constraint_type' => $constraint_type_str,
]);
// Record in statistics (for PK, UNIQUE, INDEX/KEY)
if ($index_name_for_stats !== null && ($constraint_type_str === 'PRIMARY KEY' || $constraint_type_str === 'UNIQUE' || $constraint_type_str === 'INDEX')) {
$seq = 1;
foreach ($columns_in_constraint as $col_info) {
// Need to determine IS_NULLABLE for the column in this index.
// For PK, it's always ''. For UNIQUE, depends on column's nullability.
// This requires querying the columns info table.
$is_col_nullable_in_stats = ''; // Assume NOT NULL for PK. For others, query.
// For simplicity, placeholder. A full implementation would fetch column's IS_NULLABLE.
// If part of PK, then IS_NULLABLE in COLUMNS becomes NO.
$this->insert_values($stats_table, [
'table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str,
'non_unique' => $non_unique_for_stats, 'index_schema' => $this->emulated_db_name,
'index_name' => $index_name_for_stats, 'seq_in_index' => $seq,
'column_name' => $col_info['name'],
'COLLATION' => $col_info['order'],
'sub_part' => $col_info['sub_part'],
'nullable' => $is_col_nullable_in_stats, // Needs proper derivation
'index_type' => $index_type_for_stats, // BTREE, HASH, FULLTEXT, SPATIAL
'index_comment' => '', // Parse from indexOption if present
]);
$seq++;
}
}
// Record in key_column_usage (for PK, UNIQUE, FK)
if ($constraint_type_str === 'PRIMARY KEY' || $constraint_type_str === 'UNIQUE' || $constraint_type_str === 'FOREIGN KEY') {
$seq_kcu = 1;
foreach ($columns_in_constraint as $col_info) {
$kcu_data = [
'constraint_catalog' => $this->actual_pg_schema_name, 'constraint_schema' => $this->emulated_db_name,
'constraint_name' => $constraint_name_str, 'table_catalog' => $this->actual_pg_schema_name,
'table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str,
'column_name' => $col_info['name'], 'ordinal_position' => $seq_kcu,
];
if ($constraint_type_str === 'FOREIGN KEY') {
// Parse referenced table/columns from AST (referencesClause)
$ref_clause_node = $constraint_node->get_first_descendant_node('referencesClause');
if ($ref_clause_node) {
$ref_table_node = $ref_clause_node->get_first_child_node('tableName'); // Or tableRef
$kcu_data['referenced_table_name'] = $this->get_value($ref_table_node);
// Referenced columns from keyList in referencesClause
$ref_cols_list_node = $ref_clause_node->get_first_child_node('keyList');
if ($ref_cols_list_node && isset($ref_cols_list_node->get_child_nodes('identifier')[$seq_kcu-1])) {
$kcu_data['referenced_column_name'] = $this->get_value($ref_cols_list_node->get_child_nodes('identifier')[$seq_kcu-1]);
}
// POSITION_IN_UNIQUE_CONSTRAINT needs matching with referenced table's PK/UNIQUE constraint columns.
}
}
$this->insert_values($kcu_table, $kcu_data);
$seq_kcu++;
}
}
// TODO: Record in referential_constraints for FOREIGN KEY (MATCH, UPDATE/DELETE rules)
}
private function handle_alter_add_from_ast(WP_Parser_Node $action_node, string $table_name_str, bool $is_temporary, string $cols_table, string $stats_table, string $tbl_constraints_table, string $kcu_table, array &$current_cols_map): void {
$column_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::COLUMN_SYMBOL);
$constraint_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::CONSTRAINT_SYMBOL);
if ($column_keyword || $action_node->get_first_descendant_node('columnDefinition')) { // ADD [COLUMN] col_def
$col_def_node = $action_node->get_first_descendant_node('columnDefinition');
if (!$col_def_node) return; // Should exist if it's ADD COLUMN
$column_name_str = $this->get_value($col_def_node->get_first_child_node('fieldIdentifier'));
// Determine ordinal position (MySQL supports FIRST, AFTER col)
// For simplicity, add at end if not specified.
$max_pos = 0;
foreach ($current_cols_map as $pos) $max_pos = max($max_pos, $pos);
$ordinal_position = $max_pos + 1;
// TODO: Handle FIRST/AFTER for ordinal_position and re-numbering.
$table_collation_stmt = $this->query("SELECT table_collation FROM ".$this->get_emulated_info_table_name('tables', $is_temporary)." WHERE table_schema = ? AND table_name = ?", [$this->emulated_db_name, $table_name_str]);
$table_collation = $table_collation_stmt->fetchColumn();
$mysql_column_data = $this->extract_mysql_column_data_from_ast($table_name_str, $column_name_str, $col_def_node, $ordinal_position, $table_collation);
$this->insert_values($cols_table, $mysql_column_data);
$current_cols_map[$column_name_str] = $ordinal_position; // Update map
// Handle inline constraint if any
if ($mysql_column_data['column_key'] === 'PRI' || $mysql_column_data['column_key'] === 'UNI') {
// Add to statistics, table_constraints, key_column_usage as in record_create_table
}
} elseif ($constraint_keyword || $action_node->get_first_descendant_node('tableConstraintDef')) { // ADD CONSTRAINT ...
$constraint_def_node = $action_node->get_first_descendant_node('tableConstraintDef');
if ($constraint_def_node) {
$this->record_table_constraint_def_from_ast($table_name_str, $constraint_def_node, $is_temporary, $stats_table, $tbl_constraints_table, $kcu_table);
}
} elseif ($action_node->get_first_child_token(WP_MySQL_Lexer::INDEX_SYMBOL) || $action_node->get_first_child_token(WP_MySQL_Lexer::KEY_SYMBOL) ||
$action_node->get_first_child_token(WP_MySQL_Lexer::FULLTEXT_SYMBOL) || $action_node->get_first_child_token(WP_MySQL_Lexer::SPATIAL_SYMBOL) ) {
// ADD INDEX/KEY/FULLTEXT/SPATIAL ... (not inside a tableConstraintDef node, but directly)
// This structure is common too. Need to parse index name, columns from here.
// This is essentially like creating a tableConstraintDef node on the fly from these parts.
// Example: ADD INDEX my_idx (col1, col2)
// For now, assume it's covered if AST nests this under a tableConstraintDef like structure.
// If not, this needs specific parsing here.
}
}
private function handle_alter_drop_from_ast(WP_Parser_Node $action_node, string $table_name_str, bool $is_temporary, string $cols_table, string $stats_table, string $tbl_constraints_table, string $kcu_table, array &$col_map_recreate): void {
$column_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::COLUMN_SYMBOL);
$constraint_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::CONSTRAINT_SYMBOL);
$index_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::INDEX_SYMBOL) ?? $action_node->get_first_child_token(WP_MySQL_Lexer::KEY_SYMBOL);
$primary_key_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::PRIMARY_SYMBOL) && $action_node->get_first_child_token(WP_MySQL_Lexer::KEY_SYMBOL, 1); // DROP PRIMARY KEY
$foreign_key_keyword = $action_node->get_first_child_token(WP_MySQL_Lexer::FOREIGN_SYMBOL) && $action_node->get_first_child_token(WP_MySQL_Lexer::KEY_SYMBOL, 1); // DROP FOREIGN KEY fk_name
if ($column_keyword || $action_node->get_first_child_node('fieldIdentifier')) { // DROP [COLUMN] col_name
$col_name_node = $action_node->get_first_child_node('fieldIdentifier');
if (!$col_name_node) return;
$col_name_to_drop = $this->get_value($col_name_node);
$this->delete_values($cols_table, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_name_to_drop]);
unset($col_map_recreate[$col_name_to_drop]);
// Remove column from any statistics entries
$this->delete_values($stats_table, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_name_to_drop]);
// TODO: If all columns of an index are dropped, the index itself should be dropped from statistics and table_constraints.
// This needs checking which indexes are now empty.
// Also re-sequence SEQ_IN_INDEX for affected multi-column indexes.
// Remove from KCU if part of constraints
$this->delete_values($kcu_table, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_name_to_drop]);
} elseif ($index_keyword || $primary_key_keyword || $foreign_key_keyword || $constraint_keyword) { // DROP INDEX idx_name | DROP PRIMARY KEY | DROP FOREIGN KEY fk_name | DROP CONSTRAINT cons_name
$name_to_drop = null;
if ($primary_key_keyword) {
$name_to_drop = 'PRIMARY'; // Standard name for PK index/constraint
$constraint_type_to_drop = 'PRIMARY KEY';
} else {
$name_node = $action_node->get_first_child_node('indexRef') ?? // For DROP INDEX
$action_node->get_first_child_node('fkSymbol') ?? // For DROP FOREIGN KEY (fk_name)
$action_node->get_first_child_node('constraintName'); // For DROP CONSTRAINT
if (!$name_node) return;
$name_to_drop = $this->get_value($name_node);
$constraint_type_to_drop = $foreign_key_keyword ? 'FOREIGN KEY' : null; // If not FK, type needs to be fetched or assumed INDEX/UNIQUE
}
// Delete from statistics
$this->delete_values($stats_table, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'index_name' => $name_to_drop]);
// Delete from table_constraints
$where_tc = ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'constraint_name' => $name_to_drop];
if ($constraint_type_to_drop) $where_tc['constraint_type'] = $constraint_type_to_drop;
$this->delete_values($tbl_constraints_table, $where_tc);
// Delete from key_column_usage
$this->delete_values($kcu_table, ['constraint_schema' => $this->emulated_db_name, 'constraint_name' => $name_to_drop, 'table_name' => $table_name_str]);
// If FK dropped, delete from referential_constraints
if ($foreign_key_keyword || ($constraint_type_to_drop === 'FOREIGN KEY')) {
$this->delete_values($this->get_emulated_info_table_name('referential_constraints', $is_temporary),
['constraint_schema' => $this->emulated_db_name, 'constraint_name' => $name_to_drop]);
}
}
}
private function handle_alter_change_from_ast(WP_Parser_Node $action_node, string $table_name_str, bool $is_temporary, string $cols_table, string $stats_table, string $tbl_constraints_table, string $kcu_table, array &$col_map_recreate): void {
$old_col_name_node = $action_node->get_first_child_node('fieldIdentifier');
$new_col_def_node = $action_node->get_first_descendant_node('columnDefinition'); // MySQL CHANGE provides full new definition
if (!$old_col_name_node || !$new_col_def_node) return;
$old_col_name_str = $this->get_value($old_col_name_node);
$new_col_name_str = $this->get_value($new_col_def_node->get_first_child_node('fieldIdentifier'));
// Get current ordinal position to preserve it
$ordinal_pos_stmt = $this->query("SELECT ordinal_position FROM $cols_table WHERE table_schema = ? AND table_name = ? AND column_name = ?", [$this->emulated_db_name, $table_name_str, $old_col_name_str]);
$ordinal_position = $ordinal_pos_stmt->fetchColumn();
if ($ordinal_position === false) $ordinal_position = 999; // Fallback if not found (shouldn't happen)
$table_collation_stmt = $this->query("SELECT table_collation FROM ".$this->get_emulated_info_table_name('tables', $is_temporary)." WHERE table_schema = ? AND table_name = ?", [$this->emulated_db_name, $table_name_str]);
$table_collation = $table_collation_stmt->fetchColumn();
$mysql_column_data = $this->extract_mysql_column_data_from_ast($table_name_str, $new_col_name_str, $new_col_def_node, (int)$ordinal_position, $table_collation);
$this->update_values($cols_table, $mysql_column_data, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $old_col_name_str]);
if ($old_col_name_str !== $new_col_name_str) {
$col_map_recreate[$old_col_name_str] = $new_col_name_str;
// Update column name in statistics and KCU
$this->update_values($stats_table, ['column_name' => $new_col_name_str], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $old_col_name_str]);
$this->update_values($kcu_table, ['column_name' => $new_col_name_str], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $old_col_name_str, 'constraint_schema' => $this->emulated_db_name]);
}
// If inline constraint changed, it's more complex. CHANGE often implies previous inline constraints on old col are gone unless redefined.
// This simplified version relies on sync_column_key_info to update based on stats table,
// and assumes new_col_def_node might add new inline constraints that get recorded.
}
private function handle_alter_modify_from_ast(WP_Parser_Node $action_node, string $table_name_str, bool $is_temporary, string $cols_table, string $stats_table, string $tbl_constraints_table, string $kcu_table): void {
// MODIFY [COLUMN] col_name new_col_def (no rename)
$col_name_node = $action_node->get_first_child_node('fieldIdentifier');
$new_col_def_node = $action_node->get_first_descendant_node('columnDefinition'); // Same as CHANGE, but col name in def must match existing.
if (!$col_name_node || !$new_col_def_node) return;
$col_name_str = $this->get_value($col_name_node);
// Ensure name in new_col_def matches col_name_str for MODIFY semantic
$name_in_def = $this->get_value($new_col_def_node->get_first_child_node('fieldIdentifier'));
if ($col_name_str !== $name_in_def) {
// This is an error in MySQL syntax or parser error. MODIFY doesn't rename.
return;
}
// Get current ordinal position
$ordinal_pos_stmt = $this->query("SELECT ordinal_position FROM $cols_table WHERE table_schema = ? AND table_name = ? AND column_name = ?", [$this->emulated_db_name, $table_name_str, $col_name_str]);
$ordinal_position = $ordinal_pos_stmt->fetchColumn();
if ($ordinal_position === false) return; // Column not found
$table_collation_stmt = $this->query("SELECT table_collation FROM ".$this->get_emulated_info_table_name('tables', $is_temporary)." WHERE table_schema = ? AND table_name = ?", [$this->emulated_db_name, $table_name_str]);
$table_collation = $table_collation_stmt->fetchColumn();
$mysql_column_data = $this->extract_mysql_column_data_from_ast($table_name_str, $col_name_str, $new_col_def_node, (int)$ordinal_position, $table_collation);
$this->update_values($cols_table, $mysql_column_data, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_name_str]);
}
private function handle_alter_column_options_from_ast(WP_Parser_Node $action_node, string $table_name_str, bool $is_temporary, string $cols_table): void {
// ALTER [COLUMN] col_name SET DEFAULT literal | DROP DEFAULT
$col_name_node = $action_node->get_first_child_node('fieldIdentifier');
if (!$col_name_node) return;
$col_name_str = $this->get_value($col_name_node);
$update_data = [];
if ($action_node->has_child_token(WP_MySQL_Lexer::SET_SYMBOL) && $action_node->has_child_token(WP_MySQL_Lexer::DEFAULT_SYMBOL, 1)) {
$default_literal_node = $action_node->get_first_descendant_node('signedLiteral') ?? $action_node->get_first_descendant_node('textStringLiteral');
// Could also be CURRENT_TIMESTAMP etc.
if ($default_literal_node) {
$update_data['column_default'] = $this->get_value($default_literal_node);
} elseif ($action_node->has_child_token(WP_MySQL_Lexer::CURRENT_TIMESTAMP_SYMBOL) || $action_node->has_child_token(WP_MySQL_Lexer::NOW_SYMBOL)) {
$update_data['column_default'] = 'CURRENT_TIMESTAMP';
}
} elseif ($action_node->has_child_token(WP_MySQL_Lexer::DROP_SYMBOL) && $action_node->has_child_token(WP_MySQL_Lexer::DEFAULT_SYMBOL, 1)) {
$update_data['column_default'] = null;
}
if (!empty($update_data)) {
$this->update_values($cols_table, $update_data, ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_name_str]);
}
}
private function handle_alter_rename_from_ast(WP_Parser_Node $action_node, string $current_table_name_str, bool $is_temporary, array &$col_map_recreate /* more tables needed for full rename handling */): void {
// RENAME [COLUMN] old TO new | RENAME [INDEX] old TO new | RENAME [TO|AS] new_table_name
$tables_info = $this->get_emulated_info_table_name('tables', $is_temporary);
$cols_info = $this->get_emulated_info_table_name('columns', $is_temporary);
$stats_info = $this->get_emulated_info_table_name('statistics', $is_temporary);
$constraints_info = $this->get_emulated_info_table_name('table_constraints', $is_temporary);
$kcu_info = $this->get_emulated_info_table_name('key_column_usage', $is_temporary);
// etc. for other info tables.
if ($action_node->has_child_token(WP_MySQL_Lexer::COLUMN_SYMBOL)) {
$old_name_node = $action_node->get_first_child_node('fieldIdentifier'); // After COLUMN
$new_name_node = $action_node->get_first_child_node('identifier'); // After TO or target of RENAME
if ($old_name_node && $new_name_node) {
$old_name = $this->get_value($old_name_node);
$new_name = $this->get_value($new_name_node);
$this->update_values($cols_info, ['column_name' => $new_name], ['table_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str, 'column_name'=>$old_name]);
$this->update_values($stats_info, ['column_name' => $new_name], ['table_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str, 'column_name'=>$old_name]);
$this->update_values($kcu_info, ['column_name' => $new_name], ['table_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str, 'column_name'=>$old_name, 'constraint_schema'=>$this->emulated_db_name]);
if (isset($col_map_recreate[$old_name])) $col_map_recreate[$new_name] = $col_map_recreate[$old_name]; unset($col_map_recreate[$old_name]);
}
} elseif ($action_node->has_child_token(WP_MySQL_Lexer::INDEX_SYMBOL) || $action_node->has_child_token(WP_MySQL_Lexer::KEY_SYMBOL)) {
$old_name_node = $action_node->get_first_child_node('indexRef'); // old index name
$new_name_node = $action_node->get_first_child_node('identifier'); // new index name (target of TO)
if ($old_name_node && $new_name_node) {
$old_name = $this->get_value($old_name_node);
$new_name = $this->get_value($new_name_node);
$this->update_values($stats_info, ['index_name' => $new_name], ['table_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str, 'index_name'=>$old_name]);
// Also update constraint_name in table_constraints and key_column_usage if this index was backing a constraint
$this->update_values($constraints_info, ['constraint_name' => $new_name], ['table_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str, 'constraint_name'=>$old_name]);
$this->update_values($kcu_info, ['constraint_name' => $new_name], ['constraint_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str, 'constraint_name'=>$old_name]);
}
} elseif ($action_node->has_child_token(WP_MySQL_Lexer::TO_SYMBOL) || $action_node->has_child_token(WP_MySQL_Lexer::AS_SYMBOL)) { // RENAME [TO|AS] new_table_name
$new_table_name_node = $action_node->get_first_child_node('tableName'); // Or identifier
if ($new_table_name_node) {
$new_table_name = $this->get_value($new_table_name_node);
$where_old_table = ['table_schema'=>$this->emulated_db_name, 'table_name'=>$current_table_name_str];
$update_new_table_name = ['table_name' => $new_table_name];
$this->update_values($tables_info, $update_new_table_name, $where_old_table);
$this->update_values($cols_info, $update_new_table_name, $where_old_table);
$this->update_values($stats_info, $update_new_table_name, $where_old_table);
$this->update_values($constraints_info, $update_new_table_name, $where_old_table);
$this->update_values($kcu_info, $update_new_table_name, array_merge($where_old_table, ['constraint_schema'=>$this->emulated_db_name]));
// Update table name in referential_constraints if it's referenced or references others
}
}
}
private function sync_column_key_info_after_change(string $table_name_str, bool $is_temporary): void {
$cols_table = $this->get_emulated_info_table_name('columns', $is_temporary);
$stats_table = $this->get_emulated_info_table_name('statistics', $is_temporary);
// First, clear all existing COLUMN_KEY for the table
$this->update_values($cols_table, ['column_key' => ''], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str]);
// Set MUL for any column that is part of any index and is first in that index
$stmt_mul = $this->query(
"SELECT DISTINCT column_name FROM $stats_table
WHERE table_schema = ? AND table_name = ? AND seq_in_index = 1",
[$this->emulated_db_name, $table_name_str]
);
while ($col_row = $stmt_mul->fetch(PDO::FETCH_ASSOC)) {
$this->update_values($cols_table, ['column_key' => 'MUL'], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_row['column_name']]);
}
// Set UNI for any column that is part of any UNIQUE index and is first in that index
$stmt_uni = $this->query(
"SELECT DISTINCT column_name FROM $stats_table
WHERE table_schema = ? AND table_name = ? AND non_unique = 0 AND seq_in_index = 1 AND index_name != 'PRIMARY'",
[$this->emulated_db_name, $table_name_str]
);
while ($col_row = $stmt_uni->fetch(PDO::FETCH_ASSOC)) {
$this->update_values($cols_table, ['column_key' => 'UNI'], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_row['column_name']]);
}
// Set PRI for any column that is part of PRIMARY KEY
$stmt_pri = $this->query(
"SELECT DISTINCT column_name FROM $stats_table
WHERE table_schema = ? AND table_name = ? AND index_name = 'PRIMARY'",
[$this->emulated_db_name, $table_name_str]
);
while ($col_row = $stmt_pri->fetch(PDO::FETCH_ASSOC)) {
$this->update_values($cols_table, ['column_key' => 'PRI', 'is_nullable' => 'NO'], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_row['column_name']]);
}
}
private function resequence_ordinal_positions(string $table_name_str, bool $is_temporary, string $cols_table): void {
// After ADD/DROP/CHANGE that might affect order (if FIRST/AFTER was handled)
// Fetch columns in their current order (which might be just insertion order if not explicitly managed)
// For simplicity, this just re-numbers based on current DB order.
// MySQL's internal order is usually preserved unless columns are reordered.
// If actual reordering (FIRST/AFTER) was implemented, this would ensure ORDINAL_POSITION is sequential.
$stmt = $this->query(
"SELECT column_name FROM $cols_table
WHERE table_schema = ? AND table_name = ?
ORDER BY ordinal_position", // Assume ordinal_position reflects desired order somehow.
[$this->emulated_db_name, $table_name_str]
);
$current_ordered_cols = $stmt->fetchAll(PDO::FETCH_COLUMN);
$pos = 1;
foreach($current_ordered_cols as $col_name) {
$this->update_values($cols_table, ['ordinal_position' => $pos], ['table_schema' => $this->emulated_db_name, 'table_name' => $table_name_str, 'column_name' => $col_name]);
$pos++;
}
}
public function get_detailed_index_info_for_show(string $pg_schema_unused, string $pg_table_name, string $mysql_index_name): array {
// This method should query the *emulated* MySQL schema table 'statistics'.
// $pg_schema_unused is the actual PG schema, but our emulated info uses $this->emulated_db_name as 'TABLE_SCHEMA'.
// $mysql_index_name is the name of the index as per MySQL conventions (e.g., 'PRIMARY', 'idx_mycolumn').
$stats_table = $this->get_emulated_info_table_name('statistics', false); // Assuming non-temporary table info
// Prepare the SQL to fetch all parts of the specified index
$sql = "SELECT
table_name AS \"Table\",
non_unique AS \"Non_unique\",
index_name AS \"Key_name\",
seq_in_index AS \"Seq_in_index\",
column_name AS \"Column_name\",
collation AS \"Collation\", -- 'A' for ASC, 'D' for DESC, NULL if not applicable
cardinality AS \"Cardinality\", -- This is often an estimate in MySQL
sub_part AS \"Sub_part\", -- For prefix indexes (e.g., col(10))
packed AS \"Packed\", -- MySQL specific (relevant for MyISAM, often NULL)
nullable AS \"Null\", -- 'YES' if column in index can be NULL, '' if not (MySQL specific format)
index_type AS \"Index_type\", -- BTREE, HASH, FULLTEXT, SPATIAL (MySQL types)
comment AS \"Comment\", -- MySQL index comment, not column comment
index_comment AS \"Index_comment\",-- MySQL index comment
is_visible AS \"Visible\" -- 'YES' or 'NO'
FROM $stats_table
WHERE table_schema = ? AND table_name = ? AND index_name = ?
ORDER BY seq_in_index ASC"; // Order by sequence in index is crucial
$params = [$this->emulated_db_name, $pg_table_name, $mysql_index_name];
$stmt = $this->query($sql, $params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Format results to match MySQL SHOW INDEX output more closely if needed
$formatted_results = [];
foreach ($results as $row) {
// MySQL's Non_unique is 0 for unique, 1 for non-unique.
// MySQL's Null is '' if NOT NULL, 'YES' if nullable.
// MySQL's Collation is 'A' (Ascending), 'D' (Descending), or NULL.
// Cardinality can be a large number or NULL.
// Sub_part is integer or NULL.
$formatted_row = new stdClass();
$formatted_row->Table = $row['Table'];
$formatted_row->Non_unique = (int)$row['Non_unique'];
$formatted_row->Key_name = $row['Key_name'];
$formatted_row->Seq_in_index = (int)$row['Seq_in_index'];
$formatted_row->Column_name = $row['Column_name'];
$formatted_row->Collation = $row['Collation']; // 'A', 'D', or null
$formatted_row->Cardinality = ($row['Cardinality'] !== null) ? (int)$row['Cardinality'] : null;
$formatted_row->Sub_part = ($row['Sub_part'] !== null) ? (int)$row['Sub_part'] : null;
$formatted_row->Packed = $row['Packed']; // Typically NULL for InnoDB
$formatted_row->Null = $row['Null']; // 'YES' or ''
$formatted_row->Index_type = $row['Index_type'];
$formatted_row->Comment = $row['Comment']; // Often empty unless specific index options were used
$formatted_row->Index_comment = $row['Index_comment'];
$formatted_row->Visible = $row['Visible']; // MySQL 8+ feature
$formatted_row->Expression = $row['EXPRESSION'] ?? null; // MySQL 8+ functional index expression
$formatted_results[] = $formatted_row;
}
return $formatted_results;
}
public function get_primary_key_columns(string $table_name, bool $is_temporary = false): array {
// Query emulated 'statistics' table for INDEX_NAME = 'PRIMARY'
$stats_table = $this->get_emulated_info_table_name('statistics', $is_temporary);
$sql = "SELECT column_name
FROM $stats_table
WHERE table_schema = ? AND table_name = ? AND index_name = 'PRIMARY'
ORDER BY seq_in_index ASC";
$params = [$this->emulated_db_name, $table_name];
try {
$stmt = $this->query($sql, $params);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $e) {
// If the statistics table itself doesn't exist (e.g., schema not initialized)
// or other SQL error.
// For robustness, log error and return empty array or re-throw.
// error_log("Error fetching primary key columns: " . $e->getMessage());
return [];
}
}
public function get_table_columns(string $table_name, bool $is_temporary = false): array {
$cols_table = $this->get_emulated_info_table_name('columns', $is_temporary);
$sql = "SELECT column_name
FROM $cols_table
WHERE table_schema = ? AND table_name = ?
ORDER BY ordinal_position ASC";
$params = [$this->emulated_db_name, $table_name];
try {
$stmt = $this->query($sql, $params);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $e) {
// error_log("Error fetching table columns: " . $e->getMessage());
return [];
}
}
public function get_column_key_and_extra_for_show(string $pg_schema_unused, string $pg_table_name, string $mysql_column_name): array {
// This method queries the *emulated* MySQL 'columns' table, which should store
// the COLUMN_KEY and EXTRA fields as they would appear in MySQL's INFORMATION_SCHEMA.COLUMNS.
// $pg_schema_unused is the actual PG schema, but we query based on emulated $this->emulated_db_name.
// Determine if the request is for a temporary table's column.
// This is tricky without more context. Assume non-temporary unless a flag is passed or determined.
// For this method, we'll assume it's querying info about a non-temporary table.
// If info about temporary tables is stored separately, that logic would be needed here.
$is_temporary = $this->temporary_table_exists_in_emulated_schema($pg_table_name); // Check if it's a known temp table.
$cols_emulated_table = $this->get_emulated_info_table_name('columns', $is_temporary);
$sql = "SELECT column_key, extra
FROM $cols_emulated_table
WHERE table_schema = ? AND table_name = ? AND column_name = ?";
$params = [$this->emulated_db_name, $pg_table_name, $mysql_column_name];
$key_info = '';
$extra_info = '';
try {
$stmt = $this->query($sql, $params);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if ($result) {
$key_info = $result['column_key'] ?? '';
$extra_info = $result['extra'] ?? '';
}
} catch (PDOException $e) {
// Log error or handle if the emulated schema table doesn't exist or query fails.
// error_log("Error in get_column_key_and_extra_for_show: " . $e->getMessage());
// Fallback to empty if data cannot be retrieved.
}
// The `COLUMN_KEY` in MySQL's INFORMATION_SCHEMA.COLUMNS can be:
// - 'PRI' if the column is a PRIMARY KEY or part of one.
// - 'UNI' if the column is part of a UNIQUE index (and not part of PK).
// Note: If a column is part of multiple UNIQUE indexes, 'UNI' is shown.
// If it's also PRI, PRI takes precedence.
// - 'MUL' if the column is part of a non-UNIQUE index (and not PRI or UNI).
// It means the column can have multiple occurrences of a given value within the index.
// This is shown if the column is the first column of a non-unique index.
// - '' (empty string) if the column has no index, or is not the first column of any index
// that would qualify it for PRI, UNI, or MUL.
// The `EXTRA` field can contain:
// - 'auto_increment' for AUTO_INCREMENT columns.
// - 'on update CURRENT_TIMESTAMP' for TIMESTAMP or DATETIME columns with this property.
// - 'DEFAULT_GENERATED' if column has an expression default (MySQL 8+).
// - 'VIRTUAL GENERATED', 'STORED GENERATED' for generated columns.
// - Multiple values can be space-separated, e.g., 'auto_increment on update CURRENT_TIMESTAMP'.
// The values should already be correctly stored by `extract_mysql_column_data_from_ast`
// and related DDL recording methods. This method just retrieves them.
return [
'key' => $key_info,
'extra' => $extra_info,
];
}
}
require_once __DIR__ . '/../../tests/bootstrap.php';
$driver = new WP_PostgreSQL_Driver([
'dsn' => 'pgsql:host=127.0.0.1;port=5432;user=postgres;',
'password' => 'secret',
'mysql_database_name' => 'wp_db'
]);
$result = $driver->query('CREATE DATABASE wp_db');
$result = $driver->query('SELECT 1');
var_dump($result);
try {
$result = $driver->query("CREATE TABLE IF NOT EXISTS wp_users (
ID bigint(20) unsigned NOT NULL auto_increment,
user_login varchar(60) NOT NULL default '',
user_pass varchar(64) NOT NULL default '',
user_nicename varchar(50) NOT NULL default '',
user_email varchar(100) NOT NULL default '',
user_url varchar(100) NOT NULL default '',
user_registered datetime NOT NULL default '1970-01-01 00:00:00',
user_activation_key varchar(60) NOT NULL default '',
user_status int(11) NOT NULL default '0',
display_name varchar(250) NOT NULL default '',
spam tinyint(2) NOT NULL default '0',
deleted tinyint(2) NOT NULL default '0',
PRIMARY KEY (ID),
KEY user_login_key (user_login),
KEY user_nicename (user_nicename)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
");
var_dump($result);
} catch (\Exception $e) {
// Fatal error: Uncaught PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "_wp_pgsql_mysql_info_tables_pkey"
}
$result = $driver->query('SHOW TABLES');
print_r($result);
$result = $driver->query('INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_url, user_activation_key, user_status, display_name, spam, deleted) VALUES ("test", "test", "test", "test", "test", "test", "test", "test", "test", "test")');
print_r($result);
$result = $driver->query('SELECT * FROM wp_users');
print_r($result);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment