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 "bytes"
20 "crypto/sha512"
21 "database/sql"
22 _ "embed"
23 "encoding/json"
24 "fmt"
25 "html/template"
26 "sort"
27 "strconv"
28 "strings"
29 "sync"
30 "time"
31
32 "humungus.tedunangst.com/r/webs/gencache"
33 "humungus.tedunangst.com/r/webs/htfilter"
34 "humungus.tedunangst.com/r/webs/httpsig"
35 "humungus.tedunangst.com/r/webs/login"
36 "humungus.tedunangst.com/r/webs/mz"
37)
38
39var honkwindow time.Duration = 7
40
41//go:embed schema.sql
42var sqlSchema string
43
44func userfromrow(row *sql.Row) (*WhatAbout, error) {
45 user := new(WhatAbout)
46 var seckey, options string
47 err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &seckey, &options)
48 if err == nil {
49 user.SecKey, _, err = httpsig.DecodeKey(seckey)
50 }
51 if err != nil {
52 return nil, err
53 }
54 if user.ID > 0 {
55 user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
56 err = unjsonify(options, &user.Options)
57 if err != nil {
58 elog.Printf("error processing user options: %s", err)
59 }
60 } else {
61 user.URL = fmt.Sprintf("https://%s/%s", serverName, user.Name)
62 }
63 if user.Options.Reaction == "" {
64 user.Options.Reaction = "none"
65 }
66
67 return user, nil
68}
69
70var somenamedusers = gencache.New(gencache.Options[string, *WhatAbout]{Fill: func(name string) (*WhatAbout, bool) {
71 row := stmtUserByName.QueryRow(name)
72 user, err := userfromrow(row)
73 if err != nil {
74 return nil, false
75 }
76 var marker mz.Marker
77 marker.HashLinker = ontoreplacer
78 marker.AtLinker = attoreplacer
79 user.HTAbout = template.HTML(marker.Mark(user.About))
80 user.Onts = marker.HashTags
81 return user, true
82}})
83
84var somenumberedusers = gencache.New(gencache.Options[int64, *WhatAbout]{Fill: func(userid int64) (*WhatAbout, bool) {
85 row := stmtUserByNumber.QueryRow(userid)
86 user, err := userfromrow(row)
87 if err != nil {
88 return nil, false
89 }
90 // don't touch attoreplacer, which introduces a loop
91 // finger -> getjunk -> keys -> users
92 return user, true
93}})
94
95func getserveruser() *WhatAbout {
96 user, ok := somenumberedusers.Get(serverUID)
97 if !ok {
98 elog.Panicf("lost server user")
99 }
100 return user
101}
102
103func butwhatabout(name string) (*WhatAbout, error) {
104 user, ok := somenamedusers.Get(name)
105 if !ok {
106 return nil, fmt.Errorf("no user: %s", name)
107 }
108 return user, nil
109}
110
111var honkerinvalidator gencache.Invalidator[int64]
112
113func gethonkers(userid int64) []*Honker {
114 rows, err := stmtHonkers.Query(userid)
115 if err != nil {
116 elog.Printf("error querying honkers: %s", err)
117 return nil
118 }
119 defer rows.Close()
120 var honkers []*Honker
121 for rows.Next() {
122 h := new(Honker)
123 var combos, meta string
124 err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor, &combos, &meta)
125 if err == nil {
126 err = unjsonify(meta, &h.Meta)
127 }
128 if err != nil {
129 elog.Printf("error scanning honker: %s", err)
130 continue
131 }
132 h.Combos = strings.Split(strings.TrimSpace(combos), " ")
133 honkers = append(honkers, h)
134 }
135 return honkers
136}
137
138func getdubs(userid int64) []*Honker {
139 rows, err := stmtDubbers.Query(userid)
140 return dubsfromrows(rows, err)
141}
142
143func getnameddubs(userid int64, name string) []*Honker {
144 rows, err := stmtNamedDubbers.Query(userid, name)
145 return dubsfromrows(rows, err)
146}
147
148func dubsfromrows(rows *sql.Rows, err error) []*Honker {
149 if err != nil {
150 elog.Printf("error querying dubs: %s", err)
151 return nil
152 }
153 defer rows.Close()
154 var honkers []*Honker
155 for rows.Next() {
156 h := new(Honker)
157 err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor)
158 if err != nil {
159 elog.Printf("error scanning honker: %s", err)
160 return nil
161 }
162 honkers = append(honkers, h)
163 }
164 return honkers
165}
166
167func allusers() []login.UserInfo {
168 var users []login.UserInfo
169 rows, _ := opendatabase().Query("select userid, username from users where userid > 0")
170 defer rows.Close()
171 for rows.Next() {
172 var u login.UserInfo
173 rows.Scan(&u.UserID, &u.Username)
174 users = append(users, u)
175 }
176 return users
177}
178
179func getxonk(userid int64, xid string) *Honk {
180 row := stmtOneXonk.QueryRow(userid, xid)
181 return scanhonk(row)
182}
183
184func getbonk(userid int64, xid string) *Honk {
185 row := stmtOneBonk.QueryRow(userid, xid)
186 return scanhonk(row)
187}
188
189func getpublichonks() []*Honk {
190 dt := time.Now().Add(-honkwindow).UTC().Format(dbtimeformat)
191 rows, err := stmtPublicHonks.Query(dt, 100)
192 return getsomehonks(rows, err)
193}
194func geteventhonks(userid int64) []*Honk {
195 rows, err := stmtEventHonks.Query(userid, 25)
196 honks := getsomehonks(rows, err)
197 sort.Slice(honks, func(i, j int) bool {
198 var t1, t2 time.Time
199 if honks[i].Time == nil {
200 t1 = honks[i].Date
201 } else {
202 t1 = honks[i].Time.StartTime
203 }
204 if honks[j].Time == nil {
205 t2 = honks[j].Date
206 } else {
207 t2 = honks[j].Time.StartTime
208 }
209 return t1.After(t2)
210 })
211 now := time.Now().Add(-24 * time.Hour)
212 for i, h := range honks {
213 t := h.Date
214 if tm := h.Time; tm != nil {
215 t = tm.StartTime
216 }
217 if t.Before(now) {
218 honks = honks[:i]
219 break
220 }
221 }
222 reversehonks(honks)
223 return honks
224}
225func gethonksbyuser(name string, includeprivate bool, wanted int64) []*Honk {
226 dt := time.Now().Add(-honkwindow).UTC().Format(dbtimeformat)
227 limit := 50
228 whofore := 2
229 if includeprivate {
230 whofore = 3
231 }
232 rows, err := stmtUserHonks.Query(wanted, whofore, name, dt, limit)
233 return getsomehonks(rows, err)
234}
235func gethonksforuser(userid int64, wanted int64) []*Honk {
236 dt := time.Now().Add(-honkwindow).UTC().Format(dbtimeformat)
237 rows, err := stmtHonksForUser.Query(wanted, userid, dt, userid, userid)
238 return getsomehonks(rows, err)
239}
240func gethonksforuserfirstclass(userid int64, wanted int64) []*Honk {
241 dt := time.Now().Add(-honkwindow).UTC().Format(dbtimeformat)
242 rows, err := stmtHonksForUserFirstClass.Query(wanted, userid, dt, userid, userid)
243 return getsomehonks(rows, err)
244}
245
246func gethonksforme(userid int64, wanted int64) []*Honk {
247 dt := time.Now().Add(-honkwindow).UTC().Format(dbtimeformat)
248 rows, err := stmtHonksForMe.Query(wanted, userid, dt, userid, 250)
249 return getsomehonks(rows, err)
250}
251func gethonksfromlongago(userid int64, wanted int64) []*Honk {
252 now := time.Now()
253 var honks []*Honk
254 for i := 1; i <= 4; i++ {
255 dt := time.Date(now.Year()-i, now.Month(), now.Day(), now.Hour(), now.Minute(),
256 now.Second(), 0, now.Location())
257 dt1 := dt.Add(-36 * time.Hour).UTC().Format(dbtimeformat)
258 dt2 := dt.Add(12 * time.Hour).UTC().Format(dbtimeformat)
259 rows, err := stmtHonksFromLongAgo.Query(wanted, userid, dt1, dt2, userid)
260 honks = append(honks, getsomehonks(rows, err)...)
261 }
262 return honks
263}
264func getsavedhonks(userid int64, wanted int64) []*Honk {
265 rows, err := stmtHonksISaved.Query(wanted, userid)
266 return getsomehonks(rows, err)
267}
268func gethonksbyhonker(userid int64, honker string, wanted int64) []*Honk {
269 rows, err := stmtHonksByHonker.Query(wanted, userid, honker, userid)
270 return getsomehonks(rows, err)
271}
272func gethonksbyxonker(userid int64, xonker string, wanted int64) []*Honk {
273 rows, err := stmtHonksByXonker.Query(wanted, userid, xonker, xonker, userid)
274 return getsomehonks(rows, err)
275}
276func gethonksbycombo(userid int64, combo string, wanted int64) []*Honk {
277 combo = "% " + combo + " %"
278 rows, err := stmtHonksByCombo.Query(wanted, userid, userid, combo, userid, wanted, userid, combo, userid)
279 return getsomehonks(rows, err)
280}
281func gethonksbyconvoy(userid int64, convoy string, wanted int64) []*Honk {
282 rows, err := stmtHonksByConvoy.Query(wanted, userid, userid, convoy)
283 honks := getsomehonks(rows, err)
284 return honks
285}
286func gethonksbysearch(userid int64, q string, wanted int64) []*Honk {
287 var queries []string
288 var params []interface{}
289 queries = append(queries, "honks.honkid > ?")
290 params = append(params, wanted)
291 queries = append(queries, "honks.userid = ?")
292 params = append(params, userid)
293
294 terms := strings.Split(q, " ")
295 for _, t := range terms {
296 if t == "" {
297 continue
298 }
299 negate := " "
300 if t[0] == '-' {
301 t = t[1:]
302 negate = " not "
303 }
304 if t == "" {
305 continue
306 }
307 if t == "@me" {
308 queries = append(queries, negate+"whofore = 1")
309 continue
310 }
311 if t == "@self" {
312 queries = append(queries, negate+"(whofore = 2 or whofore = 3)")
313 continue
314 }
315 if strings.HasPrefix(t, "before:") {
316 before := t[7:]
317 queries = append(queries, "dt < ?")
318 params = append(params, before)
319 continue
320 }
321 if strings.HasPrefix(t, "after:") {
322 after := t[6:]
323 queries = append(queries, "dt > ?")
324 params = append(params, after)
325 continue
326 }
327 if strings.HasPrefix(t, "site:") {
328 site := t[5:]
329 site = "%" + site + "%"
330 queries = append(queries, "xid"+negate+"like ?")
331 params = append(params, site)
332 continue
333 }
334 if strings.HasPrefix(t, "honker:") {
335 honker := t[7:]
336 xid := fullname(honker, userid)
337 if xid != "" {
338 honker = xid
339 }
340 queries = append(queries, negate+"(honks.honker = ? or honks.oonker = ?)")
341 params = append(params, honker)
342 params = append(params, honker)
343 continue
344 }
345 t = "%" + t + "%"
346 queries = append(queries, negate+"(plain like ?)")
347 params = append(params, t)
348 }
349
350 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 "
351 where := "where " + strings.Join(queries, " and ")
352 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
353 limit := " order by honks.honkid desc limit 250"
354 params = append(params, userid)
355 rows, err := opendatabase().Query(selecthonks+where+butnotthose+limit, params...)
356 honks := getsomehonks(rows, err)
357 return honks
358}
359func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
360 rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
361 honks := getsomehonks(rows, err)
362 return honks
363}
364
365func reversehonks(honks []*Honk) {
366 for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
367 honks[i], honks[j] = honks[j], honks[i]
368 }
369}
370
371func getsomehonks(rows *sql.Rows, err error) []*Honk {
372 if err != nil {
373 elog.Printf("error querying honks: %s", err)
374 return nil
375 }
376 defer rows.Close()
377 var honks []*Honk
378 for rows.Next() {
379 h := scanhonk(rows)
380 if h != nil {
381 honks = append(honks, h)
382 }
383 }
384 rows.Close()
385 donksforhonks(honks)
386 return honks
387}
388
389type RowLike interface {
390 Scan(dest ...interface{}) error
391}
392
393func scanhonk(row RowLike) *Honk {
394 h := new(Honk)
395 var dt, aud string
396 err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
397 &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
398 if err != nil {
399 if err != sql.ErrNoRows {
400 elog.Printf("error scanning honk: %s", err)
401 }
402 return nil
403 }
404 h.Date, _ = time.Parse(dbtimeformat, dt)
405 h.Audience = strings.Split(aud, " ")
406 h.Public = loudandproud(h.Audience)
407 return h
408}
409
410func donksforhonks(honks []*Honk) {
411 db := opendatabase()
412 var ids []string
413 hmap := make(map[int64]*Honk)
414 for _, h := range honks {
415 ids = append(ids, fmt.Sprintf("%d", h.ID))
416 hmap[h.ID] = h
417 }
418 idset := strings.Join(ids, ",")
419 // grab donks
420 q := fmt.Sprintf("select honkid, donks.fileid, xid, name, description, url, media, local from donks join filemeta on donks.fileid = filemeta.fileid where honkid in (%s)", idset)
421 rows, err := db.Query(q)
422 if err != nil {
423 elog.Printf("error querying donks: %s", err)
424 return
425 }
426 defer rows.Close()
427 for rows.Next() {
428 var hid int64
429 d := new(Donk)
430 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
431 if err != nil {
432 elog.Printf("error scanning donk: %s", err)
433 continue
434 }
435 d.External = !strings.HasPrefix(d.URL, serverPrefix)
436 h := hmap[hid]
437 h.Donks = append(h.Donks, d)
438 }
439 rows.Close()
440
441 // grab onts
442 q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", idset)
443 rows, err = db.Query(q)
444 if err != nil {
445 elog.Printf("error querying onts: %s", err)
446 return
447 }
448 defer rows.Close()
449 for rows.Next() {
450 var hid int64
451 var o string
452 err = rows.Scan(&hid, &o)
453 if err != nil {
454 elog.Printf("error scanning donk: %s", err)
455 continue
456 }
457 h := hmap[hid]
458 h.Onts = append(h.Onts, o)
459 }
460 rows.Close()
461
462 // grab meta
463 q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", idset)
464 rows, err = db.Query(q)
465 if err != nil {
466 elog.Printf("error querying honkmeta: %s", err)
467 return
468 }
469 defer rows.Close()
470 for rows.Next() {
471 var hid int64
472 var genus, j string
473 err = rows.Scan(&hid, &genus, &j)
474 if err != nil {
475 elog.Printf("error scanning honkmeta: %s", err)
476 continue
477 }
478 h := hmap[hid]
479 switch genus {
480 case "place":
481 p := new(Place)
482 err = unjsonify(j, p)
483 if err != nil {
484 elog.Printf("error parsing place: %s", err)
485 continue
486 }
487 h.Place = p
488 case "time":
489 t := new(Time)
490 err = unjsonify(j, t)
491 if err != nil {
492 elog.Printf("error parsing time: %s", err)
493 continue
494 }
495 h.Time = t
496 case "mentions":
497 err = unjsonify(j, &h.Mentions)
498 if err != nil {
499 elog.Printf("error parsing mentions: %s", err)
500 continue
501 }
502 case "badonks":
503 err = unjsonify(j, &h.Badonks)
504 if err != nil {
505 elog.Printf("error parsing badonks: %s", err)
506 continue
507 }
508 case "oldrev":
509 default:
510 elog.Printf("unknown meta genus: %s", genus)
511 }
512 }
513 rows.Close()
514}
515
516func donksforchonks(chonks []*Chonk) {
517 db := opendatabase()
518 var ids []string
519 chmap := make(map[int64]*Chonk)
520 for _, ch := range chonks {
521 ids = append(ids, fmt.Sprintf("%d", ch.ID))
522 chmap[ch.ID] = ch
523 }
524 idset := strings.Join(ids, ",")
525 // grab donks
526 q := fmt.Sprintf("select chonkid, donks.fileid, xid, name, description, url, media, local from donks join filemeta on donks.fileid = filemeta.fileid where chonkid in (%s)", idset)
527 rows, err := db.Query(q)
528 if err != nil {
529 elog.Printf("error querying donks: %s", err)
530 return
531 }
532 defer rows.Close()
533 for rows.Next() {
534 var chid int64
535 d := new(Donk)
536 err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
537 if err != nil {
538 elog.Printf("error scanning donk: %s", err)
539 continue
540 }
541 ch := chmap[chid]
542 ch.Donks = append(ch.Donks, d)
543 }
544}
545
546func savefile(name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
547 fileid, _, err := savefileandxid(name, desc, url, media, local, data)
548 return fileid, err
549}
550
551func hashfiledata(data []byte) string {
552 h := sha512.New512_256()
553 h.Write(data)
554 return fmt.Sprintf("%x", h.Sum(nil))
555}
556
557func savefileandxid(name string, desc string, url string, media string, local bool, data []byte) (int64, string, error) {
558 var xid string
559 if local {
560 hash := hashfiledata(data)
561 row := stmtCheckFileData.QueryRow(hash)
562 err := row.Scan(&xid)
563 if err == sql.ErrNoRows {
564 xid = xfiltrate()
565 switch media {
566 case "image/png":
567 xid += ".png"
568 case "image/jpeg":
569 xid += ".jpg"
570 case "image/svg+xml":
571 xid += ".svg"
572 case "application/pdf":
573 xid += ".pdf"
574 case "text/plain":
575 xid += ".txt"
576 }
577 _, err = stmtSaveFileData.Exec(xid, media, hash, data)
578 if err != nil {
579 return 0, "", err
580 }
581 } else if err != nil {
582 elog.Printf("error checking file hash: %s", err)
583 return 0, "", err
584 }
585 if url == "" {
586 url = fmt.Sprintf("https://%s/d/%s", serverName, xid)
587 }
588 }
589
590 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
591 if err != nil {
592 return 0, "", err
593 }
594 fileid, _ := res.LastInsertId()
595 return fileid, xid, nil
596}
597
598func finddonkid(fileid int64, url string) *Donk {
599 donk := new(Donk)
600 row := stmtFindFileId.QueryRow(fileid, url)
601 err := row.Scan(&donk.XID, &donk.Local, &donk.Desc)
602 if err == nil {
603 donk.FileID = fileid
604 return donk
605 }
606 if err != sql.ErrNoRows {
607 elog.Printf("error finding file: %s", err)
608 }
609 return nil
610}
611
612func finddonk(url string) *Donk {
613 donk := new(Donk)
614 row := stmtFindFile.QueryRow(url)
615 err := row.Scan(&donk.FileID, &donk.XID)
616 if err == nil {
617 return donk
618 }
619 if err != sql.ErrNoRows {
620 elog.Printf("error finding file: %s", err)
621 }
622 return nil
623}
624
625func savechonk(ch *Chonk) error {
626 dt := ch.Date.UTC().Format(dbtimeformat)
627 db := opendatabase()
628 tx, err := db.Begin()
629 if err != nil {
630 elog.Printf("can't begin tx: %s", err)
631 return err
632 }
633
634 res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
635 if err == nil {
636 ch.ID, _ = res.LastInsertId()
637 for _, d := range ch.Donks {
638 _, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
639 if err != nil {
640 elog.Printf("error saving donk: %s", err)
641 break
642 }
643 }
644 chatplusone(tx, ch.UserID)
645 err = tx.Commit()
646 } else {
647 tx.Rollback()
648 }
649 return err
650}
651
652func chatplusone(tx *sql.Tx, userid int64) {
653 user, ok := somenumberedusers.Get(userid)
654 if !ok {
655 return
656 }
657 options := user.Options
658 options.ChatCount += 1
659 j, err := jsonify(options)
660 if err == nil {
661 _, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
662 }
663 if err != nil {
664 elog.Printf("error plussing chat: %s", err)
665 }
666 somenamedusers.Clear(user.Name)
667 somenumberedusers.Clear(user.ID)
668}
669
670func chatnewnone(userid int64) {
671 user, ok := somenumberedusers.Get(userid)
672 if !ok || user.Options.ChatCount == 0 {
673 return
674 }
675 options := user.Options
676 options.ChatCount = 0
677 j, err := jsonify(options)
678 if err == nil {
679 db := opendatabase()
680 _, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
681 }
682 if err != nil {
683 elog.Printf("error noneing chat: %s", err)
684 }
685 somenamedusers.Clear(user.Name)
686 somenumberedusers.Clear(user.ID)
687}
688
689func meplusone(tx *sql.Tx, userid int64) {
690 user, ok := somenumberedusers.Get(userid)
691 if !ok {
692 return
693 }
694 options := user.Options
695 options.MeCount += 1
696 j, err := jsonify(options)
697 if err == nil {
698 _, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
699 }
700 if err != nil {
701 elog.Printf("error plussing me: %s", err)
702 }
703 somenamedusers.Clear(user.Name)
704 somenumberedusers.Clear(user.ID)
705}
706
707func menewnone(userid int64) {
708 user, ok := somenumberedusers.Get(userid)
709 if !ok || user.Options.MeCount == 0 {
710 return
711 }
712 options := user.Options
713 options.MeCount = 0
714 j, err := jsonify(options)
715 if err == nil {
716 db := opendatabase()
717 _, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
718 }
719 if err != nil {
720 elog.Printf("error noneing me: %s", err)
721 }
722 somenamedusers.Clear(user.Name)
723 somenumberedusers.Clear(user.ID)
724}
725
726func loadchatter(userid int64) []*Chatter {
727 duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
728 rows, err := stmtLoadChonks.Query(userid, duedt)
729 if err != nil {
730 elog.Printf("error loading chonks: %s", err)
731 return nil
732 }
733 defer rows.Close()
734 chonks := make(map[string][]*Chonk)
735 var allchonks []*Chonk
736 for rows.Next() {
737 ch := new(Chonk)
738 var dt string
739 err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
740 if err != nil {
741 elog.Printf("error scanning chonk: %s", err)
742 continue
743 }
744 ch.Date, _ = time.Parse(dbtimeformat, dt)
745 chonks[ch.Target] = append(chonks[ch.Target], ch)
746 allchonks = append(allchonks, ch)
747 }
748 donksforchonks(allchonks)
749 rows.Close()
750 rows, err = stmtGetChatters.Query(userid)
751 if err != nil {
752 elog.Printf("error getting chatters: %s", err)
753 return nil
754 }
755 for rows.Next() {
756 var target string
757 err = rows.Scan(&target)
758 if err != nil {
759 elog.Printf("error scanning chatter: %s", target)
760 continue
761 }
762 if _, ok := chonks[target]; !ok {
763 chonks[target] = []*Chonk{}
764
765 }
766 }
767 var chatter []*Chatter
768 for target, chonks := range chonks {
769 chatter = append(chatter, &Chatter{
770 Target: target,
771 Chonks: chonks,
772 })
773 }
774 sort.Slice(chatter, func(i, j int) bool {
775 a, b := chatter[i], chatter[j]
776 if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
777 if len(a.Chonks) == len(b.Chonks) {
778 return a.Target < b.Target
779 }
780 return len(a.Chonks) > len(b.Chonks)
781 }
782 return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
783 })
784
785 return chatter
786}
787
788func (honk *Honk) Plain() string {
789 return honktoplain(honk, false)
790}
791
792func (honk *Honk) VeryPlain() string {
793 return honktoplain(honk, true)
794}
795
796func honktoplain(honk *Honk, very bool) string {
797 var plain []string
798 var filt htfilter.Filter
799 if !very {
800 filt.WithLinks = true
801 }
802 if honk.Precis != "" {
803 t, _ := filt.TextOnly(honk.Precis)
804 plain = append(plain, t)
805 }
806 if honk.Format == "html" {
807 t, _ := filt.TextOnly(honk.Noise)
808 plain = append(plain, t)
809 } else {
810 plain = append(plain, honk.Noise)
811 }
812 for _, d := range honk.Donks {
813 plain = append(plain, d.Name)
814 plain = append(plain, d.Desc)
815 }
816 for _, o := range honk.Onts {
817 plain = append(plain, o)
818 }
819 return strings.Join(plain, " ")
820}
821
822func savehonk(h *Honk) error {
823 dt := h.Date.UTC().Format(dbtimeformat)
824 aud := strings.Join(h.Audience, " ")
825
826 db := opendatabase()
827 tx, err := db.Begin()
828 if err != nil {
829 elog.Printf("can't begin tx: %s", err)
830 return err
831 }
832 plain := h.Plain()
833
834 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
835 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
836 h.Oonker, h.Flags, plain)
837 if err == nil {
838 h.ID, _ = res.LastInsertId()
839 err = saveextras(tx, h)
840 }
841 if err == nil {
842 if h.Whofore == 1 {
843 dlog.Printf("another one for me: %s", h.XID)
844 meplusone(tx, h.UserID)
845 }
846 err = tx.Commit()
847 } else {
848 tx.Rollback()
849 }
850 if err != nil {
851 elog.Printf("error saving honk: %s", err)
852 }
853 honkhonkline()
854 return err
855}
856
857func updatehonk(h *Honk) error {
858 old := getxonk(h.UserID, h.XID)
859 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
860 dt := h.Date.UTC().Format(dbtimeformat)
861
862 db := opendatabase()
863 tx, err := db.Begin()
864 if err != nil {
865 elog.Printf("can't begin tx: %s", err)
866 return err
867 }
868 plain := h.Plain()
869
870 err = deleteextras(tx, h.ID, false)
871 if err == nil {
872 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, plain, h.ID)
873 }
874 if err == nil {
875 err = saveextras(tx, h)
876 }
877 if err == nil {
878 var j string
879 j, err = jsonify(&oldrev)
880 if err == nil {
881 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
882 }
883 if err != nil {
884 elog.Printf("error saving oldrev: %s", err)
885 }
886 }
887 if err == nil {
888 err = tx.Commit()
889 } else {
890 tx.Rollback()
891 }
892 if err != nil {
893 elog.Printf("error updating honk %d: %s", h.ID, err)
894 }
895 return err
896}
897
898func deletehonk(honkid int64) error {
899 db := opendatabase()
900 tx, err := db.Begin()
901 if err != nil {
902 elog.Printf("can't begin tx: %s", err)
903 return err
904 }
905
906 err = deleteextras(tx, honkid, true)
907 if err == nil {
908 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
909 }
910 if err == nil {
911 err = tx.Commit()
912 } else {
913 tx.Rollback()
914 }
915 if err != nil {
916 elog.Printf("error deleting honk %d: %s", honkid, err)
917 }
918 return err
919}
920
921func saveextras(tx *sql.Tx, h *Honk) error {
922 for _, d := range h.Donks {
923 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
924 if err != nil {
925 elog.Printf("error saving donk: %s", err)
926 return err
927 }
928 }
929 for _, o := range h.Onts {
930 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
931 if err != nil {
932 elog.Printf("error saving ont: %s", err)
933 return err
934 }
935 }
936 if p := h.Place; p != nil {
937 j, err := jsonify(p)
938 if err == nil {
939 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
940 }
941 if err != nil {
942 elog.Printf("error saving place: %s", err)
943 return err
944 }
945 }
946 if t := h.Time; t != nil {
947 j, err := jsonify(t)
948 if err == nil {
949 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
950 }
951 if err != nil {
952 elog.Printf("error saving time: %s", err)
953 return err
954 }
955 }
956 if m := h.Mentions; len(m) > 0 {
957 j, err := jsonify(m)
958 if err == nil {
959 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
960 }
961 if err != nil {
962 elog.Printf("error saving mentions: %s", err)
963 return err
964 }
965 }
966 return nil
967}
968
969var baxonker sync.Mutex
970
971func addreaction(user *WhatAbout, xid string, who, react string) {
972 baxonker.Lock()
973 defer baxonker.Unlock()
974 h := getxonk(user.ID, xid)
975 if h == nil {
976 return
977 }
978 h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
979 j, _ := jsonify(h.Badonks)
980 db := opendatabase()
981 tx, _ := db.Begin()
982 _, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
983 _, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
984 tx.Commit()
985}
986
987func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
988 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
989 if err != nil {
990 return err
991 }
992 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
993 if err != nil {
994 return err
995 }
996 if everything {
997 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
998 } else {
999 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
1000 }
1001 if err != nil {
1002 return err
1003 }
1004 return nil
1005}
1006
1007func jsonify(what interface{}) (string, error) {
1008 var buf bytes.Buffer
1009 e := json.NewEncoder(&buf)
1010 e.SetEscapeHTML(false)
1011 e.SetIndent("", "")
1012 err := e.Encode(what)
1013 return buf.String(), err
1014}
1015
1016func unjsonify(s string, dest interface{}) error {
1017 d := json.NewDecoder(strings.NewReader(s))
1018 err := d.Decode(dest)
1019 return err
1020}
1021
1022func getxonker(what, flav string) string {
1023 var res string
1024 row := stmtGetXonker.QueryRow(what, flav)
1025 row.Scan(&res)
1026 return res
1027}
1028
1029func savexonker(what, value, flav, when string) {
1030 stmtSaveXonker.Exec(what, value, flav, when)
1031}
1032
1033func savehonker(user *WhatAbout, url, name, flavor, combos, mj string) (int64, error) {
1034 var owner string
1035 if url[0] == '#' {
1036 flavor = "peep"
1037 if name == "" {
1038 name = url[1:]
1039 }
1040 owner = url
1041 } else {
1042 info, err := investigate(url)
1043 if err != nil {
1044 ilog.Printf("failed to investigate honker: %s", err)
1045 return 0, err
1046 }
1047 url = info.XID
1048 if name == "" {
1049 name = info.Name
1050 }
1051 owner = info.Owner
1052 }
1053
1054 var x string
1055 db := opendatabase()
1056 row := db.QueryRow("select xid from honkers where xid = ? and userid = ? and flavor in ('sub', 'unsub', 'peep')", url, user.ID)
1057 err := row.Scan(&x)
1058 if err != sql.ErrNoRows {
1059 if err != nil {
1060 elog.Printf("honker scan err: %s", err)
1061 } else {
1062 err = fmt.Errorf("it seems you are already subscribed to them")
1063 }
1064 return 0, err
1065 }
1066
1067 res, err := stmtSaveHonker.Exec(user.ID, name, url, flavor, combos, owner, mj)
1068 if err != nil {
1069 elog.Print(err)
1070 return 0, err
1071 }
1072 honkerid, _ := res.LastInsertId()
1073 return honkerid, nil
1074}
1075
1076func cleanupdb(arg string) {
1077 db := opendatabase()
1078 days, err := strconv.Atoi(arg)
1079 var sqlargs []interface{}
1080 var where string
1081 if err != nil {
1082 honker := arg
1083 expdate := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
1084 where = "dt < ? and honker = ?"
1085 sqlargs = append(sqlargs, expdate)
1086 sqlargs = append(sqlargs, honker)
1087 } else {
1088 expdate := time.Now().Add(-time.Duration(days) * 24 * time.Hour).UTC().Format(dbtimeformat)
1089 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
1090 sqlargs = append(sqlargs, expdate)
1091 }
1092 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
1093 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
1094 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
1095 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
1096
1097 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
1098 for _, u := range allusers() {
1099 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)
1100 }
1101
1102 filexids := make(map[string]bool)
1103 blobdb := openblobdb()
1104 rows, err := blobdb.Query("select xid from filedata")
1105 if err != nil {
1106 elog.Fatal(err)
1107 }
1108 for rows.Next() {
1109 var xid string
1110 err = rows.Scan(&xid)
1111 if err != nil {
1112 elog.Fatal(err)
1113 }
1114 filexids[xid] = true
1115 }
1116 rows.Close()
1117 rows, err = db.Query("select xid from filemeta")
1118 for rows.Next() {
1119 var xid string
1120 err = rows.Scan(&xid)
1121 if err != nil {
1122 elog.Fatal(err)
1123 }
1124 delete(filexids, xid)
1125 }
1126 rows.Close()
1127 tx, err := blobdb.Begin()
1128 if err != nil {
1129 elog.Fatal(err)
1130 }
1131 for xid := range filexids {
1132 _, err = tx.Exec("delete from filedata where xid = ?", xid)
1133 if err != nil {
1134 elog.Fatal(err)
1135 }
1136 }
1137 err = tx.Commit()
1138 if err != nil {
1139 elog.Fatal(err)
1140 }
1141}
1142
1143var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1144var stmtDeleteHonker *sql.Stmt
1145var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1146var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1147var stmtHonksFromLongAgo *sql.Stmt
1148var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1149var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1150var stmtFindFile, stmtFindFileId, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1151var stmtCheckFileData *sql.Stmt
1152var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1153var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1154var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker, stmtDeleteOldXonkers *sql.Stmt
1155var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1156var stmtHonksForUserFirstClass *sql.Stmt
1157var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1158var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1159var stmtGetTracks *sql.Stmt
1160var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1161var stmtDeliquentCheck, stmtDeliquentUpdate *sql.Stmt
1162
1163func preparetodie(db *sql.DB, s string) *sql.Stmt {
1164 stmt, err := db.Prepare(s)
1165 if err != nil {
1166 elog.Fatalf("error %s: %s", err, s)
1167 }
1168 return stmt
1169}
1170
1171func prepareStatements(db *sql.DB) {
1172 stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos, meta from honkers where userid = ? and (flavor = 'presub' or flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
1173 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1174 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1175 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1176 stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1177 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1178 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1179 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1180
1181 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 "
1182 limit := " order by honks.honkid desc limit 250"
1183 smalllimit := " order by honks.honkid desc limit ?"
1184 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1185 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1186 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? and what <> 'bonk' order by honks.honkid asc")
1187 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1188 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1189 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1190 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1191 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1192 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1193 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (rid = '' or what = 'bonk')"+myhonkers+butnotthose+limit)
1194 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+smalllimit)
1195 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
1196 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1197 stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.honkid > ? and honks.userid = ? and honkers.name = ?"+butnotthose+limit)
1198 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1199 stmtHonksByCombo = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and honks.honker in (select xid from honkers where honkers.userid = ? and honkers.combos like ?) "+butnotthose+" union "+selecthonks+"join onts on honks.honkid = onts.honkid where honks.honkid > ? and honks.userid = ? and onts.ontology in (select xid from honkers where combos like ?)"+butnotthose+limit)
1200 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1201 stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where honks.honkid > ? and onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
1202
1203 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1204 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1205 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1206 stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1207 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, plain) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1208 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1209 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ?, plain = ? where honkid = ?")
1210 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1211 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1212 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1213 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1214 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1215 blobdb := openblobdb()
1216 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1217 stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1218 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1219 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1220 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1221 stmtFindFileId = preparetodie(db, "select xid, local, description from filemeta where fileid = ? and url = ? and local = 1")
1222 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1223 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1224 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1225 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1226 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1227 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1228 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1229 stmtUntagged = preparetodie(db, "select xid, rid, flags from (select honkid, xid, rid, flags from honks where userid = ? order by honkid desc limit 10000) order by honkid asc")
1230 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1231 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1232 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1233 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1234 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1235 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1236 stmtDeleteOldXonkers = preparetodie(db, "delete from xonkers where flavor = ? and dt < ?")
1237 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")
1238 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1239 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1240 stmtAllOnts = preparetodie(db, "select ontology, count(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2) group by ontology")
1241 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1242 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1243 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1244 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1245 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1246 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1247 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1248 stmtDeliquentCheck = preparetodie(db, "select dooverid, msg from doovers where userid = ? and rcpt = ?")
1249 stmtDeliquentUpdate = preparetodie(db, "update doovers set msg = ? where dooverid = ?")
1250}