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