Back to Contents

Database Schema Files

Summary:

• What are Database Schema Files?
• Database Schema Extractor
  • Specifying the database source
  • Specifying the database driver
  • Passing database user login and password
  • Data type conversion control
  • Specifying the table owner
  • Force extraction of system tables
  • Specifying the output file name
  • Extracting column information for a single table
  • Controlling the character case
  • Using the verbose mode
  • IBM Informix synonyms
  • IBM Informix shadow columns
  • Running schema extractor in old mode
• Schema Files
  • Column Definition File (.sch)
    • Informix DISTINCT types
  • 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

The fgldbsch tool extracts the schema description for any database supported by Genero. 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.

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

Specifying the database source

You must run fgldbsch with the -db dbname option to identify the database source 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.

fgldbsch -db test1

Specifying the database driver

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

fgldbsch -db test1 -dv dbmoraB2x

Passing database user login and password

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

fgldbsch -db test1 -un scott -up fourjs

Data type conversion control

The fglcomp and fglform compilers expect BDL data types in the schema file. While most data types correspond to Informix SQL data types, non-Informix (and even Informix) databases can have specific data types that do not map to a BDL type. Therefore, data types in the schema file 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 represents a line in the conversion table of the corresponding source database. 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. 

Note that 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.

fgldbsch -ct
...
------------------------------------------------------------------
Informix        Informix A           Informix B
------------------------------------------------------------------
1 BOOLEAN       BOOLEAN (t=45)       CHAR(1)
2 INT8          INT8                 DECIMAL(19,0)
3 SERIAL8       SERIAL8              DECIMAL(19,0)
4 LVARCHAR(m)   VARCHAR2(m)          VARCHAR2(m)
5 BIGINT        BIGINT               DECIMAL(19,0)
6 BIGSERIAL     BIGSERIAL            DECIMAL(19,0)
------------------------------------------------------------------
(ns) = Not supported in 4gl.
...

fgldbsch -db test1 -cv BAAABB
                       123456

In the example above, the -cv option instructs fgldbsch to use the types of the "Informix A" column for all original column types except for BOOLEAN, BIGINT and BIGSERIAL, which must be converted to a VARCHAR2(m), the Genero VARCHAR without the 255 bytes limit.

Note that the IBM Informix LVARCHAR(m) type can be converted by default to a VARCHAR2(m) pseudo type (code 201), which will be identified as a VARCHAR(m) by compilers, without the 255 bytes limitation of the original Informix VARCHAR type.

Not all native data types can be converted to Genero BDL types. For example, user-defined types or spatial types are not supported by the language. When a table column with such unsupported data type is found, fgldbsch stops and displays an error to bring the problem to your eyes. You can use the -ie option to have fgldbsch ignore the database tables having columns with unsupported types. When this option is used, none of the table columns definition will be written to the schema file.

Specifying the table owner

With some databases, the owner of tables is mandatory to extract a schema, otherwise you could get multiple definitions of the same table in the .sch schema file if tables with the same name exist in different database user schemas. 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 database login name passed with the -un user option. This is usually the case with SQL Server and Sybase, where the owner of tables is "dbo".

fgldbsch -db test1 -un scott -up fourjs -ow dbo

Force extraction of system tables

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

fgldbsch -db test1 -st

Specifying the output file name

By default, the generated schema files get the name of the database source specified with the -db option. If needed, you can force the name of the schema file with the -of name option. Specify the output file name without the .sch extension. Note that this name will also be used to extract syscolval and syscolatt information.

fgldbsch -db test1 -of myschema

Extracting column information for a single table

In some cases, you may just want to extract schema file of new created tables. You can achieve this by using the -tn tabname option, to extract schema information of a specific table.

fgldbsch -db test1 -tn customers

Controlling the character case

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.

fgldbsch -db test1 -cc

Using the verbose mode

Use the -v option to get verbose output from fgldbsch:

fgldbsch -db test1 -v

IBM Informix synonym tables

When using an IBM Informix database, fgldbsch extracts synonyms. By default, only PUBLIC synonyms are extracted to avoid duplicates in the .sch file when the same name is used by several synonyms by different owners. If you want to extract PRIVATE synonyms, you must use the -ow option to specify the owner of the tables and synonyms.

IBM Informix shadow columns

Starting with IBM Informix IDS version 11.50.xC1, you can create shadow columns on tables by using DDL options such as ADD VERCOLS. These columns are visible in the system catalog tables and would be listed in the column descriptions of the .sch schema file. However, as shadow columns are not part of the SELECT * list, it is not expected to get these columns in the schema file. By default, the fgldbsch tool will not extract shadow columns from an IBM Informix database. You can use the -sc option to force the extraction of shadow columns:

fgldbsch -db test1 -sc

Running schema extractor in old mode

The fgldbsch program can be executed in old mode by specifying the -om option as first parameter, followed by the database source. You can pass the -c and -r options after the database source:

fgldbsch -om test1 -c -r

You typically use this mode for IBM Informix databases only.

The -c option is equivalent to -cv BBBBBBBBB in the default mode: Columns defined with an SQL type that is not a native Genero type will be converted to an equivalent type (see -cv and -ct options for more details). 

If the -r option is specified, the schema extractor will ignore columns defined with unsupported SQL types. Unsupported types have no equivalent Genero type to store and handle the value, such as BLOB or CLOB for example. Understand that unlike the -ie option, which skips the whole table definition, -r will exclude table columns with unsupported types, but the other columns defined with supported types will be written to the .sch file. Thus, a record declared with DEFINE RECORD LIKE table.* (from a partial schema definition of a table) cannot be used in a SELECT * INTO record.* statement, because the number of columns in the database table is different from the record definition.

Note also that when using the old mode, fgldbsch will extract system catalog tables (informix.sys*) for IBM Informix databases.

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.

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 and their corresponding type code that can be represented in the .sch schema file:

Informix DISTINCT types

Informix IDS version 9.x and higher allow you to define DISTINCT types from a base types with the CREATE DISTINCT TYPE instruction. In the syscolumns table, Informix identifies distinct types in the coltype column by adding the 0x0800 bit (2048) to the base type code. For example, a distinct type defined with the VARCHAR built-in type (i.e. code 13) will be identified with the code 2061 (13 + 2048). Informix sets additional bits when the distinct type is based on the LVARCHAR or BOOLEAN opaque types: If the base type is an LVARCHAR, the type code used in coltype gets the 0x2000 bit set (8192) and when the base type is BOOLEAN, the type code gets the 0x4000 bit (16384).

When extracting a schema from an Informix database defining columns with DISTINCT types, the schema extractor will keep the original type code of the distinct type in the .sch file for columns using distinct types based on built-in types (with the 0x0800 bit set). Regarding the exception of opaque types, BOOLEAN-based distinct types get the code 45 ( + 256 if NOT NULL), and LVARCHAR-based distinct types are mapped to the code 201 (+ 256 if NOT NULL) if the -cv option enables conversion from LVARCHAR to VARCHAR2.

The fglcomp and fglform compilers understand the distinct type code bit 0x0800, so you can define program variables with a DEFINE LIKE instruction based on a column that was created with a distinct Informix type.

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 .att file: