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 deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
802 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
803 if err != nil {
804 return err
805 }
806 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
807 if err != nil {
808 return err
809 }
810 if everything {
811 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
812 } else {
813 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
814 }
815 if err != nil {
816 return err
817 }
818 return nil
819}
820
821func jsonify(what interface{}) (string, error) {
822 var buf bytes.Buffer
823 e := json.NewEncoder(&buf)
824 e.SetEscapeHTML(false)
825 e.SetIndent("", "")
826 err := e.Encode(what)
827 return buf.String(), err
828}
829
830func unjsonify(s string, dest interface{}) error {
831 d := json.NewDecoder(strings.NewReader(s))
832 err := d.Decode(dest)
833 return err
834}
835
836func cleanupdb(arg string) {
837 db := opendatabase()
838 days, err := strconv.Atoi(arg)
839 var sqlargs []interface{}
840 var where string
841 if err != nil {
842 honker := arg
843 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
844 where = "dt < ? and honker = ?"
845 sqlargs = append(sqlargs, expdate)
846 sqlargs = append(sqlargs, honker)
847 } else {
848 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
849 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
850 sqlargs = append(sqlargs, expdate)
851 }
852 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
853 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
854 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
855 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
856
857 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
858 for _, u := range allusers() {
859 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)
860 }
861
862 filexids := make(map[string]bool)
863 blobdb := openblobdb()
864 rows, err := blobdb.Query("select xid from filedata")
865 if err != nil {
866 log.Fatal(err)
867 }
868 for rows.Next() {
869 var xid string
870 err = rows.Scan(&xid)
871 if err != nil {
872 log.Fatal(err)
873 }
874 filexids[xid] = true
875 }
876 rows.Close()
877 rows, err = db.Query("select xid from filemeta")
878 for rows.Next() {
879 var xid string
880 err = rows.Scan(&xid)
881 if err != nil {
882 log.Fatal(err)
883 }
884 delete(filexids, xid)
885 }
886 rows.Close()
887 tx, err := blobdb.Begin()
888 if err != nil {
889 log.Fatal(err)
890 }
891 for xid, _ := range filexids {
892 _, err = tx.Exec("delete from filedata where xid = ?", xid)
893 if err != nil {
894 log.Fatal(err)
895 }
896 }
897 err = tx.Commit()
898 if err != nil {
899 log.Fatal(err)
900 }
901}
902
903var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
904var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
905var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
906var stmtHonksFromLongAgo *sql.Stmt
907var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
908var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
909var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
910var stmtCheckFileData *sql.Stmt
911var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
912var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
913var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
914var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
915var stmtHonksForUserFirstClass *sql.Stmt
916var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
917var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
918var stmtGetTracks *sql.Stmt
919var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
920
921func preparetodie(db *sql.DB, s string) *sql.Stmt {
922 stmt, err := db.Prepare(s)
923 if err != nil {
924 log.Fatalf("error %s: %s", err, s)
925 }
926 return stmt
927}
928
929func prepareStatements(db *sql.DB) {
930 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")
931 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
932 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
933 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
934 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
935 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
936 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
937
938 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 "
939 limit := " order by honks.honkid desc limit 250"
940 smalllimit := " order by honks.honkid desc limit ?"
941 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
942 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
943 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
944 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
945 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
946 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
947 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
948 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
949 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
950 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
951 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
952 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
953 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
954 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)
955 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
956 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)
957 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
958 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)
959
960 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
961 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
962 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
963 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
964 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
965 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
966 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
967 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
968 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
969 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
970 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
971 blobdb := openblobdb()
972 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
973 stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
974 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
975 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
976 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
977 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
978 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
979 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
980 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
981 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
982 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
983 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
984 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")
985 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
986 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
987 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
988 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
989 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
990 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
991 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")
992 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
993 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
994 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")
995 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
996 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
997 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
998 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
999 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1000 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1001 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1002}