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