reverse engineering foreign keys

[Ron King]

Hi All,

I want to get MP to 'figure out' the foreign key relationships in a schema, like was done

for Liferay, like here: http://code.google.com/p/minuteproject/downloads/detail?name=Liferay-fk.sql&can=2&q=label%3Aliferay

I have columns that start with "id", but I'm not sure it is working because the generated OX java doesn't show any evidence

that it worked. What should I expect to see in the output if it works? I'm attaching the schema also.

 <foreign-key-convention

      type="autodetect-foreign-key-based-on-similarity-and-map"

      column-ending="" column-starting="id" />

[Florian Adler]

Hi,

Your schema is the concrete, I would say 'perfect' example were minuteproject can show its strength.
There are 34 tables, primary keys on each but no foreign key.

There are 2 ways to enrich the model and to add 'virtual' foreign key when there are not existing:

• Enrichment at the entity level
• Global enrichment via a convention

In your case it is worth doing it by convention otherwise its a bit long to configure.

It is also possible to use conventions because by 'reverse-analysis' your model, I figure out that it follows some conventions regarding PK and relationships considered as FK:

So yes, the article regarding Liferay makes sense, there is a need to adapt the conventions for your model.

From what I see very quickly, your FK names are

• 'id'+foreign_table_name
• except for field where the fk is idField and not idFields
• your FK are of the same type as the PK

So I the convention you mentioned work

Here is a configuration that I used:

<!DOCTYPE root>

<generator-config>

    <configuration>

        <conventions>

            <target-convention type="enable-updatable-code-feature" />

        </conventions>       

        <model name="lcm" version="1.0" package-root="net.sf.mp.demo">

            <data-model>

                <driver name="mysql" version="5.1.16" groupId="mysql" artifactId="mysql-connector-java"></driver>

                <dataSource>

                    <driverClassName>org.gjt.mm.mysql.Driver</driverClassName>

                    <url>jdbc:mysql://127.0.0.1:3306/lcm</url>

                    <username>root</username>

                    <password>mysql</password>

                </dataSource>

                <primaryKeyPolicy oneGlobal="false" >

                    <primaryKeyPolicyPattern name="autoincrementPattern"></primaryKeyPolicyPattern>

                </primaryKeyPolicy>

            </data-model>

            <business-model>   

                <business-package default="business">

                </business-package>

                <enrichment>

                    <conventions>

                        <!-- manipulate the structure and entities BEFORE manipulating the entities -->

                        <foreign-key-convention type="autodetect-foreign-key-based-on-similarity-and-map"

                            column-ending="" column-starting="id" />

                        <column-naming-convention type="apply-strip-column-name-suffix" pattern-to-strip="ID" />

                        <reference-naming-convention type="apply-referenced-alias-when-no-ambiguity" is-to-plurialize="true" />       

                    </conventions>

                    <entity name="fields" alias="field"></entity>

                </enrichment>

            </business-model>

        </model>

        <targets >

            <target refname="JPA2"

               fileName="mp-template-config-JPA2.xml"

               outputdir-root="../../dev/MP-output/lcm/output/JPA2"

               templatedir-root="../../template/framework/jpa">

               <property name="add-querydsl" value="2.1.2"></property>

               <property name="add-jpa2-implementation" value="hibernate"></property>

            </target>                        

            <target refname="SQL misc"

               fileName="mp-template-config-sqlutils.xml"

               outputdir-root="../../dev/MP-output/lcm/output/SQL"

               templatedir-root="../../template/utils/db">

            </target>                        

            <target refname="LIB" fileName="mp-template-config-bsla-LIB-features.xml"

                templatedir-root="../../template/framework/bsla">

            </target>

        </targets>

    </configuration>

</generator-config>

Unfortunately the template described in mp-template-config-sqlutils.xml were not part of the distribution. There were experiental and I did them for Liferay communtity that wishes to see the ORM model.

I will add those in the next delivery.

Anyways, I made it run for you as a preview and here are the results:

