Table

Tables represent structures such as database tables or views, Java objects or xml entities.

Table

A Table can represent one or more of the following: -

  • A Database Table
  • A Database View
  • A Bean
  • An Object
  • An XML Entity

PropertiesReader

Tables can be read in from a list in the properties file. To define a table to be read in use the following syntax: -

readTable.<index>=<ClassName> [extends <ParentTableName>]

For example: -

readTable.0=Account
readTable.1=Customer
readTable.2=Product
readTable.3=Toy extends Product

Each tables columns are defined using the following syntax: -

readColumn.<ClassName>.<index>=<propertyName> <type> <nullablity> <reference>

For example to create an Account and an Address table specify the following: -

readTable.0=Account
readColumn.Account.0=accountKey INT PRIMARY KEY
readColumn.Account.1=accountName VARCHAR(30) NOT NULL
readColumn.Account.2=balance DOUBLE NOT NULL
readColumn.Account.3=dateTimeStamp TIMESTAMP NOT NULL

readTable.1=Address
readColumn.Address.0=addressKey INT PRIMARY KEY
readColumn.Address.1=locationKey INT NOT NULL ASSOCIATE Location
readColumn.Address.2=firstName VARCHAR(30) NOT NULL
readColumn.Address.3=lastName VARCHAR(30) NOT NULL
readColumn.Address.4=email VARCHAR(100) NOT NULL
readColumn.Address.5=line1 VARCHAR(30) NOT NULL
readColumn.Address.6=line2 VARCHAR(30) NULL
readColumn.Address.7=city VARCHAR(30) NOT NULL
readColumn.Address.8=state VARCHAR(30) NOT NULL
readColumn.Address.9=postCode VARCHAR(10) NOT NULL
readColumn.Address.10=dateTimeStamp TIMESTAMP NOT NULL

A comment can be added to each table, this is set using the following syntax: -

<ClassName>.comment=<comment>

Tables can be marked as read only, using the following syntax

<ClassName>.readOnly=true

Tables can be marked as pseudo tables. This means that they are not to be persisted, and hence no Session or Home will be created, using the following syntax: -

<ClassName>.pseudo=true

By default a table refers to a table, it can also refer to a view. When this is done the table becomes read only, a create, store and remove method is not created on the home. In addition the beans are not cached. To set a table as a database view in the properties file, use the following syntax: -

<ClassName>.view=true

The SQL for the view is defined as follows:

<ClassName>.sql=SELECT ...

For example:

readTable.40=TestView
readColumn.TestView.0=name VARCHAR(10) NOT NULL
readColumn.TestView.1=gender CHAR(1) NOT NULL

TestView.view=true
TestView=$CREATE $CREATOR
TestView.sql=SELECT Customer.name, Customer.gender FROM Customer

Alternatively if the view is just a sub-set of another tables columns - then instead of setting the sql you can specifiy the viewTable (classname)

<ClassName>.viewTable=<SuperSet ClassName>

e.g.

TestView.viewTable=BigTestTable

JdbcReader

When a table name is read from the database using the JdbcReader, the name read in is set as the table's physicalName. The table name is then set as the physical name converted to UpperLower case with spaces removed. The Class and Entity name then default to the tableName. These values can be overridden using the properties files explicit table names modifier syntax.

It is possible to limit the JdbcReader so that only the tables in the list in the property file are read in. To do this use the following syntax: -

jdbc.readAll=false

By default the JdbcReader reads in tables and views. It is possible to make sure that either just tables or views are read in using the following syntax: -

jdbc.read=[TABLE]|[VIEW]

The following example ensures just views are read in: -

jdbc.read=VIEW

The JdbcReader will understand from the meta data that the table is a view, but this can be overridden by the properties file, for example if you wanted a table to be treated as a view.

Table Names

The Table in the Dictionary has a number of names, these are: -

  • TableName - the logical name used to refer to the table.
  • Physical Name - the name in the underlying store.
  • Class Name - the Object Oriented Name
  • Entity Name - the xml name

The name can be read in from the database, by the JdbcReader or read from a properties file by the PropertiesReader, using the readTable syntax.

The Physical Name, is defaulted to the table name, but can set in the properties file using the following syntax: -

<ClassName>.physicalName=<Physical Name>

