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