gen_sql

#! /usr/local/bin/bash

# let the checkutils.sh script know we are running offline
declare -i offline=1

source checkutils.sh


checkdrift_main() {
    declare -i countsame=1 filecount=0 hostcount=0 
    declare -i tellNoSN=0 tellNonMFS=0; 
    declare print_blank=no
    for host in $allnodes ; do
	print_blank=yes
	((hostcount++))
	if [ -d $projdir/$host/.tbdb ] ; then
	    cd $projdir/$host/.tbdb
	    allchecks=$(ls | sort -r | paste -s -d \\\  -) # paste command used to convert into space seperated words.
	    allchecks+=' ' # grrr my string matching, expects space at end

	    # set newest file and take off list
	    newest=${allchecks%%\ *}
	    allchecks=${allchecks#* } # pop the list
	    [[ ! $allchecks ]] && continue # ran out of files for this node

	    #only compare inventory computed in mfsmode=1
            # from the top of list skip file that have mfsmode=0
	    while (grep -c 'ismfs=0' $newest &> /dev/null ) ; do
		newest=${allchecks%%\ *} # take off the top
		allchecks=${allchecks#* } # pop the list
		[[ ! $allchecks ]] && break # ran out of files, break from loop
	    done
	    [[ ! $allchecks ]] && continue # make sure we didn't consume all the file for node
	    if (( $tellNoSN )); then
	    # X X X Hackitly Hackit Hack
	    # drop those files that have empty SN, but report it
	    if [ "$(grep 'DISKUNIT TYPE' $newest)" -o "$(grep 'DISKUNIT SECSIZE' $newest)" ] ; then
		echo "===> Missing SN in $host $(pwd)/$newest"
# pop or not to pop - that is the question (ignore !SN file or process)
#::		newest=${allchecks%%\ *} # take off the top
#::		allchecks=${allchecks#* } # pop the list
		[[ ! $allchecks ]] && continue # ran out of files
	    fi
	    fi

	    # read $newset into the hwinvcopy array, hwinvcopy array has
	    # been declared in the checkutils.sh script
	    readtmcinfo $newest hwinvcopy
	    for tocheck in $allchecks ; do
		if (( ! $tellNonMFS )) ; then
		# if not in ismfs mode then don't check
 		[[ $(grep 'ismfs=0' $tocheck) ]] && continue 
		fi
		readtmcinfo $tocheck hwinv
		# note: will check against self for sanity
		comparetmcinfo /tmp/.$$checkdiff
		((filecount++))
		if [ -s /tmp/.$$checkdiff ] ; then
		    [[ "$print_blank" == "yes" ]] && { echo ""; print_blank=no; }
		    echo "$host generated tbdb $newest $tocheck are not the same"
		    echo "==============================================================="
		    cat /tmp/.$$checkdiff
#		    echo "diff $tocheck $newest"
#		    diff $tocheck $newest
		    # reset compare file
		    newest=$tocheck
		    readtmcinfo $newest hwinvcopy
		    # XXX - start
		    # XXX Do not enable this code unless you want
		    # the files moved out of the way
#		    [[ ! -d $projdir/$host/.tbdb/.notsame ]] && sudo mkdir -p $projdir/$host/.tbdb/.notsame 
#		    sudo mv $tocheck $projdir/$host/.tbdb/.notsame
		    # XXX - end
		else
# : echo "$host $newest $tocheck SAME"
		    ((countsame++))
		fi
		rm -f /tmp/.$$checkdiff
	    done
# :[[ $countsame -gt 1 ]] && echo "$host $countsame inventory files are the same"
# :	    echo -n .
	else
	    echo "No record of node id \"$host\"."
	fi
    done
    echo ""
    echo "$filecount files in $hostcount nodes checked"
}
checkwce_main() {

    for host in $allnodes ; do
	if [ -f $projdir/$host/full ] ; then

	#    wcestate=$(grep $projdir/$host/full$)
	    readtmcinfo $projdir/$host/full hwinv
	    numberofdrives=${hwinv["DISKINFO"]}
	    numberofdrives=${numberofdrives/DISKINFO UNITS=/}
	    
            for ((idx=0; idx<${numberofdrives}; idx++)) ; do
		unset -v d ; declare -a d=(${hwinv["DISKUNIT$idx"]})
                wce=${d[2]}
		if [ "$wce" != "${wce/enabled}" ] ; then
		    :
		elif [ "$wce" != "${wce/disabled}" ] ; then
		    x=${d[1]}
		    x=${x##*SN=}; x=${x%% *}; x=${x//\"/}
		    echo "$host ${d[8]} Write Cache Disabled bsidx=$(get_bsidx_from_sn $x)"
		else
		    :
		fi
            done

	else
	    echo "No record of node id \"$host\"."
	fi
    done
}

#   README
#   to get HD info from serial number
# select b.node_id,b.bs_id,b.bsidx,a.attrvalue from blockstores as b,blockstore_attributes as a where b.bsidx=a.bsidx and b.role='element' and a.attrkey='serialnum' and a.attrvalue='$sn';
#   to get HD info from node_id
# select b.node_id,b.bs_id,b.bsidx,a.attrvalue from blockstores as b,blockstore_attributes as a where b.bsidx=a.bsidx and b.role='element' and a.attrkey='serialnum' and b.node_id='pc510';
#
#   to get bsidx from serial number
# db_bsidx=$(mysql -B -N -e "select b.bsidx from blockstores as b,blockstore_attributes as a where b.bsidx=a.bsidx and b.role='element' and a.attrkey='serialnum' and a.attrvalue='$sn';" tbdb)
#   to get serial number from bsidx
# db_sn=$(mysql -B -N -e "select a.attrvalue from blockstores as b,blockstore_attributes as a where b.bsidx=a.bsidx and b.role='element' and a.attrkey='serialnum' and b.bsidx=$bsidx;" tbdb);

get_bsidx_from_sn() {
    echo $(mysql -B -N -e "select b.bsidx from blockstores as b,blockstore_attributes as a where b.bsidx=a.bsidx and b.role='element' and a.attrkey='serialnum' and a.attrvalue='$1';" tbdb)
}
get_sn_from_bsidx() {
    echo $(mysql -B -N -e "select a.attrvalue from blockstores as b,blockstore_attributes as a where b.bsidx=a.bsidx and b.role='element' and a.attrkey='serialnum' and b.bsidx=$1;" tbdb);
}
get_host_from_bsidx() {
    echo $(mysql -B -N -e "select node_id from blockstores where bsidx=$1;" tbdb);
}
get_drive_from_bsidx() {
    echo $(mysql -B -N -e "select bs_id from blockstores where bsidx=$1;" tbdb);
}
get_bsidx_from_hostAnddrive() {
    echo $(mysql -B -N -e "select bsidx from blockstores where node_id=\"$1\" and bs_id=\"disk${2}\";" tbdb)
}

gentbsql_main() {
    local sn="UNK" hdtype="UNK" secsize="UNK" sectors="UNK" wspeed="UNK" rspeed="UNK"
    # get current unique BlockStore number from tbdb
    bsidx_base=$(mysql -B -N -e "select idx from emulab_indicies where name='next_bsidx';" tbdb)
    keep_bsidx_base=$bsidx_base
    # XXX
    # try to fill gaps above 1000, good idea?
    bsidx_base=1000
    
    for host in $allnodes ; do
	if [ -d $projdir/$host ] ; then
	    cd $projdir/$host
	    sn="UNK" hdtype="UNK" secsize="UNK" sectors="UNK" wspeed="UNK" rspeed="UNK"
#	    [[ -f "diff" ]] || { echo "# $host: No changes needed"; continue; }
	    # need the diff and node files to continue. No diff then nothing to update
	    [[ -f "diff" ]] || continue;
	    [[ -f "node" ]] || { echo "# $host: Missing node file"; continue; }
	    # check for old version of file
	    if [ -z "$(grep "Diff Report" diff)" ] ; then
		echo "# $host Inventory Report to old to use"
		continue
	    fi

	    # we need a diff file that has SN in it, check the .diff directory
	    # for the info
	    # so try and find the SN in any .full/* file with serial numbers
# debugging	    echo "# $(pwd)"
	    dotDiffFiles=$(ls -t .diff)
	    for dDF in $dotDiffFiles ; do
		SNinfo=$(grep 'DISKs:' .diff/$dDF | grep -v UNKNOWN)
		[[ $SNinfo ]] && break # found, stop
	    done
	    # if we failed to find a file then default to the sym link
	    # else we found it 
	    if [ "$SNinfo" ] ; then
		indexF=$dDF
		diffFile=.diff/$dDF
		nodeFile=.tbdb/$dDF
	    else
		indexF=$(pwd)
		diffFile=diff
		nodeFile=node
	    fi
	    listofdisks=$(grep 'DISKs:' $diffFile)
	    listofdisks=${listofdisks//DISKs:}  # take string out
	    hdnum=0
	    # if 'DISKs:' is empty then try OUT OF ORDER message
	    if [ -z "$listofdisks" ]; then
		local_listofdisks=$(grep 'OUT OF ORDER' $diffFile)
		# remove up to first 'local['
		local_listofdisks=${local_listofdisks#*local\[}
		# remove from ']' to end
		local_listofdisks=${local_listofdisks%%]*}
                # have a second case where "ERROR DISK OUT OF ORDER"
                # remove up to 'from tbdb'
		local_listofdisks=${local_listofdisks#*from\ tbdb\ }
                for i in $local_listofdisks ; do
		    [[ $i == "UNKNOWN" ]] && continue
		    echo "# Local $host disks out of order - rewrite order this run. Run $0 again"
		    echo "mysql -e \"delete from blockstore_attributes where attrvalue='$i';\" tbdb"
		    listofdisks="$listofdisks $i"
                    done
echo "### not enabled - sudo rm $projdir/$host/diff"
	    fi
	    for i in $listofdisks ; do

		toadd=$(grep $i $nodeFile)
		if [ -n "$toadd" ] ; then
		    toadd=${toadd/DISKUNIT}
		    if [ "${toadd/SN=}" != "$toadd" ]; then
			sn=${toadd##*SN=}
			sn=${sn%% *}
			sn=${sn//\"/}
			# check and see if serial number is already in database
			bsidx=$(get_bsidx_from_sn $sn)
			[[ "$bsidx" ]] && continue
		    fi

		    # /* by Mike's orders
		    #  type		"class"	"protocol"	"rot-speed"	nodes with this type
		    # 
		    #  pata-generic	local	PATA		7200		pc600,pc850,pc2000
		    #  scsi-generic	local	SCSI		10000		pc3000,pc6000
		    #  sata-generic	local	SATA		7200		pc2400w,d710,d820,pcivy
		    #  sas-generic	local	SAS		10000		d820
		    #  ssd-generic	local	SATA		0		gpuhost,d820(pc601)
		    #  c2100-sas	local	SAS		15000		c2100
		    # */
		    nodetype=$(mysql -B -N -e "select type from nodes where node_id='$host';" tbdb)
		    case $nodetype in
			pc600 | pc850 | pc2000 ) hdtype="pata-generic" ;;
			pc3000 | pc6000 ) hdtype="scsi-generic" ;;
			pc2400w | d710 | d820 | pcivy ) hdtype="sata-generic" ;;
			d820 ) hdtype="sas-generic" ;;
			gpuhost ) hdtype="ssd-generic" ;;
			c2100 ) hdtype="c2100-sas" ;;
			* ) 
			    hdtype=${toadd##*TYPE=}
			    hdtype=${hdtype%% *}
			    ;;
		    esac
		    # translate type
		    [[ "$hdtype" == "SATA" ]] && hdtype="sata-generic"
		    [[ "$hdtype" == "PATA" ]] && hdtype="pata-generic"
		    [[ "$hdtype" == "SAS" ]] && hdtype="sas-generic"
		    # XXX XXX XXX special case
		    if [ "$host" == "pc601" ] ; then
			hdtype="ssd-generic"
		    fi
		    # XXX XXX XXX special case
		    if [ "${toadd/SECSIZE=}" != "$toadd" ]; then
			secsize=${toadd##*SECSIZE=}
			secsize=${secsize%% *}
		    fi
		    if [ "${toadd/SECTORS=}" != "$toadd" ]; then
			sectors=${toadd##*SECTORS=}
			sectors=${sectors%% *}
		    fi
		    if [ "${toadd/RSPEED=}" != "$toadd" ]; then
			rspeed=${toadd##*RSPEED=}
			rspeed=${rspeed%% *}
		    fi
		    if [ "${toadd/WSPEED=}" != "$toadd" ]; then
			wspeed=${toadd##*WSPEED=}
			wspeed=${wspeed%% *}
		    fi
		    size=$(($sectors / 2048))
		    # check and see if bsidx already used
		    while [ "$(mysql -B -N -e "select bsidx from blockstores where bsidx=$bsidx_base;" tbdb)" ] ; do
			echo "# bsidx=$bsidx_base already used, bumping bsidx_base again"
			((++bsidx_base))
		    done
                    # check and see if drive already used
		    found_blockstores_idx=''
		    found_blockstoreattr_idx=''
		    while [ "$(mysql -B -N -e "select bs_id from blockstores where node_id=\"$host\" and bs_id=\"disk${hdnum}\";" tbdb)" ] ; do
			# found a driveX on this host - see what we can find in the db
			found_blockstores_idx=''
			found_blockstoreattr_idx=''
			found_blockstores_idx=$(get_bsidx_from_hostAnddrive $host ${hdnum})
			found_blockstoreattr_idx=$(get_sn_from_bsidx $found_blockstores_idx)

			if [ "$found_blockstores_idx" ] ; then
			    echo -n "# $host drive${hdnum} already used @ bsidx=$found_blockstores_idx"
			    break
			else
			    echo "# drive${hdnum} already used, bumping hdnum again"
			    ((++hdnum))
			fi
		    done
		    if [ "$found_blockstores_idx" ] ; then
			# have a blockstores idx, check if we are missing blockstore_attrs
			if [ ! "$found_blockstoreattr_idx" ] ; then
			    # create one
			    echo " BUT blockstore_attributes missing"
			    printf "mysql -e \"insert into blockstore_attributes values (%d, '%s', '%s', '%s');\" tbdb\n" "$found_blockstores_idx" "serialnum" "$sn" "string" 
			    # assume we don't have to update blockstores, but display entry
			    echo "# MATCHING BLOCKSTORES @ $(mysql -B -N -e "select bsidx,node_id,bs_id,total_size  from blockstores where bsidx=$found_blockstores_idx;" tbdb);"
			else
			    echo ""
			fi
		    else
			# just make sure we don't have an entry
			justcheck=$(mysql -B -N -e "select * from blockstore_attributes where attrvalue='$sn';" tbdb );
			if [ "$justcheck" ] ; then
			    echo "ERROR: tbdb Inconsistance  EXIT"
			    echo "bsidx=$bsidx_base but found $sn in blockstore_attributes"
			    mysql -e "select * from blockstore_attributes where attrvalue='$sn';" tbdb
			    exit
			fi

			printf "mysql -e \"insert into blockstores values (%d, '%s', 'disk%d', 0, '%s', 'element', %d, 1, now());\" tbdb\n" "$bsidx_base" "$host" "$hdnum" "$hdtype" "$size" 
			# now the second table
			printf "mysql -e \"insert into blockstore_attributes values (%d, '%s', '%s', '%s');\" tbdb\n" "$bsidx_base" "serialnum" "$sn" "string" 
			# used up a bsidx, incr bsidx
			((++bsidx_base))
		    fi
		    # FINALLY onto the next drive
		    ((++hdnum))
		else
		    if [ "$i" == "UNKNOWN" ] ; then
			x=$(get_bsidx_from_hostAnddrive $host ${hdnum})
			if [ -n "$x" ] ; then
			    echo -n "# Can not locate Serial Number for disk${hdnum} on $host"
			    # so try and find the SN in any .full/* file with serial numbers
			    dotFullFiles=$(ls .full)
			    for dFF in $dotFullFiles ; do
				SNinfo=$(grep -v 'SN="UNKNOWN"' .full/$dFF | grep DISKUNIT)
				[[ $SNinfo ]] && break # found some stop
			    done
			    echo " but found $(get_drive_from_bsidx $x) $(get_host_from_bsidx $x) @ bsidx=$x SN=$(get_sn_from_bsidx $x)" 
			else
			    echo ""
			fi
			((++hdnum))
		    else
			toadd="SN='$i'"
			if [ "$sn" != "UNK" ] ; then
			    x_bsidx=$(get_bsidx_from_sn $i)
			    [[ $x_bsidx ]] && y_hostid=$(get_host_from_bsidx $x_bsidx) || y_hostid=""
			    if [ -n "$x_bsidx" -a -n "$y_hostid" ] ; then
				printf "mysql -e \"delete from blockstores where node_id='%s' and bsidx='%s';\" tbdb\n" "$host" "$x_bsidx"
				printf "mysql -e \"delete from blockstore_attributes where attrkey='%s';\" tbdb\n" "$i"
			    else
				echo "#Discovery error node_id $host, full:has SN BUT node:does not"
				      # Have only seen this when the "full" file for host lists a hard drive, including SN
				      # but the "node" file does not have serial number for for the HD
			    fi
			fi
		    fi
		fi
	    done
	    listofnics=$(grep 'NICs:' diff)
	    listofnics=${listofnics//NICs:/}
	    for i in $listofnics ; do
		toadd=$(grep $i node)
		if [ -n "$toadd" ] ; then
		    toadd=${toadd/*ID=\"}
		    toadd=${toadd/\"}
		    uuid=$(uuidgen)
		    printf "#BYHAND mysql -e \"insert into interfaces set node_id='%s',mac='%s',card=X,port=X,interface_type='?',iface='ethX',role='?',uuid='$uuid';\" tbdb\n" "$host" "$toadd"
		else
		    toadd="ID=\"$i\""
		    printf "#BYHAND mysql -e \"delete from interfaces where node_id='%s' and %s;\" tbdb\n" "$host" "$toadd"
		fi
	    done
	else
	    echo "# No record of node id \"$host\"."
	fi
    done
    if [ $keep_bsidx_base -lt $bsidx_base ] ; then
	echo "# orginal bsidx=$keep_bsidx_base : new bsidx=$bsidx_base"
	printf "mysql -e \"update emulab_indicies set idx=%d where name='next_bsidx';\" tbdb\n" "$bsidx_base"
    else
	echo "# orginal bsidx=$keep_bsidx_base : new bsidx=$bsidx_base == no update"
    fi
}

setdirstructure() {
    # start XXX
    # decided to change names again, rename if old names -- XXX remove this after all node have run the new code
    # take into consideration if old directory structure was added to to a new directory structure 
    set -u
    projdir=/proj/emulab-ops/nodecheck
    cd $projdir
    allnodes=$(ls -d pc* pg* dbox* gpu* 2> /dev/null)
    dirs2workon=""
    for i in $allnodes; do [[ -L $i/$i.diff ]] && dirs2workon+="$i "; done
    owd=$PWD

    for host in $dirs2workon ; do
    	if [ -d ${projdir}/$host ] ; then
	    cd ${projdir}/$host
	    [[ -L $host.full ]] && rm $host.full
	    [[ -L $host.diff ]] && rm $host.diff
	    [[ -L $host ]] && rm $host
	    for i in full diff tbdb ; do
		if [ -d $i ] ; then
		    [[ ! -d .$i ]] && (mkdir .$i ; chmod g+x .$i)
		    list=$(ls -A $i)
		    for j in $list ; do
			mv $i/$j .$i
		    done
		    rmdir $i
		fi
	    done
	    cd $owd
	fi
    done
    
    # put links in place
    for i in $dirs2workon ; do
	cd $projdir/$i
	links="full diff node"
	for link in $links ; do
	    case $link in
		full | diff )
		    # make the symlinks
		    if [ ! -e ${link} -a -d .${link} ] ; then
			linkto=$(ls -t .${link} | head -1)
			if [ -n "$linkto" ] ; then
 			    ln -s .${link}/${linkto} $link
			fi
		    fi
		    ;;
		node )
		    if [ ! -e ${link} -a -d .tbdb ] ; then
			linkto=$(ls -t .tbdb | head -1)
			if [ -n "$linkto" ] ; then
 			    ln -s .tbdb/${linkto} $link
			    ln -s .tbdb/${linkto} $i
			fi
		    fi
		    ;;
	    esac
	done
    done
}

# start here
set -u
projdir=/proj/emulab-ops/nodecheck
# if projdir is automounted this should mount it
cd $projdir

if (( $# )) ; then
    allnodes="$@"
else
    nodes=$(ls */tmcc)
    allnodes=${nodes//\/tmcc/}
fi

if [ "$0" != "${0/setdir}" ] ; then
    setdirstructure
elif [ "$0" != "${0/sql}" ] ; then
    gentbsql_main $@
elif [ "$0" != "${0/drift}" ] ; then
    checkdrift_main $@
elif [ "$0" != "wce" ] ; then
    checkwce_main $@
else
    "Print useage help"
fi