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 finddonkid(fileid int64, url string) *Donk {
 578	donk := new(Donk)
 579	row := stmtFindFileId.QueryRow(fileid, url)
 580	err := row.Scan(&donk.XID, &donk.Local, &donk.Desc)
 581	if err == nil {
 582		donk.FileID = fileid
 583		return donk
 584	}
 585	if err != sql.ErrNoRows {
 586		elog.Printf("error finding file: %s", err)
 587	}
 588	return nil
 589}
 590
 591func finddonk(url string) *Donk {
 592	donk := new(Donk)
 593	row := stmtFindFile.QueryRow(url)
 594	err := row.Scan(&donk.FileID, &donk.XID)
 595	if err == nil {
 596		return donk
 597	}
 598	if err != sql.ErrNoRows {
 599		elog.Printf("error finding file: %s", err)
 600	}
 601	return nil
 602}
 603
 604func savechonk(ch *Chonk) error {
 605	dt := ch.Date.UTC().Format(dbtimeformat)
 606	db := opendatabase()
 607	tx, err := db.Begin()
 608	if err != nil {
 609		elog.Printf("can't begin tx: %s", err)
 610		return err
 611	}
 612
 613	res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
 614	if err == nil {
 615		ch.ID, _ = res.LastInsertId()
 616		for _, d := range ch.Donks {
 617			_, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
 618			if err != nil {
 619				elog.Printf("error saving donk: %s", err)
 620				break
 621			}
 622		}
 623		chatplusone(tx, ch.UserID)
 624		err = tx.Commit()
 625	} else {
 626		tx.Rollback()
 627	}
 628	return err
 629}
 630
 631func chatplusone(tx *sql.Tx, userid int64) {
 632	var user *WhatAbout
 633	ok := somenumberedusers.Get(userid, &user)
 634	if !ok {
 635		return
 636	}
 637	options := user.Options
 638	options.ChatCount += 1
 639	j, err := jsonify(options)
 640	if err == nil {
 641		_, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
 642	}
 643	if err != nil {
 644		elog.Printf("error plussing chat: %s", err)
 645	}
 646	somenamedusers.Clear(user.Name)
 647	somenumberedusers.Clear(user.ID)
 648}
 649
 650func chatnewnone(userid int64) {
 651	var user *WhatAbout
 652	ok := somenumberedusers.Get(userid, &user)
 653	if !ok || user.Options.ChatCount == 0 {
 654		return
 655	}
 656	options := user.Options
 657	options.ChatCount = 0
 658	j, err := jsonify(options)
 659	if err == nil {
 660		db := opendatabase()
 661		_, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
 662	}
 663	if err != nil {
 664		elog.Printf("error noneing chat: %s", err)
 665	}
 666	somenamedusers.Clear(user.Name)
 667	somenumberedusers.Clear(user.ID)
 668}
 669
 670func meplusone(tx *sql.Tx, userid int64) {
 671	var user *WhatAbout
 672	ok := somenumberedusers.Get(userid, &user)
 673	if !ok {
 674		return
 675	}
 676	options := user.Options
 677	options.MeCount += 1
 678	j, err := jsonify(options)
 679	if err == nil {
 680		_, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
 681	}
 682	if err != nil {
 683		elog.Printf("error plussing me: %s", err)
 684	}
 685	somenamedusers.Clear(user.Name)
 686	somenumberedusers.Clear(user.ID)
 687}
 688
 689func menewnone(userid int64) {
 690	var user *WhatAbout
 691	ok := somenumberedusers.Get(userid, &user)
 692	if !ok || user.Options.MeCount == 0 {
 693		return
 694	}
 695	options := user.Options
 696	options.MeCount = 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 me: %s", err)
 704	}
 705	somenamedusers.Clear(user.Name)
 706	somenumberedusers.Clear(user.ID)
 707}
 708
 709func loadchatter(userid int64) []*Chatter {
 710	duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
 711	rows, err := stmtLoadChonks.Query(userid, duedt)
 712	if err != nil {
 713		elog.Printf("error loading chonks: %s", err)
 714		return nil
 715	}
 716	defer rows.Close()
 717	chonks := make(map[string][]*Chonk)
 718	var allchonks []*Chonk
 719	for rows.Next() {
 720		ch := new(Chonk)
 721		var dt string
 722		err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
 723		if err != nil {
 724			elog.Printf("error scanning chonk: %s", err)
 725			continue
 726		}
 727		ch.Date, _ = time.Parse(dbtimeformat, dt)
 728		chonks[ch.Target] = append(chonks[ch.Target], ch)
 729		allchonks = append(allchonks, ch)
 730	}
 731	donksforchonks(allchonks)
 732	rows.Close()
 733	rows, err = stmtGetChatters.Query(userid)
 734	if err != nil {
 735		elog.Printf("error getting chatters: %s", err)
 736		return nil
 737	}
 738	for rows.Next() {
 739		var target string
 740		err = rows.Scan(&target)
 741		if err != nil {
 742			elog.Printf("error scanning chatter: %s", target)
 743			continue
 744		}
 745		if _, ok := chonks[target]; !ok {
 746			chonks[target] = []*Chonk{}
 747
 748		}
 749	}
 750	var chatter []*Chatter
 751	for target, chonks := range chonks {
 752		chatter = append(chatter, &Chatter{
 753			Target: target,
 754			Chonks: chonks,
 755		})
 756	}
 757	sort.Slice(chatter, func(i, j int) bool {
 758		a, b := chatter[i], chatter[j]
 759		if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
 760			if len(a.Chonks) == len(b.Chonks) {
 761				return a.Target < b.Target
 762			}
 763			return len(a.Chonks) > len(b.Chonks)
 764		}
 765		return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
 766	})
 767
 768	return chatter
 769}
 770
 771func savehonk(h *Honk) error {
 772	dt := h.Date.UTC().Format(dbtimeformat)
 773	aud := strings.Join(h.Audience, " ")
 774
 775	db := opendatabase()
 776	tx, err := db.Begin()
 777	if err != nil {
 778		elog.Printf("can't begin tx: %s", err)
 779		return err
 780	}
 781
 782	res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
 783		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
 784		h.Oonker, h.Flags)
 785	if err == nil {
 786		h.ID, _ = res.LastInsertId()
 787		err = saveextras(tx, h)
 788	}
 789	if err == nil {
 790		if h.Whofore == 1 {
 791			meplusone(tx, h.UserID)
 792		}
 793		err = tx.Commit()
 794	} else {
 795		tx.Rollback()
 796	}
 797	if err != nil {
 798		elog.Printf("error saving honk: %s", err)
 799	}
 800	honkhonkline()
 801	return err
 802}
 803
 804func updatehonk(h *Honk) error {
 805	old := getxonk(h.UserID, h.XID)
 806	oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
 807	dt := h.Date.UTC().Format(dbtimeformat)
 808
 809	db := opendatabase()
 810	tx, err := db.Begin()
 811	if err != nil {
 812		elog.Printf("can't begin tx: %s", err)
 813		return err
 814	}
 815
 816	err = deleteextras(tx, h.ID, false)
 817	if err == nil {
 818		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
 819	}
 820	if err == nil {
 821		err = saveextras(tx, h)
 822	}
 823	if err == nil {
 824		var j string
 825		j, err = jsonify(&oldrev)
 826		if err == nil {
 827			_, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
 828		}
 829		if err != nil {
 830			elog.Printf("error saving oldrev: %s", err)
 831		}
 832	}
 833	if err == nil {
 834		err = tx.Commit()
 835	} else {
 836		tx.Rollback()
 837	}
 838	if err != nil {
 839		elog.Printf("error updating honk %d: %s", h.ID, err)
 840	}
 841	return err
 842}
 843
 844func deletehonk(honkid int64) error {
 845	db := opendatabase()
 846	tx, err := db.Begin()
 847	if err != nil {
 848		elog.Printf("can't begin tx: %s", err)
 849		return err
 850	}
 851
 852	err = deleteextras(tx, honkid, true)
 853	if err == nil {
 854		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
 855	}
 856	if err == nil {
 857		err = tx.Commit()
 858	} else {
 859		tx.Rollback()
 860	}
 861	if err != nil {
 862		elog.Printf("error deleting honk %d: %s", honkid, err)
 863	}
 864	return err
 865}
 866
 867func saveextras(tx *sql.Tx, h *Honk) error {
 868	for _, d := range h.Donks {
 869		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
 870		if err != nil {
 871			elog.Printf("error saving donk: %s", err)
 872			return err
 873		}
 874	}
 875	for _, o := range h.Onts {
 876		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
 877		if err != nil {
 878			elog.Printf("error saving ont: %s", err)
 879			return err
 880		}
 881	}
 882	if p := h.Place; p != nil {
 883		j, err := jsonify(p)
 884		if err == nil {
 885			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
 886		}
 887		if err != nil {
 888			elog.Printf("error saving place: %s", err)
 889			return err
 890		}
 891	}
 892	if t := h.Time; t != nil {
 893		j, err := jsonify(t)
 894		if err == nil {
 895			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
 896		}
 897		if err != nil {
 898			elog.Printf("error saving time: %s", err)
 899			return err
 900		}
 901	}
 902	if m := h.Mentions; len(m) > 0 {
 903		j, err := jsonify(m)
 904		if err == nil {
 905			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
 906		}
 907		if err != nil {
 908			elog.Printf("error saving mentions: %s", err)
 909			return err
 910		}
 911	}
 912	return nil
 913}
 914
 915var baxonker sync.Mutex
 916
 917func addreaction(user *WhatAbout, xid string, who, react string) {
 918	baxonker.Lock()
 919	defer baxonker.Unlock()
 920	h := getxonk(user.ID, xid)
 921	if h == nil {
 922		return
 923	}
 924	h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
 925	j, _ := jsonify(h.Badonks)
 926	db := opendatabase()
 927	tx, _ := db.Begin()
 928	_, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
 929	_, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
 930	tx.Commit()
 931}
 932
 933func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
 934	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
 935	if err != nil {
 936		return err
 937	}
 938	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
 939	if err != nil {
 940		return err
 941	}
 942	if everything {
 943		_, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
 944	} else {
 945		_, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
 946	}
 947	if err != nil {
 948		return err
 949	}
 950	return nil
 951}
 952
 953func jsonify(what interface{}) (string, error) {
 954	var buf bytes.Buffer
 955	e := json.NewEncoder(&buf)
 956	e.SetEscapeHTML(false)
 957	e.SetIndent("", "")
 958	err := e.Encode(what)
 959	return buf.String(), err
 960}
 961
 962func unjsonify(s string, dest interface{}) error {
 963	d := json.NewDecoder(strings.NewReader(s))
 964	err := d.Decode(dest)
 965	return err
 966}
 967
 968func getxonker(what, flav string) string {
 969	var res string
 970	row := stmtGetXonker.QueryRow(what, flav)
 971	row.Scan(&res)
 972	return res
 973}
 974
 975func savexonker(what, value, flav, when string) {
 976	stmtSaveXonker.Exec(what, value, flav, when)
 977}
 978
 979func savehonker(user *WhatAbout, url, name, flavor, combos, mj string) (int64, error) {
 980	var owner string
 981	if url[0] == '#' {
 982		flavor = "peep"
 983		if name == "" {
 984			name = url[1:]
 985		}
 986		owner = url
 987	} else {
 988		info, err := investigate(url)
 989		if err != nil {
 990			ilog.Printf("failed to investigate honker: %s", err)
 991			return 0, err
 992		}
 993		url = info.XID
 994		if name == "" {
 995			name = info.Name
 996		}
 997		owner = info.Owner
 998	}
 999
1000	var x string
1001	db := opendatabase()
1002	row := db.QueryRow("select xid from honkers where xid = ? and userid = ? and flavor in ('sub', 'unsub', 'peep')", url, user.ID)
1003	err := row.Scan(&x)
1004	if err != sql.ErrNoRows {
1005		if err != nil {
1006			elog.Printf("honker scan err: %s", err)
1007		} else {
1008			err = fmt.Errorf("it seems you are already subscribed to them")
1009		}
1010		return 0, err
1011	}
1012
1013	res, err := stmtSaveHonker.Exec(user.ID, name, url, flavor, combos, owner, mj)
1014	if err != nil {
1015		elog.Print(err)
1016		return 0, err
1017	}
1018	honkerid, _ := res.LastInsertId()
1019	return honkerid, nil
1020}
1021
1022func cleanupdb(arg string) {
1023	db := opendatabase()
1024	days, err := strconv.Atoi(arg)
1025	var sqlargs []interface{}
1026	var where string
1027	if err != nil {
1028		honker := arg
1029		expdate := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
1030		where = "dt < ? and honker = ?"
1031		sqlargs = append(sqlargs, expdate)
1032		sqlargs = append(sqlargs, honker)
1033	} else {
1034		expdate := time.Now().Add(-time.Duration(days) * 24 * time.Hour).UTC().Format(dbtimeformat)
1035		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
1036		sqlargs = append(sqlargs, expdate)
1037	}
1038	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
1039	doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
1040	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
1041	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
1042
1043	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
1044	for _, u := range allusers() {
1045		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)
1046	}
1047
1048	filexids := make(map[string]bool)
1049	blobdb := openblobdb()
1050	rows, err := blobdb.Query("select xid from filedata")
1051	if err != nil {
1052		elog.Fatal(err)
1053	}
1054	for rows.Next() {
1055		var xid string
1056		err = rows.Scan(&xid)
1057		if err != nil {
1058			elog.Fatal(err)
1059		}
1060		filexids[xid] = true
1061	}
1062	rows.Close()
1063	rows, err = db.Query("select xid from filemeta")
1064	for rows.Next() {
1065		var xid string
1066		err = rows.Scan(&xid)
1067		if err != nil {
1068			elog.Fatal(err)
1069		}
1070		delete(filexids, xid)
1071	}
1072	rows.Close()
1073	tx, err := blobdb.Begin()
1074	if err != nil {
1075		elog.Fatal(err)
1076	}
1077	for xid, _ := range filexids {
1078		_, err = tx.Exec("delete from filedata where xid = ?", xid)
1079		if err != nil {
1080			elog.Fatal(err)
1081		}
1082	}
1083	err = tx.Commit()
1084	if err != nil {
1085		elog.Fatal(err)
1086	}
1087}
1088
1089var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1090var stmtDeleteHonker *sql.Stmt
1091var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1092var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1093var stmtHonksFromLongAgo *sql.Stmt
1094var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1095var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1096var stmtFindFile, stmtFindFileId, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1097var stmtCheckFileData *sql.Stmt
1098var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1099var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1100var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker, stmtDeleteOldXonkers *sql.Stmt
1101var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1102var stmtHonksForUserFirstClass *sql.Stmt
1103var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1104var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1105var stmtGetTracks *sql.Stmt
1106var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1107
1108func preparetodie(db *sql.DB, s string) *sql.Stmt {
1109	stmt, err := db.Prepare(s)
1110	if err != nil {
1111		elog.Fatalf("error %s: %s", err, s)
1112	}
1113	return stmt
1114}
1115
1116func prepareStatements(db *sql.DB) {
1117	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")
1118	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1119	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1120	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1121	stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1122	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1123	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1124	stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1125
1126	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 "
1127	limit := " order by honks.honkid desc limit 250"
1128	smalllimit := " order by honks.honkid desc limit ?"
1129	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1130	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1131	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
1132	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1133	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1134	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1135	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1136	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1137	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1138	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
1139	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
1140	stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
1141	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1142	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)
1143	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1144	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)
1145	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1146	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)
1147
1148	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1149	stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1150	stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1151	stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1152	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1153	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1154	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
1155	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1156	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1157	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1158	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1159	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1160	blobdb := openblobdb()
1161	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1162	stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1163	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1164	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1165	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1166	stmtFindFileId = preparetodie(db, "select xid, local, description from filemeta where fileid = ? and url = ? and local = 1")
1167	stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1168	stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1169	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1170	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1171	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1172	stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1173	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1174	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")
1175	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1176	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1177	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1178	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1179	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1180	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1181	stmtDeleteOldXonkers = preparetodie(db, "delete from xonkers where flavor = ? and dt < ?")
1182	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")
1183	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1184	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1185	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")
1186	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1187	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1188	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1189	stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1190	stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1191	stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1192	stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1193}