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