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