The Goal: To graphically design a relational data model and generate both DDL to create the DB structure and the PHP5 class structure including public/private member variables and getters, setters, update, create, delete with working DB link. Icing on the cake would be if the data model could be updated graphically after generating the code and generate a patch file for updating the PHP5 code and an SQL script with the appropriate alter/add/drop commands to update the existing schema.

Eclipse Frameworks

Eclipse has a complex collection of various layers of frameworks, some seem a little redundant or aren’t immediately clear what functionality they provide. Most of these frameworks are in “incubation” phase. There are also proprietary plugins that utilize these frameworks but are not distributed using the official eclipse repositories. The frameworks serve simply as platforms to aid development of functional plugins primarily by third parties. The Eclipse project itself doesn’t provide any implemented solutions except for UML2Tools which is a component of the MDT.

The eclipse modeling project consists of the following:

• Eclipse Modeling Framework Project (EMF) – (link) The EMF project is a modeling framework and code generation facility for building tools and other applications based on a structured data model. From a model specification described in XMI, EMF provides tools and runtime support to produce a set of Java classes for the model, along with a set of adapter classes that enable viewing and command-based editing of the model, and a basic editor.
• Model Development Tools (MDT) – (link) The Model Development Tools (MDT) project focuses on big “M” modeling within the Modeling project. Its purpose is twofold: To provide an implementation of industry standard metamodels. To provide exemplary tools for developing models based on those metamodels.
  • UML2Tools (link) is a set of GMF-based editors for viewing and editing UML models. It is an optional component of the MDT project. It does not provide entity relationship modeling so I’m not going to say much more about it.
• The Eclipse Graphical Modeling Framework (GMF) (link) provides a generative component and runtime infrastructure for developing graphical editors based on EMF and GEF. The project aims to provide these components, in addition to exemplary tools for select domain models which illustrate its capabilities.
• The Graphical Editing Framework (GEF) (link) allows developers to create a rich graphical editor from an existing application model. GEF consists of 2 plug-ins. The org.eclipse.draw2d plug-in provides a layout and rendering toolkit for displaying graphics. The developer can then take advantage of the many common operations provided in GEF and/or extend them for the specific domain. GEF employs an MVC (model-view-controller) architecture which enables simple changes to be applied to the model from the view.GEF is completely application neutral and provides the groundwork to build almost any application, including but not limited to: activity diagrams, GUI builders, class diagram editors, state machines, and even WYSIWYG text editors. The Logic Example, one of the provided examples, is pictured below

Free Standalone and Plugin Implementations

Clay Database Modeling

Clay (link) is an Eclipse plugin by Azzurri, a Japanese company. The free version provides nearly all features needed to build a proper ERD. The Pro version give support for enterprise databases, printing and exporting images, and document generation. Clay exports clean DDL code for mysql. It can also reverse engineer databases.

Conclusion: This is a decent choice. It won’t lock you into using it. It doesn’t generate an interface for the application layer so it doesn’t fulfill the goal of this study.

RISE

RISE (link) is a freemium, proprietary, Windows only solution, but it does some good stuff. RISE is the only free solution I’ve found that will generate both the data layer and the application layer based on an entity relationship diagram. It has one of the easier to use interfaces for creating entities, attributes, relations, and views. The stereotypes concept makes building common structures such as trees, lists, classifications, and extensions easier. Connecting a customizable Interface to an entity or view allows C# or PHP code to be generated, giving your application layer access to the data layer. The application code fully implements methods that perform standard operations on your data layer. RISE will even generate a SOAP web service interface and provides an AJAX framework.

Despite all that praise, there are a few problems with RISE (not including it being windows only, etc). There doesn’t seem to be a way to create indexes, primary keys, auto-incremented values, or adjust the precision on data types.

What RISE generates is not proper DDL or a database that actually reflects your ERD. It produces a stored procedure that creates four tables for keeping track of the log and model versions in addition to the actual entity tables. This is presumably so it can gracefully upgrade the structure to a new version without loosing data but it results in an unclean database.

Conclusion: RISE may be a good choice for some applications but the fact that it doesn’t follow standards makes me disqualify it for lack of extensibility if you should ever decide to stop using   it.

ArgoUML

ArgoUML (link) is an open source, standalone UML and code generating application written in Java and maintained by Tigris.org, the same group that does subversion. It doesn’t support modeling database schemas out of the box, but it does have an officially supported plugin that does so called argouml-db. As of this writing the latest argouml-db plugin isn’t supported in the latest build of ArgoUML so you’ll need to get the bundled version from the sourceforge site https://sourceforge.net/projects/dbuml.

My impressions is that the db plugin is a hacked together job and isn’t very well maintained. It’s tricky to even get running, clumsy to add attributes to a table. The properties also doesn’t have basic options that you would expect as part of a DDL such as length of columns or auto_increment functions or null / not null. Generating the code from the bundled version gives the option of generic “SQL” and java. The sql didn’t work out of the box and the generated file only contained an error. The java code didn’t include any getters, setters, or link to the database. Instead of primitive data types the generated java code imported types.sql.VARCHAR and similar.

Conclusion: not worth anyone’s time for ERDs

Umbrello

Umbrello (link) is an open source application that is part of the KDE project, which is primarily targeted at linux distributions. Getting the latest Umbrello working on mac and windows can be a bit more of a pain. Macports and Fink as package managers for Mac and both have up-to-date versions of most mainstream linux software. You can compile from source most of KDE including Umbrello. It might be something you want to do overnight. Expect to encounter errors which you’ll have to find your way around. Needless to say, this is not a user-friend installation in non-linux environments.

Umbrello does a decent job of modeling relational databases (entity relationship model) and includes all expected features including data types and properties, auto-increment, foreign key constraints, etc. It does a fairly solid job of generating mysql or postgresql DDL, but it won’t export the data model to php. You’ll have to create a separate class diagram for that.

Conclusion: if you run linux this is a decent option considering the alternatives.

Non-free solutions

• MySQL Workbench 5.1 – $99 – http://www.mysql.com/products/workbench
• DeZine V5 – $245 to $745 – http://www.datanamic.com/order/index_am.html
  • Handles schema versioning and can generate update script (expert version only)
• Visual Paradigm – $59 to $1399 http://www.visual-paradigm.com
  • Database Visual Architect eclipse plugin – $399 to $699
  • Very powerful, enterprise solution, does it all

Conclusions

This is a relatively uncrowded market for robust solutions. The only two tools found that satisfy the core requirements of my goal are Visual Paradigm’s Database Visual Architect, the enterprise level proprietary solution and in a somewhat broken way, RISE, the free solution. No other tools produce both data and application layer code. I discount RISE for not keeping with standard methods and not producing extensible, clean code. I discount DB Visual Architect for it’s prohibitive cost in non-enterprise environments.

My chosen solution is the Clay Database Modeling plugin for eclipse, which does a good job of modeling and exporting DDL for the data layer. The application layer will need to be designed and implemented separately.

Comments are welcome on these solutions or any others not included.