Import Database DDL in H2

[Jaden]

Hello,

For unit testing purposes, I would like to import the oracle
development database schema structure ONLY into an in-memory H2
database(oracle compatible).

I believe oracle's import utility will not work in this case....

How do I do this? Please advise.

Jaden

[David Cuthbert]

On Jun 18, 7:58 pm, Jaden <mail.me.ja...@gmail.com> wrote:
> For unit testing purposes, I would like to import the oracle
> development database schema structure ONLY into an in-memory H2
> database(oracle compatible).

I've done this by examining the Oracle data dictionary views and
generating DDL programatically. (I wish I could share the code; alas,
it's confidential.)

Start by keeping the Oracle Database Reference guide handy:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/toc.htm

DBA_TAB_COLUMNS will tell you the basic structure of each table.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_2091.htm#I1020277
COLUMN_ID gives the ordering of the columns.
COLUMN_NAME, DATA_TYPE and NULLABLE ('Y' or 'N') are self-
explanatory. Be sure to rename VARCHAR2 to VARCHAR; you might also
want to translate DATE to TIMESTAMP in H2 (since Oracle DATEs can have
times associated with them).
DATA_PRECISION and DATA_SCALE are relevant for NUMBER types. (Some
columns show up with DATA_PRECISION==0; in these cases, you should
just create the H2 version as NUMBER.)
CHAR_LENGTH and CHAR_USED are relevant for CHAR/NCHAR/VARCHAR2/
NVARCHAR2 types. CHAR_USED is 'B' if CHAR_LENGTH is in bytes, 'C' if
it's in characters.

Then query DBA_CONSTRAINTS to find out what else should be applied to
the table.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_1044.htm#i1576022

I start by looking at primary keys (CONSTRAINT_TYPE='P'); join it with
DBA_CONS_COLUMNS:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_1042.htm#i1575870
SELECT cc.column_name
FROM dba_constraints c
INNER JOIN dba_cons_columns cc
ON c.constraint_name = cc.constraint_name
WHERE c.owner=?
AND c.table_name=?
AND c.constraint_type='P'
ORDER BY cc.position

Do the same for unique keys (CONSTRAINT_TYPE='U').

Foreign keys (CONSTRAINT_TYPE='R') are a pain. You first need to grab
DBA_CONS_COLUMNS for the child table. R_CONSTRAINT_NAME then points
you at a primary or unique key constraint for the parent table;
R_OWNER is the owner of the table. However, you won't know the name
of the table until you query DBA_CONS_COLUMNS for R_CONSTRAINT_NAME.

I haven't implemented check constraints yet, but you'll look in
DBA_CONSTRAINTS' SEARCH_CONDITION column for this information.

Hope this gives you a pointer in the right direction. Apologies,
again, that I can't provide actual code here.

[Thomas Mueller]

Hi,

Do you have a SQL script to create the Oracle database? If yes you
could use that (it's probably the easiest solution).

There is a Oracle specific way to extract the SQL script from an
existing Oracle database, using the exp / imp command line tools, but
I forgot not sure how to do that exactly. And most likely, you would
have to modify this script (remove the tablespace syntax and so on).

Somewhat related: you can link the Oracle tables from within H2
(CREATE LINKED TABLE).

Regards,
Thomas

[Zvonko]

Hi Jaden,

If you want to do it on programatic way, here are some tips (very
briefly) how I did it.

The most simple way to get oracle ddl is using
DBMS_METADATA.GET_DDL(obj_type, obj_name, obj_schema) for tables and
views, and BMS_METADATA.GET_DEPENDENT_DDL(obj_type, obj_name,
obj_schema) for table associated constraints, reference constraints,
indices and triggers.

Before calling these functions you must strip off from ddl some
special oracle attributes (storage, segment, tablespace, etc) using
DBMS_METADATA.SET_TRANSFORM_PARAM(). Please see Oracle documentation
for details. You can also use DBMS_METADATA.SET_TRANSFORM_PARAM() to
strip off reference constraints and constraints from CREATE TABLE
statement returned by DBMS_METADATA.GET_DDL() and separate it into
extra DDL you can get using BMS_METADATA.GET_DEPENDENT_DDL().


Ddl's you get on this way need some extra editing: there are some
statements which does not exist in H2 sintax (ENABLE, DISABLE,
NOCHECK, statement MODIFY which have to be replaced with proper ALTER
COLUMN, etc ...).
It is also recomended to replace column type "DATE" with "DATETIME",
because DATE in java does not contain hours / min / sec, and Oracle
DATE does.

Maybe you'll have to change some view DDL scripts if there is no
explicite schema in subordinated table names. Depending on your oracle
table description, you may find out some other replacements in ddll
scripts are necessary too.

When creating H2 tables, it is recomended to create table without
prime key, indices and reference constraints, populate tables with
data, and then create primary key and indices. This is the fastest
way. Reference constraints you must create at end, when all tables
are created, because all H2 indices on all tables must be already
created at that time (to prevent unecessary index creation - when
creating foreign keys, H2 is creating a pair of indices too - if they
already exists, they will use existing). Loading tables without
indices is very fast in H2, but creating indices after that will take
a while on huge tables.

This is just a brief information how to do it, but java code doing it
is not a trivial program, you will spent some time working on it
(maybe too much work just for testing).

Best regards,
Zvonko

[gilbertoca]

At the end of this post[1] you will find the script to extract object
definition and their grants.
Regards,
Gilberto

[1]http://blog.gilbertoca.com/?p=338