FWD

Overview¶

• Overview
  • Database Schema Elements
  • Temporary Table Elements
  • Name Conversion
  • Data Type Conversion

The following two sections can be used as a guide to understand exactly which schema elements are supported for conversion and what output is created as a result. These are just summaries. To see the details of each supported element's converted results, please refer to the chapters on Data Definition Language (DDL), Hibernate Mappings, Data Model Objects (DMOs) and DMO Index.

Database Schema Elements¶

All schema conversion results for permanent tables will be found in DDL, Hibernate Mapping files, DMOs (interfaces and implementations) and the DMO Index. The only output that is not saved into the file system is the DDL for creating indexes which is dynamically generated and applied during the data import process.

The following documents each portion of the syntax in the .df file. Only features documented to be honored by the parser are listed here. Any features that cannot be properly read by the parser are by nature not supported. See the chapter entitled Schema Loader in Part 1 of this book for details. Any features listed as not supported in the table are silently ignored when present in the source code.

.df Schema Syntax	Category	Purpose	Supported	FWD Result
ABBREVIATED	Index	This is an optional keyword that can appear in an INDEX-FIELD clause. Its purpose is unknown.	No
(ADD | UPDATE) DATABASE string	Database	This is the header of the .df file. It suggests that a new database schema is being created or that the .df file is to be used to edit an existing schema.	Yes	This is essentially ignored, as it has no useful information. FWD always assumes that the .df file contains a complete and standalone database schema definition. The string would normally be expected to be a logical database name. Instead, in practice it seems to always be set to "?".
ADD FIELD field_name OF table_name	Field	The adds a new field of the given name to the specified table.	Yes	The name is translated to replace invalid characters and yield a version that is valid for use as a relational column name (in SQL) as well as a Java DMO instance variable name. The DMO variable name also forms the basis for the getter and setter method names of the Java DMO. The name can be overridden using conversion hints. The resulting SQL column definition will appear in the table DDL. Both the Java variable name and the relational column name will be found in the Hibernate mapping file.
ADD INDEX index_name ON table_name	Index	Creates a new index for the given table which will be named as index_name.	Yes	The data import process uses the index definitions in the P2O document to create DDL to add the proper indexes. There is also a separate tool to generate the index DDL without running the data import process. The index_name will be converted to a compatible SQL name before the DDL is generated. The index name will start with idx__. The reason the import process is tightly integrated is that import is a bulk load process. Bulk loading takes much longer if index maintenance must be done for every INSERT. Creating the indexes afterwards is much faster. Note that the unique indexes must still be added first so that no relational constraints are violated during import. Some index behavior can only be implemented inside the FWD runtime (for example, how unique indexes cause flushing of newly created records). Thus, some index data is stored in the DMO index.
ADD SEQUENCE string	Sequence	Defines a new sequence in the database.	Yes	As the sequence support in database is different than 4GL, individual issues are fixed inside the FWD runtime with directory persistence help.
ADD TABLE symbolic_name (TYPE symbol)?	Table	Defines that a table needs to be added to the database and specifies the name for the table.	Yes	The name is translated to replace invalid characters and yield a version that is valid for use as a relational table name (in SQL) as well as a Java DMO class name. The name can be overridden using conversion hints. The Java name will be found in the DMO index. Both the Java class name and the relational table name will be found in the Hibernate mapping file. The Java name will form the basis of both the interface and the implementation files for the DMO. The primary structural DDL for the database will have DROP TABLE and CREATE table statements that are associated with this table being added to the relational schema. The schema parser will allow optional TYPE symbol text to appear at the end of the line, but the purpose of this is unknown. It is not normally found and FWD ignores this text.
AREA string	Table or Index	Specifies the storage location for the resource.	No
AS data_type	Field	Specifies the data type of the field.	Yes	Defines the data type of the field. See the section below on Data Type Conversion for details on the types and the associated mappings that are supported. The type will affect the DDL, the generated DMOs and the Hibernate mappings for this field.
CAN-CREATE string	Table	Sets the list of users that are allowed to create new records in the table.	No
CAN-DELETE string	Table	Sets the list of users that are allowed to delete records from the table.	No
CAN-DUMP string	Table	Sets the list of users that are allowed to dump all records from the table.	No
CAN-LOAD string	Table	Sets the list of users that are allowed to load all records from the table.	No
CAN-READ string	Table	Sets the list of users that are allowed to read records from the table.	No	May also be usable in a field definition, but that is undocumented.
CAN-WRITE string	Table	Sets the list of users that are allowed to edit records in the table.	No	May also be usable in a field definition, but that is undocumented.
CASE-SENSITIVE	Field	If specified, then all searching or comparisons with this field will be done case-sensitively.	Yes	By default, fields are NOT CASE-SENSITIVE. If CASE-SENSITIVE is NOT specified, then WHERE clause processing and query substitution parameters are modified to implement case-insensitive comparisons. In particular, character fields in WHERE clauses are wrapped like this (data is the field name): upper(data). character expressions in substitution parameters will be wrapped like this: toUpperCase(expression). String literals are forced to uppercase if needed at conversion time and emitted in the proper case. The index DDL will have index definitions with case-insensitive character columns wrapped in upper(rtrim(char_col, ' \t\r\n')) and case-sensitive columns are wrapped in rtrim(char_col, ' \t\r\n'). These changes also impact sorting but the modifications to the ORDER BY are handled transparently at runtime by the FWD persistence layer. Case-insensitive character columns listed in the ORDER BY are dynamically wrapped in upper(rtrim(char_col, ' \t\r\n')) and case-sensitive columns are wrapped in rtrim(char_col, ' \t\r\n'). The trimming is required regardless, but it is critical for the runtime to match the same column "expression" as is used in the index DDL. This processing is always present when comparing or sorting character fields except in when CASE-SENSITIVE is specified. When specified, the upper() wrapper is eliminated in WHERE clauses, toUpperCase() is eliminated from substitution parameters, the upper() is removed from ORDER BY and index definitions (index DDL). For permanent tables, the only output that is generated by a case-sensitive field is that the DMO index will have a case-sensitive element added as a child of the class element. For example: <class interface="MyTable"> <case-sensitive name="myField"/> </class> The absence of the case-sensitive element with a name matching a given column implies that the column is not case-sensitive.
COLUMN-LABEL label	Field	Specifies the string literal to be used as a column label for any widget created from this field in a user interface frame.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
CYCLE-ON-LIMIT logical_literal	Sequence	Specifies if the sequence should wrap back to the minimum value when the maximum value is passed.	Yes	The logical literal is not expected to be enclosed in double quotes. The value should be yes or no. There is no H2 database server support, for this dialect support is implemented in FWD runtime.
DECIMALS num	Field	Specifies the number of digits to the right of the decimal point which will exist for this decimal type field.	Partial	The generated DDL will set numeric columns to a precision of 50 and a scale to the value specified as num or 10 if this property is not included in the .df. The precision setting is the total number of significant digits. The scale setting is the number of digits to the right of the decimal point. The Hibernate mapping file property element will have a scale=”num” attribute that matches the numeric literal specified as num. The DMO implementation class constructor will pass num as a second parameter to the decimal constructor when it instantiates the backing member for this field. In addition, further research is needed to determine if WHERE clauses will process properly in all cases. The issue is whether or not user-defined functions will properly receive and return intermediate decimal values that have the correct scale based on the backing column's scale setting.
DESCRIPTION string	Field, Table or Index	Provides some documentation about the resource.	Partial	The text is copied into the Javadoc for the DMOs. Currently, this text is discarded for indexes.
DUMP-NAME	Table	Specifies the base name for the .d export file that is associated with this table.	Yes	The data import process uses this to match the .d files with the associated table. Otherwise this is not output in any code or configuration that is part of the application.
EXTENT num	Field	If greater than 0, indicates the field holds an array of values of the field's data type, rather than a single value of that type. The value of the extent is the size of the array.	Yes	Array values are stored in a secondary table. The secondary table contains a foreign key column related to the primary key of the table containing the array column. An index column in the secondary table maintains the appropriate indexing of the array values. If multiple columns in the same table use the same extent value, they are all be stored in the same, secondary table, each in a separate column. Otherwise, a separate, secondary table exists for each occurrence of a new extent value within the same Progress table. This extra table and the relations with the original table are hidden from the user of the DMO by Hibernate. Hibernate knows how to properly map the extent data to and from the extra table. This is defined in the Hibernate mapping document as a list of composite elements. The table DDL will have SQL to define the additional table and the corresponding foreign key relationship. The number of rows in a secondary table equals the number of rows in the primary table times the size of the extent which the secondary table manages. For example, a table containing 1000 rows which manages four fields, each with an extent of 12 will be related to a secondary table containing (1000 * 12), or 12,000 rows. The data model object which maps to a table containing array columns provides access methods to make this backing implementation transparent. The interface class will have method signatures that include a zero-based index parameter. The implementation class will have a List member that is used to contain the individual elements. It will also have an inner class CompositeX where X is the number of elements being stored. Finally, the implementation class will have code to provide the actual get and set operations in a set-oriented manner.
FIELD-TRIGGER type_string (OVERRIDE | NO-OVERRIDE) PROC proc_name CRC logical_string	Field	Defines a procedure to use on the specific field when the given event type is raised.	No
FORMAT expression	Field	Specifies the visual formatting of the field when used as a basis for a widget in a user interface frame.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
FROZEN	Table	The table definition is read-only in the Progress 4GL data dictionary.	No	Only seen in metadata schemas. This may not be a valid user-defined table attribute.
HELP	Field	Specifies the help text that should appear in the status line of the user interface when a widget based on this field obtains focus.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
HIDDEN	Table	Unknown.	No	Only seen in metadata schemas. This may not be a valid user-defined table attribute.
INCREMENT integer_number	Sequence	Specifies the value to be added to the current value each time the sequence needs to be incremented.	Yes	An integer number not encoded inside double quotes is expected as value.
INDEX-FIELD field_name [ASCENDING | DESCENDING]	Index	Adds the given field to the index and optionally specifies the direction of the sorting of that field.	Partial	Adds a column to the index definition in the generated DDL (specified by the converted field_name for the column). The sorting direction for the column defaults to ASCENDING in SQL. At this time, the knowledge that a particular index component was DESCENDING is lost on conversion. Any queries reliant upon that DESCENDING value will have ORDER BY clauses that properly match this behavior, including the support of sorting of mixed ASCENDING/DESCENDING indexes. The converted system does not require any indexes for functional reasons. Even without indexes, the application code will provide compatible results. The reason indexes are important is to duplicate the performance of common use cases in Progress. As such, until FWD supports mixed direction indexes, it is possible that use cases that are reliant upon mixed direction indexes will perform poorly.
INITIAL literal INITIAL [ literal, ... ]	Field	Provides the default value for the field when a new record is created (e.g. using CREATE).	Yes	Most literals are encoded inside double quoted strings. The exception is ? (unknown value) which appears by itself. To properly read this value, the quotes must be removed and the contents are then lexed again with the field's data type in mind to allow the proper interpretation to occur. On output this clause results in specific initialization of the associated DMO members during the constructor for the DMO implementation class. All forms of literals are supported for each data type, including the unknown value. In addition, this supports the use of TODAY for a date field. Depending on the value of the literal, the DMO implementation class constructor will instantiate the data member with that value. For extent fields, this initialization will occur in the constructor for the CompositeX class that is associated with this field. Please see EXTENT above for details. This property has no effect on DDL.
INITIAL literal	Sequence	When used as a property of an ADD SEQUENCE, this defines a single integer value that is the value to which the sequence is set when it is created (one time).	Yes	The difference from the field is that the literal given with this value will be an integer number not encoded inside double quotes.
LABEL text LABEL text, ...	Field	Specifies the label(s) to be used for the field when it is visible in the user interfaces (as a widget in a frame).	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details. If not specified, the unmodified Progress field name is used as the default label.
LABEL text	Table	Unknown.	No	This has been seen in .df files, but the purpose is not known.
LENGTH number	Field	Unknown.	No
LOB-AREA string	Field	Unknown.	No
LOB-BYTES number	Field	Unknown.	No
LOB-SIZE size	Field	Unknown.	No
MANDATORY	Field	Indicates whether the field may contain the unknown value. If set, the unknown value is disallowed.	Yes	Maps to a not-null="true" attribute in the property element of the Hibernate mapping file. Also will be found as a not null qualifier (or the appropriate equivalent) in the column definition in the table DDL.
MAX-WIDTH num	Field	Unknown.	No
MAX-VAL integer_literal	Sequence	Sets the largest integer value that is possible in the sequence.	Yes	H2 database has no support for this, handled in FWD runtime. PostgreSQL need also a FWD fix for querying the current value when passing the bounds.
MIN-VAL integer_literal	Sequence	Sets the smallest integer value that is possible in the sequence.	Yes	H2 database has no support for this, handled in FWD runtime. PostgreSQL need also a FWD fix for querying the current value when passing the bounds.
ORDER number	Field	Default order in which the UI widget which displays this field's value is drawn in relation to other fields' widgets, when multiple fields from a table are displayed. Also the order in which table fields are exported to Progress export files. The order assigned to a given field is compared with that assigned to other fields in the table to determine the current field's relative position (in ascending order).	Yes	Stored and used to reorder properties within a class in the P2O schema document (the intermediate form from which outputs are generated). The order is critically important when importing data from Progress export files, as the data must be read from these files in this order. The P2O files are used to drive the rest of conversion and data import. The specific ordering or ordering values have no affect or purpose on the structure or use of the database in the converted system. As such, this value is only used during conversion and no traces of it will be found in the schema conversion outputs.
POSITION number	Field	Unknown. It is possible that this defines the order in which the data dictionary displays the fields in the table editor.	No	The integer value appears to be an index position within the current table, but it is undocumented.
PRIMARY	Index	Specifies that this index is to be used as the default index when no other index is selected when the 4GL compiler processes the query.	Yes	In SQL, indexes are not used to determine sorting order. In Progress 4GL, the foremost application-visible result of having an index is that any query that uses that index will have the order of record traversal determined by that index. In other words, when the 4GL compiler selects an index, it determines the sorting order of the records. Sorting is not implemented by schema features in SQL, so this feature has no affect on the converted relational schema. In FWD, the conversion handles the index selection as a conversion-time calculation for each query. FWD honors the index marked as primary as the default index when another index is not selected. This results in an ORDER BY clause being generated for the query, which has the columns listed (and the direction of sorting) which comprise the selected index's fields. This occurs for all queries that can traverse a sequence of rows in the table. See the Index Selection chapter in Part 5 of this book for details.
PSC CPSTREAM=codepage_name . number	Database	This is the .df file trailer. It specifies the codepage for the database and there are variants that have additional properties.	No	This data Is ignored at this time. The codepage for the database (and for the processing of the import) must be set independently. See the Internationalization chapter of the FWD Conversion Handbook for details. The schema parser will not match any of the other property variants, except for the use of the keyword CODEPAGE in place of CPSTREAM. The number specified is the number of characters (or possibly the number of bytes) in the .df file.
SQL-WIDTH	Field	Unknown. May be related to or calculated from the format string. Extent fields hold a value that must be divided by the extent to get the per-field size.	No	The integer value appears to specify the size of the field in bytes, but it is undocumented. It is definitely incorrect for fields such as CHARACTER which are variable sized and can grow to be much larger than the values specified.
TABLE-TRIGGER type_string (OVERRIDE | NO-OVERRIDE) PROC proc_name CRC logical_string	Table	Defines a procedure to use on the specific table when the given event type is raised.	No
UNIQUE	Index	Specifies that the field or fields in the index must be unique for each row. In the case of multiple field indexes, the combination of all fields in the index must match one and only one row in the table.	Yes	The index DDL will have the unique keyword added for unique indexes. To facilitate FWD runtime behavior for unique indexes, there will be a unique element in the table's section of the DMO index will have a list of one or more component elements which each store a name attribute with the column name that must participate in the unique index.
VALEXP string	Field	Specifies a logical expression that must evaluate true at the time that the field is interactively edited in a user interface. If it evaluates to true, the edit is accepted, otherwise the edit fails and the user is prompted with the VALMSG.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details, including the full syntax that is supported.
VALEXP string	Table	Evaluated to allow/disallow a record delete.	No
VALMSG string	Field	Specifies the text to display when the field VALEXP fails during an interactive edit in a user interface.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details, including the full syntax that is supported.
VALMSG string	Table	Specifies the text to display when the table VALEXP fails during an interactive edit in a user interface.	No
VIEW-AS phrase	Field	Specifies the default widget type and configuration values, when a widget in a user interface frame is based upon this field.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details, including the full syntax that is supported.
WORD-INDEX	Index	Specifies that the fields in this index can be searched using the CONTAINS operator in a WHERE clause.	No

