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