IDEA.md

Idea

Some idea to explore to improve Synql.

Unify logs

For now, Synql uses two tables to register modifications: _synql_log and _synql_fklog. Synql uses a view to unify its two logs into a single log ()_synql_unified_log).

These two tables could be merged into a single one:

CREATE TABLE _synql_unified_log(
    ...
    field integer NOT NULL,
    val any DEFAULT NULL,
    foreign_row_ts integer DEFAULT NULL,
    foreign_row_peer integer DEFAULT NULL,
    ...
)

val could always be set to NULL for a field that refers to a foreign key. foreign_row_ts, foreign_row_peer could always be set to NULL for fields that don't refer to foreign keys.

Primary key instead of rowid

For now Synql relies on the presence of an implicit column named rowid. This column is the real primary key of a SQLite table.

We could avoid this assumption and use the column marked as primary key. We can still fallback to rowid if no column is marked as primary key.

Pros:

• This makes the implementation more compatible with other database engines.
• This allows supporting WITHOUT ROWID SQLite tables
• This avoids some fragility (rowid vacuum, etc.)

Cons:

• This could make the code more complex.

Remove clock

Remove ts in _synql_local and only use _synql_context.

A new timestamp could be the max value between the max timestamps of the context + 1 and the current unix timestamp. The computation of the maximum of the context could be cheap thanks to https://www.sqlite.org/optoverview.html#minmax.

Pros:

• Reduce redundancy
• Remove a trigger

Cons:

• This could be hard to use during a merge?