Temporary Table Elements¶

The only sources of temporary table schema definitions that are supported by FWD are the DEFINE TEMP-TABLE and DEFINE WORK-TABLE language statements. The two are essentially equivalent functionally except that work-tables in Progress 4GL don't support the full set of features that can be obtained with temp-tables. Since Progress 4GL temp-tables are a functional superset of work-tables, FWD treats converts work-tables and temp-tables to the same result in Java. Since the DEFINE TEMP-TABLE is a superset DEFINE WORK-TABLE and converts to the same result, the DEFINE WORK-TABLE will be ignored for the rest of this book.

All schema conversion results for temp-tables will be found in Hibernate Mapping files, DMOs (interfaces and implementations) and the DMO Index. No DDL will result from the schema conversion. All DDL for creating the temp-tables, constraints and indexes is dynamically generated at runtime by FWD. For this reason, there is no need to document temp-table DDL as a conversion output.

The following documents each portion of the DEFINE TEMP-TABLE language statement. Only features documented to be honored by the parser are listed here. Any features that cannot be properly read by the parser are by nature not supported. See the chapter entitled Parser in Part 1 of this book for details. Any features listed as not supported in the table are silently ignored when present in the source code.

Progress 4GL	Category	Purpose	Supported	FWD Result
AS data_type	Field	Specifies the data type of the field.	Yes	Defines the data type of the field. See the section below on Data Type Conversion for details on the types and the associated mappings that are supported. The type will affect the generated DMOs and the Hibernate mappings for this field.
BEFORE-TABLE	Table	Specifies the before-image table when used in a ProDataSet.	No
BGCOLOR expression	Field	Specifies the background color for any widget in a graphical user interface frame which is based on the associated field.	No
COLUMN-CODEPAGE cp_name	Field	Specifies the codepage for a CLOB field to be set to that matching the cp_name in the 4GL configuration.	No
COLUMN-LABEL label	Field	Specifies the string literal to be used as a column label for any widget created from this field in a user interface frame.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
DCOLOR expression	Field	Specifies the display color for any widget in a character user interface frame which is based on the associated field.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
DECIMALS num	Field	Specifies the number of digits to the right of the decimal point which will exist for this decimal type field.	Partial	The Hibernate mapping file property element will have a scale=”num” attribute that matches the numeric literal specified as num. The DMO implementation class constructor will pass num as a second parameter to the decimal constructor when it instantiates the backing member for this field. At this time the DDL that is generated will set numeric columns to a precision of 50 and a scale to the value specified as num or 10 if this clause is not included in the field definition. The precision setting is the total number of significant digits. The scale setting is the number of digits to the right of the decimal point. In addition, further research is needed to determine if WHERE clauses will process properly in all cases. The issue is whether or not the H2 user-defined functions will properly receive and return intermediate decimal values that have the correct scale based on the backing column's scale setting.
EXTENT num	Field	If greater than 0, indicates the field holds an array of values of the field's data type, rather than a single value of that type. The value of the extent is the size of the array.	Yes	Array values are stored in a secondary table. The secondary table contains a foreign key column related to the primary key of the table containing the array column. An index column in the secondary table maintains the appropriate indexing of the array values. If multiple columns in the same table use the same extent value, they are all be stored in the same, secondary table, each in a separate column. Otherwise, a separate, secondary table exists for each occurrence of a new extent value within the same Progress table. This extra table and the relations with the original table are hidden from the user of the DMO by Hibernate. Hibernate knows how to properly map the extent data to and from the extra table. This is defined in the Hibernate mapping document as a list of composite elements. The number of rows in a secondary table equals the number of rows in the primary table times the size of the extent which the secondary table manages. For example, a table containing 1000 rows which manages four fields, each with an extent of 12 will be related to a secondary table containing (1000 * 12), or 12,000 rows. The data model object which maps to a table containing array columns provides access methods to make this backing implementation transparent. The interface class will have method signatures that include a zero-based index parameter. The implementation class will have a List member that is used to contain the individual elements. It will also have an inner class CompositeX where X is the number of elements being stored. Finally, the implementation class will have code to provide the actual get and set operations in a set-oriented manner.
FGCOLOR	Field	Specifies the foreground color for any widget in a graphical user interface frame which is based on the associated field.	No
[FIELD] field_name	Field	Defines a new field of the given name for the temp-table.	Yes	The name is translated to replace invalid characters and yield a version that is valid for use as a relational column name (in SQL) as well as a Java DMO instance variable name. The DMO variable name also forms the basis for the getter and setter method names of the Java DMO. The name can be overridden using conversion hints. Both the Java variable name and the relational column name will be found in the Hibernate mapping file.
field_name [ASCENDING | DESCENDING]	Index	Adds the given field to the index and optionally specifies the direction of the sorting of that field.	Yes	Adds a column child element to the associated index element in the temp-table's section of the DMO index. This column element will have a name attribute which will match the converted field_name for the column. The sorting direction for the column defaults to ASCENDING in SQL. If DESCENDING is specified, the column element will have a descend=”true” attribute.
FONT expression	Field	Specifies the font for any widget in a graphical user interface frame which is based on the associated field.	No
FORMAT expression	Field	Specifies the visual formatting of the field when used as a basis for a widget in a user interface frame.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
HELP text	Field	Specifies the help text that should appear in the status line of the user interface when a widget based on this field obtains focus.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
INDEX index_name	Index	Creates a new index for this temp-table with the given index_name.	Yes	The FWD runtime uses the index definitions in the DMO index to create the DDL for instantiating indexes at runtime. This 4GL syntax creates an index element in the temp-table's section of the DMO index. The index_name will be converted to a compatible SQL name and then stored as the name=”converted_index_name” attribute of the index element.
INITIAL literal INITIAL [ literal, ... ]	Field	Provides the default value for the field when a new record is created (e.g. using CREATE).	Yes	This results in specific initialization of the associated DMO members during the constructor for the DMO implementation class. All forms of literals are supported for each data type, including the unknown value. In addition, this supports the use of TODAY for a date field. Depending on the value of the literal, the DMO implementation class constructor will instantiate the data member with that value. For extent fields, this initialization will occur in the constructor for the CompositeX class that is associated with this field. Please see EXTENT above for details.
[IS | AS] PRIMARY	Index	Specifies that this index is to be used as the default index when no other index is selected when the 4GL compiler processes the query.	Yes	In SQL, indexes are not used to determine sorting order. In Progress 4GL, the foremost application-visible result of having an index is that any query that uses that index will have the order of record traversal determined by that index. In other words, when the 4GL compiler selects an index, it determines the sorting order of the records. Sorting is not implemented by schema features in SQL, so this feature has no affect on the converted relational schema. In FWD, the conversion handles the index selection as a conversion-time calculation for each query. FWD honors the index marked as primary as the default index when another index is not selected. This results in an ORDER BY clause being generated for the query, which has the columns listed (and the direction of sorting) which comprise the selected index's fields. This occurs for all queries that can traverse a sequence of rows in the table. See the Index Selection chapter in Part 5 of this book for details.
[IS | AS] UNIQUE	Index	Specifies that the field or fields in the index must be unique for each row. In the case of multiple field indexes, the combination of all fields in the index must match one and only one row in the table.	Yes	The index element in the temp-table's section of the DMO index will have an attribute unique=”true” which will cause the DDL for index creation to force the index to be a unique index.
[IS | AS] WORD-INDEX	Index	Specifies that the fields in this index can be searched using the CONTAINS operator in a WHERE clause.	No
LABEL text LABEL text, ...	Field	Specifies the label(s) to be used for the field when it is visible in the user interfaces (as a widget in a frame).	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
LIKE field_name	Field	Copies the structure of the given field (it's type and configuration) into the new field for this table.	Yes	The definitions are copied into the temp-table's field definition and will be processed by downstream conversion. Each specific feature that is copied will convert as if it was hard coded into the FIELD definition portion of the DEFINE TEMP-TABLE statement.
LIKE table_name	Table	Copies the table structure, names, types and most configuration (except for some indexes and validation expressions for fields) from the specified table.	Yes	The definitions are copied into the temp-table and will be the core of the table's schema that is then processed by downstream conversion. Each specific feature that is copied will convert as if it was hard coded into the DEFINE TEMP-TABLE statement.
MOUSE-POINTER expression	Field	Specifies the mouse pointer that should appear in the user interface when a widget based on this field obtains focus.	No
NAMESPACE-PREFIX	Table	Controls the namespace prefix for the temp-table.	No
NAMESPACE-URI	Table	Controls the namespace for the temp-table.	No
NEW GLOBAL SHARED	Table	Creates a temp-table that exists for the entire session and is accessible from all procedures.	Yes	This has no effect on the schema, it is a runtime attribute. See Part 5 for more details.
NEW SHARED	Table	Creates a temp-table that exists for as long as the procedure in which the definition exists. It is accessible only in procedures that are at or more deeply nested than the procedure that defined the table.	Yes	This has no effect on the schema, it is a runtime attribute. See Part 5 for more details.
NO-UNDO	Table	If present, temp-table changes are NOT backed out when a transaction is undone. By default, changes are backed out of a temp-table when a transaction is backed out.	Yes	This is not part of the schema conversion but is a runtime flag set by the business logic when an instance of the temp-table is created. See Part 5 of this book for details.
[NOT] CASE-SENSITIVE	Field	If NOT is specified, then the field is explicitly case-insensitive (which is also the default). If only CASE-SENSITIVE is specified, then all searching or comparisons with this field will be done case-sensitively.	Yes	By default, fields are NOT CASE-SENSITIVE. If unspecified, or if NOT CASE-SENSITIVE is specified explicitly, then WHERE clause processing and query substitution parameters are modified to implement case-insensitive comparisons. In particular, character fields in WHERE clauses are wrapped like this (data is the field name): upper(data). character expressions in substitution parameters will be wrapped like this: toUpperCase(expression). String literals are forced to uppercase if needed at conversion time and emitted in the proper case. The configuration data for temp-table index definitions is read from the DMO index at runtime, each column in an index definition will be listed as a child element with an attribute ignore-case=”true”. This is used at runtime to add (or not add) the wrapper to the DDL for index definitions as needed. The index DDL that is dynamically generated will have index definitions with case-insensitive character columns wrapped in upper(rtrim(char_col, ' \t\r\n')) and case-sensitive columns wrapped in rtrim(char_col, ' \t\r\n'). These changes also impact sorting but the modifications to the ORDER BY are handled transparently at runtime by the FWD persistence layer. Case-insensitive character columns listed in the ORDER BY are dynamically wrapped in upper(rtrim(char_col, ' \t\r\n')) and case-sensitive columns are wrapped in rtrim(char_col, ' \t\r\n'). The trimming is required regardless, but it is critical for the runtime to match the same column "expression" as is used in the dynamically generated index DDL. This processing is always present when comparing or sorting character fields except in when CASE-SENSITIVE is specified. When specified, the upper() wrapper is eliminated in WHERE clauses, toUpperCase() is eliminated from substitution parameters, the upper() is removed from ORDER BY and index definitions (DDL created from the definitions in the DMO index). For temp-tables, there are 2 outputs when there is a case-sensitive field. First, the DMO index will have a case-sensitive element added as a child of the class element. For example: <class interface="MyTable"> <case-sensitive name="myField"/> </class> The absence of the case-sensitive element with a name matching a given column implies that the column is not case-sensitive. The second output for a case-sensitive field is when it is included as an index field, that column element will have the ignore-case="false" attribute.
PFCOLOR	Field	Specifies the prompt-for color for any widget the character user interface frame which is based on the associated field.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details.
PRIVATE	Table	Marks the temp-table as one that is only accessible by the specific class in which the table is defined.	No
PROTECTED	Table	Marks the temp-table as one that is only accessible by the specific class in which the table is defined and any subclass of that class.	No
RCODE-INFORMATION	Table	Stores buffer attribute configuration in the R-CODE representation at compile time.	No	This has no effect on the logical behavior of the converted program or the converted database, so there is no reason to convert this. It is dropped.
REFERENCE-ONLY	Table	Specifies that instantiation of the temp-table is not needed, that a common instance will be accessed here that has been instantiated by a calling procedure.	No
SHARED	Table	Accesses a previously created shared or global shared temp-table.	Yes	This has no effect on the schema, it is a runtime attribute. See Part 5 for more details.
TEMP-TABLE symbolic_name	Table	Defines the name for the temp-table.	Yes	The name is translated to replace invalid characters and yield a version that is valid for use as a relational table name (in SQL) as well as a Java DMO class name. The name can be overridden using conversion hints. The Java name will be found in the DMO index. Both the Java class name and the relational table name will be found in the Hibernate mapping file. The Java name will form the basis of both the interface and the implementation files for the DMO.
TTCODEPAGE	Field	Forces the codepage for a CLOB field to be the same as -cpinternal (the internal 4GL codepage).	No
USE-INDEX index_name [AS PRIMARY]	Index	If specified, the given index definition will be copied from the LIKE table. If AS PRIMARY is specified, the index is forced to be the primary index.	Yes	The index definition is copied into the temp-table and will be processed by downstream conversion. It will be overridden as the primary index if AS PRIMARY is used. Each specific feature that is copied will convert as if it was hard coded into the DEFINE TEMP-TABLE statement. Any number of these USE-INDEX clauses can be specified. When used, the list of these indexes to be copied is forced explicitly, so that any indexes in the LIKE table that were not so specified will not be copied. Any INDEX clause definitions will be additive to the USE-INDEX definitions. Only when no USE-INDEX and no INDEX clauses are used will all the LIKE table indexes be automatically copied.
VALIDATE	Table	If specified, field level validation expressions and messages are copied from the LIKE table.	Yes	The validation definitions are copied into each field of the temp-table and will be processed by downstream conversion. Each specific feature that is copied will convert as if it was hard coded into the DEFINE TEMP-TABLE statement.
VIEW-AS phrase	Field	Specifies the default widget type and configuration values, when a widget in a user interface frame is based upon this field.	Yes	This configuration is honored as part of all frame definitions that include this field. This value has no effect on any of the schema related conversion, it is only a user interface feature. See Part 6 - User Interface of this book for details, including the full syntax that is supported.
XML-DATA-TYPE text	Field	Specifies the XML Schema data type for the field when used in XML mapping.	No
XML-NODE-TYPE text	Field	Specifies the XML node type (e.g. element, attribute) for the field when used in XML mapping.	No
WORK-TABLE symbolic_name	Table	Defines the name for the work-table.	Yes	Work-Tables are treated exactly as if they were Temp-Tables. See TEMP-TABLE symbolic_name for details.

Name Conversion¶

Name conversion processing for the schema is primarily documented in the Naming section of the Other Customization chapter of the FWD Conversion Handbook. That chapter describes the different kinds of conversions, the process by which the conversions occur and the expected results. In addition, there are powerful customization options that can be specified for the project which are documented in that chapter.

The only clarification that must be mentioned here is that the Progress names used for input may be converted to multiple output names used for different purposes.

The Progress database name(s) are not included as part of the schema. However, each FWD project configuration contains a legacy database name for each schema being converted. That legacy database name is not converted into the SQL database name. Instead, it is used as a string (unchanged) at runtime, which is used as a label to identify the database being used for specific business logic processing. The SQL database name itself is somewhat independent. It is created manually as part of the data import process, where a manual CREATE DATABASE DDL statement is executed to create a database instance. Then the FWD server's configuration database (the server's “directory”) will contain a mapping between the legacy database name and the JDBC URL that the FWD server uses to connect to the specific database instance that was created. This extra level of “mapping” of the legacy name to the actual database instance being used is useful to allow an unlimited number of arbitrarily named database instances which all share the same schema.

