Skip to content

Database schema importer

The Versio.io database schema importer enables importing and monitoring database schemas with their tables and views. These imports allow you to document and detect changes to ensure auditability.

Supported database management systems (DBMS)

The database schema importer supports the following relational database management systems:

  • Oracle database
  • Microsoft SQL server
  • MySQL
  • MariaDB
  • PostgreSQL

Configuration

You can find the configuration option in Versio.io at Environment settings > OneImporter > Configurations > Database schema.

Here you can edit all existing configurations, create new configurations or delete existing configurations. Each configuration can import multiple databases from a single DBMS. To add a new configuration enter a Name and click Add to proceed to the configuration. To edit an existing configurations click the arrow on the left of your importer name to expand the configuration options.

Following configuration options are available:

Field Description Advice
Name Meaningful name Include database name/type
Active Option to activate and deactivate the configuration. Data is only imported if the configuration is activated.
Database type Select required DBMS type
Host or IP Host name or IP address of the DBMS
Port Listening port of the DBMS The default port is set automatically
Username User name for DBMS access
Password Password for DBMS access
Database name or SID Name or SID of the databases Only databases located in the above DBMS can be specified
Entity name for database instances Name of the entity that database instances are assigned to Default value is database. Please use your own entity names only if you are familiar with the configuration of entities and entity groups.
Entity name for database table instances Name of the entity that database instances are assigned to Default value is database-table. Please use your own entity names only if you are familiar with the configuration of entities and entity groups.
Entity name for database view instances Name of the entity that database instances are assigned to Default value is database-view. Please use your own entity names only if you are familiar with the configuration of entities and entity groups.
Executing OneImporter Select a Versio.io OneImporter to import the data based on the importer configuration. Requires installed OneImporter: Installation instruction
Schedule time (cron format) Determine the frequency of imports in CRON format Learn more about CRON format: https://en.wikipedia.org/wiki/Cron

Table: Database schema configuration parameter

 

The following image is an example of a database schema configuration:

Versio.io database schema importer configuration Figure: Example of a Versio.io schema configuration

Database user rights

The following database statements are executed by the importer to determine the database schema (tables, views, indexes) depending on the database management system used. The database user to be configured must have the appropriate rights to execute these statements:

Database management system Executed SQL statements
Oracle Database SELECT table_name FROM all_tables WHERE owner= 'USERNAME'
SELECT * FROM sys.all_views WHERE owner = 'USERNAME'
SELECT * FROM all_tab_columns WHERE table_name= 'table-name' AND owner= 'USERNAME'
SELECT * FROM all_ind_columns WHERE table_owner= 'USERNAME' AND table_name = 'table-name'
Microsoft SQL Server SELECT * FROM sys.tables
SELECT * FROM INFORMATION_SCHEMA.VIEWS
exec sp_columns table-name
SELECT * FROM sys.indexes WHERE is_hypothetical = 0 AND index_id != 0 AND object_id = OBJECT_ID('table-name')
MySQL + MariaDB SHOW TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = table-name
SHOW FULL COLUMNS FROM table-name
SHOW INDEXES FROM table-name IN database-name
PostgreSQL SELECT * FROM pg_catalog.pg_tables WHERE schemaname= 'public'
SELECT * FROM information_schema.views WHERE table_schema = 'public'
SELECT * FROM information_schema.COLUMNS WHERE table_name= 'table-name'
SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'table-name'

Examples

The Versio.io OneImporter imports the database schema based on a specific configuration and stores the different instances in entities defined by you (default: databases, database-tables and database-view). To view the imported instances in Versio.io, select 'Asset & Configuration Repository → Instance Viewer'. There you can select your specific instance and click it for a detailed view in the instance history viewer.

The following two images are examples of an imported database schema:

Versio.io database instance overview
Figure: Overview of Versio.io database instance (left) and topology of related table instances (right)

 

Versio.io database table instance overview Figure: Overview of Versio.io database table instance (left) and topology of related database instance (right)