DVDPedia 4 & globally changing linked file paths...

[gregz100]

I recently upgraded to 4.0. I was used to being able to go in and do regex search/replace on the path (primarily the volume - when I moved movies in bulk) of the link file in the XML file of the old database.

Is there anyway to that in 4? I'm pretty comfortable with various tools, so if it requires a bit of hacking I'd give it a try. Any thoughts?

Greg

[Nora]

We are thinking of incorporating a 'Find & Replace' in the programs in the future. But until then you could do the following (unfortunately a bit cumbersome and doesn't work well if you have a lot of separate collections, but maybe it'll work for you):

Export your Library in .dvdpedia format. Then ctrl-click the exported .dvdpedia file and choose 'Show package contents' and open the XML file inside with any good text editor. Then do your changes to the XML, save the file and re-import it into DVDpedia by draggin the file over the DVDpedia icon.

The import will automatically be placed in a library-independent collection so you can check that everything went alright. Once you're satisfied you can select everything in your Library and delete it. Then select the imported collection and include it in the Library via the menu command under the File menu. (That's why this is not a good solution if you have a lot of separate collections since those entries will be affected by the Library deletion.)

As always when doing changes that could affect your database, make sure you have a copy of the DVDpedia data folder before you do this. Just to be safe.

[gregz100]

I think your suggestion is a good one and at least gives me an option. Thanks for your support!

Greg

[Conor]

Hi Greg,

If you're not afraid of the Terminal you can access the database via the sqlite command. Quit DVDpedia and make a copy of Database.pediadata before you start as backup. Then in terminal type:

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.pediadata

You will now have prompt that says sqlite> you can now send any SQL statement to view and modify the database. In your case what you would be interested in is something along the lines of:

Code: Select all

update ZLINK Set ZURL = replace(ZURL, 'file:///MyOldMovieFolder', 'file:///MyNEWMovieFolder');

So find and replace on the new format is still possible, I am not knowledgable enough on SQL to know if regular expressions are possible or how to do them, but the regular find and replace should meet your needs. To close the file do:

Code: Select all

.exit

For other fields it's mostly what you would expect them to be named with a Z at the front, but you can list all the tables with .tables and the entries in a table with .schema TABLENAME

[gregz100]

So does that mean that I should be able to access the database from FileMaker (if I have SQLite ODBC drivers?). I assume that's true. Looks like I'm almost there but it's asking for an ODBC User name and Password. Not sure what it should be, I left it blank, but that didn't work. Any ideas?

Greg

Hi Greg,

If you're not afraid of the Terminal you can access the database via the sqlite command. Quit DVDpedia and make a copy of Database.dvdpd before you start as backup. Then in terminal type:

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd

You will now have prompt that says sqlite> you can now send any SQL statement to view and modify the database. In your case what you would be interested in is something along the lines of:

Code: Select all

update ZLINK Set ZURL = replace(ZURL, 'file:///MyOldMovieFolder', 'file:///MyNEWMovieFolder');

So find and replace on the new format is still possible, I am not knowledgable enough on SQL to know if regular expressions are possible or how to do them, but the regular find and replace should meet your needs. To close the file do:

Code: Select all

.exit

For other fields it's mostly what you would expect them to be named with a Z at the front, but you can list all the tables with .tables and the entries in a table with .schema TABLENAME

[Conor]

Now you're getting even more advanced; try the username and password for your Mac user account that owns the file. I know very little about FileMaker; so I am just guessing.

[gregz100]

Usually I'm accessing MySQL or SQL Server data from FileMaker and in those cases there's a user/password for the SQL access (unrelated to FileMaker or the Mac). I'm not sure if your SQLite database has a user/password or not, I'm assuming based on your comment that it doesn't. So far I've had no luck accessing it from FileMaker, but if I could, that would be extremely cool because I could then use FileMaker to do reporting, global changes, etc.

Thanks for any idead and continued support.

Regards,

Greg

Now you're getting even more advanced; try the username and password for your Mac user account that owns the file. I know very little about FileMaker; so I am just guessing.

[ltcarter47]

Hi,

I bookmarked this thread a while back so I could come back to it when I needed it. Well, my DroboPro will be arriving today (YAY!) so soon I will have all my media consolidated from the many drives it's on right now. I just wanted to check and see if anyone knows if this method will still work with the current version or if there's different (possibly easier?) way now.

Thanks!

[Conor]

The SQL replace will still work. Just be sure to backup your data file before making batch changes, unlike DVDpedia SQL has no undo.

[Ennilyn]

Hi Conor.

I'm using Bookpedia 5.2.1 and was trying to globally change the linked file paths the way you described it earlier, but nothing happens.
As far as I can tell, I can enter the file Database.pediadata via sql, but the update command doesn't update anything.
Has there been a change in the file structure or anything that the commands from above don't work anymore?

Thanks, Melanie

[Conor]

Hi Melanie,

The structure has not changed, but the file name has changed from Database.pediadata to Database.dvdpd. Pediadata is the old version 4 database that would still be on your computer, so there would be no error when opening it. The SQLite command to open the database should now be (I'll also update the original post and others to avoid confusion):

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd

Everything else, the SQL commands, remain the same.

Otherwise if that is not the case, do make sure you have the right find text. You can double check your current URLs with the following code to make sure there would be a match.

Code: Select all

Select ZURL from ZLINK;

It will print out the entire list of URLs but you can look at the last few to make sure that you matching part of it. If it's still not working let me know the paths and email me the Database.dvdpd file and I can look into it.

[jeffbbz]

I realize this post is now 7 years old. But just in case. Is there a way to do this for some but not all files?

I have used this successfully in the past when i moved hard drives. But now I'm spitting my files between two hard drives. I want to keep movies with director last name a-l on the current drive but movie films by directors m-z to a different drive. is there a way to change just the moved files?

[Ennilyn]

The structure has not changed, but the file name has changed from Database.pediadata to Database.dvdpd. Pediadata is the old version 4 database that would still be on your computer, so there would be no error when opening it. The SQLite command to open the database should now be (I'll also update the original post and others to avoid confusion):

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd

Everything else, the SQL commands, remain the same.

Hi Conor.

After almost ten years I need to globally change my linked file paths again. This time with CDpedia, but even when I replace .dvdpd with .cdpd the commands do nothing. Can you help me once again?

Merry Christmas,
Melanie