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 time stamp>\SQLServer_create.sql or \Oracle10g_<export time stamp>\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.

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 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 ZERO constraint and 0 as a default value.
  - All date fields have a NOT ZERO 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 ZERO 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 default values nor 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. This index type also defines a constraint (or, in MSSQL, a trigger for update and delete functionality).
  - 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.