#!/bin/ksh
#
#
# $Log: qrysrv,v $
# Revision 1.5  2008/10/29 15:29:56  mbaker
# changed setting of FIRST=0 due to previous changes - introduced bug
#
# Revision 1.4  2008/10/28 19:28:54  mbaker
# added more comments
#
# Revision 1.3  2008/10/28 16:39:53  mbaker
# changed the qry loop - made it work right with multiple queries
#
# Revision 1.2  2008/10/27 21:39:28  mbaker
# changed tab test in header section
# added OUTFLD to default output print
#
# Revision 1.1  2008/10/27 20:28:37  mbaker
# Initial revision
#
#
#
################################################################################
#DOCBEGIN
#
# A flat file database parser that "learns" the column/field names.
#
# Sort of a database like tool for flat files from spreadsheets or just unix
# flat files.  It does queries via awk to get "selected" data from flat file
#
# It expects that the first line of the flat file IS the HEADER ROW.  (NOTE:
# you can have commented rows, so the first non-commented row is the header.)
#
# Take a TAB (default) delimited file and allow dynamic learning of the fields,
# then allow the user to query these fields
#
# ASSUMPTION: inputfile
#        Must have a header row as the first uncommmented line
#
# LIMITATION: logic AND and OR
#        This does only ANDs with the queries. 
#        If you want ORs, then run this script twice with each query statement(s)
#         and then combine the output and filter with a "sort -u".
#
# KNOWN ISSUE: awk types
#        Strings and numerics should not be treated the same, but are in awk, 
#        Which makes for incorrect output on some compare types
#         equals or not equals: are good for strings and numeric
#         gt,lt,ge,le: are good only for numeric and not strings
#
# KNOWN BUG: in awk delimiter
#        Does NOT check whether the field delimiter is valid for the input file type.
#        If it is not, then it will fail without good errors.
#
# KNOWN BUG: in awk statement
#        If os-ver is 5.10 and have 5.8, a "os-ver:ge:5.10" 
#         will give you 5.10 and 5.8 (it drops the 0 off of .10 as it is not 
#         meaninful matematically - but is in human marketing)
#
# Written by Matt Baker mbaker@computeranddata.com
#
#DOCEND
################################################################################
#

set -o allexport
SITE=customer
SRCDIR=/usr/local
CONFDIR=$SRCDIR/conf
NUMARGS=$#
CLIARGS=$*
set +o allexport

if [[ -f  $CONFDIR/$SITE.env ]]
then
	. $CONFDIR/$SITE.env
else
	#
	# Use these setting if the SITE.env doesn't exist (the SITE.env should
	# have at least these settings if used)
	#

	set -o allexport
	DEBUG=0
	#DEBUG=1
	VERBOSE=0
	#VERBOSE=1
	DELIMITER="\t"

	OPERATORS="eq ne gt lt ge le"
	# hostname output field number
	OUTFLD=1
	QUERY=""

	USER=root
	TMPDIR=/tmp
	BINDIR=$SRCDIR/bin
	CONFDIR=$SRCDIR/conf
	INPUTFILE=$CONFDIR/servers.db
	INTERFILE=$TMPDIR/inter-servers.$$
	TMPFILE=$TMPDIR/servers.$$
	set +o allexport
	
	#--------------------------------------------------------------------------
	fatal () {
    		print "$(date +%Y%m%d_%H:%M) $HOST $0 ERROR:$ERRVAL: $ERRMSG" \
	            	| tee -a $LOGFILE
	
		    	exit $ERRVAL
	}
	#--------------------------------------------------------------------------

fi

if (( DEBUG ))
then
	set -x
fi

##############
setup_hdr () {
	if (( DEBUG ))
	then
		set -x
	fi

	#
	# Change header text into useful/parseable items
	#
	HDR=$(grep -v ^# $INPUTFILE \
		| sed -n 1p \
		| tr '[A-Z]' '[a-z]' )


	if [[ $DELIMITER = : ]]
	then
		HDRFLD=$(print $HDR | awk -F: '{for (i=1;i <= NF;i++) print i":"$i}')

	elif [[ $DELIMITER = , ]]
	then
		HDRFLD=$(print $HDR | awk -F, '{for (i=1;i <= NF;i++) print i":"$i}')

	#
	# Delimiter = space (default for awk) - this is what TAB becomes too - from
	#             the grep from above space and tabs are equal here in this line
	#
	else
		HDRFLD=$(print $HDR | awk '{for (i=1;i <= NF;i++) print i":"$i}')
	fi
}

