Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:118872
Date: Mon, 24 Oct 2022 01:47:51 +0200
To: internals@lists.php.net
Message-ID: <20221024014751.155ca5ef@platypus>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Subject: SQLite3: implement session extension
From: php@bohwaz.net (BohwaZ)

Hi folks!

2 years ago I implemented some missing APIs in the SQLite3 extension,
and I still want to get SQLite3 support in PHP to get better. Today
I'll be talking about something that can become very useful:

The SQLite3 session extension allows to create changesets from a
database, that is to store a set of changes that have been done, and
either revert them, or apply them to another database.

This is the same as using git or SVN but on a database file.

You can undo or replay a set of changes. This could also be used to
replicate changes to other servers.

You will find more details here:
https://www.sqlite.org/sessionintro.html

I am proposing to add (any help welcome, C is hard for me ;) )
two new classes to the SQLite3 extension if the session extension has
been compiled in SQLite:

* SQLite3Session
* SQLite3Changeset

This also means adding a new 'session' method to SQLite3.

An example of the projected use follows, as well as the proposed stub.

If SQLite has been compiled without support for this extension, then
both classes and the 'session' function won't be compiled either.

That way you will be able to check for feature support by using
class_exists('SQLite3Session'). (if that's not the best course, please
let me know)

As I said, I'm quite slow with C, and I have very little time, so I'm
asking here before proposing a RFC and finally if all goes well I will
be working on the complete implementation during 2023. Unfortunately I
cannot commit to targeting a specific PHP release as I don't know in
advance how much free time I may have.

Any constructive feedback is appreciated :)

== Example use: ==

$db = new SQLite3('db.sqlite');

$session = $db->session('test_table');
/* same as:
  $session = new SQLite3Session($db);
  $session->attach('test_table);
*/

$db->query('INSERT INTO test_table VALUES (42);');

$changeset = session->getChangeset();

file_put_contents('change.set', (string) $changeset);

unset($changeset, $session);

// ...

$changeset = SQLite3Changeset::load(file_get_contents('change.set'));
$operations = [
  SQLite3::INSERT => 'INSERT',
  SQLite3::UPDATE => 'UPDATE',
  SQLite3::DELETE => 'DELETE',
];

foreach ($changeset->iterate() as $change) {
  printf("%s on table '%s':\nOLD: %s\nNEW: %s\nPK: %s\n\n",
    $operations[$change['operation']],
    $change['table'],
    json_encode($change['old'], JSON_PRETTY_PRINT),
    json_encode($change['new'], JSON_PRETTY_PRINT),
    json_encode($change['pk'], JSON_PRETTY_PRINT)
  );
}

$changeset->invert();

$changeset->apply($db);

== Stub: ==

<?php

/** @not-serializable */
class SQLite3Session
{
    public function __construct(SQlite3 $db, string $db_name = 'main'){}

    public function __destruct() {}

    /** @tentative-return-type */
    public function attach(?string $table_name = null): void {}

    /** @tentative-return-type */
    // enabled is true by default when creating the object
    public function enable(bool $enable): void {}

    /** @tentative-return-type */
    public function getChangeset(): SQLite3Changeset {}

    /** @tentative-return-type */
    public function getPatchset(): SQLite3Changeset {}

    /** @tentative-return-type */
    public function diff(string $table_name, string $from_db = 'main'):
    void {}

    /** @tentative-return-type */
    public function isEmpty(): bool {}

    /** @tentative-return-type */
    public function getMemoryUsed(): int {}

    /** @tentative-return-type */
    public function setTableFilter(callable $callback): void {}
}

/** @not-serializable */
class SQLite3Changeset
{
    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_OMIT
     */
    public const OMIT = UNKNOWN;

    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_REPLACE
     */
    public const REPLACE = UNKNOWN;

    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_DATA
     */
    public const DATA = UNKNOWN;

    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_NOTFOUND
     */
    public const NOTFOUND = UNKNOWN;

    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_ABORT
     */
    public const CONFLICT = UNKNOWN;

    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_CONSTRAINT
     */
    public const CONSTRAINT = UNKNOWN;

    /**
     * @var int
     * @cvalue SQLITE_CHANGESET_FOREIGN_KEY
     */
    public const FOREIGN_KEY = UNKNOWN;

    /** @tentative-return-type */
    static public function load(string $changeset): SQLite3Changeset {}

    /** @tentative-return-type */
    public function toString(): string {}

    /** @tentative-return-type */
    public function __toString(): string {}

    /** @tentative-return-type */
    /* Return a non-iterable iterator, each iteration returns an array
    containing:
	'table' => (string), // table name
	'operation' => (int), // operation type
        'old' => (array), // Old column values
        'new' => (array), // New column values
        'pk' => (array), // List of PK columns
    */
    public function iterate(): iterator {}

    /** @tentative-return-type */
    public function apply(SQLite3 $db,
	?callable $table_filter_callback = null,
	?callable $conflict_callback = null): void {}

    /** @tentative-return-type */
    public function concat(SQLite3Changeset $changeset): void {}

    /** @tentative-return-type */
    public function invert(): void {}
}