On 8/17/06 6:56 PM, [EMAIL PROTECTED] wrote:
> Is there a way for Rose to auto-initialize a schema that has a many-to-many
> relationship defined?
Yep. There are working examples in the test suite. It all depends on
conforming to the expected conventions (which are determined by the
ConventionManager, which you can subclass and customize).
The problem in your case is MySQL-specific, however. In order to
auto-initialize ...-to-many relationships, RDBO needs to be able to detect
foreign keys in the database. Although it looks like you're setting them up
when you do this:
> CREATE TABLE IF NOT EXISTS first_second_map (
> first_id INT NOT NULL REFERENCES firsts (id),
> second_id INT NOT NULL REFERENCES seconds (id),
> PRIMARY KEY (first_id, second_id)
> );
in reality, MySQL's default ISAM storage engine does not support foreign
keys at all, and the "REFERENCES ..." bits in your CREATE TABLE statements
are ignored.
You need to use the InnoDB storage engine in order to create "real" foreign
keys in MySQL, and even then there are very specific (and annoying) rule
surrounding the procedure. Basically, you have to put an index on each
column that references another column, and an index on each column that is
referenced, otherwise you get the oh-so-informative "errno: 150" error when
you try to create the table.
So, the first step to getting this to work with MySQL is to change your
table definitions to look like this:
CREATE TABLE firsts (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(50)
)
Type=InnoDB;
CREATE TABLE seconds (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
path varchar(255)
)
Type=InnoDB;
CREATE TABLE first_second_map (
first_id INT NOT NULL,
second_id INT NOT NULL,
PRIMARY KEY (first_id, second_id),
FOREIGN KEY (first_id) REFERENCES firsts (id),
FOREIGN KEY (second_id) REFERENCES seconds (id),
INDEX(first_id),
INDEX(second_id)
)
Type=InnoDB;
Yes, really :) The second step is to check that you really have InnoDB
installed and that MySQL actually made InnoDB tables like you asked it to.
If you don't have InnoDB installed, it'll just silently ignore the
"Type=InnoDB" directives. Good old MySQL. Anyway, to find out, do this:
mysql> show table status from test like 'firsts';
You should see a "Type" value of "InnoDB" in the resulting row.
Finally, you'll need to ensure that your map class finds the right table.
Since the default class-to-table mapping convention is to pluralize and
underscore-separate the class name to get the table name, the class
FirstSecondMap will look for a table named "first_second_maps", which
doesn't exist:
> throws an error
>
> DBD::mysql::db column_info failed: Table 'testdb.first_second_maps'
> doesn't exist at Rose/DB/Object/Metadata/Auto.pm line 85. Could not
> auto-generate columns for class FirstSecondMap
and there you go. So you have to either rename the mapping table and the
class to something that will convert correctly (e.g. FirstSecond and
first_seconds) or simply tell the map class what its table name is:
package FirstSecondMap;
use base 'Object';
__PACKAGE__->meta->table('first_second_map');
__PACKAGE__->meta->auto_initialize;
Finally, run the completed code. It should find all the tables and hook
everything up, creating classes like the ones shown below.
-John
---
package First;
use base qw(Object);
__PACKAGE__->meta->setup(
table => 'firsts',
columns => [
id => { type => 'integer', not_null => 1 },
name => { type => 'varchar', length => 50 },
],
primary_key_columns => [ 'id' ],
relationships => [
seconds => {
column_map => { first_id => 'id' },
foreign_class => 'Second',
map_class => 'FirstSecond',
map_from => 'first',
map_to => 'second',
type => 'many to many',
},
],
);
package Second;
use base qw(Object);
__PACKAGE__->meta->setup(
table => 'seconds',
columns => [
id => { type => 'integer', not_null => 1 },
path => { type => 'varchar', length => 255 },
],
primary_key_columns => [ 'id' ],
relationships => [
firsts => {
column_map => { second_id => 'id' },
foreign_class => 'First',
map_class => 'FirstSecond',
map_from => 'second',
map_to => 'first',
type => 'many to many',
},
],
);
package FirstSecond;
use base qw(Object);
__PACKAGE__->meta->setup(
table => 'first_seconds',
columns => [
first_id => { type => 'integer', not_null => 1 },
second_id => { type => 'integer', not_null => 1 },
],
primary_key_columns => [ 'first_id', 'second_id' ],
foreign_keys => [
first => {
class => 'First',
key_columns => { first_id => 'id' },
},
second => {
class => 'Second',
key_columns => { second_id => 'id' },
},
],
);
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
