Update Database with sqlite

[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

[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]

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

[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]

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