## Summary

> [!NOTE]
> This is an alternative approach to #494. It keeps the same randomized
storage design, but delegates coordination to SQLite's VFS and locking
protocol through a dedicated SQLite database.

This PR adds **randomized storage** for SQLite databases as an
additional layer of protection against direct web access.

- **Randomized paths:** New managed databases are stored in a directory
generated from 128 bits of randomness.
- **Portable discovery:** `db-path.php` records the database location
relative to the managed database root.
- **SQLite-backed locking:** Setup and maintenance are coordinated
through a dedicated SQLite locking database.
- **Database isolation:** Maintenance also acquires an exclusive lock on
an existing database before changing it.
- **Automatic migration:** Existing `.ht.sqlite` and `.ht.sqlite.php`
databases are moved to the randomized layout.
- **Explicit path compatibility:** User-configured database paths and
`:memory:` continue to work unchanged.
- **Retryable setup:** Another request can complete setup after an
interruption.
- **Safer failures:** Filesystem errors do not expose the randomized
database path.

## Managed storage

When no explicit database path is configured, the default layout is:

```text
wp-content/
└── database/
    ├── .htaccess
    ├── index.php
    ├── .ht.sqlite.lock
    ├── db-path.php
    └── .ht.<32-hex-char-random-key>/
        ├── .htaccess
        ├── index.php
        └── .ht.sqlite
```

The `db-path.php` file returns the database location using `__DIR__`, so
copying or moving the complete `database` directory keeps the reference
valid. If setup is interrupted after the path is published, another
request can finish creating the protected directory and database.

## Storage locking
The storage implements a locking mechanism for initialization and
maintenance. Locking uses a dedicated empty SQLite database and a
maintenance file:

1. An exclusive lock is acquired on the locking database at
".ht.sqlite.lock".
2. A maintenance marker file is created at ".ht.sqlite.maintenance":
    - When requests see the file, they use the locking database.
    - Its absence provides a fast path for normal traffic.
3. When the lock is released, the maintenance file is automatically
removed.

## Legacy migration

Migration is serialized across concurrent requests. Before moving a
legacy database, the storage manager checkpoints WAL data, switches to
`DELETE` journal mode, and acquires an exclusive SQLite lock. If the
database remains busy or migration otherwise fails, the original
database file stays in place.

## Why

A SQLite database at a predictable location under the document root may
be served directly when the web server does not honor `.htaccess` or
equivalent denial rules. The randomized directory makes accidental
exposure substantially harder while retaining a stable discovery
mechanism for WordPress and external tools.

This is an additional safeguard, not a replacement for private storage.
Keeping the database outside the document root or configuring the web
server to deny access remains the strongest protection.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **New Features**
* Added managed SQLite storage with randomized and in-memory database
locations.
  * Added support for migrating existing SQLite databases.
* Improved database protection and concurrent-access handling, including
stale-lock recovery.

* **Bug Fixes**
* Improved handling of database initialization failures with clearer
failure responses.
* Added safeguards for invalid database paths and storage-operation
failures.
* Improved cleanup of WordPress database files during test environment
resets.

* **Tests**
* Expanded coverage for initialization, migration, permissions, locking,
concurrency, recovery, and in-memory databases.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->

## Summary

Add **`DB_PATH`** as the primary SQLite database setting for database
connections, schema installation, Site Health, and storage lock
placement. It cannot be combined with `DB_DIR`, `DB_FILE`, `FQDBDIR`, or
`FQDB`.

**Legacy settings remain supported when `DB_PATH` is absent**, but
database paths and storage directories must be absolute. Storage under a
secret randomized path remains the default. The drop-in then defines
`DB_PATH` with the resolved path. Invalid values fail explicitly without
falling back to another database.

`WP_SQLite_Storage` now exposes `with_secret_path( $root )` and
`with_explicit_path( $path )`, with a private constructor. Callers
resolve the configuration before creating storage.

In-memory databases (`:memory:`) require no filesystem storage or
locking.

## Why

A single full-path setting makes the active database easier to configure
and identify. The older path constants are deprecated in PHPDoc while
their behavior remains available for backward compatibility.

Builds on #502.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **New Features**
* Configure SQLite with an absolute `DB_PATH` or `:memory:`. When
`DB_PATH` is not set, SQLite continues to use the default or legacy
database path.
* Legacy database settings are deprecated and cannot be combined with
`DB_PATH`.

* **Bug Fixes**
* Invalid database paths are rejected rather than silently falling back
to a legacy location.
* Site-health reporting now reflects the selected database path and its
size.
  * In-memory databases no longer require filesystem locking.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->

