Repository: incubator-trafodion
Updated Branches:
  refs/heads/master 8805eb26c -> cc915f3ff


[TRAFODION-1698]ODBC: trafodon doesn't support SQLForeginKeys

Fix the error of the old implementation and add test cases for it.


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: 
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/533b3834
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/533b3834
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/533b3834

Branch: refs/heads/master
Commit: 533b3834fd5b65028cdc812bf273eabe7de8ec35
Parents: 708f58f
Author: Weiqing Xu <weiqing...@esgyn.cn>
Authored: Wed Sep 28 00:53:41 2016 +0000
Committer: Weiqing Xu <weiqing...@esgyn.cn>
Committed: Thu Oct 13 12:08:44 2016 +0000

----------------------------------------------------------------------
 .../odbc/src/odbc/nsksrvrcore/srvrothers.cpp    |  39 ++--
 .../org/trafodion/jdbc_test/TestForeignKey.java | 224 +++++++++++++++++++
 2 files changed, 243 insertions(+), 20 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/533b3834/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp
----------------------------------------------------------------------
diff --git a/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp 
b/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp
index c8bf67d..b24b7e7 100644
--- a/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp
+++ b/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp
@@ -4963,17 +4963,17 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
             char fkexpSchemaNm[MAX_ANSI_NAME_LEN + 1];
             char fkexpTableNm[MAX_ANSI_NAME_LEN + 1];
 
-            if (!checkIfWildCard(fkcatalogNm, fkcatalogNmNoEsc) ||
-                !checkIfWildCard(fkschemaNm, fkschemaNmNoEsc)   ||
-                !checkIfWildCard(fktableNm, fktableNmNoEsc))
-            {
-                exception_->exception_nr = 
odbc_SQLSvc_GetSQLCatalogs_ParamError_exn_;
-                exception_->u.ParamError.ParamDesc = 
SQLSVC_EXCEPTION_WILDCARD_NOT_SUPPORTED;
-                goto MapException;
-            }
+            if (strcmp(fktableNm, "") == 0)
+                strcpy((char *)fktableNm, "%");
+            if (strcmp(fkschemaNm, "") == 0)
+                strcpy((char *)fkschemaNm, "%");
+
+            convertWildcard(metadataId, TRUE, fkcatalogNm, fkexpCatalogNm);
+            convertWildcardNoEsc(metadataId, TRUE, fkcatalogNm, 
fkcatalogNmNoEsc);
 
-            convertWildcard(metadataId, TRUE, fkschemaNm, fkexpCatalogNm);
+            convertWildcard(metadataId, TRUE, fkschemaNm, fkexpSchemaNm);
             convertWildcardNoEsc(metadataId, TRUE, fkschemaNm, 
fkschemaNmNoEsc);
+
             convertWildcard(metadataId, TRUE, fktableNm, fkexpTableNm);
             convertWildcardNoEsc(metadataId, TRUE, fktableNm, fktableNmNoEsc);
 
@@ -4982,7 +4982,7 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
                     "cast(PKCO.CATALOG_NAME as varchar(128)) PKTABLE_CAT, "
                     "cast(PKCO.SCHEMA_NAME as varchar(128)) PKTABLE_SCHEM, "
                     "cast(PKCO.TABLE_NAME as varchar(128)) PKTABLE_NAME, "
-                    "cast(PKCO.COLUMN_NAME as varchar(128)) PKCOLUMNS_NAME, "
+                    "cast(PKCO.COLUMN_NAME as varchar(128)) PKCOLUMN_NAME, "
                     "cast(FKCO.CATALOG_NAME as varchar(128)) FKTABLE_CAT, "
                     "cast(PKCO.SCHEMA_NAME as varchar(128)) FKTABLE_SCHEM, "
                     "cast(FKCO.TABLE_NAME as varchar(128)) FKTABLE_NAME, "
@@ -4990,9 +4990,9 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
                     "cast(FKKV.ORDINAL_POSITION as smallint) KEY_SEQ, "
                     "cast(0 as smallint) update_rule, " // not support
                     "cast(0 as smallint) delete_rule, " // not support
