The DACE Database Creator/Designer

DACE wants to be a database designer when it grows up. For some reason open source really lacks a good database designer. Some tools do exist but they are either buggy, slow or do not support the databases I need. Data Architect from thekompany.com looks very promising but it looks like they stopped working on it. So I started to write my own database editor. This tool is meant to be a database schema tool, not a database data tool (for that many excellent tools exist, like TOra). This tool should be like ERWin or ER/Studio but more "basic" and more user friendly 8-(. Not to mention cheaper ;-)

Design

DACE is a Java GUI program which uses JGraph as the main component to render the schema. It uses a generic mechanism to support multiple databases. I currrently focus on postgresql and Oracle (in that order). The program should be able to create a generic (database independent) database schema and generate SQL create scripts for a specific database platform. In a later stage the generic schema can be "viewed" and edited for a specific platform. I will not directly separate physical and logical views as I do not find that of any use.

The schema can be split into multiple (and possible overlapping) "subschemas" to make maintenance easier. These "subschemas" are just a partial view on the actual, complete schema with different view characteristics. These subschemas can be generated too (resulting in a "subset" SQL create script).

The focus for the program are ease of use and good print output ;-)

Current state

I started this project in 2002 but only now (2004) got more time (and need) to continue with it. The program is not even far from completion - it is currently unusable for work. The first goal is to get a very incomplete version working which has the following abilities:

  • Reverse engineering an existing database (Postgresql, Oracle) - 90% complete
  • Editing basic table, attribute and key data - 90% complete
  • Creating new tables, attributes and keys - currently implementing
  • Editing relations between tables - todo
  • Saving and loading the schemas - 100% complete
  • Generating SQL for the data present in a schema for the supported databases (Postgresql, Oracle) - todo

As soon as this first version is working I start working on the more advanced features. The next milestone is expected to have

  • Subschema's
  • Scripted (Beanshell) schema generation and schema checks
  • Control over the default generic <-> specific database type mappings
  • Printing!
  • HTML Publishing of the schema
  • Layout helper functions

Screenshots

The current development version can reverse engineer Oracle and Postgresql databases, can save and load these schema's, and can edit table data. To give an impression I've made some screenshots of the current version. The database schema shown therein is the database from my CMS system (COMA) reverse engineered from a Postgres database. And since the file format and stuff for DACE is still in flux I did not edit/layout the schema - that would be a waste of time.

Screenshot 1: the main screen

Main screen thumbnailThis shows the main window of the program with a schema loaded but not laid out ;-) The tree at the left contains all tables and will contain all "subschemas" and "database platform" views in the final version. The tree can (later) be used to quickly locate a table in the graph. The graph shows the tables and the relations between them. The table background can be set to different colors which makes it easy to group tables by function within a schema.

 

Screenshot 2: reverse engineering in progress

Reverse engineering a postgres (or Oracle) database works (at least on my databases ;-). The reverse engineering code currently uses the implementation within the JDBC driver (getDatabaseMetaData()) to reverse engineer a database. Later versions will augment this with data from the data dictionary.

 

Screenshot 3: editing a table

The edit table dialog. This allows editing all of the data for a table: attributes, keys and comments. The next version might have storage parameters too (in the physical view for a platform). The edit dialog allows fast entry of new columns by allowing you to specify not just the column name in the "name" dialog but you can also specify the type, sizes, nullity and primary key in it. By naming an attibute "myattr a40 *" you say that the attibute name is "myattr"; it's type is varchar(40), and it is part of the primary key. The dialog futher allows comments on both table and columns. The domain functionality is present in the GUI but remarkably absent in the code 8-(...

 

Screenshot 4: editing keys

The edit keys dialog. Move attributes into the key, determine the order of the attribute in the key and whether the sorting order is ascending or descending. For some reason the dialog comes up way too big but we'll fix that later.

 

Screenshot 5 and 6: editing relations.

The edit relation dialog, with some of the panes.

 

Progress report

When What
2004/05/30 Relationship rendering fixed: crow's feet now render correctly on all sides of a table. Updated the screenshots to reflect this. Added and completed the "edit key" dialog and allow keys to be added, deleted and modified from it. Some bugfixes in table code. Coded "display" part of the relation editor, plus the code to handle "phrases".
2004/05/25 Render relation cardinality code added. This was quite a lot of work ;-) I still need to fix the code to render crow's feet horizontally.. See the screenshot for the current result. Also fixed some small bugs like the key layout being lost after a load.
2004/05/24 Adding a table visually thru dropping a table on the schema completed. Rewrote the JGraph model code from the QD version to a more formal version. Save and restore main view size. Generalized the "drop things on the UI" code to prepare for relations. Started relations design and code.
2004/05/23 Completed most attribute editing. Changed save/load code and nodes code for genericism. Implemented base Keys panel. Created this page.
2004/05/22 Rewrote initial GUI code to use multiple frames for multiple schemas. Reordered the menu and toolbar code. Added JGraph layout functions (they suck currently though - something seems to go wrong 8-()
2004/05/20 Updated the old code to use JGraph 3.4.1, and uploaded most dependent libraries to Sourceforge