/genSql

Qualifiers

• /meta—generates SQL command scripts for Uniface Repository tables using the signature descriptors delivered with the IDE resources. Each generated file contains a short description of the meta model group to which the entities belong. See Scripts for Repository Tables and Scripts for User Tables

Parameters

• createTable—generates SQL command scripts for creating database tables, and where applicable, indexes and stored procedures; known as the Create Table facility. See Scripts for Creating Tables.
• createScript—generates the specified type of referential integrity script for maintaining referential integrity in the DBMS (as opposed to letting Uniface handle it). One of:
  • createRI (or createReferentialIntegrity)—generates an SQL script that can create triggers and declarative constraints for controlling referential integrity in the DBMS.
  • dropRI (or dropReferentialIntegrity)—generates an SQL script that can drop triggers and declarative constraints from the DBMS so that referential integrity can be handled by Uniface.
  • validateRI (or validateReferentialIntegrity)—generates an SQL script to check the consistency of the database, based on the relationships defined between modeled entities.

  These scripts are collectively known as the Create Script facility. See Scripts for Referential Integrity.

• entity.model—profile of the entity name (including the modelsuffix) for which to generate scripts. The profile supports the use of the wildcard characters: asterisk * (matches 0-N characters) and question mark ? (matches 1 character); for example, *.sales.
• database—three-letter mnemonic of the DBMS connector for the target database system; for example, ORA or MSS. For more information, see DBMS Connector Overview.

  Note: /gensql does not generate scripts for SEQ, TXT, and ODBC connectors.

• fileName—name of the output script file. If specified, a single file is generated per request. If omitted, a name is used that reflects the database and model in use; for example, ora_sales_createTable.sql. If the entity.model profile matches multiple models, the multiple files are generated, one for each matching model.

Use With

ide.exe—Uniface IDE

Description

When generating scripts, you must ensure that the assignment file contains settings that match the DBMS connector version and parameters that will be used for the target DBMS.

• The DBMS connector version specifies the connector that is used to generate the scripts
• The DBMS connector options (set with USYS$xxx_SETTINGS) can affect the script generation. For more information, consult the Uniface documentation for your connector and the DBMS documentation itself.

The resulting SQL script files contain DBMS-specific SQL. If necessary, these scripts can be edited to change the user name or database name, add comments, add foreign keys, or make other changes that reflect requirements specific to your application.

They can then be executed by the DBA to build tables and manage referential integrity for database entities in supported database systems.

For example, if you are using the Uniface default installation (which uses SQLite) for development, and you intend to deploy your application using an Oracle database, add the ORA connector declaration and related connector options (set with USYS$ORA_SETTINGS) to the [DRIVER_SETTINGS] section.

[DRIVER_SETTINGS]
;settings used for Uniface Repository:
SLE     U1.0
USYS$SLE_PARAMS create db = on, identifiers = quoted

;settings used for  target database:
ORA     U5.1
USYS$ORA_PARAMS map fixed length to variable, unicode_smp

Scripts for Repository Tables

By default, Uniface is configured to use SQLite (SLE) as its Repository database, and users have all rights to create and delete databases and tables. However, many DBMS systems are configured to prevent users from creating tables. Only the system administrator (DBA) can do this.

If you want to use a different DBMS system as your Repository, with different user privileges, you will need to:

1. Generate the scripts using the default SQLite database for the $IDF path.
2. Run the scripts in your target database.
3. Configure Uniface to use the target DBMS.
4. Use /genSql with the /meta sub-switch to generate SQL scripts for the Repository tables in a supported database.

For more information, see Generate Scripts for Creating Repository Tables

The scripts are generated using signature descriptors delivered with the IDE resources. Even if you have imported the Uniface meta model entities (provided in \uniface\misc\umeta.xml), they are ignored by /meta.

The default name of the generated files is prefixed with umeta_, for example, umeta_sle_dict_createtable.sql. Each generated file contains a short description of the meta model group to which the entities belong. This description is not provided in scripts generated without the /meta switch.

