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