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