From Domain to Database… A Comment Conundrum

Recently on the Data Modeler Forum, I came across this question:

Is it possible to mirror domain comments, from Domain Administration into attribute “Comments in RDBMS”?  Would like to mirror these to the ddl so they can be then available in column comments in the database.

I knew that one of the example transformation scripts provided with Data Modeler copies the column “Comments” property to “Comments in RDBMS”, so I thought I would point this out to the questioner, and that would be that.  Screen Shot 2017-07-26 at 10.43.41 PM.png

However, it seems that the “Comments” property of a domain… Screen Shot 2017-07-26 at 9.55.23 PM.png

is not transferred to the “Comments” property of the column defined using the domain (or the “Comments in RDBMS” property either).

Screen Shot 2017-07-26 at 9.57.20 PM.png 

This calls for a Custom Transformation Script.To the Bat Poles! OK, maybe not. Let’s just… Open the Custom Transformation Scripts window!

Screen Shot 2017-07-27 at 7.31.15 AM.png

Because the task at hand is so similar to the purpose of an existing script, I copied the “Copy Comments to Comments in RDBMS” script to as a new script and gave it a descriptive name. I also set the target to be a relational model, and because this script was for the forum, I set the language to Oracle Nashorn (instead of the superior Groovy Scripting Engine that I use). The existing script does a lot more than we need; it copies table, column and key comments to RDBMS Comments, so I deleted of the code to handle the comments for the tables and keys.

I was left with what is seen below; which if run, would copy the “Comments” property to the empty “Comments in RDBMS” property. But we want to copy the “Comments” property from the domain to the column.

Screen Shot 2017-07-26 at 10.49.43 PM.png

It should be noted that if you do not assign a domain to a column, the “Unknown” domain is assigned. So, the first thing I added to the script was to check each column, to to see if the domain was not set to “Unknown”.

Screen Shot 2017-07-26 at 10.56.31 PM.png

For those columns with a defined domain, I set a variable containing the domain comment property, and make sure that the comment is not empty.

Screen Shot 2017-07-26 at 11.00.38 PM.png

I then follow the original script and make sure that the comment is not too long, and then set the value of the column “Comments in RDBMS” property. Setting the column as dirty essential tells the script processor that you want to “commit” this change.

And that’s it. The entire script is seen below. Apply this script to you relational model and the comments are where you want them. That being said, always save your model before applying any transformation script. It’s as close to “rollback” as you are going to get. 

max_length = 4000;
function copyDomainComments(object){
 // if there are no comments in RDBMS
 if(object.getCommentInRDBMS().equals("")){
   // see if the object has a defined domain
   if(object.getDomain() != 'Unknown') {
   	// grab the domain comment
   	var domainCmt = object.getDomain().getComment();
   	// if there is a domain comment
     if(!domainCmt.equals("")){
   	  // check if the comment is too long
       if(domainCmt.length()>max_length){
         object.setCommentInRDBMS(domainCmt.substring(0, max_length));
       }else{
         object.setCommentInRDBMS(domainCmt);
       }
       object.setDirty(true);
     }
   }
 }
}

tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++) {
  table = tables[t]
  columns = table.getElements();
  size = table.getElementsCollection().size();
  for (var i = 0; i < columns.length; i++) {
	  column = columns[i];
	  copyDomainComments(column);
  }
}
Advertisements

One thought on “From Domain to Database… A Comment Conundrum”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s