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 "database/sql"
21 "encoding/json"
22 "fmt"
23 "html/template"
24 "log"
25 "sort"
26 "strconv"
27 "strings"
28 "time"
29
30 "humungus.tedunangst.com/r/webs/cache"
31 "humungus.tedunangst.com/r/webs/httpsig"
32 "humungus.tedunangst.com/r/webs/login"
33 "humungus.tedunangst.com/r/webs/mz"
34)
35
36func userfromrow(row *sql.Row) (*WhatAbout, error) {
37 user := new(WhatAbout)
38 var seckey, options string
39 err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &seckey, &options)
40 if err == nil {
41 user.SecKey, _, err = httpsig.DecodeKey(seckey)
42 }
43 if err != nil {
44 return nil, err
45 }
46 if user.ID > 0 {
47 user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
48 err = unjsonify(options, &user.Options)
49 if err != nil {
50 log.Printf("error processing user options: %s", err)
51 }
52 } else {
53 user.URL = fmt.Sprintf("https://%s/%s", serverName, user.Name)
54 }
55 if user.Options.Reaction == "" {
56 user.Options.Reaction = "none"
57 }
58 var marker mz.Marker
59 marker.HashLinker = ontoreplacer
60 marker.AtLinker = attoreplacer
61 user.HTAbout = template.HTML(marker.Mark(user.About))
62
63 return user, nil
64}
65
66var somenamedusers = cache.New(cache.Options{Filler: func(name string) (*WhatAbout, bool) {
67 row := stmtUserByName.QueryRow(name)
68 user, err := userfromrow(row)
69 if err != nil {
70 return nil, false
71 }
72 return user, true
73}})
74
75var somenumberedusers = cache.New(cache.Options{Filler: func(userid int64) (*WhatAbout, bool) {
76 row := stmtUserByNumber.QueryRow(userid)
77 user, err := userfromrow(row)
78 if err != nil {
79 return nil, false
80 }
81 return user, true
82}})
83
84func getserveruser() *WhatAbout {
85 var user *WhatAbout
86 ok := somenumberedusers.Get(serverUID, &user)
87 if !ok {
88 log.Panicf("lost server user")
89 }
90 return user
91}
92
93func butwhatabout(name string) (*WhatAbout, error) {
94 var user *WhatAbout
95 ok := somenamedusers.Get(name, &user)
96 if !ok {
97 return nil, fmt.Errorf("no user: %s", name)
98 }
99 return user, nil
100}
101
102var honkerinvalidator cache.Invalidator
103
104func gethonkers(userid int64) []*Honker {
105 rows, err := stmtHonkers.Query(userid)
106 if err != nil {
107 log.Printf("error querying honkers: %s", err)
108 return nil
109 }
110 defer rows.Close()
111 var honkers []*Honker
112 for rows.Next() {
113 h := new(Honker)
114 var combos, meta string
115 err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor, &combos, &meta)
116 if err == nil {
117 err = unjsonify(meta, &h.Meta)
118 }
119 if err != nil {
120 log.Printf("error scanning honker: %s", err)
121 continue
122 }
123 h.Combos = strings.Split(strings.TrimSpace(combos), " ")
124 honkers = append(honkers, h)
125 }
126 return honkers
127}
128
129func getdubs(userid int64) []*Honker {
130 rows, err := stmtDubbers.Query(userid)
131 return dubsfromrows(rows, err)
132}
133
134func getnameddubs(userid int64, name string) []*Honker {
135 rows, err := stmtNamedDubbers.Query(userid, name)
136 return dubsfromrows(rows, err)
137}
138
139func dubsfromrows(rows *sql.Rows, err error) []*Honker {
140 if err != nil {
141 log.Printf("error querying dubs: %s", err)
142 return nil
143 }
144 defer rows.Close()
145 var honkers []*Honker
146 for rows.Next() {
147 h := new(Honker)
148 err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor)
149 if err != nil {
150 log.Printf("error scanning honker: %s", err)
151 return nil
152 }
153 honkers = append(honkers, h)
154 }
155 return honkers
156}
157
158func allusers() []login.UserInfo {
159 var users []login.UserInfo
160 rows, _ := opendatabase().Query("select userid, username from users where userid > 0")
161 defer rows.Close()
162 for rows.Next() {
163 var u login.UserInfo
164 rows.Scan(&u.UserID, &u.Username)
165 users = append(users, u)
166 }
167 return users
168}
169
170func getxonk(userid int64, xid string) *Honk {
171 row := stmtOneXonk.QueryRow(userid, xid)
172 return scanhonk(row)
173}
174
175func getbonk(userid int64, xid string) *Honk {
176 row := stmtOneBonk.QueryRow(userid, xid)
177 return scanhonk(row)
178}
179
180func getpublichonks() []*Honk {
181 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
182 rows, err := stmtPublicHonks.Query(dt, 100)
183 return getsomehonks(rows, err)
184}
185func geteventhonks(userid int64) []*Honk {
186 rows, err := stmtEventHonks.Query(userid, 25)
187 honks := getsomehonks(rows, err)
188 sort.Slice(honks, func(i, j int) bool {
189 var t1, t2 time.Time
190 if honks[i].Time == nil {
191 t1 = honks[i].Date
192 } else {
193 t1 = honks[i].Time.StartTime
194 }
195 if honks[j].Time == nil {
196 t2 = honks[j].Date
197 } else {
198 t2 = honks[j].Time.StartTime
199 }
200 return t1.After(t2)
201 })
202 now := time.Now().Add(-24 * time.Hour)
203 for i, h := range honks {
204 t := h.Date
205 if tm := h.Time; tm != nil {
206 t = tm.StartTime
207 }
208 if t.Before(now) {
209 honks = honks[:i]
210 break
211 }
212 }
213 reversehonks(honks)
214 return honks
215}
216func gethonksbyuser(name string, includeprivate bool, wanted int64) []*Honk {
217 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
218 limit := 50
219 whofore := 2
220 if includeprivate {
221 whofore = 3
222 }
223 rows, err := stmtUserHonks.Query(wanted, whofore, name, dt, limit)
224 return getsomehonks(rows, err)
225}
226func gethonksforuser(userid int64, wanted int64) []*Honk {
227 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
228 rows, err := stmtHonksForUser.Query(wanted, userid, dt, userid, userid)
229 return getsomehonks(rows, err)
230}
231func gethonksforuserfirstclass(userid int64, wanted int64) []*Honk {
232 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
233 rows, err := stmtHonksForUserFirstClass.Query(wanted, userid, dt, userid, userid)
234 return getsomehonks(rows, err)
235}
236
237func gethonksforme(userid int64, wanted int64) []*Honk {
238 dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
239 rows, err := stmtHonksForMe.Query(wanted, userid, dt, userid)
240 return getsomehonks(rows, err)
241}
242func gethonksfromlongago(userid int64, wanted int64) []*Honk {
243 now := time.Now().UTC()
244 now = time.Date(now.Year()-1, now.Month(), now.Day(), now.Hour(), now.Minute(),
245 now.Second(), 0, now.Location())
246 dt1 := now.Add(-36 * time.Hour).Format(dbtimeformat)
247 dt2 := now.Add(12 * time.Hour).Format(dbtimeformat)
248 rows, err := stmtHonksFromLongAgo.Query(wanted, userid, dt1, dt2, userid)
249 return getsomehonks(rows, err)
250}
251func getsavedhonks(userid int64, wanted int64) []*Honk {
252 rows, err := stmtHonksISaved.Query(wanted, userid)
253 return getsomehonks(rows, err)
254}
255func gethonksbyhonker(userid int64, honker string, wanted int64) []*Honk {
256 rows, err := stmtHonksByHonker.Query(wanted, userid, honker, userid)
257 return getsomehonks(rows, err)
258}
259func gethonksbyxonker(userid int64, xonker string, wanted int64) []*Honk {
260 rows, err := stmtHonksByXonker.Query(wanted, userid, xonker, xonker, userid)
261 return getsomehonks(rows, err)
262}
263func gethonksbycombo(userid int64, combo string, wanted int64) []*Honk {
264 combo = "% " + combo + " %"
265 rows, err := stmtHonksByCombo.Query(wanted, userid, userid, combo, userid, wanted, userid, combo, userid)
266 return getsomehonks(rows, err)
267}
268func gethonksbyconvoy(userid int64, convoy string, wanted int64) []*Honk {
269 rows, err := stmtHonksByConvoy.Query(wanted, userid, userid, convoy)
270 honks := getsomehonks(rows, err)
271 return honks
272}
273func gethonksbysearch(userid int64, q string, wanted int64) []*Honk {
274 var queries []string
275 var params []interface{}
276 queries = append(queries, "honks.honkid > ?")
277 params = append(params, wanted)
278 queries = append(queries, "honks.userid = ?")
279 params = append(params, userid)
280
281 terms := strings.Split(q, " ")
282 for _, t := range terms {
283 if t == "" {
284 continue
285 }
286 negate := " "
287 if t[0] == '-' {
288 t = t[1:]
289 negate = " not "
290 }
291 if t == "" {
292 continue
293 }
294 if strings.HasPrefix(t, "site:") {
295 site := t[5:]
296 site = "%" + site + "%"
297 queries = append(queries, "xid"+negate+"like ?")
298 params = append(params, site)
299 continue
300 }
301 if strings.HasPrefix(t, "honker:") {
302 honker := t[7:]
303 xid := fullname(honker, userid)
304 if xid != "" {
305 honker = xid
306 }
307 queries = append(queries, negate+"(honks.honker = ? or honks.oonker = ?)")
308 params = append(params, honker)
309 params = append(params, honker)
310 continue
311 }
312 t = "%" + t + "%"
313 queries = append(queries, "noise"+negate+"like ?")
314 params = append(params, t)
315 }
316
317 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 "
318 where := "where " + strings.Join(queries, " and ")
319 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
320 limit := " order by honks.honkid desc limit 250"
321 params = append(params, userid)
322 rows, err := opendatabase().Query(selecthonks+where+butnotthose+limit, params...)
323 honks := getsomehonks(rows, err)
324 return honks
325}
326func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
327 rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
328 honks := getsomehonks(rows, err)
329 return honks
330}
331
332func reversehonks(honks []*Honk) {
333 for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
334 honks[i], honks[j] = honks[j], honks[i]
335 }
336}
337
338func getsomehonks(rows *sql.Rows, err error) []*Honk {
339 if err != nil {
340 log.Printf("error querying honks: %s", err)
341 return nil
342 }
343 defer rows.Close()
344 var honks []*Honk
345 for rows.Next() {
346 h := scanhonk(rows)
347 if h != nil {
348 honks = append(honks, h)
349 }
350 }
351 rows.Close()
352 donksforhonks(honks)
353 return honks
354}
355
356type RowLike interface {
357 Scan(dest ...interface{}) error
358}
359
360func scanhonk(row RowLike) *Honk {
361 h := new(Honk)
362 var dt, aud string
363 err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
364 &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
365 if err != nil {
366 if err != sql.ErrNoRows {
367 log.Printf("error scanning honk: %s", err)
368 }
369 return nil
370 }
371 h.Date, _ = time.Parse(dbtimeformat, dt)
372 h.Audience = strings.Split(aud, " ")
373 h.Public = loudandproud(h.Audience)
374 return h
375}
376
377func donksforhonks(honks []*Honk) {
378 db := opendatabase()
379 var ids []string
380 hmap := make(map[int64]*Honk)
381 for _, h := range honks {
382 ids = append(ids, fmt.Sprintf("%d", h.ID))
383 hmap[h.ID] = h
384 }
385 idset := strings.Join(ids, ",")
386 // grab donks
387 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)
388 rows, err := db.Query(q)
389 if err != nil {
390 log.Printf("error querying donks: %s", err)
391 return
392 }
393 defer rows.Close()
394 for rows.Next() {
395 var hid int64
396 d := new(Donk)
397 err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
398 if err != nil {
399 log.Printf("error scanning donk: %s", err)
400 continue
401 }
402 h := hmap[hid]
403 h.Donks = append(h.Donks, d)
404 }
405 rows.Close()
406
407 // grab onts
408 q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", idset)
409 rows, err = db.Query(q)
410 if err != nil {
411 log.Printf("error querying onts: %s", err)
412 return
413 }
414 defer rows.Close()
415 for rows.Next() {
416 var hid int64
417 var o string
418 err = rows.Scan(&hid, &o)
419 if err != nil {
420 log.Printf("error scanning donk: %s", err)
421 continue
422 }
423 h := hmap[hid]
424 h.Onts = append(h.Onts, o)
425 }
426 rows.Close()
427
428 // grab meta
429 q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", idset)
430 rows, err = db.Query(q)
431 if err != nil {
432 log.Printf("error querying honkmeta: %s", err)
433 return
434 }
435 defer rows.Close()
436 for rows.Next() {
437 var hid int64
438 var genus, j string
439 err = rows.Scan(&hid, &genus, &j)
440 if err != nil {
441 log.Printf("error scanning honkmeta: %s", err)
442 continue
443 }
444 h := hmap[hid]
445 switch genus {
446 case "place":
447 p := new(Place)
448 err = unjsonify(j, p)
449 if err != nil {
450 log.Printf("error parsing place: %s", err)
451 continue
452 }
453 h.Place = p
454 case "time":
455 t := new(Time)
456 err = unjsonify(j, t)
457 if err != nil {
458 log.Printf("error parsing time: %s", err)
459 continue
460 }
461 h.Time = t
462 case "mentions":
463 err = unjsonify(j, &h.Mentions)
464 if err != nil {
465 log.Printf("error parsing mentions: %s", err)
466 continue
467 }
468 case "oldrev":
469 default:
470 log.Printf("unknown meta genus: %s", genus)
471 }
472 }
473 rows.Close()
474}
475
476func donksforchonks(chonks []*Chonk) {
477 db := opendatabase()
478 var ids []string
479 chmap := make(map[int64]*Chonk)
480 for _, ch := range chonks {
481 ids = append(ids, fmt.Sprintf("%d", ch.ID))
482 chmap[ch.ID] = ch
483 }
484 idset := strings.Join(ids, ",")
485 // grab donks
486 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)
487 rows, err := db.Query(q)
488 if err != nil {
489 log.Printf("error querying donks: %s", err)
490 return
491 }
492 defer rows.Close()
493 for rows.Next() {
494 var chid int64
495 d := new(Donk)
496 err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
497 if err != nil {
498 log.Printf("error scanning donk: %s", err)
499 continue
500 }
501 ch := chmap[chid]
502 ch.Donks = append(ch.Donks, d)
503 }
504}
505
506func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
507 res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
508 if err != nil {
509 return 0, err
510 }
511 fileid, _ := res.LastInsertId()
512 if local {
513 _, err = stmtSaveFileData.Exec(xid, media, data)
514 if err != nil {
515 return 0, err
516 }
517 }
518 return fileid, nil
519}
520
521func finddonk(url string) *Donk {
522 donk := new(Donk)
523 row := stmtFindFile.QueryRow(url)
524 err := row.Scan(&donk.FileID, &donk.XID)
525 if err == nil {
526 return donk
527 }
528 if err != sql.ErrNoRows {
529 log.Printf("error finding file: %s", err)
530 }
531 return nil
532}
533
534func savechonk(ch *Chonk) error {
535 dt := ch.Date.UTC().Format(dbtimeformat)
536 db := opendatabase()
537 tx, err := db.Begin()
538 if err != nil {
539 log.Printf("can't begin tx: %s", err)
540 return err
541 }
542
543 res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
544 if err == nil {
545 ch.ID, _ = res.LastInsertId()
546 for _, d := range ch.Donks {
547 _, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
548 if err != nil {
549 log.Printf("error saving donk: %s", err)
550 break
551 }
552 }
553 err = tx.Commit()
554 } else {
555 tx.Rollback()
556 }
557 return err
558}
559
560func loadchatter(userid int64) []*Chatter {
561 duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
562 rows, err := stmtLoadChonks.Query(userid, duedt)
563 if err != nil {
564 log.Printf("error loading chonks: %s", err)
565 return nil
566 }
567 defer rows.Close()
568 chonks := make(map[string][]*Chonk)
569 var allchonks []*Chonk
570 for rows.Next() {
571 ch := new(Chonk)
572 var dt string
573 err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
574 if err != nil {
575 log.Printf("error scanning chonk: %s", err)
576 continue
577 }
578 ch.Date, _ = time.Parse(dbtimeformat, dt)
579 chonks[ch.Target] = append(chonks[ch.Target], ch)
580 allchonks = append(allchonks, ch)
581 }
582 donksforchonks(allchonks)
583 rows.Close()
584 rows, err = stmtGetChatters.Query(userid)
585 if err != nil {
586 log.Printf("error getting chatters: %s", err)
587 return nil
588 }
589 for rows.Next() {
590 var target string
591 err = rows.Scan(&target)
592 if err != nil {
593 log.Printf("error scanning chatter: %s", target)
594 continue
595 }
596 if _, ok := chonks[target]; !ok {
597 chonks[target] = []*Chonk{}
598
599 }
600 }
601 var chatter []*Chatter
602 for target, chonks := range chonks {
603 chatter = append(chatter, &Chatter{
604 Target: target,
605 Chonks: chonks,
606 })
607 }
608 sort.Slice(chatter, func(i, j int) bool {
609 a, b := chatter[i], chatter[j]
610 if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
611 if len(a.Chonks) == len(b.Chonks) {
612 return a.Target < b.Target
613 }
614 return len(a.Chonks) > len(b.Chonks)
615 }
616 return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
617 })
618
619 return chatter
620}
621
622func savehonk(h *Honk) error {
623 dt := h.Date.UTC().Format(dbtimeformat)
624 aud := strings.Join(h.Audience, " ")
625
626 db := opendatabase()
627 tx, err := db.Begin()
628 if err != nil {
629 log.Printf("can't begin tx: %s", err)
630 return err
631 }
632
633 res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
634 aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
635 h.Oonker, h.Flags)
636 if err == nil {
637 h.ID, _ = res.LastInsertId()
638 err = saveextras(tx, h)
639 }
640 if err == nil {
641 err = tx.Commit()
642 } else {
643 tx.Rollback()
644 }
645 if err != nil {
646 log.Printf("error saving honk: %s", err)
647 }
648 honkhonkline()
649 return err
650}
651
652func updatehonk(h *Honk) error {
653 old := getxonk(h.UserID, h.XID)
654 oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
655 dt := h.Date.UTC().Format(dbtimeformat)
656
657 db := opendatabase()
658 tx, err := db.Begin()
659 if err != nil {
660 log.Printf("can't begin tx: %s", err)
661 return err
662 }
663
664 err = deleteextras(tx, h.ID, false)
665 if err == nil {
666 _, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
667 }
668 if err == nil {
669 err = saveextras(tx, h)
670 }
671 if err == nil {
672 var j string
673 j, err = jsonify(&oldrev)
674 if err == nil {
675 _, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
676 }
677 if err != nil {
678 log.Printf("error saving oldrev: %s", err)
679 }
680 }
681 if err == nil {
682 err = tx.Commit()
683 } else {
684 tx.Rollback()
685 }
686 if err != nil {
687 log.Printf("error updating honk %d: %s", h.ID, err)
688 }
689 return err
690}
691
692func deletehonk(honkid int64) error {
693 db := opendatabase()
694 tx, err := db.Begin()
695 if err != nil {
696 log.Printf("can't begin tx: %s", err)
697 return err
698 }
699
700 err = deleteextras(tx, honkid, true)
701 if err == nil {
702 _, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
703 }
704 if err == nil {
705 err = tx.Commit()
706 } else {
707 tx.Rollback()
708 }
709 if err != nil {
710 log.Printf("error deleting honk %d: %s", honkid, err)
711 }
712 return err
713}
714
715func saveextras(tx *sql.Tx, h *Honk) error {
716 for _, d := range h.Donks {
717 _, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
718 if err != nil {
719 log.Printf("error saving donk: %s", err)
720 return err
721 }
722 }
723 for _, o := range h.Onts {
724 _, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
725 if err != nil {
726 log.Printf("error saving ont: %s", err)
727 return err
728 }
729 }
730 if p := h.Place; p != nil {
731 j, err := jsonify(p)
732 if err == nil {
733 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
734 }
735 if err != nil {
736 log.Printf("error saving place: %s", err)
737 return err
738 }
739 }
740 if t := h.Time; t != nil {
741 j, err := jsonify(t)
742 if err == nil {
743 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
744 }
745 if err != nil {
746 log.Printf("error saving time: %s", err)
747 return err
748 }
749 }
750 if m := h.Mentions; len(m) > 0 {
751 j, err := jsonify(m)
752 if err == nil {
753 _, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
754 }
755 if err != nil {
756 log.Printf("error saving mentions: %s", err)
757 return err
758 }
759 }
760 return nil
761}
762
763func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
764 _, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
765 if err != nil {
766 return err
767 }
768 _, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
769 if err != nil {
770 return err
771 }
772 if everything {
773 _, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
774 } else {
775 _, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
776 }
777 if err != nil {
778 return err
779 }
780 return nil
781}
782
783func jsonify(what interface{}) (string, error) {
784 var buf bytes.Buffer
785 e := json.NewEncoder(&buf)
786 e.SetEscapeHTML(false)
787 e.SetIndent("", "")
788 err := e.Encode(what)
789 return buf.String(), err
790}
791
792func unjsonify(s string, dest interface{}) error {
793 d := json.NewDecoder(strings.NewReader(s))
794 err := d.Decode(dest)
795 return err
796}
797
798func cleanupdb(arg string) {
799 db := opendatabase()
800 days, err := strconv.Atoi(arg)
801 var sqlargs []interface{}
802 var where string
803 if err != nil {
804 honker := arg
805 expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
806 where = "dt < ? and honker = ?"
807 sqlargs = append(sqlargs, expdate)
808 sqlargs = append(sqlargs, honker)
809 } else {
810 expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
811 where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
812 sqlargs = append(sqlargs, expdate)
813 }
814 doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
815 doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
816 doordie(db, "delete from onts where honkid not in (select honkid from honks)")
817 doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
818
819 doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
820 for _, u := range allusers() {
821 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)
822 }
823
824 filexids := make(map[string]bool)
825 blobdb := openblobdb()
826 rows, err := blobdb.Query("select xid from filedata")
827 if err != nil {
828 log.Fatal(err)
829 }
830 for rows.Next() {
831 var xid string
832 err = rows.Scan(&xid)
833 if err != nil {
834 log.Fatal(err)
835 }
836 filexids[xid] = true
837 }
838 rows.Close()
839 rows, err = db.Query("select xid from filemeta")
840 for rows.Next() {
841 var xid string
842 err = rows.Scan(&xid)
843 if err != nil {
844 log.Fatal(err)
845 }
846 delete(filexids, xid)
847 }
848 rows.Close()
849 tx, err := blobdb.Begin()
850 if err != nil {
851 log.Fatal(err)
852 }
853 for xid, _ := range filexids {
854 _, err = tx.Exec("delete from filedata where xid = ?", xid)
855 if err != nil {
856 log.Fatal(err)
857 }
858 }
859 err = tx.Commit()
860 if err != nil {
861 log.Fatal(err)
862 }
863}
864
865var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
866var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
867var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
868var stmtHonksFromLongAgo *sql.Stmt
869var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
870var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
871var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
872var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
873var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
874var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
875var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
876var stmtHonksForUserFirstClass *sql.Stmt
877var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
878var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
879var stmtGetTracks *sql.Stmt
880var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
881
882func preparetodie(db *sql.DB, s string) *sql.Stmt {
883 stmt, err := db.Prepare(s)
884 if err != nil {
885 log.Fatalf("error %s: %s", err, s)
886 }
887 return stmt
888}
889
890func prepareStatements(db *sql.DB) {
891 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")
892 stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
893 stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
894 stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
895 stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
896 stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
897 stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
898
899 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 "
900 limit := " order by honks.honkid desc limit 250"
901 smalllimit := " order by honks.honkid desc limit ?"
902 butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
903 stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
904 stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
905 stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
906 stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
907 stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
908 stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
909 myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
910 stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
911 stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
912 stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
913 stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
914 stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
915 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)
916 stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
917 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)
918 stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
919 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)
920
921 stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
922 stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
923 stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
924 stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
925 stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
926 stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
927 stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
928 stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
929 stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
930 stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
931 stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
932 blobdb := openblobdb()
933 stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
934 stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
935 stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
936 stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
937 stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
938 stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
939 stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
940 stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
941 stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
942 stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
943 stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
944 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")
945 stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
946 stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
947 stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
948 stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
949 stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
950 stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
951 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")
952 stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
953 stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
954 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")
955 stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
956 stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
957 stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
958 stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
959 stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
960 stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
961 stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
962}