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