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