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