protected Column createColumnFromConstant(String columnName, String constantValue) { ColumnEditor column = Column.editor().name(columnName); try { if (constantValue.startsWith("'") || constantValue.startsWith("\"")) { column.type("CHAR"); column.jdbcType(Types.CHAR); column.length(constantValue.length() - 2); } else if (constantValue.equalsIgnoreCase("TRUE") || constantValue.equalsIgnoreCase("FALSE")) { column.type("BOOLEAN"); column.jdbcType(Types.BOOLEAN); } else { setTypeInfoForConstant(constantValue, column); } } catch (Throwable t) { logger.debug("Unable to create an artificial column for the constant: " + constantValue); } return column.create(); }
/** * Returns a {@link ColumnEditor} representing the current record of the given result set of column metadata, if * included in the column whitelist. */ protected Optional<ColumnEditor> readTableColumn(ResultSet columnMetadata, TableId tableId, ColumnNameFilter columnFilter) throws SQLException { final String columnName = columnMetadata.getString(4); if (columnFilter == null || columnFilter.matches(tableId.catalog(), tableId.schema(), tableId.table(), columnName)) { final ColumnEditor column = Column.editor().name(columnName); column.jdbcType(columnMetadata.getInt(5)); column.type(columnMetadata.getString(6)); column.length(columnMetadata.getInt(7)); if (columnMetadata.getObject(9) != null) { column.scale(columnMetadata.getInt(9)); } column.optional(isNullable(columnMetadata.getInt(11))); column.position(columnMetadata.getInt(17)); column.autoIncremented("YES".equalsIgnoreCase(columnMetadata.getString(23))); String autogenerated = null; try { autogenerated = columnMetadata.getString(24); } catch (SQLException e) { // ignore, some drivers don't have this index - e.g. Postgres } column.generated("YES".equalsIgnoreCase(autogenerated)); column.nativeType(resolveNativeType(column.typeName())); return Optional.of(column); } return Optional.empty(); }
/** * Determine the column definitions for the supplied result set and add each column to the specified consumer. * * @param resultSet the query result set; may not be null * @param consumer the consumer of the definitions; may not be null * @throws SQLException if an error occurs while using the result set */ public static void columnsFor(ResultSet resultSet, Consumer<Column> consumer) throws SQLException { ResultSetMetaData metadata = resultSet.getMetaData(); ColumnEditor column = Column.editor(); for (int position = 1; position <= metadata.getColumnCount(); ++position) { String columnLabel = metadata.getColumnLabel(position); column.name(columnLabel != null ? columnLabel : metadata.getColumnName(position)); column.type(metadata.getColumnTypeName(position)); column.jdbcType(metadata.getColumnType(position)); column.length(metadata.getPrecision(position)); column.scale(metadata.getScale(position)); column.optional(isNullable(metadata.isNullable(position))); column.autoIncremented(metadata.isAutoIncrement(position)); column.generated(false); consumer.accept(column.create()); } }
private Table tableFromFromMessage(List<ReplicationMessage.Column> columns, Table table) { return table.edit() .setColumns(columns.stream() .map(column -> { final PostgresType type = column.getType(); final ColumnEditor columnEditor = Column.editor() .name(column.getName()) .jdbcType(type.getJdbcId()) .type(type.getName()) .optional(column.isOptional()) .nativeType(type.getOid()); columnEditor.length(column.getTypeMetadata().getLength()); columnEditor.scale(column.getTypeMetadata().getScale()); return columnEditor.create(); }) .collect(Collectors.toList()) ) .setPrimaryKeyNames(table.filterColumnNames(c -> table.isPrimaryKeyColumn(c.name()))).create(); } }
@Test(expected = IllegalArgumentException.class) public void shouldNotAllowAddingPrimaryKeyColumnWhenNotFound() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).position(1).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).position(1).create(); editor.addColumns(c1, c2, c3); editor.setPrimaryKeyNames("C1", "WOOPS"); }
@Before public void beforeEach() { column = Column.editor().name("firstName").jdbcType(Types.VARCHAR).type("VARCHAR").position(1).create(); column2 = Column.editor().name("lastName").jdbcType(Types.VARCHAR).type("VARCHAR").position(2).create(); column3 = Column.editor().name("otherColumn").jdbcType(Types.VARCHAR).type("VARCHAR").position(3).create(); fullyQualifiedNames = tableId + "." + column.name() + "," + tableId + "." + column3.name() + ","; }
@Test(expected = IllegalArgumentException.class) public void shouldNotReorderColumnIfNameDoesNotMatch() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).autoIncremented(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).autoIncremented(true).create(); editor.addColumns(c1, c2, c3); editor.reorderColumn("WOOPS", "C2"); assertValidPositions(editor); }
@Test @FixFor("DBZ-644") public void shouldAddTypeInformation() { int length = 255; Column column = Column.editor() .name("col") .type("VARCHAR") .jdbcType(Types.VARCHAR) .length(length) .create(); SchemaBuilder schemaBuilder = SchemaBuilder.string(); new PropagateSourceTypeToSchemaParameter().alterFieldSchema(column, schemaBuilder); assertThat(schemaBuilder.parameters().get("__debezium.source.column.type")).isEqualTo("VARCHAR"); assertThat(schemaBuilder.parameters().get("__debezium.source.column.length")).isEqualTo(String.valueOf(length)); }
@Test public void shouldRemoveColumnByName() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).autoIncremented(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).autoIncremented(true).create(); editor.addColumns(c1, c2, c3); editor.removeColumn("C2"); assertThat(editor.columns()).containsExactly(editor.columnWithName("C1"), editor.columnWithName("C3")); assertValidPositions(editor); }
@Test @FixFor("DBZ-1073") public void shouldAddColumnScale() { int length = 5; int scale = 2; Column column = Column.editor() .name("col") .type("NUMERIC") .jdbcType(Types.NUMERIC) .length(length) .scale(scale) .create(); SchemaBuilder schemaBuilder = SchemaBuilder.string(); new PropagateSourceTypeToSchemaParameter().alterFieldSchema(column, schemaBuilder); assertThat(schemaBuilder.parameters().get("__debezium.source.column.type")).isEqualTo("NUMERIC"); assertThat(schemaBuilder.parameters().get("__debezium.source.column.length")).isEqualTo(String.valueOf(length)); assertThat(schemaBuilder.parameters().get("__debezium.source.column.scale")).isEqualTo(String.valueOf(scale)); } }
@Test public void shouldFindNonExistingColumnByNameIndependentOfCase() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).position(1).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).position(1).create(); editor.addColumns(c1, c2, c3); editor.columns().forEach(col -> { assertThat(editor.columnWithName(col.name())).isNotNull(); assertThat(editor.columnWithName(col.name().toUpperCase())).isNotNull(); assertThat(editor.columnWithName(col.name().toLowerCase())).isNotNull(); }); assertThat(editor.columnWithName("WOOPS")).isNull(); }
protected void createColumnWithAllFieldsSetToNonDefaults() { column = editor.name("price") .type("NUMBER") .jdbcType(Types.DOUBLE) .length(5) .scale(2) .position(4) .optional(true) .autoIncremented(true) .generated(true) .create(); }
private ValueConverter createArrayConverter(Column column, Field fieldDefn) { PostgresType arrayType = typeRegistry.get(column.nativeType()); PostgresType elementType = arrayType.getElementType(); final String elementTypeName = elementType.getName(); final String elementColumnName = column.name() + "-element"; final Column elementColumn = Column.editor() .name(elementColumnName) .jdbcType(elementType.getJdbcId()) .nativeType(elementType.getOid()) .type(elementTypeName) .optional(true) .scale(column.scale().orElse(null)) .length(column.length()) .create(); Schema elementSchema = schemaBuilder(elementColumn) .optional() .build(); final Field elementField = new Field(elementColumnName, 0, elementSchema); final ValueConverter elementConverter = converter(elementColumn, elementField); return data -> convertArray(column, fieldDefn, elementConverter, data); }
@Test public void shouldFindGeneratedColumns() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).generated(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).generated(true).create(); editor.addColumns(c1, c2, c3); editor.setPrimaryKeyNames("C1"); table = editor.create(); assertThat(table.retrieveColumnNames()).containsExactly("C1", "C2", "C3"); table.columns().forEach(col -> { assertThat(table.isGenerated(col.name())).isEqualTo(col.isGenerated()); }); assertValidPositions(editor); }
@Test public void shouldFindAutoIncrementedColumns() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).autoIncremented(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).autoIncremented(true).create(); editor.addColumns(c1, c2, c3); editor.setPrimaryKeyNames("C1"); table = editor.create(); assertThat(table.retrieveColumnNames()).containsExactly("C1", "C2", "C3"); table.columns().forEach(col -> { assertThat(table.isAutoIncremented(col.name())).isEqualTo(col.isAutoIncremented()); }); assertValidPositions(editor); }
@Override public ColumnEditor edit() { final ColumnEditor editor = Column.editor() .name(name()) .type(typeName(), typeExpression()) .jdbcType(jdbcType()) .nativeType(nativeType) .charsetName(charsetName) .length(length()) .scale(scale().orElse(null)) .position(position()) .optional(isOptional()) .autoIncremented(isAutoIncremented()) .generated(isGenerated()); if (hasDefaultValue()) { editor.defaultValue(defaultValue()); } return editor; } }
column.type(dataType.name(),dataType.expression()); if ( dataType.length() > -1 ) column.length((int)dataType.length()); if ( dataType.scale() > -1 ) column.scale(dataType.scale());
@Before public void beforeEach() { table = Table.editor() .tableId(id) .addColumns(Column.editor().name("C1") .type("VARCHAR").jdbcType(Types.VARCHAR).length(10) .generated(true) .optional(false) .create(), Column.editor().name("C2") .type("NUMBER").jdbcType(Types.NUMERIC).length(5) .optional(false) .create(), Column.editor().name("C3") .type("DATE").jdbcType(Types.DATE).length(4) .optional(true) .create(), Column.editor().name("C4") .type("COUNTER").jdbcType(Types.INTEGER) .autoIncremented(true) .optional(true) .create()) .setPrimaryKeyNames("C1", "C2") .create(); c1 = table.columnWithName("C1"); c2 = table.columnWithName("C2"); c3 = table.columnWithName("C3"); c4 = table.columnWithName("C4"); }
@Test public void shouldAllowAddingPrimaryKeyColumnWhenFound() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).position(1).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).position(1).create(); editor.addColumns(c1, c2, c3); editor.setPrimaryKeyNames("C1"); c1 = editor.columnWithName(c1.name()); c2 = editor.columnWithName(c2.name()); c3 = editor.columnWithName(c3.name()); assertThat(c1.position()).isEqualTo(1); assertThat(c2.position()).isEqualTo(2); assertThat(c3.position()).isEqualTo(3); table = editor.create(); assertThat(table.retrieveColumnNames()).containsExactly("C1", "C2", "C3"); assertThat(table.columns()).containsExactly(c1, c2, c3); assertThat(table.primaryKeyColumnNames()).containsOnly("C1"); assertValidPositions(editor); }
@Test public void shouldReorderColumns() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).autoIncremented(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).autoIncremented(true).create(); editor.addColumns(c1, c2, c3); assertValidPositions(editor);