Improved synchronization speed for Sonarr and Radarr. #2260

2 files changed, 173 insertions, 159 deletions

diff --git a/bazarr/radarr/sync/movies.py b/bazarr/radarr/sync/movies.py
index ba9fc3b30..6741a823f 100644
--- a/bazarr/radarr/sync/movies.py
+++ b/bazarr/radarr/sync/movies.py
@@ -23,6 +23,46 @@ def update_all_movies():
     logging.info('BAZARR All existing movie subtitles indexed from disk.')
 
 
+def get_movie_file_size_from_db(movie_path):
+    try:
+        bazarr_file_size = os.path.getsize(path_mappings.path_replace_movie(movie_path))
+    except OSError:
+        bazarr_file_size = 0
+    return bazarr_file_size
+
+
+# Update movies in DB
+def update_movie(updated_movie, send_event):
+    try:
+        database.execute(
+            update(TableMovies).values(updated_movie)
+            .where(TableMovies.tmdbId == updated_movie['tmdbId']))
+    except IntegrityError as e:
+        logging.error(f"BAZARR cannot update movie {updated_movie['path']} because of {e}")
+    else:
+        store_subtitles_movie(updated_movie['path'],
+                              path_mappings.path_replace_movie(updated_movie['path']))
+
+        if send_event:
+            event_stream(type='movie', action='update', payload=updated_movie['radarrId'])
+
+
+# Insert new movies in DB
+def add_movie(added_movie, send_event):
+    try:
+        database.execute(
+            insert(TableMovies)
+            .values(added_movie))
+    except IntegrityError as e:
+        logging.error(f"BAZARR cannot insert movie {added_movie['path']} because of {e}")
+    else:
+        store_subtitles_movie(added_movie['path'],
+                              path_mappings.path_replace_movie(added_movie['path']))
+
+        if send_event:
+            event_stream(type='movie', action='update', payload=int(added_movie['radarrId']))
+
+
 def update_movies(send_event=True):
     check_radarr_rootfolder()
     logging.debug('BAZARR Starting movie sync from Radarr.')
@@ -49,15 +89,35 @@ def update_movies(send_event=True):
             return
         else:
             # Get current movies in DB
-            current_movies_db = [x.tmdbId for x in
-                                 database.execute(
-                                     select(TableMovies.tmdbId))
-                                 .all()]
-
-            current_movies_radarr = []
-            movies_to_update = []
+            current_movies_id_db = [x.tmdbId for x in
+                                    database.execute(
+                                        select(TableMovies.tmdbId))
+                                    .all()]
+            current_movies_db_kv = [x.items() for x in [y._asdict()['TableMovies'].__dict__ for y in
+                                                        database.execute(
+                                                            select(TableMovies))
+                                                        .all()]]
+
+            current_movies_radarr = [str(movie['tmdbId']) for movie in movies if movie['hasFile'] and
+                                     'movieFile' in movie and
+                                     (movie['movieFile']['size'] > 20480 or
+                                      get_movie_file_size_from_db(movie['movieFile']['path']) > 20480)]
             movies_to_add = []
-            altered_movies = []
+
+            # Remove old movies from DB
+            movies_to_delete = list(set(current_movies_id_db) - set(current_movies_radarr))
+
+            if len(movies_to_delete):
+                try:
+                    removed_movies = database.execute(delete(TableMovies)
+                                                      .where(TableMovies.tmdbId.in_(movies_to_delete))
+                                                      .returning(TableMovies.radarrId))
+                except IntegrityError as e:
+                    logging.error(f"BAZARR cannot delete movies because of {e}")
+                else:
+                    for removed_movie in removed_movies:
+                        if send_event:
+                            event_stream(type='movie', action='delete', payload=removed_movie.radarrId)
 
             # Build new and updated movies
             movies_count = len(movies)
@@ -71,75 +131,26 @@ def update_movies(send_event=True):
 
                 if movie['hasFile'] is True:
                     if 'movieFile' in movie:
-                        try:
-                            bazarr_file_size = \
-                                os.path.getsize(path_mappings.path_replace_movie(movie['movieFile']['path']))
-                        except OSError:
-                            bazarr_file_size = 0
-                        if movie['movieFile']['size'] > 20480 or bazarr_file_size > 20480:
+                        if (movie['movieFile']['size'] > 20480 or
+                                get_movie_file_size_from_db(movie['movieFile']['path']) > 20480):
                             # Add movies in radarr to current movies list
