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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
|
package main
import (
"database/sql"
"errors"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
type Entry struct {
ID int
Message Message
Status int
}
type Stats struct {
Users int
Guilds int
Channels int
URLs int
}
func initDB(path string) *sql.DB {
if _, err := os.Stat(path); errors.Is(err, os.ErrNotExist) {
file, err := os.Create(path)
if err != nil {
log.Fatal(err)
}
file.Close()
db, _ := sql.Open("sqlite3", path)
initTables(db)
return db
} else {
db, err := sql.Open("sqlite3", path)
if err != nil {
log.Fatal(err)
}
return db
}
}
func initTables(db *sql.DB) {
q := `
CREATE TABLE IF NOT EXISTS users (
id integer NOT NULL PRIMARY KEY,
user_id VARCHAR(18)
);
CREATE UNIQUE INDEX idx_users_user_id ON users(user_id);
CREATE TABLE IF NOT EXISTS guilds (
id integer NOT NULL PRIMARY KEY,
guild_id VARCHAR(18)
);
CREATE UNIQUE INDEX idx_guilds_guild_id ON guilds(guild_id);
CREATE TABLE IF NOT EXISTS channels (
id integer NOT NULL PRIMARY KEY,
channel_id VARCHAR(18)
);
CREATE UNIQUE INDEX idx_channels_channel_id ON channels(channel_id);
CREATE TABLE IF NOT EXISTS urls (
id integer NOT NULL PRIMARY KEY,
url VARCHAR(500) NOT NULL,
user_string_id INTEGER NOT NULL,
guild_string_id INTEGER NOT NULL,
channel_string_id INTEGER NOT NULL,
status_code INTEGER NOT NULL,
FOREIGN KEY(user_string_id) REFERENCES users(id),
FOREIGN KEY(guild_string_id) REFERENCES guilds(id),
FOREIGN KEY(channel_string_id) REFERENCES channels(id)
);
CREATE UNIQUE INDEX idx_urls_url ON urls(url);`
_, err := db.Exec(q)
if err != nil {
log.Fatal(err)
}
}
func (db *SqliteDB) AddArchived(m *Message, status_code int) {
// Start a transaction using default isolation
tx, err := db.db.Begin()
if err != nil {
log.Fatal(err)
}
defer tx.Rollback()
// Insert new entries in users, guilds, channels tables for new values,
// ignoring if already present
_, err = tx.Exec(`
INSERT OR IGNORE INTO users(user_id) VALUES(?);
INSERT OR IGNORE INTO guilds(guild_id) VALUES(?);
INSERT OR IGNORE INTO channels(channel_id) VALUES(?);`,
m.Author, m.Guild, m.Channel)
if err != nil {
log.Fatal(err)
}
// Insert entry in URLs table using foreign key reference IDs
_, err = tx.Exec(`
INSERT OR IGNORE INTO
urls(url, user_string_id, guild_string_id, channel_string_id, status_code)
VALUES(
?,
(SELECT id FROM users WHERE user_id = ?),
(SELECT id FROM guilds WHERE guild_id = ?),
(SELECT id FROM channels WHERE channel_id = ?),
?
);`, m.URL, m.Author, m.Guild, m.Channel, status_code)
if err != nil {
log.Fatal(err)
}
// Finally commit the transaction
if err := tx.Commit(); err != nil {
log.Fatal(err)
}
}
func (db *SqliteDB) IsCached(url string) (bool, int) {
var status_code int
err := db.db.QueryRow("SELECT status_code FROM urls WHERE url = ?",
url).Scan(&status_code)
switch {
case err == sql.ErrNoRows:
return false, status_code
case err != nil:
log.Fatal(err)
}
return true, status_code
}
func (db *SqliteDB) Stats() (*Stats, error) {
var stats Stats
err := db.db.QueryRow(`
SELECT
(SELECT COUNT(*) FROM urls),
(SELECT COUNT(*) FROM users),
(SELECT COUNT(*) FROM guilds),
(SELECT COUNT(*) FROM channels)
;`).Scan(&stats.URLs, &stats.Users, &stats.Guilds, &stats.Channels)
if err != nil {
return nil, err
}
return &stats, nil
}
func (db *SqliteDB) ListEntries(limit int, offset int, user string,
guild string, channel string) (*[]Entry, error) {
var rows *sql.Rows
var err error
if user == "" && guild == "" && channel == "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, limit, offset)
if err != nil {
return nil, err
}
} else if user != "" && guild == "" && channel == "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE user_string_id = (SELECT id FROM users WHERE user_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, user, limit, offset)
if err != nil {
return nil, err
}
} else if user != "" && guild != "" && channel == "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE user_string_id = (SELECT id FROM users WHERE user_id = ?)
AND guild_string_id = (SELECT id FROM guilds WHERE guild_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, user, guild, limit, offset)
if err != nil {
return nil, err
}
} else if user != "" && guild == "" && channel != "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE user_string_id = (SELECT id FROM users WHERE user_id = ?)
AND channel_string_id = (SELECT id FROM channels WHERE channel_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, user, channel, limit, offset)
if err != nil {
return nil, err
}
} else if user != "" && guild != "" && channel != "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE user_string_id = (SELECT id FROM users WHERE user_id = ?)
AND guild_string_id = (SELECT id FROM guilds WHERE guild_id = ?)
AND channel_string_id = (SELECT id FROM channels WHERE channel_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, user, guild, channel, limit, offset)
if err != nil {
return nil, err
}
} else if user == "" && guild != "" && channel != "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE guild_string_id = (SELECT id FROM guilds WHERE guild_id = ?)
AND channel_string_id = (SELECT id FROM channels WHERE channel_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, guild, channel, limit, offset)
if err != nil {
return nil, err
}
} else if user == "" && guild == "" && channel != "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE channel_string_id = (SELECT id FROM channels WHERE channel_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, channel, limit, offset)
if err != nil {
return nil, err
}
} else if user == "" && guild != "" && channel == "" {
rows, err = db.db.Query(`
SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code
FROM urls
INNER JOIN users ON users.id = urls.user_string_id
INNER JOIN guilds ON guilds.id = urls.guild_string_id
INNER JOIN channels ON channels.id = urls.channel_string_id
WHERE guild_string_id = (SELECT id FROM guilds WHERE guild_id = ?)
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, guild, limit, offset)
if err != nil {
return nil, err
}
}
defer rows.Close()
var entries []Entry
for rows.Next() {
var e Entry
if err := rows.Scan(&e.ID, &e.Message.URL, &e.Message.Author,
&e.Message.Guild, &e.Message.Channel, &e.Status); err != nil {
return nil, err
}
entries = append(entries, e)
}
if err := rows.Err(); err != nil {
return nil, err
}
return &entries, nil
}
|
