feat: adapt genre code for n:m relation via imdb_genre

- genre table: (id, name) with unique name constraint
- imdb_genre table: (id, imdb_id, genre_id) junction table
- Upsert genres via INSERT ... ON DUPLICATE KEY UPDATE
- Link via imdb_genre using LAST_INSERT_ID
- Check missing genres via LEFT JOIN imdb_genre

1 files changed, 29 insertions, 9 deletions

diff --git a/src/imdbdata.go b/src/imdbdata.go
index c4216a7..cea914e 100644
--- a/src/imdbdata.go
+++ b/src/imdbdata.go
@@ -263,15 +263,25 @@ func (a *App) applyImdbUpdates(ratings map[string]ratingEntry, basics map[string
 	}
 	defer bStmt.Close()
 
-	genreStmt, err := tx.Prepare(`
-		INSERT INTO genre (imdb_id, name)
-		SELECT i.id, ? FROM imdb i WHERE i.imdb_id = ?
+	genreUpsert, err := tx.Prepare(`
+		INSERT INTO genre (name) VALUES (?)
+		ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)
 	`)
 	if err != nil {
 		tx.Rollback()
-		return fmt.Errorf("prepare genre insert: %w", err)
+		return fmt.Errorf("prepare genre upsert: %w", err)
 	}
-	defer genreStmt.Close()
+	defer genreUpsert.Close()
+
+	genreLink, err := tx.Prepare(`
+		INSERT INTO imdb_genre (imdb_id, genre_id)
+		VALUES ((SELECT i.id FROM imdb i WHERE i.imdb_id = ?), ?)
+	`)
+	if err != nil {
+		tx.Rollback()
+		return fmt.Errorf("prepare genre link: %w", err)
+	}
+	defer genreLink.Close()
 
 	rCount, bCount, gCount := 0, 0, 0
 
@@ -295,9 +305,19 @@ func (a *App) applyImdbUpdates(ratings map[string]ratingEntry, basics map[string
 		bCount++
 
 		for _, g := range b.Genres {
-			if _, err := genreStmt.Exec(g, id); err != nil {
+			res, err := genreUpsert.Exec(g)
+			if err != nil {
+				tx.Rollback()
+				return fmt.Errorf("upsert genre %s: %w", g, err)
+			}
+			genreID, err := res.LastInsertId()
+			if err != nil {
+				tx.Rollback()
+				return fmt.Errorf("get genre id: %w", err)
+			}
+			if _, err := genreLink.Exec(id, genreID); err != nil {
 				tx.Rollback()
-				return fmt.Errorf("insert genre %s for %s: %w", g, id, err)
+				return fmt.Errorf("link genre %s for %s: %w", g, id, err)
 			}
 			gCount++
 		}
@@ -340,8 +360,8 @@ func (a *App) fetchAndUpdateImdbData() error {
 	}
 	if err := a.DB.QueryRow(`
 		SELECT COUNT(*) FROM imdb i
-		LEFT JOIN genre g ON g.imdb_id = i.id
-		WHERE g.id IS NULL
+		LEFT JOIN imdb_genre ig ON ig.imdb_id = i.id
+		WHERE ig.id IS NULL
 	`).Scan(&missingGenres); err != nil {
 		return fmt.Errorf("count genres: %w", err)
 	}