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, negate+"(noise like ? or precis like ?)")
 346		params = append(params, t)
 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 savehonk(h *Honk) error {
 793	dt := h.Date.UTC().Format(dbtimeformat)
 794	aud := strings.Join(h.Audience, " ")
 795
 796	db := opendatabase()
 797	tx, err := db.Begin()
 798	if err != nil {
 799		elog.Printf("can't begin tx: %s", err)
 800		return err
 801	}
 802
 803	res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
 804		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
 805		h.Oonker, h.Flags)
 806	if err == nil {
 807		h.ID, _ = res.LastInsertId()
 808		err = saveextras(tx, h)
 809	}
 810	if err == nil {
 811		if h.Whofore == 1 {
 812			meplusone(tx, h.UserID)
 813		}
 814		err = tx.Commit()
 815	} else {
 816		tx.Rollback()
 817	}
 818	if err != nil {
 819		elog.Printf("error saving honk: %s", err)
 820	}
 821	honkhonkline()
 822	return err
 823}
 824
 825func updatehonk(h *Honk) error {
 826	old := getxonk(h.UserID, h.XID)
 827	oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
 828	dt := h.Date.UTC().Format(dbtimeformat)
 829
 830	db := opendatabase()
 831	tx, err := db.Begin()
 832	if err != nil {
 833		elog.Printf("can't begin tx: %s", err)
 834		return err
 835	}
 836
 837	err = deleteextras(tx, h.ID, false)
 838	if err == nil {
 839		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
 840	}
 841	if err == nil {
 842		err = saveextras(tx, h)
 843	}
 844	if err == nil {
 845		var j string
 846		j, err = jsonify(&oldrev)
 847		if err == nil {
 848			_, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
 849		}
 850		if err != nil {
 851			elog.Printf("error saving oldrev: %s", err)
 852		}
 853	}
 854	if err == nil {
 855		err = tx.Commit()
 856	} else {
 857		tx.Rollback()
 858	}
 859	if err != nil {
 860		elog.Printf("error updating honk %d: %s", h.ID, err)
 861	}
 862	return err
 863}
 864
 865func deletehonk(honkid int64) error {
 866	db := opendatabase()
 867	tx, err := db.Begin()
 868	if err != nil {
 869		elog.Printf("can't begin tx: %s", err)
 870		return err
 871	}
 872
 873	err = deleteextras(tx, honkid, true)
 874	if err == nil {
 875		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
 876	}
 877	if err == nil {
 878		err = tx.Commit()
 879	} else {
 880		tx.Rollback()
 881	}
 882	if err != nil {
 883		elog.Printf("error deleting honk %d: %s", honkid, err)
 884	}
 885	return err
 886}
 887
 888func saveextras(tx *sql.Tx, h *Honk) error {
 889	for _, d := range h.Donks {
 890		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
 891		if err != nil {
 892			elog.Printf("error saving donk: %s", err)
 893			return err
 894		}
 895	}
 896	for _, o := range h.Onts {
 897		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
 898		if err != nil {
 899			elog.Printf("error saving ont: %s", err)
 900			return err
 901		}
 902	}
 903	if p := h.Place; p != nil {
 904		j, err := jsonify(p)
 905		if err == nil {
 906			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
 907		}
 908		if err != nil {
 909			elog.Printf("error saving place: %s", err)
 910			return err
 911		}
 912	}
 913	if t := h.Time; t != nil {
 914		j, err := jsonify(t)
 915		if err == nil {
 916			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
 917		}
 918		if err != nil {
 919			elog.Printf("error saving time: %s", err)
 920			return err
 921		}
 922	}
 923	if m := h.Mentions; len(m) > 0 {
 924		j, err := jsonify(m)
 925		if err == nil {
 926			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
 927		}
 928		if err != nil {
 929			elog.Printf("error saving mentions: %s", err)
 930			return err
 931		}
 932	}
 933	return nil
 934}
 935
 936var baxonker sync.Mutex
 937
 938func addreaction(user *WhatAbout, xid string, who, react string) {
 939	baxonker.Lock()
 940	defer baxonker.Unlock()
 941	h := getxonk(user.ID, xid)
 942	if h == nil {
 943		return
 944	}
 945	h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
 946	j, _ := jsonify(h.Badonks)
 947	db := opendatabase()
 948	tx, _ := db.Begin()
 949	_, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
 950	_, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
 951	tx.Commit()
 952}
 953
 954func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
 955	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
 956	if err != nil {
 957		return err
 958	}
 959	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
 960	if err != nil {
 961		return err
 962	}
 963	if everything {
 964		_, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
 965	} else {
 966		_, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
 967	}
 968	if err != nil {
 969		return err
 970	}
 971	return nil
 972}
 973
 974func jsonify(what interface{}) (string, error) {
 975	var buf bytes.Buffer
 976	e := json.NewEncoder(&buf)
 977	e.SetEscapeHTML(false)
 978	e.SetIndent("", "")
 979	err := e.Encode(what)
 980	return buf.String(), err
 981}
 982
 983func unjsonify(s string, dest interface{}) error {
 984	d := json.NewDecoder(strings.NewReader(s))
 985	err := d.Decode(dest)
 986	return err
 987}
 988
 989func getxonker(what, flav string) string {
 990	var res string
 991	row := stmtGetXonker.QueryRow(what, flav)
 992	row.Scan(&res)
 993	return res
 994}
 995
 996func savexonker(what, value, flav, when string) {
 997	stmtSaveXonker.Exec(what, value, flav, when)
 998}
 999
