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