search and replace in entire database

Talk to other Pedia users about the programs, share tricks and tips or ask questions about existing features.
Post Reply
gilbertdh
Bruji Friend
Bruji Friend
Posts: 10
Joined: Sat Jan 23, 2010 1:27 pm

search and replace in entire database

Post by gilbertdh »

Is there a way to do a search / find / replace throughout the entire database? For example, I want to replace every instance of Bjork with Björk no matter what field her name appears in.

Thanks,
David
User avatar
Nora
Site Admin
Posts: 2155
Joined: Sun Jul 04, 2004 5:03 am
Contact:

Re: search and replace in entire database

Post by Nora »

I don't know if there's a way to replace Bjork all across the database at once but here's how you would do it for select fields (I assume there are quite a few fields which would not contain Bjork such as any of the date or number fields so that's quite a few you could leave out already).

First off, quit DVDpedia create a copy of your DVDpedia data folder. By default the folder is located in your Home folder under ~/Library/Application Support/DVDpedia. Then run the application Terminal found in Applications/Utilities and copy paste the following into the window, followed by a return:

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zSummary = replace(zSummary, 'Bjork', 'Björk');
update zEntry set zDirector = replace(zDirector, 'Bjork', 'Björk');
.exit
This would replace Björk for any occurrences of Bjork in the Summary and Director fields. You can of course add more fields, the titles are all pretty self-explanatory. After you're done, type in .exit and quit Terminal. Now when you restart DVDpedia there should be no more Bjork in your collections.
Ted
Addicted to Bruji
Addicted to Bruji
Posts: 94
Joined: Thu Jan 20, 2005 3:30 pm
Location: NYC, NY

Re: search and replace in entire database

Post by Ted »

I guess this would be the best way to replace a disk name in the Links/URL field as in:

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zURL = replace(zSummary, 'WD 2TB Internal 4', 'Macintosh HD 4');

Is that correct?
User avatar
Conor
Top Dog
Posts: 5345
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: search and replace in entire database

Post by Conor »

Almost that would target the URL field for an entry, the links have a separate table called "zlink". So it would be:

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zlink set zURL = replace(zURL, 'WD 2TB Internal 4', 'Macintosh HD 4');
.exit
Ted
Addicted to Bruji
Addicted to Bruji
Posts: 94
Joined: Thu Jan 20, 2005 3:30 pm
Location: NYC, NY

Re: search and replace in entire database

Post by Ted »

For anyone doing this in DVDPedia 5 you need to change the name of the db file in the sqlite script from Database.pediadata to Database.dvdpd
Mier
Inductee
Inductee
Posts: 1
Joined: Sun Feb 05, 2012 10:44 am

Re: search and replace in entire database

Post by Mier »

Actually, I wish there was a simpler way to find/replace something throughout the whole database, because I'm not sure I have understood well these instructions and will be able to do this myself. I think it should become more basic and common feature that would be easier to use. Anyways, thanks for the instrucitons, I'll try them out..
User avatar
Nora
Site Admin
Posts: 2155
Joined: Sun Jul 04, 2004 5:03 am
Contact:

Re: search and replace in entire database

Post by Nora »

Just make sure you make a backup of you Database.dvdpd file before starting, as a backup. Or if you're not comfortable doing it yourself, send us your database file and let us know exactly what it is you want to change. You'll find our email on the Support page.
Canada Mike
Contributor
Contributor
Posts: 7
Joined: Tue Dec 27, 2011 4:43 pm

Re: search and replace in entire database

Post by Canada Mike »

I know it's been a while, but I'd like to add my vote for a simpler S&R function. I'm sure a majority of us keep our movies, dvdperdias and avis, on a separate drive. If you need to move the folder or change the drive, it would be very helpful to do a simple S&R.

Cheers,

Michael
Post Reply