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.