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