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	h := new(Honk)
 95	var dt, aud, onts string
 96	row := stmtOneXonk.QueryRow(userid, xid)
 97	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
 98		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
 99	if err != nil {
100		if err != sql.ErrNoRows {
101			log.Printf("error scanning xonk: %s", err)
102		}
103		return nil
104	}
105	h.Date, _ = time.Parse(dbtimeformat, dt)
106	h.Audience = strings.Split(aud, " ")
107	h.Public = !keepitquiet(h.Audience)
108	if len(onts) > 0 {
109		h.Onts = strings.Split(onts, " ")
110	}
111	return h
112}
113
114func getbonk(userid int64, xid string) *Honk {
115	h := new(Honk)
116	var dt, aud, onts string
117	row := stmtOneBonk.QueryRow(userid, xid)
118	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
119		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
120	if err != nil {
121		if err != sql.ErrNoRows {
122			log.Printf("error scanning xonk: %s", err)
123		}
124		return nil
125	}
126	h.Date, _ = time.Parse(dbtimeformat, dt)
127	h.Audience = strings.Split(aud, " ")
128	h.Public = !keepitquiet(h.Audience)
129	if len(onts) > 0 {
130		h.Onts = strings.Split(onts, " ")
131	}
132	return h
133}
134
135func getpublichonks() []*Honk {
136	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
137	rows, err := stmtPublicHonks.Query(dt)
138	return getsomehonks(rows, err)
139}
140func gethonksbyuser(name string, includeprivate bool) []*Honk {
141	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
142	whofore := 2
143	if includeprivate {
144		whofore = 3
145	}
146	rows, err := stmtUserHonks.Query(whofore, name, dt)
147	return getsomehonks(rows, err)
148}
149func gethonksforuser(userid int64) []*Honk {
150	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
151	rows, err := stmtHonksForUser.Query(userid, dt, userid, userid)
152	return getsomehonks(rows, err)
153}
154func gethonksforme(userid int64) []*Honk {
155	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
156	rows, err := stmtHonksForMe.Query(userid, dt, userid)
157	return getsomehonks(rows, err)
158}
159func gethonksbyhonker(userid int64, honker string) []*Honk {
160	rows, err := stmtHonksByHonker.Query(userid, honker, userid)
161	return getsomehonks(rows, err)
162}
163func gethonksbyxonker(userid int64, xonker string) []*Honk {
164	rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
165	return getsomehonks(rows, err)
166}
167func gethonksbycombo(userid int64, combo string) []*Honk {
168	combo = "% " + combo + " %"
169	rows, err := stmtHonksByCombo.Query(userid, combo, userid)
170	return getsomehonks(rows, err)
171}
172func gethonksbyconvoy(userid int64, convoy string) []*Honk {
173	rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
174	honks := getsomehonks(rows, err)
175	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
176		honks[i], honks[j] = honks[j], honks[i]
177	}
178	return honks
179}
180func gethonksbysearch(userid int64, q string) []*Honk {
181	q = "%" + q + "%"
182	rows, err := stmtHonksBySearch.Query(userid, q)
183	honks := getsomehonks(rows, err)
184	return honks
185}
186func gethonksbyontology(userid int64, name string) []*Honk {
187	rows, err := stmtHonksByOntology.Query(name, userid, userid)
188	honks := getsomehonks(rows, err)
189	return honks
190}
191
192func getsomehonks(rows *sql.Rows, err error) []*Honk {
193	if err != nil {
194		log.Printf("error querying honks: %s", err)
195		return nil
196	}
197	defer rows.Close()
198	var honks []*Honk
199	for rows.Next() {
200		var h Honk
201		var dt, aud, onts string
202		err = rows.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID,
203			&h.RID, &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
204		if err != nil {
205			log.Printf("error scanning honks: %s", err)
206			return nil
207		}
208		h.Date, _ = time.Parse(dbtimeformat, dt)
209		h.Audience = strings.Split(aud, " ")
210		h.Public = !keepitquiet(h.Audience)
211		if len(onts) > 0 {
212			h.Onts = strings.Split(onts, " ")
213		}
214		honks = append(honks, &h)
215	}
216	rows.Close()
217	donksforhonks(honks)
218	return honks
219}
220
221func donksforhonks(honks []*Honk) {
222	db := opendatabase()
223	var ids []string
224	hmap := make(map[int64]*Honk)
225	for _, h := range honks {
226		ids = append(ids, fmt.Sprintf("%d", h.ID))
227		hmap[h.ID] = h
228	}
229	q := fmt.Sprintf("select honkid, donks.fileid, xid, name, description, url, media, local from donks join files on donks.fileid = files.fileid where honkid in (%s)", strings.Join(ids, ","))
230	rows, err := db.Query(q)
231	if err != nil {
232		log.Printf("error querying donks: %s", err)
233		return
234	}
235	defer rows.Close()
236	for rows.Next() {
237		var hid int64
238		var d Donk
239		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
240		if err != nil {
241			log.Printf("error scanning donk: %s", err)
242			continue
243		}
244		h := hmap[hid]
245		h.Donks = append(h.Donks, &d)
246	}
247}
248
249func savehonk(h *Honk) error {
250	dt := h.Date.UTC().Format(dbtimeformat)
251	aud := strings.Join(h.Audience, " ")
252
253	res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
254		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
255		h.Oonker, h.Flags, strings.Join(h.Onts, " "))
256	if err != nil {
257		log.Printf("err saving honk: %s", err)
258		return err
259	}
260	h.ID, _ = res.LastInsertId()
261	for _, d := range h.Donks {
262		_, err = stmtSaveDonk.Exec(h.ID, d.FileID)
263		if err != nil {
264			log.Printf("err saving donk: %s", err)
265			return err
266		}
267	}
268	for _, o := range h.Onts {
269		_, err = stmtSaveOnts.Exec(strings.ToLower(o), h.ID)
270		if err != nil {
271			log.Printf("error saving ont: %s", err)
272			return err
273		}
274	}
275	return nil
276}
277
278func cleanupdb(arg string) {
279	db := opendatabase()
280	days, err := strconv.Atoi(arg)
281	if err != nil {
282		honker := arg
283		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
284		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
285		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
286	} else {
287		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
288		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)
289		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)
290	}
291	doordie(db, "delete from files where fileid not in (select fileid from donks)")
292	for _, u := range allusers() {
293		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)
294	}
295}
296
297var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
298var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
299var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
300var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
301var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
302var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
303var stmtThumbBiters, stmtZonkIt, stmtZonkDonks, stmtSaveZonker *sql.Stmt
304var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
305var stmtSelectOnts, stmtSaveOnts, stmtUpdateFlags, stmtClearFlags *sql.Stmt
306
307func preparetodie(db *sql.DB, s string) *sql.Stmt {
308	stmt, err := db.Prepare(s)
309	if err != nil {
310		log.Fatalf("error %s: %s", err, s)
311	}
312	return stmt
313}
314
315func prepareStatements(db *sql.DB) {
316	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")
317	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
318	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
319	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
320	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
321	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
322
323	selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, convoy, whofore, flags, onts from honks join users on honks.userid = users.userid "
324	limit := " order by honks.honkid desc limit 250"
325	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
326	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
327	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
328	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
329	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
330	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)
331	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
332	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
333	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
334	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
335	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
336	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
337	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
338
339	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, onts) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
340	stmtSaveOnts = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
341	stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
342	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
343	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
344	stmtZonkIt = preparetodie(db, "delete from honks where honkid = ?")
345	stmtZonkDonks = preparetodie(db, "delete from donks where honkid = ?")
346	stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
347	stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
348	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
349	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
350	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
351	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
352	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
353	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
354	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
355	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
356	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
357	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
358	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
359	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
360	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
361	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")
362	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
363	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
364	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
365}