-                            current_movies_radarr.append(str(movie['tmdbId']))
-
-                            if str(movie['tmdbId']) in current_movies_db:
-                                movies_to_update.append(movieParser(movie, action='update',
-                                                                    tags_dict=tagsDict,
-                                                                    movie_default_profile=movie_default_profile,
-                                                                    audio_profiles=audio_profiles))
+                            if str(movie['tmdbId']) in current_movies_id_db:
+                                parsed_movie = movieParser(movie, action='update',
+                                                           tags_dict=tagsDict,
+                                                           movie_default_profile=movie_default_profile,
+                                                           audio_profiles=audio_profiles)
+                                if not any([parsed_movie.items() <= x for x in current_movies_db_kv]):
+                                    update_movie(parsed_movie, send_event)
                             else:
-                                movies_to_add.append(movieParser(movie, action='insert',
-                                                                 tags_dict=tagsDict,
-                                                                 movie_default_profile=movie_default_profile,
-                                                                 audio_profiles=audio_profiles))
+                                parsed_movie = movieParser(movie, action='insert',
+                                                           tags_dict=tagsDict,
+                                                           movie_default_profile=movie_default_profile,
+                                                           audio_profiles=audio_profiles)
+                                add_movie(parsed_movie, send_event)
 
             if send_event:
                 hide_progress(id='movies_progress')
 
-            # Remove old movies from DB
-            removed_movies = list(set(current_movies_db) - set(current_movies_radarr))
-
-            for removed_movie in removed_movies:
-                database.execute(
-                    delete(TableMovies)
-                    .where(TableMovies.tmdbId == removed_movie))
-
-            # Update movies in DB
-            for updated_movie in movies_to_update:
-                if database.execute(
-                        select(TableMovies)
-                        .filter_by(**updated_movie))\
-                        .first():
-                    continue
-                else:
-                    database.execute(
-                        update(TableMovies).values(updated_movie)
-                        .where(TableMovies.tmdbId == updated_movie['tmdbId']))
-
-                    altered_movies.append([updated_movie['tmdbId'],
-                                           updated_movie['path'],
-                                           updated_movie['radarrId'],
-                                           updated_movie['monitored']])
-
-            # Insert new movies in DB
-            for added_movie in movies_to_add:
-                try:
-                    database.execute(
-                        insert(TableMovies)
-                        .values(added_movie))
-                except IntegrityError as e:
-                    logging.error(f"BAZARR cannot update movie {added_movie['path']} because of {e}")
-                    continue
-
-                altered_movies.append([added_movie['tmdbId'],
-                                       added_movie['path'],
-                                       added_movie['radarrId'],
-                                       added_movie['monitored']])
-                if send_event:
-                    event_stream(type='movie', action='update', payload=int(added_movie['radarrId']))
-
-            # Store subtitles for added or modified movies
-            for i, altered_movie in enumerate(altered_movies, 1):
-                store_subtitles_movie(altered_movie[1], path_mappings.path_replace_movie(altered_movie[1]))
-
             logging.debug('BAZARR All movies synced from Radarr into database.')
 
 
@@ -155,13 +166,17 @@ def update_one_movie(movie_id, action, defer_search=False):
     # Remove movie from DB
     if action == 'deleted':
         if existing_movie:
-            database.execute(
-                delete(TableMovies)
-                .where(TableMovies.radarrId == movie_id))
-
-            event_stream(type='movie', action='delete', payload=int(movie_id))
-            logging.debug('BAZARR deleted this movie from the database:{}'.format(path_mappings.path_replace_movie(
-                existing_movie.path)))
+            try:
+                database.execute(
+                    delete(TableMovies)
+                    .where(TableMovies.radarrId == movie_id))
+            except IntegrityError as e:
+                logging.error(f"BAZARR cannot delete movie {path_mappings.path_replace_movie(existing_movie.path)} "
+                              f"because of {e}")
+            else:
+                event_stream(type='movie', action='delete', payload=int(movie_id))
+                logging.debug('BAZARR deleted this movie from the database:{}'.format(path_mappings.path_replace_movie(
+                    existing_movie.path)))
         return
 
     movie_default_enabled = settings.general.getboolean('movie_default_enabled')
@@ -200,25 +215,33 @@ def update_one_movie(movie_id, action, defer_search=False):
 
     # Remove movie from DB
     if not movie and existing_movie:
-        database.execute(
-            delete(TableMovies)
-            .where(TableMovies.radarrId == movie_id))
-
-        event_stream(type='movie', action='delete', payload=int(movie_id))
-        logging.debug('BAZARR deleted this movie from the database:{}'.format(path_mappings.path_replace_movie(
-            existing_movie.path)))
+        try:
+            database.execute(
+                delete(TableMovies)
+                .where(TableMovies.radarrId == movie_id))
+        except IntegrityError as e:
+            logging.error(f"BAZARR cannot delete movie {path_mappings.path_replace_movie(existing_movie.path)} because "
+                          f"of {e}")
+        else:
+            event_stream(type='movie', action='delete', payload=int(movie_id))
+            logging.debug('BAZARR deleted this movie from the database:{}'.format(path_mappings.path_replace_movie(
+                existing_movie.path)))
         return
 
     # Update existing movie in DB
     elif movie and existing_movie:
