From 27584d82ad625428c0c3f0e9edb48d1f51647ec6 Mon Sep 17 00:00:00 2001
From: Jordan <me@jordan.im>
Date: Thu, 16 Dec 2021 20:50:21 -0700
Subject: db: use subqueries during insertion

---
 db.go | 38 +++++++++++---------------------------
 1 file changed, 11 insertions(+), 27 deletions(-)

diff --git a/db.go b/db.go
index ea22d93..21c68ae 100644
--- a/db.go
+++ b/db.go
@@ -81,7 +81,7 @@ func addArchived(db *sql.DB, m *Message, status_code int) {
 	defer tx.Rollback()
 
 	// Insert new entries in users, guilds, channels tables for new values,
-	// ignoring those already present
+	// ignoring if already present
 	_, err = tx.Exec(`
 		INSERT OR IGNORE INTO users(user_id) VALUES(?);
 		INSERT OR IGNORE INTO guilds(guild_id) VALUES(?);
@@ -91,33 +91,17 @@ func addArchived(db *sql.DB, m *Message, status_code int) {
 		log.Fatal(err)
 	}
 
-	// Store IDs of previously-inserted (or already-existent) rows
-	var user_string_id int
-	var guild_string_id int
-	var channel_string_id int
-
-	// Query users/guilds/channels tables for aforementioned rows
-	err = tx.QueryRow("SELECT id FROM users WHERE user_id = ?;",
-		m.Author).Scan(&user_string_id)
-	if err != nil {
-		log.Fatal(err)
-	}
-	err = tx.QueryRow("SELECT id FROM guilds WHERE guild_id = ?;",
-		m.Guild).Scan(&guild_string_id)
-	if err != nil {
-		log.Fatal(err)
-	}
-	err = tx.QueryRow("SELECT id FROM channels WHERE channel_id = ?;",
-		m.Channel).Scan(&channel_string_id)
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	// Insert entry in URLs table using IDs populated by previous selections
-	_, err = tx.Exec(`INSERT OR IGNORE INTO
+	// Insert entry in URLs table using foreign key reference IDs
+	_, err = tx.Exec(`
+	INSERT OR IGNORE INTO
 		urls(url, user_string_id, guild_string_id, channel_string_id, status_code)
-		VALUES(?, ?, ?, ?, ?);`,
-		m.URL, user_string_id, guild_string_id, channel_string_id, status_code)
+		VALUES(
+			?,
+			(SELECT id FROM users WHERE user_id = ?),
+			(SELECT id FROM guilds WHERE guild_id = ?),
+			(SELECT id FROM channels WHERE channel_id = ?),
+			?
+		);`, m.URL, m.Author, m.Guild, m.Channel, status_code)
 	if err != nil {
 		log.Fatal(err)
 	}
-- 
cgit v1.2.3-54-g00ecf