Current program details, from batch file!

[Ton]

Well,

In the spirit of keeping this as a list of nice SQL commands, I've done something similar that allows me (from a batch-file) to list recordings in a certain status...

Here's the batch-file:

Code:

"C:\gbpvr\gbpvr\Third Party\sqlite3.exe" -separator "," c:\gbpvr\gbpvr\gbpvr.db3 "SELECT p.name, p.sub_title FROM RECORDING_SCHEDULE AS s inner join PROGRAMME as p on s.programme_oid = p.oid where s.status = %1;"

I've called it "getStatus.bat", and I can call it from the command-line with a parameter, so that when I do "getStatus 5", I get an overview of all conflicting recorings, "getStatus 4" will give me an overview of all Season recordings etc....

Only thing added is that I've added an inner join to the SQL syntax (reduces the need for two seperate queries)....

[pBS]

cool,i never did get the hang of those joins, but that is just what i need..
that will simplify alot of my batches...

glad to see others are playing with it, as it does open up a lot of possibilities with very little coding..

[pBS]

i recently made a batch to "archive" shows to a new location and adjust the database to reflect it, so all info is retained..

Code:

sqlite3.exe -separator "," "%db%" "UPDATE RECORDING_SCHEDULE SET filename = 'z:\newlocation\show1.mpg' where filename like 'show1.mpg';"

and added some batch file copy bits, and a profile to allow 'archiving' from the video library...
archive.bat:

Code:

@echo off
echo starting from %cd%  >archive.log
echo %0 %1 %2 %3 %4 %5 %6 %7 %8 %9  >>archive.log
set db=GBPVR.DB3
set oid=
for /f "usebackq tokens=1,2 delims=," %%v in (`sqlite3 "%db%" "SELECT programme_oid FROM recording_schedule where filename like '%%%~nx1'; " `) do set oid=%%v
echo oid = %oid%  >>archive.log
if "%oid%" EQU "" (
echo.  >>archive.log
echo ******** That file is not in GBPVR database!  >>archive.log
goto :eof
)
echo. >>archive.log
if not exist "%~dp2" md "%~dp2"
copy "%~1" "%~2"
echo copyerrors = %errorlevel%  >>archive.log
if not errorlevel 1 (
echo running sqlite >>archive.log
sqlite3.exe -separator "," "%db%" "UPDATE RECORDING_SCHEDULE SET filename = '%~2%~nx1' where filename like '%~1';"
del "%~f1" 2>NUL
) else (
echo Copy problem!  >>archive.log
)
echo Done.  >>archive.log
ECHO. >>archive.log
ECHO. >>archive.log
goto :eof

profile:

Code:

<Conversion name="Archive to k" exe="archive.bat" cmd=" {SOURCE_FILE} z:\newlocation\archive\ " targetExtension=".avi" />

place archive.bat in gbpvr\third party\ dir and adjust profile in config.xml to reflect your needs..
i'll probably make an installer soon..will make a nice n simple conflict-free archiving solution...
[so you can use multiple drives to store files and offload main rec drive seamlessly!]

Enjoy...

[zehd]

well, here ya go for you adventurous types...[BETA!]
2 batch files, one to save them, one to restore them...simple..
only saves re-occuring recordings, no new pendings will show until you update the guide...
[update it from config/cap source and it will skip download but work for this]
then you'll see the new shows in pending and notice they're adjusted for the new machine..
also, they're really meant for a clean database, as if you have say a recording with 'group 2' reoccuring type already in there, the import will create another one, that also has that group number...so not sure how that would react..
may be able to adjust those as well...[to be unique on new machine..feedback welcome]

i'm using it to transfer between 2 machines that have totally different setups, one is pvr150 and one is software recorder, both with dual inputs and different guide data sources! [one i-xmltv and one built-in zap2it] but both with same channel lineups...both antenna and svideo in on each...
recording kicked off without a hitch, so everything looks good... it knew which card to select..

but Beware and backup your database before using this! there may be weird byproducts of this but it looks great in the database to me...just like any other re-occuring..no loss of info on recordings or anything..
it creates a few temp files to do it's dirty work, but it deletes them before running again so safe to delete if you want..[*.sql]
the files you want to keep to transfer are data2.sql and extras.sql

