Data Modeler (DM) 4.0.3 (and all earlier versions) allows “Before Create”, “After Create” and “End of Scripts” scripts to be defined at table level and they will be included in generated DDL if related option is set. DM 4.0.3 introduced support for some variables to be used thus allowing building of templates - {table}, {table abbr}, {pk name}, {view}, {schema}, {long name}. Still there is a drawback that those scripts need to be added to tables.
DM 4.1 approached the problem in different way - was added support transformation scripts to be used at specific points of DDL generation for table – “Before Create”, “Instead of Create”, “After Create” and “End of Script”.
If “Instead of Create” script is defined it’ll suppress built-in DDL generation for table and its components so DDL generation for non supported databases can be implemented.
Sets of such quadruples of scripts can be defined and one of them could be set as active – if no one is active then there is no custom DDL generation.
The dialog is activated from “Tools>Design rules and Transformations>Table DDL Transformations”. It allows writing, testing and debugging scripts. Table can be selected for “Test” and DDL preview window will pop-up and shows resulting DDL. There is no DDL preview in “Debug” mode.
Following variables are available to script during execution:
- model - relational model instance;
- pModel - physical model instance;
- table - the table in relational model;
- tableProxy - table definition in physical model;
- ddlStatementsList - should be used to return the list with DDL statements that are created by script – each DDL statement should be added as is shown below:
ddlStatementsList.add(new java.lang.String(ddl));
There is a new property on table defined “Use DDL Transformation Script” and if it’s set then table will be included in custom DDL generation. That property can be set in table properties dialog or in “DDL generation options”
DM 4.1 is distributed with “After create” script that generates journal table and related supporting trigger providing 2 DDL statements for each processed table:
The script:
/*
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script - as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
- model - relational model instance
- pModel - physical model instance
- table - the table in relational model
- tableProxy - table definition in physical model
*/
var ddl;
var lname;
//journal table name suffix
jnTabSuf = "_JN";
// trigger name suffix
jnTrgSuf = "_JNtrg";
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
if(useSchema){
lname = tableProxy.getLongName();
}else{
lname = tableProxy.getName();
}
}else{
if(useSchema){
lname = table.getLongName();
}else{
lname = table.getName();
}
}
if(prompt){
ddl= "PROMPT Creating Journal Table for '"+lname+"';\\n";
}else{
ddl = "";
}
ddl = ddl + "CREATE TABLE "+lname+jnTabSuf+"\n"+
" (JN_OPERATION CHAR(3) NOT NULL"+"\n"+
" ,JN_ORACLE_USER VARCHAR2(30) NOT NULL"+"\n"+
" ,JN_DATETIME DATE NOT NULL"+"\n"+
" ,JN_NOTES VARCHAR2(240)"+"\n"+
" ,JN_APPLN VARCHAR2(35)"+"\n"+
" ,JN_SESSION NUMBER(38)"+"\n";
cols = table.getElements();
for(var i=0;i<cols.length;i++){
col = cols\[i\];
ddl = ddl +" ,"+col.getName()+" "+col.getDatatypeString();
if(col.isMandatory()){
ddl = ddl + " NOT NULL\\n";
}else{
ddl = ddl + "\\n";
}
}
ddl = ddl + " );";
ddlStatementsList.add(new java.lang.String(ddl));
if(prompt){
ddl= "\\nPROMPT Creating Journal Trigger for '"+lname+"';\\n";
}else{
ddl = "\\n";
}
ddl = ddl +
"CREATE OR REPLACE TRIGGER "+lname+jnTrgSuf +"\n"+
" AFTER \n" +
" INSERT OR \n" +
" UPDATE OR \n" +
" DELETE ON emp2 for each row \n" +
" Declare \n" +
" rec "+lname+"_JN"+"%ROWTYPE; \n" +
" blank "+lname+"_JN"+"%ROWTYPE; \n" +
" BEGIN \n" +
" rec := blank; \n" +
" IF INSERTING OR UPDATING THEN \n" ;
for(var i=0;i<cols.length;i++){
col = cols\[i\];
ddl = ddl + " rec."+col.getName()+" := :NEW."+col.getName()+"; \\n";
}
ddl = ddl +
" rec.JN_DATETIME := SYSDATE; \n" +
" rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); \n" +
" rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE'); \n" +
" rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID'); \n" +
" IF INSERTING THEN \n" +
" rec.JN_OPERATION := 'INS'; \n" +
" ELSIF UPDATING THEN \n" +
" rec.JN_OPERATION := 'UPD'; \n" +
" END IF; \n" +
" ELSIF DELETING THEN \n" ;
for(var i=0;i<cols.length;i++){
col = cols\[i\];
ddl = ddl + " rec."+col.getName()+" := :OLD."+col.getName()+"; \\n";
}
ddl = ddl +
" rec.JN_DATETIME := SYSDATE; \n" +
" rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); \n" +
" rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE'); \n" +
" rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID'); \n" +
" rec.JN_OPERATION := 'DEL'; \n" +
" END IF; \n" +
" INSERT into "+lname+"_JN"+" VALUES rec; \n" +
" END; \n" +
" /"
ddlStatementsList.add(new java.lang.String(ddl));
The transformation script also shows the usage of some settings in preferences - "Include Prompt" and 'Include Schema"
And here is the script generated In "DDL Preview" for table COUNTRIES:
CREATE TABLE HR.COUNTRIES
(
COUNTRY\_ID CHAR (2 BYTE)
CONSTRAINT COUNTRY\_ID\_NN NOT NULL ,
COUNTRY\_NAME VARCHAR2 (40 BYTE) ,
REGION\_ID NUMBER ,
CONSTRAINT COUNTRY\_C\_ID\_PK PRIMARY KEY ( COUNTRY\_ID )
)
ORGANIZATION INDEX
PCTTHRESHOLD 50
;
/* ... other statements from generation ...*/
CREATE TABLE HR.COUNTRIES_JN
(JN_OPERATION CHAR(3) NOT NULL
,JN_ORACLE_USER VARCHAR2(30) NOT NULL
,JN_DATETIME DATE NOT NULL
,JN_NOTES VARCHAR2(240)
,JN_APPLN VARCHAR2(35)
,JN_SESSION NUMBER(38)
,COUNTRY_ID CHAR (2 BYTE) NOT NULL
,COUNTRY_NAME VARCHAR2 (40 BYTE)
,REGION_ID NUMBER
);
CREATE OR REPLACE TRIGGER HR.COUNTRIES_JNtrg
AFTER
INSERT OR
UPDATE OR
DELETE ON emp2 for each row
Declare
rec HR.COUNTRIES_JN%ROWTYPE;
blank HR.COUNTRIES_JN%ROWTYPE;
BEGIN
rec := blank;
IF INSERTING OR UPDATING THEN
rec.COUNTRY\_ID := :NEW.COUNTRY\_ID;
rec.COUNTRY\_NAME := :NEW.COUNTRY\_NAME;
rec.REGION\_ID := :NEW.REGION\_ID;
rec.JN\_DATETIME := SYSDATE;
rec.JN\_ORACLE\_USER := SYS\_CONTEXT ('USERENV', 'SESSION\_USER');
rec.JN\_APPLN := SYS\_CONTEXT ('USERENV', 'MODULE');
rec.JN\_SESSION := SYS\_CONTEXT ('USERENV', 'SESSIONID');
IF INSERTING THEN
rec.JN\_OPERATION := 'INS';
ELSIF UPDATING THEN
rec.JN\_OPERATION := 'UPD';
END IF;
ELSIF DELETING THEN
rec.COUNTRY\_ID := :OLD.COUNTRY\_ID;
rec.COUNTRY\_NAME := :OLD.COUNTRY\_NAME;
rec.REGION\_ID := :OLD.REGION\_ID;
rec.JN\_DATETIME := SYSDATE;
rec.JN\_ORACLE\_USER := SYS\_CONTEXT ('USERENV', 'SESSION\_USER');
rec.JN\_APPLN := SYS\_CONTEXT ('USERENV', 'MODULE');
rec.JN\_SESSION := SYS\_CONTEXT ('USERENV', 'SESSIONID');
rec.JN\_OPERATION := 'DEL';
END IF;
INSERT into HR.COUNTRIES\_JN VALUES rec;
END;
/
Philip Stoyanov