##############
usage() {

	if (( DEBUG ))
	then
		set -x
	fi

	setup_hdr

#Usage: $0 [-f inputfile] [FIELDNAME:OPER:PATTERN [FIELDNAME:OPER:PATTERN ...] | -help] | -[doc|info]

cat << EOF
Usage: $0 [-d delimiter] [-f inputfile] [FIELDNAME:OPER:PATTERN] | -help] | -[doc|info]

       OPERators can be: 
	          eq (equal to/matching), ne (not equal to/not matching)
	          gt (greater than), lt (less than)
	          ge (greater than or equal to), le (less than or equal to)


       NOTE: The order of fieldnames can/will affect the output, that is,
             one fields exculsion before anothers inclusion may produce
             different output than if they are reversed.

      # Get all Solaris 
      $0 os:eq:solaris

      # Get Solaris only, globals
      $0 os:eq:solaris model:ne:local-zone 

      # Get Solaris, solaris 5.10 (globals and locals)
      $0 os:eq:solaris os-ver:eq:5.10 

      # Get all HP 
      $0 model:eq:hpux

	  -d [char]   If using a tab, you should use: "-d \\t" so "\t" get by the shell.
	              Otherwise, use either colon (:), space (" "), or comma (,)

      The fieldname can be any one of the following:
      ======================================================================
      $HDR
      ======================================================================

      fieldname:eq:PATTERN
      fieldname:lt:value

EOF
}

#
# parse input
#
while (( $# ))
do
	case $1 in

		-debug) DEBUG=1
			set -x
			shift
			;;

		-v) VERBOSE=1
			shift
			;;

		-d) shift
			DELIMITER="$1"
			shift
			;;

		-f) shift
			INPUTFILE=$1
			shift
			;;

		-info | -doc) 
			awk '/DOCBEGIN/, /DOCEND/' $0 \
						| sed -e 's/^#//' \
						| sed -e '/DOCBEGIN/d' \
						| sed -e '/DOCEND/d' \
						|  more
			exit 0
			;;


		#help
		-h*) if [[ $1 != -h ]]
			then
				usage
				ERRVAL=1
				ERRMSG="Help getting syntax/usage.\n"
				fatal
			else
				usage
				exit 1
			fi
			;;

		*) QUERY="$QUERY $1"
			shift
			;;

	esac
done
	
##############
prgmsetup() {

	if (( DEBUG ))
	then
		set -x
	fi

	#if (( ! $NUMARGS ))
	#then
	#	usage
	#	ERRVAL=3
	#	ERRMSG="ERROR: Must provide an argument."
	#	fatal
	#fi

	setup_hdr

	if [[ ! -f $INPUTFILE ]]
	then
		ERRVAL=1
		ERRMSG="ERROR: file $INPUTFILE does not exist."
		fatal
	fi
	
	if [[ ! -s $INPUTFILE ]]
	then
		ERRVAL=2
		ERRMSG="ERROR: file $INPUTFILE is empty."
		fatal
	fi

	#if [[ -z $QUERY ]]
	#then
	#	usage
	#	ERRVAL=7
	#	ERRMSG="Need to have a Query argument.\n"
	#	fatal
	#fi

	if [[ -n $QUERY ]]
	then
		for QRY in $QUERY
		do
			FLD=$(echo $QRY | awk -F: '{print $1}' | tr '[A-Z]' '[a-z]')
			OPER=$(echo $QRY | awk -F: '{print $2}' | tr '[A-Z]' '[a-z]')
	
			if [[ -z $(echo $HDR | grep $FLD) ]]
			then
					usage
					ERRVAL=5
					ERRMSG="Field: $FLD is invalid.\n"
					fatal
			fi
	
			if [[ -z $(echo $OPERATORS | grep $OPER) ]]
			then
				usage
				ERRVAL=6
				ERRMSG="Operator: $OPER is an invalid.\n"
				fatal
			fi
		done
	fi			
}

