database.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE article (
    article_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    slug VARCHAR(200) NOT NULL UNIQUE,
    published DATETIME NOT NULL,
    hidden TINYINT UNSIGNED NOT NULL DEFAULT 0,
    title TEXT NOT NULL,
    summary_html LONGTEXT NOT NULL,
    full_html LONGTEXT NOT NULL,
    full_plain LONGTEXT NOT NULL,
    FULLTEXT(full_plain),
    FULLTEXT(title)
);

CREATE INDEX by_slug ON article (slug);
CREATE INDEX by_publish_date ON article (published);

CREATE TABLE article_tag (
    article_id INT UNSIGNED NOT NULL REFERENCES article (article_id) ON UPDATE CASCADE ON DELETE CASCADE,
    tag VARCHAR(200) NOT NULL,
    PRIMARY KEY(article_id, tag),
    CONSTRAINT article_fk FOREIGN KEY (article_id) REFERENCES article (article_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX by_tag ON article_tag (tag);