#!/bin/csh -f
# dbsave
# -----------------------------------------------------------------------------
# C shell script to dump data from the InfraTrac MySQL DB.
# -----------------------------------------------------------------------------
# Revision History:
#   $Log$
# -----------------------------------------------------------------------------

if ($1 == "-?" || $1 == "-h" || $1 == "help") then
   set verb = $0
   echo "Usage: $verb:t"
   echo "       $verb:t output_file.sql"
   exit 1
endif

# mysqldb show infratrac
set tables = ""
echo "Not dumping:"
echo "   itfa_dict"
echo "   itfa_unit"
echo "   itfa_user"
echo "   itfa_ingred"
echo "   itfa_product"
echo "   itfa_formltn"
echo "   itfa_formltn_ingred_map"
echo "   itfa_regenv"
echo "   itfa_approval"
echo "   itfa_approval_ingred_map"
echo "   itfa_spectrum"
echo "   itfa_access_attr"
echo "   itfa_access_attr_right_map"
echo "   itfa_user_access_attr_map"
echo "   itfa_object_access_attr_map"
echo "   itfa_file"
echo "Dumping:"

set tables = ($tables itf_dict)
echo "   itf_dict"

set tables = ($tables itf_unit)
echo "   itf_unit"

set tables = ($tables itf_user)
echo "   itf_user"

set tables = ($tables itf_ingred)
echo "   itf_ingred"

set tables = ($tables itf_product)
echo "   itf_product"

set tables = ($tables itf_formltn)
echo "   itf_formltn"

set tables = ($tables itf_formltn_ingred_map)
echo "   itf_formltn_ingred_map"

set tables = ($tables itf_regenv)
echo "   itf_regenv"

set tables = ($tables itf_approval)
echo "   itf_approval"

set tables = ($tables itf_approval_ingred_map)
echo "   itf_approval_ingred_map"

set tables = ($tables itf_spectrum)
echo "   itf_spectrum"

set tables = ($tables itf_access_attr)
echo "   itf_access_attr"

set tables = ($tables itf_access_attr_right_map)
echo "   itf_access_attr_right_map"

set tables = ($tables itf_user_access_attr_map)
echo "   itf_user_access_attr_map"

set tables = ($tables itf_object_access_attr_map)
echo "   itf_object_access_attr_map"

set tables = ($tables itf_file)
echo "   itf_file"

if ($1 == "") then
   # pause
   echo "use infratrac;"
   # Delete in reverse order to accommodate foreign key constraints.
   echo "DELETE FROM itf_file;"
   echo "DELETE FROM itf_user_access_attr_map;"
   echo "DELETE FROM itf_object_access_attr_map;"
   echo "DELETE FROM itf_access_attr_right_map;"
   echo "DELETE FROM itf_access_attr;"
   # Note: Have to null the FKs from itf_spectrum to itself before deleting.
   echo "UPDATE itf_spectrum SET derived_from_id=null;"
   echo "DELETE FROM itf_spectrum;"
   echo "DELETE FROM itf_approval_ingred_map;"
   echo "DELETE FROM itf_approval;"
   echo "DELETE FROM itf_regenv;"
   echo "DELETE FROM itf_formltn_ingred_map;"
   echo "DELETE FROM itf_formltn;"
   echo "DELETE FROM itf_product;"
   echo "DELETE FROM itf_ingred;"
   echo "DELETE FROM itf_user;"
   echo "DELETE FROM itf_unit;"
   # Note: Have to temporarily drop the FK constraints to delete from itf_dict,
   #       and to re-insert unordered data into itf_dict.
   # Can't just DELETE all itf_dict rows since they have FKs to each other.
   # Can't UPDATE the FKs to null.
   #  echo "UPDATE itf_dict SET internal_category_id=null;"
   #  echo "UPDATE itf_dict SET status_id=null;"
   # Can't use IGNORE clause of DELETE.  It hangs mysql client.
   #  echo "DELETE IGNORE FROM itf_dict;"
   # Don't want to have special logic to delete them in the right order.
   # Should be more generic than that.
   #  echo "DELETE FROM itf_dict WHERE category NOT IN ('INTERNAL_CATEGORY_LOOKUP','STATUS_LOOKUP');"
   #  echo "..."
   echo "ALTER TABLE itf_dict DROP FOREIGN KEY fk1_dict;"
   echo "ALTER TABLE itf_dict DROP FOREIGN KEY fk2_dict;"
   echo "DELETE FROM itf_dict;"
   # pause

   mysqldump -u root -p${MYSQL_PW} --skip-opt --no-create-info --skip-triggers --order-by-primary --complete-insert infratrac $tables | grep "INSERT INTO" | more

   # Note: Can now restore the FK constraints, after all of the unordered 
   #       re-INSERTS into itf_dict are done.
   echo "ALTER TABLE     itf_dict"
   echo " ADD CONSTRAINT fk1_dict"
   echo "    FOREIGN KEY fk1_dict (status_id)"
   echo "       REFERENCES itf_dict (itf_dict_id)"
   echo ";"
   echo "ALTER TABLE     itf_dict"
   echo " ADD CONSTRAINT fk2_dict"
   echo "    FOREIGN KEY fk2_dict (internal_category_id)"
   echo "       REFERENCES itf_dict (itf_dict_id)"
   echo ";"
