SQLAlchemy issues & my improvement needs more test

[GoogleCodeExporter]

recently, i have work hard for a complex database with sqlalchemy.
and i make use of wwwsqldesigner.
but i found that the sqlalchemy db in wwwsqldesigner use old datatype and .

so i rewrite the datatype.xml and the output.xsl  , anyone who would like
it please test for it.

needs to do:
1,the association table : at present, the wwwsqldesigner have too poor
ability to solve this problem. all the relation to table and the relation
to column have the same xml output :
<relation table="tablename" row="tablerow" />

more advice for wwwsqldesigner:
1,comment is very important, even in the large database system. so maybe it
needs to appear in the main window.
2,the datatype name may need local name for internationality.

Original issue reported on code.google.com by jobinso...@gmail.com on 26 May 2010 at 7:00

Attachments:

• datatypes.xml
• output.xsl

[GoogleCodeExporter]

I do not understand the point about "the association table". All DB relations 
(more
precisely: foreign key constraints) always connect a column to column, never 
column
to table. What is the behaviour you would like to have?

Original comment by ondrej.zara on 26 May 2010 at 10:31

[GoogleCodeExporter]

association table = many-to many relationship
and in sqlalchemy declarative scripts , it needs to add some lines to the top.

for instance(from turbogears2 auth.py）:

__all__ = ['User', 'Group', 'Permission']

#{ Association tables
# This is the association table for the many-to-many relationship between
# groups and permissions. This is required by repoze.what.
group_permission_table = Table('group_permission', metadata,
    Column('group_id', Integer, ForeignKey('group.group_id',
        onupdate="CASCADE", ondelete="CASCADE"), primary_key=True),
    Column('permission_id', Integer, ForeignKey('permission.permission_id',
        onupdate="CASCADE", ondelete="CASCADE"), primary_key=True)
)

# This is the association table for the many-to-many relationship between
# groups and members - this is, the memberships. It's required by repoze.what.
user_group_table = Table('user_group', metadata,
    Column('user_id', Integer, ForeignKey('user.user_id',
        onupdate="CASCADE", ondelete="CASCADE"), primary_key=True),
    Column('group_id', Integer, ForeignKey('group.group_id',
        onupdate="CASCADE", ondelete="CASCADE"), primary_key=True)
)

#{ The auth* model itself
class Group(DeclarativeBase):
……………………

Original comment by jobinso...@gmail.com on 28 May 2010 at 10:33

[GoogleCodeExporter]

The M:N relationship cannot be modelled without additional table. WWWSQLDesigner
would not (should not!) automatically create this table for you; it is 
necessary to
create it manually. This table then uses two foreign key constraints, one for 
each
table it relates to.

In your example, the group_permission_table is connected to group.group_id via 
one
foreign key and to permission.permission_id via second foreign key.

In other words: WWWSQLDesigner does not visualize the *logical* level of your 
model,
where Permissions and Groups are connected with a M:N relation. Instead, it
visualizes the physical level, where the database actually contains three 
tables to
achieve the needed behavior.

Original comment by ondrej.zara on 28 May 2010 at 12:35