1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

CREATE TABLE tag (
        tag         TEXT NOT NULL PRIMARY KEY UNIQUE,
        description TEXT,
        type        INTEGER NOT NULL
);

CREATE TABLE post (
        id             INTEGER NOT NULL PRIMARY KEY UNIQUE,
        local_file     TEXT,
        local_thumb    TEXT,
        width          INTEGER NOT NULL,
        height         INTEGER NOT NULL,

        tags_character TEXT DEFAULT '',
        tags_copyright TEXT DEFAULT '',
        tags_artist    TEXT DEFAULT '',
        tags_general   TEXT DEFAULT '',
        tags_meta      TEXT DEFAULT '',
        tags_unknown   TEXT DEFAULT '',

        download_date  TIMESTAMP DEFAULT NULL,
        upload_date    TIMESTAMP DEFAULT NULL,
        view_date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        hash           TEXT NOT NULL,
        image_url      TEXT NOT NULL,
        web_url        TEXT NOT NULL,
        source_url     TEXT,

        -- Id in the booru
        booru_id  TEXT NOT NULL,
        -- domain of the booru
        booru     TEXT NOT NULL
);

--CREATE INDEX tag_booru_id ON post (booru, booru_id);

CREATE TABLE post_tags (
       post_id INTEGER NOT NULL,
       tag TEXT NOT NULL,
       PRIMARY KEY (post_id, tag)
);

-- CREATE VIEW post_with_tags (p_id)
--        AS
--        SELECT id, local_file, local_thumb, width, height, download_date, upload_date, view_date, url, hash, source_url, booru_id, booru, post_tags.tag, type
--        FROM post
--             JOIN post_tags ON post.id = p_id
--             JOIN tag ON tag.tag = post_tags.tag
--        WHERE post.post_id = p_id;

-- CREATE VIEW posts
--        AS
--        SELECT id, local_file, local_thumb, width, height, download_date, upload_date, view_date, url, hash, source_url, booru_id, booru, post_tags.tag, tag.type
--        FROM post
--             LEFT JOIN post_tags ON post.id = post_tags.post_id
--             LEFT JOIN tag ON tag.tag = post_tags.tag;

CREATE TABLE history_search (
       query   TEXT,
       date    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);