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