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