The table names from the Progress schema are converted twice. The first target is to convert each Progress table name into a Java class name. That class name is the basis for the name of the DMO interface and the name of the DMO implementation class. The corresponding filenames are likewise based on the Java class name. The Hibernate mapping file name is also generated using that name as a basis. The second target is the SQL table name which is emitted in the DDL.

Progress field names get converted twice and through an additional step one of the converted outputs is used for a third purpose. The first target is to convert each Progress field name into a the Java variable name for the corresponding DMO property (the data member of the DMO that represents the converted data for that Progress field). This Java variable name is then reused to create getter and setter methods for the DMO, which allow that data member to be accessed and/or modified. The conversion uppercases the first character of the variable name (leaving the rest with whatever camel-case was already established). Then a getter prefix (is for logical data types and get for all other types) or setter prefix (set for all types) is added to get the final method names for each getter and setter. The second target for a Progress field name is the SQL column name which is emitted in the DDL.

Each index name is converted to the SQL index name as is documented in the FWD Conversion Handbook. The result is emitted in the DDL that is created for indexes during the data import processing OR in the case of temp-tables, the converted SQL index name will appear in the DMO index where the temp-table indexes are persistently configured.

Data Type Conversion¶

All DMO classes which have been converted from an application's Progress schema use the FWD wrapper data types for all data members except the primary key (and multiplex ID for temp tables). This is necessary to preserve proper Progress semantics in arithmetic calculations, logical comparisons and other business logic.

