You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2016/10/18 20:46:51 UTC
[1/2] incubator-trafodion git commit: [TRAFODION-1698]ODBC: trafodon
doesn't support SQLForeginKeys
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 <we...@esgyn.cn>
Authored: Wed Sep 28 00:53:41 2016 +0000
Committer: Weiqing Xu <we...@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();
+ }
+ }
+}
[2/2] incubator-trafodion git commit: Merge [TRAFODION-1698] ODBC
support for SQLForeignKeys
Posted by db...@apache.org.
Merge [TRAFODION-1698] ODBC support for SQLForeignKeys
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/cc915f3f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/cc915f3f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/cc915f3f
Branch: refs/heads/master
Commit: cc915f3ff9644e6b80f75f3a49fc1060da1712e8
Parents: 8805eb2 533b383
Author: Dave Birdsall <db...@apache.org>
Authored: Tue Oct 18 20:45:16 2016 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Tue Oct 18 20:45:16 2016 +0000
----------------------------------------------------------------------
.../odbc/src/odbc/nsksrvrcore/srvrothers.cpp | 39 ++--
.../org/trafodion/jdbc_test/TestForeignKey.java | 224 +++++++++++++++++++
2 files changed, 243 insertions(+), 20 deletions(-)
----------------------------------------------------------------------