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