Comparing the Model with the Database

Time for another post before we head into a Holiday weekend here in the UK!

When working with database models, you often want to compare the model with a live database in order to see what's different, and generate ALTER DDL to make your database match the model.  JDeveloper 11g has some powerful tools in this area that have matured over a number of releases.

Let's take an example in the Roots project.  Right now I'm working on a table called ROOTS_USERS that (unsurprisingly) models users in the system.  When users first use the system, they have to register an e-mail address and a password, and optionally load up a picture of themselves.  We've already got this working, but we've decided that before users can start adding their family tree, they have to be activated by a system admin user.

To model this, I'm adding a new column to the ROOTS_USERS table, which currently looks like this:

The new ACTIVE column is a simple VARCHAR2 column that can hold the values 'Y' or 'N' (default):

While I'm working in the editor, I can click on the "DDL" tab and see what the DDL would be for a CREATE or UPDATE of the table.  The ALTER DDL shown here for UPDATE is produced by comparing the current state of the offline table in the editor with what is stored on disk in the XML file:

I'm also going to add a constraint to enforce that the values for the new ACTIVE column can only be 'Y' or 'N':

I "OK" the dialog and this is what is shown in the Application Navigator:

The ROOTS_USERS icon has a "*" overlay on it to show that it has changed.  I'm not going to commit that change back to the subversion repository yet, as it's just one of a number of changes I'm making to the model at the moment, and I want them to be committed together when I'm done.  However, I do want to update my local database to reflect this change so that I can test my code.

As I mentioned before, we have some scripts that automatically CREATE the whole Roots schema.  I could use those scripts and my change would be included, but that would involve dropping all the tables first, then recreating them before I could test my change.  I also have some personal family data in the tables right now that I want to preserve.

So, instead I right click on the ROOTS_USERS table and select "Generate To > SQL Script...":

This is a shortcut into the Generate SQL from Database Objects wizard that I showed in an earlier post.  It takes you directly to the page where you decide whether you want to CREATE, REPLACE or ALTER:

I've chosen the ALTER option, ticked the "Manual Reconcile" check box and chosen a database connection.  This will present me with some UI that will show the differences between the ROOTS_USERS table in the offline model and the ROOTS_USERS table in that database connection.  After pressing the "Next" button, I see this:

There's quite a lot going on here, so let's go through each piece of UI in turn.

The toolbar at the top gives me buttons to navigate directly to the first and last difference, and move to the next and previous difference.  There are buttons to choose to accept or revert all changes, and a very useful button to show only the differences.

The tree on the left represents the offline ROOTS_USERS table, and the tree on the right represents the ROOTS_USERS table on the database.  Differences between the model and the database are highlighted in the tree on the left.  Here I've made a number of other changes to the table which I'm not interested in synchronizing with the database right now, so let's just focus on the new ACTIVE column and the new ROOTS_USERS_CHK1 constraint highlighted in green.

At any level in the tree, I can use the small green arrow buttons to accept a change, which will make that change show up in the right hand tree representing the object in the database.  For example, below I've clicked the small green arrow button next to the new ACTIVE column, and it now also appears on the right:

At this point I could press the "Next" button and just action this one change, but as well as creating the new column, I also want to create the new constraint on the database.  Constraints reference columns, and to illustrate that the reconcile tool knows this, I've clicked the red cross next to the ACTIVE column to revert that change, and then selected the ROOTS_USERS_CHK1 constraint:

The tool shows that it knows the constraint references a column by highlighting both the column and constraint in darker green.  If I want the constraint, then the column must be added as well, and clicking on the green arrow button next to ROOTS_USERS_CHK1 makes both the constraint and the ACTIVE column appear on the right:

I'm now happy with my list of changes to action against the database, so I press "Next", and proceed through the wizard, choosing the name of the SQL script etc.  Finally the wizard generates the script, which I run against my local development database using the SQL Worksheet:

Now both the new column and associated constraint are available in the database for me to test my code against.

Once I've finished my batch of changes, I'll need to commit those to the Subversion repository.  More on that next time, including dealing with the situation where someone else has modified the same objects that I've been working on!