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	if user.Options.Reaction == "" {
 54		user.Options.Reaction = "none"
 55	}
 56	return user, nil
 57}
 58
 59var somenamedusers = cache.New(cache.Options{Filler: func(name string) (*WhatAbout, bool) {
 60	row := stmtUserByName.QueryRow(name)
 61	user, err := userfromrow(row)
 62	if err != nil {
 63		return nil, false
 64	}
 65	return user, true
 66}})
 67
 68var somenumberedusers = cache.New(cache.Options{Filler: func(userid int64) (*WhatAbout, bool) {
 69	row := stmtUserByNumber.QueryRow(userid)
 70	user, err := userfromrow(row)
 71	if err != nil {
 72		return nil, false
 73	}
 74	return user, true
 75}})
 76
 77func getserveruser() *WhatAbout {
 78	var user *WhatAbout
 79	ok := somenumberedusers.Get(serverUID, &user)
 80	if !ok {
 81		log.Panicf("lost server user")
 82	}
 83	return user
 84}
 85
 86func butwhatabout(name string) (*WhatAbout, error) {
 87	var user *WhatAbout
 88	ok := somenamedusers.Get(name, &user)
 89	if !ok {
 90		return nil, fmt.Errorf("no user: %s", name)
 91	}
 92	return user, nil
 93}
 94
 95var honkerinvalidator cache.Invalidator
 96
 97func gethonkers(userid int64) []*Honker {
 98	rows, err := stmtHonkers.Query(userid)
 99	if err != nil {
100		log.Printf("error querying honkers: %s", err)
101		return nil
102	}
103	defer rows.Close()
104	var honkers []*Honker
105	for rows.Next() {
106		h := new(Honker)
107		var combos, meta string
108		err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor, &combos, &meta)
109		if err == nil {
110			err = unjsonify(meta, &h.Meta)
111		}
112		if err != nil {
113			log.Printf("error scanning honker: %s", err)
114			continue
115		}
116		h.Combos = strings.Split(strings.TrimSpace(combos), " ")
117		honkers = append(honkers, h)
118	}
119	return honkers
120}
121
122func getdubs(userid int64) []*Honker {
123	rows, err := stmtDubbers.Query(userid)
124	return dubsfromrows(rows, err)
125}
126
127func getnameddubs(userid int64, name string) []*Honker {
128	rows, err := stmtNamedDubbers.Query(userid, name)
129	return dubsfromrows(rows, err)
130}
131
132func dubsfromrows(rows *sql.Rows, err error) []*Honker {
133	if err != nil {
134		log.Printf("error querying dubs: %s", err)
135		return nil
136	}
137	defer rows.Close()
138	var honkers []*Honker
139	for rows.Next() {
140		h := new(Honker)
141		err = rows.Scan(&h.ID, &h.UserID, &h.Name, &h.XID, &h.Flavor)
142		if err != nil {
143			log.Printf("error scanning honker: %s", err)
144			return nil
145		}
146		honkers = append(honkers, h)
147	}
148	return honkers
149}
150
151func allusers() []login.UserInfo {
152	var users []login.UserInfo
153	rows, _ := opendatabase().Query("select userid, username from users where userid > 0")
154	defer rows.Close()
155	for rows.Next() {
156		var u login.UserInfo
157		rows.Scan(&u.UserID, &u.Username)
158		users = append(users, u)
159	}
160	return users
161}
162
163func getxonk(userid int64, xid string) *Honk {
164	row := stmtOneXonk.QueryRow(userid, xid)
165	return scanhonk(row)
166}
167
168func getbonk(userid int64, xid string) *Honk {
169	row := stmtOneBonk.QueryRow(userid, xid)
170	return scanhonk(row)
171}
172
173func getpublichonks() []*Honk {
174	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
175	rows, err := stmtPublicHonks.Query(dt, 100)
176	return getsomehonks(rows, err)
177}
178func geteventhonks(userid int64) []*Honk {
179	rows, err := stmtEventHonks.Query(userid, 25)
180	honks := getsomehonks(rows, err)
181	sort.Slice(honks, func(i, j int) bool {
182		var t1, t2 time.Time
183		if honks[i].Time == nil {
184			t1 = honks[i].Date
185		} else {
186			t1 = honks[i].Time.StartTime
187		}
188		if honks[j].Time == nil {
189			t2 = honks[j].Date
190		} else {
191			t2 = honks[j].Time.StartTime
192		}
193		return t1.After(t2)
194	})
195	now := time.Now().Add(-24 * time.Hour)
196	for i, h := range honks {
197		t := h.Date
198		if tm := h.Time; tm != nil {
199			t = tm.StartTime
200		}
201		if t.Before(now) {
202			honks = honks[:i]
203			break
204		}
205	}
206	reversehonks(honks)
207	return honks
208}
209func gethonksbyuser(name string, includeprivate bool, wanted int64) []*Honk {
210	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
211	limit := 50
212	whofore := 2
213	if includeprivate {
214		whofore = 3
215	}
216	rows, err := stmtUserHonks.Query(wanted, whofore, name, dt, limit)
217	return getsomehonks(rows, err)
218}
219func gethonksforuser(userid int64, wanted int64) []*Honk {
220	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
221	rows, err := stmtHonksForUser.Query(wanted, userid, dt, userid, userid)
222	return getsomehonks(rows, err)
223}
224func gethonksforuserfirstclass(userid int64, wanted int64) []*Honk {
225	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
226	rows, err := stmtHonksForUserFirstClass.Query(wanted, userid, dt, userid, userid)
227	return getsomehonks(rows, err)
228}
229
230func gethonksforme(userid int64, wanted int64) []*Honk {
231	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
232	rows, err := stmtHonksForMe.Query(wanted, userid, dt, userid)
233	return getsomehonks(rows, err)
234}
235func gethonksfromlongago(userid int64, wanted int64) []*Honk {
236	now := time.Now().UTC()
237	now = time.Date(now.Year() - 1, now.Month(), now.Day(), now.Hour(), now.Minute(),
238		now.Second(), 0, now.Location())
239	dt1 := now.Add(-36 * time.Hour).Format(dbtimeformat)
240	dt2 := now.Add(12 * time.Hour).Format(dbtimeformat)
241	rows, err := stmtHonksFromLongAgo.Query(wanted, userid, dt1, dt2, userid)
242	return getsomehonks(rows, err)
243}
244func getsavedhonks(userid int64, wanted int64) []*Honk {
245	rows, err := stmtHonksISaved.Query(wanted, userid)
246	return getsomehonks(rows, err)
247}
248func gethonksbyhonker(userid int64, honker string, wanted int64) []*Honk {
249	rows, err := stmtHonksByHonker.Query(wanted, userid, honker, userid)
250	return getsomehonks(rows, err)
251}
252func gethonksbyxonker(userid int64, xonker string, wanted int64) []*Honk {
253	rows, err := stmtHonksByXonker.Query(wanted, userid, xonker, xonker, userid)
254	return getsomehonks(rows, err)
255}
256func gethonksbycombo(userid int64, combo string, wanted int64) []*Honk {
257	combo = "% " + combo + " %"
258	rows, err := stmtHonksByCombo.Query(wanted, userid, userid, combo, userid, wanted, userid, combo, userid)
259	return getsomehonks(rows, err)
260}
261func gethonksbyconvoy(userid int64, convoy string, wanted int64) []*Honk {
262	rows, err := stmtHonksByConvoy.Query(wanted, userid, userid, convoy)
263	honks := getsomehonks(rows, err)
264	return honks
265}
266func gethonksbysearch(userid int64, q string, wanted int64) []*Honk {
267	var queries []string
268	var params []interface{}
269	queries = append(queries, "honks.honkid > ?")
270	params = append(params, wanted)
271	queries = append(queries, "honks.userid = ?")
272	params = append(params, userid)
273
274	terms := strings.Split(q, " ")
275	for _, t := range terms {
276		if t == "" {
277			continue
278		}
279		negate := " "
280		if t[0] == '-' {
281			t = t[1:]
282			negate = " not "
283		}
284		if t == "" {
285			continue
286		}
287		if strings.HasPrefix(t, "site:") {
288			site := t[5:]
289			site = "%" + site + "%"
290			queries = append(queries, "xid"+negate+"like ?")
291			params = append(params, site)
292			continue
293		}
294		if strings.HasPrefix(t, "honker:") {
295			honker := t[7:]
296			xid := fullname(honker, userid)
297			if xid != "" {
298				honker = xid
299			}
300			queries = append(queries, negate+"(honks.honker = ? or honks.oonker = ?)")
301			params = append(params, honker)
302			params = append(params, honker)
303			continue
304		}
305		t = "%" + t + "%"
306		queries = append(queries, "noise"+negate+"like ?")
307		params = append(params, t)
308	}
309
310	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 "
311	where := "where " + strings.Join(queries, " and ")
312	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
313	limit := " order by honks.honkid desc limit 250"
314	params = append(params, userid)
315	rows, err := opendatabase().Query(selecthonks+where+butnotthose+limit, params...)
316	honks := getsomehonks(rows, err)
317	return honks
318}
319func gethonksbyontology(userid int64, name string, wanted int64) []*Honk {
320	rows, err := stmtHonksByOntology.Query(wanted, name, userid, userid)
321	honks := getsomehonks(rows, err)
322	return honks
323}
324
325func reversehonks(honks []*Honk) {
326	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
327		honks[i], honks[j] = honks[j], honks[i]
328	}
329}
330
331func getsomehonks(rows *sql.Rows, err error) []*Honk {
332	if err != nil {
333		log.Printf("error querying honks: %s", err)
334		return nil
335	}
336	defer rows.Close()
337	var honks []*Honk
338	for rows.Next() {
339		h := scanhonk(rows)
340		if h != nil {
341			honks = append(honks, h)
342		}
343	}
344	rows.Close()
345	donksforhonks(honks)
346	return honks
347}
348
349type RowLike interface {
350	Scan(dest ...interface{}) error
351}
352
353func scanhonk(row RowLike) *Honk {
354	h := new(Honk)
355	var dt, aud string
356	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
357		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
358	if err != nil {
359		if err != sql.ErrNoRows {
360			log.Printf("error scanning honk: %s", err)
361		}
362		return nil
363	}
364	h.Date, _ = time.Parse(dbtimeformat, dt)
365	h.Audience = strings.Split(aud, " ")
366	h.Public = loudandproud(h.Audience)
367	return h
368}
369
370func donksforhonks(honks []*Honk) {
371	db := opendatabase()
372	var ids []string
373	hmap := make(map[int64]*Honk)
374	for _, h := range honks {
375		ids = append(ids, fmt.Sprintf("%d", h.ID))
376		hmap[h.ID] = h
377	}
378	idset := strings.Join(ids, ",")
379	// grab donks
380	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)
381	rows, err := db.Query(q)
382	if err != nil {
383		log.Printf("error querying donks: %s", err)
384		return
385	}
386	defer rows.Close()
387	for rows.Next() {
388		var hid int64
389		d := new(Donk)
390		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
391		if err != nil {
392			log.Printf("error scanning donk: %s", err)
393			continue
394		}
395		h := hmap[hid]
396		h.Donks = append(h.Donks, d)
397	}
398	rows.Close()
399
400	// grab onts
401	q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", idset)
402	rows, err = db.Query(q)
403	if err != nil {
404		log.Printf("error querying onts: %s", err)
405		return
406	}
407	defer rows.Close()
408	for rows.Next() {
409		var hid int64
410		var o string
411		err = rows.Scan(&hid, &o)
412		if err != nil {
413			log.Printf("error scanning donk: %s", err)
414			continue
415		}
416		h := hmap[hid]
417		h.Onts = append(h.Onts, o)
418	}
419	rows.Close()
420
421	// grab meta
422	q = fmt.Sprintf("select honkid, genus, json from honkmeta where honkid in (%s)", idset)
423	rows, err = db.Query(q)
424	if err != nil {
425		log.Printf("error querying honkmeta: %s", err)
426		return
427	}
428	defer rows.Close()
429	for rows.Next() {
430		var hid int64
431		var genus, j string
432		err = rows.Scan(&hid, &genus, &j)
433		if err != nil {
434			log.Printf("error scanning honkmeta: %s", err)
435			continue
436		}
437		h := hmap[hid]
438		switch genus {
439		case "place":
440			p := new(Place)
441			err = unjsonify(j, p)
442			if err != nil {
443				log.Printf("error parsing place: %s", err)
444				continue
445			}
446			h.Place = p
447		case "time":
448			t := new(Time)
449			err = unjsonify(j, t)
450			if err != nil {
451				log.Printf("error parsing time: %s", err)
452				continue
453			}
454			h.Time = t
455		case "mentions":
456			err = unjsonify(j, &h.Mentions)
457			if err != nil {
458				log.Printf("error parsing mentions: %s", err)
459				continue
460			}
461		case "oldrev":
462		default:
463			log.Printf("unknown meta genus: %s", genus)
464		}
465	}
466	rows.Close()
467}
468
469func donksforchonks(chonks []*Chonk) {
470	db := opendatabase()
471	var ids []string
472	chmap := make(map[int64]*Chonk)
473	for _, ch := range chonks {
474		ids = append(ids, fmt.Sprintf("%d", ch.ID))
475		chmap[ch.ID] = ch
476	}
477	idset := strings.Join(ids, ",")
478	// grab donks
479	q := fmt.Sprintf("select chonkid, donks.fileid, xid, name, description, url, media, local from donks join filemeta on donks.fileid = filemeta.fileid where chonkid in (%s)", idset)
480	rows, err := db.Query(q)
481	if err != nil {
482		log.Printf("error querying donks: %s", err)
483		return
484	}
485	defer rows.Close()
486	for rows.Next() {
487		var chid int64
488		d := new(Donk)
489		err = rows.Scan(&chid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
490		if err != nil {
491			log.Printf("error scanning donk: %s", err)
492			continue
493		}
494		ch := chmap[chid]
495		ch.Donks = append(ch.Donks, d)
496	}
497}
498
499func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
500	res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
501	if err != nil {
502		return 0, err
503	}
504	fileid, _ := res.LastInsertId()
505	if local {
506		_, err = stmtSaveFileData.Exec(xid, media, data)
507		if err != nil {
508			return 0, err
509		}
510	}
511	return fileid, nil
512}
513
514func finddonk(url string) *Donk {
515	donk := new(Donk)
516	row := stmtFindFile.QueryRow(url)
517	err := row.Scan(&donk.FileID, &donk.XID)
518	if err == nil {
519		return donk
520	}
521	if err != sql.ErrNoRows {
522		log.Printf("error finding file: %s", err)
523	}
524	return nil
525}
526
527func savechonk(ch *Chonk) error {
528	dt := ch.Date.UTC().Format(dbtimeformat)
529	db := opendatabase()
530	tx, err := db.Begin()
531	if err != nil {
532		log.Printf("can't begin tx: %s", err)
533		return err
534	}
535
536	res, err := tx.Stmt(stmtSaveChonk).Exec(ch.UserID, ch.XID, ch.Who, ch.Target, dt, ch.Noise, ch.Format)
537	if err == nil {
538		ch.ID, _ = res.LastInsertId()
539		for _, d := range ch.Donks {
540			_, err := tx.Stmt(stmtSaveDonk).Exec(-1, ch.ID, d.FileID)
541			if err != nil {
542				log.Printf("error saving donk: %s", err)
543				break
544			}
545		}
546		err = tx.Commit()
547	} else {
548		tx.Rollback()
549	}
550	return err
551}
552
553func loadchatter(userid int64) []*Chatter {
554	duedt := time.Now().Add(-3 * 24 * time.Hour).UTC().Format(dbtimeformat)
555	rows, err := stmtLoadChonks.Query(userid, duedt)
556	if err != nil {
557		log.Printf("error loading chonks: %s", err)
558		return nil
559	}
560	defer rows.Close()
561	chonks := make(map[string][]*Chonk)
562	var allchonks []*Chonk
563	for rows.Next() {
564		ch := new(Chonk)
565		var dt string
566		err = rows.Scan(&ch.ID, &ch.UserID, &ch.XID, &ch.Who, &ch.Target, &dt, &ch.Noise, &ch.Format)
567		if err != nil {
568			log.Printf("error scanning chonk: %s", err)
569			continue
570		}
571		ch.Date, _ = time.Parse(dbtimeformat, dt)
572		chonks[ch.Target] = append(chonks[ch.Target], ch)
573		allchonks = append(allchonks, ch)
574	}
575	donksforchonks(allchonks)
576	rows.Close()
577	rows, err = stmtGetChatters.Query(userid)
578	if err != nil {
579		log.Printf("error getting chatters: %s", err)
580		return nil
581	}
582	for rows.Next() {
583		var target string
584		err = rows.Scan(&target)
585		if err != nil {
586			log.Printf("error scanning chatter: %s", target)
587			continue
588		}
589		if _, ok := chonks[target]; !ok {
590			chonks[target] = []*Chonk{}
591
592		}
593	}
594	var chatter []*Chatter
595	for target, chonks := range chonks {
596		chatter = append(chatter, &Chatter{
597			Target: target,
598			Chonks: chonks,
599		})
600	}
601	sort.Slice(chatter, func(i, j int) bool {
602		a, b := chatter[i], chatter[j]
603		if len(a.Chonks) == 0 || len(b.Chonks) == 0 {
604			if len(a.Chonks) == len(b.Chonks) {
605				return a.Target < b.Target
606			}
607			return len(a.Chonks) > len(b.Chonks)
608		}
609		return a.Chonks[len(a.Chonks)-1].Date.After(b.Chonks[len(b.Chonks)-1].Date)
610	})
611
612	return chatter
613}
614
615func savehonk(h *Honk) error {
616	dt := h.Date.UTC().Format(dbtimeformat)
617	aud := strings.Join(h.Audience, " ")
618
619	db := opendatabase()
620	tx, err := db.Begin()
621	if err != nil {
622		log.Printf("can't begin tx: %s", err)
623		return err
624	}
625
626	res, err := tx.Stmt(stmtSaveHonk).Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
627		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
628		h.Oonker, h.Flags)
629	if err == nil {
630		h.ID, _ = res.LastInsertId()
631		err = saveextras(tx, h)
632	}
633	if err == nil {
634		err = tx.Commit()
635	} else {
636		tx.Rollback()
637	}
638	if err != nil {
639		log.Printf("error saving honk: %s", err)
640	}
641	honkhonkline()
642	return err
643}
644
645func updatehonk(h *Honk) error {
646	old := getxonk(h.UserID, h.XID)
647	oldrev := OldRevision{Precis: old.Precis, Noise: old.Noise}
648	dt := h.Date.UTC().Format(dbtimeformat)
649
650	db := opendatabase()
651	tx, err := db.Begin()
652	if err != nil {
653		log.Printf("can't begin tx: %s", err)
654		return err
655	}
656
657	err = deleteextras(tx, h.ID, false)
658	if err == nil {
659		_, err = tx.Stmt(stmtUpdateHonk).Exec(h.Precis, h.Noise, h.Format, h.Whofore, dt, h.ID)
660	}
661	if err == nil {
662		err = saveextras(tx, h)
663	}
664	if err == nil {
665		var j string
666		j, err = jsonify(&oldrev)
667		if err == nil {
668			_, err = tx.Stmt(stmtSaveMeta).Exec(old.ID, "oldrev", j)
669		}
670		if err != nil {
671			log.Printf("error saving oldrev: %s", err)
672		}
673	}
674	if err == nil {
675		err = tx.Commit()
676	} else {
677		tx.Rollback()
678	}
679	if err != nil {
680		log.Printf("error updating honk %d: %s", h.ID, err)
681	}
682	return err
683}
684
685func deletehonk(honkid int64) error {
686	db := opendatabase()
687	tx, err := db.Begin()
688	if err != nil {
689		log.Printf("can't begin tx: %s", err)
690		return err
691	}
692
693	err = deleteextras(tx, honkid, true)
694	if err == nil {
695		_, err = tx.Stmt(stmtDeleteHonk).Exec(honkid)
696	}
697	if err == nil {
698		err = tx.Commit()
699	} else {
700		tx.Rollback()
701	}
702	if err != nil {
703		log.Printf("error deleting honk %d: %s", honkid, err)
704	}
705	return err
706}
707
708func saveextras(tx *sql.Tx, h *Honk) error {
709	for _, d := range h.Donks {
710		_, err := tx.Stmt(stmtSaveDonk).Exec(h.ID, -1, d.FileID)
711		if err != nil {
712			log.Printf("error saving donk: %s", err)
713			return err
714		}
715	}
716	for _, o := range h.Onts {
717		_, err := tx.Stmt(stmtSaveOnt).Exec(strings.ToLower(o), h.ID)
718		if err != nil {
719			log.Printf("error saving ont: %s", err)
720			return err
721		}
722	}
723	if p := h.Place; p != nil {
724		j, err := jsonify(p)
725		if err == nil {
726			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "place", j)
727		}
728		if err != nil {
729			log.Printf("error saving place: %s", err)
730			return err
731		}
732	}
733	if t := h.Time; t != nil {
734		j, err := jsonify(t)
735		if err == nil {
736			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "time", j)
737		}
738		if err != nil {
739			log.Printf("error saving time: %s", err)
740			return err
741		}
742	}
743	if m := h.Mentions; len(m) > 0 {
744		j, err := jsonify(m)
745		if err == nil {
746			_, err = tx.Stmt(stmtSaveMeta).Exec(h.ID, "mentions", j)
747		}
748		if err != nil {
749			log.Printf("error saving mentions: %s", err)
750			return err
751		}
752	}
753	return nil
754}
755
756func deleteextras(tx *sql.Tx, honkid int64, everything bool) error {
757	_, err := tx.Stmt(stmtDeleteDonks).Exec(honkid)
758	if err != nil {
759		return err
760	}
761	_, err = tx.Stmt(stmtDeleteOnts).Exec(honkid)
762	if err != nil {
763		return err
764	}
765	if everything {
766		_, err = tx.Stmt(stmtDeleteAllMeta).Exec(honkid)
767	} else {
768		_, err = tx.Stmt(stmtDeleteSomeMeta).Exec(honkid)
769	}
770	if err != nil {
771		return err
772	}
773	return nil
774}
775
776func jsonify(what interface{}) (string, error) {
777	var buf bytes.Buffer
778	e := json.NewEncoder(&buf)
779	e.SetEscapeHTML(false)
780	e.SetIndent("", "")
781	err := e.Encode(what)
782	return buf.String(), err
783}
784
785func unjsonify(s string, dest interface{}) error {
786	d := json.NewDecoder(strings.NewReader(s))
787	err := d.Decode(dest)
788	return err
789}
790
791func cleanupdb(arg string) {
792	db := opendatabase()
793	days, err := strconv.Atoi(arg)
794	var sqlargs []interface{}
795	var where string
796	if err != nil {
797		honker := arg
798		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
799		where = "dt < ? and honker = ?"
800		sqlargs = append(sqlargs, expdate)
801		sqlargs = append(sqlargs, honker)
802	} else {
803		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
804		where = "dt < ? and convoy not in (select convoy from honks where flags & 4 or whofore = 2 or whofore = 3)"
805		sqlargs = append(sqlargs, expdate)
806	}
807	doordie(db, "delete from honks where flags & 4 = 0 and whofore = 0 and "+where, sqlargs...)
808	doordie(db, "delete from donks where honkid > 0 and honkid not in (select honkid from honks)")
809	doordie(db, "delete from onts where honkid not in (select honkid from honks)")
810	doordie(db, "delete from honkmeta where honkid not in (select honkid from honks)")
811
812	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
813	for _, u := range allusers() {
814		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)
815	}
816
817	filexids := make(map[string]bool)
818	blobdb := openblobdb()
819	rows, err := blobdb.Query("select xid from filedata")
820	if err != nil {
821		log.Fatal(err)
822	}
823	for rows.Next() {
824		var xid string
825		err = rows.Scan(&xid)
826		if err != nil {
827			log.Fatal(err)
828		}
829		filexids[xid] = true
830	}
831	rows.Close()
832	rows, err = db.Query("select xid from filemeta")
833	for rows.Next() {
834		var xid string
835		err = rows.Scan(&xid)
836		if err != nil {
837			log.Fatal(err)
838		}
839		delete(filexids, xid)
840	}
841	rows.Close()
842	tx, err := blobdb.Begin()
843	if err != nil {
844		log.Fatal(err)
845	}
846	for xid, _ := range filexids {
847		_, err = tx.Exec("delete from filedata where xid = ?", xid)
848		if err != nil {
849			log.Fatal(err)
850		}
851	}
852	err = tx.Commit()
853	if err != nil {
854		log.Fatal(err)
855	}
856}
857
858var stmtHonkers, stmtDubbers, stmtNamedDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateHonker *sql.Stmt
859var stmtAnyXonk, stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
860var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
861var stmtHonksFromLongAgo *sql.Stmt
862var stmtHonksByHonker, stmtSaveHonk, stmtUserByName, stmtUserByNumber *sql.Stmt
863var stmtEventHonks, stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
864var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
865var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
866var stmtUntagged, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
867var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
868var stmtAllOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
869var stmtHonksForUserFirstClass *sql.Stmt
870var stmtSaveMeta, stmtDeleteAllMeta, stmtDeleteSomeMeta, stmtUpdateHonk *sql.Stmt
871var stmtHonksISaved, stmtGetFilters, stmtSaveFilter, stmtDeleteFilter *sql.Stmt
872var stmtGetTracks *sql.Stmt
873var stmtSaveChonk, stmtLoadChonks, stmtGetChatters *sql.Stmt
874
875func preparetodie(db *sql.DB, s string) *sql.Stmt {
876	stmt, err := db.Prepare(s)
877	if err != nil {
878		log.Fatalf("error %s: %s", err, s)
879	}
880	return stmt
881}
882
883func prepareStatements(db *sql.DB) {
884	stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos, meta from honkers where userid = ? and (flavor = 'presub' or flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
885	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, ?, ?, ?, '')")
886	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and name = ? and xid = ? and flavor = ?")
887	stmtUpdateHonker = preparetodie(db, "update honkers set name = ?, combos = ?, meta = ? where honkerid = ? and userid = ?")
888	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
889	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
890	stmtNamedDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and name = ? and flavor = 'dub'")
891
892	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 "
893	limit := " order by honks.honkid desc limit 250"
894	smalllimit := " order by honks.honkid desc limit ?"
895	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
896	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
897	stmtAnyXonk = preparetodie(db, selecthonks+"where xid = ? order by honks.honkid asc")
898	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
899	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+smalllimit)
900	stmtEventHonks = preparetodie(db, selecthonks+"where (whofore = 2 or honks.userid = ?) and what = 'event'"+smalllimit)
901	stmtUserHonks = preparetodie(db, selecthonks+"where honks.honkid > ? and (whofore = 2 or whofore = ?) and username = ? and dt > ?"+smalllimit)
902	myhonkers := " and honker in (select xid from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'presub') and combos not like '% - %')"
903	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ?"+myhonkers+butnotthose+limit)
904	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and (what <> 'tonk')"+myhonkers+butnotthose+limit)
905	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
906	stmtHonksFromLongAgo = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and dt > ? and dt < ? and whofore = 2"+butnotthose+limit)
907	stmtHonksISaved = preparetodie(db, selecthonks+"where honks.honkid > ? and honks.userid = ? and flags & 4 order by honks.honkid desc")
908	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)
909	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.honkid > ? and honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
910	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)
911	stmtHonksByConvoy = preparetodie(db, selecthonks+"where honks.honkid > ? and (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
912	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)
913
914	stmtSaveMeta = preparetodie(db, "insert into honkmeta (honkid, genus, json) values (?, ?, ?)")
915	stmtDeleteAllMeta = preparetodie(db, "delete from honkmeta where honkid = ?")
916	stmtDeleteSomeMeta = preparetodie(db, "delete from honkmeta where honkid = ? and genus not in ('oldrev')")
917	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
918	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
919	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, whofore = ?, dt = ? where honkid = ?")
920	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
921	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
922	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, chonkid, fileid) values (?, ?, ?)")
923	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
924	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
925	blobdb := openblobdb()
926	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
927	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
928	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
929	stmtFindFile = preparetodie(db, "select fileid, xid from filemeta where url = ? and local = 1")
930	stmtUserByName = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where username = ? and userid > 0")
931	stmtUserByNumber = preparetodie(db, "select userid, username, displayname, about, pubkey, seckey, options from users where userid = ?")
932	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos, owner, meta, folxid) values (?, ?, ?, ?, '', '', '', '')")
933	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, userid, rcpt, msg) values (?, ?, ?, ?, ?)")
934	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
935	stmtLoadDoover = preparetodie(db, "select tries, userid, rcpt, msg from doovers where dooverid = ?")
936	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
937	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")
938	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
939	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
940	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
941	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
942	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor, dt) values (?, ?, ?, ?)")
943	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ? and dt < ?")
944	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")
945	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
946	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
947	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")
948	stmtGetFilters = preparetodie(db, "select hfcsid, json from hfcs where userid = ?")
949	stmtSaveFilter = preparetodie(db, "insert into hfcs (userid, json) values (?, ?)")
950	stmtDeleteFilter = preparetodie(db, "delete from hfcs where userid = ? and hfcsid = ?")
951	stmtGetTracks = preparetodie(db, "select fetches from tracks where xid = ?")
952	stmtSaveChonk = preparetodie(db, "insert into chonks (userid, xid, who, target, dt, noise, format) values (?, ?, ?, ?, ?, ?, ?)")
953	stmtLoadChonks = preparetodie(db, "select chonkid, userid, xid, who, target, dt, noise, format from chonks where userid = ? and dt > ? order by chonkid asc")
954	stmtGetChatters = preparetodie(db, "select distinct(target) from chonks where userid = ?")
955}