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