`IF()` currently always selects the false branch because its PHP
callback compares SQLite's integer condition with PHP `true`. This can
silently write incorrect values in queries such as `INSERT ... SELECT
IF(...)`.

**Translate `IF()` to SQLite's native `CASE` expression** and remove the
PHP callback. SQLite then handles numeric truthiness and evaluates only
the selected branch. `CASE` also preserves compatibility with SQLite
3.27; `IIF()` was introduced in 3.32.

Regression coverage checks numeric and string conditions, NULLs, nested
calls, large integers, branch evaluation, and `INSERT ... SELECT`.
Results were compared with MySQL 8.4, and the regression tests also pass
on PHP 7.2 with SQLite 3.27.

Fixes #517.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->

## Summary by CodeRabbit

* **New Features**
* SQL queries using MySQL’s `IF(condition, true_value, false_value)`
function are now supported when running on SQLite, including nested
expressions and queries that insert selected results.
* **Bug Fixes**
* `IF` now evaluates conditions using SQLite-compatible truthiness and
returns the matching value, including for `NULL` conditions. Only the
selected result branch is evaluated.

<!-- end of auto-generated comment: release notes by coderabbit.ai -->

## Summary

**Make `TRADITIONAL` enable its component SQL modes.** Previously, `SET
sql_mode = 'TRADITIONAL'` stored only the composite flag, leaving the
driver's existing strict and zero-date checks disabled. It now expands
the mode for both named and numeric assignments and retains
`TRADITIONAL` in `@@sql_mode`.

The expansion follows the emulated MySQL version, including
`NO_AUTO_CREATE_USER` before its removal in MySQL 8.0.11. Regression
coverage checks version differences, canonical mode ordering,
combination with `ANSI`, reassignment, clearing modes, and rejection of
invalid dates and missing required values.

This PR does not implement the deprecated MySQL 5.7 composite modes,
such as `ORACLE`, `MSSQL`, and `POSTGRESQL`.

## Why

Applications should get the same existing checks whether they select
`TRADITIONAL` or list its component modes individually. This change uses
the driver's current implementations of those modes; their existing
emulation limits still apply.

[MySQL documents `TRADITIONAL` as a combination
mode.](https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sql-mode-combo)


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->

## Summary by CodeRabbit

* **Bug Fixes**
* The `TRADITIONAL` SQL mode now applies its expected strict validation
rules, including rejecting invalid or zero dates and missing or `NULL`
values in `NOT NULL` columns.
* `TRADITIONAL` mode assignments now expand consistently across
supported assignment formats, including combined mode lists and numeric
bitmaps.
* Mode expansion respects MySQL version differences, including whether
`NO_AUTO_CREATE_USER` is available.

<!-- end of auto-generated comment: release notes by coderabbit.ai -->

## Summary

With the SQLite driver, `$wpdb->collate` is empty, so
`$wpdb->get_charset_collate()` returns `DEFAULT CHARACTER SET utf8mb4`
without a `COLLATE` clause. Tables created with it (for example, via
`dbDelta()`) therefore use the emulated MySQL 8 default,
**`utf8mb4_0900_ai_ci`**. MariaDB doesn't support this collation, so
these tables fail to import there, including when pushing a WordPress
Studio site to WordPress.com.

This PR makes `$wpdb` use the same charset and collation as WordPress on
MySQL:

- **`$wpdb->charset` is always `utf8mb4`**, matching the emulated
connection. `DB_CHARSET` is ignored.
- **`$wpdb->collate` is `utf8mb4_unicode_520_ci`**, unless `DB_COLLATE`
specifies another UTF-8 collation. For example, `utf8_bin` becomes
`utf8mb4_bin`, as in `wpdb::determine_charset()`.
- **`utf8mb4_unicode_520_ci` is listed** in
`INFORMATION_SCHEMA.COLLATIONS` and `SHOW COLLATION`.

## Why

Previously, `WP_SQLite_DB` initialized the charset only before
connecting. At that point, `wpdb::determine_charset()` leaves the values
unchanged, so the collation stayed empty. The values are now resolved
**without a connection**, since SQLite always uses UTF-8 and needs no
server capability checks. This also makes them available while
connecting, when the driver may reconstruct WordPress tables with
`wp_get_db_schema()`.

Calling `init_charset()` again after connecting, as `wpdb::db_connect()`
does, wouldn't be enough: WordPress Studio may define `DB_CHARSET` in a
mu-plugin that loads later.

This PR leaves the following unchanged:

