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