db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ashley Hayes <ashley.ha...@macalla.com>
Subject RE: Torque does not support InnoDB tables yet?
Date Thu, 11 Sep 2003 10:56:04 GMT
Hi,
I'm trying to use foreign keys with MySQL for referential integrity and
cascading functionally. 
After trying the below suggestions to set the table type as InnoDB ( on
torque-gen-3.1-alpha2 ),
I got the MySQL error "Can't create table '.\ppi\ppi_device.frm' (errno:
150)" , this is because
I need to explicitly create index for the foreign key columns on this child
table its trying create.

Since My app needs to be DB independent, I do not want to specify indices on
foreign key columns that are implicitly
created by other DBMS.

So I have modified the index.vm template to do the following:
if the db.props has a property "genForeignKeyIndex=true"
the template will create indices for all the foreign keys ( and try's not to
duplicate an already specified index).


Please excuse the messy code, I find velocity indentation is hard to deal
with, especially when the template must generate nothing if there are no
indices/foreign keys...


modified sql\base\mysql\index.vm
---------------------------------------
#foreach ($index in $table.Indices)
    INDEX $index.Name ($index.ColumnList),
#end
#if($dbprops.get("genForeignKeyIndex") == "true" )#foreach ($fk in
$table.ForeignKeys)
 ## check if an index is already explicitly defined on foreign key columns
 #set ( $indexExists = "false")
 #foreach ($index in $table.Indices) #if( $index.ColumnList ==
$fk.LocalColumnNames ) #set ( $indexExists = "true") #end#end
 ## No Index exists for the foreign key (with column names in same order),
so create an index
 #if($indexExists == "false")

    INDEX ($fk.LocalColumnNames),
#end
#end#end
---------------------------------------

How would I go about getting this included in future versions of torque...
regards, 


How 




-----Original Message-----
From: Luigi [mailto:nadero@iol.it]
Sent: 13 August 2003 13:16
To: Apache Torque Users List
Subject: Re: Torque does not support InnoDB tables yet?


Henning P. Schmiedehausen wrote:
> Thomas Schindl <tom.schindl@profile.co.at> writes:
> 
> Add a new "mysql-inno" or "mysql-4" database type to the
> generator. Should be pretty simple, basically you copy the mysql
> templates and change a few names and parameters.
> 
> Patches welcome. ;-)
> 
> 	Regards
> 		Henning
> 

Hallo,

The solution I am using is the following one (it works, apparently, but 
I am no torque guru):

I have modified the templates/sql/base/mysql/foreignkey.vm file from:

-----------
#foreach ($fk in $table.ForeignKeys)
     FOREIGN KEY ($fk.LocalColumnNames) REFERENCES $fk.ForeignTableName 
($fk.ForeignColumnNames),
#end
-----------

to:

-----------
#foreach ($fk in $table.ForeignKeys)
     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
-----------

to allow for the generation of foreign keys costraints
This should be compatible with both innodb and myisam tables and with 
4.0.x and 3.23.5x versions of mysql (the unsupported options are simply 
ignored by the db versions which don't support them).

Then to use Innodb tables, the line regarding the innodb table type must 
be uncommented (and myisam commented) in the file: 
templates/sql/base/mysql/db.props

Regards
Luigi


> 
> 	
> 
> 
>>On Wed, 2003-08-06 at 16:04, Jeroen Peschier wrote:
>>
>>>Hi all,
>>>
>>>My Torque project uses a MySQL 4.1 database. I have many onUpdate and 
>>>onDelete restrictions in the project's schema.xml. When the SQL code 
>>>gets generated it outputs the foreign keys fine in the SQL, but no 
>>>ONUPDATE or ONDELETE constraints to be found... Is this a known issue?
>>>
>>>There's also no table type in the CREATE TABLE statements. I think MySQL 
>>>defaults to using MyISAM while I want InnoDB. How do I specify a table 
>>>type? Come to think of it, why even specify a table type if foreign keys 
>>>plus constraints are defined and the database is MySQL. InnoDB should be 
>>>implied, no?
> 
> 
>>InnoDB => You have to "comment in" that in db.props-file of mysql.
> 
> 
>>>Another minor thing I cannot specify in the schema is the character set 
>>>and collation to be used on a table.
>>>
>>>As is it now I have a post-create SQL-script that does a bunch of ALTER 
>>>TABLE statement and sets the required character set and collation. I 
>>>really don't want to add the referential constraints  manually. It kinda 
>>>defeats the purpose of having it generated :)
>>>
>>>Open for suggestions...
>>>
>>>Jeroen
>>>
>>>
>>>
>>>---------------------------------------------------------------------
>>>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

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