/*

Copyright (c) minuteproject, minute...@gmail.com

All rights reserved.

 

Licensed under the Apache License, Version 2.0 (the "License")

you may not use this file except in compliance with the License.

You may obtain a copy of the License at

 

http://www.apache.org/licenses/LICENSE-2.0

 

Unless required by applicable law or agreed to in writing, software

distributed under the License is distributed on an "AS IS" BASIS,

WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

See the License for the specific language governing permissions and

limitations under the License.

 

More information on minuteproject:

twitter @minuteproject

wiki http://minuteproject.wikispaces.com 

blog http://minuteproject.blogspot.net

 

*/

/*

template reference : 

- name      : SQL.ResetAndCreateForeignKeys

- file name : SQL.ResetAndCreateForeignKeys.vm

- time      : 2012/08/26 ap. J.-C. at 13:00:24 CEST

*/

CREATE SCHEMA IF NOT EXISTS `lcm` DEFAULT CHARACTER SET latin1;

USE `lcm` ;

ALTER TABLE app ADD CONSTRAINT app_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE app ADD CONSTRAINT app_2_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE app_client_org ADD CONSTRAINT app_client_org_1_FK FOREIGN KEY (idapp) REFERENCES app(idapp);

ALTER TABLE app_client_org ADD CONSTRAINT app_client_org_2_FK FOREIGN KEY (idclient) REFERENCES client(idclient);

ALTER TABLE app_client_org ADD CONSTRAINT app_client_org_3_FK FOREIGN KEY (idorg) REFERENCES org(idorg);

ALTER TABLE app_fu ADD CONSTRAINT app_fu_1_FK FOREIGN KEY (idapp) REFERENCES app(idapp);

ALTER TABLE app_fu ADD CONSTRAINT app_fu_2_FK FOREIGN KEY (idfollowup) REFERENCES followup(idfollowup);

ALTER TABLE author_app ADD CONSTRAINT author_app_1_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE author_app ADD CONSTRAINT author_app_2_FK FOREIGN KEY (idapp) REFERENCES app(idapp);

ALTER TABLE client_attachment ADD CONSTRAINT client_attachment_1_FK FOREIGN KEY (idclient) REFERENCES client(idclient);

ALTER TABLE client_attachment ADD CONSTRAINT client_attachment_2_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE client_org ADD CONSTRAINT client_org_1_FK FOREIGN KEY (idclient) REFERENCES client(idclient);

ALTER TABLE client_org ADD CONSTRAINT client_org_2_FK FOREIGN KEY (idorg) REFERENCES org(idorg);

ALTER TABLE expense ADD CONSTRAINT expense_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE expense ADD CONSTRAINT expense_2_FK FOREIGN KEY (idfollowup) REFERENCES followup(idfollowup);

ALTER TABLE expense ADD CONSTRAINT expense_3_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE expense_comment ADD CONSTRAINT expense_comment_1_FK FOREIGN KEY (idexpense) REFERENCES expense(idexpense);

ALTER TABLE expense_comment ADD CONSTRAINT expense_comment_2_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE filter ADD CONSTRAINT filter_1_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE filter_conds ADD CONSTRAINT filter_conds_1_FK FOREIGN KEY (idfilter) REFERENCES filter(idfilter);

ALTER TABLE filter_conds ADD CONSTRAINT filter_conds_2_FK FOREIGN KEY (idfield) REFERENCES fields(idfield);

ALTER TABLE followup ADD CONSTRAINT followup_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE followup ADD CONSTRAINT followup_2_FK FOREIGN KEY (idstage) REFERENCES stage(identry);

ALTER TABLE followup ADD CONSTRAINT followup_3_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE keyword_client ADD CONSTRAINT keyword_client_1_FK FOREIGN KEY (idkeyword) REFERENCES keyword(idkeyword);

ALTER TABLE keyword_client ADD CONSTRAINT keyword_client_2_FK FOREIGN KEY (idclient) REFERENCES client(idclient);

ALTER TABLE keyword_followup ADD CONSTRAINT keyword_followup_1_FK FOREIGN KEY (idkeyword) REFERENCES keyword(idkeyword);

