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