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