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	"database/sql"
 21	"encoding/json"
 22	"fmt"
 23	"log"
 24	"sort"
 25	"strconv"
 26	"strings"
 27	"time"
 28
 29	"humungus.tedunangst.com/r/webs/cache"
 30	"humungus.tedunangst.com/r/webs/httpsig"
 31	"humungus.tedunangst.com/r/webs/login"
 32)
 33
 34var someusers = cache.New(cache.Options{Filler: func(name string) (*WhatAbout, bool) {
 35	row := stmtWhatAbout.QueryRow(name)
 36	user := new(WhatAbout)
 37	var options string
 38	err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &options)
 39	if err != nil {
 40		return nil, false
 41	}
 42	user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
 43	user.SkinnyCSS = strings.Contains(options, " skinny ")
 44	return user, true
 45}})
 46
 47var oldserveruser *WhatAbout
 48
 49func getserveruser() *WhatAbout {
 50	if oldserveruser == nil {
 51		db := opendatabase()
 52		row := db.QueryRow("select userid, username, displayname, about, pubkey, seckey from users where userid = ?", serverUID)
 53		user := new(WhatAbout)
 54		var seckey string
 55		err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &seckey)
 56		if err == nil {
 57			user.SecKey, _, err = httpsig.DecodeKey(seckey)
 58		}
 59		if err != nil {
 60			log.Panicf("trouble getting server user: %s", err)
 61		}
 62		user.URL = fmt.Sprintf("https://%s/server", serverName)
 63		oldserveruser = user
 64	}
 65	return oldserveruser
 66}
 67
 68func butwhatabout(name string) (*WhatAbout, error) {
 69	var user *WhatAbout
 70	ok := someusers.Get(name, &user)
 71	if !ok {
 72		return nil, fmt.Errorf("no user: %s", name)
 73	}
 74	return user, nil
 75}
 76
 77var honkerinvalidator cache.Invalidator
 78
 79func gethonkers(userid int64) []*Honker {
 80	rows, err := stmtHonkers.Query(userid)
 81	if err != nil {
 82		log.Printf("error querying honkers: %s", err)
 83		return nil
 84	}
 85	defer rows.Close()
 86	var honkers []*Honker
 87	for rows.Next() {
 88		h := new(Honker)
 89		var combos string
 90		err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor, &combos)
 91		h.Combos = strings.Split(strings.TrimSpace(combos), " ")
 92		if err != nil {
 93			log.Printf("error scanning honker: %s", err)
 94			return nil
 95		}
 96		honkers = append(honkers, h)
 97	}
 98	return honkers
 99}
