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