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
39var honkwindow time.Duration = 30
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, 250)
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, negate+"whofore = 1")
311 continue
312 }
313 if t == "@self" {
314 queries = append(queries, negate+"(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 return honktoplain(honk, false)
796}
797
798func (honk *Honk) VeryPlain() string {
799 return honktoplain(honk, true)
800}
801
802func honktoplain(honk *Honk, very bool) string {
803 var plain []string
804 var filt htfilter.Filter
805 if !very {
806 filt.WithLinks = true
807 }
808 if honk.Precis != "" {
809 t, _ := filt.TextOnly(honk.Precis)
810 plain = append(plain, t)
811 }
812 if honk.Format == "html" {
813 t, _ := filt.TextOnly(honk.Noise)
814 plain = append(plain, t)
815 } else {
816 plain = append(plain, honk.Noise)
817 }
818 for _, d := range honk.Donks {
819 plain = append(plain, d.Name)
820 plain = append(plain, d.Desc)
821 }
822 for _, o := range honk.Onts {
823 plain = append(plain, o)
824 }
825 return strings.Join(plain, " ")
826}
827
828func savehonk(h *Honk) error {
829 dt := h.Date.UTC().Format(dbtimeformat)
830 aud := strings.Join(h.Audience, " ")
831
832 db := opendatabase()
833 tx, err := db.Begin()
834 if err != nil {
835 elog.Printf("can't begin tx: %s", err)
836 return err
837 }
838 plain := h.Plain()
839
840 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
841 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
842 h.Oonker, h.Flags, plain)
843 if err == nil {
844 h.ID, _ = res.LastInsertId()
845 err = saveextras(tx, h)
846 }
847 if err == nil {
848 if h.Whofore == 1 {
849 meplusone(tx, h.UserID)
850 }
851 err = tx.Commit()
852 } else {
853 tx.Rollback()
854 }
855 if err != nil {
856 elog.Printf("error saving honk: %s", err)
857 }
858 honkhonkline()
859 return err
860}
861
862func updatehonk(h *Honk) error {
863 old := getxonk(h.UserID, h.XID)
864 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
865 dt := h.Date.UTC().Format(dbtimeformat)
866
867 db := opendatabase()
868 tx, err := db.Begin()
869 if err != nil {
870 elog.Printf("can't begin tx: %s", err)
871 return err
872 }
873 plain := h.Plain()
874
875 err = deleteextras(tx, h.ID, false)
876 if err == nil {
877 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, plain, h.ID)
878 }
879 if err == nil {
880 err = saveextras(tx, h)
881 }
882 if err == nil {
883 var j string
884 j, err = jsonify(&oldrev)
885 if err == nil {
886 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
887 }
888 if err != nil {
889 elog.Printf("error saving oldrev: %s", err)
890 }
891 }
892 if err == nil {
893 err = tx.Commit()
894 } else {
895 tx.Rollback()
896 }
897 if err != nil {
898 elog.Printf("error updating honk %d: %s", h.ID, err)
899 }
900 return err
901}
902
903func deletehonk(honkid int64) error {
904 db := opendatabase()
905 tx, err := db.Begin()
906 if err != nil {
907 elog.Printf("can't begin tx: %s", err)
908 return err
909 }
910
911 err = deleteextras(tx, honkid, true)
912 if err == nil {
913 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
914 }
915 if err == nil {
916 err = tx.Commit()
917 } else {
918 tx.Rollback()
919 }
920 if err != nil {
921 elog.Printf("error deleting honk %d: %s", honkid, err)
922 }
923 return err
924}
925
926func saveextras(tx *sql.Tx, h *Honk) error {
927 for _, d := range h.Donks {
928 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
929 if err != nil {
930 elog.Printf("error saving donk: %s", err)
931 return err
932 }
933 }
934 for _, o := range h.Onts {
935 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
936 if err != nil {
937 elog.Printf("error saving ont: %s", err)
938 return err
939 }
940 }
941 if p := h.Place; p != nil {
942 j, err := jsonify(p)
943 if err == nil {
944 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
945 }
946 if err != nil {
947 elog.Printf("error saving place: %s", err)
948 return err
949 }
950 }
951 if t := h.Time; t != nil {
952 j, err := jsonify(t)
953 if err == nil {
954 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
955 }
956 if err != nil {
957 elog.Printf("error saving time: %s", err)
958 return err
959 }
960 }
961 if m := h.Mentions; len(m) > 0 {
962 j, err := jsonify(m)
963 if err == nil {
964 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
965 }
966 if err != nil {
967 elog.Printf("error saving mentions: %s", err)
968 return err
969 }
970 }
971 return nil
972}
973
974var baxonker sync.Mutex
975
976func addreaction(user *WhatAbout, xid string, who, react string) {
977 baxonker.Lock()
978 defer baxonker.Unlock()
979 h := getxonk(user.ID, xid)
980 if h == nil {
981 return
982 }
983 h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
984 j, _ := jsonify(h.Badonks)
985 db := opendatabase()
986 tx, _ := db.Begin()
987 _, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
988 _, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
989 tx.Commit()
990}
991
992func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
993 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
994 if err != nil {
995 return err
996 }
997 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
998 if err != nil {
999 return err
1000 }
1001 if everything {
1002 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
1003 } else {
1004 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
1005 }
1006 if err != nil {
1007 return err
1008 }
1009 return nil
1010}
1011
1012func jsonify(what interface{}) (string, error) {
1013 var buf bytes.Buffer
1014 e := json.NewEncoder(&buf)
1015 e.SetEscapeHTML(false)
1016 e.SetIndent("", "")
1017 err := e.Encode(what)
1018 return buf.String(), err
1019}
1020
1021func unjsonify(s string, dest interface{}) error {
1022 d := json.NewDecoder(strings.NewReader(s))
1023 err := d.Decode(dest)
1024 return err
1025}
1026
1027func getxonker(what, flav string) string {
1028 var res string
1029 row := stmtGetXonker.QueryRow(what, flav)
1030 row.Scan(&res)
1031 return res
1032}
1033
1034func savexonker(what, value, flav, when string) {
1035 stmtSaveXonker.Exec(what, value, flav, when)
1036}
1037
1038func savehonker(user *WhatAbout, url, name, flavor, combos, mj string) (int64, error) {
1039 var owner string
1040 if url[0] == '#' {
1041 flavor = "peep"
1042 if name == "" {
1043 name = url[1:]
1044 }
1045 owner = url
1046 } else {
1047 info, err := investigate(url)
1048 if err != nil {
1049 ilog.Printf("failed to investigate honker: %s", err)
1050 return 0, err
1051 }
1052 url = info.XID
1053 if name == "" {
1054 name = info.Name
1055 }
1056 owner = info.Owner
1057 }
1058
1059 var x string
1060 db := opendatabase()
1061 row := db.QueryRow("select xid from honkers where xid = ? and userid = ? and flavor in ('sub', 'unsub', 'peep')", url, user.ID)
1062 err := row.Scan(&x)
1063 if err != sql.ErrNoRows {
1064 if err != nil {
1065 elog.Printf("honker scan err: %s", err)
1066 } else {
1067 err = fmt.Errorf("it seems you are already subscribed to them")
1068 }
1069 return 0, err
1070 }
1071
1072 res, err := stmtSaveHonker.Exec(user.ID, name, url, flavor, combos, owner, mj)
1073 if err != nil {
1074 elog.Print(err)
1075 return 0, err
1076 }
1077 honkerid, _ := res.LastInsertId()
1078 return honkerid, nil
1079}
1080
1081func cleanupdb(arg string) {
1082 db := opendatabase()
1083 days, err := strconv.Atoi(arg)
1084 var sqlargs []interface{}
1085 var where string
1086 if err != nil {
1087 honker := arg
1088 expdate := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
1089 where = "dt < ? and honker = ?"
1090 sqlargs = append(sqlargs, expdate)
1091 sqlargs = append(sqlargs, honker)
1092 } else {
1093 expdate := time.Now().Add(-time.Duration(days) * 24 * time.Hour).UTC().Format(dbtimeformat)
1094 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
1095 sqlargs = append(sqlargs, expdate)
1096 }
1097 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
1098 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
1099 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
1100 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
1101
1102 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
1103 for _, u := range allusers() {
1104 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)
1105 }
1106
1107 filexids := make(map[string]bool)
1108 blobdb := openblobdb()
1109 rows, err := blobdb.Query("select xid from filedata")
1110 if err != nil {
1111 elog.Fatal(err)
1112 }
1113 for rows.Next() {
1114 var xid string
1115 err = rows.Scan(&xid)
1116 if err != nil {
1117 elog.Fatal(err)
1118 }
1119 filexids[xid] = true
1120 }
1121 rows.Close()
1122 rows, err = db.Query("select xid from filemeta")
1123 for rows.Next() {
1124 var xid string
1125 err = rows.Scan(&xid)
1126 if err != nil {
1127 elog.Fatal(err)
1128 }
1129 delete(filexids, xid)
1130 }
1131 rows.Close()
1132 tx, err := blobdb.Begin()
1133 if err != nil {
1134 elog.Fatal(err)
1135 }
1136 for xid := range filexids {
1137 _, err = tx.Exec("delete from filedata where xid = ?", xid)
1138 if err != nil {
1139 elog.Fatal(err)
1140 }
1141 }
1142 err = tx.Commit()
1143 if err != nil {
1144 elog.Fatal(err)
1145 }
1146}
1147
1148func getusercount() int {
1149 row := stmtGetUserCount.QueryRow()
1150 var count int
1151 row.Scan(&count)
1152 return count
1153}
1154
1155func getactiveusercount(monthsago int) int {
1156 origin := time.Now().AddDate(0, -monthsago, 0).UTC().Format(dbtimeformat)
1157 row := stmtGetActiveUserCount.QueryRow(origin)
1158
1159 var count int
1160 row.Scan(&count)
1161 return count
1162}
1163
1164func getlocalhonkcount() int {
1165 row := stmtGetLocalHonkCount.QueryRow()
1166
1167 var count int
1168 row.Scan(&count)
1169 return count
1170}
1171
1172var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1173var stmtDeleteHonker *sql.Stmt
1174var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1175var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1176var stmtHonksFromLongAgo *sql.Stmt
1177var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1178var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1179var stmtFindFile, stmtFindFileId, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1180var stmtCheckFileData *sql.Stmt
1181var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1182var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1183var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker, stmtDeleteOldXonkers *sql.Stmt
1184var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1185var stmtHonksForUserFirstClass *sql.Stmt
1186var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1187var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1188var stmtGetTracks *sql.Stmt
1189var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1190var stmtDeliquentCheck, stmtDeliquentUpdate *sql.Stmt
1191var stmtGetUserCount *sql.Stmt
1192var stmtGetActiveUserCount *sql.Stmt
1193var stmtGetLocalHonkCount *sql.Stmt
1194var stmtSaveMastoApp *sql.Stmt
1195var stmtCheckClientId *sql.Stmt
1196var stmtSaveMastoAppToken *sql.Stmt
1197
1198func preparetodie(db *sql.DB, s string) *sql.Stmt {
1199 stmt, err := db.Prepare(s)
1200 if err != nil {
1201 elog.Fatalf("error %s: %s", err, s)
1202 }
1203 return stmt
1204}
1205
1206func prepareStatements(db *sql.DB) {
1207 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")
1208 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1209 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1210 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1211 stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1212 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1213 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1214 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1215
1216 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 "
1217 limit := " order by honks.honkid desc limit 250"
1218 smalllimit := " order by honks.honkid desc limit ?"
1219 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1220 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1221 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? and what <> 'bonk' order by honks.honkid asc")
1222 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1223 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1224 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1225 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1226 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1227 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1228 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (rid = '' or what = 'bonk')"+myhonkers+butnotthose+limit)
1229 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+smalllimit)
1230 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
1231 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1232 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)
1233 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1234 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)
1235 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1236 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)
1237
1238 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1239 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1240 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1241 stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1242 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, plain) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1243 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1244 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ?, plain = ? where honkid = ?")
1245 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1246 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1247 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1248 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1249 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1250 blobdb := openblobdb()
1251 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1252 stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1253 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1254 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1255 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1256 stmtFindFileId = preparetodie(db, "select xid, local, description from filemeta where fileid = ? and url = ? and local = 1")
1257 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1258 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1259 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1260 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1261 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1262 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1263 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1264 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")
1265 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1266 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1267 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1268 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1269 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1270 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1271 stmtDeleteOldXonkers = preparetodie(db, "delete from xonkers where flavor = ? and dt < ?")
1272 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")
1273 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1274 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1275 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")
1276 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1277 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1278 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1279 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1280 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1281 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1282 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1283 stmtDeliquentCheck = preparetodie(db, "select dooverid, msg from doovers where userid = ? and rcpt = ?")
1284 stmtDeliquentUpdate = preparetodie(db, "update doovers set msg = ? where dooverid = ?")
1285 stmtGetUserCount = preparetodie(db, "select count(*) from users where userid > 0")
1286 stmtGetActiveUserCount = preparetodie(db, "select count(distinct honker) from honks where whofore = 2 and dt > ?")
1287 stmtGetLocalHonkCount = preparetodie(db, "select count(*) from honks where whofore = 2")
1288
1289 stmtSaveMastoApp = preparetodie(db, "insert into masto (clientname, redirecturis, scopes, clientid, clientsecret, vapidkey, authtoken) values (?, ?, ?, ?, ?, ?, ?)")
1290 stmtSaveMastoAppToken = preparetodie(db, "update masto set authtoken = ?")
1291 stmtCheckClientId = preparetodie(db, "select clientid from masto where clientid = ?")
1292}