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 stmtDeleteHonker *sql.Stmt
905var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
906var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
907var stmtHonksFromLongAgo *sql.Stmt
908var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
909var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
910var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
911var stmtCheckFileData *sql.Stmt
912var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
913var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
914var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
915var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
916var stmtHonksForUserFirstClass *sql.Stmt
917var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
918var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
919var stmtGetTracks *sql.Stmt
920var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
921
922func preparetodie(db *sql.DB, s string) *sql.Stmt {
923 stmt, err := db.Prepare(s)
924 if err != nil {
925 log.Fatalf("error %s: %s", err, s)
926 }
927 return stmt
928}
929
930func prepareStatements(db *sql.DB) {
931 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")
932 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
933 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
934 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
935 stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
936 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
937 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
938 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
939
940 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 "
941 limit := " order by honks.honkid desc limit 250"
942 smalllimit := " order by honks.honkid desc limit ?"
943 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
944 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
945 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
946 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
947 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
948 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
949 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
950 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
951 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
952 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
953 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
954 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
955 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
956 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)
957 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
958 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)
959 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
960 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)
961
962 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
963 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
964 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
965 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
966 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
967 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
968 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
969 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
970 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
971 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
972 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
973 blobdb := openblobdb()
974 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
975 stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
976 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
977 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
978 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
979 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
980 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
981 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
982 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
983 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
984 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
985 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
986 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")
987 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
988 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
989 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
990 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
991 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
992 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
993 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")
994 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
995 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
996 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")
997 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
998 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
999 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1000 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1001 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1002 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1003 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1004}