home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 1
MediaPortal 1 Plugins
Popular Plugins
Moving Pictures
HowTo: Manually find movie entries with missing info via SQL queries
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="RoChess" data-source="post: 548394" data-attributes="member: 18896"><p>Tools needed:</p><p></p><ul> <li data-xf-list-type="ul">SQLite Database Browser, available at: <a href="http://sqlitebrowser.sourceforge.net/" target="_blank">SQLite Database Browser</a></li> </ul><p></p><p>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.</p><p></p><p>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.</p><p></p><ul> <li data-xf-list-type="ul">For XP this is: <strong>%AllUsersProfile%\Application Data\Team MediaPortal\MediaPortal\database\movingpictures.db3</strong></li> <li data-xf-list-type="ul">And on Vista/Win7 this is: <strong>%ProgramData%\Team MediaPortal\MediaPortal\database\movingpictures.db3</strong></li> </ul><p></p><p>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.</p><p></p><p></p><p>Find movie titles that have multiple entries on the same title (and show how many duplicates are found):</p><p></p><p> <ul> <li data-xf-list-type="ul"><strong>SELECT COUNT(*), title, year FROM movie_info GROUP BY title HAVING COUNT(*) > 1 ORDER BY title</strong></li> </ul><p></p><p>Find movie titles that have multiple entries on the same IMDb tt-ID (and show how many duplicates are found):</p><p></p><p> <ul> <li data-xf-list-type="ul"><strong>SELECT COUNT(*), imdb_id, title, year FROM movie_info GROUP BY imdb_id HAVING COUNT(*) > 1 ORDER BY title;</strong></li> </ul><p></p><p>Find all your movies that lack an imdb ID entry:</p><p></p><p> <ul> <li data-xf-list-type="ul">Just the total: <strong>SELECT COUNT(*) FROM movie_info WHERE imdb_id NOT LIKE 'tt%';</strong><br /> </li> <li data-xf-list-type="ul">List all the titles: <strong>SELECT title, year FROM movie_info WHERE imdb_id NOT LIKE 'tt%' ORDER BY title;</strong></li> </ul><p></p><p>Find all your movies that lack a plot summary:</p><p></p><p> <ul> <li data-xf-list-type="ul">Just the total: <strong>SELECT COUNT(*) FROM movie_info WHERE summary = ' ';</strong><br /> </li> <li data-xf-list-type="ul">List all the titles: <strong>SELECT title, year FROM movie_info WHERE summary = ' ' ORDER BY title;</strong></li> </ul><p></p><p>Find all your movies that lack covers:</p><p></p><p> <ul> <li data-xf-list-type="ul">Just the total: <strong>SELECT COUNT(*) FROM movie_info WHERE coverfullpath = ' ';</strong><br /> </li> <li data-xf-list-type="ul">List all the titles: <strong>SELECT title, year FROM movie_info WHERE coverfullpath = ' ' ORDER BY title;</strong></li> </ul><p></p><p>Find all your movies that lack fanart/backdrop:</p><p></p><p> <ul> <li data-xf-list-type="ul">Just the total: <strong>SELECT COUNT(*) FROM movie_info WHERE backdropfullpath = ' ';</strong><br /> </li> <li data-xf-list-type="ul">List all the titles: <strong>SELECT title, year FROM movie_info WHERE backdropfullpath = ' ' ORDER BY title;</strong></li> </ul><p></p><p></p><p>If you want to sort on 'year' instead of 'title', simply adjust the "ORDER BY title;" part into "ORDER BY year;".</p><p></p><p>With the results visible, you can then use the Moving Pictures plugin configuration to locate the movies manually and fix the missing information.</p><p></p><p><strong>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'.</strong></p><p></p><p>Well I hope this is useful for anybody else besides me, because it saves me a lot of time completing my collection.</p><p>If anybody else has useful queries please add them to this thread.</p><p></p><p><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin :D" loading="lazy" data-shortname=":D" /></p></blockquote><p></p>
[QUOTE="RoChess, post: 548394, member: 18896"] Tools needed: [list][*]SQLite Database Browser, available at: [url=http://sqlitebrowser.sourceforge.net/]SQLite Database Browser[/url][/list] 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. [list][*]For XP this is: [b]%AllUsersProfile%\Application Data\Team MediaPortal\MediaPortal\database\movingpictures.db3[/b] [*]And on Vista/Win7 this is: [b]%ProgramData%\Team MediaPortal\MediaPortal\database\movingpictures.db3[/b][/list] 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): [list][*][b]SELECT COUNT(*), title, year FROM movie_info GROUP BY title HAVING COUNT(*) > 1 ORDER BY title[/b][/list] Find movie titles that have multiple entries on the same IMDb tt-ID (and show how many duplicates are found): [list][*][b]SELECT COUNT(*), imdb_id, title, year FROM movie_info GROUP BY imdb_id HAVING COUNT(*) > 1 ORDER BY title;[/b][/list] Find all your movies that lack an imdb ID entry: [list][*]Just the total: [b]SELECT COUNT(*) FROM movie_info WHERE imdb_id NOT LIKE 'tt%';[/b] [*]List all the titles: [b]SELECT title, year FROM movie_info WHERE imdb_id NOT LIKE 'tt%' ORDER BY title;[/b][/list] Find all your movies that lack a plot summary: [list][*]Just the total: [b]SELECT COUNT(*) FROM movie_info WHERE summary = ' ';[/b] [*]List all the titles: [b]SELECT title, year FROM movie_info WHERE summary = ' ' ORDER BY title;[/b][/list] Find all your movies that lack covers: [list][*]Just the total: [b]SELECT COUNT(*) FROM movie_info WHERE coverfullpath = ' ';[/b] [*]List all the titles: [b]SELECT title, year FROM movie_info WHERE coverfullpath = ' ' ORDER BY title;[/b][/list] Find all your movies that lack fanart/backdrop: [list][*]Just the total: [b]SELECT COUNT(*) FROM movie_info WHERE backdropfullpath = ' ';[/b] [*]List all the titles: [b]SELECT title, year FROM movie_info WHERE backdropfullpath = ' ' ORDER BY title;[/b][/list] 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. [b]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'.[/b] 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. :thx: [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 1
MediaPortal 1 Plugins
Popular Plugins
Moving Pictures
HowTo: Manually find movie entries with missing info via SQL queries
Contact us
RSS
Top
Bottom
