From 1d20ca594c4246a3fcd63c52911b6d56c0aa503e Mon Sep 17 00:00:00 2001 From: dev Date: Wed, 24 Jun 2026 04:40:22 +0200 Subject: 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 --- schema/schema.sql | 50 ++++++++++++++++++++++++++++++++++++++++++++------ src/imdbdata.go | 38 +++++++++++++++++++++++++++++--------- 2 files changed, 73 insertions(+), 15 deletions(-) diff --git a/schema/schema.sql b/schema/schema.sql index b643cc7..9b1e5ab 100644 --- a/schema/schema.sql +++ b/schema/schema.sql @@ -5,19 +5,21 @@ SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; SET NAMES utf8mb4; +DROP TABLE IF EXISTS `genre`; CREATE TABLE `genre` ( `id` int(11) NOT NULL AUTO_INCREMENT, `imdb_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`), KEY `imdb_id` (`imdb_id`), CONSTRAINT `genre_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +DROP TABLE IF EXISTS `imdb`; CREATE TABLE `imdb` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `links_id` int(11) NOT NULL, `imdb_id` varchar(255) NOT NULL, `wiki_article` varchar(255) DEFAULT NULL, `synopsis` text DEFAULT NULL, @@ -31,11 +33,25 @@ CREATE TABLE `imdb` ( `average_rating` decimal(3,1) unsigned DEFAULT NULL, `num_votes` int(11) DEFAULT NULL, PRIMARY KEY (`id`), - KEY `links_id` (`links_id`), - CONSTRAINT `imdb_ibfk_1` FOREIGN KEY (`links_id`) REFERENCES `links` (`id`) + UNIQUE KEY `imdb_id` (`imdb_id`), + UNIQUE KEY `wiki_article` (`wiki_article`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +DROP TABLE IF EXISTS `imdb_genre`; +CREATE TABLE `imdb_genre` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `imdb_id` int(11) NOT NULL, + `genre_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `imdb_id` (`imdb_id`), + KEY `genre_id` (`genre_id`), + CONSTRAINT `imdb_genre_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`), + CONSTRAINT `imdb_genre_ibfk_2` FOREIGN KEY (`genre_id`) REFERENCES `genre` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; + + +DROP TABLE IF EXISTS `links`; CREATE TABLE `links` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), @@ -52,6 +68,20 @@ CREATE TABLE `links` ( ) ENGINE=InnoDB AUTO_INCREMENT=407448 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +DROP TABLE IF EXISTS `links_imdb`; +CREATE TABLE `links_imdb` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `links` int(11) NOT NULL, + `imdb` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `links` (`links`), + KEY `imdb` (`imdb`), + CONSTRAINT `links_imdb_ibfk_1` FOREIGN KEY (`links`) REFERENCES `links` (`id`), + CONSTRAINT `links_imdb_ibfk_2` FOREIGN KEY (`imdb`) REFERENCES `imdb` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; + + +DROP TABLE IF EXISTS `max_item`; CREATE TABLE `max_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `max_story_id` int(11) NOT NULL, @@ -62,18 +92,24 @@ CREATE TABLE `max_item` ( CONSTRAINT `max_item_ibfk_1` FOREIGN KEY (`max_story_id`) REFERENCES `story` (`story_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO `max_item` (`id`, `max_story_id`, `created_at`, `updated_at`) VALUES +(1, 42989962, '2025-02-09 11:30:02', '2025-02-09 11:30:01'); +DROP TABLE IF EXISTS `people`; CREATE TABLE `people` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +DROP TABLE IF EXISTS `profession`; CREATE TABLE `profession` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `profession` (`id`, `name`) VALUES @@ -81,6 +117,7 @@ INSERT INTO `profession` (`id`, `name`) VALUES (2, 'director'), (3, 'screenwriter'); +DROP TABLE IF EXISTS `story`; CREATE TABLE `story` ( `id` int(11) NOT NULL AUTO_INCREMENT, `story_id` int(11) NOT NULL, @@ -98,13 +135,14 @@ CREATE TABLE `story` ( ) ENGINE=InnoDB AUTO_INCREMENT=366974 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +DROP TABLE IF EXISTS `who`; CREATE TABLE `who` ( `id` int(11) NOT NULL AUTO_INCREMENT, `imdb_id` int(11) NOT NULL, `people_id` int(11) NOT NULL, `profession_id` int(11) NOT NULL, PRIMARY KEY (`id`), - KEY `imdb_id` (`imdb_id`), + UNIQUE KEY `imdb_id` (`imdb_id`,`people_id`,`profession_id`), KEY `people_id` (`people_id`), KEY `profession_id` (`profession_id`), CONSTRAINT `who_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`), 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) } -- cgit v1.2.3