Back to Contents
See also: Forms, Programs, Variables, fgldbsch
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:
The
You must run
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,
By default
Warning: The
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.
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.
Next table shows the data types that can be represented in the .sch schema file:
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:
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.
Database Schema Files
Summary: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:
Form fields defined with the FIELD item type can get the form item type from the schema files:01
SCHEMA stores02
MAIN03
DEFINE custrec RECORD LIKE customer.*04
DEFINE name LIKE customer.cust_name05
...06
END MAIN
Note: For handling uppercase characters in the database name you must quote the name: SCHEMA "myDatabase"01
SCHEMA stores02
LAYOUT03
GRID04
{05
[f001 ]06
}07
TABLES08
customer09
END10
ATTRIBUTES11
FIELD f001 = customer.cust_name;12
END
Database Schema Extractor
See also: fgldbschThe
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 -ieThe 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^102
customer^customer_name^256^50^203
customer^customer_address^0^100^304
order^order_num^258^4^105
order^order_custnum^258^4^206
order^order_date^263^4^307
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:
- 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).
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). |
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:
| |||||||||||
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. |
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. |
Video
http://www.4js.com/en/training/videos/genero/viewvideo/30/3-database-interaction/creating-a-schema-file-sch
沒有留言:
張貼留言