From b48e3045ff8643a85c5209d66f6ebaad9ad078b9 Mon Sep 17 00:00:00 2001
From: prabatuty <prabatuty>
Date: Wed, 30 Jul 2008 13:45:47 +0000
Subject: [PATCH] Adding tests for composite keys

---
 test/tools/csql/comp1.sql       | 23 ++++++++++++++++
 test/tools/csql/comp2.sql       | 24 ++++++++++++++++
 test/tools/csql/comp3.sql       | 24 ++++++++++++++++
 test/tools/csql/comp4.sql       |  7 +++++
 test/tools/csql/exp.test030.ksh | 58 +++++++++++++++++++++++++++++++++++++++
 test/tools/csql/exp.test031.ksh | 61 +++++++++++++++++++++++++++++++++++++++++
 test/tools/csql/exp.test032.ksh | 59 +++++++++++++++++++++++++++++++++++++++
 test/tools/csql/exp.test033.ksh | 16 +++++++++++
 test/tools/csql/test030.ksh     | 23 ++++++++++++++++
 test/tools/csql/test031.ksh     | 23 ++++++++++++++++
 test/tools/csql/test032.ksh     | 23 ++++++++++++++++
 test/tools/csql/test033.ksh     | 23 ++++++++++++++++
 12 files changed, 364 insertions(+)
 create mode 100644 test/tools/csql/comp1.sql
 create mode 100644 test/tools/csql/comp2.sql
 create mode 100644 test/tools/csql/comp3.sql
 create mode 100644 test/tools/csql/comp4.sql
 create mode 100644 test/tools/csql/exp.test030.ksh
 create mode 100644 test/tools/csql/exp.test031.ksh
 create mode 100644 test/tools/csql/exp.test032.ksh
 create mode 100644 test/tools/csql/exp.test033.ksh
 create mode 100755 test/tools/csql/test030.ksh
 create mode 100755 test/tools/csql/test031.ksh
 create mode 100755 test/tools/csql/test032.ksh
 create mode 100755 test/tools/csql/test033.ksh

