Help with SQL query for querying recordings in NPVR.db3

[b00sfuk]

Any SQL gurus out there? I need a bit of help please in completing an SQL query to retrieve the next pending & in-progress recordings from the new schema in the NPVR.db3 database.
Using GBPVRWAKE, postprocessing.bat & parallelprocessing.bat I pipe these into a text file so I can use LCDSmartie to display them on my iMON VFD. This means theat I can easily see if anything is recording or about to record and for example I don't then put the PC into standby if there is. Also when the PC is in standby it displays the time the PC will wake for its next recording. With GBPVR this function was provided by the GBPVRCli program (provided as part of the SlimmGBPVR utility); the auther has confirmed this is not planned to be updated for NPVR.

I've got this far modifying SQL that I've plagiarised from the forum (thanks pBS) which runs via the SQLITE3 command line utility .

Code:

REM pending 3 recordings
"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\PendName.txt"

"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',start_time) from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\PendTime.txt"

Rem IN-PROGRESS recordings (for upto three tuners)
"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\ProgName.txt"

"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT strftime('%%H:%%M',end_time) from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\ProgTime.txt"

REM next pending recording for USBPORT file for display during standby
"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" > "C:\UTILS\LCDsmartie\USBPortExit.txt"

"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',start_time) from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" >> "C:\UTILS\LCDsmartie\USBPortExit.txt"

What I need a bit of help with please is:

1. The times returned for start/end time are an hour early. I assume this is as actual time (in UK) is currently BST but it is reporting GMT (or UMT).

2. I can get 'name', 'start_time' & 'end_time' but 'subtitle' is now only kept within an XML subfields structure in the 'event_details' field shown below. I can't see an easy way to get at just this.

Code:

<Event>
  <OID>752285</OID>
  <Title>Match of the Day</Title>
  <SubTitle>World Cup 2010</SubTitle>
  <Description>Colin Murray presents highlights of the day&apos;s three group matches, with all the reaction from England&apos;s clash with Algeria in Cape Town. The day&apos;s other Group C game sees the USA take on Slovenia in Johannesburg, while Germany meet Serbia in Group D.</Description>
  <ChannelOID>6864</ChannelOID>
  <StartTime>2010-06-18T21:00:00.0000000</StartTime>
  <EndTime>2010-06-18T21:30:00.0000000</EndTime>
</Event>

Thanks

[martint123]

Subscribing to this thread in the hope of stealing your code to display same on my Joggler Snag is my sql is non-existent. sorry.

[forzaKGB]

If you don't mind using ssed you might do this:
ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" {XML-string or file-name}

[b00sfuk]

If you don't mind using ssed you might do this:
ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" {XML-string or file-name}

Ideally I'd like to keep it clean in the SQL but this is certainly an option to grab the whole record and then post-process the text file. Thanks.

[forzaKGB]

Would something like this help?

"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT event_details from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" | ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" > "C:\Users\Public\NPVR\EXTRAS\SubTitle.txt"

[b00sfuk]

Would something like this help?

"sqlite3.exe" -separator " -- " "npvr.db3" "SELECT event_details from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" | ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" > "C:\Users\Public\NPVR\EXTRAS\SubTitle.txt"

Thanks, I'll probably end up using this but being a bit of a minimalist I'd prefer to do it in one tool rather than two!

Still need to find a solution to the data problem which will need SQL I think.

[b00sfuk]

Think I solved this now with the help (thanks again to forzaKGB & pBS). I can now extract the details to text files of the next pending recordings and any in-progress ones. Times are local time and I can get at the XML subfields now. Details below in case it is of use to anybody.

