protected CFSecurityISOCountryLanguageBuff unpackISOCountryLanguageResultSetToBuff( ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackISOCountryLanguageResultSetToBuff"; int idxcol = 1; CFSecurityISOCountryLanguageBuff buff = schema.getFactoryISOCountryLanguage().newBuff(); { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedAt(null); } else { buff.setCreatedAt(CFCrmMySqlSchema.convertTimestampString(colString)); } } idxcol++; { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedByUserId(null); } else { buff.setCreatedByUserId(UUID.fromString(colString)); } } idxcol++; { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedAt(null); } else { buff.setUpdatedAt(CFCrmMySqlSchema.convertTimestampString(colString)); } } idxcol++; { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedByUserId(null); } else { buff.setUpdatedByUserId(UUID.fromString(colString)); } } idxcol++; buff.setRequiredISOCountryId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredISOLanguageId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }
protected CFInternetURLProtocolBuff unpackURLProtocolResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackURLProtocolResultSetToBuff"; int idxcol = 1; CFInternetURLProtocolBuff buff = schema.getFactoryURLProtocol().newBuff(); { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedByUserId(null); } else { buff.setCreatedByUserId(UUID.fromString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedAt(null); } else { buff.setCreatedAt(CFBamDb2LUWSchema.convertTimestampString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedByUserId(null); } else { buff.setUpdatedByUserId(UUID.fromString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedAt(null); } else { buff.setUpdatedAt(CFBamDb2LUWSchema.convertTimestampString(colString)); } idxcol++; } buff.setRequiredURLProtocolId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredName(resultSet.getString(idxcol)); idxcol++; buff.setRequiredDescription(resultSet.getString(idxcol)); idxcol++; buff.setRequiredIsSecure(("Y".equals(resultSet.getString(idxcol)) ? true : false)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }
@SuppressWarnings("unchecked") public static <T> T getValueFromResultSet( int index, final ResultSet resultSet, final Class<T> type) { try { if (java.sql.Date.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new java.sql.Date(time); } else if (Time.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new java.sql.Time(time); } else if (Timestamp.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new java.sql.Timestamp(time); } else if (Date.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new Date(time); } else if (Long.class.isAssignableFrom(type)) { return (T) Long.valueOf(resultSet.getLong(index)); } else if (Integer.class.isAssignableFrom(type)) { return (T) Integer.valueOf(resultSet.getInt(index)); } else if (Short.class.isAssignableFrom(type)) { return (T) Short.valueOf(resultSet.getShort(index)); } else if (Float.class.isAssignableFrom(type)) { return (T) Float.valueOf(resultSet.getFloat(index)); } else if (Double.class.isAssignableFrom(type)) { return (T) Double.valueOf(resultSet.getDouble(index)); } else if (Boolean.class.isAssignableFrom(type)) { return (T) Boolean.valueOf(resultSet.getBoolean(index)); } else if (BigDecimal.class.isAssignableFrom(type)) { return (T) resultSet.getBigDecimal(index); } else if (CharSequence.class.isAssignableFrom(type)) { return (T) resultSet.getString(index); } else if (byte[].class.isAssignableFrom(type)) { return (T) resultSet.getBytes(index); } else { throw new IllegalStateException("Type " + type + " not supported."); } } catch (Exception e) { throw new IllegalStateException( "Unable to read the value from the resultSet. Index:" + index + ", type: " + type, e); } }
@Override public List<ForeignKey> getAdditionalForeignKeys(String schemaName, Database database) throws DatabaseException { List<ForeignKey> foreignKeys = super.getAdditionalForeignKeys(schemaName, database); // Setting default schema name. Needed for correct statement generation if (schemaName == null) { schemaName = database.convertRequestedSchemaToSchema(schemaName); } // Create SQL statement to select all FKs in database which referenced to unique columns String query = "select uc_fk.constraint_name FK_NAME,uc_fk.owner FKTABLE_SCHEM,ucc_fk.table_name FKTABLE_NAME,ucc_fk.column_name FKCOLUMN_NAME,decode(uc_fk.deferrable, 'DEFERRABLE', 5 ,'NOT DEFERRABLE', 7 , 'DEFERRED', 6 ) DEFERRABILITY, decode(uc_fk.delete_rule, 'CASCADE', 0,'NO ACTION', 3) DELETE_RULE,ucc_rf.table_name PKTABLE_NAME,ucc_rf.column_name PKCOLUMN_NAME from all_cons_columns ucc_fk,all_constraints uc_fk,all_cons_columns ucc_rf,all_constraints uc_rf where uc_fk.CONSTRAINT_NAME = ucc_fk.CONSTRAINT_NAME and uc_fk.constraint_type='R' and uc_fk.r_constraint_name=ucc_rf.CONSTRAINT_NAME and uc_rf.constraint_name = ucc_rf.constraint_name and uc_rf.constraint_type = 'U' and uc_fk.owner = '" + schemaName + "' and ucc_fk.owner = '" + schemaName + "' and uc_rf.owner = '" + schemaName + "' and ucc_rf.owner = '" + schemaName + "'"; Statement statement = null; ResultSet rs = null; try { statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement(); rs = statement.executeQuery(query); while (rs.next()) { ForeignKeyInfo fkInfo = new ForeignKeyInfo(); fkInfo.setReferencesUniqueColumn(true); fkInfo.setFkName(convertFromDatabaseName(rs.getString("FK_NAME"))); fkInfo.setFkSchema(convertFromDatabaseName(rs.getString("FKTABLE_SCHEM"))); fkInfo.setFkTableName(convertFromDatabaseName(rs.getString("FKTABLE_NAME"))); fkInfo.setFkColumn(convertFromDatabaseName(rs.getString("FKCOLUMN_NAME"))); fkInfo.setPkTableName(convertFromDatabaseName(rs.getString("PKTABLE_NAME"))); fkInfo.setPkColumn(convertFromDatabaseName(rs.getString("PKCOLUMN_NAME"))); fkInfo.setDeferrablility(rs.getShort("DEFERRABILITY")); ForeignKeyConstraintType deleteRule = convertToForeignKeyConstraintType(rs.getInt("DELETE_RULE")); if (rs.wasNull()) { deleteRule = null; } fkInfo.setDeleteRule(deleteRule); foreignKeys.add(generateForeignKey(fkInfo, database, foreignKeys)); } } catch (SQLException e) { throw new DatabaseException( "Can't execute selection query to generate list of foreign keys", e); } finally { JdbcUtils.closeResultSet(rs); JdbcUtils.closeStatement(statement); } return foreignKeys; }
protected CFSecurityAuditActionBuff unpackAuditActionResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackAuditActionResultSetToBuff"; int idxcol = 1; CFSecurityAuditActionBuff buff = schema.getFactoryAuditAction().newBuff(); buff.setRequiredAuditActionId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredDescription(resultSet.getString(idxcol)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }
protected CFBamAccessSecurityBuff unpackAccessSecurityResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackAccessSecurityResultSetToBuff"; int idxcol = 1; CFBamAccessSecurityBuff buff = schema.getFactoryAccessSecurity().newBuff(); buff.setRequiredId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredName(resultSet.getString(idxcol)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }
@Test public void colNameAccess() throws SQLException { PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;"); prep.setNull(1, 0); prep.setFloat(2, Float.MIN_VALUE); prep.setShort(3, Short.MIN_VALUE); prep.executeQuery(); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertNull(rs.getString("col1")); assertTrue(rs.wasNull()); assertEquals(Float.MIN_VALUE, rs.getFloat("col2")); assertEquals(Short.MIN_VALUE, rs.getShort("bingo")); rs.close(); prep.close(); }
protected CFSecurityISOCurrencyBuff unpackISOCurrencyResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackISOCurrencyResultSetToBuff"; int idxcol = 1; CFSecurityISOCurrencyBuff buff = schema.getFactoryISOCurrency().newBuff(); { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedByUserId(null); } else { buff.setCreatedByUserId(UUID.fromString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedAt(null); } else { buff.setCreatedAt(CFInternetMSSqlSchema.convertTimestampString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedByUserId(null); } else { buff.setUpdatedByUserId(UUID.fromString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedAt(null); } else { buff.setUpdatedAt(CFInternetMSSqlSchema.convertTimestampString(colString)); } idxcol++; } buff.setRequiredISOCurrencyId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredISOCode(resultSet.getString(idxcol)); idxcol++; buff.setRequiredName(resultSet.getString(idxcol)); idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalUnitSymbol(null); } else { buff.setOptionalUnitSymbol(colVal); } } idxcol++; buff.setRequiredPrecis(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }
protected CFSecurityISOTimezoneBuff unpackISOTimezoneResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackISOTimezoneResultSetToBuff"; int idxcol = 1; CFSecurityISOTimezoneBuff buff = schema.getFactoryISOTimezone().newBuff(); { String colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedByUserId(null); } else { buff.setCreatedByUserId(UUID.fromString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setCreatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setCreatedAt(null); } else { buff.setCreatedAt(CFDbTestPgSqlSchema.convertTimestampString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedByUserId(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedByUserId(null); } else { buff.setUpdatedByUserId(UUID.fromString(colString)); } idxcol++; colString = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setUpdatedAt(null); } else if ((colString == null) || (colString.length() <= 0)) { buff.setUpdatedAt(null); } else { buff.setUpdatedAt(CFDbTestPgSqlSchema.convertTimestampString(colString)); } idxcol++; } buff.setRequiredISOTimezoneId(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredIso8601(resultSet.getString(idxcol)); idxcol++; buff.setRequiredTZName(resultSet.getString(idxcol)); idxcol++; buff.setRequiredTZHourOffset(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredTZMinOffset(resultSet.getShort(idxcol)); idxcol++; buff.setRequiredDescription(resultSet.getString(idxcol)); idxcol++; buff.setRequiredVisible(resultSet.getBoolean(idxcol)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }
@Override protected void readPrimaryKeys( DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData) throws DatabaseException, SQLException { Database database = snapshot.getDatabase(); updateListeners("Reading primary keys for " + database.toString() + " ..."); // we can't add directly to the this.primaryKeys hashSet because adding columns to an exising PK // changes the hashCode and .contains() fails List<PrimaryKey> foundPKs = new ArrayList<PrimaryKey>(); // Setting default schema name. Needed for correct statement generation if (schema == null) schema = database.convertRequestedSchemaToSchema(schema); String query = "select uc.table_name TABLE_NAME,ucc.column_name COLUMN_NAME,ucc.position KEY_SEQ,uc.constraint_name PK_NAME,ui.tablespace_name TABLESPACE from all_constraints uc,all_indexes ui,all_cons_columns ucc where uc.constraint_type = 'P' and uc.index_name = ui.index_name and uc.constraint_name = ucc.constraint_name and uc.owner = '" + schema + "' and ui.table_owner = '" + schema + "' and ucc.owner = '" + schema + "' and uc.table_name = ui.table_name and ui.table_name = ucc.table_name"; Statement statement = null; ResultSet rs = null; try { statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement(); rs = statement.executeQuery(query); while (rs.next()) { String tableName = convertFromDatabaseName(rs.getString("TABLE_NAME")); String tablespace = convertFromDatabaseName(rs.getString("TABLESPACE")); String columnName = convertFromDatabaseName(rs.getString("COLUMN_NAME")); short position = rs.getShort("KEY_SEQ"); boolean foundExistingPK = false; for (PrimaryKey pk : foundPKs) { if (pk.getTable().getName().equals(tableName)) { pk.addColumnName(position - 1, columnName); foundExistingPK = true; } } if (!foundExistingPK && !database.isLiquibaseTable(tableName)) { PrimaryKey primaryKey = new PrimaryKey(); primaryKey.setTablespace(tablespace); Table table = snapshot.getTable(tableName); if (table == null) { continue; // probably a different schema } primaryKey.setTable(table); primaryKey.addColumnName(position - 1, columnName); primaryKey.setName(convertPrimaryKeyName(rs.getString("PK_NAME"))); foundPKs.add(primaryKey); } } } finally { JdbcUtils.closeResultSet(rs); JdbcUtils.closeStatement(statement); } snapshot.getPrimaryKeys().addAll(foundPKs); }
@Override protected void readIndexes( DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData) throws DatabaseException, SQLException { Database database = snapshot.getDatabase(); updateListeners("Reading indexes for " + database.toString() + " ..."); String query = "select aic.index_name, 3 AS TYPE, aic.table_name, aic.column_name, aic.column_position AS ORDINAL_POSITION, null AS FILTER_CONDITION, ai.tablespace_name AS TABLESPACE, ai.uniqueness FROM all_ind_columns aic, all_indexes ai WHERE aic.table_owner='" + database.convertRequestedSchemaToSchema(schema) + "' and aic.index_name = ai.index_name ORDER BY INDEX_NAME, ORDINAL_POSITION"; Statement statement = null; ResultSet rs = null; Map<String, Index> indexMap = null; try { statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement(); rs = statement.executeQuery(query); indexMap = new HashMap<String, Index>(); while (rs.next()) { String indexName = convertFromDatabaseName(rs.getString("INDEX_NAME")); String tableName = rs.getString("TABLE_NAME"); String tableSpace = rs.getString("TABLESPACE"); String columnName = convertFromDatabaseName(rs.getString("COLUMN_NAME")); if (columnName == null) { // nothing to index, not sure why these come through sometimes continue; } short type = rs.getShort("TYPE"); boolean nonUnique; String uniqueness = rs.getString("UNIQUENESS"); if ("UNIQUE".equals(uniqueness)) { nonUnique = false; } else { nonUnique = true; } short position = rs.getShort("ORDINAL_POSITION"); String filterCondition = rs.getString("FILTER_CONDITION"); if (type == DatabaseMetaData.tableIndexStatistic) { continue; } Index index; if (indexMap.containsKey(indexName)) { index = indexMap.get(indexName); } else { index = new Index(); Table table = snapshot.getTable(tableName); if (table == null) { continue; // probably different schema } index.setTable(table); index.setTablespace(tableSpace); index.setName(indexName); index.setUnique(!nonUnique); index.setFilterCondition(filterCondition); indexMap.put(indexName, index); } for (int i = index.getColumns().size(); i < position; i++) { index.getColumns().add(null); } index.getColumns().set(position - 1, columnName); } } finally { JdbcUtils.closeResultSet(rs); JdbcUtils.closeStatement(statement); } for (Map.Entry<String, Index> entry : indexMap.entrySet()) { snapshot.getIndexes().add(entry.getValue()); } /* * marks indexes as "associated with" instead of "remove it" * Index should have associations with: * foreignKey, primaryKey or uniqueConstraint * */ for (Index index : snapshot.getIndexes()) { for (PrimaryKey pk : snapshot.getPrimaryKeys()) { if (index.getTable().getName().equalsIgnoreCase(pk.getTable().getName()) && index.getColumnNames().equals(pk.getColumnNames())) { index.addAssociatedWith(Index.MARK_PRIMARY_KEY); } } for (ForeignKey fk : snapshot.getForeignKeys()) { if (index.getTable().getName().equalsIgnoreCase(fk.getForeignKeyTable().getName()) && index.getColumnNames().equals(fk.getForeignKeyColumns())) { index.addAssociatedWith(Index.MARK_FOREIGN_KEY); } } for (UniqueConstraint uc : snapshot.getUniqueConstraints()) { if (index.getTable().getName().equalsIgnoreCase(uc.getTable().getName()) && index.getColumnNames().equals(uc.getColumnNames())) { index.addAssociatedWith(Index.MARK_UNIQUE_CONSTRAINT); } } } }