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