Bug 73632

Created attachment 4383 [details]
Sqlwriter.cpp and .h update

I'd like to have the option of table relationship diagrams in umbrello so you could model databases properly (this is not part of UML however).

Subject: Re: [Uml-devel]  New: umbrello sqlwriter output is anything but SQL..

Riku Voipio <nchip@kos.to> [040128 00:31]:
> http://bugs.kde.org/show_bug.cgi?id=73632      
>            Summary: umbrello sqlwriter output is anything but SQL..
> ...
> I have written a small patch (will attach soon) which produces usable output:

Would you please provide the patch?

Steinchen

Subject: Re: [Uml-devel]  New: umbrello sqlwriter output is anything but SQL..

Riku Voipio <nchip@kos.to> [040128 00:31]:
> http://bugs.kde.org/show_bug.cgi?id=73632      
>            Summary: umbrello sqlwriter output is anything but SQL..
> ...
> The umbrello generate code -> SQL does not produce anything
> meaningfull. comments get mixed between declarations

Maybe this is a feature. Or how do you add things like NOT NULL? If you use
the comment, you can add it directly behind the column name and type.

> and most declarations are not separated by "," like they should.

This seems to be true sometimes, e.g. if you create a empty table, so a
class without attributes.

>         CONSTRAINT  FOREIGN KEY (1) REFERENCES blacklist(0..*)

You also have to give the association a name, this would be the key. The (1)
is wrong, I think it should point to the column in the source table, which
can be set as a role.

Also, I don't understand, why the constraint is added to both tables. I
think it should only be added to 1 table (where the rhombus is connected
to).

So, maybe you can rework your patch, I won't apply it now. I think the
comment thing is a feature, even so it is not very clear to the user.

Maybe you can start with making a class diagram and write down what sql code
you expect. We could use this file for future test cases. Please also have a
look at the properties of the association between the classes. You can set
an association name and the roles. I think the roles are misinterpreted at
the moment as well, because instead of the names the count or something like
this is added to the sql output.

Thanks for your help,

Steinchen

bugzilla decided not want mail from me, so let me past it...

On Sun, Feb 01, 2004 at 06:15:53PM -0000, Sebastian Stein wrote:
> ------- You are receiving this mail because: -------
> > The umbrello generate code -> SQL does not produce anything
> > meaningfull. comments get mixed between declarations
 
> Maybe this is a feature. Or how do you add things like NOT NULL? If you use
> the comment, you can add it directly behind the column name and type.

Thing like NOT NULL are not really comments they are vital part 
of the data definition. It would be inconsistent to have 
varchar(200) (the field lenght) in the attribute type field, 
and other parts of definition in the comment field. 

Plus, where would put the REAL comments of the field, if the
comment field becomes magically an semantic field. Like in
the example I gave in the bug report, where I documented the
possible integer values of an field. 

> > and most declarations are not separated by "," like they should.
 
> This seems to be true sometimes, e.g. if you create a empty table, so a
> class without attributes.

This bug is because the original author is passing first by value
to printAttributes and uses it like it would be passed by reference.
You will only notice this if you use attributes of diffrent scope
(public, protected, private)

> >         CONSTRAINT  FOREIGN KEY (1) REFERENCES blacklist(0..*)
 
> You also have to 

I didn't touch the FOREIGN KEY code - It was broken already. I
don't really know what it is supposed to do, so I only fixed
the attributes part.

> give the association a name, this would be the key. The (1) 
> is wrong, I think it should point to the column in the source table,
> which can be set as a role. 

Using the multi fields in creating foreign key definitions like
the current code does is definetly wrong yes. 

> Also, I don't understand, why the constraint is added to both tables. I
> think it should only be added to 1 table (where the rhombus is connected
> to).

Actually, In my tests, the constraint is only added to the correct table
(the one with the foreign key).

In this case the correnct constraint would be:

FOREIGN KEY (tld) REFERENCES tld (tld_text)

or more compactly in the attribute definition

        tld varchar(40) REFERENCES tld,

since tld_text is the primary key of tld, we dont need to type it
manually.

All of this offcourse using postgresSQL syntax.

> Maybe you can start with making a class diagram and write down what sql code
> you expect. 

The problem is, that UML doesn't really support SQL-like relationships,
it is ment more to c++/java style relationships. I need find an 
UML+SQL book if I want to fix it more seriously.

> I think the roles are misinterpreted at
> the moment as well, because instead of the names the count or something like
> this is added to the sql output.

It uses the name of association as the constraint name and Multplicity
as the field being referenced... Using rolename instead of multicipity
would be a improvement thou, altough I dont think the UML assosciation
rolename is meant for that.

Subject: Re: [Uml-devel]  attribute comments with sqlwriter produces invalid SQL

Riku Voipio <nchip@kos.to> [040202 08:30]:
> > Maybe this is a feature. Or how do you add things like NOT NULL? If you use
> > the comment, you can add it directly behind the column name and type.
> 
> Thing like NOT NULL are not really comments they are vital part 
> of the data definition.

I know.

> It would be inconsistent to have varchar(200) (the field lenght) in the
> attribute type field, and other parts of definition in the comment field. 

Mmh, yes. So you would say if a not null is needed, you enter as datatype
"varchar(200) not null", right?

> Plus, where would put the REAL comments of the field, if the
> comment field becomes magically an semantic field.

This would be impossible.

> This bug is because the original author is passing first by value
> to printAttributes and uses it like it would be passed by reference.
> You will only notice this if you use attributes of diffrent scope
> (public, protected, private)

And what should change to an attribute if the scope changes?
 
> > You also have to 
> 
> I didn't touch the FOREIGN KEY code - It was broken already.

Yes, I know.

> I don't really know what it is supposed to do, so I only fixed the
> attributes part.

But we should do it right, so the best thing would be to fix both parts.

> > Maybe you can start with making a class diagram and write down what sql code
> > you expect. 
> 
> The problem is, that UML doesn't really support SQL-like relationships,
> it is ment more to c++/java style relationships. I need find an 
> UML+SQL book if I want to fix it more seriously.

Oh yes, but be prepared that this is a neverending topic. I also looked at
it and there are different approaches how to interpret relationships between
the classes. I mean attributes are easy, but relationships are more
difficulty, because there are several ways how to solve it. But please go
on, I'm really interested what you can find out.
 
Steinchen

- fixed adding comma for attributes with different scopes
- fixed foreign key constraint