db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thoralf Rickert" <thoralf.rick...@cadooz.de>
Subject AW: SQL generated schema
Date Fri, 18 Nov 2005 09:11:15 GMT
Hi!

I think, the second approach is possible without much work. I did three things to use ALTER
TABLEs.

1. I've added a new for-loop in the sql/base/Control.vm at the end
   of the foreach datamodel loop. This loop goes through all tables
   and calls a new template called alter-table.vm in the desired
   database type.

   #foreach ($dataModel in $dataModels)
    [...]
    #foreach ($table in $database.tables)
     $generator.parse("$basepath/alter-table.vm",$outFile,"table",$tbl)
    #end
   #end

2. I've created the alter-table.vm in the mysql template directory
   (sql/base/mysql) and added the ALTER TABLE command.

   #foreach ($fk in $table.ForeignKeys)
    ALTER TABLE $table.Name add FOREIGN KEY ($fk.LocalColumnNames)
      REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)
      #if ($fk.hasOnUpdate()) ON UPDATE $fk.OnUpdate #end
      #if ($fk.hasOnDelete()) ON DELETE $fk.OnDelete #end;
   #end

3. I've deleted the content from the /sql/base/mysql/foreignkey.vm which
   is responsible for the foreign key creating in the CREATE TABLE
   command.

So, there is just one point to do in every DB Template. Create an empty alter-table.vm template.
That is done in 2 minutes. And if somebody has a patch for a specific database type, he/she
can change the alter-table.vm and delete the content from foreignkey.vm as I did it. That's
it.

bye
Thoralf



> -----Urspr√ľngliche Nachricht-----
> Von: Greg Monroe [mailto:Greg.Monroe@DukeCE.com] 
> Gesendet: Donnerstag, 17. November 2005 17:22
> An: Apache Torque Users List
> Betreff: RE: SQL generated schema
> 
> 
> 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