test/sql/Datatype/test004.ksh

   1 #!/bin/ksh
   2 # Primary key and Foreign Key
   3 # Create table t1 (f1 varchar(10), f2 int, primary key(f1));
   4 # Create table t2 (f1 varchar(10), f2 bigint, foreign key(f1) references t1(f1));
   5 # t1.f1 should not allow NULL Value, Duplicate value.
   6 # Through update statement try to update f1 field of t1. It should be failed due to primary key
   7 # t2.f1 should allow only values which are in t1.f1.
   8 # Delete records from t1 table which has the dependancy in t2 table. It should be failed due to parent child dependancy.
   9 #
  10 CREATEFILE=${PWD}/sql/Datatype/csql.conf
  11 REL_PATH=.
  12 if [ -s "$CREATEFILE" ]
  13 then
  14     REL_PATH=`pwd`/sql/Datatype
  15 fi
  16
  17 echo "echo Create table t1 (f1 varchar(10), f2 int, primary key(f1));" >${REL_PATH}/varchar_pkek_fkey.sql
  18 echo "Create table t1 (f1 varchar(10), f2 int, primary key(f1));" >>${REL_PATH}/varchar_pkek_fkey.sql
  19 echo "echo Create table t2 (f1 varchar(10), f2 bigint, foreign key(f1) references t1(f1));" >>${REL_PATH}/varchar_pkek_fkey.sql
  20 echo "Create table t2 (f1 varchar(10), f2 bigint, foreign key(f1) references t1(f1));" >>${REL_PATH}/varchar_pkek_fkey.sql
  21 echo "insert into t1 values('CSQL1',1);" >>${REL_PATH}/varchar_pkek_fkey.sql
  22 echo "insert into t1 values('CSQL2',2);" >>${REL_PATH}/varchar_pkek_fkey.sql
  23 echo "echo insert into t1 values(NULL,3);" >>${REL_PATH}/varchar_pkek_fkey.sql
  24 echo "insert into t1 values(NULL,3);" >>${REL_PATH}/varchar_pkek_fkey.sql
  25 echo "echo insert into t1 values('CSQL2',4);" >>${REL_PATH}/varchar_pkek_fkey.sql
  26 echo "insert into t1 values('CSQL2',4);" >>${REL_PATH}/varchar_pkek_fkey.sql
  27 echo "echo select * from t1;" >>${REL_PATH}/varchar_pkek_fkey.sql
  28 echo "select * from t1;" >>${REL_PATH}/varchar_pkek_fkey.sql
  29 echo "echo update t1 set f1='CSQL1' where f1='CSQL2';" >>${REL_PATH}/varchar_pkek_fkey.sql
  30 echo "update t1 set f1='CSQL1' where f1='CSQL2';" >>${REL_PATH}/varchar_pkek_fkey.sql
  31 echo "echo select * from t1;" >>${REL_PATH}/varchar_pkek_fkey.sql
  32 echo "select * from t1;" >>${REL_PATH}/varchar_pkek_fkey.sql
  33 echo "insert into t2 values('CSQL1',10);" >>${REL_PATH}/varchar_pkek_fkey.sql
  34 echo "insert into t2 values('CSQL2',20);" >>${REL_PATH}/varchar_pkek_fkey.sql
  35 echo "echo insert into t2 values('CSQL3',30);" >>${REL_PATH}/varchar_pkek_fkey.sql
  36 echo "insert into t2 values('CSQL3',30);" >>${REL_PATH}/varchar_pkek_fkey.sql
  37 echo "echo select * from t2;" >>${REL_PATH}/varchar_pkek_fkey.sql
  38 echo "select * from t2;" >>${REL_PATH}/varchar_pkek_fkey.sql
  39 echo "echo delete from t1 where f1='CSQL1';" >>${REL_PATH}/varchar_pkek_fkey.sql
  40 echo "delete from t1 where f1='CSQL1';" >>${REL_PATH}/varchar_pkek_fkey.sql
  41 echo "echo select * from t1;" >>${REL_PATH}/varchar_pkek_fkey.sql
  42 echo "select * from t1;" >>${REL_PATH}/varchar_pkek_fkey.sql
  43 echo "echo select * from t2;" >>${REL_PATH}/varchar_pkek_fkey.sql
  44 echo "select * from t2;" >>${REL_PATH}/varchar_pkek_fkey.sql
  45
  46 echo "drop table t2;" >${REL_PATH}/drop.sql
  47 echo "drop table t1;" >>${REL_PATH}/drop.sql
  48
  49 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/drop.sql >/dev/null 2>&1
  50
  51 if [ "$VALGRIND" = "true" ]
  52 then
  53     valgrind --tool=memcheck --leak-check=full $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/varchar_pkek_fkey.sql
  54 else
  55     $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/varchar_pkek_fkey.sql
  56     if [ $? -ne 0 ]
  57     then
  58        $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/drop.sql
  59        rm -f ${REL_PATH}/varchar_pkek_fkey.sql ${REL_PATH}/drop.sql
  60        exit 1;
  61     fi
  62 fi
  63
  64 if [ "$VALGRIND" = "true" ]
  65 then
  66     valgrind --tool=memcheck --leak-check=full $CSQL_INSTALL_ROOT/bin/catalog -u root -p manager -l
  67 else
  68     $CSQL_INSTALL_ROOT/bin/catalog -u root -p manager -l
  69 fi
  70
  71 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/drop.sql
  72 if [ $? -ne 0 ]
  73 then
  74    exit 2;
  75 fi
  76
  77 rm -f ${REL_PATH}/varchar_pkek_fkey.sql ${REL_PATH}/drop.sql
  78 exit 0;