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