Database schemas

An addon that stores data in Identica's database declares its tables as a DatabaseSchema and installs it through the SchemaInstaller, both in Identica's database API. Identica creates the tables on a new installation, runs the addon's migrations on an existing one, and records how far each schema has come in the strata_history table. Proxies that share a database install a schema once: the first one takes a lock and the others wait for it.

Every owner of tables has its own schema, called a stream:

DependenciesCopy Anchor Link

The database contracts are in me.whereareiam.identica:database-api, next to me.whereareiam.identica:api. Tables are declared with Dialectica annotations, rows are read and written through Dialectica's EntityAccess, and migrations are Strata migrations. Identica provides all of them at runtime, so compile against them:

dependencies {
    compileOnly("me.whereareiam.identica:api:<version>")
    compileOnly("me.whereareiam.identica:database-api:<version>")
    compileOnly("me.whereareiam:dialectica:4.0.0")
    compileOnly("me.whereareiam:dialectica-adapter-jdbi:4.0.0")
    compileOnly("me.whereareiam:strata-adapter-database:3.1.1")
    compileOnly("me.whereareiam:strata-adapter-database-dialectica:3.1.1")
}

Identica loads Dialectica and Strata under their own package names and shares them with its addons, so the addon's plain jar uses them as they are: it neither bundles nor relocates them. The addon needs no Jdbi of its own; Identica keeps its Jdbi inside the plugin.

Declaring the tablesCopy Anchor Link

Each table is an entity class with Dialectica annotations. A table that belongs to an account references Identica's account table by name, through AccountTable, so that its rows go when the account goes:

import me.whereareiam.dialectica.entity.annotation.Id;
import me.whereareiam.dialectica.entity.annotation.Index;
import me.whereareiam.dialectica.entity.annotation.Length;
import me.whereareiam.dialectica.entity.annotation.Nullable;
import me.whereareiam.dialectica.entity.annotation.References;
import me.whereareiam.dialectica.entity.annotation.Table;
import me.whereareiam.dialectica.schema.type.ReferentialAction;
import me.whereareiam.identica.database.schema.AccountTable;

@Table("example_notes")
@Index(name = "example_notes_created_at", columns = "createdAt")
public record NoteEntity(
        @Id @Length(64) String noteId,
        @References(table = AccountTable.NAME, column = AccountTable.KEY,
                onDelete = ReferentialAction.CASCADE) UUID uniqueId,
        String text,
        @Nullable Long createdAt
) {}

Prefix table and index names with the addon's name. The Dialectica README lists every annotation and the column type each Java type gets on each database.

Declaring the schemaCopy Anchor Link

The schema names its stream, lists its tables in their newest form, and lists the streams whose tables it references:

import me.whereareiam.dialectica.Dialectica;
import me.whereareiam.dialectica.schema.Schema;
import me.whereareiam.identica.database.schema.DatabaseSchema;

public final class NotesSchema implements DatabaseSchema {
    @Override
    public @NotNull String stream() {
        return "example/notes";
    }

    @Override
    public @NotNull Schema schema() {
        return Schema.of(Dialectica.entity(NoteEntity.class).table());
    }

    @Override
    public @NotNull Set<String> requires() {
        return Set.of(ACCOUNT_STREAM);
    }
}

A stream name is made of lowercase segments separated by slashes and never changes once released.

Installing itCopy Anchor Link

Install the schema when the addon loads, before it reads or writes its tables:

identica.findService(SchemaInstaller.class).orElseThrow().install(new NotesSchema());

A provider binds its schemas in its own module instead, and Identica installs them when it loads the provider:

Multibinder.newSetBinder(binder(), DatabaseSchema.class).addBinding().to(NotesSchema.class);

Installing refuses a schema whose required streams are not installed yet, and installing the same schema again changes nothing. A schema that cannot be brought up to date throws Strata's MigrationException; the addon must not continue on its tables then.

Reading and writing rowsCopy Anchor Link

The DatabaseService hands out Dialectica's EntityAccess for an entity. It works on Identica's connection and speaks the database's dialect, so the columns are stored the way the schema declares them:

DatabaseService database = identica.findService(DatabaseService.class).orElseThrow();
EntityAccess<NoteEntity> notes = database.access(NoteEntity.class);

notes.upsert(new NoteEntity("first", uniqueId, "Remember the milk", null));
Optional<NoteEntity> note = notes.find("first");

EntityAccess inserts, upserts, updates, finds and deletes rows by their primary key. The Dialectica README describes each operation.

Changing the tables laterCopy Anchor Link

A released schema changes through migrations. Add each change as the next numbered migration, and change the entity so that schema() keeps describing the newest form, which is what a new installation gets:

@Override
public @NotNull List<Migration<DatabaseContext>> migrations() {
    return List.of(
            SchemaMigration.of(1, "note-title",
                    new AddColumn("example_notes", ColumnDefinition.builder("title", ColumnType.varchar(64)).build())),
            new Migration<>(2, "fill-titles", database -> database.update(
                    "UPDATE example_notes SET title = 'Untitled' WHERE title IS NULL"))
    );
}

• Versions start at 1, have no gaps, and are never renumbered or changed once released.
• SchemaMigration applies Dialectica schema changes, which skip what the database already has; a data change is a plain Migration.
• A migration only writes to the addon's own tables.

Strata runs the migrations an installation has not seen yet, in order, and records each one. When a stream runs for the first time against a database that already has its tables, for example because the addon created them before it used migrations, its baseline says where that database stands. The default treats every such database as a new installation; override it to return the version the existing tables have:

@Override
public @NotNull OptionalInt baseline(@NotNull DatabaseContext database, int latest) throws SQLException {
    return database.tableExists("example_notes") ? OptionalInt.of(0) : OptionalInt.empty();
}

OptionalInt.empty() is a new installation, which gets schema(); 0 runs every migration; any other version runs the migrations after it.

Where SQL belongsCopy Anchor Link

• The structure of the tables, their columns and indexes, is always declared with Dialectica, whether it is created, altered or dropped: the entities describe the current form, and a migration changes the structure through Dialectica schema changes.
• At runtime, the addon reads and writes its rows through Dialectica's EntityAccess. Hand-written queries are not part of the addon API: Identica's Jdbi stays inside the plugin, where Identica's own modules use it for what EntityAccess does not cover.
• A one-time data step inside a migration, which moves or transforms the rows of a past layout, may be plain SQL. A migration describes one fixed step from an exact past layout and never changes afterwards, so its SQL does not follow the entities.

Such a data step:

• uses standard SQL that runs unchanged on every database Identica supports;
• is safe to run again, for example as INSERT … SELECT … WHERE NOT EXISTS, so that a migration that stopped halfway finishes on the next start;
• is covered by tests that upgrade the past layout on every supported database, run the migration again, and compare the result with a new installation.

The username-layout migration of Identica's AccountSchema works this way: Dialectica creates the username tables, two INSERT … SELECT … WHERE NOT EXISTS statements move the rows of Identica 2.2.1 into them, and Dialectica drops the old table and column.

A value that SQL does not compute alike on every database is computed in Java: the data step reads the rows, binds and reads each value through the Dialect of its connection (Dialect.of(connection.getMetaData())), and writes the result. The username-table migration of AccountSchema works this way, because SQLite's LOWER changes only ASCII letters: Dialectica creates identica_account_usernames with the unique index on username_key, the step writes the username of each account there with its source and its key in lower case, which only the oldest account of a username holds, and Dialectica drops the old username column and the username state table. The step writes every username anew while the old column exists, so it too finishes on the next start after stopping halfway.