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