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.’
Now call the transformation script.
Tools > Design Rules & Transformations > Transformations.
Before
After
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.
How do I get rid of them?
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.
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); } } } } }
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.
After running our script –
Philip liked this example, so he’s included it as a ‘stock’ template in the 4.1 install and docs.
14 Comments
The example good, but is about applying the common columns on all tables in the model, if they dont have the common columns. But how can I apply to only few of them or just only one? Or the other way around: Imagine there is already an existing model and I create a new table. Into this new table I want to insert the common columns. Is there a way to do that from within the popup window only for this table?
Definitely not via pop-up, no. But between this feature and Domains, you can go pretty far. Especially domains.
In fact what is missing is the ability to choose for which tables should the common columns be applied, instead of applying for all, like it is now. would be possible in the script to implement a prompt for which tables sholud be applied?
Is there any documentation on the usage of the Oracle Nashorn or Mozilla Rhino in the Oracle SQL Developer Data Modeler? I am new to this so I may not be using the right terms but the customization in the DDL and the rules I liked it really. I wanted to get the person name who has modified the data model and include that information in the DDL Changelog.
https://imgur.com/fDQz4pE
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.
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.
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?
See’s Heli’s post, the first part.
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?
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 ?
I got it now. Thanks.
in your first screenshot, you have the Oracle Nashorn library loaded. How do we do that to replace the Rhino library?
Running Java 8, I think.
Philip rocks! Great tool to add to my reverse engineering kit.