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