-                    "cast(PKCO.COLUMN_NAME as varchar(128)) fk_name, "
-                    "cast(PKCO.COLUMN_NAME as varchar(128)) PK_NAME, "
-                    "cast(0 as smallint) DEFERRABILITY "
+                    "cast(FKKV.CONSTRAINT_NAME as varchar(128)) FK_NAME, "
+                    "cast(PKKV.CONSTRAINT_NAME as varchar(128)) PK_NAME, "
+                    "cast(0 as smallint) DEFERRABILITY " // not support
                     "from "
                     "TRAFODION.\"_MD_\".REF_CONSTRAINTS_VIEW rcv, "
                     "TRAFODION.\"_MD_\".KEYS_VIEW PKKV, "
@@ -5000,22 +5000,21 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
                     "TRAFODION.\"_MD_\".COLUMNS_VIEW PKCO, "
                     "TRAFODION.\"_MD_\".COLUMNS_VIEW FKCO "
                     "where "
-                    "PKKV.CONSTRAINT_NAME = rcv.CONSTRAINT_NAME "
-                    "and FKKV.CONSTRAINT_NAME = rcv.UNIQUE_CONSTRAINT_NAME "
+                    "FKKV.CONSTRAINT_NAME = rcv.CONSTRAINT_NAME "
+                    "and PKKV.CONSTRAINT_NAME = rcv.UNIQUE_CONSTRAINT_NAME "
                     "and PKCO.TABLE_NAME = PKKV.TABLE_NAME "
                     "and FKCO.TABLE_NAME = FKKV.TABLE_NAME "
                     "and PKCO.COLUMN_NAME = PKKV.COLUMN_NAME "
                     "and FKCO.COLUMN_NAME = FKKV.COLUMN_NAME "
-                    "and (rcv.TABLE_NAME = '%s' or trim(rcv.TABLE_NAME) LIKE 
'%s' ESCAPE '\\') "
                     "and (PKCO.SCHEMA_NAME = '%s' or trim(PKCO.SCHEMA_NAME) 
LIKE '%s' ESCAPE '\\') "
                     "and (PKCO.TABLE_NAME = '%s' or trim(PKCO.TABLE_NAME) LIKE 
'%s' ESCAPE '\\') "
                     "and (FKCO.SCHEMA_NAME = '%s' or trim(FKCO.SCHEMA_NAME) 
LIKE '%s' ESCAPE '\\') "
-                    "and (FKCO.TABLE_NAME = '%s' or trim(FKCO.TABLE_NAME) LIKE 
'%s' ESCAPE '\\');",
-                tableNmNoEsc, expTableNm,
+                    "and (FKCO.TABLE_NAME = '%s' or trim(FKCO.TABLE_NAME) LIKE 
'%s' ESCAPE '\\') "
+                    "FOR READ UNCOMMITTED ACCESS ORDER BY 1, 2, 3, 5, 6, 7, 
9;",
                 schemaNmNoEsc, expSchemaNm,
                 tableNmNoEsc, expTableNm,
-                fkschemaNm, fkexpSchemaNm,
-                fktableNm, fkexpTableNm
+                fkschemaNmNoEsc, fkexpSchemaNm,
+                fktableNmNoEsc, fkexpTableNm
                     );
             break;
         case SQL_API_SQLSTATISTICS:

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/533b3834/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestForeignKey.java
----------------------------------------------------------------------
diff --git 
a/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestForeignKey.java
 
