How to customize FK Discovery

Kent Graziano

I have a 3rd party database that has PKs defined on every table but no FKs in the database. How do I get the FK Dsicover utility to find FK columns with this type of pattern:

Parent PK column = TABCUSTNUM

Child FK column = ABCCUSTNUM

So the root column name (CUSTNUM) is standard but in every table the column name has a different 3 character "prefix" that is effectively the table short name. Is there way to get the utility to ignore the first three characters of the column names?

Philip Stoyanov-Oracle

It said isAllowSimilarTypesFK was not a valid function (no such function).

My fault, it's not included in 4.1.873

The other issue, if the FK is already there, I don't want another. I had already built a few by hand so the script created dups.

I added a check that column is not already used in FK definition.Here is the new script:

var settings = model.getAppView().getSettings();
//function to get column by matching name from position
//position is 0 based
function getColumn(table,refCol,position){
    partName = refCol.getName().substring(position);
    cols = table.getElementsCollection().toArray();
    for(var i = 0;i<cols.length;i++){
        col = cols[i];
        if(!col.equals(refCol)){
         cpartName = col.getName().substring(position);
         if(!col.isFKColumn() && partName.equals(cpartName) && col.hasEqulaDataType(refCol) ){
             return col; 
         }
        }
    }
    return null;
}
tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 pk = table.getPK();
 if(pk!=null){
     for (var p = 0; p<tables.length;p++){
      ctab = tables[p];
      list = new java.util.ArrayList();  
      pk_cols = pk.getColumns();
      for(var k=0;k<pk_cols.length;k++){          
         col = getColumn(ctab,pk_cols[k],3);
         if(col!=null){
             list.add(col);
         }
      }
      //create FK if the number of columns is the same
      if(list.size()==pk_cols.length){
          ctab.addForeignKey(pk,list);
      }
     }
 }
}

Philip Stoyanov-Oracle

Hi Kent,

No, it's not possible to tune FK discovery wizard in that extent. You can try the following script:

Philip

var settings = model.getAppView().getSettings();
//function to get column by matching name from position
//and data type - similar data types can be allowed in preferences - DM 4.1 EA2+
//position is 0 based
function getColumn(table,refCol,position){
    partName = refCol.getName().substring(position);
    cols = table.getElementsCollection().toArray();
    for(var i = 0;i<cols.length;i++){
        col = cols[i];
        if(!col.equals(refCol)){
        cpartName = col.getName().substring(position);
        if(partName.equals(cpartName)){
            if(!settings.isAllowSimilarTypesFK() && col.hasEqulaDataType(refCol)
                ||settings.isAllowSimilarTypesFK() && col.hasSimilarDataType(refCol) ) {
                    return col;   
                }
        }
        }
    }
    return null;
}
tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
table = tables[t];
pk = table.getPK();
if(pk!=null){
    for (var p = 0; p<tables.length;p++){
      ctab = tables[p];
      list = new java.util.ArrayList(); 
      pk_cols = pk.getColumns();
      for(var k=0;k<pk_cols.length;k++){   
      //position is 0 based so 4th character is at position 3     
        col = getColumn(ctab,pk_cols[k],3);
        if(col!=null){
            list.add(col);
        }
      }
      //create FK if the number of columns is the same
      if(list.size()==pk_cols.length){
          ctab.addForeignKey(pk,list);
      }
    }
}
}

Kent Graziano

Thanks. Will give this a try. What do you mean - "similar data types can be allowed in preferences - DM 4.1 EA2+ "?

Philip Stoyanov-Oracle

similar data types for FK columns - number(7) and Number(10) are similar types they are not exact as it's required for PK-FK columns matching in previous releases. It appears it's not included in 4.1 EA2. Sorry, I'll change the script tomorrow to not use

here is the new script that will work in 4.1.873

var settings = model.getAppView().getSettings();
//function to get column by matching name from position
//position is 0 based
function getColumn(table,refCol,position){
    partName = refCol.getName().substring(position);
    cols = table.getElementsCollection().toArray();
    for(var i = 0;i<cols.length;i++){
        col = cols[i];
        if(!col.equals(refCol)){
         cpartName = col.getName().substring(position);
         if(partName.equals(cpartName) && col.hasEqulaDataType(refCol) ){
             return col; 
         }
        }
    }
    return null;
}
tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 pk = table.getPK();
 if(pk!=null){
     for (var p = 0; p<tables.length;p++){
      ctab = tables[p];
      list = new java.util.ArrayList();  
      pk_cols = pk.getColumns();
      for(var k=0;k<pk_cols.length;k++){          
         col = getColumn(ctab,pk_cols[k],3);
         if(col!=null){
             list.add(col);
         }
      }
      //create FK if the number of columns is the same
      if(list.size()==pk_cols.length){
          ctab.addForeignKey(pk,list);
      }
     }
 }
}

Kent Graziano

It worked, mostly.

Got an error on these lines:

if(!settings.isAllowSimilarTypesFK() && col.hasEqulaDataType(refCol) 

                    ||settings.isAllowSimilarTypesFK() && col.hasSimilarDataType(refCol) )

It said isAllowSimilarTypesFK was not a valid function (no such function).

The other issue, if the FK is already there, I don't want another. I had already built a few by hand so the script created dups. So an FK from Tab1 to Tab2 is already there, that should be skipped.

Thanks.

Philip Stoyanov-Oracle

It said isAllowSimilarTypesFK was not a valid function (no such function).

My fault, it's not included in 4.1.873

The other issue, if the FK is already there, I don't want another. I had already built a few by hand so the script created dups.

I added a check that column is not already used in FK definition.Here is the new script:

var settings = model.getAppView().getSettings();
//function to get column by matching name from position
//position is 0 based
function getColumn(table,refCol,position){
    partName = refCol.getName().substring(position);
    cols = table.getElementsCollection().toArray();
    for(var i = 0;i<cols.length;i++){
        col = cols[i];
        if(!col.equals(refCol)){
         cpartName = col.getName().substring(position);
         if(!col.isFKColumn() && partName.equals(cpartName) && col.hasEqulaDataType(refCol) ){
             return col; 
         }
        }
    }
    return null;
}
tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 pk = table.getPK();
 if(pk!=null){
     for (var p = 0; p<tables.length;p++){
      ctab = tables[p];
      list = new java.util.ArrayList();  
      pk_cols = pk.getColumns();
      for(var k=0;k<pk_cols.length;k++){          
         col = getColumn(ctab,pk_cols[k],3);
         if(col!=null){
             list.add(col);
         }
      }
      //create FK if the number of columns is the same
      if(list.size()==pk_cols.length){
          ctab.addForeignKey(pk,list);
      }
     }
 }
}

Philip Stoyanov-Oracle

Hi Kent,

in DM 4.1.1.888 you don't need script to get the similar result.

Cases covered:

1) prefix is in table abbreviation, then FK column template should be

{table abbr}SUBSTR(4,30,FRONT,{ref column})

2) first 3 (or another number) characters from table name are used as prefix for columns then template is:

SUBSTR(1,3,FRONT,{table})SUBSTR(4,30,FRONT,{ref column})

I say similar result because the script and wizard work in different ways:

- the wizard generates the name based on template and search for column with such name (and data type)

- the script is doing partial matching of parent and child columns

Philip

Kent Graziano

Cool! Thanks for the update and the template code.