Re: SQL Plugin for DIA

["Andrew S . Halper" <ashalper acm org>]

On 2001.09.19 06:40 Nathan Stitt wrote:
> Andrew Halper <ashalper@acm.org> writes:

> > 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.
> 
> If you've downloaded it before around 10 pm CST, please do so again,

I didn't.  Intrinsic lazyness.  Beer drinking.

> as I've expanded it a bit further to put index columns into the the
> operations tab of the UML dialog.  I've attempted to toggle if it's a
> unique index by whether or not 'class scope' is selected, but
> something strange is going on and I can't get this to work.  Look
> around line 245 or so for the code.

OK, maybe tomorrow at work (beer drinking frowned upon there).

> Thats a relief.  After I did all this I started to think I really
> should have posted to the list asking if there was an easier way.  I
> was half afraid someone would conk me over the head and say 'why in
> the world did you do all that?' :)

No, I think you're on the right track.  I think the early Dia designers wanted a
ruthlessly fast diagramming tool, so peripheral stuff like code-generation was
not on the radar.  In one way I think this is good, because it kept things
focused on producing the ruthlessly fast diagramming tool we now have, rather
than abstract conceptual design (compare/contrast to the ambitious,
well-meaning, but ultimately mostly unreleased FreeCASE, which some here may
recall).  Now the code-generation pests must contend with some rather low-level
data structures, but this is gradually changing, and IMHO I think that's about
the pace it should change.

> > > 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.
> 
> Yes optional on the most of the changes would be the best way to do
> it, but to do that you somehow have to code a dialog box for the
> worksheet, I'm not sure that's possible without extending Dia itself.
> Comments?

Further down the road I had planned a pop-up dialog called from the plug-in
where these options could be set just before writing the DDL file.  I'm pretty
sure there's no technical reason why a plug-in could not put up a small dialog
box, and there is some precedent for this type of behavior in commercial
database design tools.  Also, options in the dialog box could be more
persistently defaulted (perhaps even the appearance of the pop-up dialog
itself?) with resources in the .diarc file.

> I actually like using upper case for table names as well, but I
> figured it was best to use whatever the user types in.  If they want
> uppercase, they can type it into the definition.

I think my twisted logic was:  UML class names would be capitalized, but that
would look weird in the DDL, and I liked the all-uppercase names, thus the
enforced all-uppercasing.  You're right though.  This does seem too
paternalistic.  An all-uppercase/all-lowercase table name option would be more
useful.

> > > 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.
> 
> All I was attempting to do for this is get away from having to type
> something specific into the definition and making the table display
> wider than needs to be.  The project I did this for will probably have
> around a hundred tables, and it's going to be tough to display them
> all in an orderly fashion.  I would rather have a primary key
> indicated by it being underlined, which is what happens when the
> 'class scope' is checked.  As to whether this is in accordance with
> the UML specs, I have no idea, as I have only a passing familiarity
> with UML.

Oh.  Yet another motivation for a dedicated database-design object.  The data
modeling tools I've used usually allow optional display of primary key
indicators (along with other denotations).

> > > 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.
> > 
> 
> Agreed, however I can't figure out a good way to give the user the
> option.

See my half-baked idea above.

> The project I'm planning to use this for is based on
> postgres, and it will give an error on a drop table without a table
> already existing, however it will continue, so is not fatal.  For
> MySql and Oracle it would be better to do a DROP TABLE IF EXISTS
> table_name, unfortunantly Postgres doesn't support this.

Interesting.  I think Ingres (distant cousin of Postgres) is one of the ones
that poops out, although I think this a Terminal Monitor default that can be
overidden.

> > 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.
> 
> I agree it's a pain.  Ideally, I think part of the UML plugin should
> be ripped off and a larger SQL plugin created using only the Class and
> Message objects from it.  This way the Table properties box labels
> could read stuff like 'Primary Key' rather than 'class scope'.

This description alludes to a new Dia object.  The scope of plug-ins is limited
and they have no binding to a particular Dia "sheet", thus any diagram they
encounter could potentially be fraught with objects they are not interested in. 
We must await The UrShape!

> Once
> this is done it seems like it might be worthy of inclusion into the
> core dia package.. right now it's rather hackish in my opionion.  But
> I'm still happy with it, as I can now do my sql table creation using a
> visual tool.  I had been using powerbuilder on windows, but the
> reboots were driving me mad.

Yes, it is hackish.  I knew that when I wrote it.  I thought better hack than
non-existent.  There were/are some programs around for converting Dia files into
database schemata, but I was conditioned to do it directly from the application,
and so I wrote the plug-in.  Also I'd really like to run Linux on my machine at
work, and ERwin/Notes are the last things I need to replace.

Andy