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