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