1000func savehonker(user *WhatAbout, url, name, flavor, combos, mj string) (int64, error) {
1001	var owner string
1002	if url[0] == '#' {
1003		flavor = "peep"
1004		if name == "" {
1005			name = url[1:]
1006		}
1007		owner = url
1008	} else {
1009		info, err := investigate(url)
1010		if err != nil {
1011			ilog.Printf("failed to investigate honker: %s", err)
1012			return 0, err
1013		}
1014		url = info.XID
1015		if name == "" {
1016			name = info.Name
1017		}
1018		owner = info.Owner
1019	}
1020
1021	var x string
1022	db := opendatabase()
1023	row := db.QueryRow("select xid from honkers where xid = ? and userid = ? and flavor in ('sub', 'unsub', 'peep')", url, user.ID)
1024	err := row.Scan(&x)
1025	if err != sql.ErrNoRows {
1026		if err != nil {
1027			elog.Printf("honker scan err: %s", err)
1028		} else {
1029			err = fmt.Errorf("it seems you are already subscribed to them")
1030		}
1031		return 0, err
1032	}
1033
1034	res, err := stmtSaveHonker.Exec(user.ID, name, url, flavor, combos, owner, mj)
1035	if err != nil {
1036		elog.Print(err)
1037		return 0, err
1038	}
1039	honkerid, _ := res.LastInsertId()
1040	return honkerid, nil
1041}
1042
1043func cleanupdb(arg string) {
1044	db := opendatabase()
1045	days, err := strconv.Atoi(arg)
1046	var sqlargs []interface{}
1047	var where string
1048	if err != nil {
1049		honker := arg
1050		expdate := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
1051		where = "dt < ? and honker = ?"
1052		sqlargs = append(sqlargs, expdate)
1053		sqlargs = append(sqlargs, honker)
1054	} else {
1055		expdate := time.Now().Add(-time.Duration(days) * 24 * time.Hour).UTC().Format(dbtimeformat)
1056		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
1057		sqlargs = append(sqlargs, expdate)
1058	}
1059	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
1060	doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
1061	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
1062	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
1063
1064	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
1065	for _, u := range allusers() {
1066		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)
1067	}
1068
1069	filexids := make(map[string]bool)
1070	blobdb := openblobdb()
1071	rows, err := blobdb.Query("select xid from filedata")
1072	if err != nil {
1073		elog.Fatal(err)
1074	}
1075	for rows.Next() {
1076		var xid string
1077		err = rows.Scan(&xid)
1078		if err != nil {
1079			elog.Fatal(err)
1080		}
1081		filexids[xid] = true
1082	}
1083	rows.Close()
1084	rows, err = db.Query("select xid from filemeta")
1085	for rows.Next() {
1086		var xid string
1087		err = rows.Scan(&xid)
1088		if err != nil {
1089			elog.Fatal(err)
1090		}
1091		delete(filexids, xid)
1092	}
1093	rows.Close()
1094	tx, err := blobdb.Begin()
1095	if err != nil {
1096		elog.Fatal(err)
1097	}
1098	for xid, _ := range filexids {
1099		_, err = tx.Exec("delete from filedata where xid = ?", xid)
1100		if err != nil {
1101			elog.Fatal(err)
1102		}
1103	}
1104	err = tx.Commit()
1105	if err != nil {
1106		elog.Fatal(err)
1107	}
1108}
1109
1110var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1111var stmtDeleteHonker *sql.Stmt
1112var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1113var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1114var stmtHonksFromLongAgo *sql.Stmt
1115var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1116var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1117var stmtFindFile, stmtFindFileId, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1118var stmtCheckFileData *sql.Stmt
1119var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1120var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1121var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker, stmtDeleteOldXonkers *sql.Stmt
1122var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1123var stmtHonksForUserFirstClass *sql.Stmt
1124var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1125var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1126var stmtGetTracks *sql.Stmt
1127var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1128var stmtDeliquentCheck, stmtDeliquentUpdate *sql.Stmt
1129
1130func preparetodie(db *sql.DB, s string) *sql.Stmt {
1131	stmt, err := db.Prepare(s)
1132	if err != nil {
1133		elog.Fatalf("error %s: %s", err, s)
1134	}
1135	return stmt
1136}
1137
1138func prepareStatements(db *sql.DB) {
1139	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")
1140	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1141	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1142	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1143	stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1144	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1145	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1146	stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1147
1148	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 "
1149	limit := " order by honks.honkid desc limit 250"
1150	smalllimit := " order by honks.honkid desc limit ?"
1151	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1152	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1153	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? and what <> 'bonk' order by honks.honkid asc")
1154	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1155	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1156	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1157	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1158	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1159	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1160	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (rid = '' or what = 'bonk')"+myhonkers+butnotthose+limit)
1161	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
1162	stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
1163	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1164	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)
1165	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1166	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)
1167	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1168	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)
1169
1170	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1171	stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1172	stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1173	stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1174	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1175	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1176	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
1177	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1178	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1179	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1180	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1181	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1182	blobdb := openblobdb()
1183	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1184	stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1185	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1186	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1187	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1188	stmtFindFileId = preparetodie(db, "select xid, local, description from filemeta where fileid = ? and url = ? and local = 1")
1189	stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1190	stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1191	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1192	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1193	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1194	stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1195	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1196	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")
1197	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1198	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1199	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1200	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1201	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1202	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1203	stmtDeleteOldXonkers = preparetodie(db, "delete from xonkers where flavor = ? and dt < ?")
1204	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")
1205	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1206	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1207	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")
1208	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1209	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1210	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1211	stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1212	stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1213	stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1214	stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1215	stmtDeliquentCheck = preparetodie(db, "select dooverid, msg from doovers where userid = ? and rcpt = ?")
1216	stmtDeliquentUpdate = preparetodie(db, "update doovers set msg = ? where dooverid = ?")
1217}