This post is not a ‘How to Version Your Models’ tutorial. We have that in movie form in the Oracle Learning Library (OLL.) There’s a series of several 2-3 minute videos that step 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.
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.
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.
The results are delivered in a pop-up box that looks like this
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.
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.
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.
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?