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