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