06 Data Model

Data Model

Plotwise Writer Projects are self-contained SQLite3 databases.

ER Diagram / Schemas

Here's the schema definition which is stored as a resource in the Plotwise Writer executable.

CREATE TABLE metadata
(
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    "key" TEXT,
    value TEXT
);

CREATE TABLE folders
(
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id  INTEGER REFERENCES folders (id) ON DELETE CASCADE,
    name       TEXT                NOT NULL,
    sort_index INTEGER DEFAULT 0,
    flags      INTEGER DEFAULT (0) NOT NULL
);

CREATE TABLE files
(
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    folder_id  INTEGER,
    name       TEXT NOT NULL,
    sort_index INTEGER  DEFAULT (0),
    updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT FK_files_folders FOREIGN KEY (folder_id) REFERENCES folders (id) ON DELETE CASCADE
);

CREATE TABLE content
(
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    file_id      INTEGER NOT NULL,
    content_md   TEXT    NOT NULL,
    updated_at   DATETIME DEFAULT (CURRENT_TIMESTAMP),
    is_autosave  INTEGER NOT NULL,
    content_hash TEXT    NOT NULL,
    CONSTRAINT FK_autosave_files FOREIGN KEY (file_id) REFERENCES files (id) ON DELETE CASCADE
);

CREATE TABLE tags
(
    id    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    label TEXT    NOT NULL,
    color TEXT    NOT NULL
);

CREATE TABLE _version
(
    id      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    version TEXT    NOT NULL,
    date    DATETIME DEFAULT (CURRENT_TIMESTAMP)
)

Migrations

Migrations are run every time a project is opened or created. This should bring the project schema up to date automatically. Each migration is logged in the _version table so that each one is only run once.

Version Mismatches

It's possible that someone could open a project file using an older version of the software. If the database version is higher than the current software version, a warning should be displayed and the file opened in read only mode. An effort should be made to display the project data, but there should be no guarantees.