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