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