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 global export_path_databasescheme setting under Customizer → Master Data → Global Settings in the Template Code data field.

Procedure

Select Customizer → Databases → Database Schema Export in order to initiate a schema export.
- A writable path must be entered for the worker container, e.g. /var/planta
- 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.

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.

Export via docker compose

The schema or data export can also be opened via the migration process using docker compose. See also Migration Process

Structure example:

docker compose

CODE

services:
  manager:
    image: registry.planta.services/project/manager:stable
    environment:
      - "SKIP_INIT=1"
      - "planta__server__hibernate__connection__url=<DB_CONNECTION_URL>"
      - "planta__server__hibernate__connection__username=<DB_USERNAME>"
      - "planta__server__hibernate__connection__password=<DB_PASSWORD>"
    command: ["migration", "-c /planta/parameter.par"]
    depends_on:
      customizing:
        condition: service_completed_successfully
        restart: true
    networks:
      - internal
    volumes:
      - transfer:/mnt/transfer:ro
      - ./data_export.par:/planta/data_export.par:ro
      - ./:/var/planta/export:rw

  worker:
    image: registry.planta.services/project/worker:stable
    environment:
      - "SESSION_LINK=manager:54242"
    depends_on:
      manager:
        condition: service_healthy
        restart: true
      customizing:
        condition: service_completed_successfully
        restart: true
    networks:
      - internal
    volumes:
      - transfer:/mnt/transfer:ro
      - ./export.par:/planta/parameter.par:ro

  customizing:
    image: registry.planta.services/project/customizing:stable
    environment:
      - LOG_LEVEL=INFO
    volumes:
      - transfer:/mnt/transfer:rw

networks:
  internal:
volumes:
  transfer:

export.par

CODE

--export-data
    /planta/data_export.par 
--export-schema
    /var/planta/export/schema

data_export.par

CODE

--exclude-licenses
    ZZZ

--output-file
    export/data

--exclude-tables
    MIGRATIONRULE
    MIGRATIONHISTORY
    DT153
    DT348
    DT396
    DT547
    DT953
    DT820
    DT004
    DT936