[eclipselink-users] One-to-many relation in a composite polyglot PU

[Jeremiah Menétrey <jeremiah.menetrey@xxxxxxx>]

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

In 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, as

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