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 <= 4; 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 t == "@me" {
 307			queries = append(queries, "whofore = 1")
 308			continue
 309		}
 310		if t == "@self" {
 311			queries = append(queries, "(whofore = 2 or whofore = 3)")
 312			continue
 313		}
 314		if strings.HasPrefix(t, "before:") {
 315			before := t[7:]
 316			queries = append(queries, "dt < ?")
 317			params = append(params, before)
 318			continue
 319		}
 320		if strings.HasPrefix(t, "after:") {
 321			after := t[6:]
 322			queries = append(queries, "dt > ?")
 323			params = append(params, after)
 324			continue
 325		}
 326		if strings.HasPrefix(t, "site:") {
 327			site := t[5:]
 328			site = "%" + site + "%"
 329			queries = append(queries, "xid"+negate+"like ?")
 330			params = append(params, site)
 331			continue
 332		}
 333		if strings.HasPrefix(t, "honker:") {
 334			honker := t[7:]
 335			xid := fullname(honker, userid)
 336			if xid != "" {
 337				honker = xid
 338			}
 339			queries = append(queries, negate+"(honks.honker = ? or honks.oonker = ?)")
 340			params = append(params, honker)
 341			params = append(params, honker)
 342			continue
 343		}
 344		t = "%" + t + "%"
 345		queries = append(queries, "noise"+negate+"like ?")
 346		params = append(params, t)
 347	}
 348
 349	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 "
 350	where := "where " + strings.Join(queries, " and ")
 351	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
 352	limit := " order by honks.honkid desc limit 250"
 353	params = append(params, userid)
 354	rows, err := opendatabase().Query(selecthonks+where+butnotthose+limit, params...)
 355	honks := getsomehonks(rows, err)
 356	return honks
 357}
 358func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
 359	rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
 360	honks := getsomehonks(rows, err)
 361	return honks
 362}
 363
 364func reversehonks(honks []*Honk) {
 365	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
 366		honks[i], honks[j] = honks[j], honks[i]
 367	}
 368}
 369
 370func getsomehonks(rows *sql.Rows, err error) []*Honk {
 371	if err != nil {
 372		elog.Printf("error querying honks: %s", err)
 373		return nil
 374	}
 375	defer rows.Close()
 376	var honks []*Honk
 377	for rows.Next() {
 378		h := scanhonk(rows)
 379		if h != nil {
 380			honks = append(honks, h)
 381		}
 382	}
 383	rows.Close()
 384	donksforhonks(honks)
 385	return honks
 386}
 387
 388type RowLike interface {
 389	Scan(dest ...interface{}) error
 390}
 391
 392func scanhonk(row RowLike) *Honk {
 393	h := new(Honk)
 394	var dt, aud string
 395	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
 396		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
 397	if err != nil {
 398		if err != sql.ErrNoRows {
 399			elog.Printf("error scanning honk: %s", err)
 400		}
 401		return nil
 402	}
 403	h.Date, _ = time.Parse(dbtimeformat, dt)
 404	h.Audience = strings.Split(aud, " ")
 405	h.Public = loudandproud(h.Audience)
 406	return h
 407}
 408
 409func donksforhonks(honks []*Honk) {
 410	db := opendatabase()
 411	var ids []string
 412	hmap := make(map[int64]*Honk)
 413	for _, h := range honks {
 414		ids = append(ids, fmt.Sprintf("%d", h.ID))
 415		hmap[h.ID] = h
 416	}
 417	idset := strings.Join(ids, ",")
 418	// grab donks
 419	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)
 420	rows, err := db.Query(q)
 421	if err != nil {
 422		elog.Printf("error querying donks: %s", err)
 423		return
 424	}
 425	defer rows.Close()
 426	for rows.Next() {
 427		var hid int64
 428		d := new(Donk)
 429		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
 430		if err != nil {
 431			elog.Printf("error scanning donk: %s", err)
 432			continue
 433		}
 434		d.External = !strings.HasPrefix(d.URL, serverPrefix)
 435		h := hmap[hid]
 436		h.Donks = append(h.Donks, d)
 437	}
 438	rows.Close()
 439
 440	// grab onts
 441	q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", idset)
 442	rows, err = db.Query(q)
 443	if err != nil {
 444		elog.Printf("error querying onts: %s", err)
 445		return
 446	}
 447	defer rows.Close()
 448	for rows.Next() {
 449		var hid int64
 450		var o string
 451		err = rows.Scan(&hid, &o)
 452		if err != nil {
 453			elog.Printf("error scanning donk: %s", err)
 454			continue
 455		}
 456		h := hmap[hid]
 457		h.Onts = append(h.Onts, o)
 458	}
 459	rows.Close()
 460
 461	// grab meta
 462	q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", idset)
 463	rows, err = db.Query(q)
 464	if err != nil {
 465		elog.Printf("error querying honkmeta: %s", err)
 466		return
 467	}
 468	defer rows.Close()
 469	for rows.Next() {
 470		var hid int64
 471		var genus, j string
 472		err = rows.Scan(&hid, &genus, &j)
 473		if err != nil {
 474			elog.Printf("error scanning honkmeta: %s", err)
 475			continue
 476		}
 477		h := hmap[hid]
 478		switch genus {
 479		case "place":
 480			p := new(Place)
 481			err = unjsonify(j, p)
 482			if err != nil {
 483				elog.Printf("error parsing place: %s", err)
 484				continue
 485			}
 486			h.Place = p
 487		case "time":
 488			t := new(Time)
 489			err = unjsonify(j, t)
 490			if err != nil {
 491				elog.Printf("error parsing time: %s", err)
 492				continue
 493			}
 494			h.Time = t
 495		case "mentions":
 496			err = unjsonify(j, &h.Mentions)
 497			if err != nil {
 498				elog.Printf("error parsing mentions: %s", err)
 499				continue
 500			}
 501		case "badonks":
 502			err = unjsonify(j, &h.Badonks)
 503			if err != nil {
 504				elog.Printf("error parsing badonks: %s", err)
 505				continue
 506			}
 507		case "wonkles":
 508		case "guesses":
 509		case "oldrev":
 510		default:
 511			elog.Printf("unknown meta genus: %s", genus)
 512		}
 513	}
 514	rows.Close()
 515}
 516
 517func donksforchonks(chonks []*Chonk) {
 518	db := opendatabase()
 519	var ids []string
 520	chmap := make(map[int64]*Chonk)
 521	for _, ch := range chonks {
 522		ids = append(ids, fmt.Sprintf("%d", ch.ID))
 523		chmap[ch.ID] = ch
 524	}
 525	idset := strings.Join(ids, ",")
 526	// grab donks
 527	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)
 528	rows, err := db.Query(q)
 529	if err != nil {
 530		elog.Printf("error querying donks: %s", err)
 531		return
 532	}
 533	defer rows.Close()
 534	for rows.Next() {
 535		var chid int64
 536		d := new(Donk)
 537		err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
 538		if err != nil {
 539			elog.Printf("error scanning donk: %s", err)
 540			continue
 541		}
 542		ch := chmap[chid]
 543		ch.Donks = append(ch.Donks, d)
 544	}
 545}
 546
 547func savefile(name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
 548	fileid, _, err := savefileandxid(name, desc, url, media, local, data)
 549	return fileid, err
 550}
 551
 552func hashfiledata(data []byte) string {
 553	h := sha512.New512_256()
 554	h.Write(data)
 555	return fmt.Sprintf("%x", h.Sum(nil))
 556}
 557
 558func savefileandxid(name string, desc string, url string, media string, local bool, data []byte) (int64, string, error) {
 559	var xid string
 560	if local {
 561		hash := hashfiledata(data)
 562		row := stmtCheckFileData.QueryRow(hash)
 563		err := row.Scan(&xid)
 564		if err == sql.ErrNoRows {
 565			xid = xfiltrate()
 566			switch media {
 567			case "image/png":
 568				xid += ".png"
 569			case "image/jpeg":
 570				xid += ".jpg"
 571			case "image/svg+xml":
 572				xid += ".svg"
 573			case "application/pdf":
 574				xid += ".pdf"
 575			case "text/plain":
 576				xid += ".txt"
 577			}
 578			_, err = stmtSaveFileData.Exec(xid, media, hash, data)
 579			if err != nil {
 580				return 0, "", err
 581			}
 582		} else if err != nil {
 583			elog.Printf("error checking file hash: %s", err)
 584			return 0, "", err
 585		}
 586		if url == "" {
 587			url = fmt.Sprintf("https://%s/d/%s", serverName, xid)
 588		}
 589	}
 590
 591	res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
 592	if err != nil {
 593		return 0, "", err
 594	}
 595	fileid, _ := res.LastInsertId()
 596	return fileid, xid, nil
 597}
 598
 599func finddonkid(fileid int64, url string) *Donk {
 600	donk := new(Donk)
 601	row := stmtFindFileId.QueryRow(fileid, url)
 602	err := row.Scan(&donk.XID, &donk.Local, &donk.Desc)
 603	if err == nil {
 604		donk.FileID = fileid
 605		return donk
 606	}
 607	if err != sql.ErrNoRows {
 608		elog.Printf("error finding file: %s", err)
 609	}
 610	return nil
 611}
 612
 613func finddonk(url string) *Donk {
 614	donk := new(Donk)
 615	row := stmtFindFile.QueryRow(url)
 616	err := row.Scan(&donk.FileID, &donk.XID)
 617	if err == nil {
 618		return donk
 619	}
 620	if err != sql.ErrNoRows {
 621		elog.Printf("error finding file: %s", err)
 622	}
 623	return nil
 624}
 625
 626func savechonk(ch *Chonk) error {
 627	dt := ch.Date.UTC().Format(dbtimeformat)
 628	db := opendatabase()
 629	tx, err := db.Begin()
 630	if err != nil {
 631		elog.Printf("can't begin tx: %s", err)
 632		return err
 633	}
 634
 635	res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
 636	if err == nil {
 637		ch.ID, _ = res.LastInsertId()
 638		for _, d := range ch.Donks {
 639			_, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
 640			if err != nil {
 641				elog.Printf("error saving donk: %s", err)
 642				break
 643			}
 644		}
 645		chatplusone(tx, ch.UserID)
 646		err = tx.Commit()
 647	} else {
 648		tx.Rollback()
 649	}
 650	return err
 651}
 652
 653func chatplusone(tx *sql.Tx, userid int64) {
 654	var user *WhatAbout
 655	ok := somenumberedusers.Get(userid, &user)
 656	if !ok {
 657		return
 658	}
 659	options := user.Options
 660	options.ChatCount += 1
 661	j, err := jsonify(options)
 662	if err == nil {
 663		_, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
 664	}
 665	if err != nil {
 666		elog.Printf("error plussing chat: %s", err)
 667	}
 668	somenamedusers.Clear(user.Name)
 669	somenumberedusers.Clear(user.ID)
 670}
 671
 672func chatnewnone(userid int64) {
 673	var user *WhatAbout
 674	ok := somenumberedusers.Get(userid, &user)
 675	if !ok || user.Options.ChatCount == 0 {
 676		return
 677	}
 678	options := user.Options
 679	options.ChatCount = 0
 680	j, err := jsonify(options)
 681	if err == nil {
 682		db := opendatabase()
 683		_, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
 684	}
 685	if err != nil {
 686		elog.Printf("error noneing chat: %s", err)
 687	}
 688	somenamedusers.Clear(user.Name)
 689	somenumberedusers.Clear(user.ID)
 690}
 691
 692func meplusone(tx *sql.Tx, userid int64) {
 693	var user *WhatAbout
 694	ok := somenumberedusers.Get(userid, &user)
 695	if !ok {
 696		return
 697	}
 698	options := user.Options
 699	options.MeCount += 1
 700	j, err := jsonify(options)
 701	if err == nil {
 702		_, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
 703	}
 704	if err != nil {
 705		elog.Printf("error plussing me: %s", err)
 706	}
 707	somenamedusers.Clear(user.Name)
 708	somenumberedusers.Clear(user.ID)
 709}
 710
 711func menewnone(userid int64) {
 712	var user *WhatAbout
 713	ok := somenumberedusers.Get(userid, &user)
 714	if !ok || user.Options.MeCount == 0 {
 715		return
 716	}
 717	options := user.Options
 718	options.MeCount = 0
 719	j, err := jsonify(options)
 720	if err == nil {
 721		db := opendatabase()
 722		_, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
 723	}
 724	if err != nil {
 725		elog.Printf("error noneing me: %s", err)
 726	}
 727	somenamedusers.Clear(user.Name)
 728	somenumberedusers.Clear(user.ID)
 729}
 730
 731func loadchatter(userid int64) []*Chatter {
 732	duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
 733	rows, err := stmtLoadChonks.Query(userid, duedt)
 734	if err != nil {
 735		elog.Printf("error loading chonks: %s", err)
 736		return nil
 737	}
 738	defer rows.Close()
 739	chonks := make(map[string][]*Chonk)
 740	var allchonks []*Chonk
 741	for rows.Next() {
 742		ch := new(Chonk)
 743		var dt string
 744		err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
 745		if err != nil {
 746			elog.Printf("error scanning chonk: %s", err)
 747			continue
 748		}
 749		ch.Date, _ = time.Parse(dbtimeformat, dt)
 750		chonks[ch.Target] = append(chonks[ch.Target], ch)
 751		allchonks = append(allchonks, ch)
 752	}
 753	donksforchonks(allchonks)
 754	rows.Close()
 755	rows, err = stmtGetChatters.Query(userid)
 756	if err != nil {
 757		elog.Printf("error getting chatters: %s", err)
 758		return nil
 759	}
 760	for rows.Next() {
 761		var target string
 762		err = rows.Scan(&target)
 763		if err != nil {
 764			elog.Printf("error scanning chatter: %s", target)
 765			continue
 766		}
 767		if _, ok := chonks[target]; !ok {
 768			chonks[target] = []*Chonk{}
 769
 770		}
 771	}
 772	var chatter []*Chatter
 773	for target, chonks := range chonks {
 774		chatter = append(chatter, &Chatter{
 775			Target: target,
 776			Chonks: chonks,
 777		})
 778	}
 779	sort.Slice(chatter, func(i, j int) bool {
 780		a, b := chatter[i], chatter[j]
 781		if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
 782			if len(a.Chonks) == len(b.Chonks) {
 783				return a.Target < b.Target
 784			}
 785			return len(a.Chonks) > len(b.Chonks)
 786		}
 787		return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
 788	})
 789
 790	return chatter
 791}
 792
 793func savehonk(h *Honk) error {
 794	dt := h.Date.UTC().Format(dbtimeformat)
 795	aud := strings.Join(h.Audience, " ")
 796
 797	db := opendatabase()
 798	tx, err := db.Begin()
 799	if err != nil {
 800		elog.Printf("can't begin tx: %s", err)
 801		return err
 802	}
 803
 804	res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
 805		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
 806		h.Oonker, h.Flags)
 807	if err == nil {
 808		h.ID, _ = res.LastInsertId()
 809		err = saveextras(tx, h)
 810	}
 811	if err == nil {
 812		if h.Whofore == 1 {
 813			meplusone(tx, h.UserID)
 814		}
 815		err = tx.Commit()
 816	} else {
 817		tx.Rollback()
 818	}
 819	if err != nil {
 820		elog.Printf("error saving honk: %s", err)
 821	}
 822	honkhonkline()
 823	return err
 824}
 825
 826func updatehonk(h *Honk) error {
 827	old := getxonk(h.UserID, h.XID)
 828	oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
 829	dt := h.Date.UTC().Format(dbtimeformat)
 830
 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, h.ID, false)
 839	if err == nil {
 840		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
 841	}
 842	if err == nil {
 843		err = saveextras(tx, h)
 844	}
 845	if err == nil {
 846		var j string
 847		j, err = jsonify(&oldrev)
 848		if err == nil {
 849			_, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
 850		}
 851		if err != nil {
 852			elog.Printf("error saving oldrev: %s", err)
 853		}
 854	}
 855	if err == nil {
 856		err = tx.Commit()
 857	} else {
 858		tx.Rollback()
 859	}
 860	if err != nil {
 861		elog.Printf("error updating honk %d: %s", h.ID, err)
 862	}
 863	return err
 864}
 865
 866func deletehonk(honkid int64) error {
 867	db := opendatabase()
 868	tx, err := db.Begin()
 869	if err != nil {
 870		elog.Printf("can't begin tx: %s", err)
 871		return err
 872	}
 873
 874	err = deleteextras(tx, honkid, true)
 875	if err == nil {
 876		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
 877	}
 878	if err == nil {
 879		err = tx.Commit()
 880	} else {
 881		tx.Rollback()
 882	}
 883	if err != nil {
 884		elog.Printf("error deleting honk %d: %s", honkid, err)
 885	}
 886	return err
 887}
 888
 889func saveextras(tx *sql.Tx, h *Honk) error {
 890	for _, d := range h.Donks {
 891		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
 892		if err != nil {
 893			elog.Printf("error saving donk: %s", err)
 894			return err
 895		}
 896	}
 897	for _, o := range h.Onts {
 898		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
 899		if err != nil {
 900			elog.Printf("error saving ont: %s", err)
 901			return err
 902		}
 903	}
 904	if p := h.Place; p != nil {
 905		j, err := jsonify(p)
 906		if err == nil {
 907			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
 908		}
 909		if err != nil {
 910			elog.Printf("error saving place: %s", err)
 911			return err
 912		}
 913	}
 914	if t := h.Time; t != nil {
 915		j, err := jsonify(t)
 916		if err == nil {
 917			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
 918		}
 919		if err != nil {
 920			elog.Printf("error saving time: %s", err)
 921			return err
 922		}
 923	}
 924	if m := h.Mentions; len(m) > 0 {
 925		j, err := jsonify(m)
 926		if err == nil {
 927			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
 928		}
 929		if err != nil {
 930			elog.Printf("error saving mentions: %s", err)
 931			return err
 932		}
 933	}
 934	return nil
 935}
 936
 937var baxonker sync.Mutex
 938
 939func addreaction(user *WhatAbout, xid string, who, react string) {
 940	baxonker.Lock()
 941	defer baxonker.Unlock()
 942	h := getxonk(user.ID, xid)
 943	if h == nil {
 944		return
 945	}
 946	h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
 947	j, _ := jsonify(h.Badonks)
 948	db := opendatabase()
 949	tx, _ := db.Begin()
 950	_, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
 951	_, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
 952	tx.Commit()
 953}
 954
 955func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
 956	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
 957	if err != nil {
 958		return err
 959	}
 960	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
 961	if err != nil {
 962		return err
 963	}
 964	if everything {
 965		_, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
 966	} else {
 967		_, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
 968	}
 969	if err != nil {
 970		return err
 971	}
 972	return nil
 973}
 974
 975func jsonify(what interface{}) (string, error) {
 976	var buf bytes.Buffer
 977	e := json.NewEncoder(&buf)
 978	e.SetEscapeHTML(false)
 979	e.SetIndent("", "")
 980	err := e.Encode(what)
 981	return buf.String(), err
 982}
 983
 984func unjsonify(s string, dest interface{}) error {
 985	d := json.NewDecoder(strings.NewReader(s))
 986	err := d.Decode(dest)
 987	return err
 988}
 989
 990func getxonker(what, flav string) string {
 991	var res string
 992	row := stmtGetXonker.QueryRow(what, flav)
 993	row.Scan(&res)
 994	return res
 995}
 996
 997func savexonker(what, value, flav, when string) {
 998	stmtSaveXonker.Exec(what, value, flav, when)
 999}
