- March 10, 2006
- 4,434
- 1,897
- Moderator
- #1
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.
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):
Find movie titles that have multiple entries on the same IMDb tt-ID (and show how many duplicates are found):
Find all your movies that lack an imdb ID entry:
Find all your movies that lack a plot summary:
Find all your movies that lack covers:
Find all your movies that lack fanart/backdrop:
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.
- SQLite Database Browser, available at: SQLite Database Browser
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.