FWD » Core Development » Database

Currently the Conversion Handbook and Conversion Reference describe how the identifiers for databases, tables, fields, indexes and sequences are converted from 4GL to SQL. Although this looks apparently simple, the result is not always straightforward and the result might not be exactly as predicted. There are several causes for this:

• table structure: switching from normalised (now deprecated) to expanded extent table layout will force changes not only in identifier names, but also changes in resulting SQL tables structure;
• name conversion depends on the configured dialects. The conversion aims for compatibility with all configured dialects so it will exclude identifiers colliding with reserved words and keyword. Adding a new dialect to conversion may cause the exclude additional identifiers;
• usage of .hints for customising specific artefacts;
• specific TRPL code (customer specific annotations rules).

The conversion will write the resulting identifiers in the DMO annotations for runtime and will use them to generate the DDLs used when the database schema is installed. However, there is no explicit mapping extracted at the end of the process which a customer can easily consult if some direct access to database is needed (either in CLI or JDBC/ODBC). This task will track the development of a module, tools or other kind of script to be provide this artefact.

Key aspects:

• output: we can go for different file formats. From simple .txt or .pdf to more complex .html or even .csv, .xml, .json or .xls. The firsts are easily readable/searchable by humans, but the latter may be used as input for other automated tools. Alternatively, writing to a database with a specific schema should be possible;
• the moment of extraction:
  • one solution would be to generate this at conversion time, as the new names for identifiers are decided. The advantage is that the process is on-the-fly, but the resulting artefacts will have to be linked to both source version and specific configuration. It might need additional configuration and if something else (different format, for example) is needed, the full reconversion is required;
  • alternatively, we can write a tool for extracting this information later, from existing annotations of .class files from a .jar. I think this will have a few advantages:
    • faster conversion;
    • processed only on demand, if ever needed;
    • no need to link to original code/configuration;
    • could be run multiple times, with different arguments (ex: output formats)l
    • if a bug is identified or a new output format is added, there is no need to redo the full conversion;
    • could be configured for partial processing (only a specific database or set of tables).

Ovidiu Maxiniuc wrote:

• alternatively, we can write a tool for extracting this information later, from existing annotations of .class files from a .jar. I think this will have a few advantages:
  • faster conversion;
  • processed only on demand, if ever needed;
  • no need to link to original code/configuration;
  • could be run multiple times, with different arguments (ex: output formats)l
  • if a bug is identified or a new output format is added, there is no need to redo the full conversion;
  • could be configured for partial processing (only a specific database or set of tables).

Yes, we need a tool which gets the .p2o files and emits a file for each schema and dialect. This tool can then look also into DMOs to get more info, but the .p2o files should be enough.

Please propose a structure for the table and field information - I would expect, beside legacy, SQL and DMO property names, to also emit 4GL/SQL type, extent index, format?

Yes, we need a tool which gets the .p2o files and emits a file for each schema and dialect. This tool can then look also into DMOs to get more info, but the .p2o files should be enough.

I was thinking more to scan the annotations from dmo/ folders, but parsing the .p2o XML will ultimately yield the same result.

Please propose a structure for the table and field information - I would expect, beside legacy, SQL and DMO property names, to also emit 4GL/SQL type, extent index, format?

Yes, something like this.

Note that:

• the SQL type is dialect dependent. Also, the datetime-tz is a (the only, for now) multi-column field/property. We need to accommodate it somehow in this table;
• the format per-se is irrelevant for database, but its size matters when the data width is computed for some dialects (like SQL Server, MariaDb);
• I'm strongly thinking of something more structural like xml/json for tables/fields/indexes?