diff --git a/test/tools/csql/comp1.sql b/test/tools/csql/comp1.sql
new file mode 100644
index 00000000..502c6e15
--- /dev/null
+++ b/test/tools/csql/comp1.sql
@@ -0,0 +1,23 @@
+CREATE TABLE t1 (f1 INT ,f2 INT ,f3 INT, primary key(f1,f2));
+INSERT INTO t1 VALUES(1,1,1);
+INSERT INTO t1 VALUES(2,1,2);
+INSERT INTO t1 VALUES(3,1,3);
+INSERT INTO t1 VALUES(1,1,3);
+INSERT INTO t1 VALUES(5,2,5);
+INSERT INTO t1 VALUES(10,2,10);
+INSERT INTO t1 VALUES(100,3,100);
+echo select count(f1) from t1;
+select count(f1) from t1;
+echo select * from t1 where f1=1 and f2=1;
+select * from t1 where f1=1 and f2=1;
+echo select * from t1 where f1=5 and f2=1;
+select * from t1 where f1=5 and f2=1;
+echo select * from t1 where f1=5;
+select * from t1 where f1=5;
+echo select * from t1 where f1=7;
+select * from t1 where f1=7;
+echo select * from t1 where f1=10 and f2=2 and f3=10;
+select * from t1 where f1=10 and f2=2 and f3=10;
+echo select * from t1 where f1=10 and f2=6 and f3=10;
+select * from t1 where f1=10 and f2=6 and f3=10;
+echo test over
diff --git a/test/tools/csql/comp2.sql b/test/tools/csql/comp2.sql
new file mode 100644
index 00000000..3982d0c2
--- /dev/null
+++ b/test/tools/csql/comp2.sql
@@ -0,0 +1,24 @@
+CREATE TABLE t1 (f1 INT NOT NULL,f2 INT NOT NULL,f3 INT);
+CREATE INDEX idx on t1 (f1,f2);
+INSERT INTO t1 VALUES(1,1,1);
+INSERT INTO t1 VALUES(2,1,2);
+INSERT INTO t1 VALUES(3,1,3);
+INSERT INTO t1 VALUES(1,1,3);
+INSERT INTO t1 VALUES(5,2,5);
+INSERT INTO t1 VALUES(10,2,10);
+INSERT INTO t1 VALUES(100,3,100);
+echo select count(f1) from t1;
+select count(f1) from t1;
+echo select * from t1 where f1=1 and f2=1;
+select * from t1 where f1=1 and f2=1;
+echo select * from t1 where f1=5 and f2=1;
+select * from t1 where f1=5 and f2=1;
+echo select * from t1 where f1=5;
+select * from t1 where f1=5;
+echo select * from t1 where f1=7;
+select * from t1 where f1=7;
+echo select * from t1 where f1=10 and f2=2 and f3=10;
+select * from t1 where f1=10 and f2=2 and f3=10;
+echo select * from t1 where f1=10 and f2=6 and f3=10;
+select * from t1 where f1=10 and f2=6 and f3=10;
+echo test over
diff --git a/test/tools/csql/comp3.sql b/test/tools/csql/comp3.sql
new file mode 100644
index 00000000..e50a798a
--- /dev/null
+++ b/test/tools/csql/comp3.sql
@@ -0,0 +1,24 @@
+CREATE TABLE t1 (f1 INT NOT NULL,f2 char(10) NOT NULL,f3 INT);
+CREATE INDEX idx on t1 (f1,f2) HASH UNIQUE;
+INSERT INTO t1 VALUES(1,'1',1);
+INSERT INTO t1 VALUES(2,'1',2);
+INSERT INTO t1 VALUES(3,'1',3);
+INSERT INTO t1 VALUES(1,'1',3);
+INSERT INTO t1 VALUES(5,'2',5);
+INSERT INTO t1 VALUES(10,'2',10);
+INSERT INTO t1 VALUES(100,'3',100);
+echo select count(f1) from t1;
+select count(f1) from t1;
+echo select * from t1 where f1=1 and f2=1;
+select * from t1 where f1=1 and f2='1';
+echo select * from t1 where f1=5 and f2=1;
+select * from t1 where f1=5 and f2='1';
+echo select * from t1 where f1=5;
+select * from t1 where f1=5;
+echo select * from t1 where f1=7;
+select * from t1 where f1=7;
+echo select * from t1 where f1=10 and f2=2 and f3=10;
+select * from t1 where f1=10 and f2='2' and f3=10;
+echo select * from t1 where f1=10 and f2=6 and f3=10;
+select * from t1 where f1=10 and f2='6' and f3=10;
+echo test over
diff --git a/test/tools/csql/comp4.sql b/test/tools/csql/comp4.sql
new file mode 100644
index 00000000..447104ae
--- /dev/null
+++ b/test/tools/csql/comp4.sql
@@ -0,0 +1,7 @@
+CREATE TABLE t1 (f1 INT NOT NULL,f2 char(10) NOT NULL,f3 INT);
+CREATE INDEX idx on t1 (f1,f2) TREE UNIQUE;
+INSERT INTO t1 VALUES(1,'1',1);
+INSERT INTO t1 VALUES(3,'1',3);
+echo select count(f1) from t1;
+select count(f1) from t1;
+echo test over
diff --git a/test/tools/csql/exp.test030.ksh b/test/tools/csql/exp.test030.ksh
new file mode 100644
index 00000000..c632c35f
--- /dev/null
+++ b/test/tools/csql/exp.test030.ksh
@@ -0,0 +1,58 @@
+Statement Executed
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement execute failed with error -21
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+echo select count(f1) from t1;
+---------------------------------------------------------
+		
+---------------------------------------------------------
+	6	
+	
+echo select * from t1 where f1=1 and f2=1;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	1	1	1	
+	
+echo select * from t1 where f1=5 and f2=1;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+echo select * from t1 where f1=5;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	5	2	5	
+	
+echo select * from t1 where f1=7;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+echo select * from t1 where f1=10 and f2=2 and f3=10;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	10	2	10	
+	
+echo select * from t1 where f1=10 and f2=6 and f3=10;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+CREATE TABLE t1 (f1 INT  NOT NULL , f2 INT  NOT NULL , f3 INT );
+CREATE INDEX t1_idx1_Primary on t1 (  f1  ,f2  )  UNIQUE;
+SET AUTOCOMMIT OFF;
+INSERT INTO t1 VALUES(1,1,1);
+INSERT INTO t1 VALUES(2,1,2);
+INSERT INTO t1 VALUES(3,1,3);
+INSERT INTO t1 VALUES(5,2,5);
+INSERT INTO t1 VALUES(10,2,10);
+INSERT INTO t1 VALUES(100,3,100);
+COMMIT;
+Statement Executed
diff --git a/test/tools/csql/exp.test031.ksh b/test/tools/csql/exp.test031.ksh
new file mode 100644
index 00000000..366da6cb
--- /dev/null
+++ b/test/tools/csql/exp.test031.ksh
@@ -0,0 +1,61 @@
+Statement Executed
+Statement Executed
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+echo select count(f1) from t1;
+---------------------------------------------------------
+		
+---------------------------------------------------------
+	7	
+	
+echo select * from t1 where f1=1 and f2=1;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	1	1	1	
+	1	1	3	
+	
+echo select * from t1 where f1=5 and f2=1;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+echo select * from t1 where f1=5;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	5	2	5	
+	
+echo select * from t1 where f1=7;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+echo select * from t1 where f1=10 and f2=2 and f3=10;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	10	2	10	
+	
+echo select * from t1 where f1=10 and f2=6 and f3=10;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+CREATE TABLE t1 (f1 INT  NOT NULL , f2 INT  NOT NULL , f3 INT );
+CREATE INDEX idx on t1 (  f1  ,f2  ) ;
+SET AUTOCOMMIT OFF;
+INSERT INTO t1 VALUES(1,1,1);
+INSERT INTO t1 VALUES(2,1,2);
+INSERT INTO t1 VALUES(3,1,3);
+INSERT INTO t1 VALUES(1,1,3);
+INSERT INTO t1 VALUES(5,2,5);
+INSERT INTO t1 VALUES(10,2,10);
+INSERT INTO t1 VALUES(100,3,100);
+COMMIT;
+Statement Executed
diff --git a/test/tools/csql/exp.test032.ksh b/test/tools/csql/exp.test032.ksh
new file mode 100644
index 00000000..8dd85298
--- /dev/null
+++ b/test/tools/csql/exp.test032.ksh
@@ -0,0 +1,59 @@
+Statement Executed
+Statement Executed
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement execute failed with error -21
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+echo select count(f1) from t1;
+---------------------------------------------------------
+		
+---------------------------------------------------------
+	6	
+	
+echo select * from t1 where f1=1 and f2=1;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	1	1	1	
+	
+echo select * from t1 where f1=5 and f2=1;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+echo select * from t1 where f1=5;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	5	2	5	
+	
+echo select * from t1 where f1=7;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+echo select * from t1 where f1=10 and f2=2 and f3=10;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	10	2	10	
+	
+echo select * from t1 where f1=10 and f2=6 and f3=10;
+---------------------------------------------------------
+	f1	f2	f3	
+---------------------------------------------------------
+	
+CREATE TABLE t1 (f1 INT  NOT NULL , f2 CHAR (10) NOT NULL , f3 INT );
+CREATE INDEX idx on t1 (  f1  ,f2  )  UNIQUE;
+SET AUTOCOMMIT OFF;
+INSERT INTO t1 VALUES(1, '1',1);
+INSERT INTO t1 VALUES(2, '1',2);
+INSERT INTO t1 VALUES(3, '1',3);
+INSERT INTO t1 VALUES(5, '2',5);
+INSERT INTO t1 VALUES(10, '2',10);
+INSERT INTO t1 VALUES(100, '3',100);
+COMMIT;
+Statement Executed
diff --git a/test/tools/csql/exp.test033.ksh b/test/tools/csql/exp.test033.ksh
new file mode 100644
index 00000000..2f45a35b
--- /dev/null
+++ b/test/tools/csql/exp.test033.ksh
@@ -0,0 +1,16 @@
+Statement Executed
+Statement execute failed with error -17
+Statement Executed: Rows Affected = 1
+Statement Executed: Rows Affected = 1
+echo select count(f1) from t1;
+---------------------------------------------------------
+		
+---------------------------------------------------------
+	2	
+	
+CREATE TABLE t1 (f1 INT  NOT NULL , f2 CHAR (10) NOT NULL , f3 INT );
+SET AUTOCOMMIT OFF;
+INSERT INTO t1 VALUES(1, '1',1);
+INSERT INTO t1 VALUES(3, '1',3);
+COMMIT;
+Statement Executed
diff --git a/test/tools/csql/test030.ksh b/test/tools/csql/test030.ksh
new file mode 100755
index 00000000..f7ff52f2
--- /dev/null
+++ b/test/tools/csql/test030.ksh
@@ -0,0 +1,23 @@
+#!/bin/sh
+# Test Case
+# check composite primary key
+#
+QUITFILE=${PWD}/tools/csql/quit.sql
+REL_PATH=.
+if [ -s "$QUITFILE" ]
+then
+    REL_PATH=`pwd`/tools/csql
+fi
+
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/comp1.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+$CSQL_INSTALL_ROOT/bin/csqldump -T t1
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/drop.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+
diff --git a/test/tools/csql/test031.ksh b/test/tools/csql/test031.ksh
new file mode 100755
index 00000000..9e61459f
--- /dev/null
+++ b/test/tools/csql/test031.ksh
@@ -0,0 +1,23 @@
+#!/bin/sh
+# Test Case
+# check non-unique index with composite keys
+#
+QUITFILE=${PWD}/tools/csql/quit.sql
+REL_PATH=.
+if [ -s "$QUITFILE" ]
+then
+    REL_PATH=`pwd`/tools/csql
+fi
+
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/comp2.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+$CSQL_INSTALL_ROOT/bin/csqldump -T t1
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/drop.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+
diff --git a/test/tools/csql/test032.ksh b/test/tools/csql/test032.ksh
new file mode 100755
index 00000000..3f3ef944
--- /dev/null
+++ b/test/tools/csql/test032.ksh
@@ -0,0 +1,23 @@
+#!/bin/sh
+# Test Case
+# check non-unique index with composite keys
+#
+QUITFILE=${PWD}/tools/csql/quit.sql
+REL_PATH=.
+if [ -s "$QUITFILE" ]
+then
+    REL_PATH=`pwd`/tools/csql
+fi
+
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/comp3.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+$CSQL_INSTALL_ROOT/bin/csqldump -T t1
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/drop.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+
diff --git a/test/tools/csql/test033.ksh b/test/tools/csql/test033.ksh
new file mode 100755
index 00000000..87de84c5
--- /dev/null
+++ b/test/tools/csql/test033.ksh
@@ -0,0 +1,23 @@
+#!/bin/sh
+# Test Case
+# check non-unique index with composite keys
+#
+QUITFILE=${PWD}/tools/csql/quit.sql
+REL_PATH=.
+if [ -s "$QUITFILE" ]
+then
+    REL_PATH=`pwd`/tools/csql
+fi
+
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/comp4.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+$CSQL_INSTALL_ROOT/bin/csqldump -T t1
+$CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/drop.sql
+if [ $? -ne 0 ]
+then
+   exit 1;
+fi
+
-- 
2.11.4.GIT