AW: SQL generated schema
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=idrequired=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
AW: SQL generated schema
Hallöle Thomas, 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 column name=idrequired=true type=BIGINT primaryKey=true autoIncrement=true/ column name=tableB_id required=true type=BIGINT/ index name=tableB_index index-column name=tableB_id/ /index foreign-key foreignTable=tableB reference local=tableB_id foreign=id/ /foreign-key /table table name=tableB column name=idrequired=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]
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]
AW: SQL generated schema
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]
