HowTo: Manually find movie entries with missing info via SQL queries (1 Viewer)

RoChess

Extension Developer
  • Premium Supporter
  • March 10, 2006
    4,434
    1,897
    Tools needed:


    When you use MovingPicture on a large collection, it can be time consuming to find those movies that lack information. The programming team is hard at work to add filtering options inside the plugin itself, but until then you can find out a lot already. The methods explained here can be used while MediaPortal (or Configuration) is running, because we are only 'reading' the information from the database.

    Start the 'SQLite Database Browser' program, and via File -> Open Database (or use CTRL+O or toolbar icon), and copy and paste the location of the database file for Moving Pictures.

    • For XP this is: %AllUsersProfile%\Application Data\Team MediaPortal\MediaPortal\database\movingpictures.db3
    • And on Vista/Win7 this is: %ProgramData%\Team MediaPortal\MediaPortal\database\movingpictures.db3

    Copy and paste the %... string into the text box for "File name:" and click 'Open'. If everything went ok, then you should see a lot of information listed in the main box with the scrollbar. Click on the "Execute SQL" tab above it, and inside the text box labelled "SQL String" copy and paste the following queries to get useful results.


    Find movie titles that have multiple entries on the same title (and show how many duplicates are found):

    • SELECT COUNT(*), title, year FROM movie_info GROUP BY title HAVING COUNT(*) > 1 ORDER BY title

    Find movie titles that have multiple entries on the same IMDb tt-ID (and show how many duplicates are found):

    • SELECT COUNT(*), imdb_id, title, year FROM movie_info GROUP BY imdb_id HAVING COUNT(*) > 1 ORDER BY title;

    Find all your movies that lack an imdb ID entry:

    • Just the total: SELECT COUNT(*) FROM movie_info WHERE imdb_id NOT LIKE 'tt%';
    • List all the titles: SELECT title, year FROM movie_info WHERE imdb_id NOT LIKE 'tt%' ORDER BY title;

    Find all your movies that lack a plot summary:

    • Just the total: SELECT COUNT(*) FROM movie_info WHERE summary = ' ';
    • List all the titles: SELECT title, year FROM movie_info WHERE summary = ' ' ORDER BY title;

    Find all your movies that lack covers:

    • Just the total: SELECT COUNT(*) FROM movie_info WHERE coverfullpath = ' ';
    • List all the titles: SELECT title, year FROM movie_info WHERE coverfullpath = ' ' ORDER BY title;

    Find all your movies that lack fanart/backdrop:

    • Just the total: SELECT COUNT(*) FROM movie_info WHERE backdropfullpath = ' ';
    • List all the titles: SELECT title, year FROM movie_info WHERE backdropfullpath = ' ' ORDER BY title;


    If you want to sort on 'year' instead of 'title', simply adjust the "ORDER BY title;" part into "ORDER BY year;".

    With the results visible, you can then use the Moving Pictures plugin configuration to locate the movies manually and fix the missing information.

    NOTICE: Eventhough we are only 'reading' the database, after the SELECT queries, when you close the SQLite Database Browser, you will get a dialog asking if you want to save the changes, so to be on the safe side be sure to click 'No'.

    Well I hope this is useful for anybody else besides me, because it saves me a lot of time completing my collection.
    If anybody else has useful queries please add them to this thread.

    :D
     

    RoChess

    Extension Developer
  • Premium Supporter
  • March 10, 2006
    4,434
    1,897
    • Thread starter
    • Moderator
    • #3
    Slurm, GUI sorting isn't possible in SQLite Database Browser, so thanks for the suggestion.

    My main reasoning was to have the actual editing be done within the MovingPictures plugin, to remove any risk of problems with the database structure/data, but it sounds like you have had no issues with that.
     

    Slurm

    MP Donator
  • Premium Supporter
  • December 16, 2006
    301
    33
    57
    Graz
    Home Country
    Austria Austria
    Slurm, GUI sorting isn't possible in SQLite Database Browser, so thanks for the suggestion.

    I took a short look at SQLite Database Browser because I wasn't sure if it could do so and I think SQLite Expert is way easier to handle and more comfortable (and it's free as personal edition).

    My main reasoning was to have the actual editing be done within the MovingPictures plugin, to remove any risk of problems with the database structure/data, but it sounds like you have had no issues with that.

    You're absolutly right: The best way is to use the movingpictures configuration for changes and not directly the database, but it seems to work too.

    And here's a very handy query I sometimes use for search and replace:

    update local_media set fullpath = "D:\Movies" || substr(fullpath,length("C:\Movies") +1,999) where fullpath like "C:\Movies%";

    This replaces all occurences of 'C:\Movies' with 'D:\Movies' if you move your movie collection from one drive to another or change drive letters (of course you have to update your import path too, and if you have different media_label and volume_serial for the new path they have to be updated the same way too).

    Another nice query is the following:

    update movie_info set genres = replace(genres, 'Comedy', 'Komoedie');

    I get data for my movies from different sources (imdb, ofdb, ...) and the genres are also different there. This query replaces the english word 'Comedy' with the german word 'Komoedie' for all entries so I have only one entry for the same genre and not two in two different languages.
     

    Users who are viewing this thread

    Top Bottom