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		chatplusone(tx, ch.UserID)
 599		err = tx.Commit()
 600	} else {
 601		tx.Rollback()
 602	}
 603	return err
 604}
 605
 606func chatplusone(tx *sql.Tx, userid int64) {
 607	var user *WhatAbout
 608	ok := somenumberedusers.Get(userid, &user)
 609	if !ok {
 610		return
 611	}
 612	options := user.Options
 613	options.ChatCount += 1
 614	j, err := jsonify(options)
 615	if err == nil {
 616		_, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
 617	}
 618	if err != nil {
 619		log.Printf("error plussing chat: %s", err)
 620	}
 621	somenamedusers.Clear(user.Name)
 622	somenumberedusers.Clear(user.ID)
 623}
 624
 625func chatnewnone(userid int64) {
 626	var user *WhatAbout
 627	ok := somenumberedusers.Get(userid, &user)
 628	if !ok || user.Options.ChatCount == 0 {
 629		return
 630	}
 631	options := user.Options
 632	options.ChatCount = 0
 633	j, err := jsonify(options)
 634	if err == nil {
 635		db := opendatabase()
 636		_, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
 637	}
 638	if err != nil {
 639		log.Printf("error noneing chat: %s", err)
 640	}
 641	somenamedusers.Clear(user.Name)
 642	somenumberedusers.Clear(user.ID)
 643}
 644
 645func meplusone(tx *sql.Tx, userid int64) {
 646	var user *WhatAbout
 647	ok := somenumberedusers.Get(userid, &user)
 648	if !ok {
 649		return
 650	}
 651	options := user.Options
 652	options.MeCount += 1
 653	j, err := jsonify(options)
 654	if err == nil {
 655		_, err = tx.Exec("update users set options = ? where username = ?", j, user.Name)
 656	}
 657	if err != nil {
 658		log.Printf("error plussing me: %s", err)
 659	}
 660	somenamedusers.Clear(user.Name)
 661	somenumberedusers.Clear(user.ID)
 662}
 663
 664func menewnone(userid int64) {
 665	var user *WhatAbout
 666	ok := somenumberedusers.Get(userid, &user)
 667	if !ok || user.Options.MeCount == 0 {
 668		return
 669	}
 670	options := user.Options
 671	options.MeCount = 0
 672	j, err := jsonify(options)
 673	if err == nil {
 674		db := opendatabase()
 675		_, err = db.Exec("update users set options = ? where username = ?", j, user.Name)
 676	}
 677	if err != nil {
 678		log.Printf("error noneing me: %s", err)
 679	}
 680	somenamedusers.Clear(user.Name)
 681	somenumberedusers.Clear(user.ID)
 682}
 683
 684func loadchatter(userid int64) []*Chatter {
 685	duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
 686	rows, err := stmtLoadChonks.Query(userid, duedt)
 687	if err != nil {
 688		log.Printf("error loading chonks: %s", err)
 689		return nil
 690	}
 691	defer rows.Close()
 692	chonks := make(map[string][]*Chonk)
 693	var allchonks []*Chonk
 694	for rows.Next() {
 695		ch := new(Chonk)
 696		var dt string
 697		err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
 698		if err != nil {
 699			log.Printf("error scanning chonk: %s", err)
 700			continue
 701		}
 702		ch.Date, _ = time.Parse(dbtimeformat, dt)
 703		chonks[ch.Target] = append(chonks[ch.Target], ch)
 704		allchonks = append(allchonks, ch)
 705	}
 706	donksforchonks(allchonks)
 707	rows.Close()
 708	rows, err = stmtGetChatters.Query(userid)
 709	if err != nil {
 710		log.Printf("error getting chatters: %s", err)
 711		return nil
 712	}
 713	for rows.Next() {
 714		var target string
 715		err = rows.Scan(&target)
 716		if err != nil {
 717			log.Printf("error scanning chatter: %s", target)
 718			continue
 719		}
 720		if _, ok := chonks[target]; !ok {
 721			chonks[target] = []*Chonk{}
 722
 723		}
 724	}
 725	var chatter []*Chatter
 726	for target, chonks := range chonks {
 727		chatter = append(chatter, &Chatter{
 728			Target: target,
 729			Chonks: chonks,
 730		})
 731	}
 732	sort.Slice(chatter, func(i, j int) bool {
 733		a, b := chatter[i], chatter[j]
 734		if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
 735			if len(a.Chonks) == len(b.Chonks) {
 736				return a.Target < b.Target
 737			}
 738			return len(a.Chonks) > len(b.Chonks)
 739		}
 740		return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
 741	})
 742
 743	return chatter
 744}
 745
 746func savehonk(h *Honk) error {
 747	dt := h.Date.UTC().Format(dbtimeformat)
 748	aud := strings.Join(h.Audience, " ")
 749
 750	db := opendatabase()
 751	tx, err := db.Begin()
 752	if err != nil {
 753		log.Printf("can't begin tx: %s", err)
 754		return err
 755	}
 756
 757	res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
 758		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
 759		h.Oonker, h.Flags)
 760	if err == nil {
 761		h.ID, _ = res.LastInsertId()
 762		err = saveextras(tx, h)
 763	}
 764	if err == nil {
 765		if h.Whofore == 1 {
 766			meplusone(tx, h.UserID)
 767		}
 768		err = tx.Commit()
 769	} else {
 770		tx.Rollback()
 771	}
 772	if err != nil {
 773		log.Printf("error saving honk: %s", err)
 774	}
 775	honkhonkline()
 776	return err
 777}
 778
 779func updatehonk(h *Honk) error {
 780	old := getxonk(h.UserID, h.XID)
 781	oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
 782	dt := h.Date.UTC().Format(dbtimeformat)
 783
 784	db := opendatabase()
 785	tx, err := db.Begin()
 786	if err != nil {
 787		log.Printf("can't begin tx: %s", err)
 788		return err
 789	}
 790
 791	err = deleteextras(tx, h.ID, false)
 792	if err == nil {
 793		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
 794	}
 795	if err == nil {
 796		err = saveextras(tx, h)
 797	}
 798	if err == nil {
 799		var j string
 800		j, err = jsonify(&oldrev)
 801		if err == nil {
 802			_, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
 803		}
 804		if err != nil {
 805			log.Printf("error saving oldrev: %s", err)
 806		}
 807	}
 808	if err == nil {
 809		err = tx.Commit()
 810	} else {
 811		tx.Rollback()
 812	}
 813	if err != nil {
 814		log.Printf("error updating honk %d: %s", h.ID, err)
 815	}
 816	return err
 817}
 818
 819func deletehonk(honkid int64) error {
 820	db := opendatabase()
 821	tx, err := db.Begin()
 822	if err != nil {
 823		log.Printf("can't begin tx: %s", err)
 824		return err
 825	}
 826
 827	err = deleteextras(tx, honkid, true)
 828	if err == nil {
 829		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
 830	}
 831	if err == nil {
 832		err = tx.Commit()
 833	} else {
 834		tx.Rollback()
 835	}
 836	if err != nil {
 837		log.Printf("error deleting honk %d: %s", honkid, err)
 838	}
 839	return err
 840}
 841
 842func saveextras(tx *sql.Tx, h *Honk) error {
 843	for _, d := range h.Donks {
 844		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
 845		if err != nil {
 846			log.Printf("error saving donk: %s", err)
 847			return err
 848		}
 849	}
 850	for _, o := range h.Onts {
 851		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
 852		if err != nil {
 853			log.Printf("error saving ont: %s", err)
 854			return err
 855		}
 856	}
 857	if p := h.Place; p != nil {
 858		j, err := jsonify(p)
 859		if err == nil {
 860			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
 861		}
 862		if err != nil {
 863			log.Printf("error saving place: %s", err)
 864			return err
 865		}
 866	}
 867	if t := h.Time; t != nil {
 868		j, err := jsonify(t)
 869		if err == nil {
 870			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
 871		}
 872		if err != nil {
 873			log.Printf("error saving time: %s", err)
 874			return err
 875		}
 876	}
 877	if m := h.Mentions; len(m) > 0 {
 878		j, err := jsonify(m)
 879		if err == nil {
 880			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
 881		}
 882		if err != nil {
 883			log.Printf("error saving mentions: %s", err)
 884			return err
 885		}
 886	}
 887	return nil
 888}
 889
 890var baxonker sync.Mutex
 891
 892func addreaction(user *WhatAbout, xid string, who, react string) {
 893	baxonker.Lock()
 894	defer baxonker.Unlock()
 895	h := getxonk(user.ID, xid)
 896	if h == nil {
 897		return
 898	}
 899	h.Badonks = append(h.Badonks, Badonk{Who: who, What: react})
 900	j, _ := jsonify(h.Badonks)
 901	db := opendatabase()
 902	tx, _ := db.Begin()
 903	_, _ = tx.Stmt(stmtDeleteOneMeta).Exec(h.ID, "badonks")
 904	_, _ = tx.Stmt(stmtSaveMeta).Exec(h.ID, "badonks", j)
 905	tx.Commit()
 906}
 907
 908func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
 909	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
 910	if err != nil {
 911		return err
 912	}
 913	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
 914	if err != nil {
 915		return err
 916	}
 917	if everything {
 918		_, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
 919	} else {
 920		_, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
 921	}
 922	if err != nil {
 923		return err
 924	}
 925	return nil
 926}
 927
 928func jsonify(what interface{}) (string, error) {
 929	var buf bytes.Buffer
 930	e := json.NewEncoder(&buf)
 931	e.SetEscapeHTML(false)
 932	e.SetIndent("", "")
 933	err := e.Encode(what)
 934	return buf.String(), err
 935}
 936
 937func unjsonify(s string, dest interface{}) error {
 938	d := json.NewDecoder(strings.NewReader(s))
 939	err := d.Decode(dest)
 940	return err
 941}
 942
 943func cleanupdb(arg string) {
 944	db := opendatabase()
 945	days, err := strconv.Atoi(arg)
 946	var sqlargs []interface{}
 947	var where string
 948	if err != nil {
 949		honker := arg
 950		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
 951		where = "dt < ? and honker = ?"
 952		sqlargs = append(sqlargs, expdate)
 953		sqlargs = append(sqlargs, honker)
 954	} else {
 955		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
 956		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
 957		sqlargs = append(sqlargs, expdate)
 958	}
 959	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
 960	doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
 961	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
 962	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
 963
 964	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
 965	for _, u := range allusers() {
 966		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)
 967	}
 968
 969	filexids := make(map[string]bool)
 970	blobdb := openblobdb()
 971	rows, err := blobdb.Query("select xid from filedata")
 972	if err != nil {
 973		log.Fatal(err)
 974	}
 975	for rows.Next() {
 976		var xid string
 977		err = rows.Scan(&xid)
 978		if err != nil {
 979			log.Fatal(err)
 980		}
 981		filexids[xid] = true
 982	}
 983	rows.Close()
 984	rows, err = db.Query("select xid from filemeta")
 985	for rows.Next() {
 986		var xid string
 987		err = rows.Scan(&xid)
 988		if err != nil {
 989			log.Fatal(err)
 990		}
 991		delete(filexids, xid)
 992	}
 993	rows.Close()
 994	tx, err := blobdb.Begin()
 995	if err != nil {
 996		log.Fatal(err)
 997	}
 998	for xid, _ := range filexids {
 999		_, err = tx.Exec("delete from filedata where xid = ?", xid)
1000		if err != nil {
1001			log.Fatal(err)
1002		}
1003	}
1004	err = tx.Commit()
1005	if err != nil {
1006		log.Fatal(err)
1007	}
1008}
1009
1010var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
1011var stmtDeleteHonker *sql.Stmt
1012var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
1013var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
1014var stmtHonksFromLongAgo *sql.Stmt
1015var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
1016var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
1017var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
1018var stmtCheckFileData *sql.Stmt
1019var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
1020var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
1021var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
1022var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
1023var stmtHonksForUserFirstClass *sql.Stmt
1024var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteOneMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
1025var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
1026var stmtGetTracks *sql.Stmt
1027var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
1028
1029func preparetodie(db *sql.DB, s string) *sql.Stmt {
1030	stmt, err := db.Prepare(s)
1031	if err != nil {
1032		log.Fatalf("error %s: %s", err, s)
1033	}
1034	return stmt
1035}
1036
1037func prepareStatements(db *sql.DB) {
1038	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")
1039	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
1040	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ?, folxid = ? where userid = ? and name = ? and xid = ? and flavor = ?")
1041	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
1042	stmtDeleteHonker = preparetodie(db, "delete from honkers where honkerid = ?")
1043	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
1044	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
1045	stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
1046
1047	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 "
1048	limit := " order by honks.honkid desc limit 250"
1049	smalllimit := " order by honks.honkid desc limit ?"
1050	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
1051	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
1052	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
1053	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
1054	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
1055	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
1056	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
1057	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
1058	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
1059	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
1060	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
1061	stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
1062	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
1063	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)
1064	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
1065	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)
1066	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
1067	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)
1068
1069	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
1070	stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
1071	stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
1072	stmtDeleteOneMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus = ?")
1073	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
1074	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
1075	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
1076	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
1077	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
1078	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
1079	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
1080	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
1081	blobdb := openblobdb()
1082	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, hash, content) values (?, ?, ?, ?)")
1083	stmtCheckFileData = preparetodie(blobdb, "select xid from filedata where hash = ?")
1084	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
1085	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
1086	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
1087	stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
1088	stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
1089	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', ?)")
1090	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
1091	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
1092	stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
1093	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
1094	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")
1095	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
1096	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
1097	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
1098	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
1099	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
1100	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
1101	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")
1102	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
1103	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
1104	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")
1105	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
1106	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
1107	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
1108	stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
1109	stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
1110	stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
1111	stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
1112}