Skip to content

Instantly share code, notes, and snippets.

@njamescouk
Last active March 20, 2017 15:04
Show Gist options
  • Save njamescouk/b7bf308bf2f0af3703c674f3a152526f to your computer and use it in GitHub Desktop.
Save njamescouk/b7bf308bf2f0af3703c674f3a152526f to your computer and use it in GitHub Desktop.
scrape SELECTs from markdown and run them against sqlite database reporting errors
@echo off
goto main
This is a quick hack, should be easy to port
to linux but I'm still looking for a usable
systemd free distro so haven't done it
we rely on
1. SELECT being upper case
2. SELECT prefixed with at least 4 spaces, or ~~~\nSELECT,
to induce <code> tag
3. no embedded semi colons in, for eg comments
4. semi colon not on the same line as it's SELECT
error reporting leaves *a lot* to be desired
but nevertheless quicker than cutting & pasting
sql into db manager
invoke w sthing like for %t in (*.md) do call chkSql.bat %t portal_mammals.sqlite
- NickJ
rem ============usage================
:usage
echo %1 filename database
echo scans filename for SELECT stmts,
echo runs them against database
echo and reports errors.
echo no output implies no errors
goto :eof
rem ============================
rem ============main================
:main
if "%1"=="" call :usage %0 & goto :eof
if "%2"=="" call :usage %0 & goto :eof
if not exist %1 call :usage %0 & goto :eof
if not exist %2 call :usage %0 & goto :eof
rem find selects, run the lot against db, grep error
sed -n "/ SELECT/,/;/p" %1 | sqlite3 %2 | grep -i error
rem N D dodge below gets us 2 lines at a time, we then clean the mess up with sed s... and uniq
rem note sed -r as well
sed -r -n {N;/~~~\s+SELECT/,/;/p;D} %1 | sed s/~~~// | uniq | sqlite3 %2 | grep -i error
rem get hold of one line selects in backticks
egrep -o "`SELECT[^`]*" %1 | sed s/`//g | sqlite3 %2 | grep -i error
goto :eof
rem ============================
@njamescouk
Copy link
Author

njamescouk commented Mar 19, 2017

this fails to extract SQL in solutions.

now extracts one line selects in backticks

@ashander
Copy link

ha! pretty cool

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment