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