jap_web_ng/dbschema.sql

create table custom_fields
(
    id         integer
        primary key,
    name       text not null,
    identifier text not null
);

create table decks
(
    id       INTEGER
        primary key autoincrement,
    name     TEXT not null,
    forward  TEXT not null,
    backward TEXT not null,
    filter   TEXT not null
);

create table settings
(
    id    text
        primary key,
    value text
);

create table word_groups
(
    id   INTEGER
        primary key autoincrement,
    name TEXT not null
);

create table words
(
    id       INTEGER
        primary key autoincrement,
    key      TEXT              not null,
    value    TEXT              not null,
    tags     TEXT              not null,
    more     TEXT,
    group_id integer default 1 not null
        constraint words_word_group_id_fk
            references word_groups
            on update cascade on delete cascade
);

create table history
(
    id        INTEGER
        primary key autoincrement,
    deck_id   INTEGER not null
        references decks
            on delete cascade,
    word_id   INTEGER not null
        references words
            on delete cascade,
    timestamp INTEGER not null,
    answer    INTEGER not null,
    before    INTEGER not null,
    after     INTEGER not null,
    fsrs      INTEGER not null
);

create table states
(
    id            INTEGER
        primary key autoincrement,
    word_id       INTEGER           not null
        references words
            on delete cascade,
    deck_id       INTEGER
        references decks
            on delete cascade,
    score         INTEGER default 1 not null,
    last_opened   integer           not null,
    fsrs_opened   integer           not null,
    fsrs_memory   integer           not null,
    fsrs_open_day integer           not null
);

create table exams
(
    id        INTEGER
        primary key autoincrement,
    timestamp INTEGER,
    deck_id   INTEGER not null
        references decks
            on delete cascade
);

create table exam_entries
(
    id      INTEGER
        primary key autoincrement,
    exam_id INTEGER not null
        references exams
            on delete cascade,
    word_id INTEGER not null
        references words
            on delete cascade,
    status  INTEGER default null
);