-        database.execute(
-            update(TableMovies)
-            .values(movie)
-            .where(TableMovies.radarrId == movie['radarrId']))
-
-        event_stream(type='movie', action='update', payload=int(movie_id))
-        logging.debug('BAZARR updated this movie into the database:{}'.format(path_mappings.path_replace_movie(
-            movie['path'])))
+        try:
+            database.execute(
+                update(TableMovies)
+                .values(movie)
+                .where(TableMovies.radarrId == movie['radarrId']))
+        except IntegrityError as e:
+            logging.error(f"BAZARR cannot update movie {path_mappings.path_replace_movie(movie['path'])} because "
+                          f"of {e}")
+        else:
+            event_stream(type='movie', action='update', payload=int(movie_id))
+            logging.debug('BAZARR updated this movie into the database:{}'.format(path_mappings.path_replace_movie(
+                movie['path'])))
 
     # Insert new movie in DB
     elif movie and not existing_movie:
@@ -227,7 +250,8 @@ def update_one_movie(movie_id, action, defer_search=False):
                 insert(TableMovies)
                 .values(movie))
         except IntegrityError as e:
-            logging.error(f"BAZARR cannot insert movie {movie['path']} because of {e}")
+            logging.error(f"BAZARR cannot insert movie {path_mappings.path_replace_movie(movie['path'])} because "
+                          f"of {e}")
         else:
             event_stream(type='movie', action='update', payload=int(movie_id))
             logging.debug('BAZARR inserted this movie into the database:{}'.format(path_mappings.path_replace_movie(
diff --git a/bazarr/sonarr/sync/episodes.py b/bazarr/sonarr/sync/episodes.py
index 485f112bb..d1f3bc98c 100644
--- a/bazarr/sonarr/sync/episodes.py
+++ b/bazarr/sonarr/sync/episodes.py
@@ -28,19 +28,23 @@ def sync_episodes(series_id, send_event=True):
 
     # Get current episodes id in DB
     if series_id:
-        current_episodes_db_list = [row.sonarrEpisodeId for row in
-                                    database.execute(
-                                        select(TableEpisodes.sonarrEpisodeId,
-                                               TableEpisodes.path,
-                                               TableEpisodes.sonarrSeriesId)
-                                        .where(TableEpisodes.sonarrSeriesId == series_id)).all()]
+        current_episodes_id_db_list = [row.sonarrEpisodeId for row in
+                                       database.execute(
+                                           select(TableEpisodes.sonarrEpisodeId,
+                                                  TableEpisodes.path,
+                                                  TableEpisodes.sonarrSeriesId)
+                                           .where(TableEpisodes.sonarrSeriesId == series_id)).all()]
+        current_episodes_db_kv = [x.items() for x in [y._asdict()['TableEpisodes'].__dict__ for y in
+                                                      database.execute(
+                                                          select(TableEpisodes)
+                                                          .where(TableEpisodes.sonarrSeriesId == series_id))
+                                                      .all()]]
     else:
         return
 
     current_episodes_sonarr = []
     episodes_to_update = []
     episodes_to_add = []
-    altered_episodes = []
 
     # Get episodes data for a series from Sonarr
     episodes = get_episodes_from_sonarr_api(url=url_sonarr(), apikey_sonarr=apikey_sonarr,
@@ -70,76 +74,59 @@ def sync_episodes(series_id, send_event=True):
                             current_episodes_sonarr.append(episode['id'])
 
                             # Parse episode data
-                            if episode['id'] in current_episodes_db_list:
-                                episodes_to_update.append(episodeParser(episode))
+                            if episode['id'] in current_episodes_id_db_list:
+                                parsed_episode = episodeParser(episode)
+                                if not any([parsed_episode.items() <= x for x in current_episodes_db_kv]):
+                                    episodes_to_update.append(parsed_episode)
                             else:
                                 episodes_to_add.append(episodeParser(episode))
 
     # Remove old episodes from DB
-    removed_episodes = list(set(current_episodes_db_list) - set(current_episodes_sonarr))
-
-    stmt = select(TableEpisodes.path,
-                  TableEpisodes.sonarrSeriesId,
-                  TableEpisodes.sonarrEpisodeId)
-    for removed_episode in removed_episodes:
-        episode_to_delete = database.execute(stmt.where(TableEpisodes.sonarrEpisodeId == removed_episode)).first()
-        if not episode_to_delete:
-            continue
+    episodes_to_delete = list(set(current_episodes_id_db_list) - set(current_episodes_sonarr))
+
+    if len(episodes_to_delete):
         try:
-            database.execute(
-                delete(TableEpisodes)
-                .where(TableEpisodes.sonarrEpisodeId == removed_episode))
-        except Exception as e:
-            logging.error(f"BAZARR cannot delete episode {episode_to_delete.path} because of {e}")
-            continue
+            removed_episodes = database.execute(delete(TableEpisodes)
+                                                .where(TableEpisodes.sonarrEpisodeId.in_(episodes_to_delete))
+                                                .returning(TableEpisodes.sonarrEpisodeId))
+        except IntegrityError as e:
+            logging.error(f"BAZARR cannot delete episodes because of {e}")
         else:
-            if send_event:
-                event_stream(type='episode', action='delete', payload=episode_to_delete.sonarrEpisodeId)
+            for removed_episode in removed_episodes:
+                if send_event:
+                    event_stream(type='episode', action='delete', payload=removed_episode.sonarrEpisodeId)
 
     # Update existing episodes in DB
-    for updated_episode in episodes_to_update:
-        if database.execute(
-                select(TableEpisodes)
-                .filter_by(**updated_episode))\
-                .first():
-            continue
+    if len(episodes_to_update):
+        try:
+            database.execute(update(TableEpisodes), episodes_to_update)
+        except IntegrityError as e:
+            logging.error(f"BAZARR cannot update episodes because of {e}")
         else:
-            try:
-                database.execute(
-                    update(TableEpisodes)
-                    .values(updated_episode)
-                    .where(TableEpisodes.sonarrEpisodeId == updated_episode['sonarrEpisodeId']))
-            except IntegrityError as e:
-                logging.error(f"BAZARR cannot update episode {updated_episode['path']} because of {e}")
-                continue
-            else:
-                altered_episodes.append([updated_episode['sonarrEpisodeId'],
-                                         updated_episode['path'],
-                                         updated_episode['sonarrSeriesId']])
+            for updated_episode in episodes_to_update:
+                # not using .returning() because it's not supported on executemany() with SQlite
+                store_subtitles(updated_episode['path'], path_mappings.path_replace(updated_episode['path']))
+
                 if send_event:
                     event_stream(type='episode', action='update', payload=updated_episode['sonarrEpisodeId'])
 
     # Insert new episodes in DB
-    for added_episode in episodes_to_add:
+    if len(episodes_to_add):
         try:
-            database.execute(
+            added_episodes = database.execute(
                 insert(TableEpisodes)
-                .values(added_episode))
+                .values(episodes_to_add)
+                .returning(TableEpisodes.sonarrEpisodeId, TableEpisodes.path, TableEpisodes.sonarrSeriesId))
         except IntegrityError as e:
-            logging.error(f"BAZARR cannot insert episode {added_episode['path']} because of {e}")
-            continue
+            logging.error(f"BAZARR cannot insert episodes because of {e}")
         else:
-            altered_episodes.append([added_episode['sonarrEpisodeId'],
-                                     added_episode['path'],
-                                     added_episode['monitored']])
-            if send_event:
-                event_stream(type='episode', payload=added_episode['sonarrEpisodeId'])
+            for added_episode in added_episodes:
+                store_subtitles(added_episode.path, path_mappings.path_replace(added_episode.path))
 
-    # Store subtitles for added or modified episodes
-    for i, altered_episode in enumerate(altered_episodes, 1):
-        store_subtitles(altered_episode[1], path_mappings.path_replace(altered_episode[1]))
+                if send_event:
+                    event_stream(type='episode', payload=added_episode.sonarrEpisodeId)
 
-    logging.debug('BAZARR All episodes synced from Sonarr into database.')
+    logging.debug(f'BAZARR All episodes from series ID {series_id} synced from Sonarr into database.')
 
 
 def sync_one_episode(episode_id, defer_search=False):
@@ -178,13 +165,16 @@ def sync_one_episode(episode_id, defer_search=False):
 
     # Remove episode from DB
     if not episode and existing_episode:
-        database.execute(
-            delete(TableEpisodes)
-            .where(TableEpisodes.sonarrEpisodeId == episode_id))
-
-        event_stream(type='episode', action='delete', payload=int(episode_id))
-        logging.debug('BAZARR deleted this episode from the database:{}'.format(path_mappings.path_replace(
-            existing_episode['path'])))
+        try:
+            database.execute(
+                delete(TableEpisodes)
+                .where(TableEpisodes.sonarrEpisodeId == episode_id))
+        except IntegrityError as e:
+            logging.error(f"BAZARR cannot delete episode {existing_episode.path} because of {e}")
+        else:
+            event_stream(type='episode', action='delete', payload=int(episode_id))
+            logging.debug('BAZARR deleted this episode from the database:{}'.format(path_mappings.path_replace(
+                existing_episode['path'])))
         return
 
     # Update existing episodes in DB