Allow MappingTool and persistence.xml to support drop-create for database schema

• Key: OPENJPA-94
• Type: New Feature
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Unassigned
• Reporter: Shay Banon
• Fix Version/s: 0.9.7

Description

Currently, in the persistence context, one can define:

<property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />

Which causes OpenJPA to build the database schema based on the mapping defined. Currently, there is no way to define it to drop tables if they exists before creating the database schema. This is very useful for tests that drop (if exists) and creates new tables for each test.

Comments

[Abe White]

Note that the "SynchronizeMappings" property allows you to use all the arguments of the mappingtool. So you can try something like:

buildSchema(SchemaAction=refresh, DropTables=true)

Theoretically, that will drop unused columns and tables while adding any new columns and tables needed for your mappings.

[Patrick Linskey]

One downside of dropping and re-creating schemas is the latency of the operation. I think that it'd be more useful to have an option to automatically delete all records from all mapped tables. This option could potentially also work lazily if a full class list is not available up-front -- OpenJPA could issue a delete statement when a new ClassMapping is first initialized.

[Patrick Linskey]

I implemented the delete-all-records approach with svn r492024.

[Patrick Linskey]

I added an optimization for MySQL with r492032, but disabled it by default since http://dev.mysql.com/doc/refman/5.0/en/delete.html mentions that it may fail if using InnoDB and delete constraints.

With openjpa-0.9.6-incubating, it doesn't drop tabel when I used
        <property name="openjpa.jdbc.SynchronizeMappings"
                         value="buildSchema"/>
in persistence.xml. Hence, the test data in the pervious run
are shown in the later run.

When I changed the property to
        <property name="openjpa.jdbc.SynchronizeMappings"
                      value="buildSchema(SchemaAction=refresh,DropTables=true)"/>
the test failed with error,
Exception in thread "main" <0|false|0.9.6-incubating> org.apache.openjpa.persist
ence.PersistenceException: There was an error while setting up the configuration
 plugin option "SynchronizeMappings". The plugin was of type "org.apache.openjpa
.jdbc.meta.MappingTool". Setter methods for the following plugin properties were
 not available in that type: [DropTables]. Possible plugin properties are: [ACTI
ONS, ACTION_ADD, ACTION_BUILD_SCHEMA, ACTION_DROP, ACTION_EXPORT, ACTION_IMPORT,
 ACTION_REFRESH, ACTION_VALIDATE, DropUnusedComponents, ForeignKeys, IgnoreError
s, Indexes, MODE_MAPPING, MODE_MAPPING_INIT, MODE_META, MODE_NONE, MODE_QUERY, M
appingWriter, MetaDataFile, PrimaryKeys, ReadSchema, Repository, SCHEMA_ACTION_N
ONE, SchemaAction, SchemaGroup, SchemaTool, SchemaWriter, Sequences].
Ensure that your plugin configuration string uses key values that correspond to
setter methods in the plugin class.
        at org.apache.openjpa.kernel.AbstractBrokerFactory.newBroker(AbstractBro
kerFactory.java:183)

[Sherry Shen]

Test case with README and test results.

[Patrick Linskey]

I think that the syntax you should use is the following:

        <property name="openjpa.jdbc.SynchronizeMappings"
                      value="buildSchema(SchemaAction=\"add,delete-table-contents\")"/>

Also, I don't think that that is available in 0.9.6; can you try out that syntax with a 0.9.7 snapshot? I committed the change in the beginning of January, so anything more recent than that should do.

[Sherry Shen]

With openjpa-0.9.7-incubating-SNAPSHOT on 2/26/2007 nightly, I have tried followings:
A. <property name="openjpa.jdbc.SynchronizeMappings"
              value="buildSchema(SchemaAction=add,SchemaAction=deleteTableContents)"/>
My test works without previous test data in rerun.
This ensure that I have a clean test data in each run.

B. <property name="openjpa.jdbc.SynchronizeMappings"
               value="buildSchema(SchemaAction=drop,SchemaAction=add)"/>
My test runs, but with previous test data in rerun.
What action drop does?

[Patrick Linskey]

What happens if you do:

        <property name="openjpa.jdbc.SynchronizeMappings"
                      value="buildSchema(SchemaAction=\"add,delete-table-contents\")"/>

[Sherry Shen]

 When I use
           <property name="openjpa.jdbc.SynchronizeMappings"
                      value="buildSchema(SchemaAction=\"add,delete-table-contents\")"/>
my test failed to run with error,

Caused by: java.util.MissingResourceException: org.apache.openjpa.persistence.Pe
rsistenceProductDerivation:java.io.IOException: org.xml.sax.SAXException: file:/
data/tests/ejb/issue_2356c2/META-INF/persistence.xml [Location: Line: 29, C: 57]
: org.xml.sax.SAXParseException: Element type "property" must be followed by eit
her attribute specifications, ">" or "/>".
        at org.apache.openjpa.lib.conf.ProductDerivations.reportErrors(ProductDe
rivations.java:323)

[Patrick Linskey]

Hmm.... sounds like maybe you need to do more backslash-escaping?

           <property name="openjpa.jdbc.SynchronizeMappings"
                      value="buildSchema(SchemaAction=\\\"add,delete-table-contents\\\")"/>

[Abe White]

Single quotes should work -- no need to use escaped double quotes.

[Sherry Shen]

        <property name="openjpa.jdbc.SynchronizeMappings"
              value="buildSchema(SchemaAction='add,deleteTableContents')"/>
This one works.
However, it doesn't work if deleteTableContents is replaced with delete-table-contents.
I observed the error,
  Exception in thread "main" <0|false|0.9.7-incubating-SNAPSHOT>
  org.apache.openjpa.persistence.PersistenceException:
        action == delete-table-contents
        at org.apache.openjpa.kernel.AbstractBrokerFactory.
            newBroker(AbstractBrokerFactory.java:190)
        at org.apache.openjpa.kernel.DelegatingBrokerFactory.
            newBroker(DelegatingBrokerFactory.java:139)
        at org.apache.openjpa.persistence.EntityManagerFactoryImpl.
             createEntityManager(EntityManagerFactoryImpl.java:187)
        at org.apache.openjpa.persistence.EntityManagerFactoryImpl.
             createEntityManager(EntityManagerFactoryImpl.java:140)
        at org.apache.openjpa.persistence.EntityManagerFactoryImpl.
            createEntityManager(EntityManagerFactoryImpl.java:52)
        at TestClient.main(TestClient.java:13)
      Caused by: java.lang.IllegalArgumentException: action == delete-table-contents

I also tried the other suggestion.
        <property name="openjpa.jdbc.SynchronizeMappings"
                      value="buildSchema(SchemaAction=\\\"add,delete-table-contents\\\")"/>
The test with \\\" has SAXParseException similar to the test with \".

You may use my test in the attachemnt of this issue to try which one works.

It is better to document the usage with examples.

[Patrick Linskey]

> However, it doesn't work if deleteTableContents is replaced with delete-table-contents.

Oops, sorry about that.

> It is better to document the usage with examples.

Turns out it's in the 0.9.7 docs; we just haven't done a release since it was implemented.

[Patrick Linskey]

> However, it doesn't work if deleteTableContents is replaced with delete-table-contents.

Oops, sorry about that.

> It is better to document the usage with examples.

It turns out that this is covered in the 0.9.7 docs; we just haven't done a release since the feature was implemented.