db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject RE: SQL generated schema
Date Thu, 17 Nov 2005 16:21:39 GMT
One issue is that this problem is not specific to MySQL.  I know it
exists in MS SQL (a variation is even mentioned in the new HowTo).
I strongly suspect that most DB servers will have problems defining
keys that point to tables that don't exist yet.

So, to truly fix this problem, it should be addressed for all DB types
and not just MySQL. 

IMHO, there are three possiblities here.  The first is simply to 
document better that "master" tables need defined first in the 
XML file and to point out that generated SQL for "cross-keyed" 
tables will need to be manually modified to work.

The second and maybe hardest is to truly change the format of 
the SQL generated by Torque to create tables first and then do 
Alter Table statements later to add in constraints.  This 
probably means that all the DB Templates will need to be 
overhauled.. sigh.

On the bright side, the SQL99 standard does include ALTER TABLE ADD 
<Constraints>, but constraint mgmt is listed as an optional feature.  
I'd guess that most modern SQL DB implimentations support it though.
So the template changes should be easier... but they still need to
be tested against all the DB's torque supports... sigh.

Finally, an easier to impliment but doesn't fix all situations (this 
one in particular) would be to do some XML syntax checking and throw 
an error if a foreign table was used before it was defined.  This 
would be easier because the XML format is the same for all DBs.  
Unfortunately, it would also break any schema's where tables had 
cross keys.  I also think there might be issues with the new 
multiple schema file support here.

Anyway, at a minimum, an overall strategy needs to be opted for.  
Then a general Scarab issue for this needs to be defined.  If it 
requires DB specific changes, then this issue could be used to 
track which DB's have been "fixed" until it can be totally closed.

Hmm, is this really a ddlutils project issue and it could be pawned
off on that?  8)

> -----Original Message-----
> From: Thoralf Rickert
> 
> 
> I don't think that there is really a problem with torque and 
> mysql. Normally everything is fine, but at that time 
> something like this, doesn't worked:
> 
>  <table name="tableA">
>     
>   <foreign-key foreignTable="tableB">
>     <reference local="tableB_id" foreign="id"/>
>   </foreign-key>
>  </table>
> 
>  <table name="tableB">
>   <column name="id"        required="true" type="BIGINT" 
> primaryKey="true" autoIncrement="true"/>
>   <column name="tableA_id"                 type="BIGINT"/>
> 
>   <index name="tableA_index">
>    <index-column name="tableA_id"/>
>   </index>
>     
>   <foreign-key foreignTable="tableA">
>     <reference local="tableA_id" foreign="id"/>
>   </foreign-key>
>  </table>
> 
> MySQL will quit this with an error, because it doesn't know 
> tableB during the "CREATE TABLE tableA". So it's not a bug. 
> But it could be a feature :-).
> 
> I looked into the current subversion template and I think 
> that the example above doesn't work with the current version 
> (including 3.2). But my locally changed version is able to 
> manage this. I think, it's better to upgrade my local version 
> to torque 3.2 and make then a patch.
> 
> bye
> Thoralf
> 
> 
> 
> > -----Urspr√ľngliche Nachricht-----
> > Von: Thomas Fischer [mailto:fischer@seitenbau.net]
> > Gesendet: Mittwoch, 16. November 2005 10:20
> > An: Apache Torque Users List
> > Betreff: AW: SQL generated schema
> > 
> > 
> > Thanks for the offer. I am not aware of problems with mysql
> > at the moment, but I do not know the reason for not having 
> > any problems, cause the templates were not changed since 
> > then. I will look into it and maybe we'll come back to your offer.
> > 
> >       Thomas
> > 
> > "Thoralf Rickert" <thoralf.rickert@cadooz.de> schrieb am 14.11.2005
> > 11:09:48:
> > 
> > >
> > > > Some template sets for databases do not allow forward
> > declarations
> > > > on foreign key. So you have to make sure that the
> > referenced table
> > > > is before the referencing table in the schema.xml
> > >
> > > Ah, maybe it's not part of this thread but I remember that
> > I had this
> > > cross reference problem with Torque 3.1 & MySQL (1-2 
> years ago). At
> > > that time I thought about a general way to avoid this 
> problem. The 
> > > only solution that I found, was to change the templates. I 
> > knew that
> > > it's possible to create first a simple table with the SQL CREATE
> > > statement and  after creating all tables the template 
> creates ALTER 
> > > TABLE statements to change the foreign keys settings...
> > >
> > > I made this changes to my Torque 3.1 templates and was able to to
> > > create tables with torque that have cross reference (now 
> my schema 
> > > isn't sorted by references but alphabetical by the table 
> > name ;-). I
> > > don't know, if it's possible to make this changes to all other
> > > database types that torque supports but maybe for some.
> > >
> > > I would send my changes somebody (or to the developer list) if it
> > > isn't already made in the torque 3.2 version (which I 
> didn't tested 
> > > yet).
> > >
> > > bye
> > > Thoralf
> > >
> > >
> > >
> > > 
> > 
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > > For additional commands, e-mail: torque-user-help@db.apache.org
> > >
> > 
> > 
> > 
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> > 
> > 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> 

Duke CE Privacy Statement
Please be advised that this e-mail and any files transmitted with it are confidential communication
or may otherwise be privileged or confidential and are intended solely for the individual
or entity to whom they are addressed.  If you are not the intended recipient you may not rely
on the contents of this email or any attachments, and we ask that you  please not read, copy
or retransmit this communication, but reply to the sender and destroy the email, its contents,
and all copies thereof immediately.  Any unauthorized dissemination, distribution or copying
of this communication is strictly prohibited.



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message