Issue Details (XML | Word | Printable)

Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Unassigned
Reporter: John Michelau
Votes: 0
Watchers: 1

If you were logged in you would be able to see more operations.
DataNucleus AccessPlatform

When objects X & Y have a 1-N relationship with Z, and both have the same field name for the Z collection, SchemaTool creates column names that collide in the Z table

Created: 02/Nov/13 02:38 AM   Updated: 21/Nov/13 11:10 AM   Resolved: 04/Nov/13 09:07 AM
Component/s: Software
Affects Version/s: 3.3.0.release
Fix Version/s: None

File Attachments: 1. GZip Archive one-to-n-collide.tar.gz (2 kB)

Environment: Ubuntu 13.10, Google CloudSQL (MySQL), IntelliJ 13 EAP, Maven

Datastore: MySQL
Severity: Development

 Description  « Hide
I have 3 entity classes: Group, User, and Asset. Both Group and User have a 1-N relationship with Asset, both have a field of "Set<Asset> assets" that is @Persistent, and both have a primary key field named "id". When I run SchemaTool's datanucleus:schema-create from Maven I get the following error:

The column "ASSETS_ID_OWN" exists in table "`ASSET`" and has invalid metadata. The existing column is "<column/>

After a little trial and error, I found that SchemaTool was attempting to create a column of ASSET.ASSETS_ID_OWN for both Group and User, per

I was able to work around the issue by renaming the field in one or both of the entity objects to something like "userId" or "groupId". Neither of these is appealing, because these are fields in the User and Group class respectively, so there's no need to embed scoping in the names.

My expectation here is that SchemaTool would resolve the collision somehow. The ambiguity of the naming chosen by the tool seems to be the root cause. The name gives no clue as to which table the key is for, which is a problem in general if you're manually inspecting the database. The easy fix would be to have SchemaTool prefix these foreign keys with the name of the source table.

I've attached a simple program that can illustrate this for you. I stripped my project down to the bare bones. I hope this suffices as a test case.

Just un-tar the attached file and do the following to reproduce the issue:

- Update javax.jdo.option.* to point at your own MySQL instance and authenticate with it.
- Create the "example" database in your MySQL instance.
- mvn clean package
- mvn datanucleus:schema-create

I'm probably going to end up moving to using a JOIN table for these relationships anyway, but this was definitely a barrier to getting up and going quickly with SchemaTool. It keeps it from doing the intuitive thing and "just working".

Sort Order: Ascending order - Click to sort in descending order
John Michelau added a comment - 02/Nov/13 02:46 AM
I just realized that I skipped a step from and didn't post something to the forum first. I am able to work around this issue I think (JOIN table), and was only posting for your benefit.

Andy Jefferson added a comment - 04/Nov/13 09:07 AM
Suggest you look at IdentifierFactory extension allowing you to define how columns/tables are named when the user doesn't bother specifying names. The JPA spec defines how things are named, and that would *not* avoid name clashes in your case (since the id field of the relation owner is the same in both cases, as is the relation field). The JDO spec doesn't define a naming standard so datanucleus provides one (there are some additional identifier factories builtin that you can use, or extend with your own).

Obviously you could also raise an issue on Apache JDO to include a definition of naming in the next release of the spec :
such that people have good defaults, maybe avoiding all potential naming clashes. If you do raise an issue, make sure you propose what the different tables/columns should be named so people have a basis for comment.

If you define your own identifier factory you could then contribute it for others to use (raise an improvement request on NUCRDBMS project and attach the identifier factory class to the issue).