WP_SQLite_Driver::query() API

[JanJakes]

Historically, the API of WP_SQLite_Driver::query() is the following:

/**
 * @param string  $query               Full SQL statement string.
 * @param int     $fetch_mode          PDO fetch mode. Default is PDO::FETCH_OBJ.
 * @param array   ...$fetch_mode_args  Additional fetch mode arguments.
 * @return mixed                       Return value, depending on the query type.
 */
publuc function query( string $query, $fetch_mode = PDO::FETCH_OBJ, ...$fetch_mode_args );

The return value is a WPDB-like construct, where DDL queries return true, insert/update/delete queries return the number of affected rows, and read queries return the number of returned rows.

Getting results and other values is then done via $driver->get_query_results(), $driver->get_insert_id(), etc. This part resembles the PDOStatement API and Doctrine DBAL's Result. In a way, the driver is mixing multiple things together.

---

To have a universal WP-independent API, I'd like to modify it to something like this:

publuc function query( string $query, array $params = array() ): <Result>;

The <Result> would be something like PDOStatement or Doctrine DBAL's Result class with methods to fetch data from the query, get the affected row count, and more.

We can return actual PDO Statements from the driver, but since the query translation is not 1:1, we'd need to pass the statement some explicit values in some cases. That means subclassing the PDOStatement class and making some of the values configurable. An alternative is to have a custom Result type, or exposing the fetch and similar methods on the driver itself.