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?
For more details about schema file content, see Structure of database schema files.
How to declare program variables from column definitions?
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
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.
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.
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).
-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.