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 "database/sql"
21 "encoding/json"
22 "fmt"
23 "log"
24 "sort"
25 "strconv"
26 "strings"
27 "time"
28
29 "humungus.tedunangst.com/r/webs/cache"
30 "humungus.tedunangst.com/r/webs/httpsig"
31 "humungus.tedunangst.com/r/webs/login"
32)
33
34func userfromrow(row *sql.Row) (*WhatAbout, error) {
35 user := new(WhatAbout)
36 var seckey, options string
37 err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &seckey, &options)
38 if err == nil {
39 user.SecKey, _, err = httpsig.DecodeKey(seckey)
40 }
41 if err != nil {
42 return nil, err
43 }
44 if user.ID > 0 {
45 user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
46 user.SkinnyCSS = strings.Contains(options, " skinny ")
47 } else {
48 user.URL = fmt.Sprintf("https://%s/%s", serverName, user.Name)
49 }
50 return user, nil
51}
52
53var somenamedusers = cache.New(cache.Options{Filler: func(name string) (*WhatAbout, bool) {
54 row := stmtUserByName.QueryRow(name)
55 user, err := userfromrow(row)
56 if err != nil {
57 return nil, false
58 }
59 return user, true
60}})
61
62var somenumberedusers = cache.New(cache.Options{Filler: func(userid int64) (*WhatAbout, bool) {
63 row := stmtUserByNumber.QueryRow(userid)
64 user, err := userfromrow(row)
65 if err != nil {
66 return nil, false
67 }
68 return user, true
69}})
70
71func getserveruser() *WhatAbout {
72 var user *WhatAbout
73 ok := somenumberedusers.Get(serverUID, &user)
74 if !ok {
75 log.Panicf("lost server user")
76 }
77 return user
78}
79
80func butwhatabout(name string) (*WhatAbout, error) {
81 var user *WhatAbout
82 ok := somenamedusers.Get(name, &user)
83 if !ok {
84 return nil, fmt.Errorf("no user: %s", name)
85 }
86 return user, nil
87}
88
89var honkerinvalidator cache.Invalidator
90
91func gethonkers(userid int64) []*Honker {
92 rows, err := stmtHonkers.Query(userid)
93 if err != nil {
94 log.Printf("error querying honkers: %s", err)
95 return nil
96 }
97 defer rows.Close()
98 var honkers []*Honker
99 for rows.Next() {
100 h := new(Honker)
101 var combos string
102 err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor, &combos)
103 h.Combos = strings.Split(strings.TrimSpace(combos), " ")
104 if err != nil {
105 log.Printf("error scanning honker: %s", err)
106 return nil
107 }
108 honkers = append(honkers, h)
109 }
110 return honkers
111}
112
113func getdubs(userid int64) []*Honker {
114 rows, err := stmtDubbers.Query(userid)
115 return dubsfromrows(rows, err)
116}
117
118func getnameddubs(userid int64, name string) []*Honker {
119 rows, err := stmtNamedDubbers.Query(userid, name)
120 return dubsfromrows(rows, err)
121}
122
123func dubsfromrows(rows *sql.Rows, err error) []*Honker {
124 if err != nil {
125 log.Printf("error querying dubs: %s", err)
126 return nil
127 }
128 defer rows.Close()
129 var honkers []*Honker
130 for rows.Next() {
131 h := new(Honker)
132 err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor)
133 if err != nil {
134 log.Printf("error scanning honker: %s", err)
135 return nil
136 }
137 honkers = append(honkers, h)
138 }
139 return honkers
140}
141
142func allusers() []login.UserInfo {
143 var users []login.UserInfo
144 rows, _ := opendatabase().Query("select userid, username from users where userid > 0")
145 defer rows.Close()
146 for rows.Next() {
147 var u login.UserInfo
148 rows.Scan(&u.UserID, &u.Username)
149 users = append(users, u)
150 }
151 return users
152}
153
154func getxonk(userid int64, xid string) *Honk {
155 row := stmtOneXonk.QueryRow(userid, xid)
156 return scanhonk(row)
157}
158
159func getbonk(userid int64, xid string) *Honk {
160 row := stmtOneBonk.QueryRow(userid, xid)
161 return scanhonk(row)
162}
163
164func getpublichonks() []*Honk {
165 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
166 rows, err := stmtPublicHonks.Query(dt)
167 return getsomehonks(rows, err)
168}
169func geteventhonks(userid int64) []*Honk {
170 rows, err := stmtEventHonks.Query(userid)
171 honks := getsomehonks(rows, err)
172 sort.Slice(honks, func(i, j int) bool {
173 var t1, t2 time.Time
174 if honks[i].Time == nil {
175 t1 = honks[i].Date
176 } else {
177 t1 = honks[i].Time.StartTime
178 }
179 if honks[j].Time == nil {
180 t2 = honks[j].Date
181 } else {
182 t2 = honks[j].Time.StartTime
183 }
184 return t1.After(t2)
185 })
186 now := time.Now().Add(-24 * time.Hour)
187 for i, h := range honks {
188 if h.Time.StartTime.Before(now) {
189 honks = honks[:i]
190 break
191 }
192 }
193 reversehonks(honks)
194 return honks
195}
196func gethonksbyuser(name string, includeprivate bool, wanted int64) []*Honk {
197 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
198 whofore := 2
199 if includeprivate {
200 whofore = 3
201 }
202 rows, err := stmtUserHonks.Query(wanted, whofore, name, dt)
203 return getsomehonks(rows, err)
204}
205func gethonksforuser(userid int64, wanted int64) []*Honk {
206 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
207 rows, err := stmtHonksForUser.Query(wanted, userid, dt, userid, userid)
208 return getsomehonks(rows, err)
209}
210func gethonksforuserfirstclass(userid int64, wanted int64) []*Honk {
211 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
212 rows, err := stmtHonksForUserFirstClass.Query(wanted, userid, dt, userid, userid)
213 return getsomehonks(rows, err)
214}
215func gethonksforme(userid int64, wanted int64) []*Honk {
216 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
217 rows, err := stmtHonksForMe.Query(wanted, userid, dt, userid)
218 return getsomehonks(rows, err)
219}
220func getsavedhonks(userid int64, wanted int64) []*Honk {
221 rows, err := stmtHonksISaved.Query(wanted, userid)
222 return getsomehonks(rows, err)
223}
224func gethonksbyhonker(userid int64, honker string, wanted int64) []*Honk {
225 rows, err := stmtHonksByHonker.Query(wanted, userid, honker, userid)
226 return getsomehonks(rows, err)
227}
228func gethonksbyxonker(userid int64, xonker string, wanted int64) []*Honk {
229 rows, err := stmtHonksByXonker.Query(wanted, userid, xonker, xonker, userid)
230 return getsomehonks(rows, err)
231}
232func gethonksbycombo(userid int64, combo string, wanted int64) []*Honk {
233 combo = "% " + combo + " %"
234 rows, err := stmtHonksByCombo.Query(wanted, userid, combo, userid, wanted, userid, combo, userid)
235 return getsomehonks(rows, err)
236}
237func gethonksbyconvoy(userid int64, convoy string, wanted int64) []*Honk {
238 rows, err := stmtHonksByConvoy.Query(wanted, userid, userid, convoy)
239 honks := getsomehonks(rows, err)
240 return honks
241}
242func gethonksbysearch(userid int64, q string, wanted int64) []*Honk {
243 honker := ""
244 withhonker := 0
245 site := ""
246 withsite := 0
247 terms := strings.Split(q, " ")
248 q = "%"
249 for _, t := range terms {
250 if strings.HasPrefix(t, "site:") {
251 site = t[5:]
252 site = "%" + site + "%"
253 withsite = 1
254 continue
255 }
256 if strings.HasPrefix(t, "honker:") {
257 honker = t[7:]
258 xid := fullname(honker, userid)
259 if xid != "" {
260 honker = xid
261 }
262 withhonker = 1
263 continue
264 }
265 if len(q) != 1 {
266 q += " "
267 }
268 q += t
269 }
270 q += "%"
271 rows, err := stmtHonksBySearch.Query(wanted, userid, withsite, site, withhonker, honker, honker, q, userid)
272 honks := getsomehonks(rows, err)
273 return honks
274}
275func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
276 rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
277 honks := getsomehonks(rows, err)
278 return honks
279}
280
281func reversehonks(honks []*Honk) {
282 for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
283 honks[i], honks[j] = honks[j], honks[i]
284 }
285}
286
287func getsomehonks(rows *sql.Rows, err error) []*Honk {
288 if err != nil {
289 log.Printf("error querying honks: %s", err)
290 return nil
291 }
292 defer rows.Close()
293 var honks []*Honk
294 for rows.Next() {
295 h := scanhonk(rows)
296 if h != nil {
297 honks = append(honks, h)
298 }
299 }
300 rows.Close()
301 donksforhonks(honks)
302 return honks
303}
304
305type RowLike interface {
306 Scan(dest ...interface{}) error
307}
308
309func scanhonk(row RowLike) *Honk {
310 h := new(Honk)
311 var dt, aud string
312 err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
313 &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
314 if err != nil {
315 if err != sql.ErrNoRows {
316 log.Printf("error scanning honk: %s", err)
317 }
318 return nil
319 }
320 h.Date, _ = time.Parse(dbtimeformat, dt)
321 h.Audience = strings.Split(aud, " ")
322 h.Public = !keepitquiet(h.Audience)
323 return h
324}
325
326func donksforhonks(honks []*Honk) {
327 db := opendatabase()
328 var ids []string
329 hmap := make(map[int64]*Honk)
330 for _, h := range honks {
331 ids = append(ids, fmt.Sprintf("%d", h.ID))
332 hmap[h.ID] = h
333 }
334 // grab donks
335 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)", strings.Join(ids, ","))
336 rows, err := db.Query(q)
337 if err != nil {
338 log.Printf("error querying donks: %s", err)
339 return
340 }
341 defer rows.Close()
342 for rows.Next() {
343 var hid int64
344 d := new(Donk)
345 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
346 if err != nil {
347 log.Printf("error scanning donk: %s", err)
348 continue
349 }
350 h := hmap[hid]
351 h.Donks = append(h.Donks, d)
352 }
353 rows.Close()
354
355 // grab onts
356 q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", strings.Join(ids, ","))
357 rows, err = db.Query(q)
358 if err != nil {
359 log.Printf("error querying onts: %s", err)
360 return
361 }
362 defer rows.Close()
363 for rows.Next() {
364 var hid int64
365 var o string
366 err = rows.Scan(&hid, &o)
367 if err != nil {
368 log.Printf("error scanning donk: %s", err)
369 continue
370 }
371 h := hmap[hid]
372 h.Onts = append(h.Onts, o)
373 }
374 rows.Close()
375 // grab meta
376 q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", strings.Join(ids, ","))
377 rows, err = db.Query(q)
378 if err != nil {
379 log.Printf("error querying honkmeta: %s", err)
380 return
381 }
382 defer rows.Close()
383 for rows.Next() {
384 var hid int64
385 var genus, j string
386 err = rows.Scan(&hid, &genus, &j)
387 if err != nil {
388 log.Printf("error scanning honkmeta: %s", err)
389 continue
390 }
391 h := hmap[hid]
392 switch genus {
393 case "place":
394 p := new(Place)
395 err = unjsonify(j, p)
396 if err != nil {
397 log.Printf("error parsing place: %s", err)
398 continue
399 }
400 h.Place = p
401 case "time":
402 t := new(Time)
403 err = unjsonify(j, t)
404 if err != nil {
405 log.Printf("error parsing time: %s", err)
406 continue
407 }
408 h.Time = t
409 case "oldrev":
410 default:
411 log.Printf("unknown meta genus: %s", genus)
412 }
413 }
414 rows.Close()
415}
416
417func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
418 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
419 if err != nil {
420 return 0, err
421 }
422 fileid, _ := res.LastInsertId()
423 if local {
424 _, err = stmtSaveFileData.Exec(xid, media, data)
425 if err != nil {
426 return 0, err
427 }
428 }
429 return fileid, nil
430}
431
432func finddonk(url string) *Donk {
433 donk := new(Donk)
434 row := stmtFindFile.QueryRow(url)
435 err := row.Scan(&donk.FileID, &donk.XID)
436 if err == nil {
437 return donk
438 }
439 if err != sql.ErrNoRows {
440 log.Printf("error finding file: %s", err)
441 }
442 return nil
443}
444
445func savehonk(h *Honk) error {
446 dt := h.Date.UTC().Format(dbtimeformat)
447 aud := strings.Join(h.Audience, " ")
448
449 db := opendatabase()
450 tx, err := db.Begin()
451 if err != nil {
452 log.Printf("can't begin tx: %s", err)
453 return err
454 }
455
456 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
457 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
458 h.Oonker, h.Flags)
459 if err == nil {
460 h.ID, _ = res.LastInsertId()
461 err = saveextras(tx, h)
462 }
463 if err == nil {
464 err = tx.Commit()
465 } else {
466 tx.Rollback()
467 }
468 if err != nil {
469 log.Printf("error saving honk: %s", err)
470 }
471 return err
472}
473
474func updatehonk(h *Honk) error {
475 old := getxonk(h.UserID, h.XID)
476 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
477 dt := h.Date.UTC().Format(dbtimeformat)
478
479 db := opendatabase()
480 tx, err := db.Begin()
481 if err != nil {
482 log.Printf("can't begin tx: %s", err)
483 return err
484 }
485
486 err = deleteextras(tx, h.ID)
487 if err == nil {
488 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
489 }
490 if err == nil {
491 err = saveextras(tx, h)
492 }
493 if err == nil {
494 var j string
495 j, err = jsonify(&oldrev)
496 if err == nil {
497 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
498 }
499 if err != nil {
500 log.Printf("error saving oldrev: %s", err)
501 }
502 }
503 if err == nil {
504 err = tx.Commit()
505 } else {
506 tx.Rollback()
507 }
508 if err != nil {
509 log.Printf("error updating honk %d: %s", h.ID, err)
510 }
511 return err
512}
513
514func deletehonk(honkid int64) error {
515 db := opendatabase()
516 tx, err := db.Begin()
517 if err != nil {
518 log.Printf("can't begin tx: %s", err)
519 return err
520 }
521
522 err = deleteextras(tx, honkid)
523 if err == nil {
524 _, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "nonsense")
525 }
526 if err == nil {
527 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
528 }
529 if err == nil {
530 err = tx.Commit()
531 } else {
532 tx.Rollback()
533 }
534 if err != nil {
535 log.Printf("error deleting honk %d: %s", honkid, err)
536 }
537 return err
538}
539
540func saveextras(tx *sql.Tx, h *Honk) error {
541 for _, d := range h.Donks {
542 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, d.FileID)
543 if err != nil {
544 log.Printf("error saving donk: %s", err)
545 return err
546 }
547 }
548 for _, o := range h.Onts {
549 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
550 if err != nil {
551 log.Printf("error saving ont: %s", err)
552 return err
553 }
554 }
555 if p := h.Place; p != nil {
556 j, err := jsonify(p)
557 if err == nil {
558 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
559 }
560 if err != nil {
561 log.Printf("error saving place: %s", err)
562 return err
563 }
564 }
565 if t := h.Time; t != nil {
566 j, err := jsonify(t)
567 if err == nil {
568 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
569 }
570 if err != nil {
571 log.Printf("error saving time: %s", err)
572 return err
573 }
574 }
575 return nil
576}
577
578func deleteextras(tx *sql.Tx, honkid int64) error {
579 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
580 if err != nil {
581 return err
582 }
583 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
584 if err != nil {
585 return err
586 }
587 _, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "oldrev")
588 if err != nil {
589 return err
590 }
591 return nil
592}
593
594func jsonify(what interface{}) (string, error) {
595 var buf bytes.Buffer
596 e := json.NewEncoder(&buf)
597 e.SetEscapeHTML(false)
598 e.SetIndent("", "")
599 err := e.Encode(what)
600 return buf.String(), err
601}
602
603func unjsonify(s string, dest interface{}) error {
604 d := json.NewDecoder(strings.NewReader(s))
605 err := d.Decode(dest)
606 return err
607}
608
609func cleanupdb(arg string) {
610 db := opendatabase()
611 days, err := strconv.Atoi(arg)
612 var sqlargs []interface{}
613 var where string
614 if err != nil {
615 honker := arg
616 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
617 where = "dt < ? and honker = ?"
618 sqlargs = append(sqlargs, expdate)
619 sqlargs = append(sqlargs, honker)
620 } else {
621 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
622 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
623 sqlargs = append(sqlargs, expdate)
624 }
625 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
626 doordie(db, "delete from donks where honkid not in (select honkid from honks)")
627 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
628 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
629
630 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
631 for _, u := range allusers() {
632 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)
633 }
634
635 filexids := make(map[string]bool)
636 blobdb := openblobdb()
637 rows, err := blobdb.Query("select xid from filedata")
638 if err != nil {
639 log.Fatal(err)
640 }
641 for rows.Next() {
642 var xid string
643 err = rows.Scan(&xid)
644 if err != nil {
645 log.Fatal(err)
646 }
647 filexids[xid] = true
648 }
649 rows.Close()
650 rows, err = db.Query("select xid from filemeta")
651 for rows.Next() {
652 var xid string
653 err = rows.Scan(&xid)
654 if err != nil {
655 log.Fatal(err)
656 }
657 delete(filexids, xid)
658 }
659 rows.Close()
660 tx, err := blobdb.Begin()
661 if err != nil {
662 log.Fatal(err)
663 }
664 for xid, _ := range filexids {
665 _, err = tx.Exec("delete from filedata where xid = ?", xid)
666 if err != nil {
667 log.Fatal(err)
668 }
669 }
670 err = tx.Commit()
671 if err != nil {
672 log.Fatal(err)
673 }
674}
675
676var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
677var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
678var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
679var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
680var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
681var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
682var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
683var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
684var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
685var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
686var stmtHonksForUserFirstClass, stmtSaveMeta, stmtDeleteMeta, stmtUpdateHonk *sql.Stmt
687var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
688
689func preparetodie(db *sql.DB, s string) *sql.Stmt {
690 stmt, err := db.Prepare(s)
691 if err != nil {
692 log.Fatalf("error %s: %s", err, s)
693 }
694 return stmt
695}
696
697func prepareStatements(db *sql.DB) {
698 stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos from honkers where userid = ? and (flavor = 'presub' or flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
699 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner) values (?, ?, ?, ?, ?, ?)")
700 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and name = ? and flavor = ?")
701 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ? where honkerid = ? and userid = ?")
702 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
703 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
704 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
705
706 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 "
707 limit := " order by honks.honkid desc limit 250"
708 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
709 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
710 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
711 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
712 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
713 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+limit)
714 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
715 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
716 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
717 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
718 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
719 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
720 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)
721 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
722 stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.honkid > ? and honks.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)
723 stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and (? = 0 or xid like ?) and (? = 0 or honks.honker = ? or honks.oonker = ?) and noise like ?"+butnotthose+limit)
724 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
725 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)
726
727 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
728 stmtDeleteMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus <> ?")
729 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
730 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
731 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
732 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
733 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
734 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
735 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
736 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
737 blobdb := openblobdb()
738 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
739 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
740 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
741 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
742 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
743 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
744 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
745 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
746 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
747 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
748 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
749 stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers")
750 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
751 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
752 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
753 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
754 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
755 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
756 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")
757 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
758 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
759 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")
760 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
761 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
762 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
763}