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