OpenJPA
  1. OpenJPA
  2. OPENJPA-94

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

    Details

    • Type: New Feature New Feature
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.9.7
    • Component/s: None
    • Labels:
      None

      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.

      1. openjpa_94.zip
        7 kB
        Sherry Shen

        Activity

        Hide
        Patrick Linskey added a comment -

        > 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.

        Show
        Patrick Linskey added a comment - > 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.
        Hide
        Patrick Linskey added a comment -

        > 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.

        Show
        Patrick Linskey added a comment - > 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.
        Hide
        Sherry Shen added a comment -

        <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.

        Show
        Sherry Shen added a comment - <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.
        Hide
        Abe White added a comment -

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

        Show
        Abe White added a comment - Single quotes should work – no need to use escaped double quotes.
        Hide
        Patrick Linskey added a comment -

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

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

        Show
        Patrick Linskey added a comment - Hmm.... sounds like maybe you need to do more backslash-escaping? <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction=\\\"add,delete-table-contents\\\")"/>
        Hide
        Sherry Shen added a comment -

        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)

        Show
        Sherry Shen added a comment - 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)
        Hide
        Patrick Linskey added a comment -

        What happens if you do:

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

        Show
        Patrick Linskey added a comment - What happens if you do: <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction=\"add,delete-table-contents\")"/>
        Hide
        Sherry Shen added a comment -

        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?

        Show
        Sherry Shen added a comment - 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?
        Hide
        Patrick Linskey added a comment -

        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.

        Show
        Patrick Linskey added a comment - 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.
        Hide
        Sherry Shen added a comment -

        Test case with README and test results.

        Show
        Sherry Shen added a comment - Test case with README and test results.
        Hide
        Sherry Shen added a comment -

        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)

        Show
        Sherry Shen added a comment - 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)
        Hide
        Patrick Linskey added a comment -

        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.

        Show
        Patrick Linskey added a comment - 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.
        Hide
        Patrick Linskey added a comment -

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

        Show
        Patrick Linskey added a comment - I implemented the delete-all-records approach with svn r492024.
        Hide
        Patrick Linskey added a comment -

        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.

        Show
        Patrick Linskey added a comment - 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.
        Hide
        Abe White added a comment -

        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.

        Show
        Abe White added a comment - 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.

          People

          • Assignee:
            Unassigned
            Reporter:
            Shay Banon
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development