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:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> > > 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> > > > For additional commands, e-mail: [EMAIL PROTECTED]
> > > >
> > >
> > >
> > >
> >
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> > >
> > >
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
> 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: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
