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)
495	if err == nil {
496		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, 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)
531	if err == nil {
532		_, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "nonsense")
533	}
534	if err == nil {
535		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
536	}
537	if err == nil {
538		err = tx.Commit()
539	} else {
540		tx.Rollback()
541	}
542	if err != nil {
543		log.Printf("error deleting honk %d: %s", honkid, err)
544	}
545	return err
546}
547
548func saveextras(tx *sql.Tx, h *Honk) error {
549	for _, d := range h.Donks {
550		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, d.FileID)
551		if err != nil {
552			log.Printf("error saving donk: %s", err)
553			return err
554		}
555	}
556	for _, o := range h.Onts {
557		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
558		if err != nil {
559			log.Printf("error saving ont: %s", err)
560			return err
561		}
562	}
563	if p := h.Place; p != nil {
564		j, err := jsonify(p)
565		if err == nil {
566			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
567		}
568		if err != nil {
569			log.Printf("error saving place: %s", err)
570			return err
571		}
572	}
573	if t := h.Time; t != nil {
574		j, err := jsonify(t)
575		if err == nil {
576			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
577		}
578		if err != nil {
579			log.Printf("error saving time: %s", err)
580			return err
581		}
582	}
583	return nil
584}
585
586func deleteextras(tx *sql.Tx, honkid int64) error {
587	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
588	if err != nil {
589		return err
590	}
591	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
592	if err != nil {
593		return err
594	}
595	_, err = tx.Stmt(stmtDeleteMeta).Exec(honkid, "oldrev")
596	if err != nil {
597		return err
598	}
599	return nil
600}
601
602func jsonify(what interface{}) (string, error) {
603	var buf bytes.Buffer
604	e := json.NewEncoder(&buf)
605	e.SetEscapeHTML(false)
606	e.SetIndent("", "")
607	err := e.Encode(what)
608	return buf.String(), err
609}
610
611func unjsonify(s string, dest interface{}) error {
612	d := json.NewDecoder(strings.NewReader(s))
613	err := d.Decode(dest)
614	return err
615}
616
617func cleanupdb(arg string) {
618	db := opendatabase()
619	days, err := strconv.Atoi(arg)
620	var sqlargs []interface{}
621	var where string
622	if err != nil {
623		honker := arg
624		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
625		where = "dt < ? and honker = ?"
626		sqlargs = append(sqlargs, expdate)
627		sqlargs = append(sqlargs, honker)
628	} else {
629		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
630		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
631		sqlargs = append(sqlargs, expdate)
632	}
633	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
634	doordie(db, "delete from donks where honkid not in (select honkid from honks)")
635	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
636	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
637
638	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
639	for _, u := range allusers() {
640		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)
641	}
642
643	filexids := make(map[string]bool)
644	blobdb := openblobdb()
645	rows, err := blobdb.Query("select xid from filedata")
646	if err != nil {
647		log.Fatal(err)
648	}
649	for rows.Next() {
650		var xid string
651		err = rows.Scan(&xid)
652		if err != nil {
653			log.Fatal(err)
654		}
655		filexids[xid] = true
656	}
657	rows.Close()
658	rows, err = db.Query("select xid from filemeta")
659	for rows.Next() {
660		var xid string
661		err = rows.Scan(&xid)
662		if err != nil {
663			log.Fatal(err)
664		}
665		delete(filexids, xid)
666	}
667	rows.Close()
668	tx, err := blobdb.Begin()
669	if err != nil {
670		log.Fatal(err)
671	}
672	for xid, _ := range filexids {
673		_, err = tx.Exec("delete from filedata where xid = ?", xid)
674		if err != nil {
675			log.Fatal(err)
676		}
677	}
678	err = tx.Commit()
679	if err != nil {
680		log.Fatal(err)
681	}
682}
683
684var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
685var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
686var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
687var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
688var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
689var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
690var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
691var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
692var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
693var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
694var stmtHonksForUserFirstClass, stmtSaveMeta, stmtDeleteMeta, stmtUpdateHonk *sql.Stmt
695var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
696
697func preparetodie(db *sql.DB, s string) *sql.Stmt {
698	stmt, err := db.Prepare(s)
699	if err != nil {
700		log.Fatalf("error %s: %s", err, s)
701	}
702	return stmt
703}
704
705func prepareStatements(db *sql.DB) {
706	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")
707	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner) values (?, ?, ?, ?, ?, ?)")
708	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and name = ? and flavor = ?")
709	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ? where honkerid = ? and userid = ?")
710	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
711	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
712	stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
713
714	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 "
715	limit := " order by honks.honkid desc limit 250"
716	smalllimit := " order by honks.honkid desc limit ?"
717	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
718	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
719	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
720	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
721	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
722	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
723	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
724	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
725	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
726	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
727	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
728	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
729	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)
730	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
731	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)
732	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)
733	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
734	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)
735
736	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
737	stmtDeleteMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus <> ?")
738	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
739	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
740	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
741	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
742	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
743	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
744	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
745	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
746	blobdb := openblobdb()
747	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
748	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
749	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
750	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
751	stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
752	stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
753	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
754	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
755	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
756	stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
757	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
758	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")
759	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
760	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
761	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
762	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
763	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
764	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
765	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")
766	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
767	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
768	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")
769	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
770	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
771	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
772}