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