-- 추가 개발 요청 및 필요 사항 2012.05.24
1. 기존 작성된 Interface 수정이 가능 했으면 한다.
- 전체 매핑 수정 OK!
사유: 전체 쿼리가 너무 긴 경우는 한번에 인식하는게 불가능하므로, 이 때는 등록은 별도로 하고, 매핑 업데이트만 수행해서 원하는 인터페이스를 생성하도록 구현
- 생성된 Interface에 Dataset 추가 OK!
사유: 현재 버전에서는 데이터 셋을 추가해서 한번에 interface를 생성하는 것이 불가능하다. 복잡도가 어느 정도 일지 판별하기가 어렵기 때문에 제외된 사항으로, 이 문제점을 해결하기 위한 중간 해결책으로 생성된 interface의 dataset을 add 하는 기능 추가
2. 현재 버전 오류 사항 수정
- 한글 깨짐
사유: 쿼리를 등록하면 encoding 이 달라 한글이 깨짐
ODI provides SDK, so we can make some brilliant function for ODI.
This is the one of these.
What this code can do is that sql query is transformed to ODI Interface.
So, we called this is ODI Interface Interpreter.
this has been still working since 2012.01.01.
Attached is the "ODI-SDK connect and create interface" part of whole code.
the is consist of two parts.
"Web and Core."
Web connects with a client, inputs sql, parses it, structure it as ODI-SDK(core) can understand, and send it to core.
Core gets objects related with ODI Interface from Web, connects ODI Repository, makes Interface as wished.
it can show the possibilities anyone can handle ODI easily and make the job exactly as aimed.
But it is not working perfectly now.
I mean any SQL structures like 1:1, join, mapping, inline view, subquery, dataset(union, minus) and etc is transformed by it. We will make it someday sooner or later, but not now.
What It can do is:
1:1 mapping ok.
custom mapping ok.
source tables join ok.
filter ok.
group by ok.
Then what is "What can it not do".
Restrictions of this version is:
inline view not yet (but it can handle the subquery, you may know what i mean).
dataset not yet.
At last, you can use this code as you wish, and when updating please let me know so that I can fix it as you do.
And if you cannot understand this code but want to use it, let me know as write reply or email me.
And plz let us share opinions.
thanks for reading this then.
email: jeonghyeoncheon@gmail.com
2011.12.28.
package odi.widetns;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.domain.model.OdiModel;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.model.finder.IOdiModelFinder;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiIKM;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiInterface.ExecutionLocation;
import oracle.odi.domain.project.OdiKM;
import oracle.odi.domain.project.OdiLKM;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiIKMFinder;
import oracle.odi.domain.project.finder.IOdiLKMFinder;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.project.interfaces.DataSet;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.domain.topology.finder.IOdiLogicalSchemaFinder;
import oracle.odi.interfaces.TargetIsTemporaryException;
import oracle.odi.interfaces.basic.NoSourceSetException;
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddFilter;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddJoin;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnStagingAreaSetLogicalSchema;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetDataStoreComputeAutoMapping;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetMappingSetSql;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetTargetDataStore;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM.KMType;
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerDoubleChecker;
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerFixed;
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterLazy;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerLazy;
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyLazy;
import oracle.odi.interfaces.interactive.support.targetkeychoosers.TargetKeyChooserPrimaryKey;
public class OdiWideTNSInterfaceInterpreter extends OdiWideTNSRepository {
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/**************** 00. Interpreter Key Dictionary ******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
final private String R_SCHEMA = "M";
final private String R_SOURCES = "S";
final private String R_DESTINATION = "D";
final private String R_TABLE = "T";
final private String R_FILTER = "F";
final private String R_JOIN = "J"; // --> JOIN AND FILTER
final private String R_COLUMNS = "C";
final private String R_ALIAS = "A";
final private String R_LKM = "LKM";
final private String R_IKM = "IKM";
final private String M_PROJECT = "MP";
final private String M_FOLDER = "MF";
final private String M_PROJECTANDFOLDER = "MPF";
final private String M_LOGICALSCHEMA = "ML";
final private String M_MODEL = "MM";
final private String M_LKM = "MLKM";
final private String M_IKM = "MIKM";
final private String M_KM = "MKM";
final private String K_STGSCHEMA = "G"; // --> Differenct Staging Area
final private String K_DESC = "DESC";
final private String K_TRGMODEL = R_DESTINATION + R_SCHEMA;
final private String K_TRGTABLE = R_DESTINATION + R_TABLE;
final private String K_TRGCOLUMN = R_DESTINATION + R_COLUMNS;
final private String K_SRCMODEL = R_SOURCES + R_SCHEMA;
final private String K_SRCTABLE = R_SOURCES + R_TABLE;
final private String K_SRCALIAS = R_SOURCES + R_TABLE + R_ALIAS;
final private String K_SRCCOLUMN = R_SOURCES + R_COLUMNS;
final private String K_SRCCOLUMNALIAS = R_SOURCES + R_COLUMNS + R_ALIAS;
final private String K_SRCFILTER = R_SOURCES + R_FILTER;
final private String K_SRCKM = R_SOURCES + R_LKM;
final private String K_TRGKM = R_DESTINATION + R_IKM;
private ITransactionStatus txnStatus;
private OdiInterface inf;
private OdiFolder folder;
private OdiContext cxt;
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/**************** 01. Calling WideTNS ODI Interface Interpreter ******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
public OdiWideTNSInterfaceInterpreter(String jdbcUrl, String driver,
String mstRepId, String mstRepPwd, String workrepName,
String odiUser, String odiPassword) {
super(jdbcUrl, driver, mstRepId, mstRepPwd, workrepName, odiUser,
odiPassword);
// TODO Auto-generated constructor stub
}
public Map<String, List<String>> getOdiMetadataForOdiII() {
// TODO Auto-generated method stub
Map<String, List<String>> m = new HashMap<String, List<String>>();
m.put(M_PROJECTANDFOLDER, getProjectAndFolderNames());
m.put(M_MODEL, getModelNames());
m.put(M_LOGICALSCHEMA, getLogicalSchemas());
m.put(M_LKM, getLKMNames());
m.put(M_IKM, getIKMNames());
return m;
}
@SuppressWarnings("unchecked")
public List<String> getProjectAndFolderNames() {
// TODO Auto-generated method stub
OdiProject p = null;
OdiFolder f = null;
List<String> l = new ArrayList<String>();
for (Iterator<OdiProject> iProject = ((IOdiProjectFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiProject.class))
.findAll().iterator(); iProject.hasNext();) {
p = iProject.next();
for (Iterator<OdiFolder> iFolder = ((IOdiFolderFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiFolder.class))
.findByProject(p.getCode()).iterator(); iFolder.hasNext();) {
f = iFolder.next();
l.add(p.getCode() + " - " + f.getName());
}
}
return l;
}
@SuppressWarnings("unchecked")
public List<String> getModelNames() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiModel> i = ((IOdiModelFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiModel.class))
.findAll().iterator(); i.hasNext();)
l.add(i.next().getCode());
return l;
}
@SuppressWarnings("unchecked")
public List<String> getLogicalSchemas() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiLogicalSchema> i = ((IOdiLogicalSchemaFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiLogicalSchema.class)).findAll().iterator(); i
.hasNext();)
l.add(i.next().getName());
return l;
}
@SuppressWarnings("unchecked")
public List<String> getIKMNames() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiIKM> i = ((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiIKM.class))
.findAll().iterator(); i.hasNext();)
l.add(i.next().getName());
return l;
}
@SuppressWarnings("unchecked")
public List<String> getLKMNames() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiLKM> i = ((IOdiLKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiLKM.class))
.findAll().iterator(); i.hasNext();)
l.add(i.next().getName());
return l;
}
public void setContext(String strContext) {
// TODO Auto-generated method stub
cxt = (((IOdiContextFinder) odiInstance.getTransactionalEntityManager()
.getFinder(OdiContext.class)).findByCode(strContext) != null ? ((IOdiContextFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiContext.class))
.findByCode(strContext) : ((IOdiContextFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiContext.class))
.findDefaultContext());
}
public OdiContext getContext() {
// TODO Auto-generated method stub
return (cxt != null ? cxt : ((IOdiContextFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiContext.class))
.findDefaultContext());
}
public void setFolder(String strFolder, String strProject) {
// TODO Auto-generated method stub
Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiFolder.class))
.findByName(strFolder, strProject);
folder = fold.iterator().next();
}
public OdiFolder getFolder() {
return folder;
}
private Map<String, Integer> checkKeys(Set<String> keySet) throws Exception {
// TODO Auto-generated method stub
Map<String, Integer> m = new HashMap<String, Integer>();
String str;
for (Iterator<String> iter = keySet.iterator(); iter.hasNext();) {
str = iter.next().replaceAll("[0-9]", "");
Integer freq = m.get(str);
m.put(str, (freq == null) ? 1 : freq + 1);
}
checkErrors(m);
return m;
}
private void checkErrors(Map<String, Integer> m) throws Exception {
if (m.size() < 5)
throw new Exception("somthing is missed!");
}
public String create(Map<String, String> mapInfInfo, String pProject,
String pFolder, String pInf, String pSrcModel, String pTrgModel,
String pContext) throws NoSourceSetException,
TargetIsTemporaryException {
// TODO Auto-generated method stub
setContext(pContext);
return create(mapInfInfo, pProject, pFolder, pInf, pSrcModel, pTrgModel);
}
public String create(Map<String, String> mapInfInfo, String pProject,
String pFolder, String pInf, String pSrcModel, String pTrgModel)
throws NoSourceSetException, TargetIsTemporaryException {
// TODO Auto-generated method stub
Map<String, Integer> mapCnt;
InteractiveInterfaceHelperWithActions interactiveHelper = null;
DataSet dataSet = null;
String strReturn = "";
txnStatus = startTransaction();
try {
mapCnt = checkKeys(mapInfInfo.keySet());
// Create Interface
setFolder(pFolder, pProject);
inf = new OdiInterface(getFolder(), pInf, getContext());
inf.setDescription(mapInfInfo.get(K_DESC)
+ "\n\nCreated by WideTNS ODI Interface Interpreter\n" + new Date());
System.out.println(new Date() + "***** Creating Interface");
strReturn += new Date() + "***** Creating Interface \n";
interactiveHelper = new InteractiveInterfaceHelperWithActions(
inf, odiInstance,
odiInstance.getTransactionalEntityManager());
// Set target table
interactiveHelper
.performAction(new InterfaceActionSetTargetDataStore(
((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiDataStore.class)).findByName(
mapInfInfo.get(K_TRGTABLE), pSrcModel
+ mapInfInfo.get(K_TRGMODEL)),
new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()));
System.out.println(new Date() + "***** Set target table");
strReturn += new Date() + "***** Set target table \n";
// Whether to use the different staging area
if(mapInfInfo.get(K_STGSCHEMA) != null)
{
interactiveHelper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(
((IOdiLogicalSchemaFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiLogicalSchema.class)).findByName(mapInfInfo.get(K_STGSCHEMA))));
}
dataSet = inf.getDataSets().get(0);
// Add source table
for (int i = 0; i < mapCnt.get(K_SRCTABLE); i++) {
interactiveHelper
.performAction(new InterfaceActionAddSourceDataStore(
((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager()
.getFinder(OdiDataStore.class))
.findByName(
mapInfInfo.get(K_SRCTABLE + i),
pSrcModel
+ mapInfInfo
.get(K_SRCMODEL
+ i)),
dataSet,
(mapInfInfo.get(K_SRCALIAS + i) == null ? new AliasComputerDoubleChecker()
: new AliasComputerFixed(mapInfInfo
.get(K_SRCALIAS + i))),
new ClauseImporterLazy(),
new AutoMappingComputerLazy()));
}
System.out.println(new Date() + "***** Added source tables");
strReturn += new Date() + "***** Added source tables \n";
// Auto-mapping
interactiveHelper
.performAction(new InterfaceActionOnTargetDataStoreComputeAutoMapping());
// Customized mappings
for (int i = 0; i < mapCnt.get(K_TRGCOLUMN); i++) {
interactiveHelper
.performAction(new InterfaceActionOnTargetMappingSetSql(
mapInfInfo.get(K_TRGCOLUMN + i), mapInfInfo
.get(K_SRCCOLUMN + i), dataSet));
}
System.out.println(new Date() + "***** Customized mappings");
strReturn += new Date() + "***** Customized mappings \n";
// Filter ************ need to consider more specific cases
if (mapCnt.get(K_SRCTABLE) > 1)
interactiveHelper.performAction(new InterfaceActionAddJoin(
dataSet, mapInfInfo.get(K_SRCFILTER),
ExecutionLocation.SOURCE));
else
interactiveHelper.performAction(new InterfaceActionAddFilter(
dataSet, mapInfInfo.get(K_SRCFILTER),
ExecutionLocation.SOURCE));
// IKM Select
if (mapCnt.get(K_TRGKM) > 0) {
interactiveHelper.performAction(new InterfaceActionSetKM(
((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiIKM.class)).findByName(mapInfInfo
.get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
KMType.IKM, new KMOptionRetainerLazy()));
}
// LKM Select
if (mapCnt.get(K_SRCKM) > 0) {
// interactiveHelper.performAction(new InterfaceActionSetKM(
// ((IOdiIKMFinder) odiInstance
// .getTransactionalEntityManager().getFinder(
// OdiIKM.class)).findByName(mapInfInfo
// .get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
// KMType.IKM, new KMOptionRetainerLazy()));
strReturn += new Date() + " ***** Sorry! Selecting LKM is not provided in this version!! ";
}
// Check consistency
interactiveHelper.computeSourceSets();
interactiveHelper.preparePersist();
odiInstance.getTransactionalEntityManager().persist(inf);
// Commit
commitTransaction(txnStatus);
System.out.println(new Date() + "***** Created Interface");
strReturn += new Date() + "***** Created Interface \n";
} catch (Exception e) {
e.printStackTrace();
strReturn +=new Date() + "***** Error \n" + "\n"
+ e.getMessage();
// Roll back when the process failed
rollbackTransaction(txnStatus);
} finally {
close();
}
return strReturn;
}
package odi.widetns;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.domain.model.OdiModel;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.model.finder.IOdiModelFinder;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiIKM;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiInterface.DatabaseSchema;
import oracle.odi.domain.project.OdiInterface.ExecutionLocation;
import oracle.odi.domain.project.OdiLKM;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiIKMFinder;
import oracle.odi.domain.project.finder.IOdiLKMFinder;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.project.interfaces.DataSet;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.domain.topology.finder.IOdiLogicalSchemaFinder;
import oracle.odi.interfaces.TargetIsTemporaryException;
import oracle.odi.interfaces.basic.NoSourceSetException;
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddFilter;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddJoin;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnStagingAreaSetLogicalSchema;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetDataStoreComputeAutoMapping;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetMappingSetSql;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTemporaryTargetDataStoreAddColumn;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTemporaryTargetDataStoreSetName;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM.KMType;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetTargetDataStore;
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerDoubleChecker;
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerFixed;
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterLazy;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerColumnName;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerLazy;
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyLazy;
import oracle.odi.interfaces.interactive.support.targetkeychoosers.TargetKeyChooserPrimaryKey;
public class OdiWideTNSInterfaceInterpreter extends OdiWideTNSRepository {
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/**************** 00. Interpreter Key Dictionary ******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
final private String R_SCHEMA = "M";
final private String R_SOURCES = "S";
final private String R_DESTINATION = "D";
final private String R_TABLE = "T";
final private String R_FILTER = "F";
//final private String R_JOIN = "J"; // --> JOIN AND FILTER
final private String R_COLUMNS = "C";
final private String R_ALIAS = "A";
final private String R_LKM = "LKM";
final private String R_IKM = "IKM";
//final private String R_DATASET = "U";
//final private String R_SUBQUERY = "SQ";
//final private String M_PROJECT = "MP";
//final private String M_FOLDER = "MF";
final private String M_PROJECTANDFOLDER = "MPF";
final private String M_LOGICALSCHEMA = "ML";
final private String M_MODEL = "MM";
final private String M_LKM = "MLKM";
final private String M_IKM = "MIKM";
//final private String M_KM = "MKM";
final private String K_LOGICALSCHEMA = "L"; // --> Differenct Staging Area
final private String K_DESC = "DESC";
final private String K_TRGMODEL = R_DESTINATION + R_SCHEMA;
final private String K_TRGTABLE = R_DESTINATION + R_TABLE;
final private String K_TRGCOLUMN = R_DESTINATION + R_COLUMNS;
final private String K_SRCMODEL = R_SOURCES + R_SCHEMA;
final private String K_SRCTABLE = R_SOURCES + R_TABLE;
final private String K_SRCALIAS = R_SOURCES + R_TABLE + R_ALIAS;
final private String K_SRCCOLUMN = R_SOURCES + R_COLUMNS;
final private String K_SRCCOLUMNALIAS = R_SOURCES + R_COLUMNS + R_ALIAS;
final private String K_SRCFILTER = R_SOURCES + R_FILTER;
final private String K_SRCKM = R_SOURCES + R_LKM;
final private String K_TRGKM = R_DESTINATION + R_IKM;
private ITransactionStatus txnStatus;
private OdiFolder folder;
private OdiContext cxt;
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/**************** 01. Calling WideTNS ODI Interface Interpreter ******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
public OdiWideTNSInterfaceInterpreter(String jdbcUrl, String driver,
String mstRepId, String mstRepPwd, String workrepName,
String odiUser, String odiPassword) {
super(jdbcUrl, driver, mstRepId, mstRepPwd, workrepName, odiUser,
odiPassword);
// TODO Auto-generated constructor stub
}
public Map<String, List<String>> getOdiMetadataForOdiII() {
// TODO Auto-generated method stub
Map<String, List<String>> m = new HashMap<String, List<String>>();
m.put(M_PROJECTANDFOLDER, getProjectAndFolderNames());
m.put(M_MODEL, getModelNames());
m.put(M_LOGICALSCHEMA, getLogicalSchemas());
m.put(M_LKM, getLKMNames());
m.put(M_IKM, getIKMNames());
return m;
}
@SuppressWarnings("unchecked")
public List<String> getProjectAndFolderNames() {
// TODO Auto-generated method stub
OdiProject p = null;
OdiFolder f = null;
List<String> l = new ArrayList<String>();
for (Iterator<OdiProject> iProject = ((IOdiProjectFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiProject.class))
.findAll().iterator(); iProject.hasNext();) {
p = iProject.next();
for (Iterator<OdiFolder> iFolder = ((IOdiFolderFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiFolder.class))
.findByProject(p.getCode()).iterator(); iFolder.hasNext();) {
f = iFolder.next();
l.add(p.getCode() + " - " + f.getName());
}
}
return l;
}
@SuppressWarnings("unchecked")
public List<String> getModelNames() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiModel> i = ((IOdiModelFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiModel.class))
.findAll().iterator(); i.hasNext();)
l.add(i.next().getCode());
return l;
}
@SuppressWarnings("unchecked")
public List<String> getLogicalSchemas() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiLogicalSchema> i = ((IOdiLogicalSchemaFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiLogicalSchema.class)).findAll().iterator(); i
.hasNext();)
l.add(i.next().getName());
return l;
}
@SuppressWarnings("unchecked")
public List<String> getIKMNames() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiIKM> i = ((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiIKM.class))
.findAll().iterator(); i.hasNext();)
l.add(i.next().getName());
return l;
}
@SuppressWarnings("unchecked")
public List<String> getLKMNames() {
// TODO Auto-generated method stub
List<String> l = new ArrayList<String>();
for (Iterator<OdiLKM> i = ((IOdiLKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiLKM.class))
.findAll().iterator(); i.hasNext();)
l.add(i.next().getName());
return l;
}
public void setContext(String strContext) {
// TODO Auto-generated method stub
cxt = (((IOdiContextFinder) odiInstance.getTransactionalEntityManager()
.getFinder(OdiContext.class)).findByCode(strContext) != null ? ((IOdiContextFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiContext.class))
.findByCode(strContext) : ((IOdiContextFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiContext.class))
.findDefaultContext());
}
public OdiContext getContext() {
// TODO Auto-generated method stub
return (cxt != null ? cxt : ((IOdiContextFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiContext.class))
.findDefaultContext());
}
public void setFolder(String strFolder, String strProject) {
// TODO Auto-generated method stub
Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiFolder.class))
.findByName(strFolder, strProject);
System.out.println(strFolder.getBytes()+")");System.out.println(strProject+")");
folder = fold.iterator().next();
}
public OdiFolder getFolder() {
return folder;
}
private Map<String, Integer> checkKeys(Set<String> keySet) throws Exception {
// TODO Auto-generated method stub
Map<String, Integer> m = new HashMap<String, Integer>();
String str;
for (Iterator<String> iter = keySet.iterator(); iter.hasNext();) {
str = iter.next().replaceAll("[0-9]", "");
Integer freq = m.get(str);
m.put(str, (freq == null) ? 1 : freq + 1);
}
checkErrors(m);
return m;
}
private void checkErrors(Map<String, Integer> m) throws Exception {
if (m.size() < 5)
throw new Exception("somthing is missed!");
}
/****************************************************/
/****************************************************/
/*** Default Create Interface Function 2012.01.30 ***/
/****************************************************/
/****************************************************/
public String create(Map<String, String> mapInfInfo, String pProject,
String pFolder, String pInf)
throws NoSourceSetException, TargetIsTemporaryException {
// TODO Auto-generated method stub
Map<String, Integer> mapCnt;
InteractiveInterfaceHelperWithActions interactiveHelper = null;
DataSet dataSet = null;
String strReturn = "";
OdiInterface inf = null;
txnStatus = startTransaction();
try {
mapCnt = checkKeys(mapInfInfo.keySet());
// Create Interface
setFolder(pFolder, pProject);
inf = new OdiInterface(getFolder(), pInf, getContext());
inf.setDescription(mapInfInfo.get(K_DESC)
+ "\n\nCreated by WideTNS ODI Interface Interpreter\n" + new Date());
System.out.println(new Date() + "***** Creating Interface");
strReturn += new Date() + "***** Creating Interface \n";
interactiveHelper = new InteractiveInterfaceHelperWithActions(
inf, odiInstance,
odiInstance.getTransactionalEntityManager());
// Set target table
interactiveHelper
.performAction(new InterfaceActionSetTargetDataStore(
((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiDataStore.class)).findByName(
mapInfInfo.get(K_TRGTABLE), mapInfInfo.get(K_TRGMODEL) ),
new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()));
System.out.println(new Date() + "***** Set target table");
strReturn += new Date() + "***** Set target table \n";
// Whether to use the different staging area
if(mapCnt.get(K_LOGICALSCHEMA) > 0)
{
interactiveHelper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(
((IOdiLogicalSchemaFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiLogicalSchema.class)).findByName(mapInfInfo.get(K_LOGICALSCHEMA))));
}
dataSet = inf.getDataSets().get(0);
// Add source table
for (int i = 0; i < mapCnt.get(K_SRCTABLE); i++) {
interactiveHelper
.performAction(new InterfaceActionAddSourceDataStore(
((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager()
.getFinder(OdiDataStore.class))
.findByName(
mapInfInfo.get(K_SRCTABLE + i), mapInfInfo.get(K_SRCMODEL+i)),
dataSet,
(mapInfInfo.get(K_SRCALIAS + i) == null ? new AliasComputerDoubleChecker()
: new AliasComputerFixed(mapInfInfo
.get(K_SRCALIAS + i))),
new ClauseImporterLazy(),
new AutoMappingComputerLazy()));
}
System.out.println(new Date() + "***** Added source tables");
strReturn += new Date() + "***** Added source tables \n";
// Auto-mapping
interactiveHelper
.performAction(new InterfaceActionOnTargetDataStoreComputeAutoMapping());
// Customized mappings
for (int i = 0; i < mapCnt.get(K_TRGCOLUMN); i++) {
interactiveHelper
.performAction(new InterfaceActionOnTargetMappingSetSql(
mapInfInfo.get(K_TRGCOLUMN + i), mapInfInfo
.get(K_SRCCOLUMN + i), dataSet));
}
System.out.println(new Date() + "***** Customized mappings");
strReturn += new Date() + "***** Customized mappings \n";
// Filter ************ need to consider more specific cases
if (mapCnt.get(K_SRCTABLE) > 1)
interactiveHelper.performAction(new InterfaceActionAddJoin(
dataSet, mapInfInfo.get(K_SRCFILTER),
ExecutionLocation.SOURCE));
else
interactiveHelper.performAction(new InterfaceActionAddFilter(
dataSet, mapInfInfo.get(K_SRCFILTER),
ExecutionLocation.SOURCE));
// IKM Select
if (mapCnt.get(K_TRGKM) > 0) {
interactiveHelper.performAction(new InterfaceActionSetKM(
((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiIKM.class)).findByName(mapInfInfo
.get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
KMType.IKM, new KMOptionRetainerLazy()));
}
// LKM Select ******************************************************************************** not yet
if (mapCnt.get(K_SRCKM) > 0) {
// interactiveHelper.performAction(new InterfaceActionSetKM(
// ((IOdiIKMFinder) odiInstance
// .getTransactionalEntityManager().getFinder(
// OdiIKM.class)).findByName(mapInfInfo
// .get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
// KMType.IKM, new KMOptionRetainerLazy()));
strReturn += new Date() + " ***** Sorry! Selecting LKM is not provided in this version!! ";
}
// Check consistency
interactiveHelper.computeSourceSets();
interactiveHelper.preparePersist();
odiInstance.getTransactionalEntityManager().persist(inf);
// Commit
commitTransaction(txnStatus);
System.out.println(new Date() + "***** Created Interface");
strReturn += new Date() + "***** Created Interface \n";
} catch (Exception e) {
e.printStackTrace();
strReturn +=new Date() + "***** Error \n" + "\n"
+ e.getMessage();
// Roll back when the process failed
rollbackTransaction(txnStatus);
} finally {
close();
}
return strReturn;
}
/*****************************************************************/
/*****************************************************************/
/*** Create Interface Function support for SubQuery 2012.01.30 ***/
/*****************************************************************/
/*****************************************************************/
public String create(Map<String, Object> mapInfInfo, String pProject,
String pFolder, String pInf, String pSrcModel, String pTrgModel
, OdiLogicalSchema odils)
throws NoSourceSetException, TargetIsTemporaryException {
// TODO Auto-generated method stub
Map<String, Integer> mapCnt;
InteractiveInterfaceHelperWithActions interactiveHelper = null;
DataSet dataSet = null;
String strReturn = "";
OdiInterface inf = null;
txnStatus = startTransaction();
try {
// 0. Propare Map Information
mapCnt = checkKeys(mapInfInfo.keySet());
setFolder(pFolder, pProject);
// 1. Create Interface Object
inf = new OdiInterface(getFolder(), pInf, getContext());
inf.setDescription(mapInfInfo.get(K_DESC)
+ "\n\nCreated by WideTNS ODI Interface Interpreter\n" +
"Interface Name: "+ pInf + "\n" + new Date());
interactiveHelper = new InteractiveInterfaceHelperWithActions(
inf, odiInstance,
odiInstance.getTransactionalEntityManager());
/*** Process Log ***/
System.out.println(new Date() + "***** Creating Interface" + pInf);
strReturn += new Date() + "***** Creating Interface " + pInf + "\n";
// 2. Set target table
// TODO for Sub-query
interactiveHelper
.performAction(new InterfaceActionSetTargetDataStore(
((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiDataStore.class)).findByName(
(String) mapInfInfo.get(K_TRGTABLE), pSrcModel
+ mapInfInfo.get(K_TRGMODEL)),
new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()));
System.out.println(new Date() + "***** Set target table: " + mapInfInfo.get(K_TRGTABLE));
strReturn += new Date() + "***** Set target table: " + mapInfInfo.get(K_TRGTABLE) + " \n";
// 3. Whether to use the different staging area
// TODO for Sub-query
if(mapInfInfo.get(K_LOGICALSCHEMA) != null)
{
interactiveHelper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(
((IOdiLogicalSchemaFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiLogicalSchema.class)).findByName((String) mapInfInfo.get(K_LOGICALSCHEMA))));
}
dataSet = inf.getDataSets().get(0);
// 4. Add source table
// TODO for Sub-query
for (int i = 0; i < mapCnt.get(K_SRCTABLE); i++) {
strReturn += addSourceTable((String) mapInfInfo.get(K_SRCALIAS + i),
mapInfInfo.get(K_SRCTABLE + i),
(String) mapInfInfo.get(K_SRCMODEL + i), dataSet, interactiveHelper);
}
// 5. Customized mappings
interactiveHelper
.performAction(new InterfaceActionOnTargetDataStoreComputeAutoMapping());
for (int i = 0; i < mapCnt.get(K_TRGCOLUMN); i++) {
interactiveHelper
.performAction(new InterfaceActionOnTargetMappingSetSql((String)
mapInfInfo.get(K_TRGCOLUMN + i), (String) mapInfInfo
.get(K_SRCCOLUMN + i), dataSet));
}
System.out.println(new Date() + "***** Customized mappings");
strReturn += new Date() + "***** Customized mappings \n";
// 6. set filter
if (mapCnt.get(K_SRCTABLE) > 1)
interactiveHelper.performAction(new InterfaceActionAddJoin(
dataSet,(String) mapInfInfo.get(K_SRCFILTER),
ExecutionLocation.SOURCE));
else
interactiveHelper.performAction(new InterfaceActionAddFilter(
dataSet,(String) mapInfInfo.get(K_SRCFILTER),
ExecutionLocation.SOURCE));
// 7. IKM Select
if (mapCnt.get(K_TRGKM) > 0) {
interactiveHelper.performAction(new InterfaceActionSetKM(
((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiIKM.class)).findByName((String) mapInfInfo
.get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
KMType.IKM, new KMOptionRetainerLazy()));
}
// 8. LKM Select ******************************************************************************** not yet
if (mapCnt.get(K_SRCKM) > 0) {
// interactiveHelper.performAction(new InterfaceActionSetKM(
// ((IOdiIKMFinder) odiInstance
// .getTransactionalEntityManager().getFinder(
// OdiIKM.class)).findByName(mapInfInfo
// .get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
// KMType.IKM, new KMOptionRetainerLazy()));
strReturn += new Date() + " ***** Sorry! Selecting LKM is not provided in this version!! ";
}
// 9. Check consistency
interactiveHelper.computeSourceSets();
interactiveHelper.preparePersist();
odiInstance.getTransactionalEntityManager().persist(inf);
// 10. 1Commit
commitTransaction(txnStatus);
System.out.println(new Date() + "***** Created Interface");
strReturn += new Date() + "***** Created Interface \n";
} catch (Exception e) {
e.printStackTrace();
strReturn +=new Date() + "***** Error \n" + "\n"
+ e.getMessage();
// Roll back when the process failed
rollbackTransaction(txnStatus);
} finally {
close();
}
return strReturn;
}
public String createTempTarget(Map<String, Object> mapInfInfo, String pProject,
String pFolder, String pInf, String pSrcModel, String pTrgModel
, OdiLogicalSchema odils) throws NoSourceSetException, TargetIsTemporaryException {
// TODO Auto-generated method stub
Map<String, Integer> mapCnt;
InteractiveInterfaceHelperWithActions interactiveHelper = null;
DataSet dataSet = null;
String strReturn = "";
OdiInterface inf = null;
txnStatus = startTransaction();
try {
// 0. Propare Map Information
mapCnt = checkKeys(mapInfInfo.keySet());
setFolder(pFolder, pProject);
// 1. Create Interface Object
inf = new OdiInterface(getFolder(), pInf, getContext());
inf.setDescription(mapInfInfo.get(K_DESC) + "\n\nCreated by WideTNS ODI Interface Interpreter\n" + "Interface Name: "+ pInf + "\n" + new Date());
interactiveHelper = new InteractiveInterfaceHelperWithActions(inf, odiInstance, odiInstance.getTransactionalEntityManager());
/*** Process Log ***/
strReturn += new Date() + "***** Creating Sub-Interface " + pInf + "\n";
// 2. Set target table
interactiveHelper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetName((String)mapInfInfo.get(K_TRGTABLE)));
interactiveHelper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema(DatabaseSchema.TEMPORARY_SCHEMA));
/*** Process Log ***/
strReturn += new Date() + "***** Set target table: " + mapInfInfo.get(K_TRGTABLE) + " \n";
// 3. Whether to use the different staging area
interactiveHelper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(
((IOdiLogicalSchemaFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLogicalSchema.class)).findByName((String) mapInfInfo.get(K_LOGICALSCHEMA))));
dataSet = inf.getDataSets().get(0);
// 4. Add source table
for (int i = 0; i < mapCnt.get(K_SRCTABLE); i++) {
strReturn += addSourceTable((String) mapInfInfo.get(K_SRCALIAS + i),
mapInfInfo.get(K_SRCTABLE + i),
(String) mapInfInfo.get(K_SRCMODEL + i), dataSet, interactiveHelper);
}
// 5. Customized mappings
for (int i = 0; i < mapCnt.get(K_SRCCOLUMNALIAS); i++) {
interactiveHelper.performAction(new InterfaceActionOnTemporaryTargetDataStoreAddColumn(
(String) mapInfInfo.get(K_SRCCOLUMNALIAS + i), new AutoMappingComputerColumnName()));
interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql((String) mapInfInfo.get(K_SRCCOLUMNALIAS + i), (String) mapInfInfo.get(K_SRCCOLUMN + i), dataSet));
}
/*** Process Log ***/
strReturn += new Date() + "***** Customized mappings \n";
// 6. set filter
if (mapCnt.get(K_SRCTABLE) > 1)
interactiveHelper.performAction(new InterfaceActionAddJoin(dataSet,(String) mapInfInfo.get(K_SRCFILTER),ExecutionLocation.SOURCE));
else
interactiveHelper.performAction(new InterfaceActionAddFilter(dataSet,(String) mapInfInfo.get(K_SRCFILTER),ExecutionLocation.SOURCE));
// 7. IKM Select
if (mapCnt.get(K_TRGKM) > 0) {
interactiveHelper.performAction(new InterfaceActionSetKM( ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(
OdiIKM.class)).findByName((String) mapInfInfo.get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(), KMType.IKM, new KMOptionRetainerLazy()));
}
// 8. LKM Select ******************************************************************************** not yet
if (mapCnt.get(K_SRCKM) > 0) {
// interactiveHelper.performAction(new InterfaceActionSetKM(
// ((IOdiIKMFinder) odiInstance
// .getTransactionalEntityManager().getFinder(
// OdiIKM.class)).findByName(mapInfInfo
// .get(K_TRGKM)).iterator().next(), inf.getTargetDataStore(),
// KMType.IKM, new KMOptionRetainerLazy()));
strReturn += new Date() + " ***** Sorry! Selecting LKM is not provided in this version!! ";
}
// 9. Check consistency
interactiveHelper.computeSourceSets();
interactiveHelper.preparePersist();
odiInstance.getTransactionalEntityManager().persist(inf);
// 10. 1Commit
commitTransaction(txnStatus);
System.out.println(new Date() + "***** Created Interface");
strReturn += new Date() + "***** Created Interface \n";
} catch (Exception e) {
e.printStackTrace();
strReturn +=new Date() + "***** Error \n" + "\n"
+ e.getMessage();
// Roll back when the process failed
rollbackTransaction(txnStatus);
} finally {
close();
}
return strReturn;
}
@SuppressWarnings("unchecked")
private String addSourceTable(String strTableAlias, Object object,
String strModel, DataSet dataSet,
InteractiveInterfaceHelperWithActions interactiveHelper) {
// TODO Auto-generated method stub
if(object.getClass().equals(Map.class))
return addSourceTable(strTableAlias, (Map<String, Object>) object,
strModel, dataSet,
interactiveHelper);
else if(object.getClass().equals(String.class))
return addSourceTable(strTableAlias, (String) object,
strModel, dataSet,
interactiveHelper);
return "ERROR!!";
}
public String addSourceTable(String strTableAlias,
String strTable, String strModel, DataSet ds,
InteractiveInterfaceHelperWithActions interactiveHelper){
interactiveHelper
.performAction(new InterfaceActionAddSourceDataStore(
((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager()
.getFinder(OdiDataStore.class))
.findByName(
strTable,
strModel),
ds,
(strTableAlias == null ? new AliasComputerDoubleChecker()
: new AliasComputerFixed(strTableAlias)),
new ClauseImporterLazy(),
new AutoMappingComputerLazy()));
/*** Process Log ***/
return new Date() + "***** Added source tables \n";
}
public String addSourceTable(String strTableAlias,
Map<String, Object> mapTempTable, String strModel, DataSet ds,
InteractiveInterfaceHelperWithActions interactiveHelper){
mapTempTable.put(K_TRGTABLE, strTableAlias);
// If Source Table is the Inline-view, do create sub interface.
return createTempTarget(mapTempTable, interactiveHelper.getOdiInterface().getFolder().getProject().getCode(),
interactiveHelper.getOdiInterface().getFolder().getName(), "IF.99_"+strTableAlias, strModel, strModel
, (interactiveHelper.getOdiInterface().getStagingArea().getLogicalSchema()==null?
interactiveHelper.getOdiInterface().getTargetDataStore().getLogicalSchema():
interactiveHelper.getOdiInterface().getStagingArea().getLogicalSchema()
)
);
}
OdiWideTNSInterfaceInterpreter.java