Eclipse Community Forums: Gemini » Refactoring Databases Dynamically

Help

Home

Home » Eclipse Projects » Gemini » Refactoring Databases Dynamically

Show: Today's Messages :: Show Polls :: Message Navigator

Refactoring Databases Dynamically [message #997778]

Tue, 08 January 2013 18:18

Scott Hull

Messages: 32
Registered: July 2012

Member

Hello all!

Before I begin, I wanted to thank Mike and everyone else on the forum for the helpful suggestions for fixing my issues with Gemini. Right now, I have successfully figured out a way to implement Gemini into the production level of our code and it is working beautifully.

But here comes the curve ball: How do you manage your ever-changing annotated datastructure?

Many of the suggestions online have suggested managing the data structures with native SQL queries in between iterations of a product. This works fine in theory, but our product goes through daily builds and a small change on the JPA annotated data structure trashes the database directory the next day. Although our data structures are "stable" at this point, we find bugs and require minor adjustments to meet the needs of our customers.

So we are looking for an automated tool/techniques to help convert between databases to at least preserve most of the attributes in between build cycles.

So here is the question:
What is the best way to generically migrate your database that is created with JPA?

For our product, here is the path we are considering:

Create a diagnose and conversion tool called "Save My Assets" (SMA for short). The tool, in theory, will do the following:

If a database has changed because of the following:
-An attribute was added.
-An attribute was deleted.
-A new table was added (new object mapping)
-A new table was deleted.

Solve it following this method:
1.)Using native SQL calls, grab all the column names off each table.
2.)By using native SQL calls, grab the associated values to the attributes for each table objects and store them in a key-value pair arrangement (a map of String, Object, for example). This will work for base attributes (string, int, etc), but I have not figured out how to handled this for other mapped entities or for ArrayList just yet.
3.) Remove selected item from table.
4.) Create the new annotated POJO -> copy attributes from the map to each POJO and linked entities as necessary (probably a new Entity.fromString(String_k/v_pair_mapped_representation) operation). If an attribute is missing or added, always default to the value in the default constructor.
5.) Persist new item back into the database.

Apply these steps for all pieces of the database (and linked pieces as necessary).

I believe this will be the best solution I can come up with, nevertheless I am looking for any more generically automated tools or techniques that scale better than writing and maintaining a custom set of SQL calls between each iteration.

I want to know you all's thoughts on this process. Feedback is very welcome at this point and stage!

Thank you!

[Updated on: Tue, 08 January 2013 18:44]

Report message to a moderator

