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