Wednesday, October 01, 2008

Your input requested: and DB schema management

I'm doing research into database tooling for NetBeans for the next release. One area that comes up a lot when I talk to users is schema management - making it easy to make, communicate and deploy changes to your schema as part of ongoing maintenance of your application.

The first step in this analysis is use cases. Here are some of the use cases I have come up with. Note they are not talking about a particular tool or a particular solution, but the situation the user is in and what they want to accomplish.

My question to you is: which of these are like really important to you? Which ones would you love to see solved? And which ones am I completely missing?

By the way, I know there are a lot of possible refactorings, but I just list some obvious ones to give you the idea of what I'm thinking and get your feedback. Listing all possible refactorings would be a tome, not a blog.

What I'm more interested in is what are the changes you most commonly do and which would love do be able to do as you code.

Changing/Refactoring During Application Development
  • Add a Table - A developer is modifying their application code and discovers they want to add a table. They want to do this with minimal interruption to their coding flow. For example, they could be writing a query that refers to a non-existent table and quickly add that table to the database.
  • Rename a Column - A developer is modifying his application code and wants to rename a column with minimal interruptions to his application flow. This includes quickly finding and updating all other references to their column within his code.
  • Add a Column - A developer is modifying her application code and realizes she needs a new column. She wants to add the new column to the database with minimal interruption to her coding flow.
  • Change Column Type - A developer is modifying their application code and wants to change the type of a column with minimal interruption to her application flow. This includes quickly finding and updating all other references to her column that are impacted by the change.
Deploying An Application
  • Initial Deployment - A developer has an application they want to deploy into testing or production. Their application includes a schema definition that needs to be applied to another database. He wants to create the schema as part of deploying the application.
  • Upgrade - A developer has made changes to their application that involves changes to the schema. She wants to deploy the new version of the application, and wants to apply the schema changes without losing data.
  • Review Changes - A developer has made changes to their database schema in his development database and wants to review and perhaps modify the set of changes.
  • Publish Changes - A developer has made changes to her database schema and wants to make those changes available to others, either for review or so they can be applied to another database that is controlled and managed by someone other than the developer.
  • Commit Changes to SCM - A developer wants to commit an application change to an SCM for version control and collaboration. Their application change includes schema changes, and the developer wants those changes to be included with the commit, so that other developers can apply those changes as part of upgrading the application in their workspace.
So, there you have it, the first steps towards understanding what we might want to do around this. Thanks, in advance, for your feedback.


ijuma said...


These are all very desirable features, but if I had to choose I would say the two most useful ones to me would be:

- "Commit Changes to SCM"
- "Upgrade"

There are frameworks that deal with this, but getting some support from the IDE would be nice. An example is:

hantsy said...

Why not port the MySQL tools to NetBeans platform,I need a design tool like MySQL workbentch。

Anonymous said...

Whilst understanding this isn't good practice, I frequently end up working on a DB where I cannot be confident SCM reflects installation. As such, my starting point is extract DDL from prod, uat, etc databases and SCM and reconcile them.
There is an additional complexity that in extracting the DDL you can separate tables / procs /etc however the SCM may have a greater granularity (procs for user use, procs for batch use, etc), so the SCM needs flattening into just object types to enable a clear diff.
Any tooling that helps with that would be great.....

Anonymous said...

If you could sort out the "Upgrade" issue that would be great.

A nice visual query builder in netbeans would also be good.

Anonymous said...

Of Changing/Refactoring during Application Development, I'd be happy with just the latter one (Change column type and care about propagating the change to other tables). The other refactorings seem not too important/frequent to me.

Of Deploying An Application section, the second one is the most important to me (Upgrade in production without losing data). The initial deployment would be interesting if it involves (some level of) cross-database support. The other ones are not so important to me (if you care to keep a text file with the SQL DDL commands, you should be done with standard diffs).

emstol said...

Most common are first four. Their usage frequency is much more frequent than other. Besides first four would be lower level tools than other, so for me basic is implementation of first four usecases.

Anonymous said...

The most important base tool for Netbeans is IMHO an ER design tool!

There is already a request with dozends of supporters for.

Thank you.

David Van Couvering said...

I am quite aware of the need for an ER design tool. I am asking questions about refactoring because it's not clear what, if anything, is needed. With E/R diagram, it's very clear it's needed and what it should look like.

For full, rich E/R support, MySQL Workbench is the right choice. It will be available on Mac and Linux as well as Windows in the next few months. There are also plans to have Workbench work with not just MySQL but all the key databases within the next few years.

That said, many people don't need a full E/R tool; most people manage their schemas through SQL scripts. Many people have said a simple read-only E/R visualizing tool would be sufficient and very useful. So we are looking very seriously at providing a plugin that provides a read-only visual view into your database.

Dan Brown said...

I second the need for the changes. If a schema change cannot be made without savaing and restoring existing data (eg column type changes in Oracle), feedback needs to be provided before attempting the update. It would be great if there could be a direct tie in to tools to do the save and restore (convert data type).

If a tie in to a complete ER processor is not in the works, at inport and export of the DDL from/to the IDE schema, and comparison/resolution of differences would be very helpful.

Anonymous said...

From a documetation viewpoint IMHO ERD is the best tool to use. What can also assist the developer is the use of Data Flow Diagrams in which it is possible to see (get overview) which areas of code modify the database (update, delete, add)