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