public TableDefinition buildCHILDNAMTable() {
TableDefinition table = new TableDefinition();
table.setName("CHILDNAM");
FieldDefinition fieldHOLDER_ID = new FieldDefinition();
fieldHOLDER_ID.setName("HOLDER_ID");
fieldHOLDER_ID.setTypeName("NUMBER");
fieldHOLDER_ID.setSize(18);
fieldHOLDER_ID.setSubSize(0);
fieldHOLDER_ID.setIsPrimaryKey(true);
fieldHOLDER_ID.setIsIdentity(false);
fieldHOLDER_ID.setUnique(false);
fieldHOLDER_ID.setShouldAllowNull(false);
table.addField(fieldHOLDER_ID);
FieldDefinition fieldCHILD_NAME = new FieldDefinition();
fieldCHILD_NAME.setName("CHILD_NAME");
fieldCHILD_NAME.setTypeName("VARCHAR2");
fieldCHILD_NAME.setSize(30);
fieldCHILD_NAME.setSubSize(0);
fieldCHILD_NAME.setIsPrimaryKey(true);
fieldCHILD_NAME.setIsIdentity(false);
fieldCHILD_NAME.setUnique(false);
fieldCHILD_NAME.setShouldAllowNull(false);
table.addField(fieldCHILD_NAME);
ForeignKeyConstraint foreignKeyFK_CHILDNAM_HOLDER_ID = new ForeignKeyConstraint();
foreignKeyFK_CHILDNAM_HOLDER_ID.setName("CHILD_HOLDER");
foreignKeyFK_CHILDNAM_HOLDER_ID.setTargetTable("HOLDER");
foreignKeyFK_CHILDNAM_HOLDER_ID.addSourceField("HOLDER_ID");
foreignKeyFK_CHILDNAM_HOLDER_ID.addTargetField("SSN");
table.addForeignKeyConstraint(foreignKeyFK_CHILDNAM_HOLDER_ID);
return table;
}
public TableDefinition buildVHCL_POLTable() {
TableDefinition table = new TableDefinition();
table.setName("VHCL_POL");
FieldDefinition fieldPOL_ID = new FieldDefinition();
fieldPOL_ID.setName("POL_ID");
fieldPOL_ID.setTypeName("NUMBER");
fieldPOL_ID.setSize(18);
fieldPOL_ID.setSubSize(0);
fieldPOL_ID.setIsPrimaryKey(true);
fieldPOL_ID.setIsIdentity(false);
fieldPOL_ID.setUnique(false);
fieldPOL_ID.setShouldAllowNull(false);
table.addField(fieldPOL_ID);
FieldDefinition fieldMODEL = new FieldDefinition();
fieldMODEL.setName("MODEL");
fieldMODEL.setTypeName("VARCHAR2");
fieldMODEL.setSize(30);
fieldMODEL.setSubSize(0);
fieldMODEL.setIsPrimaryKey(false);
fieldMODEL.setIsIdentity(false);
fieldMODEL.setUnique(false);
fieldMODEL.setShouldAllowNull(true);
table.addField(fieldMODEL);
ForeignKeyConstraint foreignKeyFK_VHCL_POL_POL_ID = new ForeignKeyConstraint();
foreignKeyFK_VHCL_POL_POL_ID.setName("VHCL_POL_POLICY");
foreignKeyFK_VHCL_POL_POL_ID.setTargetTable("POLICY");
foreignKeyFK_VHCL_POL_POL_ID.addSourceField("POL_ID");
foreignKeyFK_VHCL_POL_POL_ID.addTargetField("POL_ID");
table.addForeignKeyConstraint(foreignKeyFK_VHCL_POL_POL_ID);
return table;
}
public TableDefinition buildINS_PHONETable() {
TableDefinition table = new TableDefinition();
table.setName("INS_PHONE");
FieldDefinition fieldHOLDER_SSN = new FieldDefinition();
fieldHOLDER_SSN.setName("HOLDER_SSN");
fieldHOLDER_SSN.setTypeName("NUMBER");
fieldHOLDER_SSN.setSize(18);
fieldHOLDER_SSN.setSubSize(0);
fieldHOLDER_SSN.setIsPrimaryKey(true);
fieldHOLDER_SSN.setIsIdentity(false);
fieldHOLDER_SSN.setUnique(false);
fieldHOLDER_SSN.setShouldAllowNull(false);
table.addField(fieldHOLDER_SSN);
FieldDefinition fieldTYPE = new FieldDefinition();
fieldTYPE.setName("TYPE");
fieldTYPE.setTypeName("VARCHAR2");
fieldTYPE.setSize(10);
fieldTYPE.setSubSize(0);
fieldTYPE.setIsPrimaryKey(true);
fieldTYPE.setIsIdentity(false);
fieldTYPE.setUnique(false);
fieldTYPE.setShouldAllowNull(false);
table.addField(fieldTYPE);
FieldDefinition fieldAREACODE = new FieldDefinition();
fieldAREACODE.setName("AREACODE");
fieldAREACODE.setTypeName("NUMBER");
fieldAREACODE.setSize(3);
fieldAREACODE.setSubSize(0);
fieldAREACODE.setIsPrimaryKey(false);
fieldAREACODE.setIsIdentity(false);
fieldAREACODE.setUnique(false);
fieldAREACODE.setShouldAllowNull(true);
table.addField(fieldAREACODE);
FieldDefinition fieldPHONE_NUMBER = new FieldDefinition();
fieldPHONE_NUMBER.setName("PHONE_NUMBER");
fieldPHONE_NUMBER.setTypeName("NUMBER");
fieldPHONE_NUMBER.setSize(10);
fieldPHONE_NUMBER.setSubSize(0);
fieldPHONE_NUMBER.setIsPrimaryKey(false);
fieldPHONE_NUMBER.setIsIdentity(false);
fieldPHONE_NUMBER.setUnique(false);
fieldPHONE_NUMBER.setShouldAllowNull(true);
table.addField(fieldPHONE_NUMBER);
ForeignKeyConstraint foreignKeyFK_INS_PHONE_HOLDER_SSN = new ForeignKeyConstraint();
foreignKeyFK_INS_PHONE_HOLDER_SSN.setName("INS_PHONE_HOLDER");
foreignKeyFK_INS_PHONE_HOLDER_SSN.setTargetTable("HOLDER");
foreignKeyFK_INS_PHONE_HOLDER_SSN.addSourceField("HOLDER_SSN");
foreignKeyFK_INS_PHONE_HOLDER_SSN.addTargetField("SSN");
table.addForeignKeyConstraint(foreignKeyFK_INS_PHONE_HOLDER_SSN);
return table;
}
/**
* Remove the specified {@link ForeignKeyConstraint} from this table.
*
* <p>This is a more drastic removal solution that was proposed by Remke Rutgers
*
* @param constraint
*/
private ForeignKeyConstraint remove(ForeignKeyConstraint constraint) {
if (constraint != null) {
for (int i = 0; i < constraint.getChildColumns().size(); i++) {
TableColumn childColumn = constraint.getChildColumns().get(i);
TableColumn parentColumn = constraint.getParentColumns().get(i);
childColumn.removeParent(parentColumn);
parentColumn.removeChild(childColumn);
}
}
return constraint;
}
/**
* Remove any non-real foreign keys
*
* @return
*/
public List<ForeignKeyConstraint> removeNonRealForeignKeys() {
List<ForeignKeyConstraint> nonReals = new ArrayList<ForeignKeyConstraint>();
for (TableColumn column : columns.values()) {
for (TableColumn parentColumn : column.getParents()) {
ForeignKeyConstraint constraint = column.getParentConstraint(parentColumn);
if (constraint != null && !constraint.isReal()) {
nonReals.add(remove(constraint));
}
}
}
return nonReals;
}
public static TableDefinition buildSPARKPLUGTable() {
// CREATE TABLE JPA_OR_SPARK_PLUG (ID NUMBER(10) NOT NULL, SERIALNUMBER NUMBER(19) NULL,
// ENGINE_ID NUMBER(10) NULL, PRIMARY KEY (ID))
TableDefinition table = new TableDefinition();
table.setName("JPA_OR_SPARK_PLUG");
FieldDefinition fieldID = new FieldDefinition();
fieldID.setName("ID");
fieldID.setTypeName("NUMERIC");
fieldID.setSize(10);
fieldID.setSubSize(0);
fieldID.setIsPrimaryKey(true);
fieldID.setIsIdentity(true);
fieldID.setShouldAllowNull(false);
fieldID.setUnique(false);
table.addField(fieldID);
FieldDefinition fieldSERIALNUMBER = new FieldDefinition();
fieldSERIALNUMBER.setName("SERIALNUMBER");
fieldSERIALNUMBER.setTypeName("NUMERIC");
fieldSERIALNUMBER.setSize(18);
fieldSERIALNUMBER.setSubSize(0);
fieldSERIALNUMBER.setIsPrimaryKey(false);
fieldSERIALNUMBER.setIsIdentity(false);
fieldSERIALNUMBER.setShouldAllowNull(true);
fieldSERIALNUMBER.setUnique(false);
table.addField(fieldSERIALNUMBER);
FieldDefinition fieldENGINE = new FieldDefinition();
fieldENGINE.setName("ENGINE_ID");
fieldENGINE.setTypeName("NUMERIC");
fieldENGINE.setSize(10);
fieldENGINE.setSubSize(0);
fieldENGINE.setIsPrimaryKey(false);
fieldENGINE.setIsIdentity(false);
fieldENGINE.setShouldAllowNull(true);
fieldENGINE.setUnique(false);
table.addField(fieldENGINE);
// ALTER TABLE JPA_OR_SPARK_PLUG ADD CONSTRAINT JPA_OR_SPARK_PLUG_ENGINE_ID FOREIGN KEY
// (ENGINE_ID) REFERENCES JPA_OR_ENGINE (ID)
ForeignKeyConstraint foreignKeySPARKPLUG_ENGINE = new ForeignKeyConstraint();
foreignKeySPARKPLUG_ENGINE.setName("FK_SPK_PG_ENG_ID");
foreignKeySPARKPLUG_ENGINE.setTargetTable("JPA_OR_ENGINE");
foreignKeySPARKPLUG_ENGINE.addSourceField("ENGINE_ID");
foreignKeySPARKPLUG_ENGINE.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeySPARKPLUG_ENGINE);
return table;
}
public TableDefinition buildVHCL_CLMTable() {
TableDefinition table = new TableDefinition();
table.setName("VHCL_CLM");
FieldDefinition fieldCLM_ID = new FieldDefinition();
fieldCLM_ID.setName("CLM_ID");
fieldCLM_ID.setTypeName("NUMBER");
fieldCLM_ID.setSize(18);
fieldCLM_ID.setSubSize(0);
fieldCLM_ID.setIsPrimaryKey(true);
fieldCLM_ID.setIsIdentity(false);
fieldCLM_ID.setUnique(false);
fieldCLM_ID.setShouldAllowNull(false);
table.addField(fieldCLM_ID);
FieldDefinition fieldPART = new FieldDefinition();
fieldPART.setName("PART");
fieldPART.setTypeName("VARCHAR2");
fieldPART.setSize(30);
fieldPART.setSubSize(0);
fieldPART.setIsPrimaryKey(false);
fieldPART.setIsIdentity(false);
fieldPART.setUnique(false);
fieldPART.setShouldAllowNull(true);
table.addField(fieldPART);
FieldDefinition fieldPART_DESC = new FieldDefinition();
fieldPART_DESC.setName("PART_DESC");
fieldPART_DESC.setTypeName("VARCHAR2");
fieldPART_DESC.setSize(30);
fieldPART_DESC.setSubSize(0);
fieldPART_DESC.setIsPrimaryKey(false);
fieldPART_DESC.setIsIdentity(false);
fieldPART_DESC.setUnique(false);
fieldPART_DESC.setShouldAllowNull(true);
table.addField(fieldPART_DESC);
ForeignKeyConstraint foreignKeyFK_VHCL_CLM_CLM_ID = new ForeignKeyConstraint();
foreignKeyFK_VHCL_CLM_CLM_ID.setName("VHCL_CLM_CLAIM");
foreignKeyFK_VHCL_CLM_CLM_ID.setTargetTable("CLAIM");
foreignKeyFK_VHCL_CLM_CLM_ID.addSourceField("CLM_ID");
foreignKeyFK_VHCL_CLM_CLM_ID.addTargetField("CLM_ID");
table.addForeignKeyConstraint(foreignKeyFK_VHCL_CLM_CLM_ID);
return table;
}
/**
* Remove any non-real foreign keys
*
* @return
*/
public List<ForeignKeyConstraint> removeNonRealForeignKeys() {
List<ForeignKeyConstraint> nonReals = new ArrayList<ForeignKeyConstraint>();
for (TableColumn column : columns.values()) {
for (TableColumn parentColumn : column.getParents()) {
ForeignKeyConstraint constraint = column.getParentConstraint(parentColumn);
if (constraint != null && !constraint.isReal()) {
nonReals.add(constraint);
}
}
}
// remove constraints outside of above loop to prevent
// concurrent modification exceptions while iterating
for (ForeignKeyConstraint constraint : nonReals) {
remove(constraint);
}
return nonReals;
}
public static TableDefinition buildWHEELNUTTable() {
// CREATE TABLE JPA_OR_WHEEL_NUT (ID NUMBER(10) NOT NULL, WHEEL_ID NUMBER(10) NULL, PRIMARY KEY
// (ID))
TableDefinition table = new TableDefinition();
table.setName("JPA_OR_WHEEL_NUT");
FieldDefinition fieldID = new FieldDefinition();
fieldID.setName("ID");
fieldID.setTypeName("NUMERIC");
fieldID.setSize(10);
fieldID.setSubSize(0);
fieldID.setIsPrimaryKey(true);
fieldID.setIsIdentity(true);
fieldID.setShouldAllowNull(false);
fieldID.setUnique(false);
table.addField(fieldID);
FieldDefinition fieldWHEEL = new FieldDefinition();
fieldWHEEL.setName("WHEEL_ID");
fieldWHEEL.setTypeName("NUMERIC");
fieldWHEEL.setSize(10);
fieldWHEEL.setSubSize(0);
fieldWHEEL.setIsPrimaryKey(false);
fieldWHEEL.setIsIdentity(false);
fieldWHEEL.setShouldAllowNull(true);
fieldWHEEL.setUnique(false);
table.addField(fieldWHEEL);
// ALTER TABLE JPA_OR_WHEEL_NUT ADD CONSTRAINT FK_JPA_OR_WHEEL_NUT_WHEEL_ID FOREIGN KEY
// (WHEEL_ID) REFERENCES JPA_OR_WHEEL (ID)
ForeignKeyConstraint foreignKeyWHEELNUT_WHEEL = new ForeignKeyConstraint();
foreignKeyWHEELNUT_WHEEL.setName("FK_WH_NUT_WL_ID");
foreignKeyWHEELNUT_WHEEL.setTargetTable("JPA_OR_WHEEL");
foreignKeyWHEELNUT_WHEEL.addSourceField("WHEEL_ID");
foreignKeyWHEELNUT_WHEEL.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeyWHEELNUT_WHEEL);
return table;
}
private void createForeignKeyIndices(Connection conn, int minTuples) throws SQLException {
List<ForeignKeyConstraint> fkConstraints = new ArrayList<ForeignKeyConstraint>();
PreparedStatement selectStmt = conn.prepareStatement(constraintQuery);
ResultSet rs = selectStmt.executeQuery();
while (rs.next()) {
String tableName = rs.getString(1);
String columnName = rs.getString(2);
String constraintName = rs.getString(3);
fkConstraints.add(new ForeignKeyConstraint(tableName, columnName, constraintName));
}
for (ForeignKeyConstraint fk : fkConstraints) {
selectStmt = conn.prepareStatement(tuplesQuery);
selectStmt.setString(1, fk.getConstraintName());
rs = selectStmt.executeQuery();
if (rs.next()) {
fk.setTuples(rs.getLong(1));
}
}
Collections.sort(fkConstraints);
for (ForeignKeyConstraint fk : fkConstraints) {
String indexName = "fki_" + fk.getConstraintName();
if (indexName.lastIndexOf("_fkey") > 0) {
indexName = indexName.substring(0, indexName.lastIndexOf("_fkey"));
}
if (fk.getTuples() >= minTuples && !hasIndex(conn, indexName)) {
System.out.printf(
"Creating index %s (tuples: %d)\n",
(fk.getTableName() + "." + fk.getColumnName() + " --> " + indexName), fk.getTuples());
PreparedStatement createStmt =
conn.prepareStatement(
String.format(createIndex, indexName, fk.getTableName(), fk.getColumnName()));
createStmt.execute();
}
}
for (Entry<String, Pair<String, String>> entry : indexMap.entrySet()) {
String indexName = entry.getKey();
String tableName = entry.getValue().getLeft();
String columnName = entry.getValue().getRight();
if (!hasIndex(conn, indexName)) {
System.out.printf(
"Creating index %s\n", (tableName + "." + columnName + " --> " + indexName));
try {
PreparedStatement createStmt =
conn.prepareStatement(String.format(createIndex, indexName, tableName, columnName));
createStmt.execute();
} catch (SQLException e) {
System.err.println("Failed to create index " + indexName + ": " + e.getMessage());
}
}
}
}
public static TableDefinition buildWHEELTable() {
// CREATE TABLE JPA_OR_WHEEL (ID NUMBER(10) NOT NULL, SERIALNUMBER NUMBER(19) NULL, WHEELRIM_ID
// NUMBER(10) NULL,
// CHASSIS_ID NUMBER(10) NULL, MANUFACTURER VARCHAR2(255) NULL, TYPE VARCHAR2(255) NULL,
// PRIMARY KEY (ID))
TableDefinition table = new TableDefinition();
table.setName("JPA_OR_WHEEL");
FieldDefinition fieldID = new FieldDefinition();
fieldID.setName("ID");
fieldID.setTypeName("NUMERIC");
fieldID.setSize(10);
fieldID.setSubSize(0);
fieldID.setIsPrimaryKey(true);
fieldID.setIsIdentity(true);
fieldID.setShouldAllowNull(false);
fieldID.setUnique(false);
table.addField(fieldID);
FieldDefinition fieldSERIALNUMBER = new FieldDefinition();
fieldSERIALNUMBER.setName("SERIALNUMBER");
fieldSERIALNUMBER.setTypeName("NUMERIC");
fieldSERIALNUMBER.setSize(18);
fieldSERIALNUMBER.setSubSize(0);
fieldSERIALNUMBER.setIsPrimaryKey(false);
fieldSERIALNUMBER.setIsIdentity(false);
fieldSERIALNUMBER.setShouldAllowNull(true);
fieldSERIALNUMBER.setUnique(false);
table.addField(fieldSERIALNUMBER);
FieldDefinition fieldWHEELRIM = new FieldDefinition();
fieldWHEELRIM.setName("WHEELRIM_ID");
fieldWHEELRIM.setTypeName("NUMERIC");
fieldWHEELRIM.setSize(10);
fieldWHEELRIM.setSubSize(0);
fieldWHEELRIM.setIsPrimaryKey(false);
fieldWHEELRIM.setIsIdentity(false);
fieldWHEELRIM.setShouldAllowNull(true);
fieldWHEELRIM.setUnique(false);
table.addField(fieldWHEELRIM);
FieldDefinition fieldCHASSIS = new FieldDefinition();
fieldCHASSIS.setName("CHASSIS_ID");
fieldCHASSIS.setTypeName("NUMERIC");
fieldCHASSIS.setSize(10);
fieldCHASSIS.setSubSize(0);
fieldCHASSIS.setIsPrimaryKey(false);
fieldCHASSIS.setIsIdentity(false);
fieldCHASSIS.setShouldAllowNull(true);
fieldCHASSIS.setUnique(false);
table.addField(fieldCHASSIS);
FieldDefinition fieldMANUFACTURER = new FieldDefinition();
fieldMANUFACTURER.setName("MANUFACTURER");
fieldMANUFACTURER.setTypeName("VARCHAR");
fieldMANUFACTURER.setSize(60);
fieldMANUFACTURER.setSubSize(0);
fieldMANUFACTURER.setIsPrimaryKey(false);
fieldMANUFACTURER.setIsIdentity(false);
fieldMANUFACTURER.setShouldAllowNull(true);
fieldMANUFACTURER.setUnique(false);
table.addField(fieldMANUFACTURER);
FieldDefinition fieldTYPE = new FieldDefinition();
fieldTYPE.setName("TYPE");
fieldTYPE.setTypeName("VARCHAR");
fieldTYPE.setSize(60);
fieldTYPE.setSubSize(0);
fieldTYPE.setIsPrimaryKey(false);
fieldTYPE.setIsIdentity(false);
fieldTYPE.setShouldAllowNull(true);
fieldTYPE.setUnique(false);
table.addField(fieldTYPE);
// ALTER TABLE JPA_OR_WHEEL ADD CONSTRAINT FK_JPA_OR_WHEEL_WHEELRIM_ID FOREIGN KEY (WHEELRIM_ID)
// REFERENCES JPA_OR_WHEEL_RIM (ID)
ForeignKeyConstraint foreignKeyWHEEL_WHEELRIM = new ForeignKeyConstraint();
foreignKeyWHEEL_WHEELRIM.setName("FK_WHL_WHLRM_ID");
foreignKeyWHEEL_WHEELRIM.setTargetTable("JPA_OR_WHEEL_RIM");
foreignKeyWHEEL_WHEELRIM.addSourceField("WHEELRIM_ID");
foreignKeyWHEEL_WHEELRIM.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeyWHEEL_WHEELRIM);
// ALTER TABLE JPA_OR_WHEEL ADD CONSTRAINT FK_JPA_OR_WHEEL_CHASSIS_ID FOREIGN KEY (CHASSIS_ID)
// REFERENCES JPA_OR_CHASSIS (ID)
ForeignKeyConstraint foreignKeyWHEEL_CHASSIS = new ForeignKeyConstraint();
foreignKeyWHEEL_CHASSIS.setName("FK_WHL_CHAS_ID");
foreignKeyWHEEL_CHASSIS.setTargetTable("JPA_OR_CHASSIS");
foreignKeyWHEEL_CHASSIS.addSourceField("CHASSIS_ID");
foreignKeyWHEEL_CHASSIS.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeyWHEEL_CHASSIS);
return table;
}
/**
* @param rs ResultSet from {@link DatabaseMetaData#getImportedKeys(String, String, String)}
* rs.getString("FK_NAME"); rs.getString("FKCOLUMN_NAME"); rs.getString("PKTABLE_SCHEM");
* rs.getString("PKTABLE_NAME"); rs.getString("PKCOLUMN_NAME");
* @param tables Map
* @param db
* @throws SQLException
*/
protected void addForeignKey(
String fkName,
String fkColName,
String pkTableSchema,
String pkTableName,
String pkColName,
int updateRule,
int deleteRule,
Map<String, Table> tables,
Pattern excludeIndirectColumns,
Pattern excludeColumns)
throws SQLException {
if (fkName == null) return;
ForeignKeyConstraint foreignKey = foreignKeys.get(fkName);
if (foreignKey == null) {
foreignKey = new ForeignKeyConstraint(this, fkName, updateRule, deleteRule);
foreignKeys.put(fkName, foreignKey);
}
TableColumn childColumn = getColumn(fkColName);
if (childColumn != null) {
foreignKey.addChildColumn(childColumn);
Table parentTable = tables.get(pkTableName);
String parentSchema = pkTableSchema;
String baseSchema = Config.getInstance().getSchema();
// if named table doesn't exist in this schema
// or exists here but really referencing same named table in another schema
if (parentTable == null
|| (baseSchema != null && parentSchema != null && !baseSchema.equals(parentSchema))) {
parentTable =
db.addRemoteTable(
parentSchema,
pkTableName,
baseSchema,
properties,
excludeIndirectColumns,
excludeColumns);
}
if (parentTable != null) {
TableColumn parentColumn = parentTable.getColumn(pkColName);
if (parentColumn != null) {
foreignKey.addParentColumn(parentColumn);
childColumn.addParent(parentColumn, foreignKey);
parentColumn.addChild(childColumn, foreignKey);
} else {
logger.warning(
"Couldn't add FK '"
+ foreignKey.getName()
+ "' to table '"
+ this
+ "' - Column '"
+ pkColName
+ "' doesn't exist in table '"
+ parentTable
+ "'");
}
} else {
logger.warning(
"Couldn't add FK '"
+ foreignKey.getName()
+ "' to table '"
+ this
+ "' - Unknown Referenced Table '"
+ pkTableName
+ "'");
}
} else {
logger.warning(
"Couldn't add FK '"
+ foreignKey.getName()
+ "' to table '"
+ this
+ "' - Column '"
+ fkColName
+ "' doesn't exist");
}
}
public TableDefinition buildCLAIMTable() {
TableDefinition table = new TableDefinition();
table.setName("CLAIM");
FieldDefinition fieldCLM_ID = new FieldDefinition();
fieldCLM_ID.setName("CLM_ID");
fieldCLM_ID.setTypeName("NUMBER");
fieldCLM_ID.setSize(18);
fieldCLM_ID.setSubSize(0);
fieldCLM_ID.setIsPrimaryKey(true);
fieldCLM_ID.setIsIdentity(false);
fieldCLM_ID.setUnique(false);
fieldCLM_ID.setShouldAllowNull(false);
table.addField(fieldCLM_ID);
FieldDefinition fieldPOL_ID = new FieldDefinition();
fieldPOL_ID.setName("POL_ID");
fieldPOL_ID.setTypeName("NUMBER");
fieldPOL_ID.setSize(18);
fieldPOL_ID.setSubSize(0);
fieldPOL_ID.setIsPrimaryKey(false);
fieldPOL_ID.setIsIdentity(false);
fieldPOL_ID.setUnique(false);
fieldPOL_ID.setShouldAllowNull(true);
table.addField(fieldPOL_ID);
FieldDefinition fieldCLM_TYPE = new FieldDefinition();
fieldCLM_TYPE.setName("CLM_TYPE");
fieldCLM_TYPE.setTypeName("VARCHAR2");
fieldCLM_TYPE.setSize(20);
fieldCLM_TYPE.setSubSize(0);
fieldCLM_TYPE.setIsPrimaryKey(false);
fieldCLM_TYPE.setIsIdentity(false);
fieldCLM_TYPE.setUnique(false);
fieldCLM_TYPE.setShouldAllowNull(true);
table.addField(fieldCLM_TYPE);
FieldDefinition fieldAMOUNT = new FieldDefinition();
fieldAMOUNT.setName("AMOUNT");
fieldAMOUNT.setTypeName("FLOAT");
fieldAMOUNT.setSize(18);
fieldAMOUNT.setSubSize(4);
fieldAMOUNT.setIsPrimaryKey(false);
fieldAMOUNT.setIsIdentity(false);
fieldAMOUNT.setUnique(false);
fieldAMOUNT.setShouldAllowNull(true);
table.addField(fieldAMOUNT);
FieldDefinition fieldDISEASE = new FieldDefinition();
fieldDISEASE.setName("DISEASE");
fieldDISEASE.setTypeName("VARCHAR2");
fieldDISEASE.setSize(50);
fieldDISEASE.setSubSize(0);
fieldDISEASE.setIsPrimaryKey(false);
fieldDISEASE.setIsIdentity(false);
fieldDISEASE.setUnique(false);
fieldDISEASE.setShouldAllowNull(true);
table.addField(fieldDISEASE);
FieldDefinition fieldAREA = new FieldDefinition();
fieldAREA.setName("AREA");
fieldAREA.setTypeName("NUMBER");
fieldAREA.setSize(18);
fieldAREA.setSubSize(4);
fieldAREA.setIsPrimaryKey(false);
fieldAREA.setIsIdentity(false);
fieldAREA.setUnique(false);
fieldAREA.setShouldAllowNull(true);
table.addField(fieldAREA);
ForeignKeyConstraint foreignKeyFK_CLAIM_POL_ID = new ForeignKeyConstraint();
foreignKeyFK_CLAIM_POL_ID.setName("CLAIM_POL");
foreignKeyFK_CLAIM_POL_ID.setTargetTable("POLICY");
foreignKeyFK_CLAIM_POL_ID.addSourceField("POL_ID");
foreignKeyFK_CLAIM_POL_ID.addTargetField("POL_ID");
table.addForeignKeyConstraint(foreignKeyFK_CLAIM_POL_ID);
return table;
}
public static TableDefinition buildVEHICLETable() {
// CREATE TABLE JPA_OR_VEHICLE (ID NUMBER(10) NOT NULL, MODEL VARCHAR2(255) NULL, CHASSIS_ID
// NUMBER(10) NULL, ENGINE_ID NUMBER(10) NULL, PRIMARY KEY (ID))
TableDefinition table = new TableDefinition();
table.setName("JPA_OR_VEHICLE");
FieldDefinition fieldID = new FieldDefinition();
fieldID.setName("ID");
fieldID.setTypeName("NUMERIC");
fieldID.setSize(10);
fieldID.setSubSize(0);
fieldID.setIsPrimaryKey(true);
fieldID.setIsIdentity(true);
fieldID.setShouldAllowNull(false);
fieldID.setUnique(false);
table.addField(fieldID);
FieldDefinition fieldMODEL = new FieldDefinition();
fieldMODEL.setName("MODEL");
fieldMODEL.setTypeName("VARCHAR");
fieldMODEL.setSize(60);
fieldMODEL.setSubSize(0);
fieldMODEL.setIsPrimaryKey(false);
fieldMODEL.setIsIdentity(false);
fieldMODEL.setShouldAllowNull(true);
fieldMODEL.setUnique(false);
table.addField(fieldMODEL);
FieldDefinition fieldCHASSIS = new FieldDefinition();
fieldCHASSIS.setName("CHASSIS_ID");
fieldCHASSIS.setTypeName("NUMERIC");
fieldCHASSIS.setSize(10);
fieldCHASSIS.setSubSize(0);
fieldCHASSIS.setIsPrimaryKey(false);
fieldCHASSIS.setIsIdentity(false);
fieldCHASSIS.setShouldAllowNull(true);
fieldCHASSIS.setUnique(false);
table.addField(fieldCHASSIS);
FieldDefinition fieldENGINE = new FieldDefinition();
fieldENGINE.setName("ENGINE_ID");
fieldENGINE.setTypeName("NUMERIC");
fieldENGINE.setSize(10);
fieldENGINE.setSubSize(0);
fieldENGINE.setIsPrimaryKey(false);
fieldENGINE.setIsIdentity(false);
fieldENGINE.setShouldAllowNull(true);
fieldENGINE.setUnique(false);
table.addField(fieldENGINE);
// ALTER TABLE JPA_OR_VEHICLE ADD CONSTRAINT FK_JPA_OR_VEHICLE_ENGINE_ID FOREIGN KEY (ENGINE_ID)
// REFERENCES JPA_OR_ENGINE (ID)
ForeignKeyConstraint foreignKeyVEHICLE_ENGINE = new ForeignKeyConstraint();
foreignKeyVEHICLE_ENGINE.setName("FK_OR_VEH_ENG_ID");
foreignKeyVEHICLE_ENGINE.setTargetTable("JPA_OR_ENGINE");
foreignKeyVEHICLE_ENGINE.addSourceField("ENGINE_ID");
foreignKeyVEHICLE_ENGINE.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeyVEHICLE_ENGINE);
// ALTER TABLE JPA_OR_VEHICLE ADD CONSTRAINT FK_JPA_OR_VEHICLE_CHASSIS_ID FOREIGN KEY
// (CHASSIS_ID) REFERENCES JPA_OR_CHASSIS (ID)
ForeignKeyConstraint foreignKeyVEHICLE_CHASSIS = new ForeignKeyConstraint();
foreignKeyVEHICLE_CHASSIS.setName("FK_OR_VEH_CHAS_ID");
foreignKeyVEHICLE_CHASSIS.setTargetTable("JPA_OR_CHASSIS");
foreignKeyVEHICLE_CHASSIS.addSourceField("CHASSIS_ID");
foreignKeyVEHICLE_CHASSIS.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeyVEHICLE_CHASSIS);
return table;
}
public TableDefinition buildPOLICYTable() {
TableDefinition table = new TableDefinition();
table.setName("POLICY");
FieldDefinition fieldPOL_ID = new FieldDefinition();
fieldPOL_ID.setName("POL_ID");
fieldPOL_ID.setTypeName("NUMBER");
fieldPOL_ID.setSize(18);
fieldPOL_ID.setSubSize(0);
fieldPOL_ID.setIsPrimaryKey(true);
fieldPOL_ID.setIsIdentity(false);
fieldPOL_ID.setUnique(false);
fieldPOL_ID.setShouldAllowNull(false);
table.addField(fieldPOL_ID);
FieldDefinition fieldSSN = new FieldDefinition();
fieldSSN.setName("SSN");
fieldSSN.setTypeName("NUMBER");
fieldSSN.setSize(18);
fieldSSN.setSubSize(0);
fieldSSN.setIsPrimaryKey(false);
fieldSSN.setIsIdentity(false);
fieldSSN.setUnique(false);
fieldSSN.setShouldAllowNull(true);
table.addField(fieldSSN);
FieldDefinition fieldDESCRIPT = new FieldDefinition();
fieldDESCRIPT.setName("DESCRIPT");
fieldDESCRIPT.setTypeName("VARCHAR2");
fieldDESCRIPT.setSize(100);
fieldDESCRIPT.setSubSize(0);
fieldDESCRIPT.setIsPrimaryKey(false);
fieldDESCRIPT.setIsIdentity(false);
fieldDESCRIPT.setUnique(false);
fieldDESCRIPT.setShouldAllowNull(true);
table.addField(fieldDESCRIPT);
FieldDefinition fieldPOL_TYPE = new FieldDefinition();
fieldPOL_TYPE.setName("POL_TYPE");
fieldPOL_TYPE.setTypeName("NUMBER");
fieldPOL_TYPE.setSize(1);
fieldPOL_TYPE.setSubSize(0);
fieldPOL_TYPE.setIsPrimaryKey(false);
fieldPOL_TYPE.setIsIdentity(false);
fieldPOL_TYPE.setUnique(false);
fieldPOL_TYPE.setShouldAllowNull(true);
table.addField(fieldPOL_TYPE);
FieldDefinition fieldMAX_COV = new FieldDefinition();
fieldMAX_COV.setName("MAX_COV");
fieldMAX_COV.setTypeName("NUMBER");
fieldMAX_COV.setSize(18);
fieldMAX_COV.setSubSize(4);
fieldMAX_COV.setIsPrimaryKey(false);
fieldMAX_COV.setIsIdentity(false);
fieldMAX_COV.setUnique(false);
fieldMAX_COV.setShouldAllowNull(true);
table.addField(fieldMAX_COV);
FieldDefinition fieldCOV_RATE = new FieldDefinition();
fieldCOV_RATE.setName("COV_RATE");
// NUMBER corresponds to long which is mapped to INTEGER data type in some database such as DB2
// and MySQL.
// FLOAT should be used which reflect float java type.
fieldCOV_RATE.setTypeName("FLOAT(16)");
fieldCOV_RATE.setSize(18);
fieldCOV_RATE.setSubSize(4);
fieldCOV_RATE.setIsPrimaryKey(false);
fieldCOV_RATE.setIsIdentity(false);
fieldCOV_RATE.setUnique(false);
fieldCOV_RATE.setShouldAllowNull(true);
table.addField(fieldCOV_RATE);
FieldDefinition fieldCNST_DTE = new FieldDefinition();
fieldCNST_DTE.setName("CNST_DTE");
fieldCNST_DTE.setTypeName("DATE");
fieldCNST_DTE.setSize(7);
fieldCNST_DTE.setSubSize(0);
fieldCNST_DTE.setIsPrimaryKey(false);
fieldCNST_DTE.setIsIdentity(false);
fieldCNST_DTE.setUnique(false);
fieldCNST_DTE.setShouldAllowNull(true);
table.addField(fieldCNST_DTE);
ForeignKeyConstraint foreignKeyFK_POLICY_SSN = new ForeignKeyConstraint();
foreignKeyFK_POLICY_SSN.setName("POLICY_HOLDER");
foreignKeyFK_POLICY_SSN.setTargetTable("HOLDER");
foreignKeyFK_POLICY_SSN.addSourceField("SSN");
foreignKeyFK_POLICY_SSN.addTargetField("SSN");
table.addForeignKeyConstraint(foreignKeyFK_POLICY_SSN);
return table;
}
/**
* @param rs ResultSet from {@link DatabaseMetaData#getImportedKeys(String, String, String)}
* rs.getString("FK_NAME"); rs.getString("FKCOLUMN_NAME"); rs.getString("PKTABLE_SCHEM");
* rs.getString("PKTABLE_NAME"); rs.getString("PKCOLUMN_NAME");
* @param tables Map
* @param db
* @throws SQLException
*/
protected void addForeignKey(
String fkName,
String fkColName,
String pkTableSchema,
String pkTableName,
String pkColName,
Map<String, Table> tables,
Database db,
Properties properties,
Pattern excludeIndirectColumns,
Pattern excludeColumns)
throws SQLException {
if (fkName == null) return;
ForeignKeyConstraint foreignKey = getForeignKey(fkName);
if (foreignKey == null) {
foreignKey = new ForeignKeyConstraint(this, fkName);
foreignKeys.put(foreignKey.getName(), foreignKey);
}
TableColumn childColumn = getColumn(fkColName);
if (childColumn != null) {
foreignKey.addChildColumn(childColumn);
Table parentTable = tables.get(pkTableName);
if (parentTable == null) {
String otherSchema = pkTableSchema;
if (otherSchema != null
&& !otherSchema.equals(getSchema())
&& Config.getInstance().isOneOfMultipleSchemas()) {
parentTable =
db.addRemoteTable(
otherSchema,
pkTableName,
getSchema(),
properties,
excludeIndirectColumns,
excludeColumns);
}
}
if (parentTable != null) {
TableColumn parentColumn = parentTable.getColumn(pkColName);
if (parentColumn != null) {
foreignKey.addParentColumn(parentColumn);
childColumn.addParent(parentColumn, foreignKey);
parentColumn.addChild(childColumn, foreignKey);
} else {
System.err.println(
"Couldn't add FK '"
+ foreignKey.getName()
+ "' to table '"
+ this
+ "' - Column '"
+ pkColName
+ "' doesn't exist in table '"
+ parentTable
+ "'");
}
} else {
System.err.println(
"Couldn't add FK '"
+ foreignKey.getName()
+ "' to table '"
+ this
+ "' - Unknown Referenced Table '"
+ pkTableName
+ "'");
}
} else {
System.err.println(
"Couldn't add FK '"
+ foreignKey.getName()
+ "' to table '"
+ this
+ "' - Column '"
+ fkColName
+ "' doesn't exist");
}
}
public TableDefinition buildINS_ADDRTable() {
TableDefinition table = new TableDefinition();
table.setName("INS_ADDR");
FieldDefinition fieldSSN = new FieldDefinition();
fieldSSN.setName("SSN");
fieldSSN.setTypeName("NUMBER");
fieldSSN.setSize(18);
fieldSSN.setSubSize(0);
fieldSSN.setIsPrimaryKey(true);
fieldSSN.setIsIdentity(false);
fieldSSN.setUnique(false);
fieldSSN.setShouldAllowNull(false);
table.addField(fieldSSN);
FieldDefinition fieldSTREET = new FieldDefinition();
fieldSTREET.setName("STREET");
fieldSTREET.setTypeName("VARCHAR2");
fieldSTREET.setSize(30);
fieldSTREET.setSubSize(0);
fieldSTREET.setIsPrimaryKey(false);
fieldSTREET.setIsIdentity(false);
fieldSTREET.setUnique(false);
fieldSTREET.setShouldAllowNull(true);
table.addField(fieldSTREET);
FieldDefinition fieldCITY = new FieldDefinition();
fieldCITY.setName("CITY");
fieldCITY.setTypeName("VARCHAR2");
fieldCITY.setSize(25);
fieldCITY.setSubSize(0);
fieldCITY.setIsPrimaryKey(false);
fieldCITY.setIsIdentity(false);
fieldCITY.setUnique(false);
fieldCITY.setShouldAllowNull(true);
table.addField(fieldCITY);
FieldDefinition fieldSTATE = new FieldDefinition();
fieldSTATE.setName("STATE");
fieldSTATE.setTypeName("VARCHAR2");
fieldSTATE.setSize(2);
fieldSTATE.setSubSize(0);
fieldSTATE.setIsPrimaryKey(false);
fieldSTATE.setIsIdentity(false);
fieldSTATE.setUnique(false);
fieldSTATE.setShouldAllowNull(true);
table.addField(fieldSTATE);
FieldDefinition fieldCOUNTRY = new FieldDefinition();
fieldCOUNTRY.setName("COUNTRY");
fieldCOUNTRY.setTypeName("VARCHAR2");
fieldCOUNTRY.setSize(20);
fieldCOUNTRY.setSubSize(0);
fieldCOUNTRY.setIsPrimaryKey(false);
fieldCOUNTRY.setIsIdentity(false);
fieldCOUNTRY.setUnique(false);
fieldCOUNTRY.setShouldAllowNull(true);
table.addField(fieldCOUNTRY);
FieldDefinition fieldZIPCODE = new FieldDefinition();
fieldZIPCODE.setName("ZIPCODE");
fieldZIPCODE.setTypeName("VARCHAR2");
fieldZIPCODE.setSize(10);
fieldZIPCODE.setSubSize(0);
fieldZIPCODE.setIsPrimaryKey(false);
fieldZIPCODE.setIsIdentity(false);
fieldZIPCODE.setUnique(false);
fieldZIPCODE.setShouldAllowNull(true);
table.addField(fieldZIPCODE);
ForeignKeyConstraint foreignKeyFK_INS_ADDR_SSN = new ForeignKeyConstraint();
foreignKeyFK_INS_ADDR_SSN.setName("ADDRESS_HOLDER");
foreignKeyFK_INS_ADDR_SSN.setTargetTable("HOLDER");
foreignKeyFK_INS_ADDR_SSN.addSourceField("SSN");
foreignKeyFK_INS_ADDR_SSN.addTargetField("SSN");
table.addForeignKeyConstraint(foreignKeyFK_INS_ADDR_SSN);
return table;
}