Thus, all DMO accessor (getter) and mutator (setter) methods return and accept, respectively, one of the following types:

• FWD wrapper types (most converted data fields);
• java.lang.Long (primary key field/methods);
• java.lang.Integer (_multiplex field for temp table DMOs);
• object references to other DMOs, in the case of relational associations.

The backing DMO data members which are wrappers are instances of Java classes provided by FWD in the com.goldencode.p2j.util package.

While Hibernate built-in types are used for java.lang.Integer (Hibernate type integer) and java.lang.Long (Hibernate type long), the back-end database in the target environment and Hibernate are unaware of the FWD wrapper data types. This is another area where the mismatch between Progress 4GL semantics and the target environment must be managed.

The solution is the use of Hibernate's user data type facility. Specifically, custom implementations of the org.hibernate.usertype.UserType interface. This means that all Hibernate properties are implemented as user data-types provided by FWD in the com.goldencode.p2j.persist.type package. This provides transparent transitions between the FWD wrappers needed by the converted application, and the SQL data types needed by the database. For the custom user type implementations, a mapping is provided between the FWD wrapper types and the closest corresponding JDBC data type (defined in the java.sql.Types class), as follows:

Progress Type	JDBC/SQL Type	FWD Type	Hibernate User Type Implementation	Notes
blob	n/a	n/a	n/a	Not yet supported.
character	VARCHAR	character	CharacterUserType	User type uses java.lang.String internally.
class	n/a	n/a	n/a	Not yet supported.
clob	n/a	n/a	n/a	Not yet supported.
com-handle	n/a	n/a	n/a	Not yet supported.
date	DATE	date	DateUserType	User type uses java.sql.Date internally.
datetime	n/a	n/a	n/a	Not yet supported.
datetime-tz	n/a	n/a	n/a	Not yet supported.
decimal	NUMERIC	decimal	DecimalUserType	User type uses java.math.BigDecimal internally.
integer	INTEGER	integer	IntegerUserType	User type uses int internally.
int64	n/a	n/a	n/a	Not yet supported.
handle	VARCHAR	Handle	HandleUserType	User type uses java.lang.String internally.
logical	BIT	logical	LogicalUserType	User type uses boolean internally.
raw	VARBINARY	raw	RawUserType	User type uses byte[] internally.
recid	INTEGER	recid	IntegerUserType	User type uses int internally.
rowid	BIGINT	rowid	RowidUserType	User type uses java.lang.Long internally.

