Generate XML/ SQL output with data types of target

Question

I need to convert the structure of PostgreSQL databases to Oracle. In PostgreSQL, I have a postgres database with data.

In Oracle I have a blank database in which I want to write postgres database which in PostgreSQL.

In fact, I do not need the data, only the structure (relationships).

For this I use Liquibase. I get the changelog from PostgreSQL with the command:

liquibase \
   --driver=org.postgresql.Driver \
   --classpath="C:\db_drivers\postgresql-9.3-1102.jdbc3.jar" \
   --changeLogFile="./postgresql_changelog.xml" \
   --url="jdbc:postgresql://localhost:5432/postgres" \
   --username=schema_name_here \
   --password=************** \ 
   --logLevel=debug \ 
   --defaultSchemaName=sep \
   generateChangeLog

After this I try to create objects in the Oracle database:

liquibase \
   --driver=oracle.jdbc.OracleDriver \
   --classpath="C:\db_drivers\ojdbc14.jar" \
   --changeLogFile="./postgresql_changelog.xml" \
   --url="jdbc:oracle:thin:@ip_here:orabeta" \
   --username=*** \
   --password=*** \
   update

Does not work: ORA-00902

Here is a fragment of postgresql_changelog.xml:

...
<changeSet author="Alexey (generated)" id="1409146335011-53">
   <createTable tableName="TABLE1A">
      <column name="total_pk" type="INT8">
         <constraints nullable="false"/>
      </column>
      <column name="form_fk" type="INT8">
         <constraints nullable="false"/>
      </column>
...

I also generate a pure SQL- file:

liquibase \
   --driver=oracle.jdbc.OracleDriver \
   --classpath="C:\db_drivers\ojdbc14.jar" \
   --changeLogFile="./postgresql_changelog.xml" \
   --url="jdbc:oracle:thin:@ip_here:orabeta" \
   --username=*** \
   --password=*** \
   updateSQL > update.sql

Here is a fragment of update.sql:

...
CREATE TABLE SCHEMA_HERE.TABLE1A (total_pk INT8 NOT NULL, form_fk INT8, .....etc );
INSERT INTO SCHEMA_HERE.TABLE1A (ID, FORM_ID, ...etc)
...

I would like to generate the file, in which all data types correspond to the target database, ie that I want to create. I can write a simple parser that replace data types, but it is not the right solution - can be many database.

It possible to generate XML/ SQL output with data types of target database?

Or maybe there is an option that allow to generate output with "abstract" data types? Ie with the data types that are not in the real databases, for example, instead of INT8 - abstract integer data type, etc.

I would be very grateful for the information. Thanks to all.

Answer 1

Actually, it is necessary to manually correct the data types in the generated file (and not only the data types, but also all specifics of the database(reduce the length of the constraint names, indexes, etc.). Data types are not converted automatically.

For example:

...
<changeSet author="Alexey (generated)" id="1409146335011-53">
   <createTable tableName="TABLE1A">
      <!-- replace INT8 to NUMBER(16) for Oracle (for example) -->
      <column name="total_pk" type="INT8">           
         <constraints nullable="false"/>
      </column>
      <!-- replace INT4 to NUMBER(10) for Oracle (for example) -->
      <column name="form_fk" type="INT4"> 
         <constraints nullable="false"/>
      </column>   
...

After that execute the command:

liquibase \
   --driver=oracle.jdbc.OracleDriver \
   --classpath="C:\db_drivers\ojdbc14.jar" \
   --changeLogFile="./postgresql_changelog.xml" \
   --url="jdbc:oracle:thin:@ip_here:orabeta" \
   --username=*** \
   --password=*** \
   update

The structure will be generated on the target database.

But, really, can be used "abstract" data types, as write in the documentation: Liquibase, Column tag

To help make scripts database-independent, the following “generic” data types will be converted to the correct database implementation: BOOLEAN CURRENCY UUID CLOB BLOB DATE DATETIME TIME BIGINT

Also, specifying a java.sql.Types.* type will be converted to the correct type as well. If needed, precision can be included. Here are some examples: java.sql.Types.TIMESTAMP java.sql.Types.VARCHAR(255)

And possible to make the copy of structure without DDL (and data without DML).

Is similarly possible migrate the data (option --diffTypes = "data"):

liquibase \
   --driver=org.postgresql.Driver \
   --classpath="C:\db_drivers\postgresql-9.3-1102.jdbc3.jar" \
   --changeLogFile="./data.xml" \
   --url="jdbc:postgresql://localhost:5432/postgres" \
   --username=*** \
   --password=*** \
   --logLevel=debug \ 
   --defaultSchemaName=schema_name_here \
   --diffTypes="data" \
   generateChangeLog 

And after temporary disabling all constraints (manually):

liquibase \
   --driver=oracle.jdbc.OracleDriver \
   --classpath="C:\db_drivers\ojdbc14.jar" \
   --changeLogFile="./data.xml" \
   --url="jdbc:oracle:thin:@ip_here:orabeta" \
   --username=*** \
   --password=*** \
   --logLevel=debug \ 
   update