Using the SSED utility mentioned above and the SQLITE3 command line utility (http://www.sqlite.org/download.html). This could be scheduled to run every few minutes, run in each of the NPVR batch files plus at wake/startup (like I do) or maybe called directly from within VFD/LCD software apps.

Code:

REM PENDING NEXT 3 RECORDINGS
REM Name
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\PendName.txt"

REM Subtitle
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT event_details from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" | C:\Users\Public\NPVR\EXTRAS\ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" > "C:\Users\Public\NPVR\EXTRAS\PendSubT.txt"

REM Start Time
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',datetime(start_time,'localtime')) from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\PendTime.txt"


Rem IN-PROGRESS RECORDINGS (upto 3 to cover all tuners)
REM Name
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;"  "C:\Users\Public\NPVR\EXTRAS\ProgName.txt"

REM Subtitle
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT event_details from SCHEDULED_RECORDING where status = '1' order by start_time imit 3;" | C:\Users\Public\NPVR\EXTRAS\ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" > "C:\Users\Public\NPVR\EXTRAS\ProgSubT.txt"

REM End Time
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT strftime('%%H:%%M',datetime(end_time,'localtime')) from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" > "C:\Users\Public\NPVR\EXTRAS\ProgTime.txt"


REM PENDING NEXT 1 RECORDING for USBPORT
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" > "C:\UTILS\LCDsmartie\USBPortExit.txt"

REM Name
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',start_time) from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" >> "C:\UTILS\LCDsmartie\USBPortExit.txt"

REM Start Time
"C:\Users\Public\NPVR\EXTRAS\sqlite3.exe" -separator " -- " "C:\Users\Public\NPVR\npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',datetime(start_time,'localtime')) from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" >> "C:\UTILS\LCDsmartie\USBPortExit.txt"

[b00sfuk]

Tidied up version & duplicated line removed. Could probably reduce the number of statements but this was easiest way to get what I wanted without learning more complex SQL. Needed each field on a new line for selection in LCDSmartie so it was easiest to run multiple times to output to separate files. I.E. using the LCDSmartie command $File(C:\Users\Public\NPVR\EXTRAS\PendName.txt,1) to show the 1st line of the file.

Code:

SET NDIR=C:\Users\Public\NPVR
SET LCDDIR=C:\UTILS\LCDsmartie

REM PENDING NEXT 3 RECORDINGS
REM Name
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" > "%NDIR%\EXTRAS\PendName.txt"

REM Subtitle
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT event_details from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" | %NDIR%\EXTRAS\ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" > "%NDIR%\EXTRAS\PendSubT.txt"

REM Start Time
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',datetime(start_time,'localtime')) from SCHEDULED_RECORDING where status = '0' order by start_time limit 3;" > "%NDIR%\EXTRAS\PendTime.txt"


Rem IN-PROGRESS RECORDINGS (upto 3 to cover all tuners)
REM Name
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" > "%NDIR%\EXTRAS\ProgName.txt"

REM Subtitle
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT event_details from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" | %NDIR%\EXTRAS\ssed -n -e "s/.*<SubTitle>\(.*\)<\/SubTitle>.*/\1/p" > "%NDIR%\EXTRAS\ProgSubT.txt"

REM End Time
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT strftime('%%H:%%M',datetime(start_time,'localtime')) from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" > "%NDIR%\EXTRAS\ProgTime.txt"


REM PENDING NEXT 1 RECORDING for USBPORT FILE TO DISPLAY IN STANDBY USING LCDSMARTIE 5.3.2
REM Name
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT name from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" > "%LCDDIR%\USBPortExit.txt"

REM Start Time
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT strftime('%%d-%%m %%H:%%M',datetime(start_time,'localtime')) from SCHEDULED_RECORDING where status = '0' order by start_time limit 1;" >> "%LCDDIR%\USBPortExit.txt"

[mikaelgu]

Excellent code! Thank you so much, you saved me hours upon hours of hard labour, blood, sweat and probably tears aswell (not mine, the wife´s ).

[b00sfuk]

Glad it helped. Forget to update this thread but there is an error in one line of the final revision where it should be end-time not start time for in-progress recordings.

Code:

REM End Time
"%NDIR%\EXTRAS\sqlite3.exe" "%NDIR%\npvr.db3" "SELECT strftime('%%H:%%M',datetime([B]end_time[/B],'localtime')) from SCHEDULED_RECORDING where status = '1' order by start_time limit 3;" > "%NDIR%\EXTRAS\ProgTime.txt"