Ritual/schema.sql at master · sky-cake/Ritual · GitHub

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
CREATE TABLE IF NOT EXISTS `%%BOARD%%` (
    doc_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    media_id INTEGER NOT NULL,
    poster_ip TEXT NOT NULL,
    num INTEGER NOT NULL,
    subnum INTEGER NOT NULL,
    thread_num INTEGER NOT NULL,
    op INTEGER NOT NULL,
    timestamp INTEGER NOT NULL,
    timestamp_expired INTEGER NOT NULL,
    preview_orig TEXT,
    preview_w INTEGER NOT NULL,
    preview_h INTEGER NOT NULL,
    media_filename TEXT,
    media_w INTEGER NOT NULL,
    media_h INTEGER NOT NULL,
    media_size INTEGER NOT NULL,
    media_hash TEXT,
    media_orig TEXT,
    spoiler INTEGER NOT NULL,
    deleted INTEGER NOT NULL,
    capcode TEXT NOT NULL,
    email TEXT,
    name TEXT,
    trip TEXT,
    title TEXT,
    comment TEXT,
    delpass TEXT,
    sticky INTEGER NOT NULL,
    locked INTEGER NOT NULL,
    poster_hash TEXT,
    poster_country TEXT,
    exif TEXT,
    UNIQUE (num, subnum)
);

CREATE TABLE IF NOT EXISTS `%%BOARD%%_images` (
    media_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    media_hash TEXT NOT NULL,
    media TEXT,
    preview_op TEXT,
    preview_reply TEXT,
    total INTEGER NOT NULL,
    banned INTEGER NOT NULL,
    UNIQUE (media_hash)
);

CREATE TABLE IF NOT EXISTS `%%BOARD%%_threads` (
    thread_num INTEGER NOT NULL PRIMARY KEY,
    time_op INTEGER NOT NULL,
    time_last INTEGER NOT NULL,
    time_bump INTEGER NOT NULL,
    time_ghost INTEGER,
    time_ghost_bump INTEGER,
    time_last_modified INTEGER NOT NULL,
    nreplies INTEGER NOT NULL,
    nimages INTEGER NOT NULL,
    sticky INTEGER NOT NULL,
    locked INTEGER NOT NULL,
    UNIQUE (thread_num)
);

CREATE TABLE IF NOT EXISTS `%%BOARD%%_deleted` (
    doc_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    media_id INTEGER NOT NULL,
    poster_ip TEXT NOT NULL,
    num INTEGER NOT NULL,
    subnum INTEGER NOT NULL,
    thread_num INTEGER NOT NULL,
    op INTEGER NOT NULL,
    timestamp INTEGER NOT NULL,
    timestamp_expired INTEGER NOT NULL,
    preview_orig TEXT,
    preview_w INTEGER NOT NULL,
    preview_h INTEGER NOT NULL,
    media_filename TEXT,
    media_w INTEGER NOT NULL,
    media_h INTEGER NOT NULL,
    media_size INTEGER NOT NULL,
    media_hash TEXT,
    media_orig TEXT,
    spoiler INTEGER NOT NULL,
    deleted INTEGER NOT NULL,
    capcode TEXT NOT NULL,
    email TEXT,
    name TEXT,
    trip TEXT,
    title TEXT,
    comment TEXT,
    delpass TEXT,
    sticky INTEGER NOT NULL,
    locked INTEGER NOT NULL,
    poster_hash TEXT,
    poster_country TEXT,
    exif TEXT,
    UNIQUE (num)
);

DROP INDEX IF EXISTS `idx_%%BOARD%%_num`;
CREATE UNIQUE INDEX IF NOT EXISTS idx_%%BOARD%%_num_subnum ON `%%BOARD%%` (num, subnum);

CREATE INDEX IF NOT EXISTS idx_%%BOARD%%_media_id ON `%%BOARD%%` (media_id);
CREATE INDEX IF NOT EXISTS idx_%%BOARD%%_thread_num ON `%%BOARD%%` (thread_num);
CREATE INDEX IF NOT EXISTS idx_%%BOARD%%_timestamp ON `%%BOARD%%` (timestamp);

CREATE UNIQUE INDEX IF NOT EXISTS idx_%%BOARD%%_num ON `%%BOARD%%_images` (media_id);
CREATE INDEX IF NOT EXISTS idx_%%BOARD%%_num ON `%%BOARD%%_images` (media_hash);

CREATE UNIQUE INDEX IF NOT EXISTS idx_%%BOARD%%_num ON `%%BOARD%%_threads` (thread_num);