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