This feature first saw the light of day when we talked to someone who is a long-time database developer, but in no way a database modeler (at least he wouldn’t describe himself as such). However, the whiteboard in his office had a database model on it, so we asked him how come. He said that the first thing he does when he gets his hands on a new database is to draw the picture of it, mostly just to understand it, and partly as an aide memoir while he’s working on it.
We told him that he could reverse engineer it into JDev and draw the picture that way. When he replied, there was a note in his voice that told us he just wasn’t going to do that, even though it’s easy enough to do (reference my last Quick Tour posting).
It’s hard sometimes to talk about features in your product without crossing the line from enthusing about the cool bits to wingeing about why users don’t love your product as much as you do, but we needed to get this right in our minds. What we concluded was that as far as he was concerned the database is the single source of truth, not some XML files nor a picture on his whiteboard. The reason he found the picture on the whiteboard acceptable and a model in JDev not was simply that no-one was going to mistake a whiteboard hand drawing for the single source of truth, but they might a JDev project. You’d have to count me in that category. A JDev project is where I keep my source code and it’s where I keep my database design. For me, generating DDL and running it is the same as compiling my code and deploying my application (well not quite, but you get the picture).
But that’s not the point. The point is that for some people the way JDeveloper, and all database design tools, work is just plain wrong! The database is king. So that’s when we conceived of a diagram built not of metadata objects but directly on the database catalog that contains the same metadata. Instant visualization (the problem to be solved) without file-based metadata (the situation to be avoided).
The two scenarios look like this:
- Scenario 1: Reverse engineer from the database catalog into a JDeveloper project and then create a diagram of the offline metadata.
- Scenario 2: Create the diagram directly from the metadata in the database catalog. There is no offline metadata (apart from the diagram itself, if I choose to save it).
In Scenario 2, it’s conceivable I may not even save the diagram. If I created it just to understand the foreign keys then once I’ve done that I might just delete the diagram. However, I could also save it safely because whenever it is opened it will attempt to synchronize with the database, and if it can’t, it won’t display, so my database single source of truth is still king.
I covered Scenario 1 in the Quick Tour in my last post, so let’s just run through Scenario 2 with the tool. First I create a new diagram, which I do in the context of a project, because if I choose to save the diagram, that’s where it will go:
Then I need to connect to the database I’m going to model:
Again, I’m using the shipped HR demo schema in my XE database.
I browse down the database connection to the tables I want to model, select them and drag them onto the diagram.
When I do that the tool asks if I want to reverse engineer and then build the diagram, or if I want to leave them in the database and model them there:
And that’s it:
I’ve cheated a little here, and captured the diagram when I’d done my custom tidy up, as described in the Quick Tour, but that’s it.
It takes a little longer to open a connected model diagram because it has to query from the database to check the diagram is in sync, and I recommend you put a note on the diagram to say it is an online model because you can edit it and it changes the catalog directly by generating an ALTER TABLE when you "OK" the editor. That has all the restrictions you’d expect if you applied the same ALTER TABLE in SQLPlus, but here it’s generated for you.
Let’s see that in action. Edit a table and add a column:
"OK" the dialog and refresh the Database Navigator view (control at the top of the pane) to see the added column:
Now drop the column in the Database Navigator and refresh the diagram (Menu > View > Refresh) and it’s gone.
Some people who have seen this regard it as a dangerous tool. Well, so is a chain saw, but you try taking a tree down without one! In reality, this is no more dangerous than the SQL Worksheet in JDeveloper and it can be more convenient. It’s just another tool in the toolbox. Hope it helps.
While building our "Roots" genealogy app, we haven't used online modeling very much, as for us the model in the offline database is king. This is because we're working in a team, and want to source control all the application artefacts in Subversion, including the schema itself. Changes to the schema are made in the offline database and checked into Subversion. Team members can then pick up those changes and generate the DDL to refresh their local schemas (most of us are working against local instances of Oracle XE). We also have some powerful tools to compare different versions of the offline objects, and to compare the model with objects in the online database. I'll be going into more detail on that in my next post.
No comments:
Post a Comment