db: 3NF

3 files changed, 79 insertions, 16 deletions

diff --git a/db.go b/db.go
index 8397d60..cbc6400 100644
--- a/db.go
+++ b/db.go
@@ -34,32 +34,95 @@ func initDB(path string) *sql.DB {
 func initTables(db *sql.DB) {
 
 	q := `CREATE TABLE IF NOT EXISTS urls (
-		id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
+		id integer NOT NULL PRIMARY KEY,
 		url VARCHAR(500) NOT NULL,
-		author_id VARCHAR(18),
-		guild_id VARCHAR(18),
-		channel_id VARCHAR(18),
-		status_code INTEGER
+		user_string_id INTEGER NOT NULL,
+		guild_string_id INTEGER NOT NULL,
+		channel_string_id INTEGER NOT NULL,
+		status_code INTEGER NOT NULL
 	);
-	CREATE UNIQUE INDEX idx_urls_url ON urls(url);`
-	s, err := db.Prepare(q)
+	CREATE UNIQUE INDEX idx_urls_url ON urls(url);
+
+	CREATE TABLE IF NOT EXISTS users (
+		id integer NOT NULL PRIMARY KEY,
+		user_id VARCHAR(18)
+	);
+	CREATE UNIQUE INDEX idx_users_user_id ON users(user_id);
+
+	CREATE TABLE IF NOT EXISTS guilds (
+		id integer NOT NULL PRIMARY KEY,
+		guild_id VARCHAR(18)
+	);
+	CREATE UNIQUE INDEX idx_guilds_guild_id ON guilds(guild_id);
+
+	CREATE TABLE IF NOT EXISTS channels (
+		id integer NOT NULL PRIMARY KEY,
+		channel_id VARCHAR(18)
+	);
+	CREATE UNIQUE INDEX idx_channels_channel_id ON channels(channel_id);`
+	_, err := db.Exec(q)
 	if err != nil {
 		log.Fatal(err)
 	}
-	defer s.Close()
-	s.Exec()
 }
 
 func addArchived(db *sql.DB, m *Message, status_code int) {
 
-	q := `INSERT OR IGNORE INTO urls(url, author_id, guild_id, channel_id, status_code) VALUES (?, ?, ?, ?, ?)`
-	s, err := db.Prepare(q)
+	// Start a transaction using default isolation
+	tx, err := db.Begin()
 	if err != nil {
 		log.Fatal(err)
 	}
-	defer s.Close()
-	_, err = s.Exec(m.URL, m.Author, m.Guild, m.Channel, status_code)
+
+	// Insert new entries in users, guilds, channels tables for new values,
+	// ignoring those already present
+	_, err = tx.Exec(`
+		INSERT OR IGNORE INTO users(user_id) VALUES(?);
+		INSERT OR IGNORE INTO guilds(guild_id) VALUES(?);
+		INSERT OR IGNORE INTO channels(channel_id) VALUES(?);`,
+		m.Author, m.Guild, m.Channel)
 	if err != nil {
+		_ = tx.Rollback()
+		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 {
+		_ = tx.Rollback()
+		log.Fatal(err)
+	}
+	err = tx.QueryRow("SELECT id FROM guilds WHERE guild_id = ?;",
+		m.Guild).Scan(&guild_string_id)
+	if err != nil {
+		_ = tx.Rollback()
+		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
+		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)
+	if err != nil {
+		_ = tx.Rollback()
+		log.Fatal(err)
+	}
+
+	// Finally commit the transaction
+	if err := tx.Commit(); err != nil {
+		_ = tx.Rollback()
 		log.Fatal(err)
 	}
 }
diff --git a/go.mod b/go.mod
index 167d5f5..ef29ca4 100644
--- a/go.mod
+++ b/go.mod
@@ -5,5 +5,5 @@ go 1.16
 require (
 	github.com/bwmarrin/discordgo v0.23.2
 	github.com/mattn/go-sqlite3 v1.14.9
-	golang.org/x/net v0.0.0-20211201190559-0a0e4e1bb54c
+	golang.org/x/net v0.0.0-20211205041911-012df41ee64c
 )
diff --git a/go.sum b/go.sum
index 537376f..f022b29 100644
--- a/go.sum
+++ b/go.sum
@@ -6,8 +6,8 @@ github.com/mattn/go-sqlite3 v1.14.9 h1:10HX2Td0ocZpYEjhilsuo6WWtUqttj2Kb0KtD86/K
 github.com/mattn/go-sqlite3 v1.14.9/go.mod h1:NyWgC/yNuGj7Q9rpYnZvas74GogHl5/Z4A/KQRfk6bU=
 golang.org/x/crypto v0.0.0-20181030102418-4d3f4d9ffa16 h1:y6ce7gCWtnH+m3dCjzQ1PCuwl28DDIc3VNnvY29DlIA=
 golang.org/x/crypto v0.0.0-20181030102418-4d3f4d9ffa16/go.mod h1:6SG95UA2DQfeDnfUPMdvaQW0Q7yPrPDi9nlGo2tz2b4=
-golang.org/x/net v0.0.0-20211201190559-0a0e4e1bb54c h1:WtYZ93XtWSO5KlOMgPZu7hXY9WhMZpprvlm5VwvAl8c=
-golang.org/x/net v0.0.0-20211201190559-0a0e4e1bb54c/go.mod h1:9nx3DQGgdP8bBQD5qxJ1jj9UTztislL4KSBs9R2vV5Y=
+golang.org/x/net v0.0.0-20211205041911-012df41ee64c h1:7SfqwP5fxEtl/P02w5IhKc86ziJ+A25yFrkVgoy2FT8=
+golang.org/x/net v0.0.0-20211205041911-012df41ee64c/go.mod h1:9nx3DQGgdP8bBQD5qxJ1jj9UTztislL4KSBs9R2vV5Y=
 golang.org/x/sys v0.0.0-20201119102817-f84b799fce68/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20210423082822-04245dca01da/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/term v0.0.0-20201126162022-7de9c90e9dd1/go.mod h1:bj7SfCRtBDWHUb9snDiAeCFNEtKQo2Wmx5Cou7ajbmo=