Relationships and MySQL domain reverse engineering

[niklassaers]

Hi guys,

I've been using Roo since 1.0.0 and decided for my new projects I want to use the database as the domain model and reverse engineer it for the project. So I've got all my schema written, call roo to generate the domain model, and.... hmm... no relationships. Hadn't thought about that, really, they're just always there. ;-)

Here's a data model. Even though MySQL doesn't really support foreign keys (and I guess that's why it's not mapped correctly), I've included them in the schema:

create table folder (
  id bigint not null auto_increment,
  title varchar(100) not null,
  primary key (id) 
) ENGINE=InnoDB;

create table language (
  id bigint not null auto_increment,
  name varchar(30),
  code varchar(3),
  primary key (id) 
) ENGINE=InnoDB;

create table document (
  id bigint not null auto_increment,
  folderId int not null,
  title varchar(100) not null,
  primary key (id),
  FOREIGN KEY (folderId) REFERENCES folder(id)
) ENGINE=InnoDB;

create table page (
  id bigint not null auto_increment,
  version int not null default 1,
  documentId int not null,
  languageId int not null default 1,
  fileName varchar(100),
  primary key (id),
  FOREIGN KEY (documentId) REFERENCES document(id),
  FOREIGN KEY (languageId) REFERENCES language(id)
) ENGINE=InnoDB;

When this is reverse engineered, I get mappings like

@Column(name = "languageId")
@NotNull
private Integer Page.languageid;

where I was hoping/expecting something like this:

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "languageId", nullable = false, referencedColumnName="id")
@ForeignKey(name="FK_LanguageID")
private Language Page.language;

Do I need to do the relationship mapping myself, or is there something I'm missing? It's been very good so far letting Roo take care of the relationships, so I'm hoping to keep using that with the reverse engineering as well.

Cheers

Nik

[niklassaers]

A little note here: I thought I was using InnoDB (really XtraDB, actually ;-) ) but it turned out that the engine was not there and it just used MyISAM instead. With InnoDB (or XtraDB) Roo finds the foreign keys just fine. :-)

So I guess my problem is solved, and the question boils down to: how should this be handled on MyISAM tables?

Cheers

Nik

[KEDD]

Good day

I'm new to spring roo and presently trying to use roo 1.1.0 and mysql 5.1.41

I am following the the guide which says to use the following commands:

project --topLevelPackage org.csdev.yesdb
persistence setup --provider HIBERNATE --database MYSQL --userName root --password ******
database reverse engineer --package ~.model --schema yesdb

I am not sure of the next steps.
I tried:
"Perform test"
"database instrospect schema yesdb" which returns "Schema yesdb does not exist or does not have any tables....."

Any guidance to get this working will be appreciated.

I have been searching the forums for a while, but most of the posts seem to be pre-built-in reverse engineering.

thanks

[niklassaers]

Hi, it'd probably be best to start a new thread instead of following up on an old one, but I'll do my best to help out anyway. :-)

So you've come to the step where you've successfully reverse engineered the database? Congratulations, you have your domain model set up. If you do a 'perform eclipse' (is that still needed?) and then load the project in for instance STS, you'll find your entity classes (they look empty, but the "magic" has already happened, the database abstractions are in the entity aspects. To prove this to yourself and make the function "@Transient private void getMyVariableSomeOtherWay() { return this.getMyVariable(); }", replacing of course MyVariable with an actual column name from your database, and it should compile fine, even give you auto-completion.

The next step, probably after a few iterations of database schema changes and re-reverse-engineering it, would be to make some business logic. In STS, make the ~.business package and put in what you feel like doing. To add some fun to it, add some integration tests that test that the services indeed do what you expect them to, all the way down to the database.

For my Roo projects, this is usually all I need as I use it to do lots of heavy lifting inhouse. But if you need a web layer, go ahead and add one. :-) I'll give you a hint, type "hint" :-)

On your question about schma, the --schema doesn't mean anything when it comes to MySQL.

Cheers

Nik

[KEDD]

Hey
Thanks for the quick response.

However I think you're a couple steps ahead of me.

I have not successfully reverse engineered from my MYSQL DB.

From searching further I think I have problems connecting to my database. I conclude this from the failure of "database instrospect schema yesdb"

Further no files are created from the database reverse engineer command so I assume that it has failed.

I can connect to the MYSQL database from the command line and can verify that the database exist with the needed tables

I will also attempt to place it in a new trend, but thanks in advance for the help

much thanks
kyle

[niklassaers]

When you did

"database reverse engineer --package ~.model --schema yesdb"

What did Roo say?

Cheers

Nik

[KEDD]

It gives "unable to get connection from driver"

I not sure if it counts when I enter

"persistence setup --provider HIBERNATE --database MYSQL --userName root --password ******"

I get

Please enter your database details in src/main/resources/META-INF/spring/database.properties.

However when I enter

database properties list

I see the correct information and when I navigate to the file mentioned the information is correctly loaded

[KEDD]

When you did

"database reverse engineer --package ~.model --schema yesdb"

What did Roo say?

Cheers

Nik

Correction
I get

Created ROOT\.roo-dbre
Managed SRC_MAIN_RESOURCES\META-INF\persistence.xml

in the .roo-dbre

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--WARNING: DO NOT EDIT THIS FILE. THIS FILE IS MANAGED BY SPRING ROO.-->
<database name="" schema="yesdb"/>

thanks

[niklassaers]

In "persistence setup" you're missing --databaseName and it wouldn't hurt to add --hostName to be a bit explicit either :-) Personally I prefer OPENJPA to HIBERNATE, but that's just taste I guess. You see that even better in your followup, database name is set to ""

[KEDD]

Great News:

That did the trick.
Roo successfully completed the reverse engineering

Much thanks