Set specific shows to record in a subdirectory?

[dshoup]

Is it possible to edit the gbpvr.db3 data base so that a specific reoccurring recording records in a specific subdirectory? I like to put all my kids shows in a folder called "kids shows". I can do this manually after they record by moving the file then changing the path of the "filename" field accordingly in the RECORDING_SCHEDULE table of the database and then change the "sub_title" field to include the show name and the show's "name" field to be "kids shows" in the PROGRAMME table.

I want to know if I can do something to the record that designates the reoccurring recording to tell it to create a folder for the show in the ..\kids shows\ sub-folder instead of the default show folder. If so, what record needs editing (I see several records in the RECORDING_SCHEDULE table for each reoccurring show title...is it the Status=4 or recording type=5 or something else that identifies the record that controls the reoccurring program) and how would I modify it (i.e., would I change the name to "kids shows\???" or "\kids shows\???" where ??? is the name of the show...or is there some other modification that should be made)? Can I get GBPVR to automatically give the show the "kids shows" title so that it does not appear in the main list but rather all these shows get listed as "episodes" of the "kids shows" program in the recordings view?

[Graham]

Does it help to compare the content of recording-dump.xml with the values in the database?

[dennit]

Is it possible to edit the gbpvr.db3 data base so that a specific reoccurring recording records in a specific subdirectory?

I don't think so. However, you could use postprocessing.bat to do what you want. GBPVR runs it after the recording and %1 is the fully qualified name of the recorded file. You'd need to move the recording, then modify gbpvr.db3 with an SQL statement. There are instructions floating around here on how to do that. It shouldn't be too hard. If this is of interest, and you need help, let me know.

[stustunz]

what i do is just move the files to the folder you want them in the drag and drop the folder onto vidimport
no shagging around changing the data base as vidimport has already done it
you can also run vidimport from a batch file to automatically scan folders

[dshoup]

Thanks for all the help! I looked at vidimport as it appeared the simpler solution, but I don't think it will work as it is going to leave the show name the same (and I do not want that or it would show in the recordings screen by show name...I want all these shows to be listed under a show name).

So, I'm pursuing the postprocess.bat idea...but could use some help. Here is what I have so far:

for /f "tokens=3 delims=\" %%a in (%1) do if "%%a"=="Pippi Longstocking" goto kids:
for /f "tokens=3 delims=\" %%a in (%1) do if "%%a"=="Babar" goto kids:
for /f "tokens=3 delims=\" %%a in (%1) do if "%%a"=="Rescue Heroes" goto kids:

:kids
for /f "tokens=3 delims=\" %%m in (%1) do md "E:\GBPVR\Kids-shows\%%m\"
for /f "tokens=3 delims=\" %%m in (%1) do move "%~p1%~n1.*" "E:\GBPVR\Kids-shows\%%m\"

This will identify the files I want and move them to the new directory . Now I need to accomplish some gbpvr.db3 changes and have no idea how to do this. Here are the steps I'd like to perform:

1. Change the "filename" field in the RECORDING_SCHEDULE table for this record from the old path to the new path.

2. Change the "sub_title" field in the PROGRAMME table for this record to be the old sub title with the show title ("name" field in this table) added to the front with a "-" character between it and the subtitle. (For example if show name is "kidshow" and the subtitle is "episode1" then I want the sub_title field to end up being "Kidshow-episode1")

3. Change the "name" field in the PROGRAMME table to "Kids-shows". (This step is sort of the whole point...I want all my kids shows to show in the Recordings screen under the program name "kids-shows", but I need the actual show name to be a part of the episode name so we can tell what show it really is).

