Early work on the schema; should be kept up to date with new features in extraction pipeline and corpus selection.

[borh]

-- 作るの逆順でないと参照制約でエラーが出る
DROP TABLE IF EXISTS collocation;
DROP TABLE IF EXISTS sentence_word;
DROP TABLE IF EXISTS word;
DROP TABLE IF EXISTS lemma;
DROP TABLE IF EXISTS sentence;
DROP TABLE IF EXISTS source;

CREATE TABLE IF NOT EXISTS source (
    id INTEGER PRIMARY KEY,
    year INTEGER NOT NULL,
    title TEXT NOT NULL,
    author TEXT,
    publisher TEXT,
    corpus TEXT
);

CREATE TABLE IF NOT EXISTS sentence (
    id INTEGER PRIMARY KEY,
    text TEXT NOT NULL,
    source_id INTEGER NOT NULL REFERENCES source(id)
);

CREATE TABLE IF NOT EXISTS lemma (
    id INTEGER PRIMARY KEY,
    string TEXT NOT NULL,
    pos TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS word (
    id INTEGER PRIMARY KEY,
    string TEXT NOT NULL,
    pron TEXT NOT NULL,
    inf TEXT, -- inflection form
    dep TEXT NOT NULL, -- UD dependency type
    lemma_id INTEGER NOT NULL REFERENCES lemma(id)
);

CREATE TABLE sentence_word (
    id INTEGER PRIMARY KEY,
    sentence_id INTEGER NOT NULL REFERENCES sentence(id),
    word_id INTEGER NOT NULL REFERENCES word(id),
    begin INTEGER NOT NULL,
    end INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS collocation (
    word_1_sw_id INTEGER NOT NULL REFERENCES sentence_word(id),
    particle_sw_id INTEGER NOT NULL REFERENCES sentence_word(id),
    word_2_sw_id INTEGER NOT NULL REFERENCES sentence_word(id)
);

[borh]

Note that for loading a corpus, in addition to the following fields (and excepting id):

CREATE TABLE IF NOT EXISTS source (
    id INTEGER PRIMARY KEY,
    year INTEGER NOT NULL,
    title TEXT NOT NULL,
    author TEXT,
    publisher TEXT,
    corpus TEXT
);

we also need a way to load in the sentences from a file, so adding a path column is also needed.

Sample CSV file headers:

year,title,author,publisher,corpus,path