7 Replies Latest reply on Aug 11, 2015 1:50 PM by Kent Graziano

    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?

        • 1. Re: How to customize FK Discovery
          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);
                }
              }
          }
          }
          
          1 person found this helpful
          • 2. Re: How to customize FK Discovery
            Kent Graziano

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

            • 3. Re: Re: How to customize FK Discovery
              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);
                    }
                   }
               }
              }
              
              1 person found this helpful
              • 4. Re: How to customize FK Discovery
                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.

                • 5. Re: Re: How to customize FK Discovery
                  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);
                        }
                       }
                   }
                  }
                  
                  • 6. Re: How to customize FK Discovery
                    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

                    1 person found this helpful
                    • 7. Re: How to customize FK Discovery
                      Kent Graziano

                      Cool! Thanks for the update and the template code.