NextPVR Forums
  • ______
  • Home
  • New Posts
  • Wiki
  • Members
  • Help
  • Search
  • Register
  • Login
  • Home
  • Wiki
  • Members
  • Help
  • Search
NextPVR Forums Information Community Announcements v
« Previous 1 … 9 10 11 12 13 … 56 Next »
Search DB batch util

 
  • 0 Vote(s) - 0 Average
Search DB batch util
pBS
Offline

Posting Freak

Posts: 4,829
Threads: 182
Joined: Aug 2005
#1
2009-09-02, 02:23 PM (This post was last modified: 2009-09-05, 01:11 AM by pBS.)
Here's a database search util batch file i made...can search title,subtitle,description, with wildcards Smile
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 Big Grin
[dang bullets]

Copy and save as dbinfo.bat or whatever.bat..Big Grin
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]
Hardware: HDHR Prime, HDPVR 1212, Raspberry pi2, VFD display w/LCDSmartie
ElihuRozen
Offline

Senior Member

Massachusetts, USA
Posts: 514
Threads: 51
Joined: Apr 2006
#2
2009-09-02, 06:16 PM
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
Offline

Posting Freak

Posts: 4,829
Threads: 182
Joined: Aug 2005
#3
2009-09-02, 08:44 PM (This post was last modified: 2009-09-02, 09:05 PM by 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...Smile [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..
Hardware: HDHR Prime, HDPVR 1212, Raspberry pi2, VFD display w/LCDSmartie
ElihuRozen
Offline

Senior Member

Massachusetts, USA
Posts: 514
Threads: 51
Joined: Apr 2006
#4
2009-09-02, 11:33 PM
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
Offline

Posting Freak

Posts: 4,829
Threads: 182
Joined: Aug 2005
#5
2009-09-03, 10:01 AM (This post was last modified: 2009-09-03, 10:37 AM by 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...Big Grin
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..Smile
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
Hardware: HDHR Prime, HDPVR 1212, Raspberry pi2, VFD display w/LCDSmartie
« Next Oldest | Next Newest »

Users browsing this thread: 1 Guest(s)



Possibly Related Threads…
Thread Author Replies Views Last Post
  Yet Another RenameRecordings Batch files SANGER_A2 53 28,109 2012-06-16, 06:45 AM
Last Post: SANGER_A2
  Manual RenameRecording Batch File SANGER_A2 0 2,197 2011-10-08, 11:11 AM
Last Post: SANGER_A2
  NPVR Rename Recordings Batch File raceviper13 8 8,042 2011-04-23, 11:52 PM
Last Post: raceviper13
  import single file metadata util pBS 0 1,235 2009-03-26, 07:12 AM
Last Post: pBS
  Batch to surely Hibernate & Update EPG dfdario 1 1,779 2008-03-09, 02:32 PM
Last Post: cakes
  transcode only if disk near full - batch file Koenie 3 2,134 2006-09-27, 07:39 AM
Last Post: Koenie
  Updated show search plug-in posted Jeff 0 949 2006-09-19, 03:12 AM
Last Post: Jeff
  Potential Web Interface Skin and Search Function sleptoor 2 1,869 2005-01-07, 09:34 PM
Last Post: sleptoor

  • View a Printable Version
  • Subscribe to this thread
Forum Jump:

© Designed by D&D, modified by NextPVR - Powered by MyBB

Linear Mode
Threaded Mode