Can someone help me figure out the syntax for these above 3 steps that modify the database (I'm a complete SQL newbie)?

[Graham]

You might need this ...

http://www.sqlite.org/sqlite.html

Scroll to the bottom for stuff about "shell" scripts.

You will need to use SQL UPDATE ...

http://www.w3schools.com/SQl/sql_update.asp

Manipulating the content of the GBPVR database is not without risk blah backups blah

[dennit]

I need to accomplish some gbpvr.db3 changes and have no idea how to do this. Here are the steps I'd like to perform:

1. Change the "filename" field in the RECORDING_SCHEDULE table for this record from the old path to the new path.

2. Change the "sub_title" field in the PROGRAMME table for this record to be the old sub title with the show title ("name" field in this table) added to the front with a "-" character between it and the subtitle. (For example if show name is "kidshow" and the subtitle is "episode1" then I want the sub_title field to end up being "Kidshow-episode1")

3. Change the "name" field in the PROGRAMME table to "Kids-shows". (This step is sort of the whole point...I want all my kids shows to show in the Recordings screen under the program name "kids-shows", but I need the actual show name to be a part of the episode name so we can tell what show it really is).

Can someone help me figure out the syntax for these above 3 steps that modify the database (I'm a complete SQL newbie)?

Here is simple step 1:

Code:

sqlite3.exe "c:\path\to\gbpvr.db3" "UPDATE RECORDING_SCHEDULE SET filename = 'newfilename' WHERE filename = 'original filename';"

You look pretty good with bat files, so I suspect you know how to substitute %1 for original filename and path\to\%~n1 for newfilename, etc.

I gave you that first, since it's easy to see how it works, but don't run it first.

Instead, let's use the same search that found the filename in RECORDING_SCHEDULE to find the programme_oid, then use that in PROGRAMME to do step 2. :

Code:

sqlite3.exe "c:\path\to\gbpvr.db3" "UPDATE PROGRAMME SET sub_title=name||' - '||sub_title WHERE oid in (SELECT programme_oid FROM RECORDING_SCHEDULE WHERE filename='original filename');"

Step 3 is the same (SET name='Kids-shows'). I suspect you can figure out the rest.

[dshoup]

Thanks to both of you. Graham your links were very helpful (I have used w3schools for html info, but did not realize they had sql tutorials...I could get real dangerous now!)

dennit, your code saved me a bunch of work...I was creating a line of sql to read names into variables then trying to use a different line to make changes and so on. I never really did get things working, so I was very appreciative when I saw your post! I'm getting an error that states

SQL error: Unrecognized token: "'E:\gbpvr\1\new"

on each of my 3 sql lines...it is getting hung up on a space in the file name (note the quotes before the E: includes a double quote and a singe quote, the quote after "new" is just a double quote). I'm trying to use a test file called "E:\gbpvr\1\new text document.txt" as if it were a show name. I can not see why it does not handle the space as it is in quotes (the preceding line with the actual command is typed correctly with the file/path in both double and single quotes [both at front and end of the file/path...balanced as they should be]). Can you see what I'm doing wrong? I'm copying part of my bat file below so you can see the syntax I'm using:

for /f "tokens=3 delims=\" %%m in (%1) do set showname=%%m
set dirname=E:\GBPVR\Movies-kids\%showname%\

c:
cd C:\Program Files\devnz\gbpvr\

rem************update GBPVR database to point to new file location
sqlite3.exe "C:\Program Files\devnz\gbpvr\testgbpvr.db3" "UPDATE PROGRAMME SET sub_title=name||' - '||sub_title WHERE oid in (SELECT programme_oid FROM RECORDING_SCHEDULE WHERE filename='%1');"

I've tried changing the single quotes to double quotes. I have also tried the %1 without any quotes. None are getting me past this point.

[dennit]

Can you see what I'm doing wrong?

No, but the combination of Windows, batch files and spaces in filename can be tricky to troubleshoot. My first recommendation would be to not use spaces. New filename can use underscores, while you can locate the file with a regex or LIKE in the WHERE portion of the SQL. Alternatively, one can do this:

Code:

batch file script for sqlite
Echo.
Echo Reset DB Entry...
Echo .echo on                     >  sqlscript.scr
Echo .output stdout               >> sqlscript.scr
Echo UPDATE RECORDING_SCHEDULE    >> sqlscript.scr
Echo    SET filename='%new_fileloc%' >> sqlscript.scr
Echo  WHERE filename='%orig_fileloc%';      >> sqlscript.scr
@ECHO ON
SQLITE3 GBPVR.DB3 < sqlscript.scr
@ECHO OFF

This can sometimes help deal with the spaces issue where a single command line has trouble.

[ElihuRozen]

First, if you use the /x flag with dir, it will show the short file name. If I create a file "x doc.txt", the short file name would be xdoc~1.txt. Since you already know what directory you are using, you would only have to look up the short name once.

Second, if you go to a DOS prompt and run "help for" (without the quotes) the help text discusses the usebackq option. That might be helpful for what you are doing.