You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Shay Banon <ki...@gmail.com> on 2007/01/02 21:34:45 UTC
Perform automatic drop and create db schema
I am trying to figure out how to configure OpenJPA to perform drop and then
create the db schema. I got as far as:
<property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />
Which causes the schema to be created, but does not drop it when the EMF
closes (or when a new EMF starts).
The main reason I want it for is for simple tests, where each tests works
against a fresh copy of the database.
I tried doing things like: buildSchema(SchemaTool=drop) and things in that
nature, but have not managed to find the correct configuration (don't have
much time to dive into the OpenJPA code, sorry for that).
Cheers,
Shay
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8130220
Sent from the open-jpa-dev mailing list archive at Nabble.com.
RE: Perform automatic drop and create db schema
Posted by Shay Banon <ki...@gmail.com>.
I think so as well :)
Patrick Linskey wrote:
>
> I think that Abe and Shay are talking about slightly different features.
>
> -Patrick
>
> --
> Patrick Linskey
> BEA Systems, Inc.
>
> _______________________________________________________________________
> Notice: This email message, together with any attachments, may contain
> information of BEA Systems, Inc., its subsidiaries and affiliated
> entities, that may be confidential, proprietary, copyrighted and/or
> legally privileged, and is intended solely for the use of the individual
> or entity named in this message. If you are not the intended recipient,
> and have received this message in error, please immediately return this
> by email and then delete it.
>
>> -----Original Message-----
>> From: Abe White
>> Sent: Tuesday, January 02, 2007 2:04 PM
>> To: open-jpa-dev@incubator.apache.org
>> Subject: Re: Perform automatic drop and create db schema
>>
>> > Just using refresh does not clean up the data in the database
>> > (getting Unique
>> > constraints violations). Just for kicks I tried
>> > SchemaTool.DropTables=true,
>> > it did pass the configuration phase, but it still did not
>> cleaned the
>> > data/schema.
>>
>> None of the options I mentioned are meant to clean up the
>> data. Just
>> to drop unused schema components.
>> ______________________________________________________________
>> _________
>> Notice: This email message, together with any attachments,
>> may contain
>> information of BEA Systems, Inc., its subsidiaries and
>> affiliated
>> entities, that may be confidential, proprietary,
>> copyrighted and/or
>> legally privileged, and is intended solely for the use of the
>> individual
>> or entity named in this message. If you are not the intended
>> recipient,
>> and have received this message in error, please immediately
>> return this
>> by email and then delete it.
>>
>
>
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8132032
Sent from the open-jpa-dev mailing list archive at Nabble.com.
RE: Perform automatic drop and create db schema
Posted by Patrick Linskey <pl...@bea.com>.
I think that Abe and Shay are talking about slightly different features.
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: Abe White
> Sent: Tuesday, January 02, 2007 2:04 PM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: Perform automatic drop and create db schema
>
> > Just using refresh does not clean up the data in the database
> > (getting Unique
> > constraints violations). Just for kicks I tried
> > SchemaTool.DropTables=true,
> > it did pass the configuration phase, but it still did not
> cleaned the
> > data/schema.
>
> None of the options I mentioned are meant to clean up the
> data. Just
> to drop unused schema components.
> ______________________________________________________________
> _________
> Notice: This email message, together with any attachments,
> may contain
> information of BEA Systems, Inc., its subsidiaries and
> affiliated
> entities, that may be confidential, proprietary,
> copyrighted and/or
> legally privileged, and is intended solely for the use of the
> individual
> or entity named in this message. If you are not the intended
> recipient,
> and have received this message in error, please immediately
> return this
> by email and then delete it.
>
Re: Perform automatic drop and create db schema
Posted by Abe White <aw...@bea.com>.
> Just using refresh does not clean up the data in the database
> (getting Unique
> constraints violations). Just for kicks I tried
> SchemaTool.DropTables=true,
> it did pass the configuration phase, but it still did not cleaned the
> data/schema.
None of the options I mentioned are meant to clean up the data. Just
to drop unused schema components.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
Re: Perform automatic drop and create db schema
Posted by Shay Banon <ki...@gmail.com>.
Just using refresh does not clean up the data in the database (getting Unique
constraints violations). Just for kicks I tried SchemaTool.DropTables=true,
it did pass the configuration phase, but it still did not cleaned the
data/schema.
Abe White wrote:
>
>> Caused by: org.apache.openjpa.lib.util.ParseException: 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].
>
> Try it without the DropTables=true. It won't drop unused tables, but
> it should still drop unused columns. If that works, it should be a
> pretty minor fix to get DropTables working. (It might work already
> if you change it to "SchemaTool.DropTables=true", but it won't be
> deterministic so I'd rather leave it aside for now.)
>
> _______________________________________________________________________
> Notice: This email message, together with any attachments, may contain
> information of BEA Systems, Inc., its subsidiaries and affiliated
> entities, that may be confidential, proprietary, copyrighted and/or
> legally privileged, and is intended solely for the use of the individual
> or entity named in this message. If you are not the intended recipient,
> and have received this message in error, please immediately return this
> by email and then delete it.
>
>
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8131627
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: Perform automatic drop and create db schema
Posted by Abe White <aw...@bea.com>.
> Caused by: org.apache.openjpa.lib.util.ParseException: 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].
Try it without the DropTables=true. It won't drop unused tables, but
it should still drop unused columns. If that works, it should be a
pretty minor fix to get DropTables working. (It might work already
if you change it to "SchemaTool.DropTables=true", but it won't be
deterministic so I'd rather leave it aside for now.)
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
Re: Perform automatic drop and create db schema
Posted by Shay Banon <ki...@gmail.com>.
It is one of the first things I tried, I got this exception:
Caused by: org.apache.openjpa.lib.util.ParseException: 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: [ACTIONS, ACTION_ADD,
ACTION_BUILD_SCHEMA, ACTION_DROP, ACTION_EXPORT, ACTION_IMPORT,
ACTION_REFRESH, ACTION_VALIDATE, DropUnusedComponents, ForeignKeys,
IgnoreErrors, Indexes, MODE_MAPPING, MODE_MAPPING_INIT, MODE_META,
MODE_NONE, MODE_QUERY, MappingWriter, MetaDataFile, PrimaryKeys, ReadSchema,
Repository, SCHEMA_ACTION_NONE, 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.lib.conf.Configurations.configureInstance(Configurations.java:352)
at
org.apache.openjpa.lib.conf.Configurations.configureInstance(Configurations.java:280)
at
org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:153)
at
org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.newBrokerImpl(JDBCBrokerFactory.java:127)
at
org.apache.openjpa.kernel.AbstractBrokerFactory.newBroker(AbstractBrokerFactory.java:164)
... 17 more
I also tried different combinations but to no avail...
Abe White wrote:
>
>> Unfortunately, we don't have any automatic drop-table feature, but
>> I agree it would be handy (you might want to make a JIRA report
>> with the suggestion).
>
> 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. If you try it,
> let us know how it works out.
> _______________________________________________________________________
> Notice: This email message, together with any attachments, may contain
> information of BEA Systems, Inc., its subsidiaries and affiliated
> entities, that may be confidential, proprietary, copyrighted and/or
> legally privileged, and is intended solely for the use of the individual
> or entity named in this message. If you are not the intended recipient,
> and have received this message in error, please immediately return this
> by email and then delete it.
>
>
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8131374
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: Perform automatic drop and create db schema
Posted by Abe White <aw...@bea.com>.
> Unfortunately, we don't have any automatic drop-table feature, but
> I agree it would be handy (you might want to make a JIRA report
> with the suggestion).
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. If you try it,
let us know how it works out.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
RE: Perform automatic drop and create db schema
Posted by Patrick Linskey <pl...@bea.com>.
It's in 0.9.7-snapshot; I don't remember the change number.
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: Dain Sundstrom [mailto:dain@iq80.com]
> Sent: Wednesday, January 31, 2007 12:22 PM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: Perform automatic drop and create db schema
>
> What version of OpenJPA will this be in and did dropTable get
> implemented?
>
> -dain
>
> On Jan 2, 2007, at 6:50 PM, Patrick Linskey wrote:
>
> >> -----Original Message-----
> >> From: Shay Banon [mailto:kimchy@gmail.com]
> >> Sent: Tuesday, January 02, 2007 2:33 PM
> >> To: open-jpa-dev@incubator.apache.org
> >> Subject: RE: Perform automatic drop and create db schema
> >>
> >>
> >> Automatically clean that data without dropping the tables
> >> makes even more
> >> sense. That would be a really cool feature.
> >
> > Deciding that two is a quorum, and needing something to do on my
> > flight
> > to Salt Lake City, I implemented a new SchemaTool action called
> > 'deleteTableContents' that does what you'd expect, more-or-less.
> >
> > Along the way, I made it possible to specify multiple SchemaTool
> > actions
> > via a comma-separated list.
> >
> > I've done some basic testing of it; more testing
> (especially around
> > the
> > scope of the classes that the operations happen on) would probably
> > be a
> > good thing.
> >
> > You can try it out like so:
> >
> > <property name="openjpa.jdbc.SynchronizeMappings"
> > value="buildSchema,deleteTableContents" />
> >
> > or:
> >
> > <property name="openjpa.jdbc.SynchronizeMappings"
> > value="refresh,deleteTableContents" />
> >
> > -Patrick
> >
> ______________________________________________________________
> ________
> > _
> > Notice: This email message, together with any attachments, may
> > contain
> > information of BEA Systems, Inc., its subsidiaries and
> > affiliated
> > entities, that may be confidential, proprietary, copyrighted
> > and/or
> > legally privileged, and is intended solely for the use of the
> > individual
> > or entity named in this message. If you are not the intended
> > recipient,
> > and have received this message in error, please immediately return
> > this
> > by email and then delete it.
> >
>
>
Re: Perform automatic drop and create db schema
Posted by Dain Sundstrom <da...@iq80.com>.
What version of OpenJPA will this be in and did dropTable get
implemented?
-dain
On Jan 2, 2007, at 6:50 PM, Patrick Linskey wrote:
>> -----Original Message-----
>> From: Shay Banon [mailto:kimchy@gmail.com]
>> Sent: Tuesday, January 02, 2007 2:33 PM
>> To: open-jpa-dev@incubator.apache.org
>> Subject: RE: Perform automatic drop and create db schema
>>
>>
>> Automatically clean that data without dropping the tables
>> makes even more
>> sense. That would be a really cool feature.
>
> Deciding that two is a quorum, and needing something to do on my
> flight
> to Salt Lake City, I implemented a new SchemaTool action called
> 'deleteTableContents' that does what you'd expect, more-or-less.
>
> Along the way, I made it possible to specify multiple SchemaTool
> actions
> via a comma-separated list.
>
> I've done some basic testing of it; more testing (especially around
> the
> scope of the classes that the operations happen on) would probably
> be a
> good thing.
>
> You can try it out like so:
>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema,deleteTableContents" />
>
> or:
>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="refresh,deleteTableContents" />
>
> -Patrick
> ______________________________________________________________________
> _
> Notice: This email message, together with any attachments, may
> contain
> information of BEA Systems, Inc., its subsidiaries and
> affiliated
> entities, that may be confidential, proprietary, copyrighted
> and/or
> legally privileged, and is intended solely for the use of the
> individual
> or entity named in this message. If you are not the intended
> recipient,
> and have received this message in error, please immediately return
> this
> by email and then delete it.
>
RE: Perform automatic drop and create db schema
Posted by Patrick Linskey <pl...@bea.com>.
> -----Original Message-----
> From: Shay Banon [mailto:kimchy@gmail.com]
> Sent: Tuesday, January 02, 2007 2:33 PM
> To: open-jpa-dev@incubator.apache.org
> Subject: RE: Perform automatic drop and create db schema
>
>
> Automatically clean that data without dropping the tables
> makes even more
> sense. That would be a really cool feature.
Deciding that two is a quorum, and needing something to do on my flight
to Salt Lake City, I implemented a new SchemaTool action called
'deleteTableContents' that does what you'd expect, more-or-less.
Along the way, I made it possible to specify multiple SchemaTool actions
via a comma-separated list.
I've done some basic testing of it; more testing (especially around the
scope of the classes that the operations happen on) would probably be a
good thing.
You can try it out like so:
<property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema,deleteTableContents" />
or:
<property name="openjpa.jdbc.SynchronizeMappings"
value="refresh,deleteTableContents" />
-Patrick
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
Re: Perform automatic drop and create db schema
Posted by Marc Prud'hommeaux <mp...@apache.org>.
The problem with that is that if there are restrictive foreign keys,
it can be a little more complicated than just running through each of
the tables and deleting each one (and unfortunately, SQL strangely
doesn't allow multiple tables in delete clauses, a la "delete from
table1, table2").
It's still probably faster than dropping the table and re-creating
it, though.
On Jan 2, 2007, at 5:33 PM, Shay Banon wrote:
>
> Automatically clean that data without dropping the tables makes
> even more
> sense. That would be a really cool feature.
>
>
> Patrick Linskey wrote:
>>
>> IMO, a more valuable option would be a way to delete all records
>> in all
>> mapped tables, rather than doing unnecessary schema interrogation.
>>
>> Additionally, note that with JPA, deleting all records during setUp
>> () is
>> as easy as 'em.createQuery("delete from Employee").executeUpdate();'
>>
>> -Patrick
>>
>> --
>> Patrick Linskey
>> BEA Systems, Inc.
>>
>> _____________________________________________________________________
>> __
>> Notice: This email message, together with any attachments, may
>> contain
>> information of BEA Systems, Inc., its subsidiaries and
>> affiliated
>> entities, that may be confidential, proprietary, copyrighted
>> and/or
>> legally privileged, and is intended solely for the use of the
>> individual
>> or entity named in this message. If you are not the intended
>> recipient,
>> and have received this message in error, please immediately return
>> this
>> by email and then delete it.
>>
>>> -----Original Message-----
>>> From: robert burrell donkin [mailto:robertburrelldonkin@gmail.com]
>>> Sent: Tuesday, January 02, 2007 1:39 PM
>>> To: open-jpa-dev@incubator.apache.org
>>> Subject: Re: Perform automatic drop and create db schema
>>>
>>> On 1/2/07, Craig L Russell <Cr...@sun.com> wrote:
>>>> For What It's Worth:
>>>>
>>>> +1 on the drop-tables feature for OpenJPA. But I would caution
>>>> against using it on each test.
>>>>
>>>> Sadly, my experience is that drop-create-tables is 99.9% of the
>>>> time
>>>> taken in a typical test.
>>>>
>>>> The JDO TCK runs hundreds of tests and we drop-create tables
>>>> only on
>>>> demand. The drop-create step takes several minutes compared to a
>>>> few
>>>> seconds to actually run the tests.
>>>
>>> yeh - dropping then recreating isn't very efficient but is effective
>>>
>>> i've found that it's hard to educate developers unfamiliar with
>>> automated testing. creating good integration tests is important but
>>> takes a long while. too often neglected due to time pressure. i
>>> suspect that tool developers could do more to help.
>>>
>>> for example, IMHO containers should ship with integrated code
>>> coverage
>>> tools. there are good enough open source ones but since they are not
>>> bundled with containers they are not used as widely as they
>>> should be
>>> in commercial development work.
>>>
>>>> After several years of doing this kind of work, I've concluded that
>>>> the best practical strategy (we tried beating up the
>>> database vendors
>>>> to make drop-create as fast as insert/delete rows, to no
>>> avail) is to
>>>> write your tests such that at the beginning of the test, you create
>>>> your test data and at the end of the test, you delete the test
>>>> data,
>>>> leaving the database in an empty state.
>>>
>>> +1
>>>
>>> but this is where a side door would be of most use. sophisticated
>>> object relational layers generally make it relatively slow and
>>> unnatural to just delete everything in a table. which is as it
>>> should
>>> be. it'd just be cool to able if the tool developers also created
>>> testing only side doors.
>>>
>>> i have an idea that this is all reasonably easily doable but isn't
>>> well known or packaged up into tools which are easy to learn.
>>>
>>> it would be very cool to be able to start a recording tool in
>>> setup to
>>> intercept and record every create, update, delete in the data access
>>> layer then in tearDown just ask the data access layer to undo
>>> everything that was done.
>>>
>>> it would also be very cool to have a complete dump and replace
>>> facility for black-box-in-container functional testing. in setup,
>>> just
>>> push a load of data as xml. the data access layer deletes data from
>>> all the tables specified and inserts the given data.
>>>
>>> easy, dynamic flushing of all object caches would also be useful.
>>>
>>> (sadly, i'm really interested in meta-data ATM, both email and
>>> source
>>> auditing so there's not much chance of hacking together something
>>> which demonstrates what i mean any time soon...)
>>>
>>> - robert
>>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Perform-
> automatic-drop-and-create-db-schema-tf2909915.html#a8132118
> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>
RE: Perform automatic drop and create db schema
Posted by Shay Banon <ki...@gmail.com>.
Automatically clean that data without dropping the tables makes even more
sense. That would be a really cool feature.
Patrick Linskey wrote:
>
> IMO, a more valuable option would be a way to delete all records in all
> mapped tables, rather than doing unnecessary schema interrogation.
>
> Additionally, note that with JPA, deleting all records during setUp() is
> as easy as 'em.createQuery("delete from Employee").executeUpdate();'
>
> -Patrick
>
> --
> Patrick Linskey
> BEA Systems, Inc.
>
> _______________________________________________________________________
> Notice: This email message, together with any attachments, may contain
> information of BEA Systems, Inc., its subsidiaries and affiliated
> entities, that may be confidential, proprietary, copyrighted and/or
> legally privileged, and is intended solely for the use of the individual
> or entity named in this message. If you are not the intended recipient,
> and have received this message in error, please immediately return this
> by email and then delete it.
>
>> -----Original Message-----
>> From: robert burrell donkin [mailto:robertburrelldonkin@gmail.com]
>> Sent: Tuesday, January 02, 2007 1:39 PM
>> To: open-jpa-dev@incubator.apache.org
>> Subject: Re: Perform automatic drop and create db schema
>>
>> On 1/2/07, Craig L Russell <Cr...@sun.com> wrote:
>> > For What It's Worth:
>> >
>> > +1 on the drop-tables feature for OpenJPA. But I would caution
>> > against using it on each test.
>> >
>> > Sadly, my experience is that drop-create-tables is 99.9% of the time
>> > taken in a typical test.
>> >
>> > The JDO TCK runs hundreds of tests and we drop-create tables only on
>> > demand. The drop-create step takes several minutes compared to a few
>> > seconds to actually run the tests.
>>
>> yeh - dropping then recreating isn't very efficient but is effective
>>
>> i've found that it's hard to educate developers unfamiliar with
>> automated testing. creating good integration tests is important but
>> takes a long while. too often neglected due to time pressure. i
>> suspect that tool developers could do more to help.
>>
>> for example, IMHO containers should ship with integrated code coverage
>> tools. there are good enough open source ones but since they are not
>> bundled with containers they are not used as widely as they should be
>> in commercial development work.
>>
>> > After several years of doing this kind of work, I've concluded that
>> > the best practical strategy (we tried beating up the
>> database vendors
>> > to make drop-create as fast as insert/delete rows, to no
>> avail) is to
>> > write your tests such that at the beginning of the test, you create
>> > your test data and at the end of the test, you delete the test data,
>> > leaving the database in an empty state.
>>
>> +1
>>
>> but this is where a side door would be of most use. sophisticated
>> object relational layers generally make it relatively slow and
>> unnatural to just delete everything in a table. which is as it should
>> be. it'd just be cool to able if the tool developers also created
>> testing only side doors.
>>
>> i have an idea that this is all reasonably easily doable but isn't
>> well known or packaged up into tools which are easy to learn.
>>
>> it would be very cool to be able to start a recording tool in setup to
>> intercept and record every create, update, delete in the data access
>> layer then in tearDown just ask the data access layer to undo
>> everything that was done.
>>
>> it would also be very cool to have a complete dump and replace
>> facility for black-box-in-container functional testing. in setup, just
>> push a load of data as xml. the data access layer deletes data from
>> all the tables specified and inserts the given data.
>>
>> easy, dynamic flushing of all object caches would also be useful.
>>
>> (sadly, i'm really interested in meta-data ATM, both email and source
>> auditing so there's not much chance of hacking together something
>> which demonstrates what i mean any time soon...)
>>
>> - robert
>>
>
>
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8132118
Sent from the open-jpa-dev mailing list archive at Nabble.com.
RE: Perform automatic drop and create db schema
Posted by Patrick Linskey <pl...@bea.com>.
IMO, a more valuable option would be a way to delete all records in all
mapped tables, rather than doing unnecessary schema interrogation.
Additionally, note that with JPA, deleting all records during setUp() is
as easy as 'em.createQuery("delete from Employee").executeUpdate();'
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: robert burrell donkin [mailto:robertburrelldonkin@gmail.com]
> Sent: Tuesday, January 02, 2007 1:39 PM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: Perform automatic drop and create db schema
>
> On 1/2/07, Craig L Russell <Cr...@sun.com> wrote:
> > For What It's Worth:
> >
> > +1 on the drop-tables feature for OpenJPA. But I would caution
> > against using it on each test.
> >
> > Sadly, my experience is that drop-create-tables is 99.9% of the time
> > taken in a typical test.
> >
> > The JDO TCK runs hundreds of tests and we drop-create tables only on
> > demand. The drop-create step takes several minutes compared to a few
> > seconds to actually run the tests.
>
> yeh - dropping then recreating isn't very efficient but is effective
>
> i've found that it's hard to educate developers unfamiliar with
> automated testing. creating good integration tests is important but
> takes a long while. too often neglected due to time pressure. i
> suspect that tool developers could do more to help.
>
> for example, IMHO containers should ship with integrated code coverage
> tools. there are good enough open source ones but since they are not
> bundled with containers they are not used as widely as they should be
> in commercial development work.
>
> > After several years of doing this kind of work, I've concluded that
> > the best practical strategy (we tried beating up the
> database vendors
> > to make drop-create as fast as insert/delete rows, to no
> avail) is to
> > write your tests such that at the beginning of the test, you create
> > your test data and at the end of the test, you delete the test data,
> > leaving the database in an empty state.
>
> +1
>
> but this is where a side door would be of most use. sophisticated
> object relational layers generally make it relatively slow and
> unnatural to just delete everything in a table. which is as it should
> be. it'd just be cool to able if the tool developers also created
> testing only side doors.
>
> i have an idea that this is all reasonably easily doable but isn't
> well known or packaged up into tools which are easy to learn.
>
> it would be very cool to be able to start a recording tool in setup to
> intercept and record every create, update, delete in the data access
> layer then in tearDown just ask the data access layer to undo
> everything that was done.
>
> it would also be very cool to have a complete dump and replace
> facility for black-box-in-container functional testing. in setup, just
> push a load of data as xml. the data access layer deletes data from
> all the tables specified and inserts the given data.
>
> easy, dynamic flushing of all object caches would also be useful.
>
> (sadly, i'm really interested in meta-data ATM, both email and source
> auditing so there's not much chance of hacking together something
> which demonstrates what i mean any time soon...)
>
> - robert
>
Re: Perform automatic drop and create db schema
Posted by robert burrell donkin <ro...@gmail.com>.
On 1/2/07, Craig L Russell <Cr...@sun.com> wrote:
> For What It's Worth:
>
> +1 on the drop-tables feature for OpenJPA. But I would caution
> against using it on each test.
>
> Sadly, my experience is that drop-create-tables is 99.9% of the time
> taken in a typical test.
>
> The JDO TCK runs hundreds of tests and we drop-create tables only on
> demand. The drop-create step takes several minutes compared to a few
> seconds to actually run the tests.
yeh - dropping then recreating isn't very efficient but is effective
i've found that it's hard to educate developers unfamiliar with
automated testing. creating good integration tests is important but
takes a long while. too often neglected due to time pressure. i
suspect that tool developers could do more to help.
for example, IMHO containers should ship with integrated code coverage
tools. there are good enough open source ones but since they are not
bundled with containers they are not used as widely as they should be
in commercial development work.
> After several years of doing this kind of work, I've concluded that
> the best practical strategy (we tried beating up the database vendors
> to make drop-create as fast as insert/delete rows, to no avail) is to
> write your tests such that at the beginning of the test, you create
> your test data and at the end of the test, you delete the test data,
> leaving the database in an empty state.
+1
but this is where a side door would be of most use. sophisticated
object relational layers generally make it relatively slow and
unnatural to just delete everything in a table. which is as it should
be. it'd just be cool to able if the tool developers also created
testing only side doors.
i have an idea that this is all reasonably easily doable but isn't
well known or packaged up into tools which are easy to learn.
it would be very cool to be able to start a recording tool in setup to
intercept and record every create, update, delete in the data access
layer then in tearDown just ask the data access layer to undo
everything that was done.
it would also be very cool to have a complete dump and replace
facility for black-box-in-container functional testing. in setup, just
push a load of data as xml. the data access layer deletes data from
all the tables specified and inserts the given data.
easy, dynamic flushing of all object caches would also be useful.
(sadly, i'm really interested in meta-data ATM, both email and source
auditing so there's not much chance of hacking together something
which demonstrates what i mean any time soon...)
- robert
Re: Perform automatic drop and create db schema
Posted by Shay Banon <ki...@gmail.com>.
The way I usually do things is by starting a transaction, and simply rolling
it back when my test finishes (if it wasn't committed/rolled back during the
test method). This usually supports 90% of an application integration tests
needs. In my case, I have simple tests, I am running them against an in
memory HSQL, and the costs are very small. I need this feature since I also
test the transaction integration, and it requires more complex scenario then
the test scenario I described in the beginning. I don't want to "corrupt" my
code with Jdbc code that could be avoided.
As developers of tools/libraries/frameworks, we need to give the developers
all the options, and educate them regarding the benefits/drawbacks of using
each one. I am glad for the response in this thread! I have been burned by
other libraries that are pretty nasty in their responses (but I won't name
names :) ).
robert burrell donkin-2 wrote:
>
> On 1/2/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
>>
>> Personally, I like to put both the data cleanup and data
>> initialization in the setUp() stage. It's sometimes a bit slower, but
>> if there is faulty cleanup logic in a previous test's tearDown(), it
>> may affect some random test down the road, and it can sometimes be
>> very difficult to track down.
>
> i found that data cleaning in both setUp() and tearDown(), is
> necessary (at least for standard commercial development)
>
> - robert
>
>
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8131565
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: Perform automatic drop and create db schema
Posted by robert burrell donkin <ro...@gmail.com>.
On 1/2/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
>
> Personally, I like to put both the data cleanup and data
> initialization in the setUp() stage. It's sometimes a bit slower, but
> if there is faulty cleanup logic in a previous test's tearDown(), it
> may affect some random test down the road, and it can sometimes be
> very difficult to track down.
i found that data cleaning in both setUp() and tearDown(), is
necessary (at least for standard commercial development)
- robert
Re: Perform automatic drop and create db schema
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Personally, I like to put both the data cleanup and data
initialization in the setUp() stage. It's sometimes a bit slower, but
if there is faulty cleanup logic in a previous test's tearDown(), it
may affect some random test down the road, and it can sometimes be
very difficult to track down.
TCKs that come out of Sun are especially notorious for this problem :)
On Jan 2, 2007, at 4:07 PM, Craig L Russell wrote:
> For What It's Worth:
>
> +1 on the drop-tables feature for OpenJPA. But I would caution
> against using it on each test.
>
> Sadly, my experience is that drop-create-tables is 99.9% of the
> time taken in a typical test.
>
> The JDO TCK runs hundreds of tests and we drop-create tables only
> on demand. The drop-create step takes several minutes compared to a
> few seconds to actually run the tests.
>
> After several years of doing this kind of work, I've concluded that
> the best practical strategy (we tried beating up the database
> vendors to make drop-create as fast as insert/delete rows, to no
> avail) is to write your tests such that at the beginning of the
> test, you create your test data and at the end of the test, you
> delete the test data, leaving the database in an empty state.
>
> JUnit facilitates this by providing a setUp and tearDown. We create
> the test data in setUp and delete it in tearDown. Of course, the
> tearDown might fail, leaving the data in an unpredictable state,
> but it does work 99.9% of the time. That's why we have a common
> tearDown that is very carefully implemented to catch exceptions,
> retry, etc.
>
> Craig
>
> On Jan 2, 2007, at 12:52 PM, Marc Prud'hommeaux wrote:
>
>> Robert-
>>
>> I completely agree. We usually just build all the tables once and
>> then just try to make sure all the objects are deleted at the end
>> of the test, but you are correct that this is sometimes more
>> cumbersome than it could be. An easy drop-then-create option would
>> simplify this, although some databases can be notoriously slow
>> with schema interrogation and manipulation that doing it for each
>> test might wind up being prohibitively slow.
>>
>>
>>
>> On Jan 2, 2007, at 3:44 PM, robert burrell donkin wrote:
>>
>>> On 1/2/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
>>>> Shay-
>>>>
>>>> Unfortunately, we don't have any automatic drop-table feature,
>>>> but I
>>>> agree it would be handy (you might want to make a JIRA report with
>>>> the suggestion).
>>>>
>>>> The only other recourse, I think, would be to just manually delete
>>>> the database files before running your tests.
>>>
>>> support for easy integration testing is one area where i think many
>>> JDO implementations could really improve
>>>
>>> it's vital to start with a known database state and clean up after
>>> each integration test. this isn't as easy as it should be when you
>>> have a complex object-relational mapper with extensive caching. a
>>> set
>>> of side doors for integration testing would really help
>>> productivity.
>>>
>>> - robert
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
Re: Perform automatic drop and create db schema
Posted by Craig L Russell <Cr...@Sun.COM>.
For What It's Worth:
+1 on the drop-tables feature for OpenJPA. But I would caution
against using it on each test.
Sadly, my experience is that drop-create-tables is 99.9% of the time
taken in a typical test.
The JDO TCK runs hundreds of tests and we drop-create tables only on
demand. The drop-create step takes several minutes compared to a few
seconds to actually run the tests.
After several years of doing this kind of work, I've concluded that
the best practical strategy (we tried beating up the database vendors
to make drop-create as fast as insert/delete rows, to no avail) is to
write your tests such that at the beginning of the test, you create
your test data and at the end of the test, you delete the test data,
leaving the database in an empty state.
JUnit facilitates this by providing a setUp and tearDown. We create
the test data in setUp and delete it in tearDown. Of course, the
tearDown might fail, leaving the data in an unpredictable state, but
it does work 99.9% of the time. That's why we have a common tearDown
that is very carefully implemented to catch exceptions, retry, etc.
Craig
On Jan 2, 2007, at 12:52 PM, Marc Prud'hommeaux wrote:
> Robert-
>
> I completely agree. We usually just build all the tables once and
> then just try to make sure all the objects are deleted at the end
> of the test, but you are correct that this is sometimes more
> cumbersome than it could be. An easy drop-then-create option would
> simplify this, although some databases can be notoriously slow with
> schema interrogation and manipulation that doing it for each test
> might wind up being prohibitively slow.
>
>
>
> On Jan 2, 2007, at 3:44 PM, robert burrell donkin wrote:
>
>> On 1/2/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
>>> Shay-
>>>
>>> Unfortunately, we don't have any automatic drop-table feature, but I
>>> agree it would be handy (you might want to make a JIRA report with
>>> the suggestion).
>>>
>>> The only other recourse, I think, would be to just manually delete
>>> the database files before running your tests.
>>
>> support for easy integration testing is one area where i think many
>> JDO implementations could really improve
>>
>> it's vital to start with a known database state and clean up after
>> each integration test. this isn't as easy as it should be when you
>> have a complex object-relational mapper with extensive caching. a set
>> of side doors for integration testing would really help productivity.
>>
>> - robert
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Perform automatic drop and create db schema
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Robert-
I completely agree. We usually just build all the tables once and
then just try to make sure all the objects are deleted at the end of
the test, but you are correct that this is sometimes more cumbersome
than it could be. An easy drop-then-create option would simplify
this, although some databases can be notoriously slow with schema
interrogation and manipulation that doing it for each test might wind
up being prohibitively slow.
On Jan 2, 2007, at 3:44 PM, robert burrell donkin wrote:
> On 1/2/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
>> Shay-
>>
>> Unfortunately, we don't have any automatic drop-table feature, but I
>> agree it would be handy (you might want to make a JIRA report with
>> the suggestion).
>>
>> The only other recourse, I think, would be to just manually delete
>> the database files before running your tests.
>
> support for easy integration testing is one area where i think many
> JDO implementations could really improve
>
> it's vital to start with a known database state and clean up after
> each integration test. this isn't as easy as it should be when you
> have a complex object-relational mapper with extensive caching. a set
> of side doors for integration testing would really help productivity.
>
> - robert
Re: Perform automatic drop and create db schema
Posted by robert burrell donkin <ro...@gmail.com>.
On 1/2/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
> Shay-
>
> Unfortunately, we don't have any automatic drop-table feature, but I
> agree it would be handy (you might want to make a JIRA report with
> the suggestion).
>
> The only other recourse, I think, would be to just manually delete
> the database files before running your tests.
support for easy integration testing is one area where i think many
JDO implementations could really improve
it's vital to start with a known database state and clean up after
each integration test. this isn't as easy as it should be when you
have a complex object-relational mapper with extensive caching. a set
of side doors for integration testing would really help productivity.
- robert
Re: Perform automatic drop and create db schema
Posted by Shay Banon <ki...@gmail.com>.
Opened up: https://issues.apache.org/jira/browse/OPENJPA-94 .
Thanks!
Marc Prud wrote:
>
> Shay-
>
> Unfortunately, we don't have any automatic drop-table feature, but I
> agree it would be handy (you might want to make a JIRA report with
> the suggestion).
>
> The only other recourse, I think, would be to just manually delete
> the database files before running your tests.
>
>
> On Jan 2, 2007, at 3:34 PM, Shay Banon wrote:
>
>>
>> I am trying to figure out how to configure OpenJPA to perform drop
>> and then
>> create the db schema. I got as far as:
>>
>> <property name="openjpa.jdbc.SynchronizeMappings"
>> value="buildSchema" />
>>
>> Which causes the schema to be created, but does not drop it when
>> the EMF
>> closes (or when a new EMF starts).
>>
>> The main reason I want it for is for simple tests, where each tests
>> works
>> against a fresh copy of the database.
>>
>> I tried doing things like: buildSchema(SchemaTool=drop) and things
>> in that
>> nature, but have not managed to find the correct configuration
>> (don't have
>> much time to dive into the OpenJPA code, sorry for that).
>>
>> Cheers,
>> Shay
>> --
>> View this message in context: http://www.nabble.com/Perform-
>> automatic-drop-and-create-db-schema-tf2909915.html#a8130220
>> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>>
>
>
>
--
View this message in context: http://www.nabble.com/Perform-automatic-drop-and-create-db-schema-tf2909915.html#a8131090
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: Perform automatic drop and create db schema
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Shay-
Unfortunately, we don't have any automatic drop-table feature, but I
agree it would be handy (you might want to make a JIRA report with
the suggestion).
The only other recourse, I think, would be to just manually delete
the database files before running your tests.
On Jan 2, 2007, at 3:34 PM, Shay Banon wrote:
>
> I am trying to figure out how to configure OpenJPA to perform drop
> and then
> create the db schema. I got as far as:
>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema" />
>
> Which causes the schema to be created, but does not drop it when
> the EMF
> closes (or when a new EMF starts).
>
> The main reason I want it for is for simple tests, where each tests
> works
> against a fresh copy of the database.
>
> I tried doing things like: buildSchema(SchemaTool=drop) and things
> in that
> nature, but have not managed to find the correct configuration
> (don't have
> much time to dive into the OpenJPA code, sorry for that).
>
> Cheers,
> Shay
> --
> View this message in context: http://www.nabble.com/Perform-
> automatic-drop-and-create-db-schema-tf2909915.html#a8130220
> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>
Re: Perform automatic drop and create db schema
Posted by Dain Sundstrom <da...@iq80.com>.
If you use hsqldb or H2 you can create an in-memory which will always
be empty. This makes testing very easy and fast.
-dain
On Jan 2, 2007, at 12:34 PM, Shay Banon wrote:
>
> I am trying to figure out how to configure OpenJPA to perform drop
> and then
> create the db schema. I got as far as:
>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema" />
>
> Which causes the schema to be created, but does not drop it when
> the EMF
> closes (or when a new EMF starts).
>
> The main reason I want it for is for simple tests, where each tests
> works
> against a fresh copy of the database.
>
> I tried doing things like: buildSchema(SchemaTool=drop) and things
> in that
> nature, but have not managed to find the correct configuration
> (don't have
> much time to dive into the OpenJPA code, sorry for that).
>
> Cheers,
> Shay
> --
> View this message in context: http://www.nabble.com/Perform-
> automatic-drop-and-create-db-schema-tf2909915.html#a8130220
> Sent from the open-jpa-dev mailing list archive at Nabble.com.