b/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestForeignKey.java
new file mode 100644
index 0000000..d6c2175
--- /dev/null
+++ 
b/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestForeignKey.java
@@ -0,0 +1,224 @@
+// @@@ START COPYRIGHT @@@
+//
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+//
+// @@@ END COPYRIGHT @@@
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Statement;
+
+import javax.swing.text.Utilities;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import static org.junit.Assert.*;
+
+public class TestForeignKey {
+       private static final String PKTABLE1 = "PKTABLE1";
+       private static final String PK1 = "PK1";
+       private static final String PKTABLE2 = "PKTABLE2";
+       private static final String PK2 = "PK2";
+       private static final String FKTABLE1 = "FKTABLE1";
+       private static final String FK1 ="FK1";
+       private static final String FK2 = "FK2";
+
+       private static final String FKTABLE2 = "FKTABLE2";
+       private static final String FK21 = "FK21";
+       private static final String FK22 = "FK22";
+
+       private static final String strCreatePKTABLE1Query = "CREATE TABLE " + 
Utils.schema + "." + PKTABLE1 + "( "
+                       + PK1 + " INT NOT NULL PRIMARY KEY)";
+       private static final String strDropPKTABLE1Query = "DROP TABLE " + 
Utils.schema + "." + PKTABLE1;
+
+       private static final String strCreatePKTABLE2Query = "CREATE TABLE " + 
Utils.schema + "." + PKTABLE2 + "( "
+                       + PK2 + " INT NOT NULL PRIMARY KEY)";
+       private static final String strDropPKTABLE2Query = "DROP TABLE " + 
Utils.schema + "." + PKTABLE2;
+
+       private static final String strCreateFKTABLE1Query = "CREATE TABLE " + 
Utils.schema + "." + FKTABLE1 + "( "
+                       + FK1 + " INT NOT NULL, "
+                       + FK2 + " INT NOT NULL, "
+                       + "FOREIGN KEY (" + FK1 + ") REFERENCES " + 
Utils.schema + "." + PKTABLE1 + "(" + PK1 + "), "
+                       + "FOREIGN KEY (" + FK2 + ") REFERENCES " + 
Utils.schema + "." + PKTABLE2 + "(" + PK2 + "))";
+       private static final String strDropFKTABLE1Query = "DROP TABLE " + 
Utils.schema + "." + FKTABLE1;
+
+       private static final String strCreateFKTABLE2Query = "CREATE TABLE " + 
Utils.schema + "." + FKTABLE2 + "( "
+                       + FK21 + " INT NOT NULL, "
+                       + FK22 + " INT NOT NULL, "
+                       + "FOREIGN KEY (" + FK21 + ") REFERENCES " + 
Utils.schema + "." + PKTABLE1 + "(" + PK1 + "), "
+                       + "FOREIGN KEY (" + FK22 + ") REFERENCES " + 
Utils.schema + "." + PKTABLE2 + "(" + PK2 + "))";
+       private static final String strDropFKTABLE2Query = "DROP TABLE " + 
Utils.schema + "." + FKTABLE2;
+
+       private static Connection _conn;
+       
+       @BeforeClass
+       public static void doTestSuiteSetup() throws Exception {
+               try{
+                       _conn = DriverManager.getConnection(Utils.url, 
Utils.usr, Utils.pwd);
+                       Statement stmt = _conn.createStatement();
+                       
+                       stmt.execute(strCreatePKTABLE1Query);
+                       stmt.execute(strCreatePKTABLE2Query);
+                       stmt.execute(strCreateFKTABLE1Query);
+                       stmt.execute(strCreateFKTABLE2Query);
+               }
+               catch (Exception e) {
+                       System.out.println(e.getMessage());
+                       e.printStackTrace();
+               }
+       }
+       
+       @Test
+       public void testGetImportedKeys() {
+               ForeignKeyInfo[] expFkInfo = {
+                               new ForeignKeyInfo("TRAFODION", Utils.schema, 
PKTABLE1, PK1, "TRAFODION", Utils.schema, FKTABLE1, FK1, (short) 1),
+                               new ForeignKeyInfo("TRAFODION", Utils.schema, 
PKTABLE2, PK2, "TRAFODION", Utils.schema, FKTABLE1, FK2, (short)1)
+               };
+               
+               try {
+                       DatabaseMetaData metaData = _conn.getMetaData();
+                       ResultSet rs = metaData.getImportedKeys("TRAFODION", 
Utils.schema, FKTABLE1);
+                       int rowNum = 0;
+                       while(rs.next()) {
+                               compareForeignkeyWithExp("testGetImportedKeys", 
rowNum + 1, rs, expFkInfo[rowNum]);
+                               rowNum += 1;
+                       }
+                       assertEquals(rowNum, 2);
+               } catch (Exception e) {
+                       e.printStackTrace();
+               }
+       }
+       
+       @Test
+       public void testGetExportedKeys() {
+               ForeignKeyInfo[] expFkInfo = {
+                               new ForeignKeyInfo("TRAFODION", Utils.schema, 
PKTABLE1, PK1, "TRAFODION", Utils.schema, FKTABLE1, FK1, (short)1),
+                               new ForeignKeyInfo("TRAFODION", Utils.schema, 
PKTABLE1, PK1, "TRAFODION", Utils.schema, FKTABLE2, FK21, (short)1)
+               };
+               
+               try {
+                       DatabaseMetaData metaData = _conn.getMetaData();
+                       ResultSet rs = metaData.getExportedKeys("TRAFODION", 
Utils.schema, PKTABLE1);
+                       int rowNum = 0;
+                       while(rs.next()) {
+                               compareForeignkeyWithExp("testGetExportedKeys", 
rowNum + 1, rs, expFkInfo[rowNum]);
+                               rowNum += 1;
+                       }
+                       assertEquals(rowNum, 2);
+               } catch (Exception e) {
+                       e.printStackTrace();
+               }               
+       }
+       
+       @Test
+       public void testGetCrossReference() {
+               ForeignKeyInfo[] expFkInfo = {
+                               new ForeignKeyInfo("TRAFODION", Utils.schema, 
PKTABLE1, PK1, "TRAFODION", Utils.schema, FKTABLE1, FK1, (short)1)
+               };
+               
+               try {
+                       DatabaseMetaData metaData = _conn.getMetaData();
+                       ResultSet rs = metaData.getCrossReference("TRAFODION", 
Utils.schema, PKTABLE1, "TRAFODION", Utils.schema, FKTABLE1);
+                       int rowNum = 0;
+                       while(rs.next()) {
+                               
compareForeignkeyWithExp("testGetCrossReference", rowNum + 1, rs, 
expFkInfo[rowNum]);
+                               rowNum += 1;
+                       }
+                       assertEquals(rowNum, 1);
+               } catch (Exception e) {
+                       e.printStackTrace();
+               }               
+       }
+
+       @AfterClass
+       public static void cleanTable() {
+               try (Statement stmt = _conn.createStatement()){
+                       stmt.execute(strDropFKTABLE1Query);
+                       stmt.execute(strDropFKTABLE2Query);
+                       stmt.execute(strDropPKTABLE1Query);
+                       stmt.execute(strDropPKTABLE2Query);
+               } catch (Exception e) {
+                       System.out.println(e.getMessage());
+                       e.printStackTrace();
+               }
+               
+               try {
+                       _conn.close();
+               } catch (Exception e) {
+                       System.out.println(e.getMessage());
+                       e.printStackTrace();
+               }
+       }
+       
+       private class ForeignKeyInfo {
+               public String pkTableCat;
+               public String pkTableSchem;
+               public String pkTableName;
+               public String pkColumnName;
+               public String fkTableCat;
+               public String fkTableSchem;
+               public String fkTableName;
+               public String fkColumnName;
+               public short keySeq;
+               
+               public ForeignKeyInfo (String pkTableCat, String pkTableSchem, 
String pkTableName, String pkColumnName,
+                               String fkTableCat, String fkTableSchem, String 
fkTableName, String fkColumnName, 
+                               short keySeq) {
+                       this.pkTableCat = pkTableCat;
+                       this.pkTableSchem = pkTableSchem;
+                       this.pkTableName = pkTableName;
+                       this.pkColumnName = pkColumnName;
+                       this.fkTableCat = fkTableCat;
+                       this.fkTableSchem = fkTableSchem;
+                       this.fkTableName = fkTableName;
+                       this.fkColumnName = fkColumnName;
+                       this.keySeq = keySeq;
+               }
+       }
+
+       private void compareForeignkeyWithExp(String methodName, int rowNum, 
ResultSet rs, ForeignKeyInfo fkInfo) {
+               try {
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " PKTABLE_CAT", fkInfo.pkTableCat,
+                                       rs.getString("PKTABLE_CAT"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " PKTABLE_SCHEM", fkInfo.pkTableSchem,
+                                       rs.getString("PKTABLE_SCHEM"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " PKTABLE_NAME", fkInfo.pkTableName,
+                                       rs.getString("PKTABLE_NAME"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " PKCOLUMNS_NAME", fkInfo.pkColumnName,
+                                       rs.getString("PKCOLUMN_NAME"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " FKTABLE_CAT", fkInfo.fkTableCat,
+                                       rs.getString("FKTABLE_CAT"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " FKTABLE_SCHEM", fkInfo.fkTableSchem,
+                                       rs.getString("FKTABLE_SCHEM"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " FKTABLE_NAME", fkInfo.fkTableName,
+                                       rs.getString("FKTABLE_NAME"));
+                       assertEquals(methodName + " rowNum " + 
Integer.toString(rowNum) + " FKCOLUMN_NAME", fkInfo.fkColumnName,
+                                       rs.getString("FKCOLUMN_NAME"));
+                       // Skip KEY_SEQ, UPDATE_RULE, DELETE_RULE, 
DEFERRABILITY now , since
+                       // them was been supported now.
+
+                       // Skip PK_NAME and FK_NAME
+               } catch (Exception e) {
+                       e.printStackTrace();
+               }
+       }
+}

Reply via email to