schema.sql

CREATE TABLE title (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    kanji VARCHAR(50) NOT NULL,
    hiragana VARCHAR(50) NOT NULL,
    romanji VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (kanji)
);

CREATE TABLE book (
    id INT UNSIGNED NOT NULL,
    released CHAR(6) NOT NULL,
    classification VARCHAR(50) NOT NULL,
    title_id INT UNSIGNED NOT NULL,
    original_id VARCHAR(20) NOT NULL,
    published VARCHAR(10) NOT NULL,
    published_ad VARCHAR(10) NOT NULL,
    nr_books INT UNSIGNED,
    pages_per_scan TINYINT UNSIGNED NOT NULL,
    aspect VARCHAR(20) NOT NULL,
    nr_scans INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (title_id) REFERENCES title (id),
    INDEX ititle_id (title_id)
);

CREATE TABLE page (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    book INT UNSIGNED NOT NULL,
    page INT UNSIGNED NOT NULL,
    lr TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (book, page, lr),
    FOREIGN KEY (book) REFERENCES book (id)
);

CREATE TABLE keypoint (
    page_id INT UNSIGNED NOT NULL,
    feature INT UNSIGNED NOT NULL,
    x FLOAT UNSIGNED NOT NULL,
    y FLOAT UNSIGNED NOT NULL,
    size FLOAT UNSIGNED NOT NULL,
    angle FLOAT UNSIGNED NOT NULL,
    response FLOAT UNSIGNED NOT NULL,
    octave TINYINT UNSIGNED NOT NULL,
    class_id TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (page_id, feature),
    FOREIGN KEY (page_id) REFERENCES page (id)
);

CREATE TABLE descriptor (
    page_id  INT UNSIGNED NOT NULL,
    feature  INT UNSIGNED NOT NULL,
    `0`  TINYINT UNSIGNED NOT NULL,
    `1`  TINYINT UNSIGNED NOT NULL,
    `2`  TINYINT UNSIGNED NOT NULL,
    `3`  TINYINT UNSIGNED NOT NULL,
    `4`  TINYINT UNSIGNED NOT NULL,
    `5`  TINYINT UNSIGNED NOT NULL,
    `6`  TINYINT UNSIGNED NOT NULL,
    `7`  TINYINT UNSIGNED NOT NULL,
    `8`  TINYINT UNSIGNED NOT NULL,
    `9`  TINYINT UNSIGNED NOT NULL,
    `10` TINYINT UNSIGNED NOT NULL,
    `11` TINYINT UNSIGNED NOT NULL,
    `12` TINYINT UNSIGNED NOT NULL,
    `13` TINYINT UNSIGNED NOT NULL,
    `14` TINYINT UNSIGNED NOT NULL,
    `15` TINYINT UNSIGNED NOT NULL,
    `16` TINYINT UNSIGNED NOT NULL,
    `17` TINYINT UNSIGNED NOT NULL,
    `18` TINYINT UNSIGNED NOT NULL,
    `19` TINYINT UNSIGNED NOT NULL,
    `20` TINYINT UNSIGNED NOT NULL,
    `21` TINYINT UNSIGNED NOT NULL,
    `22` TINYINT UNSIGNED NOT NULL,
    `23` TINYINT UNSIGNED NOT NULL,
    `24` TINYINT UNSIGNED NOT NULL,
    `25` TINYINT UNSIGNED NOT NULL,
    `26` TINYINT UNSIGNED NOT NULL,
    `27` TINYINT UNSIGNED NOT NULL,
    `28` TINYINT UNSIGNED NOT NULL,
    `29` TINYINT UNSIGNED NOT NULL,
    `30` TINYINT UNSIGNED NOT NULL,
    `31` TINYINT UNSIGNED NOT NULL,
    `32` TINYINT UNSIGNED NOT NULL,
    `33` TINYINT UNSIGNED NOT NULL,
    `34` TINYINT UNSIGNED NOT NULL,
    `35` TINYINT UNSIGNED NOT NULL,
    `36` TINYINT UNSIGNED NOT NULL,
    `37` TINYINT UNSIGNED NOT NULL,
    `38` TINYINT UNSIGNED NOT NULL,
    `39` TINYINT UNSIGNED NOT NULL,
    `40` TINYINT UNSIGNED NOT NULL,
    `41` TINYINT UNSIGNED NOT NULL,
    `42` TINYINT UNSIGNED NOT NULL,
    `43` TINYINT UNSIGNED NOT NULL,
    `44` TINYINT UNSIGNED NOT NULL,
    `45` TINYINT UNSIGNED NOT NULL,
    `46` TINYINT UNSIGNED NOT NULL,
    `47` TINYINT UNSIGNED NOT NULL,
    `48` TINYINT UNSIGNED NOT NULL,
    `49` TINYINT UNSIGNED NOT NULL,
    `50` TINYINT UNSIGNED NOT NULL,
    `51` TINYINT UNSIGNED NOT NULL,
    `52` TINYINT UNSIGNED NOT NULL,
    `53` TINYINT UNSIGNED NOT NULL,
    `54` TINYINT UNSIGNED NOT NULL,
    `55` TINYINT UNSIGNED NOT NULL,
    `56` TINYINT UNSIGNED NOT NULL,
    `57` TINYINT UNSIGNED NOT NULL,
    `58` TINYINT UNSIGNED NOT NULL,
    `59` TINYINT UNSIGNED NOT NULL,
    `60` TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (page_id, feature),
    FOREIGN KEY (page_id) REFERENCES page (id)
);

CREATE TABLE fmatch (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    src_page_id INT UNSIGNED NOT NULL,
    src_feature INT UNSIGNED NOT NULL,
    dst_page_id INT UNSIGNED NOT NULL,
    dst_feature INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (src_page_id, src_feature, dst_page_id, dst_feature),
    FOREIGN KEY (src_page_id, src_feature) REFERENCES keypoint (page_id, feature),
    FOREIGN KEY (dst_page_id, dst_feature) REFERENCES keypoint (page_id, feature),
    INDEX isrc_page_id (src_page_id),
    INDEX idst_page_id (dst_page_id)
);