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