Environment Catalog

Table Statement

TABLE table_name
[TYPE table_type_spec]
[PHYSICAL "filespec"]
[PARTITION BY "partition_spec"]
[OPTIONS "options"]
[CARDINALITY n]
[INDEXMAINTENANCE {DBMS | API}]
[{PARENT / PRIMARY} [{KEY / CONSTRAINT}] column_spec
[{CHILD / FOREIGN} [{KEY / CONSTRAINT}] column_spec
REFERENCES table[(column_spec)]]
[AS "as_spec"]
COLUMNS [FROM] "table" | column definitions for this table

TABLE table_name

table_name

table_name represents a non-ambiguous table or view name. table_name must be unique within the database statement but can be ambiguous within the environment. However, any reference to an ambiguous table_name in an application will have to be qualified by the database name: database_name.table_name

table_name MUST BEGIN WITH A LETTER, A-Z (case insensitive), and may contain up to 32 characters including letters, numbers and allowed special characters.

If table_name contains any reserved words or allowed special characters, it must be enclosed in single or double quotes.

TYPE table_type_spec

TYPE is set according to the DATABASE TYPE declared.

One of the following:

• [{CACHED | DYNAMIC}] VIEW
• OST (Flat Files)
• TDF (Flat Files)
• RELATIONAL (SQL-Class databases only)

PHYSICAL "filespec"

Optional. filespec is a quoted string, unlimited in length, and can contain an unlimited number of comma separated file specifications, including file names and appropriate path and password information as well as any operating system command that outputs a valid physical file name entry. However, each individual file specification is limited to 255 characters in length. See File Name Handling for more details.

OPTIONS "options"

Options apply to TDF (tab-delimited file) tables only. Multiple options are space separated. The entire option string is enclosed in quotes. Valid options are:

COLUMN='string' - Defines the column delimiter.

RECORD='string' - Defines the record delimiter.

QUOTES - Instructs OA to support quotes in a record. If omitted, quotes are not supported. Note that this option does not cause OA to create TDF files with quotes. It is strictly to support quotes in existing files.

ESCAPE='character' - Defines an escape character used to escape delimiters and quotes.

options "column='\t' record='\r\n'"

CARDINALITY n

The cardinality of a table is the number of records in the table. This number should be near the actual table cardinality. Omnidex uses this number to better optimize some queries.

INDEXMAINTENANCE {DBMS | API}

Optional. Default is API. Specify how Omnidex indexes will be updated.

API - The indexes will be updated by Omnidex automatically as the database data is updated.

DBMS - The indexes will be updated by some other means, ODXAIM for example, in a separated process.

This setting overrides the indexmaintenance definition in the database section.

[{PARENT / PRIMARY} [{KEY / CONSTRAINT}] column_spec]
[{CHILD / FOREIGN} [{KEY / CONSTRAINT}] column_spec]

Optional. The column_spec is the column name or a column specification qualified by a table name. keytype declares any of the following values:

PARENT or PRIMARY
CHILD or FOREIGN

Foreign keys use a REFERENCES clause to relate the child to a parent table by a previously declared primary constraint. Example:

FOREIGN CUSTOMER_NO REFERENCES
CUSTOMERS(CUSTOMER_NO)

- or -

CHILD KEY CUSTOMER_NO REFERENCES
CUSTOMERS(CUSTOMER_NO)

Primary keys and foreign keys can be declared for the same table.
Multiple column constraints are not presently supported.
Omnidex does not enforce constraints. Constraint enforcement is left up to the DBMS. Therefore, constraints on flat file databases are not enforced.

AS "sql_statement"

AS defines a table as a UNION VIEW. Data from other physical tables in the database is unioned together using SQL select statements. The other physical tables must also be defined in the environment file.

The column definitions for the view must match the original physical tables exactly. Therefore, the COLUMNS FROM "table" column definition should be used, as opposed to explicitly defining each column in the view.

Top