Ispirer Toolkit: Schema Mapping

Each database has a different organization structure. For example, in Oracle, the schema and the user are the same, PG databases allow to create your own schemas in each database, in MySQL the database and the schema are the same. It is often difficult to understand how to change the organization of the database during the conversion process.

To do this, our tool uses the option “Remove schema names in SQL objects”. This option allows to choose how the schema name will change during the conversion process.

There are 3 possible values for this option:

• Yes. In the process of converting all objects, the schema name will be deleted – all SQL files with CREATE statements will be generated, where the schema name will not be added. This value is set up as default value.
• No. During the conversion process, all schema names from the source database will be saved.
• Default. During the conversion, all schema names from the source database will be saved, and the default schema names for the database will be mapped (for example, for the SQL Server - PG direction, the dbo schema will be converted to public).

For example, let's take two objects from SQL Server and see how they will be converted if different options are used.

Source objects:

CREATE TABLE dbo.employees_tab
(
  emp_id INT,
  department VARCHAR(22),
  name VARCHAR(30),
  age INT,
  salary INT
)

create procedure schema1.salary_increase @dep_id varchar(22), @name varchar(30), @amount integer
as
UPDATE dbo.employees_tab SET salary = salary + @amount where department=@dep_id and name=@name

Now let’s compare the conversion results to PostgreSQL database.

• Option value Yes:

CREATE TABLE employees_tab
(
   emp_id INTEGER,
   department VARCHAR(22),
   name VARCHAR(30),
   age INTEGER,
   salary INTEGER
);

create or replace PROCEDURE salary_increase(v_dep_id VARCHAR(22), v_name VARCHAR(30), v_amount INTEGER)
LANGUAGE plpgsql
   AS $$
BEGIN
   UPDATE employees_tab SET salary = salary+v_amount where department = v_dep_id and name = v_name;
END; $$;

• Option value No:

CREATE TABLE dbo.employees_tab
(
   emp_id INTEGER,
   department VARCHAR(22),
   name VARCHAR(30),
   age INTEGER,
   salary INTEGER
);

create or replace PROCEDURE schema1.salary_increase(v_dep_id VARCHAR(22), v_name VARCHAR(30), v_amount INTEGER)
LANGUAGE plpgsql
   AS $$
BEGIN
   UPDATE dbo.employees_tab SET salary = salary+v_amount where department = v_dep_id and name = v_name;
END; $$;

• Option value default:

CREATE TABLE employees_tab
(
   emp_id INTEGER,
   department VARCHAR(22),
   name VARCHAR(30),
   age INTEGER,
   salary INTEGER
);

create or replace PROCEDURE schema1.salary_increase(v_dep_id VARCHAR(22), v_name VARCHAR(30), v_amount INTEGER)
LANGUAGE plpgsql
   AS $$
BEGIN
   UPDATE employees_tab SET salary = salary+v_amount where department = v_dep_id and name = v_name;
END; $$;

Also, if you select DEFAULT and Yes, you can specify a new schema (“Change schema names to” option) in which all source objects from all schemas will be created.

Please note that our tool does not create schemas in the target database. In case you use No or Default - you need to manually create all non-default schemas in the target database before converting. Therefore, for various tests, we recommend using the Yes option. in such a case, the object will be created in the default schema in the target database and there will definitely not be any problems with the absence of one or another schema in the database.

How to Change Objects Names During Migration

More Tips and Tricks

If you have any questions or face any difficulties, please contact our support team at support@ispirer.com