search and replace in entire database

Talk to other Pedia users about the programs, share tricks and tips or ask questions about existing features.

search and replace in entire database

Postby gilbertdh » Sun Jan 16, 2011 11:26 pm

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
gilbertdh
Bruji Friend
Bruji Friend
 
Posts: 10
Joined: Sat Jan 23, 2010 1:27 pm

Re: search and replace in entire database

Postby Nora » Mon Jan 17, 2011 5:06 am

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.
User avatar
Nora
Site Admin
 
Posts: 2155
Joined: Sun Jul 04, 2004 5:03 am

Re: search and replace in entire database

Postby Ted » Fri Mar 04, 2011 8:29 am

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?
Ted
Addicted to Bruji
Addicted to Bruji
 
Posts: 89
Joined: Thu Jan 20, 2005 3:30 pm
Location: NYC, NY

Re: search and replace in entire database

Postby Conor » Sat Mar 05, 2011 6:41 pm

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
User avatar
Conor
Top Dog
 
Posts: 5266
Joined: Sat Jul 03, 2004 12:58 pm

Re: search and replace in entire database

Postby Ted » Sat Jan 21, 2012 9:53 am

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
Ted
Addicted to Bruji
Addicted to Bruji
 
Posts: 89
Joined: Thu Jan 20, 2005 3:30 pm
Location: NYC, NY

Re: search and replace in entire database

Postby Mier » Sat Feb 11, 2012 10:34 pm

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..
Mier
Inductee
Inductee
 
Posts: 1
Joined: Sun Feb 05, 2012 10:44 am

Re: search and replace in entire database

Postby Nora » Sun Feb 12, 2012 3:51 am

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.
User avatar
Nora
Site Admin
 
Posts: 2155
Joined: Sun Jul 04, 2004 5:03 am

Re: search and replace in entire database

Postby Canada Mike » Sun Sep 30, 2012 2:29 pm

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
Canada Mike
Contributor
Contributor
 
Posts: 7
Joined: Tue Dec 27, 2011 4:43 pm


Return to PediaTalk