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