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