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