cmd/marabot: reduce S3 query counts

Signed-off-by: Xe Iaso <me@xeiaso.net>

3 files changed, 35 insertions, 3 deletions

diff --git a/cmd/marabot/discord.go b/cmd/marabot/discord.go
index 5ea8966..a1e16c5 100644
--- a/cmd/marabot/discord.go
+++ b/cmd/marabot/discord.go
@@ -55,6 +55,19 @@ func (mr *MaraRevolt) importDiscordData(ctx context.Context, db *sql.DB, dg *dis
 		mr.attachmentPreprocess.Add([3]string{eURL, "emoji", ""}, len(eURL))
 	}
 
+	rows, err := mr.db.QueryContext(ctx, "SELECT url, message_id FROM discord_attachments WHERE url NOT IN ( SELECT url FROM s3_uploads )")
+	if err == nil {
+		defer rows.Close()
+		for rows.Next() {
+			var url, messageID string
+			if err := rows.Scan(&url, &messageID); err != nil {
+				continue
+			}
+
+			mr.attachmentPreprocess.Add([3]string{url, "attachments", messageID}, len(url))
+		}
+	}
+
 	return nil
 }
 
@@ -125,6 +138,9 @@ DO UPDATE SET username = EXCLUDED.username, avatar_url = EXCLUDED.avatar_url, ac
 	}
 
 	for _, emb := range m.Embeds {
+		if emb.Image == nil {
+			continue
+		}
 		if _, err := mr.db.Exec(`INSERT INTO discord_attachments (id, message_id, url, proxy_url, filename, content_type, width, height, size) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`, uuid.NewString(), m.ID, emb.Image.URL, emb.Image.ProxyURL, filepath.Base(emb.Image.URL), "", emb.Image.Width, emb.Image.Height, 0); err != nil {
 			return err
 		}
diff --git a/cmd/marabot/main.go b/cmd/marabot/main.go
index bbbec9a..e7fb608 100644
--- a/cmd/marabot/main.go
+++ b/cmd/marabot/main.go
@@ -171,9 +171,19 @@ func (mr *MaraRevolt) preprocessLinks(ctx context.Context, data [][3]string) {
 		link := linkkind[0]
 		msgID := linkkind[2]
 
+		var count int
+		if err := mr.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM s3_uploads WHERE url = ?", link).Scan(&count); err != nil {
+			ln.Error(ctx, err)
+			continue
+		}
+		if count != 0 {
+			continue
+		}
+
 		att, err := hashURL(link, kind)
 		if err != nil {
 			ln.Error(ctx, err, ln.F{"link": link, "kind": kind})
+			continue
 		}
 
 		att.MessageID = aws.String(msgID)
@@ -225,12 +235,16 @@ func (mr *MaraRevolt) s3Upload(ctx context.Context, att []*Attachment) {
 	for _, att := range att {
 		key := filepath.Join(att.Kind, att.ID)
 
+		f := ln.F{"kind": att.Kind, "id": att.ID, "url": att.URL, "content_type": att.ContentType}
+
 		var count int
 		if err := mr.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM s3_uploads WHERE id = ?", att.ID).Scan(&count); err != nil {
-			ln.Error(ctx, err)
+			ln.Error(ctx, err, f)
 			continue
 		}
 
+		f["count"] = count
+
 		if count != 0 {
 			continue
 		}
@@ -245,12 +259,12 @@ func (mr *MaraRevolt) s3Upload(ctx context.Context, att []*Attachment) {
 				"Message-ID":   att.MessageID,
 			},
 		}); err != nil {
-			ln.Error(ctx, err, ln.Action("trying to upload to S3"), ln.F{"att_url": att.URL, "att_content_type": att.ContentType})
+			ln.Error(ctx, err, ln.Action("trying to upload to S3"), f)
 			continue
 		}
 
 		if _, err := mr.db.ExecContext(ctx, "INSERT INTO s3_uploads(id, url, kind, content_type, created_at, message_id) VALUES (?, ?, ?, ?, ?, ?)", att.ID, att.URL, att.Kind, att.ContentType, att.CreatedAt, att.MessageID); err != nil {
-			ln.Error(ctx, err, ln.Action("saving upload information to DB"))
+			ln.Error(ctx, err, ln.Action("saving upload information to DB"), f)
 		}
 	}
 
diff --git a/cmd/marabot/revolt.go b/cmd/marabot/revolt.go
index f9bffc0..4588713 100644
--- a/cmd/marabot/revolt.go
+++ b/cmd/marabot/revolt.go
@@ -67,6 +67,8 @@ func (mr *MaraRevolt) MessageCreate(ctx context.Context, msg *revolt.Message) er
 	}
 	avatarURL := mr.cli.ResolveAttachment(author.Avatar)
 
+	mr.attachmentPreprocess.Add([3]string{avatarURL, "avatars", ""}, len(avatarURL))
+
 	if _, err := mr.db.ExecContext(ctx, "INSERT INTO revolt_users(id, username, avatar_url, created_at) VALUES(?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET username = EXCLUDED.username, avatar_url = EXCLUDED.avatar_url, created_at = EXCLUDED.created_at", author.Id, author.Username, avatarURL, author.CreatedAt.Format(time.RFC3339)); err != nil {
 		ln.Error(ctx, err, ln.Action("writing revolt user record"))
 	}