Search DB batch util

[pBS]

Here's a database search util batch file i made...can search title,subtitle,description, with wildcards
Just polished it up for general consumption, added help, fixed bullets/crlf..

Usage: dbfind.bat [-t -s -d -a] searchterm
Example: dbfind -t house
Returns: shows with exactly "house" as Title. [case insensitive]
Switches: -t=title -s=subtitle -d=description -a=all3:title,subtitle,description

Use %% as wildcard: -t house%% would return all shows that start with "house"..[similar to house*]
or -t %%house%% would return shows with "house" anywhere in the title..
or -a %%house%% would return shows with "house" anywhere in database..
results sorted by date, case insensitive, only shows in future returned..

Must have sqlite3.exe in gbpvr dir and place batch file there too..
Run with no options for full help..

It's quick and thorough..
of course odd foreign characters will trip up the input, but it should handle almost anything..[Dos]
Case Insensitive and formatted date/times output..
Had a bugger of a time with crlf's in epg data but they work now
[dang bullets]

Copy and save as dbinfo.bat or whatever.bat..

Code:

@echo off
cls
setlocal ENABLEDELAYEDEXPANSION
echo.
echo.
set query=
set pm=AM
set hr=
set min=
if "%*"=="" goto :help

set query=%*
set query=%query:"=%

if "%query:"=%" NEQ "%query:-t =%" set query=p.name like '%query:-t =%'
if "%query:"=%" NEQ "%query: -t=%" set query=p.name like '%query: -t=%'
if "%query:"=%" NEQ "%query:-d =%" set query=p.description like '%query:-d =%'
if "%query:"=%" NEQ "%query: -d=%" set query=p.description like '%query: -d=%'
if "%query:"=%" NEQ "%query:-s =%" set query=p.sub_title like '%query:-s =%'
if "%query:"=%" NEQ "%query: -s=%" set query=p.sub_title like '%query: -s=%'
if "%query:"=%" NEQ "%query:-a =%" set query=(p.name like '%query:-a =%' or p.description like '%query:-a =%' or p.sub_title like '%query:-a =%')
if "%query:"=%" NEQ "%query: -a=%" set query=(p.name like '%query: -a=%' or p.description like '%query: -a=%' or p.sub_title like '%query: -a=%')
    
        
echo Searching for show with %query%

for /f "usebackq tokens=1,2,3,4,5,6,* delims=|" %%b in (`sqlite3 -separator "|" gbpvr.db3 "SELECT  strftime('%%m-%%d',p.start_time), strftime('%%H',p.start_time), strftime('%%M',p.start_time), c.display_name, p.name, p.sub_title, p.description FROM programme as p inner join channel as c on p.channel_oid = c.oid where start_time > date('now') and %query% order by p.start_time; " `) do (
if not "%%e" EQU "" (echo. && echo Hit Control-c to cancel or && pause) else (echo %%b )
if not "%%e" EQU "" cls && echo. && echo.
rem fix time, add am/pm
if %%c GTR 11 (set /a hr=%%c-12
        set pm=PM
        ) else (
        set hr=%%c)
if %%c EQU 0 (set hr=12
set pm=AM)
if %%c EQU 12 (set hr=12
set pm=PM)
if not "%%e" EQU "" echo %%b !hr!:%%d!pm! CH-%%e
if not "%%f" EQU "" echo                      [%%f]
if not "%%g" EQU "" if "%%h" EQU "" (echo    "No Sub" && echo. && echo    %%g && echo.) else (echo    "%%g" && echo.)
if not "%%h" EQU "" echo %%h
)
goto :eof



:help
echo.
echo Usage: %0 [-t -s -d -a] searchterm
echo.
echo Example: %~n0 -t house
echo Returns: shows with exactly "house" as Title. [case insensitive]
echo.
echo Switches: -t=title -s=subtitle -d=description -a=all3:title,subtitle,description
echo.
echo  Use %%%% as wildcard: -t house%%%% would return all shows that start with "house"..[similar to house*]
echo  or -t %%%%house%%%% would return shows with "house" anywhere in the title..
echo  or -a %%%%house%%%% would return shows with "house" anywhere in database..
echo  results sorted by date, case insensitive, only shows in future returned..
echo.

sample output with UltraXML enhanced epg:
[first output of query with cmd "Dbfind -t house"]

Code:

09-05 08:00AM CH-105 USA
                             [House]
   "Autopsy-(6302)"

(Sep-20-05) A 9-year-old patient with terminal cancer handles her situation so w
ell that House begins to wonder whether she is really that brave or if it is a m
edical symptom.
-(EP00688359.0025)
   -ñ  Actors: Hugh Laurie, Lisa Edelstein, Robert Sean Leonard
   -ñ  Guests: Sasha Pieterse, Jewel Christian

Hit Control-c to cancel or
Press any key to continue  . . .

enjoy!

[Updated to fix when no subtitle exists, thanx ElihuRozen]

[ElihuRozen]

Thanks for this great utility.

Should this line:

Code:

if not "%%f" EQU "" echo    "%%g" && echo.

be like this:

Code:

if not "%%g" EQU "" echo    "%%g" && echo.

instead?

[pBS]

yea, normally it would be, but i had to pull off some magic to make that part work at all..
are you getting odd output?

yep, you're right, it can be %%g... [really same thing tho]

nice thing about this is, you can have multiple wildcards anywhere in search string..
like: house%%international%% would match 'house hunters international' 'house hunters international * ' and not 'house hunters'..
or all new shows as %% *
or %%arthur%%clarke%% matches arthur c. clarke or arthur clarke or arthur c. clarke * anywhere in text etc etc...
good catch, it took many tries to get right..

[ElihuRozen]

When I searched for a movie that has no subtitle, the description is listed where the subtitle should be, but the ending quotes shows up at the start of the second line of text. It overwrites the character that should be there. So I changed the line to check if %%h exists. If it doesn't, it prints the description with no quotes. if %%h does exist, it prints the subtitle with quotes. What do you think of this:

Code:

if not "%%g" EQU "" if "%%h" EQU "" (echo    %%g&&echo.) else (echo    "%%g"&&echo.)

[pBS]

aha, sometimes it just takes another head in the game...
your logic is what i needed, now i have it putting "No Sub" and keeping formatting same as when there is one...
try this line instead...

Code:

if not "%%g" EQU "" if "%%h" EQU "" (echo    "No Sub" && echo. && echo    %%g && echo.) else (echo    "%%g" && echo.)

and this one up top to be able to use any key:

Code:

if not "%%e" EQU "" (echo. && echo Hit Control-c to cancel && pause) else (echo %%b )

should look same for every one,including no subtitles..
i'll look at adding this logic to descriptions too for when neither is there..
[there always has to be a title..but not sub or desc..]
good deal...was short circuiting my brain...lol