#!/bin/bash
# Wrapper script for those don't want to learn how to construct 
# sqlite queries, or might get confsed about sqlite versions.
# Looks long but is really just one trivia sqlite query

f="comet_subrun_info.sqlite"
w="" # User defined "where"
sr_start_min="" 
sr_stop_min="" 
sr_start_max="" 
sr_stop_max=""
# Columns to list
cols="run, subrun, datetime(tstart,'unixepoch','localtime') as start, datetime(tstop,'unixepoch','localtime') as stop, trigger_priority, beb_active, beam_status, mag_current_min, mag_current_max, comment"


# As of 2011 SQLite version 3 was used to write the database and most
# GNU/Linux distros ship the SQLlite version 3 CLI as the executable
# "sqlite3", so use this as the default
SQLITE=""
SQLITE_LIST="sqlite3 sqlite"
SQLITE_OPTS=""
CHECK_VERSION=""
PROG=${0##*/}
usage="Usage: $PROG [options] 
Write information about COMET subruns from semioffline sqlite
database to std out.

options:
   -i, --in-file  FILE   Use FILE as input sqlite DB.
                         Default is $f
   --start-min    TIME   Only output subruns that start at or after TIME
   --start-max    TIME   Only output subruns that start at or before TIME
   --stop-min     TIME   Only output subruns that stop at or after TIME
   --stop-max     TIME   Only output subruns that stop at or before TIME
   -w, --where    WHERE  SQLlite where clause.
   -s, --sqlite   SQLITE Run SQLITE SQLite CLI executable.
                         Default is the first of: $SQLITE_LIST
                         found in the path.
   -o, --options  OPTS   Pass OPTS to the SQLite CLI executable.
                         Eg: $PROG --options \"-cvs -header\".
   --check-version       Try to check for compatibility between
                         sqlite file and executable versions
   -h, --help            Print this usage message.

The TIME values are of the format accepted by the \`date' command on your
system. 
Eg for 5:23 PM, 10th March 2011, you can use: \"2011-03-10 17:23\"

All times given in the output and read as a command line option are in
the local timezone of your system. To override this please set the TZ
environment variable to the desired value.

Eg. for bourne style shells
export TZ=\"Asia/Tokyo\"

Eg. for broken (aka c style) shells
setenv TZ \"Asia/Tokyo\"

The query ouput includes the following columns:

${cols}
"
while [ "$1" != "" ] ; do
    case "$1" in
	-i | --in-file )
	    f="$2"
	    shift ;;
	--start-min )
	    w="${w} and $(date +%s -d $2)<=tstart"
	    shift ;;
	--start-max )
	    w="${w} and $(date +%s -d $2)>=tstart"
	    shift ;;
	--stop-min )
	    w="${w} and $(date +%s -d $2)<=tstop"
	    shift ;;
	--stop-max )
	    w="${w} and $(date +%s -d $2)>=tstop"
	    shift ;;
	-w | --where )
	    w="${w} and $2"
	    shift ;;
	-s | --sqlite )
	    SQLITE="$2"
	    shift ;;
	-o | --opt )
	    SQLITE_OPTS="$2"
	    shift ;;
	--check-version )
	    CHECK_VERSION=1
	    ;;
	-h | --help )
	    echo "$usage" 2>&1
	    exit 0
	    ;;
	* )
	    echo "$usage" 1>&2
	    exit 1
	    ;;
    esac
    shift
done

########################################################
# Check that sqlite exists
#

if [ "$SQLITE" != "" ] ; then # Explicitly set
    which $SQLITE > /dev/null 2>&1
    if [ "$?" != "0" ] ;then
	echo "ERROR: sqlite executable, $SQLITE not found." 1>&2
	exit 2
    fi
else  # Search from list
    for SQLITE in $SQLITE_LIST ; do
	which $SQLITE > /dev/null 2>&1
	if [ "$?" = "0" ] ; then
	    break 
	fi
	SQLITE=""
    done
    if [ "$SQLITE" = "" ] ;then 
	echo "ERROR: could not find an SQLite CLI among: $SQLITE_LIST" 1>&2
	exit 2
    fi
fi

########################################################
# Check versions...
#
# Versions of SQLite CLI older than the version of SQLite used to
# create the database may not recognise it as a DB file and exit with
# an error message that may scare the user. So, try to check for
# version miss match and give a friendly warning message to give the
# sqlite error message some context.
# 

if [ "$CHECK_VERSION" != "" ] ; then
# Determine the db file sqlite version
    FILE_TYPE=$(file -b $f)
    FT_ARRAY=($FILE_TYPE)
    DBFILE_VERSION=${FT_ARRAY[1]}
    DBFILE_TYPE=${FT_ARRAY[0]}
    DBFILE_MAJ_VER=${DBFILE_VERSION%%\.*}

# Determine the sqlite version
    SQLITE_VERSION=$($SQLITE -version)
    SQLITE_MAJ_VER=${SQLITE_VERSION%%\.*}

    if [ "$DBFILE_TYPE" != "SQLite" ] ; then
	echo "WARNING: system does not recognise $f as an SQLite file." 1>&2
    elif [ ${SQLITE_MAJ_VER} -lt ${DBFILE_MAJ_VER} ] ; then
# Check if the versions appear to be compatible
# (Ie major version of exectable is at least as high as the DB file)
	echo "WARNING: sqlite reports version ${SQLITE_VERSION} but database file" 1>&2
	echo "appears to be version ${DBFILE_VERSION}." 1>&2
	echo "So, sqlite ${DBFILE_MAJ_VER} is probably required." 1>&2
    fi
fi

########################################################
# Now the actual DB query

# Replace the leading " and " with "where "
w="${w/ and /where }"
# Put the query together
q="select ${cols} from subrun_info ${w} order by run, subrun;"
# Run it
$SQLITE $SQLITE_OPTS "$f" "$q"
exit $?