Physical Table Descriptions

All columns of the legacy system’s tables must be defined, not only those that contain data that will be available in 3DSpace. All columns of the foreign tables must be defined in the order they exist in the foreign database. Each column must have at least COLUMN and DATATYPE specified. Comments in the provided mapping file show the syntax of the description:

#### physical tables
# COLUMN DATATYPE [primary]

where:

Item	Description / Values
COLUMN	The column heading.
DATATYPE	The type of data in the column. Can be one of these values: int real string date long raw hashed largestring The first four data types correspond to attribute types in 3DSpace.

Rules:

• Long and raw are defined in Oracle. Long means a long string datatype, and raw means a long raw datatype.
• A string datatype is limited to 255 characters. Anything longer will be truncated. A long datatype can be used for longer strings. When mapping to 3DSpace, you could use a long anywhere a string is needed (for a description or string attribute).
• The largestring keyword allows you to specify internal string lengths greater than 255 characters. You can declare a column of a table to be of type largestring followed by its length in bytes. The length can be any number supported by the database. The largestring columns store a null character so the value set should be at least 1 more than the number of bytes you need the column to hold, or the value will be truncated.
• A raw datatype can be used if your adaplet supports icons/images, in which case you can map the icon to a raw column.
• A hashed datatype can be used for columns that represent unique object or relationship ids. This is only suitable for use on an integer column (number(10) will suffice), and there must be a unique constraint on the column.

Adaplets must abide by Oracle scale and precision definitions assigned to columns when the legacy schema was designed. In Oracle SQL*Plus, you can show all columns in the correct order with data types by entering describe TABLE_NAME;

The primary keyword indicates that this column is the object’s identifier. The primary column should be a name or integer column. If more than one column must be used to provide a unique identifier for an object, do not use the primary keyword. For more information, see Creating an ID From Multiple Table Columns.

When a table does not have a single column that provides a unique identifier key to be used as primary, you can include the Oracle column called ROWID in the table description. The value of ROWID evaluates to the internal Oracle identifier for a table row. For example:

table DEPT {
      ROWID string primary ;
      ... }

If the primary column in a table is not an integer, the identifier must be mapped when used in an 3DSpace definition in the mapping file. For more information, see Mapping File Syntax.

Example table with column definitions for the Employee table:

table EMP {
  EMPNO int primary;
  ENAME string;
  JOB string;
  MGR int;
  HIREDATE date;
  SAL real;
  COMM real;
  DEPTNO int;
}

The EMP table has two string type columns, three integer type columns, two real number columns, and one date column. EMPNO will be used as its OID in 3DSpace. The ENAME column is used as the Employee business object’s name.