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	"database/sql"
 20	"fmt"
 21	"log"
 22	"strconv"
 23	"strings"
 24	"time"
 25
 26	"humungus.tedunangst.com/r/webs/login"
 27)
 28
 29func butwhatabout(name string) (*WhatAbout, error) {
 30	row := stmtWhatAbout.QueryRow(name)
 31	var user WhatAbout
 32	var options string
 33	err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &options)
 34	user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
 35	user.SkinnyCSS = strings.Contains(options, " skinny ")
 36	return &user, err
 37}
 38
 39func gethonkers(userid int64) []*Honker {
 40	rows, err := stmtHonkers.Query(userid)
 41	if err != nil {
 42		log.Printf("error querying honkers: %s", err)
 43		return nil
 44	}
 45	defer rows.Close()
 46	var honkers []*Honker
 47	for rows.Next() {
 48		var f Honker
 49		var combos string
 50		err = rows.Scan(&f.ID, &f.UserID, &f.Name, &f.XID, &f.Flavor, &combos)
 51		f.Combos = strings.Split(strings.TrimSpace(combos), " ")
 52		if err != nil {
 53			log.Printf("error scanning honker: %s", err)
 54			return nil
 55		}
 56		honkers = append(honkers, &f)
 57	}
 58	return honkers
 59}
 60
 61func getdubs(userid int64) []*Honker {
 62	rows, err := stmtDubbers.Query(userid)
 63	if err != nil {
 64		log.Printf("error querying dubs: %s", err)
 65		return nil
 66	}
 67	defer rows.Close()
 68	var honkers []*Honker
 69	for rows.Next() {
 70		var f Honker
 71		err = rows.Scan(&f.ID, &f.UserID, &f.Name, &f.XID, &f.Flavor)
 72		if err != nil {
 73			log.Printf("error scanning honker: %s", err)
 74			return nil
 75		}
 76		honkers = append(honkers, &f)
 77	}
 78	return honkers
 79}
 80
 81func allusers() []login.UserInfo {
 82	var users []login.UserInfo
 83	rows, _ := opendatabase().Query("select userid, username from users")
 84	defer rows.Close()
 85	for rows.Next() {
 86		var u login.UserInfo
 87		rows.Scan(&u.UserID, &u.Username)
 88		users = append(users, u)
 89	}
 90	return users
 91}
 92
 93func getxonk(userid int64, xid string) *Honk {
 94	row := stmtOneXonk.QueryRow(userid, xid)
 95	return scanhonk(row)
 96}
 97
 98func getbonk(userid int64, xid string) *Honk {
 99	row := stmtOneBonk.QueryRow(userid, xid)
100	return scanhonk(row)
101}
102
103func getpublichonks() []*Honk {
104	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
105	rows, err := stmtPublicHonks.Query(dt)
106	return getsomehonks(rows, err)
107}
108func gethonksbyuser(name string, includeprivate bool) []*Honk {
109	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
110	whofore := 2
111	if includeprivate {
112		whofore = 3
113	}
114	rows, err := stmtUserHonks.Query(whofore, name, dt)
115	return getsomehonks(rows, err)
116}
117func gethonksforuser(userid int64) []*Honk {
118	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
119	rows, err := stmtHonksForUser.Query(userid, dt, userid, userid)
120	return getsomehonks(rows, err)
121}
122func gethonksforuserfirstclass(userid int64) []*Honk {
123	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
124	rows, err := stmtHonksForUserFirstClass.Query(userid, dt, userid, userid)
125	return getsomehonks(rows, err)
126}
127func gethonksforme(userid int64) []*Honk {
128	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
129	rows, err := stmtHonksForMe.Query(userid, dt, userid)
130	return getsomehonks(rows, err)
131}
132func gethonksbyhonker(userid int64, honker string) []*Honk {
133	rows, err := stmtHonksByHonker.Query(userid, honker, userid)
134	return getsomehonks(rows, err)
135}
136func gethonksbyxonker(userid int64, xonker string) []*Honk {
137	rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
138	return getsomehonks(rows, err)
139}
140func gethonksbycombo(userid int64, combo string) []*Honk {
141	combo = "% " + combo + " %"
142	rows, err := stmtHonksByCombo.Query(userid, combo, userid)
143	return getsomehonks(rows, err)
144}
145func gethonksbyconvoy(userid int64, convoy string) []*Honk {
146	rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
147	honks := getsomehonks(rows, err)
148	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
149		honks[i], honks[j] = honks[j], honks[i]
150	}
151	return honks
152}
153func gethonksbysearch(userid int64, q string) []*Honk {
154	q = "%" + q + "%"
155	rows, err := stmtHonksBySearch.Query(userid, q)
156	honks := getsomehonks(rows, err)
157	return honks
158}
159func gethonksbyontology(userid int64, name string) []*Honk {
160	rows, err := stmtHonksByOntology.Query(name, userid, userid)
161	honks := getsomehonks(rows, err)
162	return honks
163}
164
165func getsomehonks(rows *sql.Rows, err error) []*Honk {
166	if err != nil {
167		log.Printf("error querying honks: %s", err)
168		return nil
169	}
170	defer rows.Close()
171	var honks []*Honk
172	for rows.Next() {
173		h := scanhonk(rows)
174		if h != nil {
175			honks = append(honks, h)
176		}
177	}
178	rows.Close()
179	donksforhonks(honks)
180	return honks
181}
182
183type RowLike interface {
184	Scan(dest ...interface{}) error
185}
186
187func scanhonk(row RowLike) *Honk {
188	h := new(Honk)
189	var dt, aud string
190	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
191		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
192	if err != nil {
193		if err != sql.ErrNoRows {
194			log.Printf("error scanning honk: %s", err)
195		}
196		return nil
197	}
198	h.Date, _ = time.Parse(dbtimeformat, dt)
199	h.Audience = strings.Split(aud, " ")
200	h.Public = !keepitquiet(h.Audience)
201	return h
202}
203
204func donksforhonks(honks []*Honk) {
205	db := opendatabase()
206	var ids []string
207	hmap := make(map[int64]*Honk)
208	for _, h := range honks {
209		ids = append(ids, fmt.Sprintf("%d", h.ID))
210		hmap[h.ID] = h
211	}
212	// grab donks
213	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)", strings.Join(ids, ","))
214	rows, err := db.Query(q)
215	if err != nil {
216		log.Printf("error querying donks: %s", err)
217		return
218	}
219	defer rows.Close()
220	for rows.Next() {
221		var hid int64
222		var d Donk
223		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
224		if err != nil {
225			log.Printf("error scanning donk: %s", err)
226			continue
227		}
228		h := hmap[hid]
229		h.Donks = append(h.Donks, &d)
230	}
231	rows.Close()
232
233	// grab onts
234	q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", strings.Join(ids, ","))
235	rows, err = db.Query(q)
236	if err != nil {
237		log.Printf("error querying onts: %s", err)
238		return
239	}
240	defer rows.Close()
241	for rows.Next() {
242		var hid int64
243		var o string
244		err = rows.Scan(&hid, &o)
245		if err != nil {
246			log.Printf("error scanning donk: %s", err)
247			continue
248		}
249		h := hmap[hid]
250		h.Onts = append(h.Onts, o)
251	}
252	rows.Close()
253	// grab places
254	q = fmt.Sprintf("select honkid, name, latitude, longitude, url from places where honkid in (%s)", strings.Join(ids, ","))
255	rows, err = db.Query(q)
256	if err != nil {
257		log.Printf("error querying places: %s", err)
258		return
259	}
260	defer rows.Close()
261	for rows.Next() {
262		var hid int64
263		p := new(Place)
264		err = rows.Scan(&hid, &p.Name, &p.Latitude, &p.Longitude, &p.Url)
265		if err != nil {
266			log.Printf("error scanning place: %s", err)
267			continue
268		}
269		h := hmap[hid]
270		h.Place = p
271	}
272	rows.Close()
273}
274
275func savefile(xid string, name string, desc string, url string, media string, local bool, data []byte) (int64, error) {
276	res, err := stmtSaveFile.Exec(xid, name, desc, url, media, local)
277	if err != nil {
278		return 0, err
279	}
280	fileid, _ := res.LastInsertId()
281	_, err = stmtSaveFileData.Exec(xid, media, data)
282	if err != nil {
283		return 0, err
284	}
285	return fileid, nil
286}
287
288func savehonk(h *Honk) error {
289	dt := h.Date.UTC().Format(dbtimeformat)
290	aud := strings.Join(h.Audience, " ")
291
292	res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
293		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
294		h.Oonker, h.Flags)
295	if err != nil {
296		log.Printf("err saving honk: %s", err)
297		return err
298	}
299	h.ID, _ = res.LastInsertId()
300	err = saveextras(h)
301	return err
302}
303
304func saveextras(h *Honk) error {
305	for _, d := range h.Donks {
306		_, err := stmtSaveDonk.Exec(h.ID, d.FileID)
307		if err != nil {
308			log.Printf("err saving donk: %s", err)
309			return err
310		}
311	}
312	for _, o := range h.Onts {
313		_, err := stmtSaveOnt.Exec(strings.ToLower(o), h.ID)
314		if err != nil {
315			log.Printf("error saving ont: %s", err)
316			return err
317		}
318	}
319	if p := h.Place; p != nil {
320		_, err := stmtSavePlace.Exec(h.ID, p.Name, p.Latitude, p.Longitude, p.Url)
321		if err != nil {
322			log.Printf("error saving place: %s", err)
323			return err
324		}
325	}
326
327	return nil
328}
329
330func deleteextras(honkid int64) {
331	_, err := stmtDeleteDonks.Exec(honkid)
332	if err != nil {
333		log.Printf("error deleting: %s", err)
334	}
335	_, err = stmtDeleteOnts.Exec(honkid)
336	if err != nil {
337		log.Printf("error deleting: %s", err)
338	}
339	_, err = stmtDeletePlace.Exec(honkid)
340	if err != nil {
341		log.Printf("error deleting: %s", err)
342	}
343}
344
345func deletehonk(honkid int64) {
346	deleteextras(honkid)
347	_, err := stmtDeleteHonk.Exec(honkid)
348	if err != nil {
349		log.Printf("error deleting: %s", err)
350	}
351}
352
353func updatehonk(h *Honk) {
354	old := getxonk(h.UserID, h.XID)
355	_, err := stmtSaveOld.Exec(old.ID, old.Precis, old.Noise)
356	if err != nil {
357		log.Printf("error saving old: %s", err)
358		return
359	}
360	deleteextras(h.ID)
361
362	dt := h.Date.UTC().Format(dbtimeformat)
363	stmtUpdateHonk.Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
364
365	saveextras(h)
366}
367
368func cleanupdb(arg string) {
369	db := opendatabase()
370	days, err := strconv.Atoi(arg)
371	if err != nil {
372		honker := arg
373		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
374		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
375		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
376	} else {
377		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
378		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and convoy not in (select convoy from honks where whofore = 2 or whofore = 3))", expdate)
379		doordie(db, "delete from honks where dt < ? and whofore = 0 and convoy not in (select convoy from honks where whofore = 2 or whofore = 3)", expdate)
380	}
381	doordie(db, "delete from filemeta where fileid not in (select fileid from donks)")
382	for _, u := range allusers() {
383		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)
384	}
385}
386
387var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
388var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
389var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
390var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtWhatAbout *sql.Stmt
391var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk *sql.Stmt
392var stmtFindFile, stmtGetFileData, stmtSaveFileData, stmtSaveFile *sql.Stmt
393var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
394var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
395var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
396var stmtSelectOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
397var stmtSavePlace, stmtDeletePlace, stmtHonksForUserFirstClass, stmtSaveOld, stmtUpdateHonk *sql.Stmt
398
399func preparetodie(db *sql.DB, s string) *sql.Stmt {
400	stmt, err := db.Prepare(s)
401	if err != nil {
402		log.Fatalf("error %s: %s", err, s)
403	}
404	return stmt
405}
406
407func prepareStatements(db *sql.DB) {
408	stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
409	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
410	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
411	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
412	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
413	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
414
415	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 "
416	limit := " order by honks.honkid desc limit 250"
417	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
418	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
419	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
420	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
421	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
422	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and honker in (select xid from honkers where userid = ? and flavor = 'sub' and combos not like '% - %')"+butnotthose+limit)
423	stmtHonksForUserFirstClass = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and (what <> 'tonk') and honker in (select xid from honkers where userid = ? and flavor = 'sub' and combos not like '% - %')"+butnotthose+limit)
424	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
425	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
426	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
427	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
428	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
429	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
430	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
431
432	stmtSaveOld = preparetodie(db, "insert into forsaken (honkid, precis, noise) values (?, ?, ?)")
433	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
434	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
435	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
436	stmtSavePlace = preparetodie(db, "insert into places (honkid, name, latitude, longitude, url) values (?, ?, ?, ?, ?)")
437	stmtDeletePlace = preparetodie(db, "delete from places where honkid = ?")
438	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
439	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
440	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
441	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
442	stmtSaveFile = preparetodie(db, "insert into filemeta (xid, name, description, url, media, local) values (?, ?, ?, ?, ?, ?)")
443	blobdb, err := sql.Open("sqlite3", blobdbname)
444	if err != nil {
445		log.Fatal(err)
446	}
447	stmtSaveFileData = preparetodie(blobdb, "insert into filedata (xid, media, content) values (?, ?, ?)")
448	stmtGetFileData = preparetodie(blobdb, "select media, content from filedata where xid = ?")
449	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
450	stmtFindFile = preparetodie(db, "select fileid from filemeta where url = ? and local = 1")
451	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
452	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
453	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
454	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
455	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
456	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
457	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers")
458	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
459	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
460	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
461	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
462	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
463	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
464	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")
465	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
466	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
467	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
468}