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