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 gethonksfromlongago(userid int64, wanted int64) []*Honk {
236 now := time.Now().UTC()
237 dt1 := now.Add(-367 * 24 * time.Hour).Format(dbtimeformat)
238 dt2 := now.Add(-364 * 24 * time.Hour).Format(dbtimeformat)
239 rows, err := stmtHonksFromLongAgo.Query(wanted, userid, dt1, dt2, userid)
240 return getsomehonks(rows, err)
241}
242func getsavedhonks(userid int64, wanted int64) []*Honk {
243 rows, err := stmtHonksISaved.Query(wanted, userid)
244 return getsomehonks(rows, err)
245}
246func gethonksbyhonker(userid int64, honker string, wanted int64) []*Honk {
247 rows, err := stmtHonksByHonker.Query(wanted, userid, honker, userid)
248 return getsomehonks(rows, err)
249}
250func gethonksbyxonker(userid int64, xonker string, wanted int64) []*Honk {
251 rows, err := stmtHonksByXonker.Query(wanted, userid, xonker, xonker, userid)
252 return getsomehonks(rows, err)
253}
254func gethonksbycombo(userid int64, combo string, wanted int64) []*Honk {
255 combo = "% " + combo + " %"
256 rows, err := stmtHonksByCombo.Query(wanted, userid, userid, combo, userid, wanted, userid, combo, userid)
257 return getsomehonks(rows, err)
258}
259func gethonksbyconvoy(userid int64, convoy string, wanted int64) []*Honk {
260 rows, err := stmtHonksByConvoy.Query(wanted, userid, userid, convoy)
261 honks := getsomehonks(rows, err)
262 return honks
263}
264func gethonksbysearch(userid int64, q string, wanted int64) []*Honk {
265 var queries []string
266 var params []interface{}
267 queries = append(queries, "honks.honkid > ?")
268 params = append(params, wanted)
269 queries = append(queries, "honks.userid = ?")
270 params = append(params, userid)
271
272 terms := strings.Split(q, " ")
273 for _, t := range terms {
274 if t == "" {
275 continue
276 }
277 negate := " "
278 if t[0] == '-' {
279 t = t[1:]
280 negate = " not "
281 }
282 if t == "" {
283 continue
284 }
285 if strings.HasPrefix(t, "site:") {
286 site := t[5:]
287 site = "%" + site + "%"
288 queries = append(queries, "xid"+negate+"like ?")
289 params = append(params, site)
290 continue
291 }
292 if strings.HasPrefix(t, "honker:") {
293 honker := t[7:]
294 xid := fullname(honker, userid)
295 if xid != "" {
296 honker = xid
297 }
298 queries = append(queries, negate+"(honks.honker = ? or honks.oonker = ?)")
299 params = append(params, honker)
300 params = append(params, honker)
301 continue
302 }
303 t = "%" + t + "%"
304 queries = append(queries, "noise"+negate+"like ?")
305 params = append(params, t)
306 }
307
308 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 "
309 where := "where " + strings.Join(queries, " and ")
310 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
311 limit := " order by honks.honkid desc limit 250"
312 params = append(params, userid)
313 rows, err := opendatabase().Query(selecthonks+where+butnotthose+limit, params...)
314 honks := getsomehonks(rows, err)
315 return honks
316}
317func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
318 rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
319 honks := getsomehonks(rows, err)
320 return honks
321}
322
323func reversehonks(honks []*Honk) {
324 for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
325 honks[i], honks[j] = honks[j], honks[i]
326 }
327}
328
329func getsomehonks(rows *sql.Rows, err error) []*Honk {
330 if err != nil {
331 log.Printf("error querying honks: %s", err)
332 return nil
333 }
334 defer rows.Close()
335 var honks []*Honk
336 for rows.Next() {
337 h := scanhonk(rows)
338 if h != nil {
339 honks = append(honks, h)
340 }
341 }
342 rows.Close()
343 donksforhonks(honks)
344 return honks
345}
346
347type RowLike interface {
348 Scan(dest ...interface{}) error
349}
350
351func scanhonk(row RowLike) *Honk {
352 h := new(Honk)
353 var dt, aud string
354 err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
355 &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
356 if err != nil {
357 if err != sql.ErrNoRows {
358 log.Printf("error scanning honk: %s", err)
359 }
360 return nil
361 }
362 h.Date, _ = time.Parse(dbtimeformat, dt)
363 h.Audience = strings.Split(aud, " ")
364 h.Public = loudandproud(h.Audience)
365 return h
366}
367
368func donksforhonks(honks []*Honk) {
369 db := opendatabase()
370 var ids []string
371 hmap := make(map[int64]*Honk)
372 for _, h := range honks {
373 ids = append(ids, fmt.Sprintf("%d", h.ID))
374 hmap[h.ID] = h
375 }
376 idset := strings.Join(ids, ",")
377 // grab donks
378 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)
379 rows, err := db.Query(q)
380 if err != nil {
381 log.Printf("error querying donks: %s", err)
382 return
383 }
384 defer rows.Close()
385 for rows.Next() {
386 var hid int64
387 d := new(Donk)
388 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
389 if err != nil {
390 log.Printf("error scanning donk: %s", err)
391 continue
392 }
393 h := hmap[hid]
394 h.Donks = append(h.Donks, d)
395 }
396 rows.Close()
397
398 // grab onts
399 q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", idset)
400 rows, err = db.Query(q)
401 if err != nil {
402 log.Printf("error querying onts: %s", err)
403 return
404 }
405 defer rows.Close()
406 for rows.Next() {
407 var hid int64
408 var o string
409 err = rows.Scan(&hid, &o)
410 if err != nil {
411 log.Printf("error scanning donk: %s", err)
412 continue
413 }
414 h := hmap[hid]
415 h.Onts = append(h.Onts, o)
416 }
417 rows.Close()
418
419 // grab meta
420 q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", idset)
421 rows, err = db.Query(q)
422 if err != nil {
423 log.Printf("error querying honkmeta: %s", err)
424 return
425 }
426 defer rows.Close()
427 for rows.Next() {
428 var hid int64
429 var genus, j string
430 err = rows.Scan(&hid, &genus, &j)
431 if err != nil {
432 log.Printf("error scanning honkmeta: %s", err)
433 continue
434 }
435 h := hmap[hid]
436 switch genus {
437 case "place":
438 p := new(Place)
439 err = unjsonify(j, p)
440 if err != nil {
441 log.Printf("error parsing place: %s", err)
442 continue
443 }
444 h.Place = p
445 case "time":
446 t := new(Time)
447 err = unjsonify(j, t)
448 if err != nil {
449 log.Printf("error parsing time: %s", err)
450 continue
451 }
452 h.Time = t
453 case "mentions":
454 err = unjsonify(j, &h.Mentions)
455 if err != nil {
456 log.Printf("error parsing mentions: %s", err)
457 continue
458 }
459 case "oldrev":
460 default:
461 log.Printf("unknown meta genus: %s", genus)
462 }
463 }
464 rows.Close()
465}
466
467func donksforchonks(chonks []*Chonk) {
468 db := opendatabase()
469 var ids []string
470 chmap := make(map[int64]*Chonk)
471 for _, ch := range chonks {
472 ids = append(ids, fmt.Sprintf("%d", ch.ID))
473 chmap[ch.ID] = ch
474 }
475 idset := strings.Join(ids, ",")
476 // grab donks
477 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)
478 rows, err := db.Query(q)
479 if err != nil {
480 log.Printf("error querying donks: %s", err)
481 return
482 }
483 defer rows.Close()
484 for rows.Next() {
485 var chid int64
486 d := new(Donk)
487 err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
488 if err != nil {
489 log.Printf("error scanning donk: %s", err)
490 continue
491 }
492 ch := chmap[chid]
493 ch.Donks = append(ch.Donks, d)
494 }
495}
496
497func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
498 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
499 if err != nil {
500 return 0, err
501 }
502 fileid, _ := res.LastInsertId()
503 if local {
504 _, err = stmtSaveFileData.Exec(xid, media, data)
505 if err != nil {
506 return 0, err
507 }
508 }
509 return fileid, nil
510}
511
512func finddonk(url string) *Donk {
513 donk := new(Donk)
514 row := stmtFindFile.QueryRow(url)
515 err := row.Scan(&donk.FileID, &donk.XID)
516 if err == nil {
517 return donk
518 }
519 if err != sql.ErrNoRows {
520 log.Printf("error finding file: %s", err)
521 }
522 return nil
523}
524
525func savechonk(ch *Chonk) error {
526 dt := ch.Date.UTC().Format(dbtimeformat)
527 db := opendatabase()
528 tx, err := db.Begin()
529 if err != nil {
530 log.Printf("can't begin tx: %s", err)
531 return err
532 }
533
534 res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
535 if err == nil {
536 ch.ID, _ = res.LastInsertId()
537 for _, d := range ch.Donks {
538 _, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
539 if err != nil {
540 log.Printf("error saving donk: %s", err)
541 break
542 }
543 }
544 err = tx.Commit()
545 } else {
546 tx.Rollback()
547 }
548 return err
549}
550
551func loadchatter(userid int64) []*Chatter {
552 duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
553 rows, err := stmtLoadChonks.Query(userid, duedt)
554 if err != nil {
555 log.Printf("error loading chonks: %s", err)
556 return nil
557 }
558 defer rows.Close()
559 chonks := make(map[string][]*Chonk)
560 var allchonks []*Chonk
561 for rows.Next() {
562 ch := new(Chonk)
563 var dt string
564 err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
565 if err != nil {
566 log.Printf("error scanning chonk: %s", err)
567 continue
568 }
569 ch.Date, _ = time.Parse(dbtimeformat, dt)
570 chonks[ch.Target] = append(chonks[ch.Target], ch)
571 allchonks = append(allchonks, ch)
572 }
573 donksforchonks(allchonks)
574 rows.Close()
575 rows, err = stmtGetChatters.Query(userid)
576 if err != nil {
577 log.Printf("error getting chatters: %s", err)
578 return nil
579 }
580 for rows.Next() {
581 var target string
582 err = rows.Scan(&target)
583 if err != nil {
584 log.Printf("error scanning chatter: %s", target)
585 continue
586 }
587 if _, ok := chonks[target]; !ok {
588 chonks[target] = []*Chonk{}
589
590 }
591 }
592 var chatter []*Chatter
593 for target, chonks := range chonks {
594 chatter = append(chatter, &Chatter{
595 Target: target,
596 Chonks: chonks,
597 })
598 }
599 sort.Slice(chatter, func(i, j int) bool {
600 a, b := chatter[i], chatter[j]
601 if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
602 if len(a.Chonks) == len(b.Chonks) {
603 return a.Target < b.Target
604 }
605 return len(a.Chonks) > len(b.Chonks)
606 }
607 return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
608 })
609
610 return chatter
611}
612
613func savehonk(h *Honk) error {
614 dt := h.Date.UTC().Format(dbtimeformat)
615 aud := strings.Join(h.Audience, " ")
616
617 db := opendatabase()
618 tx, err := db.Begin()
619 if err != nil {
620 log.Printf("can't begin tx: %s", err)
621 return err
622 }
623
624 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
625 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
626 h.Oonker, h.Flags)
627 if err == nil {
628 h.ID, _ = res.LastInsertId()
629 err = saveextras(tx, h)
630 }
631 if err == nil {
632 err = tx.Commit()
633 } else {
634 tx.Rollback()
635 }
636 if err != nil {
637 log.Printf("error saving honk: %s", err)
638 }
639 honkhonkline()
640 return err
641}
642
643func updatehonk(h *Honk) error {
644 old := getxonk(h.UserID, h.XID)
645 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
646 dt := h.Date.UTC().Format(dbtimeformat)
647
648 db := opendatabase()
649 tx, err := db.Begin()
650 if err != nil {
651 log.Printf("can't begin tx: %s", err)
652 return err
653 }
654
655 err = deleteextras(tx, h.ID, false)
656 if err == nil {
657 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
658 }
659 if err == nil {
660 err = saveextras(tx, h)
661 }
662 if err == nil {
663 var j string
664 j, err = jsonify(&oldrev)
665 if err == nil {
666 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
667 }
668 if err != nil {
669 log.Printf("error saving oldrev: %s", err)
670 }
671 }
672 if err == nil {
673 err = tx.Commit()
674 } else {
675 tx.Rollback()
676 }
677 if err != nil {
678 log.Printf("error updating honk %d: %s", h.ID, err)
679 }
680 return err
681}
682
683func deletehonk(honkid int64) error {
684 db := opendatabase()
685 tx, err := db.Begin()
686 if err != nil {
687 log.Printf("can't begin tx: %s", err)
688 return err
689 }
690
691 err = deleteextras(tx, honkid, true)
692 if err == nil {
693 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
694 }
695 if err == nil {
696 err = tx.Commit()
697 } else {
698 tx.Rollback()
699 }
700 if err != nil {
701 log.Printf("error deleting honk %d: %s", honkid, err)
702 }
703 return err
704}
705
706func saveextras(tx *sql.Tx, h *Honk) error {
707 for _, d := range h.Donks {
708 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
709 if err != nil {
710 log.Printf("error saving donk: %s", err)
711 return err
712 }
713 }
714 for _, o := range h.Onts {
715 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
716 if err != nil {
717 log.Printf("error saving ont: %s", err)
718 return err
719 }
720 }
721 if p := h.Place; p != nil {
722 j, err := jsonify(p)
723 if err == nil {
724 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
725 }
726 if err != nil {
727 log.Printf("error saving place: %s", err)
728 return err
729 }
730 }
731 if t := h.Time; t != nil {
732 j, err := jsonify(t)
733 if err == nil {
734 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
735 }
736 if err != nil {
737 log.Printf("error saving time: %s", err)
738 return err
739 }
740 }
741 if m := h.Mentions; len(m) > 0 {
742 j, err := jsonify(m)
743 if err == nil {
744 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
745 }
746 if err != nil {
747 log.Printf("error saving mentions: %s", err)
748 return err
749 }
750 }
751 return nil
752}
753
754func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
755 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
756 if err != nil {
757 return err
758 }
759 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
760 if err != nil {
761 return err
762 }
763 if everything {
764 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
765 } else {
766 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
767 }
768 if err != nil {
769 return err
770 }
771 return nil
772}
773
774func jsonify(what interface{}) (string, error) {
775 var buf bytes.Buffer
776 e := json.NewEncoder(&buf)
777 e.SetEscapeHTML(false)
778 e.SetIndent("", "")
779 err := e.Encode(what)
780 return buf.String(), err
781}
782
783func unjsonify(s string, dest interface{}) error {
784 d := json.NewDecoder(strings.NewReader(s))
785 err := d.Decode(dest)
786 return err
787}
788
789func cleanupdb(arg string) {
790 db := opendatabase()
791 days, err := strconv.Atoi(arg)
792 var sqlargs []interface{}
793 var where string
794 if err != nil {
795 honker := arg
796 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
797 where = "dt < ? and honker = ?"
798 sqlargs = append(sqlargs, expdate)
799 sqlargs = append(sqlargs, honker)
800 } else {
801 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
802 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
803 sqlargs = append(sqlargs, expdate)
804 }
805 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
806 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
807 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
808 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
809
810 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
811 for _, u := range allusers() {
812 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)
813 }
814
815 filexids := make(map[string]bool)
816 blobdb := openblobdb()
817 rows, err := blobdb.Query("select xid from filedata")
818 if err != nil {
819 log.Fatal(err)
820 }
821 for rows.Next() {
822 var xid string
823 err = rows.Scan(&xid)
824 if err != nil {
825 log.Fatal(err)
826 }
827 filexids[xid] = true
828 }
829 rows.Close()
830 rows, err = db.Query("select xid from filemeta")
831 for rows.Next() {
832 var xid string
833 err = rows.Scan(&xid)
834 if err != nil {
835 log.Fatal(err)
836 }
837 delete(filexids, xid)
838 }
839 rows.Close()
840 tx, err := blobdb.Begin()
841 if err != nil {
842 log.Fatal(err)
843 }
844 for xid, _ := range filexids {
845 _, err = tx.Exec("delete from filedata where xid = ?", xid)
846 if err != nil {
847 log.Fatal(err)
848 }
849 }
850 err = tx.Commit()
851 if err != nil {
852 log.Fatal(err)
853 }
854}
855
856var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
857var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
858var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
859var stmtHonksFromLongAgo *sql.Stmt
860var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
861var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
862var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
863var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
864var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
865var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
866var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
867var stmtHonksForUserFirstClass *sql.Stmt
868var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
869var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
870var stmtGetTracks *sql.Stmt
871var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
872
873func preparetodie(db *sql.DB, s string) *sql.Stmt {
874 stmt, err := db.Prepare(s)
875 if err != nil {
876 log.Fatalf("error %s: %s", err, s)
877 }
878 return stmt
879}
880
881func prepareStatements(db *sql.DB) {
882 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")
883 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta) values (?, ?, ?, ?, ?, ?, ?)")
884 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and name = ? and flavor = ?")
885 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
886 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
887 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
888 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
889
890 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 "
891 limit := " order by honks.honkid desc limit 250"
892 smalllimit := " order by honks.honkid desc limit ?"
893 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
894 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
895 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
896 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
897 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
898 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
899 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
900 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
901 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
902 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
903 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
904 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
905 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
906 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)
907 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
908 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)
909 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
910 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)
911
912 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
913 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
914 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
915 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
916 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
917 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
918 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
919 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
920 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
921 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
922 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
923 blobdb := openblobdb()
924 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
925 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
926 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
927 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
928 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
929 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
930 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
931 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
932 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
933 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
934 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
935 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")
936 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
937 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
938 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
939 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
940 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
941 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
942 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")
943 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
944 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
945 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")
946 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
947 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
948 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
949 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
950 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
951 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
952 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
953}