Spandrels

If your interests include data warehousing, travel and architecture then you need to start following Dai Clegg's new blog.  Dai was until recently my boss at Oracle, and this blog is part of his legacy, as he encouraged me to start blogging about the database features in JDev so that they could reach a wider audience.

Dai has a great way of linking together concepts from different fields, which often came out in our discussions, and which I'll miss a lot.  For example, I don't quite remember how Brunelleschi's dome related to the architecture of the Database API, but somehow it did, and we had a very entertaining conversation about it!

Copying, Comparing and Reconciling Database Models

Before I talk some more about version control with Subversion, I wanted to demonstrate how JDeveloper can be used to create a copy of an offline database model, as well as compare and reconcile models.

As you might remember from previous posts, the Copy Database Objects to a Project wizard can be used to copy or import database objects from a database to an offline database.  This tool can also be used to copy objects from one offline database to another to create a copy of model.

The ROOTS schema lives in an offline database called DATABASE1.  I'm going to use the wizard to create a complete copy of that offline database.  First I create a new offline database called DATABASE2 by right-clicking on the Offline Database Sources node in the Application Navigator and selecting "New Offline Database...":

Now I right-click on DATABASE2 and select "Copy to Project...".  The first thing I need to tell the wizard is where I'm copying objects from (i.e. the source).  When I used this wizard before, I picked a database source, but this time I'm going to choose a project and offline database (DATABASE1):

In the next page of the wizard, I choose the target, which is the newly created offline database, DATABASE2:

