| The ancient proverb, "A picture is worth a thousand words" is neither ancient nor proverbial. It was an advertising slogan for baking powder in the early 20th century. Many Chinese streetcars carried this version of the ad. |
 |
|
Schema Mania is a place for people who like (or need, or are just good at) database designs. It's completely non-profit, dependent on the enthusiasm of its visitors and the talent of its contributors. |
Schema Mania was conceived as a repository of database designs. You'd be able to come here, browse for a database design in your "problem space". With luck, you'd find something at least similar to what you had in mind. You'd download it, adapt it to your needs, and be happy. www.schemamania.org would be a web of database designs, if you will.
But, a funny thing happened on the way to the forum. Much of the technology that Schema Mania needs is not ready for general use. What's available is nascent; the rest is missing. However valuable the concept might be, Schema Mania lacks both software and standards. It thus became part of Schema Mania's goal to bring together people of various disciplines, to help them find each other and create better tools.
Given the state of technology and standards today, what could it mean to "browse database designs"? What sort of file would you view and/or download, and what would you like to be able to do with it?
For viewing purposes, most people like a picture, a diagram of the database tables and their relationships. Pictures are, as Confucius never said1, worth a thousand words in this regard. A picture of a database is very helpful for human beings who want to understand the design. For implementation, though, we need a way to communicate the picture to the database. Later on (or, sometimes, earlier on), it's nice to have a way to convert existing database designs into pictures. We need, in other words, an intelligent picture, one that can be created from words and converted to words. We need it, but it doesn't exist. Not yet, not quite.
It's hardly news that file formats for pictures abound on the web. JPEG, PNG, you name it, the web does graphics. What these formats have in common is that they treat the picture as a block of dots. To the extent that they can be said to contain information, that information is only useful to the program that displays the picture. They are to intelligent diagrams as a fax is to email: Understandable and useful to the human being, but intractable, just a blob of dots.
The great thing about standards, as some wag said, is that there are so many to pick from. Pictures have not stopped at the blob-of-dots dead end. The W3C developed Scalar Vector Graphics (SVG), an XML derivative. And there's a little-heralded part of the Gnome Office called Dia. Dia is a diagram editor and, like the rest of the Gnome Office, it stores its files in XML format. Dia shapes can be described with SVG, and the whole Dia diagram is stored as XML according to a freely available DTD. In a market defined as "free software to create diagrams stored in a published XML format", Dia is alone. It has no competition.
Dia is short for "Diagram". It might as well stand for "Data In Action". A blob of dots it's certainly not. Dia makes intelligent pictures.
It didn't take long (nothing does, in this environment) before someone came up with a way to convert a Dia diagram to SQL. Someone else created a way to convert Perl and C++ to UML Dia diagrams. As of this writing, work is underway to create links in Dia files, to connect them together.
The efforts to date to connect Dia diagrams to database servers have (as their authors acknowledge) been quick hacks. They focus on a subset if one or another flavor of SQL, to get a job done. No criticism here: these programs are Good Things. But their authors are working in isolation without a framework and without any way to build on one another's work.
To be a full-fledged database diagramming tool, Dia needs:
Dia++ is an idea, not a separate project, to make Dia a better database diagramming tool. That means improvements to Dia itself, and to the surrounding tools and glue.
I never meta architecture I didn't like?
What does Schema Mania need to fulfill its purpose? Free tools and free thinking.
Except for the first item, software already exists to do some part of each of these things. But what is the first item doing there, and why isn't it just a Dia file?
By the way, "databases" means relational databases. Maybe object-oriented databases or some other kind of database could be represented in Dia and within the meta-architecture described here, but the author doesn't have any experience with such things and consequently can't say much about them. At this point in the history of technology, relational databases are widespread, well-understood, entrenched, and unchallenged. The envisioned population of Schema Maniacs have relational designs to use and share. No offense intended to enthusiasts of more enlightened database architectures.
Database designs have no accepted XML DTD representation2, and Schema Mania needs one. Why not just use Dia's DTD and go home? Dia diagrams of necessity contain a lot of non-database information (color and location of objects, for example) and don't have any place for database rules (cardinality of a relationship, for example). After all, Dia is a general purpose diagramming tool. There's no assurance that an arbitrary Dia file contains anything resembling a database diagram. It could as easily be a circuit diagram or organization chart.
The would-be tool writer, who'd like to apply a Dia diagram to, say, a MySQL database, has to master the Dia DTD, apply a whole bunch of extra-Dia sanity checks to make sure the diagram objects are even vaguely database objects, before he can begin transforming the diagram to SQL. He has another hurdle, too: each target database vendor has a different SQL flavor.
The Dia-to-SQL tool thus has to solve at least two problems:
Now suppose there were an accepted XML DTD for relational database schema. If you're familiar with XML, you know that there's a standard in the XML framework for converting one XML document to another: XLST. All the rules of step #1 could be embedded in an XLST and processed with any program that understands XLSTs and DTDs. The operation is not really specific to SQL or even Dia. The challenge moves from parsing Dia files to defining rules in XLST. From code to data, in other words. A lot of people would say that's a Good Thing.
Step #1 is technology independent; it doesn't matter whose database server you use, there are still tables and keys and such. Step #2 involves a particular flavor of SQL, as defined by a particular version of a database server, and as such will have many implementations. Step #2 is obviously much easier to implement if step #1 is complete. That's the primary argument, in the Dia-To-SQL context, favoring an XML DTD for relational database designs.
This two step approach is not just a good idea; it's also been implemented once as a student project. Dominique Rohard wrote XSLT files and converted a Dia diagram to SQL using a generic XSLT processor. Dominique is French, which has fettered our communication, but was still a wonderful demonstration. Unfortunately, Dominique has gone on to other things, and the links she had provided to her work have gone off the air. Alas.
There is a standard that might be applicable: XMI. XMI is a meta-model interchange format being promoted by IBM and others as a way to exchange UML and MOF diagrams. It employs some extensions to XML (as this writer understands it). Because XMI can hold information about object models generally, it's a bit heavyweight for holding simple relational models. But it might suit our purpose.
For Schema Mania to achieve its real goals — to hold a repository of mix-and-match database design diagrams — we need a full-blown database diagram editor. Dia would be an obvious choice. But in the years since this page was first published (in 2001), no one has yet stepped up to the plate. Progress has been made, incrementally, in other areas.
Schema Mania aims to coordinate links and information about the disparate efforts bring free software tools to databases. If you know of such an effort, send me a link and a note about what you think about it. I'll add it to the collection.
It was pointed out to me that nothing has to wait for everything. There's really no reason schemas need to exist in diagram form. It would be convenient, of course, but it's not strictly necessary. And, for that matter, it's not strictly necessary that there be one diagrammatic format, especially if there were tools to convert between formats. Moreover, it might be a good idea to start the repository without these things in place, if for no other reason than to show the idea's viability. Not to mention that we'd have database designs to exchange. Which was the idea in the first place, you might recall.
So, send me your database, yearning to be free. It can be DDL, or in some diagramming tool you use. Maybe you've got some SQL and a blob-o-dots diagram generated from it. Send what you've got. I'll post it here with your attribution.
What have we got to work with, today? Below is a list of tools and projects I know about. If you know of others, please send them along
Some database tools are written in Java, perhaps because Java is fairly popular in corporate environments, where many database folk make their living. Your humble author doesn't run Java, because it's not free. That may change if and when I can compile the JVM from source and run it on NetBSD.
If you have experience with these tools and would like to offer your comments, send them here for inclusion and credit.
The most interesting is the schema processed by Torque, the database back end to Turbine, a well-known Jakarta project. The Torque DTD is maintained in a web-accessible CVS repository. From the XML schema description, Torque can generate database creation scripts for a bunch of databases (and Java classes). Torque does not use XSLT to effect these translations, however. Instead, there is a Java base class that can be specialized for a given flavor of SQL.
One fairly straightforward step for someone with the right experience would seem to be a Dia<->Torque XSLT file. The Torque could be the backend tool to generate SQL.
Among the projects that rely on Torque is Scarab.
XML is also the storage mechanism for Druid a database analysis, design and documentation tool. I could not find a DTD for Druid's files.
Nextobjects is an Integrated Development Tool for Enterprise Applications. It uses Turbine Torque for code generation.
Gaudi allows you to maintain visual repesentations of a database's layout, edit table's data, generate code to bind objects to tables, and export and import data in XML format to easely move data from one database to another. It's known to work with DB2, Oracle, PostgresSQL, PointBase, SQL Server and Sybase.
ERW is a set of specifications and tools that makes it easy to create, modify and maintain via web a database described by an entity-relationship schema.
"The" Entity-Relation modelling tool for the freeware community.
Automatically document a PostgreSQL based database in various output formats. UML, Docbook, HTML, and other outputs are coming. See also Rod Taylor's page.
DBDesigner 4 is a visual database design system that integrates database design, modeling, creation and maintenance into a single environment. It works specifically with MySQL, and is written in (of all things) Pascal.
Dia can be used to draw many different kinds of diagrams. It has special objects to help draw entity relationship diagrams, UML diagrams, flowcharts, network diagrams, and simple circuits.
A perl script that converts Dia UML objects to SQL for Postgres, Oracle, and Sybase, with more on the way.
A PHP parser that reads system objects from an Interbase/Firebird database and writes a Dia file.
Generates several kinds of code, including SQL, from a Dia UML Diagram.
Creates several kinds of outputs, including Dia UML Diagrams (and Graphviz), from a variety of sources, including SQL and Torque.
Alzabo is a data modelling tool. It allows you to define a schema by creating tables, columns, indexes, foreign keys, etc. It generates the SQL to create a new schema, and can reverse engineer an existing schema. Alzabo is smart enough to generate "differential SQL" in order to apply future changes.
Medoosa is a documentation tool for C++ that can produce UML class diagrams including generalizations and associations. Corrections can be made interactively in a diagram editor (Dia) and are fed back into the source as Javadoc-style comments. The XSLT processor is xsltproc
Graphviz is open source graph visualization software. Graph visualization is a way of representing structural information as diagrams of abstract graphs and networks. Some of the projects listed here produce Graphviz output.
UMLGraph allows the declarative specification and drawing of UML class and sequence diagrams. Requires Java.
A set of Perl modules that, amongh other things, generate "pseudo-ER diagrams"
This page updated $Id: index.html,v 1.13 2010/12/05 22:34:31 jklowden Exp $
| The ancient proverb, "A picture is worth a thousand words" is neither ancient nor proverbial. It was an advertising slogan for baking powder in the early 20th century. Many Chinese streetcars carried this version of the ad. |
|
About the author. Not much to say, really. If you know me, you might find my helter-skelter home page handy (or not). By day I work as a quantitative analyst; Schema Mania is strictly a hobby. Well, it's also a passion, because hobbies should be passions, after all.
 |
I advocate the use and credo of Free Software. |
 |
The web site you have come to runs on NetBSD, |
 |
was edited with Bluefish, |
 |
and Nedit |
 |
and receives mail with the venerable postfix system. |
Flawless DSL service provided by Speakeasy.net, an ISP that, unlike most, provides support for customers who want to run their own web sites.