All of the user type implementation classes transparently map the Progress unknown value to NULL.

The mapping between DMO properties (i.e., Progress fields and the replacement SQL columns) and the appropriate user type implementation is defined in the Hibernate mapping (.hbm.xml) document for each DMO class on a per-property basis. By using this architecture, the transition between database and DMO data types is hidden inside the Hibernate layer and the Progress semantic is preserved for converted code. However, the mapping is easily changed post-conversion, if it is deemed that different data types (e.g., primitives or J2SE wrappers) are suitable in certain cases. Perhaps more importantly, this architecture supports the development of new DMOs and supporting application modules which do not use the Progress semantic at all.

The types used by the Java JDBC layer and the types actually specified in the SQL DDL are normally the same. Due to database-specific differences in behavior and implementation of these types, FWD sometimes must override the SQL types in use to obtain the proper support for Progress semantics. This is done in FWD's custom implementations of the Hibernate Dialect class (actually implementations which override Hibernate's database-specific Dialect implementations), which are found in the com.goldencode.p2j.persist.dialect package. In each case, the JDBC types are constant, but the actual types used in the DDL is swapped by Hibernate (at the request of FWD) in the following cases:

Database	JDBC Type	SQL Type	Notes
PostgreSQL	VARCHAR	TEXT	This is a PostgreSQL-proprietary type that implements an unlimited-size character data column. The VARCHAR in PostgreSQL is limited to a specific size that is specified in the DDL. Since Progress character fields don't have a specific limit (other than the inherent limits in the database), using TEXT is the best mapping.
PostgreSQL	NUMERIC	NUMERIC($p,$s)	Forces the PostgreSQL dialect to generate column specifications (DDL) that honor the precision ($p) and scale ($s) of the column. The precision is the number of significant digits, which will be set to 50 when a column is created. The scale is the number of digits to the right of the decimal point. It is set to 10 by default and will otherwise be set to the value of the DECIMALS option. The resulting DDL will properly configure decimal columns.
H2	NUMERIC	NUMERIC($p,$s)	Forces the H2 dialect to generate column specifications (DDL) that honor the precision ($p) and scale ($s) of the column. The precision is the number of significant digits, which will be set to 50 when a column is created. The scale is the number of digits to the right of the decimal point. It is set to 10 by default and will otherwise be set to the value of the DECIMALS option. The resulting DDL will properly configure decimal columns.
H2	VARBINARY	BINARY	By default, VARBINARY is a bounded (size-limited) type. H2 allows an unbounded version of this using the BINARY type.
H2	VARCHAR	VARCHAR	By default, VARCHAR(n) is a bounded (size-limited) type with a maximum length of n (n is a decimal integer). H2 allows an unbounded version of this using the VARCHAR type with no parenthesized integer length.

The following are undocumented data types that have been encountered in the metadata schema (.df) files but which are not supported as user-defined fields: BIGINT, BYTE, DOUBLE, FIXCHAR, FLOAT, SHORT, TIME and TIMESTAMP. These metadata types are not supported in FWD.

---

© 2004-2017 Golden Code Development Corporation. ALL RIGHTS RESERVED.