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