How to compare schema between two different database users?

Question

I'm developing a custom nifi processor to compare schema of two different database users in Oracle DB. So this custom nifi processor will be able validate the schema between different db users, were we have to choose one user as Source and other one as Target.

If the source has a table which is not available in the target, then the processor will generate the create script for that table.If source and target has a table with same name, then it will compare the columns.

If the source table has a column name which is not there in target table, processor will generate an alter script(ADD). If the same columns are present, then it will compare the datatype and length. If there is a mismatch in datatype and length, processor will generate another alter the script (Modify).

The approach I took for doing the same is, get the table details from the table user_tab_columns, it will give all the column names, datatype,data length, precision, scale, etc.

So I'll do the same approach for my two DB users. Then I will compare each and every single value for the source and target, if there's mismatch I'll do the required operations.

I just wanted to know that, is there a different approach for performing the schema comparison between different DB users, and also how I can improve the current approach.

private void validateCurrentTable(XelerateJsonTableDetail currentTable,DataValidationDetails dataValidationDetails) throws SQLException {

    boolean isDataAvailable = true;
    boolean validationFlag;
    List<DataValidationMetaDetails> sourceMetaDetailsList = new ArrayList<DataValidationMetaDetails>();
    List<DataValidationMetaDetails> targetMetaDetailsList = new ArrayList<DataValidationMetaDetails>();

    ResultSet metaDataSource = getColumns(sourceConnection,currentTable.getTableName());

    while(isDataAvailable){
        DataValidationMetaDetails sourceMetaDetails = new DataValidationMetaDetails() ;
        if(!metaDataSource.next()){
            isDataAvailable = false;
        }else{
            sourceMetaDetails.settableName(metaDataSource.getString("TABLE_NAME"));
            sourceMetaDetails.setColumnName(metaDataSource.getString("COLUMN_NAME"));
            sourceMetaDetails.setdataType(metaDataSource.getString("DATA_TYPE"));
            sourceMetaDetails.setdataLength(metaDataSource.getString("DATA_LENGTH"));
            sourceMetaDetails.setdataPrescision(checkNull(metaDataSource.getString("DATA_PRECISION")));
            sourceMetaDetails.setdataScale(checkNull(metaDataSource.getString("DATA_SCALE")));
            if("C".equals(metaDataSource.getString("CHAR_USED"))){
                sourceMetaDetails.setDataCharLength(metaDataSource.getString("CHAR_LENGTH"));
                sourceMetaDetails.setDataCharUsed(metaDataSource.getString("CHAR_USED"));
            }
            sourceMetaDetailsList.add(sourceMetaDetails);
        }
    }

    isDataAvailable = true;
    ResultSet metaDataTarget = getColumns(targetConnection,currentTable.getTableName());

    while(isDataAvailable){
        DataValidationMetaDetails targetMetaDetails = new DataValidationMetaDetails() ;
        if(!metaDataTarget.next()){
            isDataAvailable = false;
        }else{
            targetMetaDetails.settableName(metaDataTarget.getString("TABLE_NAME"));
            targetMetaDetails.setColumnName(metaDataTarget.getString("COLUMN_NAME"));
            targetMetaDetails.setdataType(metaDataTarget.getString("DATA_TYPE"));
            targetMetaDetails.setdataLength(metaDataTarget.getString("DATA_LENGTH"));
            targetMetaDetails.setdataPrescision(checkNull(metaDataTarget.getString("DATA_PRECISION")));
            targetMetaDetails.setdataScale(checkNull(metaDataTarget.getString("DATA_SCALE")));
            if("C".equals(metaDataTarget.getString("CHAR_USED"))){
                targetMetaDetails.setDataCharLength(metaDataTarget.getString("CHAR_LENGTH"));
                targetMetaDetails.setDataCharUsed(metaDataTarget.getString("CHAR_USED"));
            }
            targetMetaDetailsList.add(targetMetaDetails);
        }
    }
    validationFlag = compareSchema(sourceMetaDetailsList,targetMetaDetailsList);
}

private boolean compareSchema(List<DataValidationMetaDetails> sourceMetaDetailsList, List<DataValidationMetaDetails> targetMetaDetailsList) {

    Map<String,DataValidationMetaDetails> schemaMap = new HashMap<String,DataValidationMetaDetails>();
    DataValidationMetaDetails mapMetaDeatils = new DataValidationMetaDetails();

    for(DataValidationMetaDetails metaDeatils : targetMetaDetailsList){
        schemaMap.put(metaDeatils.getColumnName(), metaDeatils);
    }

    for(DataValidationMetaDetails metaDeatils : sourceMetaDetailsList){
        if(null!=schemaMap.get(metaDeatils.getColumnName())){
            mapMetaDeatils = schemaMap.get(metaDeatils.getColumnName());
            if(mapMetaDeatils.getdataType().equals(metaDeatils.getdataType())){
                if(!mapMetaDeatils.getdataLength().equals(metaDeatils.getdataLength())){
                    if(!mapMetaDeatils.getdataPrescision().equals(metaDeatils.getdataPrescision())){
                        if(!mapMetaDeatils.getdataScale().equals(metaDeatils.getdataScale())){
                            String dataTypeMod = "";
                            genAlterModifyScript(metaDeatils,"");
                        }
                    }else{

                    }
                }else{

                    genAlterModifyScript(metaDeatils,"L");
                }
            }else{
                logger.info("Data Type Mismatch for the Column : "+metaDeatils.getColumnName());
            }

        }else{
            genAlterAddScript(metaDeatils);
        }
    }
    return false;
}


private void genAlterModifyScript(DataValidationMetaDetails metaDeatils,String flag) {

    alterModifyQuery.append("\n");
    alterModifyQuery.append("ALTER TABLE ");
    alterModifyQuery.append(metaDeatils.gettableName());
    alterModifyQuery.append(" MODIFY ");
    alterModifyQuery.append(metaDeatils.getColumnName());

    switch(flag){
        case "L" : alterModifyQuery.append("("+metaDeatils.getdataLength()+")");
        break;

        case "P" : alterModifyQuery.append("("+metaDeatils.getdataPrescision()+","+metaDeatils.getdataScale()+")");
        break;

        case "S" : alterModifyQuery.append("("+metaDeatils.getdataPrescision()+","+metaDeatils.getdataScale()+")");
        break;
    }
}

Answer 1

In Oracle you could find table mismatches between SCHEMA_1 and SCHEMA_2 by doing something like:

SELECT *
  FROM DBA_TABLES t1
  WHERE t1.OWNER = 'SCHEMA_1'
MINUS
SELECT *
  FROM DBA_TABLES t2
  WHERE t2.OWNER = 'SCHEMA_2';

Similarly, to find column mismatches you could use

SELECT c1.COLUMN_NAME,
       c1.DATA_TYPE
  FROM DBA_TAB_COLS c1
  WHERE c1.OWNER = 'SCHEMA_1' AND
        c1.TABLE_NAME IN (SELECT t2.TABLE_NAME
                            FROM DBA_TABLES t2
                            WHERE t2.OWNER = 'SCHEMA_2')
MINUS
SELECT c2.COLUMN_NAME,
       c2.DATA_TYPE
  FROM DBA_TAB_COLS c2
  WHERE c2.OWNER = 'SCHEMA_2' AND
        c2.TABLE_NAME IN (SELECT t1.TABLE_NAME
                            FROM DBA_TABLES t1
                            WHERE t1.OWNER = 'SCHEMA_1')

Tweak this to compare whatever else you'd like to compare. You can extend this fairly easily to other database objects by querying the correct DBA views and fields.