Update Database with sqlite

Any trouble you encounter with the Pedias, here's the place to ask for help.
Post Reply
DriverJC
Bruji Friend
Bruji Friend
Posts: 16
Joined: Mon Aug 02, 2010 9:00 pm

Update Database with sqlite

Post by DriverJC »

I just upgraded to version 5.0 and am liking it so far. I especially like the searching of TVDB for tv show information. That being said, I have a question.

I would like to run an update script in sqlite that would take the entries in Title, Season, and Episode and format it into "#<Season><Episode>: <Title> and put the information in Custom 3.

I need to know how to format the command to select the record and update it.

Any Ideas?
Joel
User avatar
Conor
Top Dog
Posts: 5345
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Update Database with sqlite

Post by Conor »

Hi Joel,

Very thrilled that you are enjoying the new version. For the SQL command it would be the following in terminal:

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zCustom3 = '#' || zSeason || zEpisode || ': '  || zTitle WHERE zCustom3 is null AND zEpisode is not null AND zSeason is not null;
.exit
This code will update the custom3 only if it not empty and only if there is a season and episode. Feel free to change the part after the where to reflect what you would prefer. Also I wasn't sure if you didn't want any type of separator between season and episode. The || is the concatenate command, So to add the brackets as well it would have been:

Code: Select all

update zEntry set zCustom3 = '#<' || zSeason || '><' || zEpisode || '>: <'  || zTitle || '>' WHERE zCustom3 is null AND zEpisode is not null AND zSeason is not null;
DriverJC
Bruji Friend
Bruji Friend
Posts: 16
Joined: Mon Aug 02, 2010 9:00 pm

Re: Update Database with sqlite

Post by DriverJC »

Thank you for the script. It worked great. it Turned Title: Mythbusters; Season: 9; Episode: 19 into #919: Location Location Location. with is what I wanted it to do. Unfortuantely it also changed Title: The Secret Circle; Season: 1; episode: 9 into #19: Balcoin.

After looking at my prior post I see that I didn't specify that I needed Episodes with Single Digit Episode Numbers changed to double digit numbers.

How would I modify the script to do that, so that Title: The Secret Circle; Season: 1; episode: 9; is changed into #109: Balcoin?


Thank you for a great product.
Joel
User avatar
Nora
Site Admin
Posts: 2155
Joined: Sun Jul 04, 2004 5:03 am
Contact:

Re: Update Database with sqlite

Post by Nora »

Code: Select all

update zEntry set zCustom3 = '#' || zSeason || substr('0' || zEpisode, -2) || ': '  || zTitle WHERE zCustom3 is null AND zEpisode is not null AND zSeason is not null;
That's the code you want to use in that case. If you want to re-do all of your Custom3 fields again, take out this section

Code: Select all

zCustom3 is null AND
since that will keep it from overwriting where there's a value otherwise.
DriverJC
Bruji Friend
Bruji Friend
Posts: 16
Joined: Mon Aug 02, 2010 9:00 pm

Re: Update Database with sqlite

Post by DriverJC »

Thank you that worked great. I love the pedia programs you all put out and can't wait until you get a new release of pocket pedia.

Thanks
Joel
Post Reply