- **Tables created without a collation** (for example, a plain `CREATE
TABLE`) still use `utf8mb4_0900_ai_ci`, the emulated MySQL 8 default.
- **Existing tables** keep their recorded collation.
- **Column collations** in the information schema remain
`utf8mb4_0900_ai_ci`. Exports use only the table collation.

Broader charset and collation support is tracked in #456.

Alternative to #511.
Related to Automattic/studio#4737.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->

## Summary by CodeRabbit

* **Bug Fixes**
* Improved compatibility with WordPress charset and collation settings
by consistently resolving to `utf8mb4` and the appropriate collation,
including `utf8mb4_unicode_520_ci`.
* Added support for the `utf8mb4_unicode_520_ci` collation in collation
metadata.

<!-- end of auto-generated comment: release notes by coderabbit.ai -->

Document **randomized database storage and `DB_PATH` configuration** in
the plugin FAQ and a new plugin README, including protection from public
web access.

The new README also covers usage, requirements, and development. Link it
from the root README and exclude it from plugin release ZIPs.

Related to #502 and #512.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **Documentation**
* Added setup, configuration, security, development, and licensing
guidance for the SQLite integration plugin.
* Expanded the FAQ with details on default and custom database paths,
changing database locations, and temporary in-memory databases.
* Clarified storage requirements and recommended keeping database files
in a protected location outside the web root.
  * Updated the component list to link directly to the plugin package.
* **Chores**
  * Excluded the package README from the plugin release archive.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->

## Release `3.1.0`

Prepares the version numbers and release notes for `3.1.0`.

**Changelog draft:**

---

**SQLite Database Integration 3.1 is here! 🎉**

This release improves **database storage and configuration** and fixes
several MySQL compatibility issues.

## What's new

Version 3.1 improves how WordPress sites store, locate, and protect
their SQLite databases. It also fixes SQL behavior and export
compatibility:

- **Randomized database paths:** The default database location is now a
randomized directory under `wp-content/database/`, recorded in
`wp-content/database/db-path.php`.
([#502](#502))
- **`DB_PATH`:** Configure the database with one full-path constant,
also available at runtime for integrations.
([#512](#512))
- **SQL compatibility:** Fix `IF()` condition evaluation and make
`TRADITIONAL` enable its component SQL modes.
([#518](#518),
[#509](#509))
- **WordPress table collations:** Default to `utf8mb4_unicode_520_ci`
for new tables created with WordPress's charset settings, improving
exports to MariaDB. Existing tables keep their recorded collation.
([#514](#514))
- **Documentation:** A new plugin README and expanded FAQ explain
database storage and secure configuration.
([#520](#520))

For more information about database paths and secure configuration, read
the [database storage
guide](https://github.com/WordPress/sqlite-database-integration/blob/trunk/packages/plugin-sqlite-database-integration/README.md#database-storage).

## Upgrading to 3.1

Upgrading an existing SQLite site is straightforward:

1. **Back up** your SQLite database.
2. **Update the plugin** to version 3.1.

Existing `.ht.sqlite` and `.ht.sqlite.php` databases move to the
randomized layout automatically unless a database file path is
explicitly configured.

To properly **secure the database**, set `DB_PATH` in `wp-config.php` to
an absolute file path outside the web root that your web server does not
expose. Its directory must be writable by PHP. Changing `DB_PATH` does
not move an existing database.

## Breaking changes

Review these changes if you use custom database settings or
integrations:

- **Database paths:** Default database files now move to randomized
paths under `wp-content/database/`. Explicitly configured file paths
stay unchanged. Integrations, including backup and migration tools, must
read `DB_PATH` after WordPress loads instead of assuming a fixed
filename.
- **Legacy constants:** `DB_DIR` and `DB_FILE` are now deprecated. They
and the previously deprecated `FQDB` and `FQDBDIR` remain supported, but
`DB_PATH` takes precedence. Conflicting values trigger warnings.
- **Absolute paths:** Relative database file and directory paths are now
rejected. `:memory:` remains available for in-memory databases.

## Thank you

Thank you to everyone who contributed, tested, and helped update
integrations.

**Changes since 3.0.2:**
[`v3.0.2...v3.1.0`](v3.0.2...v3.1.0)

---

**PR comparison:**
[`v3.0.2...release/v3.1.0`](v3.0.2...release/v3.1.0)

## Next steps

1. **Review** the release changes and changelog draft.
2. **Merge** this pull request to complete the release.

Merging will automatically build the plugin ZIP, create a [GitHub
release](https://github.com/WordPress/sqlite-database-integration/releases),
and deploy to
[WordPress.org](https://wordpress.org/plugins/sqlite-database-integration/).