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 donksforchonks(chonks []*Chonk) {
458 db := opendatabase()
459 var ids []string
460 chmap := make(map[int64]*Chonk)
461 for _, ch := range chonks {
462 ids = append(ids, fmt.Sprintf("%d", ch.ID))
463 chmap[ch.ID] = ch
464 }
465 idset := strings.Join(ids, ",")
466 // grab donks
467 q := fmt.Sprintf("select chonkid, donks.fileid, xid, name, description, url, media, local from donks join filemeta on donks.fileid = filemeta.fileid where chonkid in (%s)", idset)
468 rows, err := db.Query(q)
469 if err != nil {
470 log.Printf("error querying donks: %s", err)
471 return
472 }
473 defer rows.Close()
474 for rows.Next() {
475 var chid int64
476 d := new(Donk)
477 err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
478 if err != nil {
479 log.Printf("error scanning donk: %s", err)
480 continue
481 }
482 ch := chmap[chid]
483 ch.Donks = append(ch.Donks, d)
484 }
485}
486
487func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
488 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
489 if err != nil {
490 return 0, err
491 }
492 fileid, _ := res.LastInsertId()
493 if local {
494 _, err = stmtSaveFileData.Exec(xid, media, data)
495 if err != nil {
496 return 0, err
497 }
498 }
499 return fileid, nil
500}
501
502func finddonk(url string) *Donk {
503 donk := new(Donk)
504 row := stmtFindFile.QueryRow(url)
505 err := row.Scan(&donk.FileID, &donk.XID)
506 if err == nil {
507 return donk
508 }
509 if err != sql.ErrNoRows {
510 log.Printf("error finding file: %s", err)
511 }
512 return nil
513}
514
515func savechonk(ch *Chonk) error {
516 dt := ch.Date.UTC().Format(dbtimeformat)
517 db := opendatabase()
518 tx, err := db.Begin()
519 if err != nil {
520 log.Printf("can't begin tx: %s", err)
521 return err
522 }
523
524 res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
525 if err == nil {
526 ch.ID, _ = res.LastInsertId()
527 for _, d := range ch.Donks {
528 _, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
529 if err != nil {
530 log.Printf("error saving donk: %s", err)
531 break
532 }
533 }
534 err = tx.Commit()
535 } else {
536 tx.Rollback()
537 }
538 return err
539}
540
541func loadchatter(userid int64) []*Chatter {
542 duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
543 rows, err := stmtLoadChonks.Query(userid, duedt)
544 if err != nil {
545 log.Printf("error loading chonks: %s", err)
546 return nil
547 }
548 defer rows.Close()
549 chonks := make(map[string][]*Chonk)
550 var allchonks []*Chonk
551 for rows.Next() {
552 ch := new(Chonk)
553 var dt string
554 err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
555 if err != nil {
556 log.Printf("error scanning chonk: %s", err)
557 continue
558 }
559 ch.Date, _ = time.Parse(dbtimeformat, dt)
560 chonks[ch.Target] = append(chonks[ch.Target], ch)
561 allchonks = append(allchonks, ch)
562 }
563 donksforchonks(allchonks)
564 rows.Close()
565 rows, err = stmtGetChatters.Query(userid)
566 if err != nil {
567 log.Printf("error getting chatters: %s", err)
568 return nil
569 }
570 for rows.Next() {
571 var target string
572 err = rows.Scan(&target)
573 if err != nil {
574 log.Printf("error scanning chatter: %s", target)
575 continue
576 }
577 if _, ok := chonks[target]; !ok {
578 chonks[target] = []*Chonk{}
579
580 }
581 }
582 var chatter []*Chatter
583 for target, chonks := range chonks {
584 chatter = append(chatter, &Chatter{
585 Target: target,
586 Chonks: chonks,
587 })
588 }
589 sort.Slice(chatter, func(i, j int) bool {
590 a, b := chatter[i], chatter[j]
591 if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
592 if len(a.Chonks) == len(b.Chonks) {
593 return a.Target < b.Target
594 }
595 return len(a.Chonks) > len(b.Chonks)
596 }
597 return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
598 })
599
600 return chatter
601}
602
603func savehonk(h *Honk) error {
604 dt := h.Date.UTC().Format(dbtimeformat)
605 aud := strings.Join(h.Audience, " ")
606
607 db := opendatabase()
608 tx, err := db.Begin()
609 if err != nil {
610 log.Printf("can't begin tx: %s", err)
611 return err
612 }
613
614 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
615 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
616 h.Oonker, h.Flags)
617 if err == nil {
618 h.ID, _ = res.LastInsertId()
619 err = saveextras(tx, h)
620 }
621 if err == nil {
622 err = tx.Commit()
623 } else {
624 tx.Rollback()
625 }
626 if err != nil {
627 log.Printf("error saving honk: %s", err)
628 }
629 honkhonkline()
630 return err
631}
632
633func updatehonk(h *Honk) error {
634 old := getxonk(h.UserID, h.XID)
635 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
636 dt := h.Date.UTC().Format(dbtimeformat)
637
638 db := opendatabase()
639 tx, err := db.Begin()
640 if err != nil {
641 log.Printf("can't begin tx: %s", err)
642 return err
643 }
644
645 err = deleteextras(tx, h.ID, false)
646 if err == nil {
647 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
648 }
649 if err == nil {
650 err = saveextras(tx, h)
651 }
652 if err == nil {
653 var j string
654 j, err = jsonify(&oldrev)
655 if err == nil {
656 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
657 }
658 if err != nil {
659 log.Printf("error saving oldrev: %s", err)
660 }
661 }
662 if err == nil {
663 err = tx.Commit()
664 } else {
665 tx.Rollback()
666 }
667 if err != nil {
668 log.Printf("error updating honk %d: %s", h.ID, err)
669 }
670 return err
671}
672
673func deletehonk(honkid int64) error {
674 db := opendatabase()
675 tx, err := db.Begin()
676 if err != nil {
677 log.Printf("can't begin tx: %s", err)
678 return err
679 }
680
681 err = deleteextras(tx, honkid, true)
682 if err == nil {
683 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
684 }
685 if err == nil {
686 err = tx.Commit()
687 } else {
688 tx.Rollback()
689 }
690 if err != nil {
691 log.Printf("error deleting honk %d: %s", honkid, err)
692 }
693 return err
694}
695
696func saveextras(tx *sql.Tx, h *Honk) error {
697 for _, d := range h.Donks {
698 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
699 if err != nil {
700 log.Printf("error saving donk: %s", err)
701 return err
702 }
703 }
704 for _, o := range h.Onts {
705 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
706 if err != nil {
707 log.Printf("error saving ont: %s", err)
708 return err
709 }
710 }
711 if p := h.Place; p != nil {
712 j, err := jsonify(p)
713 if err == nil {
714 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
715 }
716 if err != nil {
717 log.Printf("error saving place: %s", err)
718 return err
719 }
720 }
721 if t := h.Time; t != nil {
722 j, err := jsonify(t)
723 if err == nil {
724 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
725 }
726 if err != nil {
727 log.Printf("error saving time: %s", err)
728 return err
729 }
730 }
731 if m := h.Mentions; len(m) > 0 {
732 j, err := jsonify(m)
733 if err == nil {
734 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
735 }
736 if err != nil {
737 log.Printf("error saving mentions: %s", err)
738 return err
739 }
740 }
741 return nil
742}
743
744func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
745 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
746 if err != nil {
747 return err
748 }
749 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
750 if err != nil {
751 return err
752 }
753 if everything {
754 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
755 } else {
756 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
757 }
758 if err != nil {
759 return err
760 }
761 return nil
762}
763
764func jsonify(what interface{}) (string, error) {
765 var buf bytes.Buffer
766 e := json.NewEncoder(&buf)
767 e.SetEscapeHTML(false)
768 e.SetIndent("", "")
769 err := e.Encode(what)
770 return buf.String(), err
771}
772
773func unjsonify(s string, dest interface{}) error {
774 d := json.NewDecoder(strings.NewReader(s))
775 err := d.Decode(dest)
776 return err
777}
778
779func cleanupdb(arg string) {
780 db := opendatabase()
781 days, err := strconv.Atoi(arg)
782 var sqlargs []interface{}
783 var where string
784 if err != nil {
785 honker := arg
786 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
787 where = "dt < ? and honker = ?"
788 sqlargs = append(sqlargs, expdate)
789 sqlargs = append(sqlargs, honker)
790 } else {
791 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
792 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
793 sqlargs = append(sqlargs, expdate)
794 }
795 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
796 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
797 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
798 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
799
800 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
801 for _, u := range allusers() {
802 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)
803 }
804
805 filexids := make(map[string]bool)
806 blobdb := openblobdb()
807 rows, err := blobdb.Query("select xid from filedata")
808 if err != nil {
809 log.Fatal(err)
810 }
811 for rows.Next() {
812 var xid string
813 err = rows.Scan(&xid)
814 if err != nil {
815 log.Fatal(err)
816 }
817 filexids[xid] = true
818 }
819 rows.Close()
820 rows, err = db.Query("select xid from filemeta")
821 for rows.Next() {
822 var xid string
823 err = rows.Scan(&xid)
824 if err != nil {
825 log.Fatal(err)
826 }
827 delete(filexids, xid)
828 }
829 rows.Close()
830 tx, err := blobdb.Begin()
831 if err != nil {
832 log.Fatal(err)
833 }
834 for xid, _ := range filexids {
835 _, err = tx.Exec("delete from filedata where xid = ?", xid)
836 if err != nil {
837 log.Fatal(err)
838 }
839 }
840 err = tx.Commit()
841 if err != nil {
842 log.Fatal(err)
843 }
844}
845
846var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
847var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
848var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
849var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
850var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
851var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
852var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
853var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
854var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
855var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
856var stmtHonksForUserFirstClass *sql.Stmt
857var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
858var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
859var stmtGetTracks *sql.Stmt
860var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
861
862func preparetodie(db *sql.DB, s string) *sql.Stmt {
863 stmt, err := db.Prepare(s)
864 if err != nil {
865 log.Fatalf("error %s: %s", err, s)
866 }
867 return stmt
868}
869
870func prepareStatements(db *sql.DB) {
871 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")
872 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta) values (?, ?, ?, ?, ?, ?, ?)")
873 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and name = ? and flavor = ?")
874 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
875 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
876 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
877 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
878
879 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 "
880 limit := " order by honks.honkid desc limit 250"
881 smalllimit := " order by honks.honkid desc limit ?"
882 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
883 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
884 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
885 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
886 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
887 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
888 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
889 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
890 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
891 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
892 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
893 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
894 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)
895 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
896 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)
897 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
898 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)
899
900 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
901 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
902 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
903 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
904 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
905 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
906 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
907 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
908 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
909 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
910 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
911 blobdb := openblobdb()
912 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
913 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
914 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
915 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
916 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
917 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
918 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
919 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
920 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
921 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
922 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
923 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")
924 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
925 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
926 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
927 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
928 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
929 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
930 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")
931 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
932 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
933 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")
934 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
935 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
936 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
937 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
938 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
939 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
940 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
941}