Schema Files Syntax

This article contains the following topics:
casm1401
This article contains the following topics:
The CA SDM database schema is defined in multiple .sch files in the $NX_ROOT/site (UNIX) or
installation-directory
/site (Windows) directory. During configuration, these .sch files along with any customized .sch files you may create are merged together into a single file called $NX_ROOT/site /ddict.sch (UNIX) or
installation-directory
/site/ddict.sch (Windows).
Review Using the Web Screen Painter prior to making any changes to these files.
Do not modify any .sch files in the $NX_ROOT/site (UNIX) or
installation
-
directory
/site (Windows) directory. Any changes you make to these files will be lost when upgrading to a new release or when certain patches are applied. If you want to make schema changes, you must create a file in the $NX_ROOT/site/mods (UNIX) or
installation-directory
/site/mods (Windows) directory with the file suffix .sch. Then add your changes to your .sch file, and your changes will then be merged with delivered schema during configuration. This is the only way to preserve your modifications when you upgrade to a new release.
TABLE Statement
Defines the logical tables in the CA SDM database schema and the logical columns (fields) in those tables. These logical tables and columns are then mapped to the physical tables and columns used by your database management system in a mapping statement that follows the TABLE statement.
If you define a new table, you must define a mapping statement for that table. The Mapping Statement is illustrated at the end of this chapter, followed by an example that combines the TABLE, TABLE_INFO, and mapping statements.
Syntax
TABLE
table_name
{
field value_type field_attributes
; [...]}
Arguments
  • TABLE
    Introduces the TABLE statement. Must be uppercase. You must have one TABLE statement for each logical table in the schema.
  • table_name
    The name of the database table, for example, Call_Req. If adding a database table, you can specify any name beginning with a lowercase letter z. (This avoids possible conflict with existing and future CA SDM table names.) If changing an existing table, find the table in one of the .sch files and use the same name.
  • field
    The name of a logical column in the table, for example, id or desc. You must identify each column by name. If adding a table or adding a column to an existing table, you can specify any name beginning with a lowercase letter z; however, field names must not end with the characters “_f.” (This avoids possible conflict with existing and future CA SDM column names.) If changing an existing column, find the column in one of the .sch files and use the same name.
  • value_type
    The field’s data type. Valid values are:
Value
Description
STRING
nn
A string that is
nn
characters long.
INTEGER
A 32-bit number.
LOCAL_TIME
The number of seconds since January 1, 1970. CA SDM automatically reformats this data type to the designated date format, for example:
mm
/
dd
/
yy hh
:
mm
:
ss
.
DURATION
A period of time, measured in seconds.
REAL
A floating point number
UUID
A 16 byte binary value.
 
field_attributes
A description of the field. Valid values are:
Value
Description
KEY
Identifies this field as the primary key to be used for identifying records to be updated with pdm_load. This is used if the default primary key, id, is not specified. Must be specified if the field is the primary key in the table.
NOT_NULL
Indicates that the field must contain a value. Must be specified if the field is the primary key in the table. Optional if the field is not the primary key.
REF other_table_name
Indicates that the field references another table. Optional whether the field is the primary key or not.
S_KEY
Optionally identifies this field as the secondary key to be used for identifying records to be updated with pdm_load.
UNIQUE
Indicates that the values in the field must be unique. Must be specified if the field is the primary key in the table. Optional if the field is not the primary key.
Macros are synonyms that will be converted during configuration to the value the macro represents. You can use macros for either data types or attributes. If you wish to use macros, you must add in #include statement to include the file that defines the macro including the path name (usually relative to your schema file). The include statement must be defined prior to using the macro. Example of an include statement:
#include "../schema.mac"
The following are some of the macros defined in .mac files located in the $ NX_ROOT/site (UNIX) or
installation-directory
/site (Windows) directory.
Data Type
Equivalent
nn
NOT_NULL
uniq
UNIQUE NOT_NULL
ADDR_LINE
STRING 30
EMAILADDR
STRING 120
ENT_DESC
STRING 40
ENT_NAME
STRING 30
OSI_NAME
STRING 80
OSI_TYPE_STRING
STRING 60
USERID
STRING 85
PHONENUM
STRING 32
SYMBOL
STRING 12
HIER_SYM
STRING 60
LONG_SYM
STRING 30
COMMENT
STRING 1000
LONG_STR
STRING 500
LONG_DESC
STRING 240
BOOL
INTEGER
Examples
This TABLE statement in the database schema defines severities. The macro nn indicates that a value is required in the del field. The macro uniq indicates that values are required and must be unique:
#include :../schema.mac" TABLE Severity { id INTEGER uniq KEY; // key id del INTEGER nn; // 0=present,1=gone sym SYMBOL uniq S_KEY; // type symbol desc ENT_DESC; // non-OSI specified column }
This modified TABLE statement makes the Priority field on the Request Detail window required:
TABLE Call_Req {priority INTEGER NOT_NULL;}
This TABLE statement adds a resolution code field to the Call_Req table. The content of the field is numeric and references the Resolution_Code table. This reference allows users to double-click the Resolution Code field on the Request Detail window to display the values in the Resolution_Code table:
TABLE Call_Req {zres_code INTEGER REF Resolution_Code;}
TABLE_INFO Statement
This instructs your database management system how to store and index data in the logical tables. The extent to which these instructions are followed depends on the database management system. If no instructions are provided, the database management system follows its own storage and indexing instructions.
Syntax
TABLE_INFO table_name {
[STORAGE
storage_mtd Field
;] [INDEX
ndx_props field1
[
field2
...];] ...}
Arguments
  • TABLE_INFO
    Introduces the TABLE_INFO statement. Must be uppercase. The TABLE_INFO statement is optional, but if specified, you can have only one TABLE_INFO for each TABLE statement, and it must follow the TABLE statement.
  • table_name
    The name of the database table in the TABLE statement.
  • STORAGE
    storage_mtd
    Identifies the storage method. Valid values are listed as follows, but note that some database management systems ignore these values:
