You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by su...@apache.org on 2016/08/05 22:10:32 UTC
[2/6] incubator-trafodion git commit: add JDBC test for getIndexInfo
add JDBC test for getIndexInfo
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/3032a0c9
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/3032a0c9
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/3032a0c9
Branch: refs/heads/master
Commit: 3032a0c9718c529d4086dea775f9cdaa0d2a4b41
Parents: 7ba2118
Author: Weiqing Xu <we...@esgyn.cn>
Authored: Mon Aug 1 05:14:49 2016 +0000
Committer: Weiqing Xu <we...@esgyn.cn>
Committed: Mon Aug 1 05:14:49 2016 +0000
----------------------------------------------------------------------
.../odbc/src/odbc/nsksrvrcore/srvrothers.cpp | 46 +++---
.../trafodion/jdbc_test/TestGetIndexInfo.java | 161 +++++++++++++++++++
2 files changed, 186 insertions(+), 21 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3032a0c9/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 7ee9e56..3481d4b 100644
--- a/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp
+++ b/core/conn/odbc/src/odbc/nsksrvrcore/srvrothers.cpp
@@ -4945,16 +4945,16 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
"cast('%s' as varchar(128)) TABLE_CAT, "
"cast(trim(ob.SCHEMA_NAME) as varchar(128)) TABLE_SCHEM, "
"cast(trim(ob.OBJECT_NAME) as varchar(128)) TABLE_NAME, "
- "cast(0 as smallint) NON_UNIQUE, " // not support
- "cast('' as varchar(128)) INDEX_QUALIFIER, " // not support
- "cast('' as varchar(128)) INDEX_NAME, "
- "cast(0 as smallint) TYPE, " // not support
- "cast(co.column_number as smallint) ORDINAL_POSITION, "
+ "cast(NULL as smallint) NON_UNIQUE, " // return NULL if TYPE is SQL_TABLE_STAT
+ "cast(NULL as varchar(128)) INDEX_QUALIFIER, " // return NULL if TYPE is SQL_TABLE_STAT
+ "cast(NULL as varchar(128)) INDEX_NAME, " // return NULL if TYPE is SQL_TABLE_STAT
+ "cast(0 as smallint) TYPE, " // TYPE is SQL_TABLE_STAT
+ "cast(NULL as smallint) ORDINAL_POSITION, " // return NULL if TYPE is SQL_TABLE_STAT
"cast(trim(co.COLUMN_NAME) as varchar(128)) COLUMN_NAME, "
- "cast('' as char(1)) ASC_OR_DES, "
- "cast(sb.rowcount as integer) CARDINALITY, "
- "cast(0 as integer) PAGES, " // not support
- "cast('' as varchar(128)) FILTER_CONDITION " // not support
+ "cast(NULL as char(1)) ASC_OR_DESC, " // return NULL if TYPE is SQL_TABLE_STAT
+ "cast(sb.rowcount as integer) CARDINALITY, " // number of rows
+ "cast(NULL as integer) PAGES, " // not support
+ "cast(NULL as varchar(128)) FILTER_CONDITION " // not support
"from "
"TRAFODION.\"_MD_\".OBJECTS ob, "
"TRAFODION.\"_MD_\".COLUMNS co, "
@@ -4971,18 +4971,18 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
"union "
"select "
"cast('%s' as varchar(128)) TABLE_CAT, "
- "cast(trim(ob.SCHEMA_NAME) as varchar(128)) TABLE_SCHEM, "
- "cast(trim(ob.OBJECT_NAME) as varchar(128)) TABLE_NAME, "
+ "cast(trim(ob_table.SCHEMA_NAME) as varchar(128)) TABLE_SCHEM, "
+ "cast(trim(ob_table.OBJECT_NAME) as varchar(128)) TABLE_NAME, "
"cast(idx.is_unique as smallint) NON_UNIQUE, "
- "cast('' as varchar(128)) INDEX_QUALIFIER, "
- "cast(trim(ob_table.OBJECT_NAME) as varchar(128)) INDEX_NAME, "
- "cast(3 as smallint) TYPE, "
+ "cast(NULL as varchar(128)) INDEX_QUALIFIER, " // not support
+ "cast(trim(ob.OBJECT_NAME) as varchar(128)) INDEX_NAME, "
+ "cast(3 as smallint) TYPE, " // SQL_INDEX_OTHER
"cast(0 as smallint) ORDINAL_POSITION, "
- "cast('' as varchar(128)) COLUMN_NAME, "
- "cast('' as char(1)) ASC_OR_DES, "
- "cast(0 as integer) CARDINALITY, "
- "cast(0 as integer) PAGES, "
- "cast('' as varchar(128)) FILTER_CONDITION "
+ "cast('' as varchar(128)) COLUMN_NAME, " // return an empty string if the expression cannot be determined.
+ "cast(NULL as char(1)) ASC_OR_DESC, " // not subsequent
+ "cast(NULL as integer) CARDINALITY, "
+ "cast(NULL as integer) PAGES, "
+ "cast(NULL as varchar(128)) FILTER_CONDITION "
"from "
"TRAFODION.\"_MD_\".OBJECTS ob, "
"TRAFODION.\"_MD_\".INDEXES idx, "
@@ -4994,14 +4994,17 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
"and idx.BASE_TABLE_UID=ob_table.OBJECT_UID "
"and (ob_table.SCHEMA_NAME = '%s' or trim(ob_table.SCHEMA_NAME) LIKE '%s' ESCAPE '\\') "
"and (ob_table.OBJECT_NAME = '%s' or trim(ob_table.OBJECT_NAME) LIKE '%s' ESCAPE '\\') "
- "and (ob_table.OBJECT_TYPE in ('BT', 'VI'));",
+ "and (ob_table.OBJECT_TYPE in ('BT', 'VI')) "
+ "%s "
+ "ORDER BY 1, 2, 3, 7, 9, 6 ;",
tableParam[0],
inputParam[0],
inputParam[0], inputParam[1],
inputParam[2], inputParam[3],
tableParam[0],
inputParam[0], inputParam[1],
- inputParam[2], inputParam[3]
+ inputParam[2], inputParam[3],
+ uniqueness == 1 ? "" : "and idx.is_unique=1"
);
break;
@@ -5031,6 +5034,7 @@ odbc_SQLSvc_GetSQLCatalogs_sme_(
// Temporary solution - bypass checks on metadata tables
unsigned int savedParserFlags = 0;
+
SQL_EXEC_GetParserFlagsForExSqlComp_Internal(savedParserFlags);
try
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3032a0c9/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestGetIndexInfo.java
----------------------------------------------------------------------
diff --git a/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestGetIndexInfo.java b/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestGetIndexInfo.java
new file mode 100644
index 0000000..6605328
--- /dev/null
+++ b/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestGetIndexInfo.java
@@ -0,0 +1,161 @@
+package src.test.java.org.trafodion.jdbc_test;
+
+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 org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import static org.junit.Assert.*;
+
+public class TestGetIndexInfo {
+
+ private static final String INDEX_INFO_TEST_TABLE = "INDEX_INFO_TEST_TABLE";
+
+ private static final String strCreateTableQuery = "CREATE TABLE " + INDEX_INFO_TEST_TABLE + "(C1 INT, C2 INT)";
+ private static final String strInsertQuery = "INSERT INTO " + INDEX_INFO_TEST_TABLE + " (C1, C2) VALUES (?, ?)";
+ private static final String strUpdateStatisticsQuery = "UPDATE STATISTICS FOR TABLE " + INDEX_INFO_TEST_TABLE + " ON (C1, C2)";
+ private static final String strDropTableQuery = "DROP TABLE " + INDEX_INFO_TEST_TABLE;
+ private static final String INDEX_C1_NAME = INDEX_INFO_TEST_TABLE + "_INDEX";
+
+ private static final String strCreateIndexQuery = "CREATE INDEX " + INDEX_C1_NAME +" on " + INDEX_INFO_TEST_TABLE + "(C1)";
+
+ private static Connection _conn = null;
+
+ @BeforeClass
+ public static void doTestSuiteSetup() throws Exception {
+ try {
+ _conn = DriverManager.getConnection(Utils.url, Utils.usr, Utils.pwd);
+ Statement stmt = _conn.createStatement();
+ stmt.execute(strCreateTableQuery);
+
+ PreparedStatement pstmt = _conn.prepareStatement(strInsertQuery);
+ int[][] testValues = {
+ {1, 2},
+ {10, 3},
+ {2, 2}
+ };
+ System.out.println(testValues.length);
+ for (int i = 0; i < testValues.length; i++) {
+ pstmt.setInt(1, testValues[i][0]);
+ pstmt.setInt(2, testValues[i][1]);
+ pstmt.addBatch();
+ }
+ pstmt.executeBatch();
+ pstmt.close();
+
+ // create index
+ stmt.execute(strCreateIndexQuery);
+
+ // update statistics on the table
+ stmt.execute(strUpdateStatisticsQuery);
+
+ stmt.close();
+ }
+ catch (Exception e) {
+ System.out.println(e.getMessage());
+ e.printStackTrace();
+ } finally {
+ }
+ }
+
+ @Test
+ public void testGetNoneUniqueIndexInfo() {
+ IndexInfo[] expIndexInfo = {
+ new IndexInfo("TRAFODION", "SEABASE", INDEX_INFO_TEST_TABLE, false, (String)null, (String)null, (short)0, (short)0, "C1", 0, 3, (short)0, (String)null),
+ new IndexInfo("TRAFODION", "SEABASE", INDEX_INFO_TEST_TABLE, false, (String)null, (String)null, (short)0, (short)0, "C2", 0, 3, (short)0, (String)null),
+ new IndexInfo("TRAFODION", "SEABASE", INDEX_INFO_TEST_TABLE, false, (String)null, INDEX_C1_NAME, (short)3, (short)0, "", 0, 0, (short)0, (String)null)
+ };
+
+ try {
+ DatabaseMetaData meta = _conn.getMetaData();
+ ResultSet indexInformation = meta.getIndexInfo(_conn.getCatalog(), null, INDEX_INFO_TEST_TABLE, false, false);
+
+ int rowNum = 0;
+ while(indexInformation.next()) {
+ compareInfoWithExp("testGetUniqueIndexInfo", rowNum + 1, indexInformation, expIndexInfo[rowNum]);
+ rowNum += 1;
+ }
+ assertEquals(rowNum, 3);
+ } catch(Exception e) {
+ System.out.println(e.getMessage());
+ e.printStackTrace();
+ }
+ }
+
+ @AfterClass
+ public static void cleanTable() {
+ try ( Statement stmt = _conn.createStatement() ) {
+ stmt.execute(strDropTableQuery);
+ } catch(Exception e) {
+ // do nothing
+ }
+
+ try {
+ _conn.close();
+ } catch(Exception e) {
+
+ }
+ }
+
+ private class IndexInfo {
+ public String dbCatalog = "TRAFODION";
+ public String dbSchema = "SEABASE";
+ public String dbTableName;
+ public boolean dbNoneUnique = false;
+ public String dbIndexQualifier;
+ public String dbIndexName;
+ public short dbType;
+ public short dbOrdinalPosition;
+ public String dbColumnName;
+ public String dbAscOrDesc;
+ public int dbCardinality;
+ public int dbPages;
+ public String dbFilterCondition;
+
+ public IndexInfo(String dbCatalog, String dbSchema, String dbTableName, boolean dbNoneUnique, String dbIndexQualifier, String dbIndexName,
+ short dbType, short dbOrdinalPosition, String dbColumnName, int dbAscOrdesc, int dbCardinality, int dbPages, String dbFilterCondition) {
+ this.dbCatalog = dbCatalog;
+ this.dbSchema = dbSchema;
+ this.dbTableName = dbTableName;
+ this.dbNoneUnique = dbNoneUnique;
+ this.dbIndexQualifier = dbIndexQualifier;
+ this.dbIndexName = dbIndexName;
+ this.dbType = dbType;
+ this.dbOrdinalPosition = dbOrdinalPosition;
+ this.dbColumnName = dbColumnName;
+ this.dbCardinality = dbCardinality;
+ this.dbPages = dbPages;
+ this.dbFilterCondition = dbFilterCondition;
+ }
+ }
+
+ private void compareInfoWithExp(String methondName, int rowNum, ResultSet rs, IndexInfo indexInfo) {
+ try {
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbCatalog ", indexInfo.dbCatalog, rs.getString("TABLE_CAT"));
+ String currentSchema = _conn.getSchema();
+
+ // Since we may run the test in different Schema, so compare the result to the current schema of the connection here.
+ assertEquals(methondName + " rowNUm " + Integer.toString(rowNum) + " dbSchem ", currentSchema, rs.getString("TABLE_SCHEM"));
+
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbTableName ", indexInfo.dbTableName, rs.getString("TABLE_NAME"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbNoneUnique ", indexInfo.dbNoneUnique, rs.getBoolean("NON_UNIQUE"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbIndexQualifier ", indexInfo.dbIndexQualifier, rs.getString("INDEX_QUALIFIER"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbIndexName ", indexInfo.dbIndexName, rs.getString("INDEX_NAME"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbType ", indexInfo.dbType, rs.getShort("TYPE"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbOridinalPosition ", indexInfo.dbOrdinalPosition, rs.getShort("ORDINAL_POSITION"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbColumnName ", indexInfo.dbColumnName, rs.getString("COLUMN_NAME"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbAscOrDesc ", indexInfo.dbAscOrDesc, rs.getString("ASC_OR_DESC"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbCardinality ", indexInfo.dbCardinality, rs.getInt("CARDINALITY"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbPages ", indexInfo.dbPages, rs.getInt("PAGES"));
+ assertEquals(methondName + " rowNum " + Integer.toString(rowNum) + " dbFilterCondition ", indexInfo.dbFilterCondition, rs.getString("FILTER_CONDITION"));
+ } catch (Exception e) {
+ System.out.println(e.getMessage());
+ e.printStackTrace();
+ }
+ }
+}
\ No newline at end of file