In the next page, I select the objects I want to copy (let's have all of them):

Finally, I choose to CREATE these objects in the new offline database, since I know it's completely empty:

I press the "Finish" buttton, and a few moments later, the Application Navigator shows that DATABASE2 contains a copy of the ROOTS schema:

To show how model compare and reconcile works, I'm going to work on the copy of the offline database, DATABASE2, removing the ACTIVE column on the ROOTS_USERS table, along with its associated constraint.  Once I've done this, I can run the Copy Database Objects to a Project wizard again, choosing the offline database DATABASE1 as my source, and DATABASE2 as my target, and again selecting all the objects.  This time, when I get to the "Choose Operation" page of the wizard, I'm going to choose ALTER with Manual Reconcile.  Going to the next page shows the reconcile UI:

The DATABASE1 model is on the left hand side, and DATABASE2 is on the right.  As you can see, the ACTIVE column and it's associated constraint are missing from DATABASE2.  If I wanted to reconcile the models, I could shuttle the ACTIVE column and the constraint over to the right hand side.  Unlike the Generate SQL from Database Objects wizard, this wizard will not generate DDL, but will action those changes directly against the target DATABASE2 model.

Marginal Boundaries

A friend of mine has been working hard to launch Marginal Boundaries, a quarterly e-zine which specializes in speculative fiction.  If you enjoy this kind of writing, check out the first issue, which is now available.  There are some excerpts on the site to give you a taste of what's included.

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!

Database Edition Role

It's been a busy week, and I haven't had time to write about comparing and merging offline database schemas as promised.  I'll try to get to that next week.  In the meantime, here's a handy tip:

When JDeveloper starts up for the first time you will see a role selection dialog.  If you're just using the product for database development, then choose the "Database Edition" role.  This just loads the extensions that you will need for working with databases.  With this role selected the tool will start up faster and take up less memory.

You can also change the role by going to Tools > Preferences and selecting the Role:

Note that if you change the role, JDeveloper will need to restart.

Oracle JDeveloper 11g Handbook

As part of the appbuild project, we've had to move outside our comfort zones into areas of the product that aren't so familiar to us.  Once we finished the database design and started to build the ADF Model, View and Controller layers, many of us bought this book and found it very helpful.  It's certainly been next to me on my desk every time I've worked on Roots.

If you need to start learning about JDeveloper 11g and ADF, I would certainly recommend getting yourself a copy.  As well as plenty of examples (including a complete demo app), there's a good amount of overview information that ties everything together, including a little history about ADF that explains where it has come from and how much has changed in the 11g release of JDeveloper to make the tool much more powerful and productive for application developers.

Generating SQL from the Database Model

We've been working hard on our internal app building project this week, so I thought another blog would be in order to introduce the "Roots" schema, and show how we've set up JDeveloper for team working and generation of SQL from the model.

The idea for Roots came from my recent interest in researching my own family history using a website called ancestry.co.uk. I thought it would be interesting to use JDeveloper and ADF to build something similar, although our ambition level was a little lower given this was a part-time project, and the rest of our time is filled with adding lots of lovely database modeling features to JDev!

We're naturally interested in databases, so we spent a lot of time thinking about the Roots schema and modeling it in JDev. We wanted the schema to be very powerful and flexible, even though we might not have time to implement all the features on top of the model in one go.

Here's a JPG of the Roots diagram, which I exported from JDev using "Diagram > Publish Diagram":


The basic idea is that Users of the system create Humans that can have different Relationships with each other (Parent-Child, Partner for example). Humans can have different identities over time. For example, my wife was born Astrid Jennings (first Identity). When she married me, she became Astrid Cobb (second Identity). Both Relationships and Identities can be time bound by Events. For example, the default Identity for a Human has an associated birth Event, and a marriage has a beginning Event (and sometimes an end Event).

You'll notice from the diagram that I've chosen to display icons for mandatory columns and columns that participate in constraints. Enable this by selecting a shape on the diagram and then checking the "Show Icons" check box in the Property Inspector under the "Display Options" category. We're not entirely happy with the icons, and it's likely that they'll change in the future, by they are useful for seeing what's important at a glance.

Now let's look at the offline database in the Application Navigator:


Underneath the "Offline Database Sources" node you can see the offline database itself, called "DATABASE1". It's here that you set various options, such as which type of real database the offline database is emulating.

Underneath DATABASE1 you'll see the ROOTS schema, and underneath that the offline objects in the model. Each object is persisted on disk as an XML file. Look at the ROOTS_EVENTS table, and you'll see a number next to it. This is the revision number from Subversion, as we're keeping our offline database schema (along with all of the source for Roots) in a Subversion repository so that everything can be shared by the team.

To pick up schema changes from others, team members refresh their local copy of the offline database from the Subversion repository. Most people are working against a local Oracle XE database for development, so to refresh their personal online database, they need to generate the SQL DDL.

Here I've scrolled the Application Navigator a little to the bottom of the ROOTS schema:


To refresh their local database, all people need to do is right click on the "Generate Roots Script" object and select "Generate". This generates a DDL script for the core database objects from the offline model. They then need to run the "roots_install.sql" file using SQL Worksheet or SQL Plus, which calls the core DDL script, plus some other hand-written scripts to install seed data, and some example data based on the British Royal Family.

Let's see how the "Generate Roots Script" was created.

First I ran the "SQL Generated from Database Objects" wizard from the New Gallery:


The first page allows you to pick where you're generating from. In this case I picked an offline database in the project:


Here you choose whether you want to generate the DDL to a SQL script or direct to the database. Here I opted to generate to a SQL script:


Next I selected all the objects in the schema that I want to generate:

The Operations page is where you choose whether to CREATE, REPLACE or ALTER the objects that you've chosen to generate. In this case, I want a CREATE DDL script:

This page allows me to choose some options for the SQL script, such as its name and location. Notice that we consider the script to be a derived object, so it goes into the classes directory along with artefacts like compiled Java classes:


Finally I choose to save the settings, and provide a name and location for the file. This is the magic that allows me to run this whole process again by simply right clicking on the "Generate Roots Script" object in the Application Navigator:


That's all I have time for today. In future posts we'll look more at team working using Subversion source control, and some of the other options you saw in the generate wizard. In particular the powerful visual reconcile feature that can be used when you use the ALTER option and compare the model with the online database.