Value
Description
BTREE
Indicates to use the balanced tree storage method.
HASH
Indicates to use the hash table storage method. This is valid only if the field is the primary key.
HEAP
Indicates to use the heap storage method.
  • field
    Identifies the column that is to be stored according to the specified storage method (STORAGE
    storage_mtd
    ). Must be specified the same way as the name of the column in the TABLE statement.
  • INDEX
    ndx_props
    Identifies one or more properties for an index that consists of the fields specified. Valid values are:
Value
Description
SORT ASCENDING | DESCENDING
Indicates whether to sort the data in the fields in ascending or descending order. Data is sorted in ascending order by default; therefore, only SORT DESCENDING need be specified.
PRIMARY
Indicates to use this index as the default sort order for the table.
CLUSTER
Identifies this as a clustering index.
UNIQUE
Indicates that values in the index must be unique.
  • field1
    [
    field2 . . .
    ]
Identifies the column or columns that are to be indexed according to the specified index properties (INDEX
ndx_props
). Must be specified the same way as the name of the columns in the TABLE statement.
Examples
This TABLE_INFO statement instructs the database management system to use a hash table to store values in the id field in the Contact_Type table, and to sort the table in descending order according to the values in the sym field. It also indicates that values must be unique:
TABLE_INFO Contact_Type {
STORAGE HASH id; INDEX SORT DESCENDING PRIMARY UNIQUE sym;}
Mapping Statement
Defines the correspondence between the logical tables and columns in the CA SDM database schema and the physical tables and columns used by your database management system. This statement follows each TABLE statement in a.sch file. You must define it when you define a new table.
Syntax
p1 logical_table_name -> CURR_PROV physical_table_name [{logical_field -> physical_field ...] ;
Arguments
  • p1
    Introduces the mapping statement. Must be specified as p1.
  • logical_table_name
    The name of the database table in the TABLE statement, for example, zManufacturer.
  • CURR_PROV
    A required keyword.
  • physical_table_name
    The name of the table used by your database management system, for example, man. Short names improve performance and are required by some database management systems.
  • logical_field
    The name of the column in the CA SDM database schema, for example, desc. Must be the same as
    field
    in the TABLE statement. Omit this when the logical columns and physical columns have identical names. When omitted, the semicolon follows
    physical_table_name
    .
  • physical_field
The name of the column used by your database management system, for example, nx_desc. Omit this when the logical columns and physical columns have identical names. When omitted, the semicolon follows
physical_table_name
.
Examples
This example illustrates how TABLE, mapping (p1), and TABLE_INFO statements define a zManufacturer table:
TABLE zManufacturer { id INTEGER uniq KEY; // key id del INTEGER nn; // 0=present,1=gone sym HIER_SYM uniq S_KEY; // manufacturer name desc ENT_DESC; // manufacturer description } p1 zManufacturer -> CURR_PROV man // maps logical table "zManufacturer" { // to physical table "man" desc -> nx_desc; // maps logical column "desc" } // to physical column "nx_desc" TABLE_INFO zManufacturer {
STORAGE HASH id; INDEX SORT ASCENDING PRIMARY UNIQUE sym;}