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