1000
1001func savehonker(user *WhatAbout, url, name, flavor, combos, mj string) (int64, error) {
1002	var owner string
1003	if url[0] == '#' {
1004		flavor = "peep"
1005		if name == "" {
1006			name = url[1:]
1007		}
1008		owner = url
1009	} else {
1010		info, err := investigate(url)
1011		if err != nil {
1012			ilog.Printf("failed to investigate honker: %s", err)
1013			return 0, err
1014		}
1015		url = info.XID
1016		if name == "" {
1017			name = info.Name
1018		}
1019		owner = info.Owner
1020	}
1021
1022	var x string
1023	db := opendatabase()
1024	row := db.QueryRow("select xid from honkers where xid = ? and userid = ? and flavor in ('sub', 'unsub', 'peep')", url, user.ID)
1025	err := row.Scan(&x)
1026	if err != sql.ErrNoRows {
1027		if err != nil {
1028			elog.Printf("honker scan err: %s", err)
1029		} else {
1030			err = fmt.Errorf("it seems you are already subscribed to them")
1031		}
1032		return 0, err
1033	}
1034
1035	res, err := stmtSaveHonker.Exec(user.ID, name, url, flavor, combos, owner, mj)
1036	if err != nil {
1037		elog.Print(err)
1038		return 0, err
1039	}
1040	honkerid, _ := res.LastInsertId()
1041	return honkerid, nil
1042}
1043
1044func cleanupdb(arg string) {
1045	db := opendatabase()
1046	days, err := strconv.Atoi(arg)
1047	var sqlargs []interface{}
1048	var where string
1049	if err != nil {
1050		honker := arg
1051		expdate := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
1052		where = "dt < ? and honker = ?"
1053		sqlargs = append(sqlargs, expdate)
1054		sqlargs = append(sqlargs, honker)
1055	} else {
1056		expdate := time.Now().Add(-time.Duration(days) * 24 * time.Hour).UTC().Format(dbtimeformat)
1057		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
1058		sqlargs = append(sqlargs, expdate)
1059	}
1060	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
1061	doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
1062	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
1063	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
1064
1065	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
1066	for _, u := range allusers() {
1067		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)
1068	}
1069
1070	filexids := make(map[string]bool)
1071	blobdb := openblobdb()
1072	rows, err := blobdb.Query("select xid from filedata")
1073	if err != nil {
1074		elog.Fatal(err)
1075	}
1076	for rows.Next() {
1077		var xid string
1078		err = rows.Scan(&xid)
1079		if err != nil {
1080			elog.Fatal(err)
1081		}
1082		filexids[xid] = true
1083	}
1084	rows.Close()
1085	rows, err = db.Query("select xid from filemeta")
1086	for rows.Next() {
1087		var xid string
1088		err = rows.Scan(&xid)
1089		if err != nil {
1090			elog.Fatal(err)
1091		}
1092		delete(filexids, xid)
1093	}
1094	rows.Close()
1095	tx, err := blobdb.Begin()
1096	if err != nil {
1097		elog.Fatal(err)
1098	}
1099	for xid, _ := range filexids {
1100		_, err = tx.Exec("delete from filedata where xid = ?", xid)
1101		if err != nil {
1102			elog.Fatal(err)
1103		}
1104	}
1105	err = tx.Commit()
1106	if err != nil {
1107		elog.Fatal(err)
1108	}
1109}
1110
1111var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1112var stmtDeleteHonker *sql.Stmt
1113var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1114var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1115var stmtHonksFromLongAgo *sql.Stmt
1116var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1117var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1118var stmtFindFile, stmtFindFileId, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1119var stmtCheckFileData *sql.Stmt
1120var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1121var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1122var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker, stmtDeleteOldXonkers *sql.Stmt
1123var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1124var stmtHonksForUserFirstClass *sql.Stmt
1125var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1126var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1127var stmtGetTracks *sql.Stmt
1128var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1129var stmtDeliquentCheck, stmtDeliquentUpdate *sql.Stmt
1130
1131func preparetodie(db *sql.DB, s string) *sql.Stmt {
1132	stmt, err := db.Prepare(s)
1133	if err != nil {
1134		elog.Fatalf("error %s: %s", err, s)
1135	}
1136	return stmt
1137}
1138
1139func prepareStatements(db *sql.DB) {
1140	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")
1141	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1142	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1143	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1144	stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1145	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1146	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1147	stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1148
1149	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 "
1150	limit := " order by honks.honkid desc limit 250"
1151	smalllimit := " order by honks.honkid desc limit ?"
1152	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1153	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1154	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
1155	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1156	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1157	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1158	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1159	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1160	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1161	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
1162	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
1163	stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
1164	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1165	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)
1166	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1167	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)
1168	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1169	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)
1170
1171	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1172	stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1173	stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1174	stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1175	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1176	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1177	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
1178	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1179	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1180	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1181	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1182	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1183	blobdb := openblobdb()
1184	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1185	stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1186	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1187	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1188	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1189	stmtFindFileId = preparetodie(db, "select xid, local, description from filemeta where fileid = ? and url = ? and local = 1")
1190	stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1191	stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1192	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1193	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1194	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1195	stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1196	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1197	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")
1198	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1199	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1200	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1201	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1202	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1203	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1204	stmtDeleteOldXonkers = preparetodie(db, "delete from xonkers where flavor = ? and dt < ?")
1205	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")
1206	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1207	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1208	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")
1209	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1210	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1211	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1212	stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1213	stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1214	stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1215	stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1216	stmtDeliquentCheck = preparetodie(db, "select dooverid, msg from doovers where userid = ? and rcpt = ?")
1217	stmtDeliquentUpdate = preparetodie(db, "update doovers set msg = ? where dooverid = ?")
1218}