database.go (view raw)
1//
2// Copyright (c) 2019 Ted Unangst <tedu@tedunangst.com>
3//
4// Permission to use, copy, modify, and distribute this software for any
5// purpose with or without fee is hereby granted, provided that the above
6// copyright notice and this permission notice appear in all copies.
7//
8// THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
9// WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
10// MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
11// ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
12// WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
13// ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
14// OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
15
16package main
17
18import (
19 "database/sql"
20 "fmt"
21 "log"
22 "strconv"
23 "strings"
24 "time"
25
26 "humungus.tedunangst.com/r/webs/login"
27)
28
29func butwhatabout(name string) (*WhatAbout, error) {
30 row := stmtWhatAbout.QueryRow(name)
31 var user WhatAbout
32 var options string
33 err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &options)
34 user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
35 user.SkinnyCSS = strings.Contains(options, " skinny ")
36 return &user, err
37}
38
39func gethonkers(userid int64) []*Honker {
40 rows, err := stmtHonkers.Query(userid)
41 if err != nil {
42 log.Printf("error querying honkers: %s", err)
43 return nil
44 }
45 defer rows.Close()
46 var honkers []*Honker
47 for rows.Next() {
48 var f Honker
49 var combos string
50 err = rows.Scan(&f.ID, &f.UserID, &f.Name, &f.XID, &f.Flavor, &combos)
51 f.Combos = strings.Split(strings.TrimSpace(combos), " ")
52 if err != nil {
53 log.Printf("error scanning honker: %s", err)
54 return nil
55 }
56 honkers = append(honkers, &f)
57 }
58 return honkers
59}
60
61func getdubs(userid int64) []*Honker {
62 rows, err := stmtDubbers.Query(userid)
63 if err != nil {
64 log.Printf("error querying dubs: %s", err)
65 return nil
66 }
67 defer rows.Close()
68 var honkers []*Honker
69 for rows.Next() {
70 var f Honker
71 err = rows.Scan(&f.ID, &f.UserID, &f.Name, &f.XID, &f.Flavor)
72 if err != nil {
73 log.Printf("error scanning honker: %s", err)
74 return nil
75 }
76 honkers = append(honkers, &f)
77 }
78 return honkers
79}
80
81func allusers() []login.UserInfo {
82 var users []login.UserInfo
83 rows, _ := opendatabase().Query("select userid, username from users")
84 defer rows.Close()
85 for rows.Next() {
86 var u login.UserInfo
87 rows.Scan(&u.UserID, &u.Username)
88 users = append(users, u)
89 }
90 return users
91}
92
93func getxonk(userid int64, xid string) *Honk {
94 row := stmtOneXonk.QueryRow(userid, xid)
95 return scanhonk(row)
96}
97
98func getbonk(userid int64, xid string) *Honk {
99 row := stmtOneBonk.QueryRow(userid, xid)
100 return scanhonk(row)
101}
102
103func getpublichonks() []*Honk {
104 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
105 rows, err := stmtPublicHonks.Query(dt)
106 return getsomehonks(rows, err)
107}
108func gethonksbyuser(name string, includeprivate bool) []*Honk {
109 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
110 whofore := 2
111 if includeprivate {
112 whofore = 3
113 }
114 rows, err := stmtUserHonks.Query(whofore, name, dt)
115 return getsomehonks(rows, err)
116}
117func gethonksforuser(userid int64) []*Honk {
118 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
119 rows, err := stmtHonksForUser.Query(userid, dt, userid, userid)
120 return getsomehonks(rows, err)
121}
122func gethonksforme(userid int64) []*Honk {
123 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
124 rows, err := stmtHonksForMe.Query(userid, dt, userid)
125 return getsomehonks(rows, err)
126}
127func gethonksbyhonker(userid int64, honker string) []*Honk {
128 rows, err := stmtHonksByHonker.Query(userid, honker, userid)
129 return getsomehonks(rows, err)
130}
131func gethonksbyxonker(userid int64, xonker string) []*Honk {
132 rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
133 return getsomehonks(rows, err)
134}
135func gethonksbycombo(userid int64, combo string) []*Honk {
136 combo = "% " + combo + " %"
137 rows, err := stmtHonksByCombo.Query(userid, combo, userid)
138 return getsomehonks(rows, err)
139}
140func gethonksbyconvoy(userid int64, convoy string) []*Honk {
141 rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
142 honks := getsomehonks(rows, err)
143 for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
144 honks[i], honks[j] = honks[j], honks[i]
145 }
146 return honks
147}
148func gethonksbysearch(userid int64, q string) []*Honk {
149 q = "%" + q + "%"
150 rows, err := stmtHonksBySearch.Query(userid, q)
151 honks := getsomehonks(rows, err)
152 return honks
153}
154func gethonksbyontology(userid int64, name string) []*Honk {
155 rows, err := stmtHonksByOntology.Query(name, userid, userid)
156 honks := getsomehonks(rows, err)
157 return honks
158}
159
160func getsomehonks(rows *sql.Rows, err error) []*Honk {
161 if err != nil {
162 log.Printf("error querying honks: %s", err)
163 return nil
164 }
165 defer rows.Close()
166 var honks []*Honk
167 for rows.Next() {
168 h := scanhonk(rows)
169 if h != nil {
170 honks = append(honks, h)
171 }
172 }
173 rows.Close()
174 donksforhonks(honks)
175 return honks
176}
177
178type RowLike interface {
179 Scan(dest ...interface{}) error
180}
181
182func scanhonk(row RowLike) *Honk {
183 h := new(Honk)
184 var dt, aud, onts string
185 err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
186 &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
187 if err != nil {
188 if err != sql.ErrNoRows {
189 log.Printf("error scanning honk: %s", err)
190 }
191 return nil
192 }
193 h.Date, _ = time.Parse(dbtimeformat, dt)
194 h.Audience = strings.Split(aud, " ")
195 h.Public = !keepitquiet(h.Audience)
196 if len(onts) > 0 {
197 h.Onts = strings.Split(onts, " ")
198 }
199 return h
200}
201
202func donksforhonks(honks []*Honk) {
203 db := opendatabase()
204 var ids []string
205 hmap := make(map[int64]*Honk)
206 for _, h := range honks {
207 ids = append(ids, fmt.Sprintf("%d", h.ID))
208 hmap[h.ID] = h
209 }
210 q := fmt.Sprintf("select honkid, donks.fileid, xid, name, description, url, media, local from donks join files on donks.fileid = files.fileid where honkid in (%s)", strings.Join(ids, ","))
211 rows, err := db.Query(q)
212 if err != nil {
213 log.Printf("error querying donks: %s", err)
214 return
215 }
216 defer rows.Close()
217 for rows.Next() {
218 var hid int64
219 var d Donk
220 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
221 if err != nil {
222 log.Printf("error scanning donk: %s", err)
223 continue
224 }
225 h := hmap[hid]
226 h.Donks = append(h.Donks, &d)
227 }
228}
229
230func savehonk(h *Honk) error {
231 dt := h.Date.UTC().Format(dbtimeformat)
232 aud := strings.Join(h.Audience, " ")
233
234 res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
235 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
236 h.Oonker, h.Flags, strings.Join(h.Onts, " "))
237 if err != nil {
238 log.Printf("err saving honk: %s", err)
239 return err
240 }
241 h.ID, _ = res.LastInsertId()
242 err = saveextras(h)
243 return err
244}
245
246func saveextras(h *Honk) error {
247 for _, d := range h.Donks {
248 _, err := stmtSaveDonk.Exec(h.ID, d.FileID)
249 if err != nil {
250 log.Printf("err saving donk: %s", err)
251 return err
252 }
253 }
254 for _, o := range h.Onts {
255 _, err := stmtSaveOnt.Exec(strings.ToLower(o), h.ID)
256 if err != nil {
257 log.Printf("error saving ont: %s", err)
258 return err
259 }
260 }
261 return nil
262}
263
264func deleteextras(honkid int64) {
265 _, err := stmtDeleteDonks.Exec(honkid)
266 if err != nil {
267 log.Printf("error deleting: %s", err)
268 }
269 _, err = stmtDeleteOnts.Exec(honkid)
270 if err != nil {
271 log.Printf("error deleting: %s", err)
272 }
273}
274
275func deletehonk(honkid int64) {
276 deleteextras(honkid)
277 _, err := stmtDeleteHonk.Exec(honkid)
278 if err != nil {
279 log.Printf("error deleting: %s", err)
280 }
281}
282
283func updatehonk(h *Honk) {
284 old := getxonk(h.UserID, h.XID)
285 _, err := stmtSaveOld.Exec(old.ID, old.Precis, old.Noise)
286 if err != nil {
287 log.Printf("error saving old: %s", err)
288 return
289 }
290 deleteextras(h.ID)
291
292 dt := h.Date.UTC().Format(dbtimeformat)
293 stmtUpdateHonk.Exec(h.Precis, h.Noise, dt, h.ID)
294
295 saveextras(h)
296}
297
298func cleanupdb(arg string) {
299 db := opendatabase()
300 days, err := strconv.Atoi(arg)
301 if err != nil {
302 honker := arg
303 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
304 doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
305 doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
306 } else {
307 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
308 doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and convoy not in (select convoy from honks where whofore = 2 or whofore = 3))", expdate)
309 doordie(db, "delete from honks where dt < ? and whofore = 0 and convoy not in (select convoy from honks where whofore = 2 or whofore = 3)", expdate)
310 }
311 doordie(db, "delete from files where fileid not in (select fileid from donks)")
312 for _, u := range allusers() {
313 doordie(db, "delete from zonkers where userid = ? and wherefore = 'zonvoy' and zonkerid < (select zonkerid from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 1 offset 200)", u.UserID, u.UserID)
314 }
315}
316
317var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
318var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
319var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
320var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
321var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
322var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
323var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
324var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
325var stmtSelectOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
326var stmtSaveOld, stmtUpdateHonk *sql.Stmt
327
328func preparetodie(db *sql.DB, s string) *sql.Stmt {
329 stmt, err := db.Prepare(s)
330 if err != nil {
331 log.Fatalf("error %s: %s", err, s)
332 }
333 return stmt
334}
335
336func prepareStatements(db *sql.DB) {
337 stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
338 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
339 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
340 stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
341 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
342 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
343
344 selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, convoy, whofore, flags, onts from honks join users on honks.userid = users.userid "
345 limit := " order by honks.honkid desc limit 250"
346 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
347 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
348 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
349 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
350 stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
351 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and honker in (select xid from honkers where userid = ? and flavor = 'sub' and combos not like '% - %')"+butnotthose+limit)
352 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
353 stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
354 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
355 stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
356 stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
357 stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
358 stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
359
360 stmtSaveOld = preparetodie(db, "insert into forsaken (honkid, precis, noise) values (?, ?, ?)")
361 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, onts) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
362 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
363 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, dt = ? where honkid = ?")
364 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
365 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
366 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
367 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
368 stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
369 stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
370 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
371 stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
372 stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
373 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
374 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
375 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
376 stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
377 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
378 stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
379 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
380 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
381 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
382 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
383 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
384 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
385 stmtRecentHonkers = preparetodie(db, "select distinct(honker) from honks where userid = ? and honker not in (select xid from honkers where userid = ? and flavor = 'sub') order by honkid desc limit 100")
386 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
387 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
388 stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
389}