Ability to correlate DVDPedia information with other info

[The Ancient Mariner]

I have quite a few movies and I maintain information about these movies in multiple locations on my computer. For example, DVDPedia contains all the information about a movie, I have several HDD drives that I'm using to copy my movies onto disk in anticipation of playing them wirelessly from my Mac Pro to my HDTV via a Mac Mini (don't ask me why that instead of Apple TV, it's just a personal preference) and I also maintain a folder of DVD art for the disks.

The problem is keeping all the data correlated, e.g., do I have entries for Sabrina in DVDPedia, on the HDDs and the DVD art file? While I'm using one of the Custom fields in DVDPedia I'm finding that I'm not as dogmatic about keeping it up to date as I could/should be.

It would be great if I had a utility that I could run that read all three information sources and gave me a printout on all the titles (in a spreadsheet, for example) that indicated: Sabrina: DVDPedia=Yes, HDD=Yes, DVD Art=Yes, meaning Sabrina exists on all three; or Sabrina: DVDPedia=Yes, HDD=No, DVD Art=Yes; or Sabrina: DVDPedia=No, HDD=No, DVD Art=Yes.

Sort of the classic read, compare and report of three files.

Or, if I've missed an obvious alternative solution, please let me know. At present my DVD collection numbers in the thousands so a manual compare, while possible, is tedious. Thanks!

[Conor]

There are a number of options.

Personally I would link all the files to DVDpedia and that way I could use a smart collection of "any link URL does not contain coverArt or HDD" (two or three separate collections) where you can then quickly track what movies still need linking or actual files. This has the advantage that you can then play the movie from DVDpedia (directly, via the Front Row Plug-ins or remotely) and view the cover art.

There is a hidden debugging "Verify Links" that will verify that all the linked files are present. This is displayed by holding down option key and clicking on the main help menu. The command will create a collection and place any movies where the link file was not reachable. The console log will also display more details about the missing links.

The time consuming part in this setup is adding the original links to each movie.

Since you are looking at automating the report it might be better to use a script (perl or bash) that takes the title of all the movies in DVDpedia and cross references that against the covers and movie files. The script approach would mean full customization and by using direct SQL access you could even modify your DVDpedia database directly with the results (backup your Library/Application Support/DVDpedia/Database.dvdpd file before attempting any changes programmatically).

For example to produce a list of titles to process:

Code: Select all

#/bin/bash
sqlite3 Library/Application\ Support/DVDpedia/Database.dvdpd "select ztitle from zentry;"

Update: fixed #/bin/bash (but this is only a rough outline of code that needs tweaking)
Depending on your folder layouts you might need more information, but then you check for the files. For example if covers are located under "/Hard Drive/CoverArt/Sabrina/cover.jpg" then the following would work:

Code: Select all

#!/bin/bash
$titles = sqlite3 Library/Application\ Support/DVDpedia/Database.dvdpd "select ztitle from zentry;"
for i in $titles; do 
$testfile = "/Hard Drive/CoverArt/$1/cover.jpg"
if [ -f testfile ]
then
echo cover for %i exists!
fi
done

To send the results back into DVDpedia to have a more friendly GUI report that can be access via columns and smart collections you can change the "echo cover for %i exists!" for:

Code: Select all

sqlite3 Library/Application\ Support/DVDpedia/Database.dvdpd "update zentry set zcustom = "YES" where ztitle = %1;"

Although here I would recommend also getting zuid and using that as the where clause, to avoid matching entries that might have the same title (although that might not be the case in your collection). Of course all the above relies on the assumption that there is a structure to the storage of this files that you can infer from the information in DVDpedia.

[sjk]

Code: Select all

#bash

Should be #/bin/bash if you want the first line to be recognized as a shebang instead of a comment.