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