001: /*
002: * Copyright 2006-2007 The Kuali Foundation.
003: *
004: * Licensed under the Educational Community License, Version 1.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.opensource.org/licenses/ecl1.php
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package org.kuali.kfs.database;
017:
018: import java.util.Iterator;
019: import java.util.List;
020: import java.util.Map;
021:
022: import org.kuali.core.dbplatform.KualiDBPlatformOracle;
023: import org.kuali.core.dbplatform.RawSQL;
024: import org.kuali.core.util.UnitTestSqlDao;
025: import org.kuali.kfs.context.KualiTestBase;
026: import org.kuali.kfs.context.SpringContext;
027: import org.kuali.test.ConfigureContext;
028:
029: @ConfigureContext
030: @RawSQL
031: public class MismatchedForeignKeyTest extends KualiTestBase {
032: private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger
033: .getLogger(MismatchedForeignKeyTest.class);
034:
035: protected UnitTestSqlDao unitTestSqlDao = null;
036:
037: public MismatchedForeignKeyTest() {
038: super ();
039: }
040:
041: @Override
042: protected void setUp() throws Exception {
043: super .setUp();
044:
045: LOG.debug("setUp() starting");
046: unitTestSqlDao = SpringContext.getBean(UnitTestSqlDao.class);
047: }
048:
049: /**
050: * select occurrences where foreign key columns do not match the parent columns in data types or sizes.
051: */
052: public void testExistingMismatchedForeignKeys() {
053: if (unitTestSqlDao.getDbPlatform() instanceof KualiDBPlatformOracle) {
054: final List rows = unitTestSqlDao
055: .sqlSelect("SELECT c.table_name AS child_table_name, cc.column_name AS child_column_name, "
056: + "rc.table_name AS parent_table_name, rcc.column_name AS parent_column_name, "
057: + "DECODE( cols.data_type, 'NUMBER', DECODE( cols.data_precision, NULL, cols.data_type, "
058: + "cols.data_type||'('||cols.DATA_PRECISION||','||cols.DATA_SCALE||')' ), 'CHAR', cols.data_type||'('||cols.data_length||')', "
059: + "'VARCHAR2', cols.data_type||'('||cols.data_length||')', cols.data_type ) AS child_data_type, "
060: + "DECODE( cols.data_type, 'NUMBER', DECODE( rcols.data_precision, NULL, rcols.data_type, "
061: + "rcols.data_type||'('||rcols.DATA_PRECISION||','||rcols.DATA_SCALE||')' ), "
062: + "'CHAR', rcols.data_type||'('||rcols.data_length||')', 'VARCHAR2', rcols.data_type||'('||rcols.data_length||')', "
063: + "rcols.data_type ) AS parent_data_type "
064: + "FROM user_constraints c, user_constraints rc, user_cons_columns cc, user_cons_columns rcc, user_tab_columns cols, user_tab_columns rcols "
065: + "WHERE c.constraint_type = 'R' AND cc.constraint_name = c.constraint_name AND rcc.constraint_name = c.r_constraint_name "
066: + "AND rcc.position = cc.position AND cols.table_name = c.table_name AND cols.column_name = cc.column_name "
067: + "AND rc.constraint_name = c.r_constraint_name AND rcols.table_name = rc.table_name AND rcols.column_name = rcc.column_name "
068: + "AND ( cols.data_type <> rcols.data_type OR NVL( cols.data_length, 0 ) <> NVL( rcols.data_length, 0 ) "
069: + "OR NVL( cols.data_precision, 0 ) <> NVL( rcols.data_precision, 0 ) OR NVL( cols.data_scale, 0 ) <> NVL( rcols.data_scale, 0 ))");
070:
071: StringBuffer failureMessage = new StringBuffer(
072: "Foreign Key Mismatches: ");
073:
074: if (rows.size() > 0) {
075: for (Iterator iter = rows.iterator(); iter.hasNext();) {
076: Map element = (Map) iter.next();
077: failureMessage
078: .append("\n\tCHILD table/column/data-type: ");
079: failureMessage.append(
080: element.get("CHILD_TABLE_NAME"))
081: .append("/").append(
082: element.get("CHILD_COLUMN_NAME"))
083: .append("/").append(
084: element.get("CHILD_DATA_TYPE"));
085: failureMessage
086: .append(" -- PARENT table/column/data-type: ");
087: failureMessage.append(
088: element.get("PARENT_TABLE_NAME")).append(
089: "/").append(
090: element.get("PARENT_COLUMN_NAME")).append(
091: "/")
092: .append(element.get("PARENT_DATA_TYPE"));
093: }
094: failureMessage.append("\n");
095: }
096:
097: assertEquals(failureMessage.toString(), 0, rows.size());
098: } else {
099: System.err
100: .println("Unable to test as no SQL available to test for this platform.");
101: }
102: }
103:
104: }
|