here they are...also requires sqlite3.exe in current dir..
re-occuring saver:
EDIT: added search term so use: "save.bat dead zone" to export only shows with that in title, or nothing to export all.

Code:

@echo off
set db=\\Pvr\os\program files\devnz\gbpvr\gbpvr.db3
del /q data.sql 2>NUL
del /q data2.sql 2>NUL
del /q extras.sql 2>NUL
set show=
if "%*"=="" (
set show=%%
) else (
set show=%%%*%%
)
echo %show%
:: save reoccurings from recording_schedule to data2.sql
for /f "usebackq tokens=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 delims=," %%a in (`sqlite3 -separator "," "%db%" "SELECT * FROM RECORDING_SCHEDULE where status='4' and recording_type='5'  and filename like '%show%';"`) do echo NULL,'%%b','%%c','%%d','%%e','%%f','%%g','%%h','%%i','%%j','%%k','%%l','%%m' >> data2.sql
if not exist data2.sql goto :bad
:: save recording_extras to extras.sql
for /f "usebackq tokens=1,2,3,4,5 delims=," %%a in (`sqlite3 -separator "," "%db%" "SELECT * FROM reoccuring_extras ;"`) do echo '%%a','%%b','%%c','%%d','%%e' >> extras.sql
:: Adjust channels in data from reoccuring table, and save as data.sql for re-import.
for /f "usebackq tokens=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 delims=," %%a in (data2.sql) do (
    for /f "usebackq tokens=1,2 delims=," %%v in (`sqlite3 "%db%" "SELECT channel_number FROM channel where oid=%%j;"`) do (
echo adjusting oid-%%j  to ch-%%v and exporting %%e
   echo NULL,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,'%%v',%%k,%%l,%%m >> data.sql
   )
)
goto :Eof
:bad
echo Bad Query..Try again

re-occuring importer:

Code:

@echo off
set db=C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3
del /q dataimp.sql

:: Adjust for new channel oid's, and save as dataimp.sql for re-import.
for /f "usebackq tokens=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 delims=," %%a in (data.sql) do (
    for /f "usebackq tokens=1,2 delims=," %%v in (`sqlite3 "%db%" "SELECT oid FROM channel where channel_number=%%j;"`) do (
echo adjusting ch-%%j into new oid-%%v for %%e
   echo NULL,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,'%%v',%%k,%%l,%%m >> dataimp.sql
   )
)

:: import recordings
for /f "tokens=* delims=" %%a in (dataimp.sql) do sqlite3 -separator "," "%db%" "INSERT INTO "RECORDING_SCHEDULE" VALUES (%%a);"

:: import reoccuring_Extras
for /f "tokens=* delims=" %%a in (extras.sql) do sqlite3 -separator "," "%db%" "INSERT INTO "REOCCURING_EXTRAS" VALUES (%%a);"

goto :eof

just change the database location at top of each for your situation...

be carefull as its not fully tested..tho you can easily go into recordings and delete them before updating guide if it goofs..

let me know if any problems..

I stopped GB-PVR Recording service. I exported from my old database, seemed OK... So far, at least I have a list of shows I should reprogram

When I try to input the schedule, Seems OK, though there is an error: SQL error: unrecognized token: "','' );" and then the batch file crashes...

I start the service, start GB-PVR and when I go into recordings, 'Error: specified cast is not valid'

Any ideas?

[pBS]

hmmm,looks like and extra ' in there...[token: "','' );" but not sure if that's all it said...does the data look good in the saved files?
sample of my data:

Code:

NULL,'1415234','2','4','The Daily Show With Jon Stewart','5','31','2006-10-23 22:30:00.0000000','2006-10-24 01:30:00.0000000','107','2','0','1'

