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