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 gethonksforuserfirstclass(userid int64) []*Honk {
123 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
124 rows, err := stmtHonksForUserFirstClass.Query(userid, dt, userid, userid)
125 return getsomehonks(rows, err)
126}
127func gethonksforme(userid int64) []*Honk {
128 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
129 rows, err := stmtHonksForMe.Query(userid, dt, userid)
130 return getsomehonks(rows, err)
131}
132func gethonksbyhonker(userid int64, honker string) []*Honk {
133 rows, err := stmtHonksByHonker.Query(userid, honker, userid)
134 return getsomehonks(rows, err)
135}
136func gethonksbyxonker(userid int64, xonker string) []*Honk {
137 rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
138 return getsomehonks(rows, err)
139}
140func gethonksbycombo(userid int64, combo string) []*Honk {
141 combo = "% " + combo + " %"
142 rows, err := stmtHonksByCombo.Query(userid, combo, userid)
143 return getsomehonks(rows, err)
144}
145func gethonksbyconvoy(userid int64, convoy string) []*Honk {
146 rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
147 honks := getsomehonks(rows, err)
148 for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
149 honks[i], honks[j] = honks[j], honks[i]
150 }
151 return honks
152}
153func gethonksbysearch(userid int64, q string) []*Honk {
154 q = "%" + q + "%"
155 rows, err := stmtHonksBySearch.Query(userid, q)
156 honks := getsomehonks(rows, err)
157 return honks
158}
159func gethonksbyontology(userid int64, name string) []*Honk {
160 rows, err := stmtHonksByOntology.Query(name, userid, userid)
161 honks := getsomehonks(rows, err)
162 return honks
163}
164
165func getsomehonks(rows *sql.Rows, err error) []*Honk {
166 if err != nil {
167 log.Printf("error querying honks: %s", err)
168 return nil
169 }
170 defer rows.Close()
171 var honks []*Honk
172 for rows.Next() {
173 h := scanhonk(rows)
174 if h != nil {
175 honks = append(honks, h)
176 }
177 }
178 rows.Close()
179 donksforhonks(honks)
180 return honks
181}
182
183type RowLike interface {
184 Scan(dest ...interface{}) error
185}
186
187func scanhonk(row RowLike) *Honk {
188 h := new(Honk)
189 var dt, aud string
190 err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
191 &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
192 if err != nil {
193 if err != sql.ErrNoRows {
194 log.Printf("error scanning honk: %s", err)
195 }
196 return nil
197 }
198 h.Date, _ = time.Parse(dbtimeformat, dt)
199 h.Audience = strings.Split(aud, " ")
200 h.Public = !keepitquiet(h.Audience)
201 return h
202}
203
204func donksforhonks(honks []*Honk) {
205 db := opendatabase()
206 var ids []string
207 hmap := make(map[int64]*Honk)
208 for _, h := range honks {
209 ids = append(ids, fmt.Sprintf("%d", h.ID))
210 hmap[h.ID] = h
211 }
212 // grab donks
213 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, ","))
214 rows, err := db.Query(q)
215 if err != nil {
216 log.Printf("error querying donks: %s", err)
217 return
218 }
219 defer rows.Close()
220 for rows.Next() {
221 var hid int64
222 var d Donk
223 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
224 if err != nil {
225 log.Printf("error scanning donk: %s", err)
226 continue
227 }
228 h := hmap[hid]
229 h.Donks = append(h.Donks, &d)
230 }
231 rows.Close()
232
233 // grab onts
234 q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", strings.Join(ids, ","))
235 rows, err = db.Query(q)
236 if err != nil {
237 log.Printf("error querying onts: %s", err)
238 return
239 }
240 defer rows.Close()
241 for rows.Next() {
242 var hid int64
243 var o string
244 err = rows.Scan(&hid, &o)
245 if err != nil {
246 log.Printf("error scanning donk: %s", err)
247 continue
248 }
249 h := hmap[hid]
250 h.Onts = append(h.Onts, o)
251 }
252 rows.Close()
253 // grab places
254 q = fmt.Sprintf("select honkid, name, latitude, longitude from places where honkid in (%s)", strings.Join(ids, ","))
255 rows, err = db.Query(q)
256 if err != nil {
257 log.Printf("error querying places: %s", err)
258 return
259 }
260 defer rows.Close()
261 for rows.Next() {
262 var hid int64
263 p := new(Place)
264 err = rows.Scan(&hid, &p.Name, &p.Latitude, &p.Longitude)
265 if err != nil {
266 log.Printf("error scanning place: %s", err)
267 continue
268 }
269 h := hmap[hid]
270 h.Place = p
271 }
272 rows.Close()
273}
274
275func savehonk(h *Honk) error {
276 dt := h.Date.UTC().Format(dbtimeformat)
277 aud := strings.Join(h.Audience, " ")
278
279 res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
280 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
281 h.Oonker, h.Flags)
282 if err != nil {
283 log.Printf("err saving honk: %s", err)
284 return err
285 }
286 h.ID, _ = res.LastInsertId()
287 err = saveextras(h)
288 return err
289}
290
291func saveextras(h *Honk) error {
292 for _, d := range h.Donks {
293 _, err := stmtSaveDonk.Exec(h.ID, d.FileID)
294 if err != nil {
295 log.Printf("err saving donk: %s", err)
296 return err
297 }
298 }
299 for _, o := range h.Onts {
300 _, err := stmtSaveOnt.Exec(strings.ToLower(o), h.ID)
301 if err != nil {
302 log.Printf("error saving ont: %s", err)
303 return err
304 }
305 }
306 if h.Place != nil {
307 _, err := stmtSavePlace.Exec(h.ID, h.Place.Name, h.Place.Latitude, h.Place.Longitude)
308 if err != nil {
309 log.Printf("error saving ont: %s", err)
310 return err
311 }
312 }
313
314 return nil
315}
316
317func deleteextras(honkid int64) {
318 _, err := stmtDeleteDonks.Exec(honkid)
319 if err != nil {
320 log.Printf("error deleting: %s", err)
321 }
322 _, err = stmtDeleteOnts.Exec(honkid)
323 if err != nil {
324 log.Printf("error deleting: %s", err)
325 }
326 _, err = stmtDeletePlace.Exec(honkid)
327 if err != nil {
328 log.Printf("error deleting: %s", err)
329 }
330}
331
332func deletehonk(honkid int64) {
333 deleteextras(honkid)
334 _, err := stmtDeleteHonk.Exec(honkid)
335 if err != nil {
336 log.Printf("error deleting: %s", err)
337 }
338}
339
340func updatehonk(h *Honk) {
341 old := getxonk(h.UserID, h.XID)
342 _, err := stmtSaveOld.Exec(old.ID, old.Precis, old.Noise)
343 if err != nil {
344 log.Printf("error saving old: %s", err)
345 return
346 }
347 deleteextras(h.ID)
348
349 dt := h.Date.UTC().Format(dbtimeformat)
350 stmtUpdateHonk.Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
351
352 saveextras(h)
353}
354
355func cleanupdb(arg string) {
356 db := opendatabase()
357 days, err := strconv.Atoi(arg)
358 if err != nil {
359 honker := arg
360 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
361 doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
362 doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
363 } else {
364 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
365 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)
366 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)
367 }
368 doordie(db, "delete from files where fileid not in (select fileid from donks)")
369 for _, u := range allusers() {
370 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)
371 }
372}
373
374var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
375var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
376var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
377var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
378var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
379var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
380var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
381var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
382var stmtSelectOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
383var stmtSavePlace, stmtDeletePlace, stmtHonksForUserFirstClass, stmtSaveOld, stmtUpdateHonk *sql.Stmt
384
385func preparetodie(db *sql.DB, s string) *sql.Stmt {
386 stmt, err := db.Prepare(s)
387 if err != nil {
388 log.Fatalf("error %s: %s", err, s)
389 }
390 return stmt
391}
392
393func prepareStatements(db *sql.DB) {
394 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")
395 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
396 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
397 stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
398 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
399 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
400
401 selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, format, convoy, whofore, flags from honks join users on honks.userid = users.userid "
402 limit := " order by honks.honkid desc limit 250"
403 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
404 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
405 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
406 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
407 stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
408 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)
409 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and (what <> 'tonk') and honker in (select xid from honkers where userid = ? and flavor = 'sub' and combos not like '% - %')"+butnotthose+limit)
410 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
411 stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
412 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
413 stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
414 stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
415 stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
416 stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
417
418 stmtSaveOld = preparetodie(db, "insert into forsaken (honkid, precis, noise) values (?, ?, ?)")
419 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
420 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
421 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
422 stmtSavePlace = preparetodie(db, "insert into places (honkid, name, latitude, longitude) values (?, ?, ?, ?)")
423 stmtDeletePlace = preparetodie(db, "delete from places where honkid = ?")
424 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
425 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
426 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
427 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
428 stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
429 stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
430 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
431 stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
432 stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
433 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
434 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
435 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
436 stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
437 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
438 stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
439 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
440 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
441 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
442 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
443 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
444 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
445 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")
446 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
447 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
448 stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
449}