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