Set specific shows to record in a subdirectory?

[dshoup]

I should probably hate myself for using spaces in my file names...but I'm stuck with it as they are all over the place in my gbpvr.db3 given that I rename files in postprocess.bat. Anyhow, I'm taking it a step at a time and I think I'm getting somewhere using the "like" sql qualifier (I've been able to get the OID queried and saved as a variable name, which I can then use to rename the sub_title...so in theory the rest should just be like falling off a log...but I did not say that out loud ('don't want to jinx it). It will likely be a few days before I can finish working on it (perhaps next weekend), but I'll report back and load the entire thing if I can get it working (or I'll come whining and begging for more help if not). Anyhow thanks for all the help up to now!

[dennit]

I think I'm getting somewhere using the "like" sql qualifier (I've been able to get the OID queried and saved as a variable name, which I can then use to rename the sub_title...so in theory the rest should just be like falling off a log...but I did not say that out loud ('don't want to jinx it).

I'm sure you know, but be careful with using LIKE to avoid false matches.

I recall having some trouble occasionally with spaces in filenames when using the single command line format (like in my first post) that I did not have when using the echo-into-a-script method (as in my last post). Plus, when you echo into the script, you can open the script afterwards and easily see what SQL query was actually created by your batch file, with all of it's percent parameters filled in. So, if you still have trouble, I recommend that method.

[dshoup]

Well, I think I have everything working so I though I would post my batch file for anyone else who might be interested (see below). Many thanks to Dennit, Graham, and ElihuRozen for your help on ideas to make this work and debugging!

The problem I had with spaces in the file name was actually one of those “Doh!” moments (and had nothing to do with spaces!). I was using ‘%1’ as the token for the file name in my SQL search string…%1 produces the file name with double quotes around it. Therefore I was creating a string that had single quotes as I specified (and as needed for the SQL syntax), then double quotes inside this…so it was looking for the file name to be in double quotes in the database. Changing this to ‘%~1’ returned just the file name (no quotes), which then is put in the single quotes I had around the %~1 part. This allowed me to use an exact match for the file name instead of the “like” function that could lead to a false match.

For those not familiar with batch files, I’ll explain what this does. My goal was to have all my kid’s shows listed in a single folder called “Movies-Kids” in the GBPVR Recordings screen. The batch first identifies the shows that should be moved this way (I have an example of how to specify a specific show title and a second example that shows how to send everything recorded from a given tv channel). It then looks up the OID code from the GBPVR database. Once this code is identified (I’ve used an if statement to abort the process if no code is found as it will only screw things up if it continues), the file is moved to the new destination (directory is created if necessary). I’ve set it up so the shows stay in subdirectories named by the show title. Unfortunately, GBPVR does not nest folders in the Recordings screen, so these are of no use with respect to the GBPVR interface, but it seems tidier than dumping them all together (and can help if you want to use a jpg file to provide an icon for the show…it still shows the folder.jpg file in this subdirectory when you highlight the show in the Recordings screen if you have Recordings skin configured to do so). Assuming the file move was successful (again, and if statement aborts the process if this is not true) the batch file then runs SQL queries to update the file location in the GBPVR database so it can find the file you just moved. The batch file also copies the show’s title to the beginning of the description, and renames the show title to be “Movies-Kids”. If you do not give the show a new title, it will continue to show up in the old show name folder in the Recordings screen even thought the file was moved (that could be useful as a way to clean up hard disk space…but many other plugins/utilities have an “archive” function that accomplishes this).

I’ve included some logging in a file called e:\transcoded\kids-show.log. You could change this to any file name/path you would like (it is just a text file)…you could even omit the “>>e:\transcoded\kids-show.log” statements throughout the program if you want the program to run with no logging (but I find things that are working well have a way of not working all of a sudden some times…nice to be able to see what the program is creating as file/path names so you can see what went wrong).

Anyway, I hope someone else out there can find this useful. I’m definitely not much of a programmer, so forgive me if the code is inefficient in areas. I’m also not sure I can provide much in the way of support (but would try if you like), but there are many talented programmers on the forum who are very helpful as you can see from this thread.

Code:

Rem *********Specify the shows to be moved to “Movies-Kids” subfolder
for /f "tokens=3 delims=\" %%a in (%1) do if "%%a"=="Puppet Parade" goto Movies-kids:
if "%2" == "62-2" goto Movies-kids:

:Movies-kids
rem **********Determine name of show to use for subdirectory name***********
     echo     . >> "e:\transcoded\kids-show.log"
     echo Began processing %1 from channel %2 (size=%~z1) at %TIME% %DATE% >> "e:\transcoded\kids-show.log"
for /f "tokens=3 delims=\" %%m in (%1) do set showname=%%m
set dirname=E:\GBPVR\Movies-kids\%showname%\

rem **********Get programme_OID from gbpvr.db3******************************
c:
cd C:\Program Files\devnz\gbpvr\
for /f "usebackq tokens=1,2 delims=," %%v in (`sqlite3 "gbpvr.db3" "SELECT programme_oid FROM recording_schedule where filename='%~1'; " `) do set oid=%%v
if "%oid%" EQU "" (
    echo      !!!!!!!!! Could not find '%1' in GBPVR database>> "e:\transcoded\kids-show.log"
    goto eof
    ) else (echo      sqlite3 returned oid - %oid% for '%~nx1'>>"e:\transcoded\kids-show.log" )

rem ************create directory if needed and move file********************
if not exist "%dirname%" md "%dirname%"
command.com /c rem
move "%~d1%~sp1%~sn1.*" "%dirname%"
if not exist "%dirname%%~nx1" (
    echo      !!!!!!!!!! failed to move "%~d1%~sp1%~sn1.*" to "%dirname%"
    goto eof
    ) else (echo    "%~d1%~sp1%~sn1.*" was moved to "%dirname%" >> "e:\transcoded\kids-show.log")
    
rem ************update GBPVR database to point to new file location*********
sqlite3.exe "C:\Program Files\devnz\gbpvr\gbpvr.db3" "UPDATE PROGRAMME SET sub_title=name||' - '||sub_title WHERE oid=%oid%;"
sqlite3.exe "C:\Program Files\devnz\gbpvr\gbpvr.db3" "UPDATE PROGRAMME SET name='Movies-kids' WHERE oid=%oid%;"
sqlite3.exe "C:\Program Files\devnz\gbpvr\gbpvr.db3" "UPDATE RECORDING_SCHEDULE SET filename ='%dirname%%~nx1' WHERE programme_oid=%oid%;"

goto eof

:eof

[dennit]

I was using ‘%1’ as the token for the file name in my SQL search string…%1 produces the file name with double quotes around it. Therefore I was creating a string that had single quotes as I specified (and as needed for the SQL syntax), then double quotes inside this…so it was looking for the file name to be in double quotes in the database.

I'm glad you got it working. One of the reasons I occasionally use the echo-into-a-script method is to catch problems like this. It lets you see exactly what the SQL query is.

[dshoup]

The "echo to script" was actually how I caught the problem. I had never seen that technique before. You can bet I will use it again in the future...neat trick.