Foreign Keys

Foreign Keys

A Foreign Key is a column that refers another table. The relationship between tables can be specified in terms of Cardinality and Dependency.

Cardinality refers to the number of objects at each end of the relationship. There is a parent-child relationship between objects. The cardinality can be specified as one of the following:-

  • NONE
  • ONE_ZERO
  • ONE_ONE
  • MANY_ZERO
  • MANY_ONE

Dependency refers to whether the the relationship between two objects. It can be either: -

  • Dependent
  • Associative

A dependent (aggregate) relationship refers to whether the child is dependent on the parent. An associative relationship is just what is says.

A good example of a dependent MANY_ONE relationship is in the assembly of a table and legs, the existence of legs is dependent on the table.

Each Foreign Key Column will try to find a Foreign Table for its key.

Properties Reader

When reading Columns in with the Properties Reader the Cardinality and Dependency is specified in the relationship modifier: -

ASSOCIATE | ASSOCIATES | DEPENDENT | DEPENDENTS <ClassName>

The combination of the relationship modifier and the column nullability sets the dependency and cardinality as follows: -

TYPE & NULLABILITY Dependency Cardinality
ASSOCIATE & NULL Associate ZERO_ONE
ASSOCIATE & NOT NULL Associate ONE_ONE
ASSOCIATES & NULL Associate ZERO_MANY
ASSOCIATES & NOT NULL Associate ONE_MANY
DEPENDENT & NULL Dependent ZERO_ONE
ADEPENDENT& NOT NULL Dependent ONE_ONE
DEPENDENTS & NULL Dependent ZERO_MANY
DEPENDENTS & NOT NULL Dependent ONE_MANY

