Back to Contents

Database Schema Files

Summary:

• What are Database Schema Files?
• Database Schema Extractor
• Schema Files
  • Column Definition File (.sch)
  • Column Validation File (.val)
  • Column Video Attributes File (.att)

See also: Forms, Programs, Variables, fgldbsch

Definition of Database Schema Files

Database Schema Files hold the definition of the database tables and columns. The schema files contain the column data types, validation rules, form item types, and display attributes for columns.

The schema files are typically used to centralize column data types to define program variables, as well as display attributes which are normally specified in the form specification file.

The database schema files are generated with the fgldbsch tool from the system tables of an existing database.

In program sources or form specification files, you must specify the database schema file with the SCHEMA instruction. The FGLDBPATH environment variable can be used to define a list of directories where the compiler can find database schema files.

Program variables can be defined with the LIKE keyword to get the data type defined in the schema files:

01 SCHEMA stores
02 MAIN
03   DEFINE custrec RECORD LIKE customer.*
04   DEFINE name LIKE customer.cust_name
05   ...
06 END MAIN

Form fields defined with the FIELD item type can get the form item type from the schema files:

01 SCHEMA stores
02 LAYOUT
03 GRID
04 {
05   [f001         ]
06 }
07 TABLES
08   customer
09 END
10 ATTRIBUTES
11 FIELD f001 = customer.cust_name;
12 END

Note: For handling uppercase characters in the database name you must quote the name: SCHEMA "myDatabase"

Database Schema Extractor

See also: fgldbsch

The fgldbsch tool extracts the schema description for any database supported by the product. Schema information is extracted from the database specific system tables. The database type is automatically detected after connection; you do not have to specify any database server type.

fgldbsch -db test1 -un scott -up fourjs -v -ie

The database system must be available and the database client environment must be set properly in order to generate the schema files.

You must run fgldbsch with the -db dbname option to identify the database to which to connect. The dbname and related options could be present in the FGLPROFILE file. See Indirect database specification method  in Database Connections. Otherwise, related options have to be provided with the fgldbsch command.

If the operating system user is not the database user, you can provide a database login and password with the -un and -up options.

The database driver can be specified with the -dv dbdriver option, if the default driver is not appropriate.

The BDL compiler expects FGL data types in the schema file. While most data types correspond to Informix SQL data types, non-Informix databases can have different data types. Therefore, data types are generated from the system catalog tables according to some conversion rules. You can control the conversion method with the -cv option. Each character position of the string passed by this option corresponds to a line in the conversion table. You must give a conversion code for each data type (for example: -cv AABAAAB). Run the tool with the -ct option to see the conversion tables. When using X as conversion code, the columns using the corresponding data types will be ignored and not written to the .sch file. This is particularly useful in the case of auto-generated columns like SQL Server's uniqueidentifier data type, when using a DEFAULT NEWID() clause.

With some databases, the owner of tables is mandatory to extract a schema, otherwise you would get multiple definitions of the same table in the .sch schema file. To prevent such mistakes, you can specify the schema owner with the -ow owner option. If this option is not used, fgldbsch will use the login name passed with the -un user option. 

By default fgldbsch does not generate system table definitions. Use the -st option to extract schema information of system tables.

Use the -tn tabname option to extract schema information of a specific table. You may use the -of name option to generate files with a different name than the default name (the name of the database specified with the -db option).

By default, table and column names are converted to lower case letters to enforce compatibility with Informix. You can force lower case, upper case or case-sensitive generation by using the -cl, -cu or -cc options.

Schema Files

Column Definition File (.sch)

The .sch file contains the data types of table columns.

Example:

01 customer^customer_num^258^4^1
02 customer^customer_name^256^50^2
03 customer^customer_address^0^100^3
04 order^order_num^258^4^1
05 order^order_custnum^258^4^2
06 order^order_date^263^4^3
07 order^order_total^261^1538^4

Description:

The data type of program variables or form fields used to hold data of a given database column must match the data type used in the database. BDL simplifies the definition of these elements by centralizing the information in external .sch files, which contain column data types.

In form files, you can directly specify the table and column name in the field definition in the ATTRIBUTES section of forms.

In programs, you can define variables with the data type of a database column by using the LIKE keyword.

Warnings:

1. As column data types are extracted from the database system tables, you may get different results with different database servers. For example, Informix provides the DATE data type to store simple dates in year, month, and day format (= BDL DATE), while Oracle stores DATEs as year to second ( = BDL DATETIME YEAR TO SECOND).

The following table describes the fields you will find in a row of the .sch file:

Next table shows the data types that can be represented in the .sch schema file:

Column Validation File (.val)

The .val file holds functional and display attributes of columns.

Example:

01 customer^customer_name^STYLE^"important"^
02 customer^customer_name^SHIFT^UP^
03 customer^customer_name^COMMENTS^"Name of the customer"^
04 order^order_date^DEFAULT^TODAY^
05 order^order_date^COMMENTS^"Creation date of the order"^

Description:

The .val file holds default attributes and validation rules for database columns.

In form files, the attributes are taken from the .val file as defaults if the corresponding attribute is not explicitly specified in the field definition of the ATTRIBUTES section.

In programs, you can validate variable values in accordance with the INCLUDE attribute by using the VALIDATE instruction.

The .val file can be generated by fgldbsch from the Informix-specific syscolval table, or can be edited by an external column attributes editor.

The following table describes the structure of the .val file:

The supported attribute definitions are:

Column Video Attributes File (.att)

The .att file contains the default video attributes of columns.

This file is generated by fgldbsch from the Informix-specific syscolatt table.

The following table describes the structure of the .val file:

Warning: This feature is supported for compatibility with Informix 4GL only.