網頁

2010年11月17日 星期三

Database Schema Files

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

Back to Contents

Database Schema Files

Summary:
See also: FormsProgramsVariablesfgldbsch

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
TypeDescription
1STRINGDatabase table name.
2STRINGColumn name.
3SMALLINTCoded column data type. If the column is NOT NULL, you must add 256 to the value.
4SMALLINTCoded data type length.
5SMALLINTOrdinal position of the column in the table.
6STRINGDefault value of the database column.
The value can be a simple numeric constant (1234.56) or a string delimited by single quotes ('abcdef').
7STRINGDefault 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 nameData type code (field #3)Data type length (field #4)
This is a SMALLINT value encoding the length or composite length of the type.
CHAR0Maximum number of characters.
SMALLINT1Fixed length of 2
INTEGER2Fixed length of 4
FLOAT3Fixed length of 8
SMALLFLOAT4Fixed length of 4
DECIMAL5The length is computed using the following formula:
length = ( precision * 256 ) + scale
SERIAL6Fixed length of 4
DATE7Fixed length of 4
MONEY8Same as DECIMAL
Unused9
DATETIME10For 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)
BYTE11Length of descriptor
TEXT12Length of descriptor
VARCHAR13If length is positive:
length = ( min_space * 256 ) + max_sizeIf length is negative:
length + 65536 = ( min_space * 256 ) + max_size
INTERVAL14Same as DATETIME
NCHAR15Same as CHAR
NVARCHAR16Same as VARCHAR
INT817Fixed length of 10 (sizeof int8 structure)
Will be converted to FGL BIGINT FGL type!
SERIAL818Fixed 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 type40
BIGINT53Fixed length of 8 (bytes)
BIGSERIAL54Fixed length of 8 (bytes)
VARCHAR2201Maximum number of characters
Will be converted to FGL VARCHAR type!
NVARCHAR2202Maximum 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:
PosTypeDescription
1STRINGDatabase table name. 
2STRINGColumn name.
3STRINGColumn property name.
4STRINGColumn property value.
The supported attribute definitions are:
Attribute NameDescription
ACTIONDefines the ACTION attribute.
Value must be an identifier.
AUTONEXTDefines the AUTONEXT attribute.
When this attribute is defined, value is YES.
AUTOSCALEDefines the AUTOSCALE attribute.
When this attribute is defined, value is YES.
CENTURYDefines the CENTURY attribute.
The value must be one of: RCF, or P.
COLORDefines the COLOR attribute.
The value is a color identifier (RED, GREEN, BLUE, ...)
COMMENTSDefines the COMMENTS attribute.
The value is a quoted string or Localized String (%"xxx").
DEFAULTDefines the DEFAULT attribute.
Number, quoted string or identifier (TODAY).
FORMATDefines the FORMAT attribute.
The value is a quoted string.
HEIGHTDefines the HEIGHT attribute.
The value is an integer followed by: { CHARACTERSCOLUMNSLINESPOINTS, or PIXELS }
IMAGEDefines the IMAGE attribute.
The value is a quoted string.
INCLUDEDefines an include list as the INCLUDE attribute.
Value must be a list: (value [,...]), where value can be a number, quoted string or identifier (TODAY).
INITIALIZERDefines the INITIALIZER attribute.
Value must be an identifier.
INVISIBLEDefines the INVISIBLE attribute.
When this attribute is defined, value is YES.
ITEMSDefines the VALUEUNCHECKED attribute.
The value must be a list: (item [,...]), where item can be a number, a quoted string or (value,"label").
ITEMTYPEDefines 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
JUSTIFYDefines the JUSTIFY attribute.
The value must be one of: LEFTCENTER or RIGHT.
ORIENTATIONDefines the ORIENTATION attribute.
The value must be one of: VERTICAL or HORIZONTAL.
PICTUREDefines the PICTURE attribute.
The value is a quoted string.
SAMPLEDefines the SAMPLE attribute.
The value is a quoted string.
SCROLLDefines the SCROLL attribute.
When this attribute is defined, value is YES.
SCROLLBARSDefines the SCROLLBARS attribute.
The value must be one of: XY or BOTH.
SHIFTCorresponds to the UPSHIFT and DOWNSHIFT attributes.
Values can be UP or DOWN.
SIZEPOLICYDefines the SIZEPOLICY attribute.
The value must be one of: INITIALDYNAMIC or FIXED.
STEPDefines the STEP attribute.
The value must be an integer.
STRECHDefines the STRETCH attribute.
The value must be one of: XY or BOTH.
STYLEDefines the STYLE attribute.
The value is a quoted string.
TAGDefines the TAG attribute.
The value is a quoted string.
TEXTDefines the TEXT attribute.
The value is a quoted string or Localized String (%"xxx").
TITLEDefines the TITLE attribute.
The value is a quoted string or Localized String (%"xxx").
VALUEMINDefines the VALUEMIN attribute.
The value must be an integer.
VALUEMAXDefines the VALUEMAX attribute.
The value must be an integer.
VALUECHECKEDDefines the VALUECHECKED attribute.
The value must be an number or a quoted string.
VALUEUNCHECKEDDefines the VALUEUNCHECKED attribute.
The value must be an number or a quoted string.
VERIFYDefines the VERIFY attribute.
When this attribute is defined, value is YES.
WANTTABSDefines the WANTTABS attribute.
When this attribute is defined, value is YES.
WANTNORETURNSDefines the WANTNORETURNS attribute.
When this attribute is defined, value is YES.
WIDTHDefines the WIDTH attribute.
The value is an integer followed by: { CHARACTERSCOLUMNSLINESPOINTS, 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:
PosTypeDescription
1STRINGDatabase table name. 
2STRINGColumn name.
3SMALLINTOrdinal number of the attribute record.
4STRINGCOLOR attribute (coded).
5CHAR(1)INVERSE attribute (y/n).
6CHAR(1)UNDERLINE attribute (y/n).
7CHAR(1)BLINK attribute (y/n).
8CHAR(1)LEFT attribute (y/n).
9STRINGFORMAT attribute.
10STRINGCondition.
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

沒有留言:

張貼留言