RE: Generating SQL including Foreign Key constraints (OpenJpa 1.2.2)

[Henno Vermeulen]

We also had the problem that no foreign keys were generated. We solved this by 
adding:

                        <property name="openjpa.jdbc.MappingDefaults"
                                value="ForeignKeyDeleteAction=restrict, 
JoinForeignKeyDeleteAction=restrict" />

to out persistence.xml. If I understand it well (the OpenJPA manual, is quite 
concise about this, see also 
http://en.wikipedia.org/wiki/Foreign_key#RESTRICT), this simply means that you 
should not be able to delete an entity when there is still a foreign key 
reference to it. The way to enforce this is by using foreign key constraints, 
so when you use this option, OpenJPA will generate them.

HTH,
Henno

-----Oorspronkelijk bericht-----
Van: baboune [mailto:sey...@yahoo.com] 
Verzonden: woensdag 23 februari 2011 10:01
Aan: users@openjpa.apache.org
Onderwerp: Generating SQL including Foreign Key constraints (OpenJpa 1.2.2)


Hi,

We recently did a switch from toplink to OpenJPA.  I am trying to generate
the sql files attached to the entities we have.  For context, we use
Glassfish, EJB3, JPA, and MySQL.

So, I have two entities:
@Entity
@Table(name="SMALLEMPLOYEE")
public class SmallEmployee implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    //@GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id = null;
    private String name = null;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "COMP_ID", nullable = false)
    //@ForeignKey
    private BigCompany company = null;

    @Version
    private int version;

...
}

And:
@Entity
@Table(name = "BIGCOMPANY")
public class BigCompany implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id = null;
    private String name = null;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "company", fetch =
FetchType.EAGER)
    private Set<SmallEmployee> employees = new HashSet<SmallEmployee>();

    @Version
    private int version;
...
}

Which is quite simple.

We use maven for building:
<plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>openjpa-maven-plugin</artifactId>
                <version>1.1</version>
                <configuration>
                    <includes>**/persist/*.class</includes>
                    <addDefaultConstructor>true</addDefaultConstructor>
                   
<enforcePropertyRestrictions>true</enforcePropertyRestrictions>
                    <sqlFile>./mytables.sql</sqlFile>                    
                </configuration>
                <executions>
                    <execution>
                        <id>enhancer</id>
                        <phase>process-classes</phase>
                        <goals>
                            <goal>enhance</goal>
                            <goal>sql</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

And the persistence.xml:
<!-- OpenJPA -->
    <persistence-unit name="BM" transaction-type="JTA">
       
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
        <jta-data-source>jdbc/bm</jta-data-source>
        <class>persist.BigCompany</class>
        <class>persist.SmallEmployee</class>
        <properties>
            <property name="openjpa.jdbc.DBDictionary"
value="mysql(DriverVendor=mysql)"/>            
        </properties>
    </persistence-unit>

Ok, now my question, how can I generate the SQL so that the foreign keys are
part of the generated sql?

If I run maven, what I get is this:
CREATE TABLE BIGCOMPANY (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb;
CREATE TABLE SMALLEMPLOYEE (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, COMP_ID BIGINT NOT NULL, PRIMARY KEY (id))
TYPE = innodb;
CREATE INDEX I_SMLLPLY_COMPANY ON SMALLEMPLOYEE (COMP_ID);

By default, I would expect that "CREATE INDEX I_SMLLPLY_COMPANY ON
SMALLEMPLOYEE (COMP_ID);" would be expressed as a foreign key constraint. 
The only way I manage to generate that is by modifying the MsallEMployee
class and add the @ForeignKey annotation on the BigCompany company field.

So if I do:
@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "COMP_ID", nullable = false)
    //@ForeignKey
    private BigCompany company = null;

Then I get:
CREATE TABLE BIGCOMPANY (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb;
CREATE TABLE SMALLEMPLOYEE (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, COMP_ID BIGINT NOT NULL, PRIMARY KEY (id))
TYPE = innodb;
ALTER TABLE SMALLEMPLOYEE ADD FOREIGN KEY (COMP_ID) REFERENCES BIGCOMPANY
(id);

That would be problem 1.  How to generate the foreign key constraint without
the openjpa annotation (which toplink did automatically)?

Problem 2, can I have my generated SQL with the constraint expressed in the
CREATE table statement and not as a separate ALTER statement?

CREATE TABLE SMALLEMPLOYEE (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, COMP_ID BIGINT NOT NULL, PRIMARY KEY (id),
CONSTRAINT smallemployee_ibfk_1 FOREIGN KEY (COMP_ID) REFERENCES
BIGCOMPANY(id)) ENGINE=InnoDB;

Instead of the extra line with an ALTER statement...

?

Thanks

-- 
View this message in context: 
http://openjpa.208410.n2.nabble.com/Generating-SQL-including-Foreign-Key-constraints-OpenJpa-1-2-2-tp6055639p6055639.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.