##############
qry() {
	if (( DEBUG ))
	then
		set -x
	fi

	FIRST=1

	for QRY in $QUERY
	do
		FLD=$(echo $QRY  | awk -F: '{print $1}' | tr '[A-Z]' '[a-z]')
		OPER=$(echo $QRY | awk -F: '{print $2}' | tr '[A-Z]' '[a-z]')
		PAT=$(echo $QRY  | awk -F: '{print $3}' | tr '[A-Z]' '[a-z]')

		HDRFLDNUM=$(print $HDRFLD \
			| tr ' ' '\012' \
			| grep -w $FLD \
			| awk -F: '{print $1}')
		
		if (( FIRST ))
		then
			#
			# No file manipulation needed
			# Set variable so we don't come here again
			#
			:
		else 
			#
			# Second and more passes reach here
			# Set INPUTFILE to be TMPFILE, which is the "original" output
			#  from the FIRST run
			#
			INPUTFILE=$INTERFILE
			cp $TMPFILE $INTERFILE
		fi
		
		#
		# equal "eq", in awk "~"
		#
		# Note: the awk quoting is crazy, especially when mixing in shell
		#  variables.  Don't change - unless you are strong in heart.
		#
		# Get only lines with text (ignore blank lines which is there in Exel).
		# We make all text lower case so we don't have to worry about it later.
		# We get rid of line 1 because that is the header line.
		#
		if [[ $OPER == eq ]]
		then
			ACTION="~"
		elif [[ $OPER == ne ]]
		then
			ACTION="!~"
		elif [[ $OPER == gt ]]
		then
			ACTION=">"
		elif [[ $OPER == ge ]]
		then
			ACTION=">="
		elif [[ $OPER == lt ]]
		then
			ACTION="<"
		elif [[ $OPER == le ]]
		then
			ACTION="<="
		else
			ERRVAL=15
			ERRMSG="ERROR: unknown operator: $OPER"
			fatal
		fi

		if (( FIRST ))
		then
			cat $INPUTFILE \
				| grep -v ^# \
				| sed -e '1d' \
				| tr '[A-Z]' '[a-z]' \
				| awk -F$DELIMITER \
			 	'{if  ( $'$HDRFLDNUM' '$ACTION' /'$PAT'/) print $0}' \
						> $TMPFILE 
		else
			#
			# no delimiter, because we are "piping" using files, no OFS
			#
			cat $INPUTFILE \
				| awk '{if  ( $'$HDRFLDNUM' '$ACTION' /'$PAT'/) print $0}' \
						> $TMPFILE 
		fi

		if (( DEBUG ))
		then
			echo "==============================="
			cat $TMPFILE
			echo "==============================="
		fi

		if (( FIRST ))
		then
			FIRST=0
		fi 

	done
}

##############
output() {
	if (( DEBUG ))
	then
		set -x
	fi

	#
	# Get rid of "unknown" and "unk" patterns (you may have others in your
	# spreadsheet that you don't want to get through).
	# Get rid of blank lines
	# Make it pretty by sorting
	#
	awk '{print $'$OUTFLD'}' $TMPFILE \
				| grep -vw unknown \
				| grep -vw unk \
				| grep -v ^$ \
				| sort

}
	
##############
cleanup() {
	if (( DEBUG ))
	then
		set -x
	fi

	for FILE in $INTERFILE $TMPFILE
	do
		if [[ -f $FILE ]]
		then
			rm $FILE
		fi
	done
}

################################################################################
# Main
#
if (( DEBUG ))
then
	set -x
fi

prgmsetup

if [[ -n $QUERY ]]
then
	qry
else
	#if no query args, just print the first field - no qualification
	cat $INPUTFILE \
		| grep -v ^# \
		| sed -e '1d' \
		| tr '[A-Z]' '[a-z]' \
		| awk -F$DELIMITER '{print $'$OUTFLD'}' \
		| sort > $TMPFILE
fi

output
cleanup