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