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