else
   echo "use infratrac;"                                        >  $1
   # Delete in reverse order to accommodate foreign key constraints.
   echo "DELETE FROM itf_file;"                                 >> $1
   echo "DELETE FROM itf_user_access_attr_map;"                 >> $1
   echo "DELETE FROM itf_object_access_attr_map;"               >> $1
   echo "DELETE FROM itf_access_attr_right_map;"                >> $1
   echo "DELETE FROM itf_access_attr;"                          >> $1
   # Note: Have to null the FKs from itf_spectrum to itself before deleting.
   echo "UPDATE itf_spectrum SET derived_from_id=null;"         >> $1
   echo "DELETE FROM itf_spectrum;"                             >> $1
   echo "DELETE FROM itf_approval_ingred_map;"                  >> $1
   echo "DELETE FROM itf_approval;"                             >> $1
   echo "DELETE FROM itf_regenv;"                               >> $1
   echo "DELETE FROM itf_formltn_ingred_map;"                   >> $1
   echo "DELETE FROM itf_formltn;"                              >> $1
   echo "DELETE FROM itf_product;"                              >> $1
   echo "DELETE FROM itf_ingred;"                               >> $1
   echo "DELETE FROM itf_user;"                                 >> $1
   echo "DELETE FROM itf_unit;"                                 >> $1
   # Note: Have to temporarily drop the FK constraints to delete from itf_dict,
   #       and to re-insert unordered data into itf_dict.
   # Can't just DELETE all itf_dict rows since they have FKs to each other.
   # Can't UPDATE the FKs to null.
   #  echo "UPDATE itf_dict SET internal_category_id=null;"        >> $1
   #  echo "UPDATE itf_dict SET status_id=null;"                   >> $1
   # Can't use IGNORE clause of DELETE.  It hangs mysql client.
   #  echo "DELETE IGNORE FROM itf_dict;"                          >> $1
   # Don't want to have special logic to delete them in the right order.
   # Should be more generic than that.
   #  echo "DELETE FROM itf_dict WHERE category NOT IN ('INTERNAL_CATEGORY_LOOKUP','STATUS_LOOKUP');"  >> $1
   #  echo "..."
   echo "ALTER TABLE itf_dict DROP FOREIGN KEY fk1_dict;"       >> $1
   echo "ALTER TABLE itf_dict DROP FOREIGN KEY fk2_dict;"       >> $1
   echo "DELETE FROM itf_dict;"                                 >> $1

    mysqldump -u root -p${MYSQL_PW} --skip-opt --no-create-info --skip-triggers --order-by-primary --complete-insert infratrac $tables | grep "INSERT INTO" >> $1

   # Note: Can now restore the FK constraints, after all of the unordered 
   #       re-INSERTS into itf_dict are done.
   echo "ALTER TABLE     itf_dict"                              >> $1
   echo " ADD CONSTRAINT fk1_dict"                              >> $1
   echo "    FOREIGN KEY fk1_dict (status_id)"                  >> $1
   echo "       REFERENCES itf_dict (itf_dict_id)"              >> $1
   echo ";"                                                     >> $1
   echo "ALTER TABLE     itf_dict"                              >> $1
   echo " ADD CONSTRAINT fk2_dict"                              >> $1
   echo "    FOREIGN KEY fk2_dict (internal_category_id)"       >> $1
   echo "       REFERENCES itf_dict (itf_dict_id)"              >> $1
   echo ";"                                                     >> $1
endif

echo "Don't forget to also save the external image and spectra files."