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