100
101func getdubs(userid int64) []*Honker {
102	rows, err := stmtDubbers.Query(userid)
103	if err != nil {
104		log.Printf("error querying dubs: %s", err)
105		return nil
106	}
107	defer rows.Close()
108	var honkers []*Honker
109	for rows.Next() {
110		h := new(Honker)
111		err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor)
112		if err != nil {
113			log.Printf("error scanning honker: %s", err)
114			return nil
115		}
116		honkers = append(honkers, h)
117	}
118	return honkers
119}
120
121func allusers() []login.UserInfo {
122	var users []login.UserInfo
123	rows, _ := opendatabase().Query("select userid, username from users where userid > 0")
124	defer rows.Close()
125	for rows.Next() {
126		var u login.UserInfo
127		rows.Scan(&u.UserID, &u.Username)
128		users = append(users, u)
129	}
130	return users
131}
132
133func getxonk(userid int64, xid string) *Honk {
134	row := stmtOneXonk.QueryRow(userid, xid)
135	return scanhonk(row)
136}
137
138func getbonk(userid int64, xid string) *Honk {
139	row := stmtOneBonk.QueryRow(userid, xid)
140	return scanhonk(row)
141}
142
143func getpublichonks() []*Honk {
144	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
145	rows, err := stmtPublicHonks.Query(dt)
146	return getsomehonks(rows, err)
147}
148func geteventhonks(userid int64) []*Honk {
149	rows, err := stmtEventHonks.Query(userid)
150	honks := getsomehonks(rows, err)
151	sort.Slice(honks, func(i, j int) bool {
152		var t1, t2 time.Time
153		if honks[i].Time == nil {
154			t1 = honks[i].Date
155		} else {
156			t1 = honks[i].Time.StartTime
157		}
158		if honks[j].Time == nil {
159			t2 = honks[j].Date
160		} else {
161			t2 = honks[j].Time.StartTime
162		}
163		return t1.After(t2)
164	})
165	now := time.Now().Add(-24 * time.Hour)
166	for i, h := range honks {
167		if h.Time.StartTime.Before(now) {
168			honks = honks[:i]
169			break
170		}
171	}
172	reversehonks(honks)
173	return honks
174}
175func gethonksbyuser(name string, includeprivate bool, wanted int64) []*Honk {
176	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
177	whofore := 2
178	if includeprivate {
179		whofore = 3
180	}
181	rows, err := stmtUserHonks.Query(wanted, whofore, name, dt)
182	return getsomehonks(rows, err)
183}
184func gethonksforuser(userid int64, wanted int64) []*Honk {
185	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
186	rows, err := stmtHonksForUser.Query(wanted, userid, dt, userid, userid)
187	return getsomehonks(rows, err)
188}
189func gethonksforuserfirstclass(userid int64, wanted int64) []*Honk {
190	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
191	rows, err := stmtHonksForUserFirstClass.Query(wanted, userid, dt, userid, userid)
192	return getsomehonks(rows, err)
193}
194func gethonksforme(userid int64, wanted int64) []*Honk {
195	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
196	rows, err := stmtHonksForMe.Query(wanted, userid, dt, userid)
197	return getsomehonks(rows, err)
198}
199func getsavedhonks(userid int64, wanted int64) []*Honk {
200	rows, err := stmtHonksISaved.Query(wanted, userid)
201	return getsomehonks(rows, err)
202}
203func gethonksbyhonker(userid int64, honker string, wanted int64) []*Honk {
204	rows, err := stmtHonksByHonker.Query(wanted, userid, honker, userid)
205	return getsomehonks(rows, err)
206}
207func gethonksbyxonker(userid int64, xonker string, wanted int64) []*Honk {
208	rows, err := stmtHonksByXonker.Query(wanted, userid, xonker, xonker, userid)
209	return getsomehonks(rows, err)
210}
211func gethonksbycombo(userid int64, combo string, wanted int64) []*Honk {
212	combo = "% " + combo + " %"
213	rows, err := stmtHonksByCombo.Query(wanted, userid, combo, userid)
214	return getsomehonks(rows, err)
215}
216func gethonksbyconvoy(userid int64, convoy string, wanted int64) []*Honk {
217	rows, err := stmtHonksByConvoy.Query(wanted, userid, userid, convoy)
218	honks := getsomehonks(rows, err)
219	return honks
220}
221func gethonksbysearch(userid int64, q string, wanted int64) []*Honk {
222	honker := ""
223	withhonker := 0
224	site := ""
225	withsite := 0
226	terms := strings.Split(q, " ")
227	q = "%"
228	for _, t := range terms {
229		if strings.HasPrefix(t, "site:") {
230			site = t[5:]
231			site = "%" + site + "%"
232			withsite = 1
233			continue
234		}
235		if strings.HasPrefix(t, "honker:") {
236			honker = t[7:]
237			xid := fullname(honker, userid)
238			if xid != "" {
239				honker = xid
240			}
241			withhonker = 1
242			continue
243		}
244		if len(q) != 1 {
245			q += " "
246		}
247		q += t
248	}
249	q += "%"
250	rows, err := stmtHonksBySearch.Query(wanted, userid, withsite, site, withhonker, honker, honker, q, userid)
251	honks := getsomehonks(rows, err)
252	return honks
253}
254func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
255	rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
256	honks := getsomehonks(rows, err)
257	return honks
258}
259
260func reversehonks(honks []*Honk) {
261	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
262		honks[i], honks[j] = honks[j], honks[i]
263	}
264}
265
266func getsomehonks(rows *sql.Rows, err error) []*Honk {
267	if err != nil {
268		log.Printf("error querying honks: %s", err)
269		return nil
270	}
271	defer rows.Close()
272	var honks []*Honk
273	for rows.Next() {
274		h := scanhonk(rows)
275		if h != nil {
276			honks = append(honks, h)
277		}
278	}
279	rows.Close()
280	donksforhonks(honks)
281	return honks
282}
283
284type RowLike interface {
285	Scan(dest ...interface{}) error
286}
287
288func scanhonk(row RowLike) *Honk {
289	h := new(Honk)
290	var dt, aud string
291	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
292		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
293	if err != nil {
294		if err != sql.ErrNoRows {
295			log.Printf("error scanning honk: %s", err)
296		}
297		return nil
298	}
299	h.Date, _ = time.Parse(dbtimeformat, dt)
300	h.Audience = strings.Split(aud, " ")
301	h.Public = !keepitquiet(h.Audience)
302	return h
303}
304
305func donksforhonks(honks []*Honk) {
306	db := opendatabase()
307	var ids []string
308	hmap := make(map[int64]*Honk)
309	for _, h := range honks {
310		ids = append(ids, fmt.Sprintf("%d", h.ID))
311		hmap[h.ID] = h
312	}
313	// grab donks
314	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)", strings.Join(ids, ","))
315	rows, err := db.Query(q)
316	if err != nil {
317		log.Printf("error querying donks: %s", err)
318		return
319	}
320	defer rows.Close()
321	for rows.Next() {
322		var hid int64
323		d := new(Donk)
324		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
325		if err != nil {
326			log.Printf("error scanning donk: %s", err)
327			continue
328		}
329		h := hmap[hid]
330		h.Donks = append(h.Donks, d)
331	}
332	rows.Close()
333
334	// grab onts
335	q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", strings.Join(ids, ","))
336	rows, err = db.Query(q)
337	if err != nil {
338		log.Printf("error querying onts: %s", err)
339		return
340	}
341	defer rows.Close()
342	for rows.Next() {
343		var hid int64
344		var o string
345		err = rows.Scan(&hid, &o)
346		if err != nil {
347			log.Printf("error scanning donk: %s", err)
348			continue
349		}
350		h := hmap[hid]
351		h.Onts = append(h.Onts, o)
352	}
353	rows.Close()
354	// grab meta
355	q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", strings.Join(ids, ","))
356	rows, err = db.Query(q)
357	if err != nil {
358		log.Printf("error querying honkmeta: %s", err)
359		return
360	}
361	defer rows.Close()
362	for rows.Next() {
363		var hid int64
364		var genus, j string
365		err = rows.Scan(&hid, &genus, &j)
366		if err != nil {
367			log.Printf("error scanning honkmeta: %s", err)
368			continue
369		}
370		h := hmap[hid]
371		switch genus {
372		case "place":
373			p := new(Place)
374			err = unjsonify(j, p)
375			if err != nil {
376				log.Printf("error parsing place: %s", err)
377				continue
378			}
379			h.Place = p
380		case "time":
381			t := new(Time)
382			err = unjsonify(j, t)
383			if err != nil {
384				log.Printf("error parsing time: %s", err)
385				continue
386			}
387			h.Time = t
388		case "oldrev":
389		default:
390			log.Printf("unknown meta genus: %s", genus)
391		}
392	}
393	rows.Close()
394}
395
396func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
397	res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
398	if err != nil {
399		return 0, err
400	}
401	fileid, _ := res.LastInsertId()
402	if local {
403		_, err = stmtSaveFileData.Exec(xid, media, data)
404		if err != nil {
405			return 0, err
406		}
407	}
408	return fileid, nil
409}
410
411func finddonk(url string) *Donk {
412	donk := new(Donk)
413	row := stmtFindFile.QueryRow(url)
414	err := row.Scan(&donk.FileID, &donk.XID)
415	if err == nil {
416		return donk
417	}
418	if err != sql.ErrNoRows {
419		log.Printf("error finding file: %s", err)
420	}
421	return nil
422}
423
424func savehonk(h *Honk) error {
425	dt := h.Date.UTC().Format(dbtimeformat)
426	aud := strings.Join(h.Audience, " ")
427
428	db := opendatabase()
429	tx, err := db.Begin()
430	if err != nil {
431		log.Printf("can't begin tx: %s", err)
432		return err
433	}
434
435	res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
436		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
437		h.Oonker, h.Flags)
438	if err == nil {
439		h.ID, _ = res.LastInsertId()
440		err = saveextras(tx, h)
441	}
442	if err == nil {
443		err = tx.Commit()
444	} else {
445		tx.Rollback()
446	}
447	if err != nil {
448		log.Printf("error saving honk: %s", err)
449	}
450	return err
451}
452
453func updatehonk(h *Honk) error {
454	old := getxonk(h.UserID, h.XID)
455	oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
456	dt := h.Date.UTC().Format(dbtimeformat)
457
458	db := opendatabase()
459	tx, err := db.Begin()
460	if err != nil {
461		log.Printf("can't begin tx: %s", err)
462		return err
463	}
464
465	err = deleteextras(tx, h.ID)
466	if err == nil {
467		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
468	}
469	if err == nil {
470		err = saveextras(tx, h)
471	}
472	if err == nil {
473		var j string
474		j, err = jsonify(&oldrev)
475		if err == nil {
476			_, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
477		}
478		if err != nil {
479			log.Printf("error saving oldrev: %s", err)
480		}
481	}
482	if err == nil {
483		err = tx.Commit()
484	} else {
485		tx.Rollback()
486	}
487	if err != nil {
488		log.Printf("error updating honk %d: %s", h.ID, err)
489	}
490	return err
491}
492
493func deletehonk(honkid int64) error {
494	db := opendatabase()
495	tx, err := db.Begin()
496	if err != nil {
497		log.Printf("can't begin tx: %s", err)
498		return err
499	}
500
501	err = deleteextras(tx, honkid)
502	if err == nil {
503		_, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "nonsense")
504	}
505	if err == nil {
506		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
507	}
508	if err == nil {
509		err = tx.Commit()
510	} else {
511		tx.Rollback()
512	}
513	if err != nil {
514		log.Printf("error deleting honk %d: %s", honkid, err)
515	}
516	return err
517}
518
519func saveextras(tx *sql.Tx, h *Honk) error {
520	for _, d := range h.Donks {
521		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, d.FileID)
522		if err != nil {
523			log.Printf("error saving donk: %s", err)
524			return err
525		}
526	}
527	for _, o := range h.Onts {
528		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
529		if err != nil {
530			log.Printf("error saving ont: %s", err)
531			return err
532		}
533	}
534	if p := h.Place; p != nil {
535		j, err := jsonify(p)
536		if err == nil {
537			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
538		}
539		if err != nil {
540			log.Printf("error saving place: %s", err)
541			return err
542		}
543	}
544	if t := h.Time; t != nil {
545		j, err := jsonify(t)
546		if err == nil {
547			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
548		}
549		if err != nil {
550			log.Printf("error saving time: %s", err)
551			return err
552		}
553	}
554	return nil
555}
556
557func deleteextras(tx *sql.Tx, honkid int64) error {
558	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
559	if err != nil {
560		return err
561	}
562	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
563	if err != nil {
564		return err
565	}
566	_, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "oldrev")
567	if err != nil {
568		return err
569	}
570	return nil
571}
572
573func jsonify(what interface{}) (string, error) {
574	var buf bytes.Buffer
575	e := json.NewEncoder(&buf)
576	e.SetEscapeHTML(false)
577	e.SetIndent("", "")
578	err := e.Encode(what)
579	return buf.String(), err
580}
581
582func unjsonify(s string, dest interface{}) error {
583	d := json.NewDecoder(strings.NewReader(s))
584	err := d.Decode(dest)
585	return err
586}
587
588func cleanupdb(arg string) {
589	db := opendatabase()
590	days, err := strconv.Atoi(arg)
591	var sqlargs []interface{}
592	var where string
593	if err != nil {
594		honker := arg
595		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
596		where = "dt < ? and honker = ?"
597		sqlargs = append(sqlargs, expdate)
598		sqlargs = append(sqlargs, honker)
599	} else {
600		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
601		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
602		sqlargs = append(sqlargs, expdate)
603	}
604	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
605	doordie(db, "delete from donks where honkid not in (select honkid from honks)")
606	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
607	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
608
609	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
610	for _, u := range allusers() {
611		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)
612	}
613
614	filexids := make(map[string]bool)
615	blobdb := openblobdb()
616	rows, err := blobdb.Query("select xid from filedata")
617	if err != nil {
618		log.Fatal(err)
619	}
620	for rows.Next() {
621		var xid string
622		err = rows.Scan(&xid)
623		if err != nil {
624			log.Fatal(err)
625		}
626		filexids[xid] = true
627	}
628	rows.Close()
629	rows, err = db.Query("select xid from filemeta")
630	for rows.Next() {
631		var xid string
632		err = rows.Scan(&xid)
633		if err != nil {
634			log.Fatal(err)
635		}
636		delete(filexids, xid)
637	}
638	rows.Close()
639	tx, err := blobdb.Begin()
640	if err != nil {
641		log.Fatal(err)
642	}
643	for xid, _ := range filexids {
644		_, err = tx.Exec("delete from filedata where xid = ?", xid)
645		if err != nil {
646			log.Fatal(err)
647		}
648	}
649	err = tx.Commit()
650	if err != nil {
651		log.Fatal(err)
652	}
653}
654
655var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
656var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
657var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
658var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtWhatAbout *sql.Stmt
659var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
660var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
661var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
662var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
663var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
664var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
665var stmtHonksForUserFirstClass, stmtSaveMeta, stmtDeleteMeta, stmtUpdateHonk *sql.Stmt
666var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
667
668func preparetodie(db *sql.DB, s string) *sql.Stmt {
669	stmt, err := db.Prepare(s)
670	if err != nil {
671		log.Fatalf("error %s: %s", err, s)
672	}
673	return stmt
674}
675
676func prepareStatements(db *sql.DB) {
677	stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos from honkers where userid = ? and (flavor = 'presub' or flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
678	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
679	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
680	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ? where honkerid = ? and userid = ?")
681	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
682	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
683
684	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 "
685	limit := " order by honks.honkid desc limit 250"
686	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
687	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
688	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
689	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
690	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
691	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+limit)
692	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
693	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
694	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
695	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
696	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
697	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
698	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)
699	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
700	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.honkid > ? and honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
701	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and (? = 0 or xid like ?) and (? = 0 or honks.honker = ? or honks.oonker = ?) and noise like ?"+butnotthose+limit)
702	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
703	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)
704
705	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
706	stmtDeleteMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus <> ?")
707	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
708	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
709	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
710	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
711	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
712	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
713	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
714	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
715	blobdb := openblobdb()
716	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
717	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
718	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
719	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
720	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ? and userid > 0")
721	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
722	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
723	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
724	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
725	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
726	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers")
727	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
728	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
729	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
730	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
731	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
732	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
733	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")
734	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
735	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
736	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")
737	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
738	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
739	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
740}