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 err = tx.Commit()
599 } else {
600 tx.Rollback()
601 }
602 return err
603}
604
605func loadchatter(userid int64) []*Chatter {
606 duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
607 rows, err := stmtLoadChonks.Query(userid, duedt)
608 if err != nil {
609 log.Printf("error loading chonks: %s", err)
610 return nil
611 }
612 defer rows.Close()
613 chonks := make(map[string][]*Chonk)
614 var allchonks []*Chonk
615 for rows.Next() {
616 ch := new(Chonk)
617 var dt string
618 err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
619 if err != nil {
620 log.Printf("error scanning chonk: %s", err)
621 continue
622 }
623 ch.Date, _ = time.Parse(dbtimeformat, dt)
624 chonks[ch.Target] = append(chonks[ch.Target], ch)
625 allchonks = append(allchonks, ch)
626 }
627 donksforchonks(allchonks)
628 rows.Close()
629 rows, err = stmtGetChatters.Query(userid)
630 if err != nil {
631 log.Printf("error getting chatters: %s", err)
632 return nil
633 }
634 for rows.Next() {
635 var target string
636 err = rows.Scan(&target)
637 if err != nil {
638 log.Printf("error scanning chatter: %s", target)
639 continue
640 }
641 if _, ok := chonks[target]; !ok {
642 chonks[target] = []*Chonk{}
643
644 }
645 }
646 var chatter []*Chatter
647 for target, chonks := range chonks {
648 chatter = append(chatter, &Chatter{
649 Target: target,
650 Chonks: chonks,
651 })
652 }
653 sort.Slice(chatter, func(i, j int) bool {
654 a, b := chatter[i], chatter[j]
655 if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
656 if len(a.Chonks) == len(b.Chonks) {
657 return a.Target < b.Target
658 }
659 return len(a.Chonks) > len(b.Chonks)
660 }
661 return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
662 })
663
664 return chatter
665}
666
667func savehonk(h *Honk) error {
668 dt := h.Date.UTC().Format(dbtimeformat)
669 aud := strings.Join(h.Audience, " ")
670
671 db := opendatabase()
672 tx, err := db.Begin()
673 if err != nil {
674 log.Printf("can't begin tx: %s", err)
675 return err
676 }
677
678 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
679 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
680 h.Oonker, h.Flags)
681 if err == nil {
682 h.ID, _ = res.LastInsertId()
683 err = saveextras(tx, h)
684 }
685 if err == nil {
686 err = tx.Commit()
687 } else {
688 tx.Rollback()
689 }
690 if err != nil {
691 log.Printf("error saving honk: %s", err)
692 }
693 honkhonkline()
694 return err
695}
696
697func updatehonk(h *Honk) error {
698 old := getxonk(h.UserID, h.XID)
699 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
700 dt := h.Date.UTC().Format(dbtimeformat)
701
702 db := opendatabase()
703 tx, err := db.Begin()
704 if err != nil {
705 log.Printf("can't begin tx: %s", err)
706 return err
707 }
708
709 err = deleteextras(tx, h.ID, false)
710 if err == nil {
711 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
712 }
713 if err == nil {
714 err = saveextras(tx, h)
715 }
716 if err == nil {
717 var j string
718 j, err = jsonify(&oldrev)
719 if err == nil {
720 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
721 }
722 if err != nil {
723 log.Printf("error saving oldrev: %s", err)
724 }
725 }
726 if err == nil {
727 err = tx.Commit()
728 } else {
729 tx.Rollback()
730 }
731 if err != nil {
732 log.Printf("error updating honk %d: %s", h.ID, err)
733 }
734 return err
735}
736
737func deletehonk(honkid int64) error {
738 db := opendatabase()
739 tx, err := db.Begin()
740 if err != nil {
741 log.Printf("can't begin tx: %s", err)
742 return err
743 }
744
745 err = deleteextras(tx, honkid, true)
746 if err == nil {
747 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
748 }
749 if err == nil {
750 err = tx.Commit()
751 } else {
752 tx.Rollback()
753 }
754 if err != nil {
755 log.Printf("error deleting honk %d: %s", honkid, err)
756 }
757 return err
758}
759
760func saveextras(tx *sql.Tx, h *Honk) error {
761 for _, d := range h.Donks {
762 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
763 if err != nil {
764 log.Printf("error saving donk: %s", err)
765 return err
766 }
767 }
768 for _, o := range h.Onts {
769 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
770 if err != nil {
771 log.Printf("error saving ont: %s", err)
772 return err
773 }
774 }
775 if p := h.Place; p != nil {
776 j, err := jsonify(p)
777 if err == nil {
778 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
779 }
780 if err != nil {
781 log.Printf("error saving place: %s", err)
782 return err
783 }
784 }
785 if t := h.Time; t != nil {
786 j, err := jsonify(t)
787 if err == nil {
788 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
789 }
790 if err != nil {
791 log.Printf("error saving time: %s", err)
792 return err
793 }
794 }
795 if m := h.Mentions; len(m) > 0 {
796 j, err := jsonify(m)
797 if err == nil {
798 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
799 }
800 if err != nil {
801 log.Printf("error saving mentions: %s", err)
802 return err
803 }
804 }
805 return nil
806}
807
808var baxonker sync.Mutex
809
810func addreaction(user *WhatAbout, xid string, who, react string) {
811 baxonker.Lock()
812 defer baxonker.Unlock()
813 h := getxonk(user.ID, xid)
814 if h == nil {
815 return
816 }
817 h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
818 j, _ := jsonify(h.Badonks)
819 db := opendatabase()
820 tx, _ := db.Begin()
821 _, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
822 _, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
823 tx.Commit()
824}
825
826func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
827 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
828 if err != nil {
829 return err
830 }
831 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
832 if err != nil {
833 return err
834 }
835 if everything {
836 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
837 } else {
838 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
839 }
840 if err != nil {
841 return err
842 }
843 return nil
844}
845
846func jsonify(what interface{}) (string, error) {
847 var buf bytes.Buffer
848 e := json.NewEncoder(&buf)
849 e.SetEscapeHTML(false)
850 e.SetIndent("", "")
851 err := e.Encode(what)
852 return buf.String(), err
853}
854
855func unjsonify(s string, dest interface{}) error {
856 d := json.NewDecoder(strings.NewReader(s))
857 err := d.Decode(dest)
858 return err
859}
860
861func cleanupdb(arg string) {
862 db := opendatabase()
863 days, err := strconv.Atoi(arg)
864 var sqlargs []interface{}
865 var where string
866 if err != nil {
867 honker := arg
868 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
869 where = "dt < ? and honker = ?"
870 sqlargs = append(sqlargs, expdate)
871 sqlargs = append(sqlargs, honker)
872 } else {
873 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
874 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
875 sqlargs = append(sqlargs, expdate)
876 }
877 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
878 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
879 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
880 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
881
882 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
883 for _, u := range allusers() {
884 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)
885 }
886
887 filexids := make(map[string]bool)
888 blobdb := openblobdb()
889 rows, err := blobdb.Query("select xid from filedata")
890 if err != nil {
891 log.Fatal(err)
892 }
893 for rows.Next() {
894 var xid string
895 err = rows.Scan(&xid)
896 if err != nil {
897 log.Fatal(err)
898 }
899 filexids[xid] = true
900 }
901 rows.Close()
902 rows, err = db.Query("select xid from filemeta")
903 for rows.Next() {
904 var xid string
905 err = rows.Scan(&xid)
906 if err != nil {
907 log.Fatal(err)
908 }
909 delete(filexids, xid)
910 }
911 rows.Close()
912 tx, err := blobdb.Begin()
913 if err != nil {
914 log.Fatal(err)
915 }
916 for xid, _ := range filexids {
917 _, err = tx.Exec("delete from filedata where xid = ?", xid)
918 if err != nil {
919 log.Fatal(err)
920 }
921 }
922 err = tx.Commit()
923 if err != nil {
924 log.Fatal(err)
925 }
926}
927
928var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
929var stmtDeleteHonker *sql.Stmt
930var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
931var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
932var stmtHonksFromLongAgo *sql.Stmt
933var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
934var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
935var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
936var stmtCheckFileData *sql.Stmt
937var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
938var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
939var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
940var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
941var stmtHonksForUserFirstClass *sql.Stmt
942var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
943var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
944var stmtGetTracks *sql.Stmt
945var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
946
947func preparetodie(db *sql.DB, s string) *sql.Stmt {
948 stmt, err := db.Prepare(s)
949 if err != nil {
950 log.Fatalf("error %s: %s", err, s)
951 }
952 return stmt
953}
954
955func prepareStatements(db *sql.DB) {
956 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")
957 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
958 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
959 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
960 stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
961 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
962 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
963 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
964
965 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 "
966 limit := " order by honks.honkid desc limit 250"
967 smalllimit := " order by honks.honkid desc limit ?"
968 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
969 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
970 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
971 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
972 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
973 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
974 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
975 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
976 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
977 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
978 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
979 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
980 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
981 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)
982 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
983 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)
984 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
985 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)
986
987 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
988 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
989 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
990 stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
991 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
992 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
993 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
994 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
995 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
996 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
997 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
998 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
999 blobdb := openblobdb()
1000 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1001 stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1002 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1003 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1004 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1005 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1006 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1007 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1008 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1009 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1010 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1011 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1012 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")
1013 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1014 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1015 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1016 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1017 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1018 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1019 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")
1020 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1021 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1022 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")
1023 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1024 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1025 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1026 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1027 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1028 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1029 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1030}