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