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

#!/bin/sh


# SETUP

# directory where the sequence files are stored
sequencedir=/magic/sequences
# access to the sql database
alias mymysql='mysql -s -u MAGIC -h hercules --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


# CHECK 1
echo Checking if all sequences in Sequences have a corresponding sequence files
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: $sequence
      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 the db.
      continue
   fi
done

# CHECK 2
echo Checking if all sequence files have a corresponding entry in Sequences
sequences=`echo SELECT fSequenceFirst FROM $db.Sequences | mymysql`
for sequence in $sequences
do
   var=`find $sequencedir -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