ALTER TABLE keyword_followup ADD CONSTRAINT keyword_followup_2_FK FOREIGN KEY (idfollowup) REFERENCES followup(idfollowup);

ALTER TABLE keyword_legal_case ADD CONSTRAINT keyword_legal_case_1_FK FOREIGN KEY (idkeyword) REFERENCES keyword(idkeyword);

ALTER TABLE keyword_legal_case ADD CONSTRAINT keyword_legal_case_2_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE keyword_legal_case ADD CONSTRAINT keyword_legal_case_3_FK FOREIGN KEY (idstage) REFERENCES stage(identry);

ALTER TABLE keyword_org ADD CONSTRAINT keyword_org_1_FK FOREIGN KEY (idkeyword) REFERENCES keyword(idkeyword);

ALTER TABLE keyword_org ADD CONSTRAINT keyword_org_2_FK FOREIGN KEY (idorg) REFERENCES org(idorg);

ALTER TABLE legal_case ADD CONSTRAINT legal_case_1_FK FOREIGN KEY (idstage) REFERENCES stage(identry);

ALTER TABLE legal_case_attachment ADD CONSTRAINT legal_case_attachment_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE legal_case_attachment ADD CONSTRAINT legal_case_attachment_2_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE legal_case_author ADD CONSTRAINT legal_case_author_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE legal_case_author ADD CONSTRAINT legal_case_author_2_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE legal_case_client_org ADD CONSTRAINT legal_case_client_org_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

ALTER TABLE legal_case_client_org ADD CONSTRAINT legal_case_client_org_2_FK FOREIGN KEY (idclient) REFERENCES client(idclient);

ALTER TABLE legal_case_client_org ADD CONSTRAINT legal_case_client_org_3_FK FOREIGN KEY (idorg) REFERENCES org(idorg);

ALTER TABLE org_attachment ADD CONSTRAINT org_attachment_1_FK FOREIGN KEY (idorg) REFERENCES org(idorg);

ALTER TABLE org_attachment ADD CONSTRAINT org_attachment_2_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE rep_col ADD CONSTRAINT rep_col_1_FK FOREIGN KEY (idreport) REFERENCES report(idreport);

ALTER TABLE rep_col ADD CONSTRAINT rep_col_2_FK FOREIGN KEY (idfield) REFERENCES fields(idfield);

ALTER TABLE rep_filter ADD CONSTRAINT rep_filter_1_FK FOREIGN KEY (idfilter) REFERENCES filter(idfilter);

ALTER TABLE rep_filter ADD CONSTRAINT rep_filter_2_FK FOREIGN KEY (idreport) REFERENCES report(idreport);

ALTER TABLE rep_filter ADD CONSTRAINT rep_filter_3_FK FOREIGN KEY (idfield) REFERENCES fields(idfield);

ALTER TABLE rep_filters ADD CONSTRAINT rep_filters_1_FK FOREIGN KEY (idreport) REFERENCES report(idreport);

ALTER TABLE rep_filters ADD CONSTRAINT rep_filters_2_FK FOREIGN KEY (idfilter) REFERENCES filter(idfilter);

ALTER TABLE rep_line ADD CONSTRAINT rep_line_1_FK FOREIGN KEY (idreport) REFERENCES report(idreport);

ALTER TABLE rep_line ADD CONSTRAINT rep_line_2_FK FOREIGN KEY (idfield) REFERENCES fields(idfield);

ALTER TABLE report ADD CONSTRAINT report_1_FK FOREIGN KEY (idauthor) REFERENCES author(idauthor);

ALTER TABLE stage ADD CONSTRAINT stage_1_FK FOREIGN KEY (idlegal_case) REFERENCES legal_case(idlegal_case);

commit;

where I added the 2 bold lines

CREATE SCHEMA IF NOT EXISTS `lcm` DEFAULT CHARACTER SET latin1;

USE `lcm` ;

I run them against mysql without errors.

Best regards,

Florian.

2012/8/24 Ron King <ronc...@gmail.com>

[Ron King]

Thanks Florian!