Skip to content

Resolve column keys when PDO SQLite lacks SQLITE_ENABLE_COLUMN_METADATA #446

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
wojtekn wants to merge 4 commits into from
Closed

Resolve column keys when PDO SQLite lacks SQLITE_ENABLE_COLUMN_METADATA #446

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
2f930cc
Resolve column keys when PDO SQLite lacks SQLITE_ENABLE_COLUMN_METADATA
wojtekn Jul 3, 2026
c70991f
Add coverage and skip expression columns in the column-metadata fallback
wojtekn Jul 3, 2026
983e79e
Gate the table fallback on column-metadata absence and fix alignment
wojtekn Jul 3, 2026
2ef9318
Make the column-metadata fallback test build-independent
wojtekn Jul 3, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
80 changes: 75 additions & 5 deletions packages/mysql-on-sqlite/src/sqlite/class-wp-pdo-mysql-on-sqlite.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -502,6 +502,16 @@ class WP_PDO_MySQL_On_SQLite extends PDO {
*/
private $last_column_meta = array();

/**
* Source table of the last SELECT, if it read from exactly one base table.
*
* Fallback origin table for "get_last_column_meta()" when PDO doesn't report
* one (SQLite built without "SQLITE_ENABLE_COLUMN_METADATA"). Null otherwise.
*
* @var string|null
*/
private $last_result_single_table = null;

/**
* Data for emulating the "FOUND_ROWS()" function.
*
Expand Down Expand Up @@ -1241,9 +1251,21 @@ public function get_last_column_meta(): array {
$name = $meta['name'];
$type = strtoupper( $meta['sqlite:decl_type'] ?? $meta['native_type'] ?? '' );

// PDO only includes the "table" key when SQLite was built with
// "SQLITE_ENABLE_COLUMN_METADATA"; otherwise the origin table is
// unknown and the information schema lookup below (and its column key
// flags) would be skipped. When the key is absent, fall back to the
// last SELECT's single source table. It's only a candidate - each
// column is still confirmed against the information schema, so
// expression columns (e.g. "COUNT(*)") don't inherit it.
$table_is_known = array_key_exists( 'table', $meta );
if ( ! $table_is_known && null !== $this->last_result_single_table ) {
$table = $this->last_result_single_table;
}

// When table is known, we can get data from the information schema.
$column_info = null;
if ( null !== $table ) {
if ( null !== $table && '' !== $table ) {
$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table );
$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
$column_info = $this->execute_sqlite_query(
Expand Down Expand Up @@ -1364,11 +1386,21 @@ public function get_last_column_meta(): array {
$mysqli_charsetnr = 63; // binary
}

// Expose the origin table. When PDO reported it, use that verbatim.
// Otherwise apply the single-table fallback, but only to columns
// confirmed to belong to that table via the information schema
// ("$column_info" is set) - so expression columns keep an empty table.
if ( $table_is_known ) {
$table_name = $meta['table'];
} else {
$table_name = null !== $column_info ? ( $table ?? '' ) : '';
}

$column_meta[] = array(
'native_type' => $native_type,
'pdo_type' => $pdo_type,
'flags' => $flags,
'table' => $meta['table'] ?? '',
'table' => $table_name,
'name' => $meta['name'],
'len' => $len,
'precision' => $precision,
Expand All @@ -1379,7 +1411,7 @@ public function get_last_column_meta(): array {
* We'll add the data here for use cases such as "wpdb::get_col_info()".
*/
'mysqli:orgname' => $meta['name'], // TODO: Use correct original name when alias is used.
'mysqli:orgtable' => $meta['table'] ?? '', // TODO: Use correct original name when table alias is used.
'mysqli:orgtable' => $table_name, // TODO: Use correct original name when table alias is used.
'mysqli:db' => $this->db_name, // TODO: Use correct DB for queries to information schema.
'mysqli:charsetnr' => $mysqli_charsetnr,
'mysqli:flags' => 0, // TODO: We can compute correct MySQL flags.
Expand Down Expand Up @@ -1894,7 +1926,40 @@ private function execute_select_statement( WP_Parser_Node $node ): void {
// Store column meta info. This must be done before fetching data, which
// seems to erase type information for expressions in the SELECT clause.
$this->store_last_column_meta_from_statement( $stmt );
$this->last_result_statement = $stmt;
$this->last_result_statement = $stmt;
$this->last_result_single_table = $this->get_select_single_table_name( $node );
}

/**
* Get the single base table a SELECT reads from, or null when not exactly
* one (joins, subqueries, derived tables, UNIONs). See
* "$last_result_single_table".
*
* @param WP_Parser_Node $node The "selectStatement" AST node.
* @return string|null
*/
private function get_select_single_table_name( WP_Parser_Node $node ): ?string {
// A single-table read has exactly one table reference and no subquery.
$single_tables = $node->get_descendant_nodes( 'singleTable' );
if ( count( $single_tables ) !== 1 ) {
return null;
}
if ( null !== $node->get_first_descendant_node( 'derivedTable' ) ) {
return null;
}

$table_ref = $single_tables[0]->get_first_child_node( 'tableRef' );
if ( null === $table_ref ) {
return null;
}

// Only base tables in the current schema qualify; skip information_schema.
$database = $this->get_database_name( $table_ref );
if ( 'information_schema' === strtolower( $database ) ) {
return null;
}

return $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
}

/**
Expand Down Expand Up @@ -2783,7 +2848,9 @@ private function execute_show_statement( WP_Parser_Node $node ): void {

$sql = $this->get_mysql_create_table_statement( $table_is_temporary, $table_name );

$this->last_column_meta = array(
// Synthetic result columns; no single source table applies.
$this->last_result_single_table = null;
$this->last_column_meta = array(
array(
'native_type' => 'STRING',
'pdo_type' => PDO::PARAM_STR,
Expand Down Expand Up @@ -5670,6 +5737,9 @@ private function translate_update_list( string $table_name, WP_Parser_Node $pare
*/
private function store_last_column_meta_from_statement( PDOStatement $stmt ): void {
$this->last_column_meta = array();

// Clear the fallback; only a single-table SELECT re-sets it.
$this->last_result_single_table = null;
for ( $i = 0; $i < $stmt->columnCount(); $i++ ) {
/*
* Workaround for PHP PDO SQLite bug (#79664) in PHP < 7.3.
Expand Down
106 changes: 106 additions & 0 deletions packages/mysql-on-sqlite/tests/WP_SQLite_Driver_Tests.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -51,6 +51,33 @@ private function assertQueryError( $sql, $error_message ) {
$this->assertSame( $error_message, $exception->getMessage() );
}

/**
* Set a private property of the inner WP_PDO_MySQL_On_SQLite driver.
*
* @param mixed $value
*/
private function setPdoDriverProperty( string $name, $value ): void {
$this->pdoDriverProperty( $name )->setValue( $this->getPdoDriver(), $value );
}

private function getPdoDriver(): WP_PDO_MySQL_On_SQLite {
$prop = new ReflectionProperty( WP_SQLite_Driver::class, 'mysql_on_sqlite_driver' );
// ReflectionProperty::setAccessible() is a no-op since PHP 8.1 and
// deprecated in 8.5; only needed on older versions the suite still tests.
if ( PHP_VERSION_ID < 80100 ) {
$prop->setAccessible( true );
}
return $prop->getValue( $this->engine );
}

private function pdoDriverProperty( string $name ): ReflectionProperty {
$prop = new ReflectionProperty( WP_PDO_MySQL_On_SQLite::class, $name );
if ( PHP_VERSION_ID < 80100 ) {
$prop->setAccessible( true );
}
return $prop;
}

public function testRegexp() {
$this->assertQuery(
"INSERT INTO _options (option_name, option_value) VALUES ('rss_0123456789abcdef0123456789abcdef', '1');"
Expand Down Expand Up @@ -8068,6 +8095,85 @@ public function testColumnInfo(): void {
);
}

/**
* When PHP's SQLite is built without "SQLITE_ENABLE_COLUMN_METADATA", PDO
* omits the column "table", so the driver can't read the origin table from
* it. For a single-table SELECT it should fall back to that table and still
* resolve the column keys - while leaving expression columns untouched.
*
* The stored column metadata is injected directly so the test doesn't depend
* on how a given PHP/SQLite build populates "getColumnMeta()".
*/
public function testColumnInfoWithoutPdoColumnMetadata(): void {
$this->assertQuery(
'CREATE TABLE t (
id INT,
name TEXT,
PRIMARY KEY (id),
UNIQUE KEY (name(64))
)'
);

// "id" and "name" are real columns; "cnt" is an expression column. None
// carry a "table" (as on a build without SQLITE_ENABLE_COLUMN_METADATA).
$this->setColumnMetaForSingleTable(
't',
array(
array(
'name' => 'id',
'sqlite:decl_type' => 'INT',
),
array(
'name' => 'name',
'sqlite:decl_type' => 'TEXT',
),
array(
'name' => 'cnt',
'sqlite:decl_type' => 'INTEGER',
),
)
);
$column_info = $this->engine->get_last_column_meta();

// Real columns get the fallback table and their keys.
$this->assertSame( 't', $column_info[0]['table'] );
$this->assertSame( 't', $column_info[0]['mysqli:orgtable'] );
$this->assertContains( 'primary_key', $column_info[0]['flags'] );
$this->assertContains( 'unique_key', $column_info[1]['flags'] );

// The expression column is not in the table, so it keeps an empty table.
$this->assertSame( '', $column_info[2]['table'] );
$this->assertNotContains( 'primary_key', $column_info[2]['flags'] );

// With no single source table (e.g. a join), the fallback stays off.
$this->setColumnMetaForSingleTable(
null,
array(
array(
'name' => 'id',
'sqlite:decl_type' => 'INT',
),
)
);
$column_info = $this->engine->get_last_column_meta();

$this->assertSame( '', $column_info[0]['table'] );
$this->assertNotContains( 'primary_key', $column_info[0]['flags'] );
}

/**
* Inject stored column metadata (without a "table" key) plus a single-table
* fallback, to drive get_last_column_meta() deterministically.
*
* @param string|null $single_table Fallback table, or null for none.
* @param array $columns Partial column-meta rows; "name" and
* "sqlite:decl_type" are enough.
*/
private function setColumnMetaForSingleTable( ?string $single_table, array $columns ): void {
$this->setPdoDriverProperty( 'last_column_meta', $columns );
$this->setPdoDriverProperty( 'last_result_single_table', $single_table );
}

public function testColumnInfoWithConstraints(): void {
$this->assertQuery(
'CREATE TABLE t (
Expand Down
Loading