The Class Name, is defaulted to the table name, but can set in the properties file using the following syntax: -

<ClassName>.className=<Class Name>

The Entity Name, is defaulted to the table name, but can set in the properties file using the following syntax: -

<ClassName>.entityName=<Entity Name>

For example: -

<CurrentAccounts>.physicalName=CRRT ACCT
<CurrentAccounts>.className=Current Account
<CurrentAccounts>.entityName=$CONTEXTNAME_CurrentAccount

Author

An author can be added to each table, this is set using the following syntax: -

<DatabaseName>.<ClassName>.author=<copyright>

For example: -

RefDatabase.MyTable.author=Robin Sharp

Comment

A comment can be added to each table, this is set using the following syntax: -

<DatabaseName>.<ClassName>.comment=<comment>

For example: -

RefDatabase.MyTable.comment=This is a comment

Version

A version can be added to each table, this is set using the following syntax: -

<DatabaseName>.<ClassName>.version=<version>

For example: -

RefDatabase.MyTable.version=Version 1.1.2
 

Table Columns

Columns can be added to, or removed from a Table bean, and they can be accessed by name, index or Enumeration. When a column is added to a table the column's table property is set and unset accordingly.

A list of mandatory and non-mandatory columns on a table can be returned, so that the minimal set needed for creation can be found.

A list of the column names in alphabetical order can also be returned, so xml based descriptions (e.g. EJB) can be created.

The table bean in the dictionary can also return an array of information about keys and locks. See the sections on primaryKeys, foreignKeys and locks for more information.

Table Objects

Table Objects are tables that are not created as full blown Beans but as simple objects. Table Objects are small objects that do not warrant a table of their own, but do warrant an object of their own. These objects have no storage of their own and as such no home or session is created for them. These objects are stored by being folded into a parent table, as columns. The Table class in the Dictionary has a number of methods of accessing these table objects.

The following example creates a Money table with two columns: -

readTable.0=Money
readColumn.Money.0=currency CHAR(3) NOT NULL
readColumn.Money.1=amount FLOAT NOT NULL

The object is then created using the property: -

Money=$CREATE $OBJECTCLASS

This will create a class with the signature: -

public class Money implements java.io.Serializable

This class can then be added to tables as a foreign key (see the Foreign Key section for more details).

For example to add the Money object to an Account table do the following: -

readTable.0=Account
readColumn.Account.0=accountKey INT PRIMARY KEY
readColumn.Account.1=accountName VARCHAR(30)
readColumn.Account.2=balance Money NULL

This will create an AccountBean with the methods: -

public Money getBalance()
public void setBalance( Money balance ) throws ValidationException

The set method will validate the object that the properties are not null.

This will also create an Account table with the following columns: -

CREATE TABLE Account
(
accountKey INT PRIMARY KEY,
accountName VARCHAR(30),
balance_currency CHAR(3) NULL
balance_amount FLOAT NULL
)

These columns can then be searched on and indexed as part of the Account table.

Table Indexes

Indexes can be added to or removed from a Table bean and they can be accessed by name, index or Enumeration. When an Index is added to a table the Index's table property is set and unset accordingly.

The same Column objects in the Table are used in the Index object to refer to the Table's Columns.

The Table can be asked for its primary key Index.

See the section on Indexes for more information.

Table Validation

When a Table is validated the following checks are made: -

  • The tableName is not set
  • The className is not set
  • The physicalName is not set
  • The Table has a singleton PK and no PKKey has been set.
  • Cross reference all the foreignKeys
  • Validate each of the Columns
  • Check if there are Indexes with the same columns and names
  • Validate each of the Indexes
  • Check there is a BeanType for every bean key
  • Set the default Key generation options

Writing Example

The following code fragment shows how to iterate through all the Columns for a Table, and write out their Java members: -

for( int index = 0; index < table.getColumnCount(); index++ )
{
    Column column = table.getColumn( index );
    writer.println( "protected " + column.getTypeName() + " " column.getPropertyName() + ";" );
}

The following code fragment shows how to iterate through all the Indexes for a Table, and write out their names: -

for( Enumeration indexes = table.getIndexes(); indexes.hasMoreElements(); )
{
    Index index = (Index)indexes.nextElement();
    System.out.println( index.getIndexName() );
}