Re: Refactoring Databases Dynamically [message #997790 is a reply to message #997778]

Tue, 08 January 2013 18:50

Jay Billings

Messages: 54
Registered: July 2011

Member

I work with Forest and I just thought I would mention that our data structures are not "ever-changing," but they will change over the years and across different versions of the code. It will be a rare event that we remove fields, but over the course of a year we may add one or two fields that hold state that needs to be persisted. Ideally we could provide a small migration tool to bring persisted data from one version of the code to the next so that users could keep their data without having to manually reconstruct it.

Jay

Jay Jay Billings
Oak Ridge National Laboratory
Twitter: @jayjaybillings

Report message to a moderator

Re: Refactoring Databases Dynamically [message #997822 is a reply to message #997778]

Tue, 08 January 2013 20:20

Michael Keith

Messages: 243
Registered: July 2009

Senior Member

Hi Scott,

Glad that things are working with your application and that Gemini JPA is of some use to you.

To your schema migration question, I'm not 100% clear on the use case. You talked about iterations of the product and I thought you were referring to migrating production environments across product releases. But then you mentioned daily builds and it sounded more like the object model (and hence the schema) was changing during development. These are two fairly different problems that are typically solved in different ways.

In production it would generally be a bad idea to change a customer's database schema with a new release. Customers would typically have a cow if the database schema and the data got bulk changed in the course of an application upgrade. In many cases it would not even be possible because the database schema is outside the control of the application and the application had to map to the existing database schema to begin with. Also, there may be other applications that rely on that schema so it simply cannot be changed.

However, assuming that the database is shipped with, or private to, the application and that the customer has no qualms with a bulk data operation as part of the upgrade, the most structured way of modifying the schema and the data within it is typically to create an upgrade script that makes exactly the schema changes necessary for the new release. I wouldn't get into the business of creating a whole new program to do this, I would just use one of the schema migration/comparison/diff tools and use them to compare the two schemas. They already solve this problem. Take a look at Flyway and see if it does what you want.

During development phases, migration is not typically considered a problem because there is no live data in the database. The schema can just be removed and recreated as necessary.

Not sure if any of this helps...

-Mike

[Updated on: Tue, 08 January 2013 20:23]

Report message to a moderator

Re: Refactoring Databases Dynamically [message #997825 is a reply to message #997822]

Tue, 08 January 2013 20:31

Scott Hull

Messages: 32
Registered: July 2012

Member

Mike,

I forgot to mention that our database is shipped with our product, and that our database (for the most part) will be unique to each user or user-group depending on the setup (we wont necessarily run their databases). We currently ship our product with a derby database.

Our goal is to adjust the database behind the scenes so that if there is a database schema change, the user can run the tool to correct their database on their local machine or user group as necessary. We don't adjust the database schema very often; however, when a product is under continuous integration and a person can download the latest snapshot daily, these schema adjustments have to be done as quickly and painlessly as possible to help prevent a database from being trashed if a minor adjustment needs to be made on the schema.

Thank you for your input and continuous support! I will have a look at Flyway to see if this is a viable option, nevertheless I have to mention that these "adjustments" need to be shipped with the product and possibly unaffected by the build number as much as possible.

[Updated on: Tue, 08 January 2013 20:37]

Report message to a moderator

Re: Refactoring Databases Dynamically [message #1007861 is a reply to message #997825]

Thu, 07 February 2013 19:42

Scott Hull

Messages: 32
Registered: July 2012

Member

Is there a way to get the current dataSource off the EntityManagerFactory (or EntityManager)? If so, how?

The datasource is required to begin work on the Flyway plugin.

Flyway flyway = new Flyway();
flyway.setDataSource(/*How do I get this? */);
flyway.migrate();

Thanks,
Scott

Report message to a moderator

Re: Refactoring Databases Dynamically [message #1008236 is a reply to message #1007861]

Mon, 11 February 2013 16:42

Michael Keith

Messages: 243
Registered: July 2009

Senior Member

Hi Scott,

Do you really need the same actual datasource that is being used by the EMF or do you just want to connect to the same database but not necessarily through the same datasource object?

If the latter one then you can just look it up in the service registry. Will you know the database properties or will they be embedded within the persistence descriptor as javax.persistence.jdbc properties? If you don't know them then you can get them by calling getProperties() on the EMF. Gemini JPA returns an additional Map under the key "PUnitInfo" in the Properties Map. From that Map you can find the driver properties under driver[Url|ClassName|User|Password] keys. Once you have them then you can just look up the service under the driverClassName property (see the Gemini DBAccess examples).

Let me know how it goes.

-Mike

Report message to a moderator

Re: Refactoring Databases Dynamically [message #1008458 is a reply to message #997778]

Tue, 12 February 2013 15:09

Scott Hull

Messages: 32
Registered: July 2012

Member

Mike,

Thank you for your reply. I will give it a go within the next couple of days. Thank you for your information.

Forest

Report message to a moderator

Re: Refactoring Databases Dynamically [message #1046985 is a reply to message #997822]

Mon, 22 April 2013 16:32

Jay Billings

Messages: 54
Registered: July 2011

Member

Mike,

I got around to looking at this issue on our project some more. Flyway seems like it would work really well if we could dump the schema creation commands that Gemini/EclipseLink uses to create a new database. Is this available through one of the Gemini log levels or through some option?

We can use Flyway from the command line as described in their documentation

http://flywaydb.org/getstarted/firststeps/commandline.html

probably immediately if we can figure out how to dump the ddl-generation output. (I think that's the correct output we need.)

Unfortuantely Flyway does not have any native integration with the JPA. Instead, it needs the exact commands used to create the tables.

Thanks for your time and apologies for digging up an old post.

Jay

Jay Jay Billings
Oak Ridge National Laboratory
Twitter: @jayjaybillings

Report message to a moderator

Re: Refactoring Databases Dynamically [message #1047013 is a reply to message #1046985]

Mon, 22 April 2013 17:14

Michael Keith

Messages: 243
Registered: July 2009

Senior Member

Hi Jay,

You can direct EclipseLink to write the SQL out to a file as well by using some EclipseLink persistence unit properties. I believe that by specifying eclipselink.ddl-generation.output-mode of "both" and then including values for eclipselink.create-ddl-jdbc-file-name and eclipselink.drop-ddl-jdbc-file-name then you should be able to get that.

See here for an example.

-Mike

Report message to a moderator

Previous Topic:	"Sometimes", Gemini misses some service creation
Next Topic:	Gemini Web 2.2.2 RELEASE is now available

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 10 07:54:09 GMT 2023

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter