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
|
package main
import (
"database/sql"
"errors"
"fmt"
"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 {
var db *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", "file:"+path+"?cache=shared")
initTables(db)
} else {
//db, err := sql.Open("sqlite3", path)
db, _ = sql.Open("sqlite3", "file:"+path+"?cache=shared")
if err != nil {
log.Fatal(err)
}
}
db.SetMaxOpenConns(1)
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, search string) (*[]Entry, error) {
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 TRUE`
args := []interface{}{}
if user != "" {
query = fmt.Sprintf(`%s
AND users.user_id = ?
`, query)
args = append(args, user)
}
if guild != "" {
query = fmt.Sprintf(`%s
AND guilds.guild_id = ?
`, query)
args = append(args, guild)
}
if channel != "" {
query = fmt.Sprintf(`%s
AND channels.channel_id = ?
`, query)
args = append(args, channel)
}
if search != "" {
query = fmt.Sprintf(`%s
AND urls.url LIKE '%%' || ? || '%%'
`, query)
args = append(args, search)
}
query = fmt.Sprintf(`%s
ORDER BY urls.id DESC
LIMIT ? OFFSET ?;`, query)
args = append(args, limit)
args = append(args, offset)
var rows *sql.Rows
var err error
rows, err = db.db.Query(query, args...)
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
}
|
