|[eclipselink-users] One-to-many relation in a composite polyglot PU|
Hello,I am trying to migrate a MySQL database to a composite MySQL-MongoDB database and still keep related entities managed by EclipseLink (as opposed to removing the explicit relations and managing them manually).
The summarized question is:Is it possible to have a one-to-many (or more generally a many-to-many) relation across a composite persistence unit, one side in a relational database and the other in a NoSQL database ?
In essence, I have two entities that participate in a one-to-many relationship, with the owning entities on the "many" side. I.e., it requires only two tables in MySQL with a column on the "many" side that contains the ID of the entity on the "one" side:
| One | | Many | |-----| |--------| | id | | id | | ... | | one_id | | ... | | ... |I am trying to replicate that structure in y polyglot and composite PU, with the "Many" side on MongoDB. Note that I don't actually use MongoDB how it is intended to be used (i.e. as a *document* store), and I am interested in keeping the flat structure of a SQL table.
A bidirectional relation would be great, however I would also be happy with a unidirectional relation IF it goes from the "One" (MySQL) to the "Many" (MongoDB) side, i.e. I should be able to do: `one.getManies()`
I came already across the example by Markus Eisele, the only one I found showing a composite *and* polyglot PU in EclipseLink with a great level of details:
http://blog.eisele.net/2012/11/polyglot-persistence-eclipselink-with.html https://github.com/myfear/polyglot-persistenceIn this example, the relation between the RDB and NoSQL databases is a one-to-one relation, and it seems that it is an important part of the trick. The article somewhat implies that the one-to-one relation is capital to the magic, but never explains it.
So I put together a toy project to try and come up with a composite polyglot one-to-many example, but failed so far to do exactly what I wanted to.
You can find the sources on github: https://github.com/Crazygolem/eclipselink-polyglot/tree/poly https://github.com/Crazygolem/eclipselink-polyglot/tree/poly/entities/src/main/java/entities (there is a readme in the master branch)My solution is to introduce another entity besides the "Many" one in the NoSQL database that has a one-to-one relationship with the "One" entity in the relational database and mirrors it, i.e. a one-(to-one)-to-many relationship:
One --|-- (One) ---- Many MySQL | MongoDB But I am not the most happy with this solution, as1. It introduces a new entity solely for the sake of migrating a table, i.e. the relation cannot be preserved as-is when migrating. Also that new entity has no direct use for the user, just like a table keeping track of *-to-many relations where the owning entity is on the "*" side, i.e. it is managed behind the scene by the JPA provider, you won't have to create an entity for that purpose even if there is an additional table (but somehow in my polyglot case you have to?)
2. The phantom "(One)" entity stores in MongoDB a reference to each of its "Many" entities and I would have liked to avoid that, as it is done in MySQL with the foreign key only on "one" side ("one_id"). (This part is not driven by relational absolutisms, but by the fact that the number of stored IDs might "rapidly" overgrow the document size limit of 16 MB in my case.) It might be a limitation due to the fact that there are no proper joins with [EL for?] MongoDB [or are they?], but it should be doable in terms of `find(<criteria>)` where the criteria keeps only documents with a "foreign key" to the matching "(One)" entity's ID. So this is a bit unintuitive, but i can manage that for now.
So, now for the million points questions:Is a one-to-one relation the only solution to cross the border of a RDB-NoSQL composite persistence unit?
If not, how to get rid of that "(One)" entity?Otherwise, is there a way to get rid of the IDs of the "Many" entities in "(One)"? (i.e. basically do the same thing as `mappedBy` does in an RDB PU)
Best regards, Jeremiah
Back to the top