source: trunk/MagicSoft/Mars/datacenter/scripts/dbchk@ 8499

#!/bin/sh


# SETUP

# directory where the sequence files are stored
sequencedir=/magic/sequences
callistodir=/magic/data/callisto
stardir=/magic/data/star
# access to the sql database
alias mymysql='mysql -s -u MAGIC -h vela --password=d99swMT!'
# database name
db=MyMagic


# TODO

# Spped up with SQL: FIELD(25123, 1, 2, 3, 4, 5, ..., 1000200);
# Cross check sequences in Sequence and other databases
# added switch which allows to correct for the problems immediatly
# unify the checks (especially 1b and 1c)
# for the sequences better use 0* instead of 0+ ?


# CHECK 1
echo Checking if all sequence files have a corresponding entry in Sequences
files=`find $sequencedir -type f`
for file in $files
do
   sequence=`echo $file | sed -e "s/^.*\/sequence0\+\([0-9]\+\)\.txt$/\1/"`
   if [ "$sequence" = "" ]
   then
      echo No sequence file: $file
      continue
   fi

   var=`echo SELECT fSequenceFirst FROM $db.Sequences WHERE fSequenceFirst=$sequence | mymysql`
   if ! [ "$sequence" = "$var" ]
   then
      echo Sequence-File $sequence exist but it is not in Sequences.
      continue
   fi
done

# CHECK 1b (callisto)
echo Checking if all sequences in $callistodir have a corresponding sequence in Sequence
dirs=`find $callistodir -type d`
for dir in $dirs
do
   sequence=`echo $file | sed -e "s/^.*\/0\+\([0-9]\+\)$/\1/"`
   if [ "$sequence" = "" ]
   then
      echo Invalid directory: $dir
      continue
   fi

   var=`echo SELECT fSequenceFirst FROM $db.Sequences WHERE fSequenceFirst=$sequence | mymysql`
   if ! [ "$sequence" = "$var" ]
   then
      echo $dir exists but no corresponding sequence in Sequences.
      continue
   fi
done

# CHECK 1c (star)
echo Checking if all sequences in $stardir have a corresponding sequence in Sequence
dirs=`find $stardir -type d`
for dir in $dirs
do
   sequence=`echo $file | sed -e "s/^.*\/0\+\([0-9]\+\)$/\1/"`
   if [ "$sequence" = "" ]
   then
      echo Invalid directory: $dir
      continue
   fi

   var=`echo SELECT fSequenceFirst FROM $db.Sequences WHERE fSequenceFirst=$sequence | mymysql`
   if ! [ "$sequence" = "$var" ]
   then
      echo $dir exists but no corresponding sequence in Sequences.
      continue
   fi
done

# CHECK 2
echo Checking if all sequences in Sequences have a corresponding sequence files
sequences=`echo SELECT fSequenceFirst FROM $db.Sequences | mymysql`
for sequence in $sequences
do
   var=`find $sequencedir -type f -regex .*/sequence0+${sequence}\.txt$`
   if [ "$var" = "" ]
   then
      echo Sequence-File for $sequence not found but in db.
   fi
done

# CHECK 3
echo Checking if all sequences from Sequences exists RunData
sequences=`echo SELECT fSequenceFirst FROM $db.Sequences GROUP BY fSequenceFirst | mymysql`
for sequence in $sequences
do
   res=`echo SELECT fSequenceFirst FROM $db.RunData WHERE fSequenceFirst=$sequence GROUP BY fSequenceFirst | mymysql`
   if ! [ "$sequence" = "$res" ]
   then
      echo Sequence $sequence exists in Sequences but not in RunData.
      continue
   fi
done

# CHECK 4
echo Checking if all sequences from RunData exists in Sequences
sequences=`echo SELECT fSequenceFirst FROM $db.RunData WHERE not fSequenceFirst=0 GROUP BY fSequenceFirst | mymysql`
for sequence in $sequences
do
   var=`echo SELECT fSequenceFirst FROM $db.Sequences WHERE fSequenceFirst=$sequence | mymysql`
   if ! [ "$sequence" = "$var" ]
   then
      echo Sequence $sequence exists in RunData but not in Sequences.
      continue
   fi
done