Skip to content

Instantly share code, notes, and snippets.

@njamescouk
Created November 25, 2018 20:59
Show Gist options
  • Save njamescouk/1437887e5e534712d837f32a6c0f084e to your computer and use it in GitHub Desktop.
Save njamescouk/1437887e5e534712d837f32a6c0f084e to your computer and use it in GitHub Desktop.
scrape SELECTs from markdown and run them against sqlite database reporting errors - bash version
# This is a quick hack
#
# 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 thingy in *.md ; do chksql.sh $thingy joajArticle.db; done
#
# - NickJ
# ============usage================
function 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
exit
}
# ============================
# ============main================
if [ -z $1 ]; then usage $0 & exit; fi
if [ -z $2 ]; then usage $0 & exit; fi
if [ ! -e $1 ]; then usage $0 & exit; fi
if [ ! -e $2 ]; then usage $0 & exit; fi
echo checking $1...
# find selects, run the lot against db, grep error
sed -n "/ SELECT/,/;/p" $1 | sqlite3 $2 | grep -i ^error
# N D dodge below gets us 2 lines at a time, we then clean the mess up with sed s... and uniq
# note sed -r as well
sed -r -n "{N;/~~~\s+SELECT/,/;/p;D}" $1 | sed s/~~~// | uniq | sqlite3 $2 | grep -i ^error
# get hold of one line selects in backticks
grep -E -o "\`SELECT[^\`]*" $1 | sed s/\`//g | sqlite3 $2 | grep -i ^error
exit
# ============================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment