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