Understanding database schemas

Database schemas hold the definition of the database tables and columns.

Purpose of database schema files

In program sources or form specification files, specify the database schema file with the SCHEMA instruction.

When the database schema is defined, it is possible to declare program variables and form fields by referencing the database table or column name.

The program variables and form fields will get the type of the database column, as defined in the schema file.

What contain database schema files?

The schema files contain the column data types (.sch file), data validation rules (.val file), and console/tty display attributes (.att file).

Note: The .val and .att files are supported for backward compatibility and are not recommended in new developments.

For more details about schema file content, see Structure of database schema files.

How to declare program variables from column definitions?

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

SCHEMA stores
MAIN
   DEFINE rec_cust RECORD LIKE customer.*
   DEFINE name LIKE customer.cust_name
   ...
END MAIN

Multiple database schemas can be used by specifying the schema prefix in the LIKE clause:

DEFINE rec_cust RECORD LIKE orders:customer.*
DEFINE rec_item RECORD LIKE stock:item.*

For more details, see the DEFINE instruction.

Locating database schema files

The FGLDBPATH environment variable can be used to define a list of directories where the compiler can find database schema files.

For more details, see FGLDBPATH.

When are database column types used to define program variable?

The data types, display attributes, and validation rules are taken from the database schema files during compilation.

Important: Make sure that the schema files of the development database correspond to the production database, otherwise the elements defined in the compiled version of your modules and forms will not match the table structures of the production database.

What unit for CHAR/VARCHAR types in .sch file?

When extracting a database schema with fgldbsch, the size of CHAR/VARCHAR columns is read from the database system tables.

When extracting CHAR/VARCHAR column sizes, fgldbsch writes a number of characters (not bytes) to the .sch file.

When compiling sources using DEFINE ... LIKE, the size of CHAR/VARCHAR columns in the .sch file will be interpreted as a number of bytes or characters following the FGL_LENGTH_SEMANTICS environment variable.

For more details, see also Extracting database schemas.

Optimized compilation with schema files

With large projects, the database schema file can contain thousands of column definitions.

To improve compilation time, the fglcomp compiler will automatically produce a .42d index file from the .sch schema file, in the same directory as the .sch file.

When the .sch file changes, the .42d index file is re-generated.

The .42d file can be safely removed, to cleanup source directories.

Extracting database schemas with fgldbsch

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

Note: It is strongly recommended that you re-generate the schema files when upgrading to a new compiler version. Bug fixes and new data type support can require schema file changes. If the schema file holds data type codes that are unknown to the current version, the compilers will raise the error -6634.

The fgldbsch must connect to the database server, with a db user allowed to query the database system tables (for example, INFORMATION_SCHEMA in an Oracle® MySQL database).

Note: For some types of databases, the table owner is mandatory to extract schema information. If you do not specify the -ow option in the comment line, fgldbsch will take the -un user name as default. If you do not use the -un/-up options because you are using indirect database connection with FGLPROFILE settings to identify the database user, or if the database user is authenticated by the operating system, the fgldbsch tool will try to identify the current database user after connection and use this name as table owner to extract the schema.

For more details, see Database schema extractor options.