DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Example Of Batch File/sqlite Interaction

04.15.2011
| 3465 views |
  • submit to reddit
        REM see usage message for usage
setlocal
set db=x:\xx\xxx\xxxx\spans.db
if "%1"=="start" goto startspan
if "%1"=="complete" goto completespan
if "%1"=="adjust" goto adjust
if "%1"=="all" goto allspan
if "%1"=="today" goto todayspan
if "%1"=="prev" goto prevspan
if "%1"=="abort" goto abortspan
if "%1"=="sql" goto sql
@echo off
echo spans start
echo spans complete { last ^| ^<span_num^> }
echo spans adjust { last ^| ^<span_num^> } { start ^| end } "<datetime>"
echo spans all
echo spans today [-n]
echo spans prev
echo spans abort
echo spans sql "sql..."
goto theend
:startspan
sqlite3 %db% "insert into span (span_type,start) values (1, datetime('now'));"
goto theend
REM
:completespan
if "%2"=="last" goto completelastspan
set spannummatch=%2
goto completecommon
:completelastspan
set spannummatch=(select max(span_num) from span)
REM fall through
:completecommon
sqlite3 %db% "update span set complete=1, end=datetime('now') where span_num = %spannummatch%;"
goto theend
REM
:allspan
sqlite3 %db% "select * from span;"
goto theend
REM
:adjust
if "%2"=="last" goto adjustlastspan
set spannummatch=%2
goto adjustcommon
:adjustlastspan
set spannummatch=(select max(span_num) from span)
REM fall through
:adjustcommon
REM move all params up one so that %3 becomes %2, etc
shift
set newdatetime=%3
set newdatetime=%newdatetime:~1,-1%
sqlite3 %db% "update span set %2=datetime('%newdatetime%') where span_num = %spannummatch%;"
goto theend
REM
:todayspan
set datematch=julianday(date('now'))%2
:todayandprev
sqlite3 %db% "select *, ( julianday(end) - julianday(start)) * 1440 minutes from span where julianday(date(start)) = %datematch%;"
goto theend
REM
:prevspan
set datematch=( select max(julianday(date(start))) from span where julianday(date(start)) != julianday(date('now')) )
goto todayandprev
REM
:abortspan
sqlite3 %db% "delete from span where span_num = (select max(span_num) from span);"
goto theend
REM
:sql
sqlite3 %db% %2
goto theend
:theend