dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test/TestForeignKey.java

// @@@ 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();
		}
	}
}