Photo by james_in_to

Previously I blogged about specifying one-to-many relationships in ATG. Turns out I could have done it much more simply.

Using the same example of players belonging to a team here are the simplified SQL repository definitions.
[code language=”xml”]<item-descriptor name="team">
<table name="team" type="primary" id-column-names="id">
<property name="name" required="true" />
</table>
<table name="player" type="multi" id-column-names="team_id">
<property name="players" column-name="id" data-type="set" component-item-type="player" />
</table>
</item-descriptor>

<item-descriptor name="player">
<table name="player" type="primary" id-column-names="id">
<property name="name" required="true" />
<property name="team" column-name="team_id" item-type="team" />
</table>
</item-descriptor>[/code]
And here is the SQL.
[code language=”sql”]CREATE TABLE team (
id VARCHAR2(40) NOT NULL,
name VARCHAR2(3) NOT NULL,
CONSTRAINT team_p PRIMARY KEY (id)
);

CREATE TABLE player (
id VARCHAR2(40) NOT NULL,
name VARCHAR2(40) NOT NULL,
team_id VARCHAR2(40),
CONSTRAINT player_p PRIMARY KEY (id)
);[/code]
You can set the team in the player repository item.
[code language=”java”]player.setPropertyValue("team", team);[/code]
Or add the player to the team repository item.
[code language=”java”]Set<RepositoryItem> players = (Set<RepositoryItem>) team.getPropertyValue("players");
players.add(player);[/code]
What gets tricky is if you require a player to be on a team.
[code language=”xml”]<property name="team" column-name="team_id" item-type="team" required="true" />[/code]
[code language=”sql”]team_id VARCHAR2(40) NOT NULL,[/code]
In this case you cannot remove a player from a team by doing this:
[code language=”java”]Set<RepositoryItem> players = (Set<RepositoryItem>) team.getPropertyValue("players");
players.remove(player);[/code]
This is because when you remove the player from the team the player will no longer have a team. This violates both the repository definition and will result in a SQL error because team_id cannot be null.

The only way you can change a player’s team is to do this.
[code language=”java”]player.setPropertyValue("team", newTeam);[/code]
To help enforce this you can make the team’s players property unwritable.
[code language=”xml”]<property name="players" column-name="id" data-type="set" component-item-type ="player" writable="false" />[/code]
I hope this makes sense. If you have any questions please leave them in the comments.