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