Skip to content

Conversation

@JanJakes
Copy link
Member

@JanJakes JanJakes commented Jul 3, 2025

This is needed for integration of the new SQLite driver in Studio.

We discovered that Studio doesn't use the default implemented in Playground, as it's using wp-now and booting Playground differently.

As a hotfix, when there is no DB_NAME defined at all, we'll fall back to the same value that Studio is using through an MU plugin with polyfills.

A proper fix will be to make the SQLite driver independent of the DB_NAME constant.

@JanJakes JanJakes changed the title Use default value when DB_NAME constant is not defined Use default value when DB_NAME constant is not defined Jul 3, 2025
@JanJakes JanJakes requested a review from adamziel July 3, 2025 08:45
* the default value of the constant in "wp-config-sample.php" and in Studio.
*/
if ( ! defined( 'DB_NAME' ) ) {
define( 'DB_NAME', 'database_name_here' );
Copy link
Collaborator

@adamziel adamziel Jul 3, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

log a warning maybe? or would that not make sense?

Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@adamziel At the moment, this seems to be a valid use case in Studio that probably shouldn't even generate warnings, I guess.

That said, I think I figured out a way to make the information schema DB_NAME independent, so I think this will be only temporary. The idea is to use temporary views on top of the stored information schema tables. They are naturally per-session and enable injecting any DB_NAME to the affected columns, they are low cost to create (they don't materialize + it can be lazy), and they could also unlock features like row count and auto-increment values.

Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This code is not studio-specific, though, but distributed to all sites where the SQLite plugin is installed. Would that make a warning reasonable?

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@adamziel the issue is not Studio-specific. It could happen on WoA sites, too, if user uses WordPress with SQLite plugin. WoA sites don't have database constants defined in wp-config.php file.

Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Makes sense. I guess it makes sense for any sqlite-only site to skip the database constants.

@adamziel adamziel merged commit e7e411d into develop Jul 3, 2025
12 checks passed
@JanJakes JanJakes mentioned this pull request Oct 14, 2025
JanJakes added a commit that referenced this pull request Oct 15, 2025
This PR replaces
#262. It is
a simpler solution to the same problem:

When a table reference targets an information schema table, we replace
it with a subquery, injecting the configured database name dynamically:

```sql
-- The following query:
SELECT *, t.*, t.table_schema FROM information_schema.tables t

-- Will be translated to:
SELECT *, t.*, t.table_schema FROM (
  SELECT
    `TABLE_CATALOG`,
    IIF(`TABLE_SCHEMA` = 'information_schema', `TABLE_SCHEMA`, 'database_name') AS `TABLE_SCHEMA`,
    `TABLE_NAME`,
    ...
  FROM _wp_sqlite_mysql_information_schema_tables AS tables
) t
```
The same logic will be applied to table references in JOIN clauses as
well.

---

With the new SQLite driver, we keep running into issues with missing and
incorrect database name values
(#197,
#203,
#226,
#260,
#261, and
other issues).

Thinking through this further, I came to the conclusion that to provide
maximum flexibility and portability, it would be best to provide an API
in the shape of:

_"Mount `my-sqlite-file.sqlite` as `my-db-name`."_

Even if we consider adding multi-database support one day, it would
still be great to allow mounting additional SQLite files under dynamic
database names.

I don't see any downsides to doing this, except maybe that we'll have to
choose some database name in some scenarios, such as in database admin
tools. However, in these cases we can use a reasonable default or the
file name.

This is a WIP pull request demonstrating the simplest approach. For it
to be merged, I only need to resolve how the existing database name
values should be treated.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

4 participants