db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject RE: SQL generated schema
Date Thu, 17 Nov 2005 16:54:01 GMT




The first and third approach fails for circular references (a -> b -> a),
as Thoralf wrote.

Well, the alter table strategy is already implemented for oracle, and
Thoralf has implemented it for mysql, and we have a patch in scarab
implementing it for derby.

I would think this is the way to go. If someone needs it for other
databases, they can take the existing alter table stuff and apply that for
the new database.

Torque does not use ddlutils for that kind of stuff (nor for anything
else). Maybe we should consider this in the future, but then, the sql
templates are quite ripe in most cases. This would need careful
consideration what is gained and what is lost.

    Thomas


"Greg Monroe" <Greg.Monroe@DukeCE.com> schrieb am 17.11.2005 17:21:39:

> 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
>


---------------------------------------------------------------------
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