http://www.4js.com/online_documentation/fjs-fgl-2.20.02-manual-html/User/DatabaseSchema.html

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.
Warning: The data types, display attributes, and validation rules are taken from the Database Schema Files during compilation. 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.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.
Warning: The fgldbsch tool in BDL v1.3x provides the -ns option to generate without the database system tables. This option is no longer supported in the fgldbsch tool in BDL v2.xxand is replaced by the -st option to generate with the database 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.
Warning: When using an 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.

---

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:

Pos	Type	Description
1	STRING	Database table name.
2	STRING	Column name.
3	SMALLINT	Coded column data type. If the column is NOT NULL, you must add 256 to the value.
4	SMALLINT	Coded data type length.
5	SMALLINT	Ordinal position of the column in the table.
6	STRING	Default value of the database column. The value can be a simple numeric constant (1234.56) or a string delimited by single quotes ('abcdef').
7	STRING	Default form item type. The value can be one of the form item types (Edit, ButtonEdit, ComboBox, and so on).

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

Data type name	Data type code (field #3)	Data type length (field #4) This is a SMALLINT value encoding the length or composite length of the type.
CHAR	0	Maximum number of characters.
SMALLINT	1	Fixed length of 2
INTEGER	2	Fixed length of 4
FLOAT	3	Fixed length of 8
SMALLFLOAT	4	Fixed length of 4
DECIMAL	5	The length is computed using the following formula: length = ( precision * 256 ) + scale
SERIAL	6	Fixed length of 4
DATE	7	Fixed length of 4
MONEY	8	Same as DECIMAL
Unused	9
DATETIME	10	For DATETIME and INTERVAL types, the length is determined using the next formula: length = ( digits * 256 ) + ( qual1 * 16 ) + qual2 where digits is the total number of digits used when displaying the date-time value. For example, a DATETIME YEAR TO MINUTE (YYYY-MM-DD hh:mm) uses 12 digits. The qual1 and qual2 elements identify date-time qualifiers according to the following list: 0 = YEAR 2 = MONTH 4 = DAY 6 = HOUR 8 = MINUTE 10 = SECOND 11 = FRACTION(1) 12 = FRACTION(2) 13 = FRACTION(3) 14 = FRACTION(4) 15 = FRACTION(5)
BYTE	11	Length of descriptor
TEXT	12	Length of descriptor
VARCHAR	13	If length is positive: length = ( min_space * 256 ) + max_sizeIf length is negative: length + 65536 = ( min_space * 256 ) + max_size
INTERVAL	14	Same as DATETIME
NCHAR	15	Same as CHAR
NVARCHAR	16	Same as VARCHAR
INT8	17	Fixed length of 10 (sizeof int8 structure) Will be converted to FGL BIGINT FGL type!
SERIAL8	18	Fixed length of 10 (sizeof int8 structure) Will be converted to FGL BIGINT FGL type!
SET (Unused)	19
MULTISET (Unused)	20
LIST (Unused)	21
Unnamed ROW (Unused)	22
Variable-length opaque type	40
BIGINT	53	Fixed length of 8 (bytes)
BIGSERIAL	54	Fixed length of 8 (bytes)
VARCHAR2	201	Maximum number of characters Will be converted to FGL VARCHAR type!
NVARCHAR2	202	Maximum number of characters Will be converted to FGL VARCHAR type!
Named ROW (Unused)	4118

---

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:

Pos	Type	Description
1	STRING	Database table name.
2	STRING	Column name.
3	STRING	Column property name.
4	STRING	Column property value.

The supported attribute definitions are:

Attribute Name	Description
ACTION	Defines the ACTION attribute. Value must be an identifier.
AUTONEXT	Defines the AUTONEXT attribute. When this attribute is defined, value is YES.
AUTOSCALE	Defines the AUTOSCALE attribute. When this attribute is defined, value is YES.
CENTURY	Defines the CENTURY attribute. The value must be one of: R, C, F, or P.
COLOR	Defines the COLOR attribute. The value is a color identifier (RED, GREEN, BLUE, ...)
COMMENTS	Defines the COMMENTS attribute. The value is a quoted string or Localized String (%"xxx").
DEFAULT	Defines the DEFAULT attribute. Number, quoted string or identifier (TODAY).
FORMAT	Defines the FORMAT attribute. The value is a quoted string.
HEIGHT	Defines the HEIGHT attribute. The value is an integer followed by: { CHARACTERS, COLUMNS, LINES, POINTS, or PIXELS }
IMAGE	Defines the IMAGE attribute. The value is a quoted string.
INCLUDE	Defines an include list as the INCLUDE attribute. Value must be a list: (value [,...]), where value can be a number, quoted string or identifier (TODAY).
INITIALIZER	Defines the INITIALIZER attribute. Value must be an identifier.
INVISIBLE	Defines the INVISIBLE attribute. When this attribute is defined, value is YES.
ITEMS	Defines the VALUEUNCHECKED attribute. The value must be a list: (item [,...]), where item can be a number, a quoted string or (value,"label").
ITEMTYPE	Defines the Form Item Type to be used when the column is used as FIELD in forms. Value must be an identifier defining the item type (case sensitive!): Edit, ButtonEdit, Label, Image, DateEdit, TextEdit, ComboBox, RadioGroup, CheckBox, Slider, SpinEdit, TimeEdit, ProgressBar
JUSTIFY	Defines the JUSTIFY attribute. The value must be one of: LEFT, CENTER or RIGHT.
ORIENTATION	Defines the ORIENTATION attribute. The value must be one of: VERTICAL or HORIZONTAL.
PICTURE	Defines the PICTURE attribute. The value is a quoted string.
SAMPLE	Defines the SAMPLE attribute. The value is a quoted string.
SCROLL	Defines the SCROLL attribute. When this attribute is defined, value is YES.
SCROLLBARS	Defines the SCROLLBARS attribute. The value must be one of: X, Y or BOTH.
SHIFT	Corresponds to the UPSHIFT and DOWNSHIFT attributes. Values can be UP or DOWN.
SIZEPOLICY	Defines the SIZEPOLICY attribute. The value must be one of: INITIAL, DYNAMIC or FIXED.
STEP	Defines the STEP attribute. The value must be an integer.
STRECH	Defines the STRETCH attribute. The value must be one of: X, Y or BOTH.
STYLE	Defines the STYLE attribute. The value is a quoted string.
TAG	Defines the TAG attribute. The value is a quoted string.
TEXT	Defines the TEXT attribute. The value is a quoted string or Localized String (%"xxx").
TITLE	Defines the TITLE attribute. The value is a quoted string or Localized String (%"xxx").
VALUEMIN	Defines the VALUEMIN attribute. The value must be an integer.
VALUEMAX	Defines the VALUEMAX attribute. The value must be an integer.
VALUECHECKED	Defines the VALUECHECKED attribute. The value must be an number or a quoted string.
VALUEUNCHECKED	Defines the VALUEUNCHECKED attribute. The value must be an number or a quoted string.
VERIFY	Defines the VERIFY attribute. When this attribute is defined, value is YES.
WANTTABS	Defines the WANTTABS attribute. When this attribute is defined, value is YES.
WANTNORETURNS	Defines the WANTNORETURNS attribute. When this attribute is defined, value is YES.
WIDTH	Defines the WIDTH attribute. The value is an integer followed by: { CHARACTERS, COLUMNS, LINES, POINTS, or PIXELS }

---

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:

Pos	Type	Description
1	STRING	Database table name.
2	STRING	Column name.
3	SMALLINT	Ordinal number of the attribute record.
4	STRING	COLOR attribute (coded).
5	CHAR(1)	INVERSE attribute (y/n).
6	CHAR(1)	UNDERLINE attribute (y/n).
7	CHAR(1)	BLINK attribute (y/n).
8	CHAR(1)	LEFT attribute (y/n).
9	STRING	FORMAT attribute.
10	STRING	Condition.

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

Video
http://www.4js.com/en/training/videos/genero/viewvideo/30/3-database-interaction/creating-a-schema-file-sch