Most properties are refered to using the following syntax (currently, the primary key uses the propertyName+KeySuffix() in the properties file (e.g. MyTable.myTableId.xxx=<value>): -

<ClassName>.<propertyName>.xxx=<value>

Jdbc Reader

Dependency and Cardinality information is not reliably available through the Jdbc interface, so it must be supplemented using the properties file.

Cardinality and dependency can be specified in the properties file using the following syntax: -

<ClassName>.<propertyName>.associate=<ClassName>
<ClassName>.<propertyName>.associates=<ClassName>
<ClassName>.<propertyName>.dependent=<ClassName>
<ClassName>.<propertyName>.dependents=<ClassName>

Code generation

JGenerator generates code to the JBeans standard. JBeans aims to produce a simple, standard and useful set of interfaces. For relationships these are intended to be two way.

Where there is a MANY_ONE or MANY_ZERO relationship, for example between a House and its rooms, the following interfaces are generated on a House: -

public void addRoom( Room room ) throws ValidationException;
public void removeRoom( Room room ) throws ValidationException;
public Enumeration getRooms() throws ValidationException;
public void clearRooms();

On a Room the following interfaces are generated: -

public House getHouse() throws ValidationException;
public void setHouse( House house ) throws ValidationException;

Where there is a ONE_ONE or ONE_ZERO relationship, for example between an Order and its details, the following code is generated on an Order: -

public OrderDetails getOrderDetails();
public void setOrderDetails( OrderDetails orderDetails );

On an OrderDetail the following

public Order getOrder();
public void setOrder( Order order );

Relationship

By default the property methods that are generated are based on the propertyName. Sometimes the relationship name is other than the columns names infer. To override the relationship name use the following syntax: -

<ClassName>.<propertyName>.relationship=<Relationship Name>

For example: -

User.client.relationship=Owner

Bean Keys

Bean Keys provide a powerful mechanism for getting a foreign key to point to more than one other table. This functionality is useful where a foreign key is of a super-type, such as a Location and there are several Tables that represent Locations, for example a Country, State or City.

To specify a Bean Key two columns are needed one is the foreign key and the other is the type of the key. Because the type of the foreign key is shared amongst several tables those tables must share the same primary key type. The type of the key is specified using a special Enumerated Type called a Bean Type, where the class name must be used as the Enumerated Types name, and this must correspond to the class names of other beans in the package.

For example there could be a Government table that represents different levels of government (e.g. Country, State, City).

The Table and the Bean Type would be specified as follows (assume a Country, State and City table have been defined): -

readColumn.Government.1=locationKey INT ASSOCIATES BEAN
readColumn.Government.2=locationType CHAR(2)

Location=$CREATE $BEANTYPECREATOR
LocationType.enum=0.COUNTRY,"CO",$PACKAGE.Country
LocationType.enum=1.COUNTRY,"ST",$PACKAGE.State
LocationType.enum=2.CITY,"CI",$PACKAGE.City

This will create the following methods on the Government interface.

public Bean getLocation();
public void setLocation( Bean location );

public LocationType getLocationType();
public void setLocationType( LocationType locationType );

This will also create a AbstractBeanType (a subclass of EnumeratedType), which is a simple EnumeratedType, except that is has the following method: -

public Bean findByPrimaryKey(ClassContext cc,Object pk);

The way this works is that the Lazy Government Bean loads up the locationKey and the locationType. When the user requests the Location the Lazy gets the Bean from the AbstractBean type findByPrimaryKey method.

Two bean keys of the same type can be added to a table. For example if we wanted to have another Location (a position) associated with a table, but want use the LocationType again, a simple enumerated name mapping will do the job. For example: -

readColumn.Government.1=locationKey INT ASSOCIATES BEAN
readColumn.Government.2=locationType CHAR(2)
readColumn.Government.3=positionKey INT ASSOCIATES BEAN
readColumn.Government.4=positionType CHAR(2)

Government.positionType.enum=LocationType

Alien Keys

Alien Keys is a term used (in remote joins) to refer to relationships outside the database. JGenerator supports alien keys by allowing the table name in the relationship modified syntax to be prefixed with a Context name. When the Column looks for its foreign key in the database, it will first look it its own databases. Then it will try to find Columns in databases in other contexts.

For example imagine a corporate with several databases, where the Customer information is held on one database and the Investment Account information is held on another. It would be very nice to have a getPerson() method on the InvestmentAccount object. The customer information department would have to supply a context properties file describing their database or views. The Investment Department could then link up. A column in the InvestmentProduct table might look like this: -

readColumn.InvestmentProduct.3=personKey VARCHAR(30) ASSOCIATE Customer.Person

In addition the Customer context needs to be imported to the InvestmentAccount context. The following line in the properties file loads up the Customer Context meta data into the Dictionary: -

read.0=$GENERATOR.JGenerator $ROOT/customer.properties

SubClasses

It may be necessary to restrict the type of classes that can be set in a foreign key, especially if they are a Bean relationship.

These classes can be restricted using a comma seperate list of class names (if they are declared as tables) or fully qualified class names if they are not.

<className>.<propertyName>.subClass=class1,com.xyz.class2

Foreign Key Methods

The Table Bean has a number of methods to access Foreign Keys.

Accessor methods for the foreign keys on a table: -

  • public void addForeignKey( Column foreignKey );
  • public void removeForeignKey( Column foreignKey );
  • public Column getForeignKey( int foreignKeyNo );
  • public Column getLastForeignKey();
  • public Column getForeignKey( String foreignKeyName );
  • public int getForeignKeyCount();
  • public int getIndexOfForeignKey( Column foreignKeyColumn );
  • public Enumeration getForeignKeys();
  • public Enumeration getDependentKeys();
  • public Enumeration getAssociateKeys();

Accessor methods for the foreign keys on the foreign table: -

  • public Column getForeignColumn( int remoteColumnNo );
  • public Column getLastForeignColumn();
  • public int getForeignColumnCount();
  • public int getIndexOfForeignColumn( Column remoteColumn );
  • public Enumeration getForeignColumns();

The Column Bean has a number of methods to access Foreign Keys: -

  • public boolean isForeignKey();
  • public void setForeignKey( boolean foreignKey );
  • public String getForeignTableName();
  • public void setForeignTableName( String foreignTableName );
  • public String getForeignContextName();
  • public void setForeignContextName( String foreignContextName );
  • public Table getForeignTable();
  • public int getCardinality();
  • public void setCardinality( int cardinality );
  • public boolean isZeroToOne();
  • public boolean isOneToOne();
  • public boolean isOneToMany();
  • public boolean isAssociate();
  • public void setAssociate( boolean associate );
  • public boolean isDependent();
  • public void setDependent( boolean dependent );
  • public String getRelationship();
  • public void setRelationship( String relationship );

Foreign Key Caching

By default collections of beans that represent a many-one relationship are cached in the Bean. Where there is a single server this caching remains valid. This is known as link caching. Beans can switch off the link cache between beans

<table>.useLinkCache=<true|false>

For example a Parent table has a many-one Child tables then setting it to true will use linkCaching for the whole table: -

Parent.uselinkCache=true

A reason for switching off link caching on a single server is sortOrder. With linkCaching the sort order is fixed in the beans, so if the database changes this is not reflected in the order that the beans return.

If you still want link caching and want to preserve sort order the Javelin core classes also have Comparators for handling this on the client.

FindBy Caching

The findByPrimaryKey and findByAll methods can be switched to get their results from the cache. Where there is a single server then the cache will be kept upto date - so it is *safe* to get these values from the cache. To switch this behaviour on say

[<table>.]findByAll.useCache=true

[<table>.]findByPrimaryKey.useCache=true