Customizing
Customizing
Breadcrumbs

Schema Export

Information

  • You have the option to export the current database definition (data tables, columns, constraints, etc.) 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-Zeitstempel>\SQLServer_create.sql or \Oracle10g_<Export-Zeitstempel>\Oracle10g_create.sql.

  • 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.sql 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.

Note

  • 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 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 Activated 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 are in front of the UUID field and are defined at the end of the data table.

    • Default values and NOT ZERO constraints:

      • All columns of the primary key imply a NOT ZERO 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 a 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 ZERO 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.