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