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 = loudandproud(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 idset := strings.Join(ids, ",")
376 // grab donks
377 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)
378 rows, err := db.Query(q)
379 if err != nil {
380 log.Printf("error querying donks: %s", err)
381 return
382 }
383 defer rows.Close()
384 for rows.Next() {
385 var hid int64
386 d := new(Donk)
387 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
388 if err != nil {
389 log.Printf("error scanning donk: %s", err)
390 continue
391 }
392 h := hmap[hid]
393 h.Donks = append(h.Donks, d)
394 }
395 rows.Close()
396
397 // grab onts
398 q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", idset)
399 rows, err = db.Query(q)
400 if err != nil {
401 log.Printf("error querying onts: %s", err)
402 return
403 }
404 defer rows.Close()
405 for rows.Next() {
406 var hid int64
407 var o string
408 err = rows.Scan(&hid, &o)
409 if err != nil {
410 log.Printf("error scanning donk: %s", err)
411 continue
412 }
413 h := hmap[hid]
414 h.Onts = append(h.Onts, o)
415 }
416 rows.Close()
417
418 // grab meta
419 q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", idset)
420 rows, err = db.Query(q)
421 if err != nil {
422 log.Printf("error querying honkmeta: %s", err)
423 return
424 }
425 defer rows.Close()
426 for rows.Next() {
427 var hid int64
428 var genus, j string
429 err = rows.Scan(&hid, &genus, &j)
430 if err != nil {
431 log.Printf("error scanning honkmeta: %s", err)
432 continue
433 }
434 h := hmap[hid]
435 switch genus {
436 case "place":
437 p := new(Place)
438 err = unjsonify(j, p)
439 if err != nil {
440 log.Printf("error parsing place: %s", err)
441 continue
442 }
443 h.Place = p
444 case "time":
445 t := new(Time)
446 err = unjsonify(j, t)
447 if err != nil {
448 log.Printf("error parsing time: %s", err)
449 continue
450 }
451 h.Time = t
452 case "oldrev":
453 default:
454 log.Printf("unknown meta genus: %s", genus)
455 }
456 }
457 rows.Close()
458}
459
460func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
461 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
462 if err != nil {
463 return 0, err
464 }
465 fileid, _ := res.LastInsertId()
466 if local {
467 _, err = stmtSaveFileData.Exec(xid, media, data)
468 if err != nil {
469 return 0, err
470 }
471 }
472 return fileid, nil
473}
474
475func finddonk(url string) *Donk {
476 donk := new(Donk)
477 row := stmtFindFile.QueryRow(url)
478 err := row.Scan(&donk.FileID, &donk.XID)
479 if err == nil {
480 return donk
481 }
482 if err != sql.ErrNoRows {
483 log.Printf("error finding file: %s", err)
484 }
485 return nil
486}
487
488func savehonk(h *Honk) error {
489 dt := h.Date.UTC().Format(dbtimeformat)
490 aud := strings.Join(h.Audience, " ")
491
492 db := opendatabase()
493 tx, err := db.Begin()
494 if err != nil {
495 log.Printf("can't begin tx: %s", err)
496 return err
497 }
498
499 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
500 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
501 h.Oonker, h.Flags)
502 if err == nil {
503 h.ID, _ = res.LastInsertId()
504 err = saveextras(tx, h)
505 }
506 if err == nil {
507 err = tx.Commit()
508 } else {
509 tx.Rollback()
510 }
511 if err != nil {
512 log.Printf("error saving honk: %s", err)
513 }
514 return err
515}
516
517func updatehonk(h *Honk) error {
518 old := getxonk(h.UserID, h.XID)
519 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
520 dt := h.Date.UTC().Format(dbtimeformat)
521
522 db := opendatabase()
523 tx, err := db.Begin()
524 if err != nil {
525 log.Printf("can't begin tx: %s", err)
526 return err
527 }
528
529 err = deleteextras(tx, h.ID)
530 if err == nil {
531 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
532 }
533 if err == nil {
534 err = saveextras(tx, h)
535 }
536 if err == nil {
537 var j string
538 j, err = jsonify(&oldrev)
539 if err == nil {
540 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
541 }
542 if err != nil {
543 log.Printf("error saving oldrev: %s", err)
544 }
545 }
546 if err == nil {
547 err = tx.Commit()
548 } else {
549 tx.Rollback()
550 }
551 if err != nil {
552 log.Printf("error updating honk %d: %s", h.ID, err)
553 }
554 return err
555}
556
557func deletehonk(honkid int64) error {
558 db := opendatabase()
559 tx, err := db.Begin()
560 if err != nil {
561 log.Printf("can't begin tx: %s", err)
562 return err
563 }
564
565 err = deleteextras(tx, honkid)
566 if err == nil {
567 _, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "nonsense")
568 }
569 if err == nil {
570 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
571 }
572 if err == nil {
573 err = tx.Commit()
574 } else {
575 tx.Rollback()
576 }
577 if err != nil {
578 log.Printf("error deleting honk %d: %s", honkid, err)
579 }
580 return err
581}
582
583func saveextras(tx *sql.Tx, h *Honk) error {
584 for _, d := range h.Donks {
585 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, d.FileID)
586 if err != nil {
587 log.Printf("error saving donk: %s", err)
588 return err
589 }
590 }
591 for _, o := range h.Onts {
592 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
593 if err != nil {
594 log.Printf("error saving ont: %s", err)
595 return err
596 }
597 }
598 if p := h.Place; p != nil {
599 j, err := jsonify(p)
600 if err == nil {
601 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
602 }
603 if err != nil {
604 log.Printf("error saving place: %s", err)
605 return err
606 }
607 }
608 if t := h.Time; t != nil {
609 j, err := jsonify(t)
610 if err == nil {
611 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
612 }
613 if err != nil {
614 log.Printf("error saving time: %s", err)
615 return err
616 }
617 }
618 return nil
619}
620
621func deleteextras(tx *sql.Tx, honkid int64) error {
622 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
623 if err != nil {
624 return err
625 }
626 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
627 if err != nil {
628 return err
629 }
630 _, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "oldrev")
631 if err != nil {
632 return err
633 }
634 return nil
635}
636
637func jsonify(what interface{}) (string, error) {
638 var buf bytes.Buffer
639 e := json.NewEncoder(&buf)
640 e.SetEscapeHTML(false)
641 e.SetIndent("", "")
642 err := e.Encode(what)
643 return buf.String(), err
644}
645
646func unjsonify(s string, dest interface{}) error {
647 d := json.NewDecoder(strings.NewReader(s))
648 err := d.Decode(dest)
649 return err
650}
651
652func cleanupdb(arg string) {
653 db := opendatabase()
654 days, err := strconv.Atoi(arg)
655 var sqlargs []interface{}
656 var where string
657 if err != nil {
658 honker := arg
659 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
660 where = "dt < ? and honker = ?"
661 sqlargs = append(sqlargs, expdate)
662 sqlargs = append(sqlargs, honker)
663 } else {
664 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
665 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
666 sqlargs = append(sqlargs, expdate)
667 }
668 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
669 doordie(db, "delete from donks where honkid not in (select honkid from honks)")
670 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
671 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
672
673 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
674 for _, u := range allusers() {
675 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)
676 }
677
678 filexids := make(map[string]bool)
679 blobdb := openblobdb()
680 rows, err := blobdb.Query("select xid from filedata")
681 if err != nil {
682 log.Fatal(err)
683 }
684 for rows.Next() {
685 var xid string
686 err = rows.Scan(&xid)
687 if err != nil {
688 log.Fatal(err)
689 }
690 filexids[xid] = true
691 }
692 rows.Close()
693 rows, err = db.Query("select xid from filemeta")
694 for rows.Next() {
695 var xid string
696 err = rows.Scan(&xid)
697 if err != nil {
698 log.Fatal(err)
699 }
700 delete(filexids, xid)
701 }
702 rows.Close()
703 tx, err := blobdb.Begin()
704 if err != nil {
705 log.Fatal(err)
706 }
707 for xid, _ := range filexids {
708 _, err = tx.Exec("delete from filedata where xid = ?", xid)
709 if err != nil {
710 log.Fatal(err)
711 }
712 }
713 err = tx.Commit()
714 if err != nil {
715 log.Fatal(err)
716 }
717}
718
719var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
720var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
721var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
722var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
723var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
724var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
725var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
726var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
727var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
728var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
729var stmtHonksForUserFirstClass, stmtSaveMeta, stmtDeleteMeta, stmtUpdateHonk *sql.Stmt
730var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
731
732func preparetodie(db *sql.DB, s string) *sql.Stmt {
733 stmt, err := db.Prepare(s)
734 if err != nil {
735 log.Fatalf("error %s: %s", err, s)
736 }
737 return stmt
738}
739
740func prepareStatements(db *sql.DB) {
741 stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos from honkers where userid = ? and (flavor = 'presub' or flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
742 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner) values (?, ?, ?, ?, ?, ?)")
743 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and name = ? and flavor = ?")
744 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ? where honkerid = ? and userid = ?")
745 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
746 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
747 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
748
749 selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, format, convoy, whofore, flags from honks join users on honks.userid = users.userid "
750 limit := " order by honks.honkid desc limit 250"
751 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
752 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
753 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
754 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
755 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
756 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+limit)
757 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
758 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
759 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
760 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
761 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
762 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
763 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)
764 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
765 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)
766 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)
767 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
768 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)
769
770 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
771 stmtDeleteMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus <> ?")
772 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
773 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
774 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
775 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
776 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
777 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
778 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
779 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
780 blobdb := openblobdb()
781 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
782 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
783 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
784 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
785 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
786 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
787 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
788 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
789 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
790 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
791 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
792 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")
793 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
794 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
795 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
796 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
797 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
798 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
799 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")
800 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
801 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
802 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")
803 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
804 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
805 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
806}