This post is not a ‘How to Version Your Models’ tutorial. We have that in movie form in the Oracle Learning Library (OLL.) It’s about 13 minutes long and steps you through how to add a model to source control via Subversion, how to make changes, commit said changes, and how someone else can do a Check Out to see and continue your work.

I want to concentrate on a small but critical component of this process. Once you’ve made changes to a model, you’re going to want more information about said changes and you’re going to want to see what the impact on the database will be if you ever decide to synchronize your model back to the live system.

Show me what’s different, and show me the code to make those changes happen in the database!

SQL Developer Data Modeler (SQLDev) has the subversion client interface built into the tool. You can view your trunk and branches plus what’s versioned in each of those locations. Once you open a model, you can see what incoming and outgoing changes are in play. Let’s take a second to investigate that.

Pending Changes – Incoming and Outgoing

In order to make changes to a versioned model, you’ll need to ensure first that:

  • You have a Subversion system defined with proper access
  • You have checked out a model
  • You have made changes to the model
  • You have saved the changes to your local copy

Once you’ve made changes to the local copy, these will appear as ‘Outgoing Changes.’

You’ll want to make sure the ‘Pending Changes’ panel is opened via the Versioning – Pending Changes menu item.

SQLDev will tell you what you're sending out and what's outstanding from the server

The integrated Subversion interface is showing me what will be going out if I choose to ‘Commit’ the changes. Since the models are composed of a collection of individual XML files representing each data modelling element, I can commit my changes at a per object level. Or I can commit all the changes at the model level. If someone else was make changes to the model in the Subversion repository, I would see those changes in the ‘Incoming Changes’ panel.

If I mouse-right-click on a pending change, I can get the details. You have the option of comparing the model to the latest local copy or to what’s on the server.

Compare with

I want to see what changes are about to be committed to the Subversion repository when I do a ‘commit.’ So I can choose the table I want to investigate.

How does the latest file differ from what had been checked out from Subversion?

The results are delivered in a pop-up box that looks like this

I've changed the column from a CLOB to a VARCHAR(4000)

But Wait Jeff, I want an ALTER DDL Script!

In order to generate ALTER scripts – that is, to get a DDL script that will ALTER the objects in the database to reflect what is in the current version of the model, you need to do a model ‘Compare.’ SQLDev makes this very easy when it comes to treating the live database as a model.

The main toolbar has two buttons which allow you to do this compare on the fly.

The most popular buttons in the tool?

Synching with the data dictionary will allow me to generate an ALTER script that updates the database to reflect what’s in the model.

The resulting dialog shows me all of the affected model and database objects with the details for each listed side-by-side.

Compare Model Results

Now instead of showing you the same old ‘this column is a apple, and this column is an orange’ I navigated to the ‘Data Type Conversion’ panel. Here I can determine how the ALTER script will be generated. Maybe I am OK with a simple

 ALTER TABLE SH.PROMOTIONS 
    MODIFY ( PROMO_SUBCATEGORY VARCHAR2 (30 BYTE) ) 
;

But maybe this will result in data loss! Instead I want a temp copy of the table created and then I want the data moved over, etc. By checking the ‘Recreate table’ option, I am telling SQLDev to use this strategy. Here’s what the resulting ALTER script looks like instead:

ALTER TABLE SH.PRODUCTS RENAME TO bcp_PRODUCTS 
;
CREATE TABLE SH.PRODUCTS 
    ( 
     PROD_ID NUMBER (6)  NOT NULL , 
     PROD_NAME VARCHAR2 (50 BYTE)  NOT NULL ,
...
INSERT INTO SH.PRODUCTS 
    (PROD_ID , PROD_NAME , PROD_DESC , PROD_SUBCATEGORY , PROD_SUBCATEGORY_ID , PROD_SUBCATEGORY_DESC ,
 PROD_CATEGORY , PROD_CATEGORY_ID , PROD_CATEGORY_DESC , PROD_WEIGHT_CLASS , PROD_UNIT_OF_MEASURE ,
 PROD_PACK_SIZE , SUPPLIER_ID , PROD_STATUS , PROD_LIST_PRICE , PROD_MIN_PRICE , PROD_TOTAL ,
 PROD_TOTAL_ID , PROD_SRC_ID , PROD_EFF_FROM , PROD_EFF_TO , PROD_VALID )
SELECT 
    PROD_ID , PROD_NAME , PROD_DESC , PROD_SUBCATEGORY , PROD_SUBCATEGORY_ID , PROD_SUBCATEGORY_DESC ,
 PROD_CATEGORY , PROD_CATEGORY_ID , PROD_CATEGORY_DESC , PROD_WEIGHT_CLASS , PROD_UNIT_OF_MEASURE ,
 PROD_PACK_SIZE , SUPPLIER_ID , PROD_STATUS , PROD_LIST_PRICE , PROD_MIN_PRICE , PROD_TOTAL ,
 PROD_TOTAL_ID , PROD_SRC_ID , PROD_EFF_FROM , PROD_EFF_TO , PROD_VALID 
FROM 
    bcp_PRODUCTS 
;
...

Regardless of the method you choose, I highly recommend you review ALL outstanding changes and their related ALTER scripts. If you want to run the script against the database, you’ll need to save the file and execute it outside the SQL Developer Data Modeler.

But Wait Jeff, I want an ALTER DDL script for 2 different versions of the model, not between the current and the database!

OK, now you’re just getting picky!

If I want to generate an ALTER between version 3 and version 4 of my model, I need to do a CHECK OUT of the older version, or revision.

On the check out dialog, be sure to toggle on the ‘use revision’ control, then tell us what revision you want to check out.

Tells us what revision to check out

Then use Tools – Compare/Merge Models, then point to the model file you just had copied down locally when you did the ‘Check Out with revision.’

The compare results navigation and the DDL scripting works just the same.

An idea going forward is to make this more convenient by making these DDL scripts available directly from the ‘Version History’ dialog instead of forcing you to do a check out and launching the compare dialog. Does this sound like a good idea?