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