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