0

Reverse-engineering DDL and wondering "one possibility" in Hackolade

ZHOU PU

October 19, 2020 13:02

None

Follow

Hello Pascal,

I have two questions about some fonctionnalities in Hackolade.

Q1: I tried to import a file DDL of Oracle to have my data model. But for the tables which have the composite keys as PK, I can't get the primary key automatically.

The DDL I used as below:

CREATE TABLE "CONTRAT"
( "ID_CONTRAT" VARCHAR2(30 BYTE),
"DT_DEBUT" DATE,
"DT_FIN" DATE,
"LB_TYPE" VARCHAR2(30 BYTE),
"LB_DESCRIPTION" VARCHAR2(500 BYTE),
"FL_ACTIF" NUMBER(1,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_DWH" ;

ALTER TABLE "DWH"."D_DETAIL_INSTALLATION" MODIFY ("ID_CONTRAT" NOT NULL ENABLE);
ALTER TABLE "DWH"."D_DETAIL_INSTALLATION" MODIFY ("DT_DEBUT" NOT NULL ENABLE);
ALTER TABLE "DWH"."D_DETAIL_INSTALLATION" ADD PRIMARY KEY ("ID_CONTRAT", "DT_DEBUT", "LB_TYPE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_DWH_I" ENABLE;

In addition, for the column with type "Date" in Oracle target, even I do not have the possibility to choose it as primary key by hand. But the choice exists for other types.

 

Q2 : I wonder if we can create a relation between two tables by "homonymie".

Can I create a relation when a primary key of one table is detected that it exists as a field in another table? The detection is made by the same fields which have the same name, the same type and the same size.

For example, 

I have two tables. Each has their primary key. The field "code_pays" is the primary key in table "pays". In table "film", there is also a field "code_pays" with the same name, type and size. So can I create a relation between "pays" and "film" directly by clicking a button or right click ?

Because I have lots of tables in my data model, it will take a lot of time to do this if by hand. I am looking forward to this fonctionnality.

Thank you very much in advance if you can give some ideas about these questions. Thanks :)

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Helpdesk October 19, 2020 13:53 Official comment

  Hello Zhou,

  Thanks for the details which we had been asking in the response to the ticket you open with support.  Did you receive our response to your support ticket?

  One issue seems to be that you're using a plugin for Oracle target that is not released.  It is an old version dating from several years ago which has not been maintained.  And we do not have an official Oracle plugin that has been released at this point.  It is planned for the future, but there is no release date announced at this stage.

  Nevertheless, it seems that we need to add support for composite keys in ALTER statements when doing reverse-engineering of Oracle DDLs.  We will add this ASAP.  I'll keep you posted.

  Q2: we do not have this feature.  It would be preferable if your Foreign Key relationships were declared in your DDLs, so we could import them.

   

• 0
  
  Helpdesk October 26, 2020 09:29

  Hello Zhou,

  The engineers have looked into your request.  As it turns out, the DDL ALTER statements were not correct and should be:

  ALTER TABLE "DWH"."CONTRAT" MODIFY ("ID_CONTRAT" NOT NULL ENABLE);
  ALTER TABLE "DWH"."CONTRAT" MODIFY ("DT_DEBUT" NOT NULL ENABLE);
  ALTER TABLE "DWH"."CONTRAT" ADD PRIMARY KEY ("ID_CONTRAT", "DT_DEBUT", "LB_TYPE")

  This only solves part of the problem.  And there are more things we can fine-tune in the coming days.  Those enhancements will be included in the next release.  Best regards

• 0
  
  ZHOU PU October 29, 2020 14:10

  Thank you very much for your reply.

  You are right. I made a mistake when I made copy-paste of DDL ALTER statements and the Oracle plugin is what I found from your GitHub repo. Looking forward to the next release of Hackolade. Thanks:)

  About the Q2, depending on certain constraints on the client side, we can't have relations of foreign keys in our DDL. So I wonder if this feature will be considered in the future plan ? It's just a proposal for the helpful features.

• 0
  
  Helpdesk November 09, 2020 08:06

  As mentioned in your helpdesk ticket, the software was enhanced to the DDL syntax you submitted.

  Best regards