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 dlog.Printf("meta load %s %s", genus, j)
482 switch genus {
483 case "place":
484 p := new(Place)
485 err = unjsonify(j, p)
486 if err != nil {
487 elog.Printf("error parsing place: %s", err)
488 continue
489 }
490 h.Place = p
491 case "time":
492 t := new(Time)
493 err = unjsonify(j, t)
494 if err != nil {
495 elog.Printf("error parsing time: %s", err)
496 continue
497 }
498 h.Time = t
499 case "mentions":
500 err = unjsonify(j, &h.Mentions)
501 if err != nil {
502 elog.Printf("error parsing mentions: %s", err)
503 continue
504 }
505 case "badonks":
506 err = unjsonify(j, &h.Badonks)
507 if err != nil {
508 elog.Printf("error parsing badonks: %s", err)
509 continue
510 }
511 case "seealso":
512 h.SeeAlso = j
513 case "onties":
514 h.Onties = j
515 case "link":
516 h.Link = j
517 case "legalname":
518 h.LegalName = j
519 case "oldrev":
520 default:
521 elog.Printf("unknown meta genus: %s", genus)
522 }
523 }
524 rows.Close()
525}
526
527func donksforchonks(chonks []*Chonk) {
528 db := opendatabase()
529 var ids []string
530 chmap := make(map[int64]*Chonk)
531 for _, ch := range chonks {
532 ids = append(ids, fmt.Sprintf("%d", ch.ID))
533 chmap[ch.ID] = ch
534 }
535 idset := strings.Join(ids, ",")
536 // grab donks
537 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)
538 rows, err := db.Query(q)
539 if err != nil {
540 elog.Printf("error querying donks: %s", err)
541 return
542 }
543 defer rows.Close()
544 for rows.Next() {
545 var chid int64
546 d := new(Donk)
547 err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
548 if err != nil {
549 elog.Printf("error scanning donk: %s", err)
550 continue
551 }
552 ch := chmap[chid]
553 ch.Donks = append(ch.Donks, d)
554 }
555}
556
557func savefile(name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
558 fileid, _, err := savefileandxid(name, desc, url, media, local, data)
559 return fileid, err
560}
561
562func hashfiledata(data []byte) string {
563 h := sha512.New512_256()
564 h.Write(data)
565 return fmt.Sprintf("%x", h.Sum(nil))
566}
567
568func savefileandxid(name string, desc string, url string, media string, local bool, data []byte) (int64, string, error) {
569 var xid string
570 if local {
571 hash := hashfiledata(data)
572 row := stmtCheckFileData.QueryRow(hash)
573 err := row.Scan(&xid)
574 if err == sql.ErrNoRows {
575 xid = xfiltrate()
576 switch media {
577 case "image/png":
578 xid += ".png"
579 case "image/jpeg":
580 xid += ".jpg"
581 case "image/svg+xml":
582 xid += ".svg"
583 case "application/pdf":
584 xid += ".pdf"
585 case "text/plain":
586 xid += ".txt"
587 }
588 _, err = stmtSaveFileData.Exec(xid, media, hash, data)
589 if err != nil {
590 return 0, "", err
591 }
592 } else if err != nil {
593 elog.Printf("error checking file hash: %s", err)
594 return 0, "", err
595 }
596 if url == "" {
597 url = serverURL("/d/%s", xid)
598 }
599 }
600
601 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
602 if err != nil {
603 return 0, "", err
604 }
605 fileid, _ := res.LastInsertId()
606 return fileid, xid, nil
607}
608
609func finddonkid(fileid int64, url string) *Donk {
610 donk := new(Donk)
611 row := stmtFindFileId.QueryRow(fileid, url)
612 err := row.Scan(&donk.XID, &donk.Local, &donk.Desc)
613 if err == nil {
614 donk.FileID = fileid
615 return donk
616 }
617 if err != sql.ErrNoRows {
618 elog.Printf("error finding file: %s", err)
619 }
620 return nil
621}
622
623func finddonk(url string) *Donk {
624 donk := new(Donk)
625 row := stmtFindFile.QueryRow(url)
626 err := row.Scan(&donk.FileID, &donk.XID)
627 if err == nil {
628 return donk
629 }
630 if err != sql.ErrNoRows {
631 elog.Printf("error finding file: %s", err)
632 }
633 return nil
634}
635
636func savechonk(ch *Chonk) error {
637 dt := ch.Date.UTC().Format(dbtimeformat)
638 db := opendatabase()
639 tx, err := db.Begin()
640 if err != nil {
641 elog.Printf("can't begin tx: %s", err)
642 return err
643 }
644
645 res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
646 if err == nil {
647 ch.ID, _ = res.LastInsertId()
648 for _, d := range ch.Donks {
649 _, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
650 if err != nil {
651 elog.Printf("error saving donk: %s", err)
652 break
653 }
654 }
655 chatplusone(tx, ch.UserID)
656 err = tx.Commit()
657 } else {
658 tx.Rollback()
659 }
660 return err
661}
662
663func chatplusone(tx *sql.Tx, userid int64) {
664 user, ok := somenumberedusers.Get(userid)
665 if !ok {
666 return
667 }
668 options := user.Options
669 options.ChatCount += 1
670 j, err := jsonify(options)
671 if err == nil {
672 _, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
673 }
674 if err != nil {
675 elog.Printf("error plussing chat: %s", err)
676 }
677 somenamedusers.Clear(user.Name)
678 somenumberedusers.Clear(user.ID)
679}
680
681func chatnewnone(userid int64) {
682 user, ok := somenumberedusers.Get(userid)
683 if !ok || user.Options.ChatCount == 0 {
684 return
685 }
686 options := user.Options
687 options.ChatCount = 0
688 j, err := jsonify(options)
689 if err == nil {
690 db := opendatabase()
691 _, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
692 }
693 if err != nil {
694 elog.Printf("error noneing chat: %s", err)
695 }
696 somenamedusers.Clear(user.Name)
697 somenumberedusers.Clear(user.ID)
698}
699
700func meplusone(tx *sql.Tx, userid int64) {
701 user, ok := somenumberedusers.Get(userid)
702 if !ok {
703 return
704 }
705 options := user.Options
706 options.MeCount += 1
707 j, err := jsonify(options)
708 if err == nil {
709 _, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
710 }
711 if err != nil {
712 elog.Printf("error plussing me: %s", err)
713 }
714 somenamedusers.Clear(user.Name)
715 somenumberedusers.Clear(user.ID)
716}
717
718func menewnone(userid int64) {
719 user, ok := somenumberedusers.Get(userid)
720 if !ok || user.Options.MeCount == 0 {
721 return
722 }
723 options := user.Options
724 options.MeCount = 0
725 j, err := jsonify(options)
726 if err == nil {
727 db := opendatabase()
728 _, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
729 }
730 if err != nil {
731 elog.Printf("error noneing me: %s", err)
732 }
733 somenamedusers.Clear(user.Name)
734 somenumberedusers.Clear(user.ID)
735}
736
737func loadchatter(userid int64) []*Chatter {
738 duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
739 rows, err := stmtLoadChonks.Query(userid, duedt)
740 if err != nil {
741 elog.Printf("error loading chonks: %s", err)
742 return nil
743 }
744 defer rows.Close()
745 chonks := make(map[string][]*Chonk)
746 var allchonks []*Chonk
747 for rows.Next() {
748 ch := new(Chonk)
749 var dt string
750 err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
751 if err != nil {
752 elog.Printf("error scanning chonk: %s", err)
753 continue
754 }
755 ch.Date, _ = time.Parse(dbtimeformat, dt)
756 chonks[ch.Target] = append(chonks[ch.Target], ch)
757 allchonks = append(allchonks, ch)
758 }
759 donksforchonks(allchonks)
760 rows.Close()
761 rows, err = stmtGetChatters.Query(userid)
762 if err != nil {
763 elog.Printf("error getting chatters: %s", err)
764 return nil
765 }
766 for rows.Next() {
767 var target string
768 err = rows.Scan(&target)
769 if err != nil {
770 elog.Printf("error scanning chatter: %s", target)
771 continue
772 }
773 if _, ok := chonks[target]; !ok {
774 chonks[target] = []*Chonk{}
775
776 }
777 }
778 var chatter []*Chatter
779 for target, chonks := range chonks {
780 chatter = append(chatter, &Chatter{
781 Target: target,
782 Chonks: chonks,
783 })
784 }
785 sort.Slice(chatter, func(i, j int) bool {
786 a, b := chatter[i], chatter[j]
787 if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
788 if len(a.Chonks) == len(b.Chonks) {
789 return a.Target < b.Target
790 }
791 return len(a.Chonks) > len(b.Chonks)
792 }
793 return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
794 })
795
796 return chatter
797}
798
799func (honk *Honk) Plain() string {
800 return honktoplain(honk, false)
801}
802
803func (honk *Honk) VeryPlain() string {
804 return honktoplain(honk, true)
805}
806
807func honktoplain(honk *Honk, very bool) string {
808 var plain []string
809 var filt htfilter.Filter
810 if !very {
811 filt.WithLinks = true
812 }
813 if honk.Precis != "" {
814 t, _ := filt.TextOnly(honk.Precis)
815 plain = append(plain, t)
816 }
817 if honk.Format == "html" {
818 t, _ := filt.TextOnly(honk.Noise)
819 plain = append(plain, t)
820 } else {
821 plain = append(plain, honk.Noise)
822 }
823 for _, d := range honk.Donks {
824 plain = append(plain, d.Name)
825 plain = append(plain, d.Desc)
826 }
827 for _, o := range honk.Onts {
828 plain = append(plain, o)
829 }
830 return strings.Join(plain, " ")
831}
832
833func savehonk(h *Honk) error {
834 dt := h.Date.UTC().Format(dbtimeformat)
835 aud := strings.Join(h.Audience, " ")
836
837 db := opendatabase()
838 tx, err := db.Begin()
839 if err != nil {
840 elog.Printf("can't begin tx: %s", err)
841 return err
842 }
843 plain := h.Plain()
844
845 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
846 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
847 h.Oonker, h.Flags, plain)
848 if err == nil {
849 h.ID, _ = res.LastInsertId()
850 err = saveextras(tx, h)
851 }
852 if err == nil {
853 if h.Whofore == 1 {
854 dlog.Printf("another one for me: %s", h.XID)
855 meplusone(tx, h.UserID)
856 }
857 err = tx.Commit()
858 } else {
859 tx.Rollback()
860 }
861 if err != nil {
862 elog.Printf("error saving honk: %s", err)
863 }
864 honkhonkline()
865 return err
866}
867
868func updatehonk(h *Honk) error {
869 old := getxonk(h.UserID, h.XID)
870 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
871 dt := h.Date.UTC().Format(dbtimeformat)
872
873 db := opendatabase()
874 tx, err := db.Begin()
875 if err != nil {
876 elog.Printf("can't begin tx: %s", err)
877 return err
878 }
879 plain := h.Plain()
880
881 err = deleteextras(tx, h.ID, false)
882 if err == nil {
883 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, plain, h.ID)
884 }
885 if err == nil {
886 err = saveextras(tx, h)
887 }
888 if err == nil {
889 var j string
890 j, err = jsonify(&oldrev)
891 if err == nil {
892 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
893 }
894 if err != nil {
895 elog.Printf("error saving oldrev: %s", err)
896 }
897 }
898 if err == nil {
899 err = tx.Commit()
900 } else {
901 tx.Rollback()
902 }
903 if err != nil {
904 elog.Printf("error updating honk %d: %s", h.ID, err)
905 }
906 return err
907}
908
909func deletehonk(honkid int64) error {
910 db := opendatabase()
911 tx, err := db.Begin()
912 if err != nil {
913 elog.Printf("can't begin tx: %s", err)
914 return err
915 }
916
917 err = deleteextras(tx, honkid, true)
918 if err == nil {
919 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
920 }
921 if err == nil {
922 err = tx.Commit()
923 } else {
924 tx.Rollback()
925 }
926 if err != nil {
927 elog.Printf("error deleting honk %d: %s", honkid, err)
928 }
929 return err
930}
931
932func saveextras(tx *sql.Tx, h *Honk) error {
933 for _, d := range h.Donks {
934 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
935 if err != nil {
936 elog.Printf("error saving donk: %s", err)
937 return err
938 }
939 }
940 for _, o := range h.Onts {
941 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
942 if err != nil {
943 elog.Printf("error saving ont: %s", err)
944 return err
945 }
946 }
947 if p := h.Place; p != nil {
948 j, err := jsonify(p)
949 if err == nil {
950 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
951 }
952 if err != nil {
953 elog.Printf("error saving place: %s", err)
954 return err
955 }
956 }
957 if t := h.Time; t != nil {
958 j, err := jsonify(t)
959 if err == nil {
960 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
961 }
962 if err != nil {
963 elog.Printf("error saving time: %s", err)
964 return err
965 }
966 }
967 if m := h.Mentions; len(m) > 0 {
968 j, err := jsonify(m)
969 if err == nil {
970 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
971 }
972 if err != nil {
973 elog.Printf("error saving mentions: %s", err)
974 return err
975 }
976 }
977 if onties := h.Onties; onties != "" {
978 _, err := tx.Stmt(stmtSaveMeta).Exec(h.ID, "onties", onties)
979 if err != nil {
980 elog.Printf("error saving onties: %s", err)
981 return err
982 }
983 }
984 if legalname := h.LegalName; legalname != "" {
985 _, err := tx.Stmt(stmtSaveMeta).Exec(h.ID, "legalname", legalname)
986 if err != nil {
987 elog.Printf("error saving legalname: %s", err)
988 return err
989 }
990 }
991 if seealso := h.SeeAlso; seealso != "" {
992 _, err := tx.Stmt(stmtSaveMeta).Exec(h.ID, "seealso", seealso)
993 if err != nil {
994 elog.Printf("error saving seealso: %s", err)
995 return err
996 }
997 }
998 if link := h.Link; link != "" {
999 _, err := tx.Stmt(stmtSaveMeta).Exec(h.ID, "link", link)
1000 if err != nil {
1001 elog.Printf("error saving link: %s", err)
1002 return err
1003 }
1004 }
1005 return nil
1006}
1007
1008var baxonker sync.Mutex
1009
1010func addreaction(user *WhatAbout, xid string, who, react string) {
1011 baxonker.Lock()
1012 defer baxonker.Unlock()
1013 h := getxonk(user.ID, xid)
1014 if h == nil {
1015 return
1016 }
1017 h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
1018 j, _ := jsonify(h.Badonks)
1019 db := opendatabase()
1020 tx, _ := db.Begin()
1021 _, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
1022 _, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
1023 tx.Commit()
1024}
1025
1026func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
1027 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
1028 if err != nil {
1029 return err
1030 }
1031 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
1032 if err != nil {
1033 return err
1034 }
1035 if everything {
1036 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
1037 } else {
1038 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
1039 }
1040 if err != nil {
1041 return err
1042 }
1043 return nil
1044}
1045
1046func jsonify(what interface{}) (string, error) {
1047 var buf bytes.Buffer
1048 e := json.NewEncoder(&buf)
1049 e.SetEscapeHTML(false)
1050 e.SetIndent("", "")
1051 err := e.Encode(what)
1052 return buf.String(), err
1053}
1054
1055func unjsonify(s string, dest interface{}) error {
1056 d := json.NewDecoder(strings.NewReader(s))
1057 err := d.Decode(dest)
1058 return err
1059}
1060
1061func getxonker(what, flav string) string {
1062 var res string
1063 row := stmtGetXonker.QueryRow(what, flav)
1064 row.Scan(&res)
1065 return res
1066}
1067
1068func savexonker(what, value, flav, when string) {
1069 stmtSaveXonker.Exec(what, value, flav, when)
1070}
1071
1072func savehonker(user *WhatAbout, url, name, flavor, combos, mj string) (int64, error) {
1073 var owner string
1074 if url[0] == '#' {
1075 flavor = "peep"
1076 if name == "" {
1077 name = url[1:]
1078 }
1079 owner = url
1080 } else {
1081 info, err := investigate(url)
1082 if err != nil {
1083 ilog.Printf("failed to investigate honker: %s", err)
1084 return 0, err
1085 }
1086 url = info.XID
1087 if name == "" {
1088 name = info.Name
1089 }
1090 owner = info.Owner
1091 }
1092
1093 var x string
1094 db := opendatabase()
1095 row := db.QueryRow("select xid from honkers where xid = ? and userid = ? and flavor in ('sub', 'unsub', 'peep')", url, user.ID)
1096 err := row.Scan(&x)
1097 if err != sql.ErrNoRows {
1098 if err != nil {
1099 elog.Printf("honker scan err: %s", err)
1100 } else {
1101 err = fmt.Errorf("it seems you are already subscribed to them")
1102 }
1103 return 0, err
1104 }
1105
1106 res, err := stmtSaveHonker.Exec(user.ID, name, url, flavor, combos, owner, mj)
1107 if err != nil {
1108 elog.Print(err)
1109 return 0, err
1110 }
1111 honkerid, _ := res.LastInsertId()
1112 return honkerid, nil
1113}
1114
1115func cleanupdb(arg string) {
1116 db := opendatabase()
1117 days, err := strconv.Atoi(arg)
1118 var sqlargs []interface{}
1119 var where string
1120 if err != nil {
1121 honker := arg
1122 expdate := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
1123 where = "dt < ? and honker = ?"
1124 sqlargs = append(sqlargs, expdate)
1125 sqlargs = append(sqlargs, honker)
1126 } else {
1127 expdate := time.Now().Add(-time.Duration(days) * 24 * time.Hour).UTC().Format(dbtimeformat)
1128 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
1129 sqlargs = append(sqlargs, expdate)
1130 }
1131 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
1132 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
1133 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
1134 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
1135
1136 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
1137 for _, u := range allusers() {
1138 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)
1139 }
1140
1141 filexids := make(map[string]bool)
1142 g_blobdb = openblobdb()
1143 rows, err := g_blobdb.Query("select xid from filedata")
1144 if err != nil {
1145 elog.Fatal(err)
1146 }
1147 for rows.Next() {
1148 var xid string
1149 err = rows.Scan(&xid)
1150 if err != nil {
1151 elog.Fatal(err)
1152 }
1153 filexids[xid] = true
1154 }
1155 rows.Close()
1156 rows, err = db.Query("select xid from filemeta")
1157 for rows.Next() {
1158 var xid string
1159 err = rows.Scan(&xid)
1160 if err != nil {
1161 elog.Fatal(err)
1162 }
1163 delete(filexids, xid)
1164 }
1165 rows.Close()
1166 tx, err := g_blobdb.Begin()
1167 if err != nil {
1168 elog.Fatal(err)
1169 }
1170 for xid := range filexids {
1171 _, err = tx.Exec("delete from filedata where xid = ?", xid)
1172 if err != nil {
1173 elog.Fatal(err)
1174 }
1175 }
1176 err = tx.Commit()
1177 if err != nil {
1178 elog.Fatal(err)
1179 }
1180 closedatabases()
1181}
1182
1183var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1184var stmtDeleteHonker *sql.Stmt
1185var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1186var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1187var stmtHonksFromLongAgo *sql.Stmt
1188var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1189var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1190var stmtFindFile, stmtFindFileId, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1191var stmtCheckFileData *sql.Stmt
1192var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1193var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1194var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker, stmtDeleteOldXonkers *sql.Stmt
1195var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1196var stmtHonksForUserFirstClass *sql.Stmt
1197var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1198var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1199var stmtGetTracks *sql.Stmt
1200var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1201var stmtDeliquentCheck, stmtDeliquentUpdate *sql.Stmt
1202
1203func preparetodie(db *sql.DB, s string) *sql.Stmt {
1204 stmt, err := db.Prepare(s)
1205 if err != nil {
1206 elog.Fatalf("error %s: %s", err, s)
1207 }
1208 return stmt
1209}
1210
1211var g_blobdb *sql.DB
1212
1213func closedatabases() {
1214 err := alreadyopendb.Close()
1215 if err != nil {
1216 elog.Printf("error closing database: %s", err)
1217 }
1218 if g_blobdb != nil {
1219 err = g_blobdb.Close()
1220 if err != nil {
1221 elog.Printf("error closing database: %s", err)
1222 }
1223 }
1224}
1225
1226func prepareStatements(db *sql.DB) {
1227 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")
1228 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1229 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1230 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1231 stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1232 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1233 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1234 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1235
1236 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 "
1237 limit := " order by honks.honkid desc limit 250"
1238 smalllimit := " order by honks.honkid desc limit ?"
1239 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1240 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1241 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? and what <> 'bonk' order by honks.honkid asc")
1242 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1243 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1244 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1245 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1246 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1247 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1248 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (rid = '' or what = 'bonk')"+myhonkers+butnotthose+limit)
1249 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+smalllimit)
1250 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and (whofore = 2 or flags & 4)"+butnotthose+limit)
1251 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1252 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)
1253 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1254 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)
1255 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1256 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)
1257
1258 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1259 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1260 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1261 stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1262 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, plain) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1263 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1264 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ?, plain = ? where honkid = ?")
1265 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1266 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1267 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1268 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1269 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1270 g_blobdb = openblobdb()
1271 stmtSaveFileData = preparetodie(g_blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1272 stmtCheckFileData = preparetodie(g_blobdb, "select xid from filedata where hash = ?")
1273 stmtGetFileData = preparetodie(g_blobdb, "select media, content from filedata where xid = ?")
1274 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1275 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1276 stmtFindFileId = preparetodie(db, "select xid, local, description from filemeta where fileid = ? and url = ? and local = 1")
1277 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1278 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1279 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1280 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1281 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1282 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1283 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1284 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")
1285 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1286 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1287 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1288 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1289 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1290 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1291 stmtDeleteOldXonkers = preparetodie(db, "delete from xonkers where flavor = ? and dt < ?")
1292 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")
1293 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1294 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1295 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")
1296 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1297 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1298 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1299 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1300 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1301 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1302 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1303 stmtDeliquentCheck = preparetodie(db, "select dooverid, msg from doovers where userid = ? and rcpt = ?")
1304 stmtDeliquentUpdate = preparetodie(db, "update doovers set msg = ? where dooverid = ?")
1305}