Schema Export New from DB 39.5.0

Information

• You have the option to export the current database definition in the Oracle or MSSQL format.

Requirements

• The directory into which you want to export must be defined for the export_path_databasescheme global setting under Customizer Master Data Global Settings in the Template code data field.
  • In Windows systems, no single \ is used, but two \\.
    • C:\\planta\\export\\schema.

Procedure

• Select _Customizer Databases Database Schema Export in order to initiate a schema export.
  • The export can take between 30 seconds and 1 minute.
• Afterwards, the exported schema is located in the specified directory under \SQLServer_<Export time stamp>\SQLServer_create.sql or \Oracle10g_<Export time stamp>\Oracle10g_create.
• The drop scripts contain information on the deletion of the current schema.
• The hibernate_mapping subdirectories contain data table definitions for Java.
• In order to transfer the exported schema to a new database, theoretically the content of _create.sql can simply be opened, in SqlPlus or SqlDeveloper, for example.
  • We highly recommend that you do this in a regular installation process.
    • Here, you can simply rename the exported _create.sql script into 00_tables in an existing installer and copy it to \db\bin\sql\oracle\10_tables or \db\bin\sql\ms-sql\10_tables\ and possibly replace the already existing file.

Notes

• For the database, only the database definition (the schema) is exported, not the database content (real data).
• A schema is always created in both Oracle and MSSQL format, regardless of the format of the actually used database.
  • It is therefore not necessary to open the schema export twice (e.g. with an Oracle and an MSSQL database).

Detailed structure of the script content

• In a create-script, all data tables including their columns are defined first.
• The following data tables are exported:
  • All activated PLANTA data tables, i.e., all data tables for the schema of which the Activated checkbox is activated and the Activated checkbox for the data table itself is activated.
  • For each data table of the Q1B and Q2B schemas there is also a data table of the same name with the suffix HIS and identical columns that need Hibernate Envers in order to save history files. See also Historization.
  • Furthermore the internal REVINFO table of Envers.
• Definition of the data table columns.
  • All columns that have been set to Active, are written.
  • From version S 39.5.0, each data table (except REVINFO) has a UUID column containing a unique ID for each record.
  • The primary keys stand before the UUID field and are defined at the end of the data table.
  • Default values and NOT NULL constraints:
    • All columns of the primary key imply a NOT NULL constraint.
    • All numeric fields (integer, short, double) have a NOT NULL constraint and 0 as a default value.
    • All date fields have a NOT NULL constraint and 01.01.1970 as default value (in both Oracle or MSSQL notation).
    • The UUID fields create a new, unique UUID value as default value (in both Oracle and MSSQL notation) and have a NOT NULL constraint as well.
    • Numeric fields that form part of a foreign key in the referenced data table of which NULL permitted is activated, are an exception. Such columns neither have a default value nor a NOT NULL constraint.
• Then the definitions of the indices and constraints of all data tables follow.
  • There are different types of indices and constraints:
    • I-text indices for all data tables the records of which contain an I-text. This index type has no constraint.
    • Foreign key indices for each data table that defines a link to another data table. Furthermore, this index type defines a constraint (or, in MSSQL, a trigger for update and delete function).
    • Envers also creates constraints for its history tables that refer to the general REVINFO Envers table. Envers does not create indices for this.
• Finally, the script contains a new database sequence (for Oracle databases), which is required by Envers.