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