ide.exe /gensql /meta createtable *.dict mss

 Description
    -----------
    Script type                     : Create Table
    Uniface Driver Version          : U4.0
    Driver settings USYS$MSS_PARAMS : identifiers = quoted, procedures=on

    Generated by Uniface Version    : 10.4.01 (F116 0419_1)
    Generated on                    : 25-jan-22 14:09:52
    File name                       : umeta_mss_dict_createtable.sql

    Application model DICT
    ----------------------
    The entities of the application model DICT contain the source definitions
    for applications.

    Data is stored via the logical path $IDF.

    Model name                      : DICT
    Copyright                       : 2022 Rocket Software B.V. All rights reserved.

Scripts for User Tables

To generate scripts for user tables, use /genSql without the /meta switch and specify desired profile for entity.model. Before generating the scripts, Uniface analyzes the entities to generate signature descriptors, and uses these to generate the scripts.

Scripts are generated only for modeled database entities (Database Behavior property set to Y (In Database)) that match the profile (including meta model entities, if imported). The generated scripts contain a header describing the script and the generation options.

Scripts for Creating Tables

Use the createTable option to generate scripts that can:

• Create database tables for modeled database entities.
• Create indexes for entities that define one or more indexes.
• Generate stored procedures if the DBMS supports stored procedures and the corresponding connector option is present in the USYS$XXX_PARAMS setting

The following restrictions apply when generating create table scripts for user tables:

• Subtypes are ignored.
• Mismatched data types in foreign keys are not detected.
• If you define more than one nullify relationship with overlapping foreign key fields between two entities, this results in a semantically incorrect delete trigger within the DBMS.
• The priority of the delete action ensures that only the most efficient triggers are created, so multiple relationships can result in the creation of a different trigger than the one you intended.

• Entity assignments can cause inconsistent naming in some statements. Changing the entity name using an assignment file does not affect the names used in drop trigger and create trigger statements, although it affects the names of tables, indexes, and stored procedures.

  For example, the following assignments cause the Create Table utility to use the assigned names (ENT2 and ENT4) for tables and stored procedures in DB2:

  Db2Entcs1 $DB2:ent2
  entcs1 $DB2:ent4

For more information, see Create Table Facility and Generate Scripts for Creating Database Tables.

Scripts for Referential Integrity

By default, Uniface handles referential integrity checking. For some databases, it is possible for the database itself to handle referential integrity checking. The scripts that you generate with the /genSql CreateScript facility enable you to implement that, or reverse it, if required.

The generated scripts map relationships defined in the application model to declarative constraints and database triggers in the database. They can:

• Check relationships between database tables for violations of referential integrity.
• Generate declarative constraints and, if necessary, database triggers.
• Drop declarative referential integrity constraints and database triggers, if required.

For example, some DBMSs support declarative referential integrity constraints with the Restricted foreign key constraint, but not with the Cascading or Nullify foreign key constraint. For these DBMSs, Uniface generates a declarative constraint for relationships with the Restricted foreign key constraint and an appropriate mix of declarative constraints and database triggers for relationships with a Cascading or Nullify foreign key constraint.

When Uniface uses database triggers to implement relationships, the generated triggers check all possible modifications to the data. That is, when the existing data in a database does not violate referential integrity, and the generated triggers have been created, no violations of referential integrity can occur in the database.

When generating SQL scripts, the following limitations apply:

• Related tables must be in the same database, unless the DBMS supports the use of declarative constraints and triggers across database boundaries.
• With some DBMSs, the generated database triggers reject non-valid modifications by means of statement-level rollback or transaction rollback.

  In some cases, triggers can only generate an error. The error is available through the $dberror ProcScript function. In this case, the correct implementation of referential integrity must rely on the application to perform a rollback when an I/O error occurs.

• Referential integrity checking in the database is more restrictive than in Uniface. If you apply referential integrity checking on existing applications, the applications can fail the checks in the database for the first time.

For more information, see Create Script Facility and Generate Referential Integrity Scripts.