Monday, July 29, 2013

Who's on first? Adding Who columns in the Modeler

   The SQL Developer Datamodeler has some great built in transformation.  Let's take a look at the Table Template one.
  


  I'm going to start with the plain ol' HR schema and we're going to add in the standard who columns to it.





Step 1 is to create a table named "table_template"  There's plenty of options since it's just a normal table in the model.  The two interesting checkboxes which are Engineer and Generate in DDL.  This will make it so this table is not generated when you ask for the DDL or Engineer into a Logical Model.




Step 2 is to add in the who columns we want to propagate to all tables in the model.




Step 3 is to do the transformation.  




Step 4 is to press the Apply button.





The final result is that all the tables in the model now have every column from the table_template.




The transformation itself is fairly straight forward and it's all Javascript:



 /* name of the table to template from */
var t_name = "table_template";
var p_name = "ctemplateID";

template = model.getTableSet().getByName(t_name);
if(template!=null){
    tcolumns = template.getElements();
    tables = model.getTableSet().toArray();
    /* walk all tables in the model */
    for (var t = 0; t<tables.length;t++){
       table = tables[t];
      // compare name ignoring the case
       if(!table.getName().equalsIgnoreCase(t_name)){
         for (var i = 0; i < tcolumns.length; i++) {
            column = tcolumns[i];
            col = table.getColumnByProperty(p_name,column.getObjectID());
            if(col==null){
             col = table.createColumn();
            }
            column.copy(col);
            //set property after copy otherwise it'll be cleared
            col.setProperty(p_name,column.getObjectID());
            table.setDirty(true);
         }
     }
    }
}