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; }