Adding Common Columns to Tables via Templates and Transformation Scripts

thatjeffsmith SQL Developer 10 Comments

Tell Others About This Story:

In Oracle SQL Developer Data Modeler, you can create a table in your relational design that stores columns that you want to be added to all of your other tables.

Call it, ‘table_template.’

Whatever columns I add to this particular table can be automatically added to all of my other tables.

Whatever columns I add to this particular table can be automatically added to all of my other tables.

Now call the transformation script.

Tools > Design Rules & Transformations > Transformations.

You can change the name of your template table to anything you want - just modify the highlighted bit of this script before you run the transformation.

You can change the name of your template table to anything you want – just modify the highlighted bit of this script before you run the transformation.

Before

My tables without the columns I want to add via the transformation script.

My tables without the columns I want to add via the transformation script.

After

Notice the script was smart enough to not add the columns to the original template table.

Notice the script was smart enough to not add the columns to the original template table.

These are cool, but they don’t belong in my Logical model!

So let’s say you started with a Relational Model, and you have decided to engineer a Logical one. These template columns may not make any sense in your Logical model.

So, how do I get rid of them?

These attributes make no sense for my entities - get 'em out of here!

These attributes make no sense for my entities – get ’em out of here!

With the help of a friend, I have this awesome transformation script for you 🙂

By friend I mean, Philip, and by ‘help’ I mean – wrote the script 100% for me.

[text] 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();
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){
attr = col.getEngAttribute();
if(attr!=null){
ent = attr.getEntity();
attr.remove();
if(ent!=null){
ent.setDirty(true);
}
}
// col.remove();
table.setDirty(true);
}
}
}
}
}
[/text]

There’s an important line in there.

// col.remove();

I added the comment. If you run this script without that line commented out, you’ll lose your attributes AND your template columns. I just wanted to remove the attributes so I commented out the bit for deleting the columns. You can use this script to JUST remove the columns too, just comment out the logical/attribute bits.

When you add this new script, you need to add it as a ‘relational’ object – it needs access to the table names from the relational model as it traverses the logical.

So anyways, after running it:

The attributes derived from the template columns  have been removed.

The attributes derived from the template columns have been removed.

Philip liked this example, so he’s included it as a ‘stock’ template in the 4.1 install and docs.

Related Posts

Tell Others About This Story:

Comments 10

  1. Thanks Jeff, how can we take this to the next level and copy indexes, relationships, and constraints as well?

    That way columns like created_by and modified_by could include foreign key constraints to a USERS table, or range columns could include a start <= end constraint, etc.

    1. thatjeffsmith Post
      Author

      There’s a DDL transformation script feature that will do EXACTLY what you want. Tools > Design Rules > Table DDL Transformations. Look at the Journal Tables example – that will show you how to reference the object names and create new objects based on them. Then, when you generate your DDL, there’s an option to have those scripts fire on selected objects.

    2. I don’t appear to have that sample script or any other sample Table DDL transformations for that matter. I’m running SQL Dev 4.1.2.20 using the embedded data modeler instead of the standalone version.

      Where else can I find these?

    3. thatjeffsmith Post
      Author
  2. Interesting article Jeff. I’ve been using a template table to add common columns to tables that are forward engineered from the logical model.

    Can you tell me if there is a way so that intersection tables that are generated to resolve many to many relationships can inherit the common columns from the template table?

  3. Hi,

    I’m struggling a little with the scripts – what I want to do is remove all the column names in my relational model ,where the “_” character appears with so table_id would become TableId is there a property for this ?

  4. in your first screenshot, you have the Oracle Nashorn library loaded. How do we do that to replace the Rhino library?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *