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