Re: SQL Plugin for DIA

[Andrew Halper <ashalper acm org>]

Nathan Stitt wrote:

> I've extended Andy Halper's c sql plugin for DIA (found from:
> ftp://az.water.usgs.gov/pub/ashalper/src/dia/plug-ins/ off of
> http://www.schemamania.org/ ) to support foreign keys using UML
> Message lines.  I've attempted to contact him using the email address
> in the README file, but it bounced. If anyone knows how to reach him
> please let me know.

oy, I'm here.

> As my changes touch pretty much the entire sql-export.c file, I've put
> it up at: http://www.stitt.org/code/dia-sql/ rather than make a
> patch.  It compiles (for me at least) against both 0.88.1 and cvs.

I'll try to take a look at it tonight.  I had planned to make a more
official patch once I got the import (and export) code working with
StdProp, but I haven't worked on it recently.

> I did this by recording the coords of the connection points while
> creating the table, then comparing the end points of each UML Message
> to see if they were in the same position.  If so, I add a 'alter table
> XXX add constraint foreign key' statement at the end of the sql file.

Cool!  I had vaguely thought of doing something like this, but thought
it would be too challenging to code, and I had my hands full with my
dreadful SQL parser.

> Something is not quite right with this method, because if I load a
> saved diagram and attempt to export, only about half off the UML
> Messages will match.  This is apparently because their end points are
> not in the 'exact' same location as the handles on the Class
> attributes.  However if I select all objects on the diagram and move
> them around a bit, then everything lines up ok.  Because of this bug,
> I'm not sure if this is worth submitting, but figured it couldn't
> hurt.  It works well enough for what I need to do at this point at
> least.  A better solution would be to do 'fuzzy' locations so that if
> a line was a few coords off, it would still match.

yes...Challenging.

> Of course the
> *right* thing to do would be to recode Dia so that the info could be
> easily retrieved.

Yes.  My sketchy plan (and background for my sketchy plan) is:  The only
reason I chose the UML object to represent database schemata was that it
seemed to be the object best suited for the task, and I had a book on
how to do it.  Other than the import plug-in, further evolution (even
with the StdProp code) is going to be tough-going.  I gather people are
working on easier ways of creating custom objects (i.e. UrShape), so
once that's done, I was thinking of creating some type of IDEF1X/IE
object, and pursue further SQL support in that  direction (probably,
ultimately abandoning the UML <-> SQL code).

> Perhaps this is possible already, and I've wasted my
> time hacking this up

I don't think so.

> Table name are no longer converted to uppercase, but are left the way
> they were typed in.

This is Joe Celko/IDEF1X style.  I had planned to make it optional.

> Primary keys are indicated by clicking 'class scope' in the UML
> Class dialog box, rather than typing {OID} in the column name.

As long as it's kosher with the UML definition, it's fine with me.  I
just used "{OID}" tags because that's what they use in the book I had,
and I didn't know any better.

> Tables are created from each UML Class unless they are tagged as
> 'abstract' in the Dialog box. In the original they were not created if
> they had {persistent} in the table (class) name.

"Persistent" is legit (though annoying) UML.  I'm not familiar with
"abstract".

> Added drop table statements before the create table statement.

Also should be optional.  Believe it or not some interpreters poop out
on the DROP TABLE statements if the table does not exist.

> If these changes are not wanted, it should be fairly easy to put it back
> the way it was.

Not at all.  Thank you very much.  With the intrinsic lazyness and then
the international turmoil, I haven't been working on this much lately
and I'm glad to see someone is interested in it.
 
> If there is any interest in this I will, time permitting, make it
> stand alone with autconf support for easier installation.  At the
> present it still uses Andy's method of untaring in the dia source
> tree and compiling.

Yes, this is a pain.  I did it this way because 1) I didn't know any
better, 2) there wasn't much enthusiasm for adding it to CVS at the
time, and 3) I didn't think it should be put in CVS until I wrote the
import code and it was somewhat stable.

Andy