[note the placement of ' and , chars]
possibly need a quote around the NULL but i never needed it..[like 'NULL',]
rem out the @echo off and see if any more info..
and maybe can save\redirect the output with >>file.txt at end...

[zehd]

Anyone still reading this thread?

This is what I'm trying to do... I'm trying to query the sqlite database to find if there are back to back shows on the same tuner. If there are, I would cut a chunk of time from the second show and append it to the first show, (and vice versa) just so a person doesn't lose bits of a show when a TV network isn't punctual...

I've already created the mechanism to cut and append/prepend... Now I'm having trouble querying the db. Perhaps it's because I don't understand the way tables reference in SQL lanuage. (I've always used MS Access and was able to use arrows and stuff)

I'm thinking it should go like this:

1. Batch file to receive input filename as passed parameter.
   
2. Lookup the start and end times of that recording
   
3. Lookup tuner number for that recording
   
4. Check if there is any other show that starts immediately after or ends immediately before show on the same tuner
   
5. Output filename of show that qualifies...
   

I was really wanting to do most of this within VB6, but have since given in to the idea of batch files. So I would have the batch file output to text file, and then have my program parse the text file.

I was wondering if someone wanted to help me get started...

Thanks

Frank

[Mister Slimm]

Anyone still reading this thread?

Now I'm having trouble querying the db. Perhaps it's because I don't understand the way tables reference in SQL lanuage. (I've always used MS Access and was able to use arrows and stuff)

1. Yes.
   
2. Try mocking up a similar query in Access and then click the SQL view button to get the SQL equivalent SQL command. The SQL view button is the same one that switches between Design and Data view. Use the little dropdown arrow by the side of that button and you'll see one marked SQL.
   

[zehd]

OK, so now I'm pulling data out of the database. I created a query that gives me one line of text, in a text file, delimited with commas. I can parse through the data to get the fields I want...

But I was wondering, That's a bit of disc access, having the query output to text file, and then parsing through the text file. I was wondering if anyone has a better idea...

I read today that the clip.exe that ships with server 2003 allows the output of a command line to pipe to the clipboard. I also read that one could get a copy of server 2003 clip.exe and overwrite XPs, and it would work fine. It's just that I would then need to include the file in my distro and that wouldn't be cool methinks...

Is there another way for me to work with the data pulled using sqlite3.exe VIRTUALLY, without the need for writing a temp file and parsing it out...

It's not the end of the world, just wondered...

And yes, I'm using VB6. That maybe a limiting factor right there. Sure wish there was a great wrapper for sqlite3...

[pBS]

are you using a batch file for this? i just put the results into a variable and then play with it from there..[using a for loop]

sample from my currently playing .bat file..

Code:

for /f "usebackq tokens=1,2 delims=," %%v in (`sqlite3 "%db%" "SELECT programme_oid FROM recording_schedule where filename like '%%%~nx1'; " `) do sqlite3 "%db%" " SELECT name FROM PROGRAMME where oid='%%v'; "

that's a lot of steps so it's possible but will take several queries to complete...

i would use a join whenever possible to limit no. of accesses..
the data put in variables..

so basically:

Code:

sqlite3.exe -separator ":" "c:\program files\devnz\gbpvr\gbpvr.db3" "SELECT p.name, p.sub_title FROM channel AS c inner join PROGRAMME as p on c.oid = p.channel_oid where start_Time < %timedate% and end_time > %timedate% and c.channel_number = %1;"

sound simple enough? lol
j/k...but basically you just use a for-loop to 'set' a variable with its output, that you can then use elsewhere in your batch..

check the other examples as they show how to use for loop's output for further processing without temp files..
and joins will cut down on the processing needed..but can be quite mind bending..hehe

lots of processing needed tho on the times, as they aren't easy to add and subtract in dos..
i'm thinking autoit would be a good candidate as it can handle the date/time maniplulations and has a sqlite wrapper also..
and it can capture sqlite's output when it runs it....

[zehd]

are you using a batch file for this? i just put the results into a variable and then play with it from there..[using a for loop]

Thanks pbs, but the point is to avoid using batch files. If I was using one, then a batch file variable would be fine. I was hoping there would be a way to do something like view a Batch file variable inside a VB program (that's why I mentioned clipboard)

Anyway... I have written a sort of wrapper, that will query the database, then output to textfile. I then 'split' the entire line into an array using the comma delimiters. I can then use the different fields quite easily. It's quite fast.

I don't like the work around, not all that elegant, but have to say thanks you pbs and the rest for getting be started with sqlite3 access...

I now have my program looking up the start and end times and tuner number, then adjacent recordings before and after, on the same tuner, and the three queries take about a 3rd of a second. That's actually good enough for me...