@Test public void parseDateDefaultValue() { String sql = "CREATE TABLE DATE_TABLE (" + " A date NOT NULL DEFAULT '0000-00-00'," + " B date NOT NULL DEFAULT '2018-00-01'," + " C date NOT NULL DEFAULT '0000-12-31'," + " D date NOT NULL DEFAULT '2018-01-00'," + " E date NOT NULL DEFAULT '9999-09-09'," + " F date NOT NULL DEFAULT '1111-11-11'," + " G date NOT NULL DEFAULT '2018-08-31'," + " H date NOT NULL DEFAULT 0" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "DATE_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); assertThat(table.columnWithName("B").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); assertThat(table.columnWithName("C").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); assertThat(table.columnWithName("D").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(Date.from(ZonedDateTime.of(9999, 9, 9, 0, 0, 0, 0, ZoneOffset.UTC).toInstant())); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(Date.from(ZonedDateTime.of(1111, 11, 11, 0, 0, 0, 0, ZoneOffset.UTC).toInstant())); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(Date.from(ZonedDateTime.of(2018, 8, 31, 0, 0, 0, 0, ZoneOffset.UTC).toInstant())); assertThat(table.columnWithName("H").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); }
@Test @FixFor("DBZ-901") public void parseAlterTableTruncatedDefaulDateTime() { String sql = "CREATE TABLE TIME_TABLE (" + " A datetime(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000'" + ");"; String alterSql = "ALTER TABLE TIME_TABLE ADD COLUMN B DATETIME(3) NOT NULL DEFAULT '1970-01-01 00:00:00';"; parser.parse(sql, tables); parser.parse(alterSql, tables); Table table = tables.forTable(new TableId(null, null, "TIME_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); assertThat(table.columnWithName("B").defaultValue()).isEqualTo((Date.from(Instant.ofEpochMilli(0)))); }
@Test public void parseNumericAndDecimalToDecimalDefaultValue() { final MySqlValueConverters converters = new MySqlValueConverters(JdbcValueConverters.DecimalMode.PRECISE, TemporalPrecisionMode.CONNECT, JdbcValueConverters.BigIntUnsignedMode.LONG); final AbstractDdlParser parser = parserProducer.apply(converters); String sql = "CREATE TABLE NUMERIC_DECIMAL_TABLE (\n" + " A NUMERIC NOT NULL DEFAULT 1.23,\n" + " B DECIMAL NOT NULL DEFAULT 2.321,\n" + " C NUMERIC NULL DEFAULT '12.678'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "NUMERIC_DECIMAL_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(BigDecimal.valueOf(1.23)); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(BigDecimal.valueOf(2.321)); assertThat(table.columnWithName("C").defaultValue()).isEqualTo(BigDecimal.valueOf(12.678)); }
/** * Add to the supplied {@link SchemaBuilder} a field for the column with the given information. * * @param builder the schema builder; never null * @param column the column definition * @param mapper the mapping function for the column; may be null if the columns is not to be mapped to different values */ protected void addField(SchemaBuilder builder, Column column, ColumnMapper mapper) { SchemaBuilder fieldBuilder = valueConverterProvider.schemaBuilder(column); if (fieldBuilder != null) { if (mapper != null) { // Let the mapper add properties to the schema ... mapper.alterFieldSchema(column, fieldBuilder); } if (column.isOptional()) fieldBuilder.optional(); // if the default value is provided if (column.hasDefaultValue()) { fieldBuilder.defaultValue(column.defaultValue()); } builder.field(column.name(), fieldBuilder.build()); if (LOGGER.isDebugEnabled()) { LOGGER.debug("- field '{}' ({}{}) from column {}", column.name(), builder.isOptional() ? "OPTIONAL " : "", fieldBuilder.type(), column); } } else { LOGGER.warn("Unexpected JDBC type '{}' for column '{}' that will be ignored", column.jdbcType(), column.name()); } }
@Test public void parseNumericAndDecimalToDoubleDefaultValue() { String sql = "CREATE TABLE NUMERIC_DECIMAL_TABLE (\n" + " A NUMERIC NOT NULL DEFAULT 1.23,\n" + " B DECIMAL NOT NULL DEFAULT 2.321,\n" + " C NUMERIC NULL DEFAULT '12.678'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "NUMERIC_DECIMAL_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(1.23d); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(2.321d); assertThat(table.columnWithName("C").defaultValue()).isEqualTo(12.678d); }
@Test public void parseBooleanDefaultValue() { String sql = "CREATE TABLE BOOLEAN_TABLE (\n" + " A BOOLEAN NULL DEFAULT 0,\n" + " B BOOLEAN NOT NULL DEFAULT '1',\n" + " C BOOLEAN NOT NULL DEFAULT '9',\n" + " D BOOLEAN NOT NULL DEFAULT TRUE,\n" + " E BOOLEAN DEFAULT NULL\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "BOOLEAN_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(false); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(true); assertThat(table.columnWithName("C").defaultValue()).isEqualTo(true); assertThat(table.columnWithName("D").defaultValue()).isEqualTo(true); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(null); }
@Test public void parseNumberDefaultValue() { String sql = "CREATE TABLE NUMBER_TABLE (\n" + " A TINYINT NULL DEFAULT 10,\n" + " B SMALLINT NOT NULL DEFAULT '5',\n" + " C INTEGER NOT NULL DEFAULT 0,\n" + " D BIGINT NOT NULL DEFAULT 20,\n" + " E INT NULL DEFAULT NULL,\n" + " F FLOAT NULL DEFAULT 0,\n" + " G DOUBLE NOT NULL DEFAULT 1.0\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "NUMBER_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo((short) 10); assertThat(table.columnWithName("B").defaultValue()).isEqualTo((short) 5); assertThat(table.columnWithName("C").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("D").defaultValue()).isEqualTo(20L); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(null); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(0d); }
@Test public void parseRealDefaultValue() { String sql = "CREATE TABLE REAL_TABLE (\n" + " A REAL NOT NULL DEFAULT 1,\n" + " B REAL NULL DEFAULT NULL \n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "REAL_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(1f); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(null); }
@Override public boolean equals(Object obj) { if (obj == this) return true; if (obj instanceof Column) { Column that = (Column) obj; return this.name().equalsIgnoreCase(that.name()) && this.typeExpression().equalsIgnoreCase(that.typeExpression()) && this.typeName().equalsIgnoreCase(that.typeName()) && this.jdbcType() == that.jdbcType() && Strings.equalsIgnoreCase(this.charsetName(),that.charsetName()) && this.position() == that.position() && this.length() == that.length() && this.scale().equals(that.scale()) && this.isOptional() == that.isOptional() && this.isAutoIncremented() == that.isAutoIncremented() && this.isGenerated() == that.isGenerated() && Objects.equals(this.defaultValue(), that.defaultValue()) && this.hasDefaultValue() == that.hasDefaultValue(); } return false; }
@Test public void parseStringDefaultValue() { String sql = "CREATE TABLE UNSIGNED_STRING_TABLE (\n" + " A CHAR NULL DEFAULT 'A',\n" + " B CHAR NULL DEFAULT 'b',\n" + " C VARCHAR(10) NULL DEFAULT 'CC',\n" + " D NCHAR(10) NULL DEFAULT '10',\n" + " E NVARCHAR NULL DEFAULT '0',\n" + " F CHAR DEFAULT NULL,\n" + " G VARCHAR(10) DEFAULT NULL,\n" + " H NCHAR(10) DEFAULT NULL\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_STRING_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo("A"); assertThat(table.columnWithName("B").defaultValue()).isEqualTo("b"); assertThat(table.columnWithName("C").defaultValue()).isEqualTo("CC"); assertThat(table.columnWithName("D").defaultValue()).isEqualTo("10"); assertThat(table.columnWithName("E").defaultValue()).isEqualTo("0"); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(null); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(null); assertThat(table.columnWithName("H").defaultValue()).isEqualTo(null); }
@Test public void parseBitDefaultValue() { String sql = "CREATE TABLE BIT_TABLE (\n" + " A BIT(1) NULL DEFAULT NULL,\n" + " B BIT(1) DEFAULT 0,\n" + " C BIT(1) DEFAULT 1,\n" + " D BIT(1) DEFAULT b'0',\n" + " E BIT(1) DEFAULT b'1',\n" + " F BIT(1) DEFAULT TRUE,\n" + " G BIT(1) DEFAULT FALSE,\n" + " H BIT(10) DEFAULT b'101000010',\n" + " I BIT(10) DEFAULT NULL,\n" + " J BIT(25) DEFAULT b'10110000100001111'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "BIT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(null); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(false); assertThat(table.columnWithName("C").defaultValue()).isEqualTo(true); assertThat(table.columnWithName("D").defaultValue()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(true); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(true); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(false); assertThat(table.columnWithName("H").defaultValue()).isEqualTo(new byte[] {66, 1}); assertThat(table.columnWithName("I").defaultValue()).isEqualTo(null); assertThat(table.columnWithName("J").defaultValue()).isEqualTo(new byte[] {15, 97, 1, 0}); }
@Test public void parseDefaultValue() { String ddl = "CREATE TABLE tmp (id INT NOT NULL, " + "columnA CHAR(60) NOT NULL DEFAULT 'A'," + "columnB INT NOT NULL DEFAULT 1," + "columnC VARCHAR(10) NULL DEFAULT 'C'," + "columnD VARCHAR(10) NULL DEFAULT NULL," + "columnE VARCHAR(10) NOT NULL," + "my_date datetime NOT NULL DEFAULT '2018-04-27 13:28:43');"; parser.parse(ddl, tables); Table table = tables.forTable(new TableId(null, null, "tmp")); assertThat(table.columnWithName("id").isOptional()).isEqualTo(false); assertThat(table.columnWithName("columnA").defaultValue()).isEqualTo("A"); assertThat(table.columnWithName("columnB").defaultValue()).isEqualTo(1); assertThat(table.columnWithName("columnC").defaultValue()).isEqualTo("C"); assertThat(table.columnWithName("columnD").defaultValue()).isEqualTo(null); assertThat(table.columnWithName("columnE").defaultValue()).isEqualTo(null); }
@Test public void parseUnsignedBigIntDefaultValueToBigDecimal() { final MySqlValueConverters converters = new MySqlValueConverters(JdbcValueConverters.DecimalMode.DOUBLE, TemporalPrecisionMode.CONNECT, JdbcValueConverters.BigIntUnsignedMode.PRECISE); final AbstractDdlParser parser = parserProducer.apply(converters); String sql = "CREATE TABLE UNSIGNED_BIGINT_TABLE (\n" + " A BIGINT UNSIGNED NULL DEFAULT 0,\n" + " B BIGINT UNSIGNED NULL DEFAULT '10',\n" + " C BIGINT UNSIGNED NULL,\n" + " D BIGINT UNSIGNED NOT NULL,\n" + " E BIGINT UNSIGNED NOT NULL DEFAULT 0,\n" + " F BIGINT UNSIGNED NOT NULL DEFAULT '0',\n" + " G BIGINT UNSIGNED NOT NULL DEFAULT '18446744073709551615'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_BIGINT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(BigDecimal.ZERO); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(new BigDecimal(10)); assertThat(table.columnWithName("C").isOptional()).isEqualTo(true); assertThat(table.columnWithName("C").hasDefaultValue()).isTrue(); assertThat(table.columnWithName("D").isOptional()).isEqualTo(false); assertThat(table.columnWithName("D").hasDefaultValue()).isFalse(); assertThat(table.columnWithName("E").isOptional()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(BigDecimal.ZERO); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(BigDecimal.ZERO); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(new BigDecimal("18446744073709551615")); }
@Test public void parseUnsignedTinyintDefaultValue() { String sql = "CREATE TABLE UNSIGNED_TINYINT_TABLE (" + " A TINYINT UNSIGNED NULL DEFAULT 0," + " B TINYINT UNSIGNED NULL DEFAULT '10'," + " C TINYINT UNSIGNED NULL," + " D TINYINT UNSIGNED NOT NULL," + " E TINYINT UNSIGNED NOT NULL DEFAULT 0," + " F TINYINT UNSIGNED NOT NULL DEFAULT '0'," + " G TINYINT UNSIGNED NOT NULL DEFAULT '255'" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_TINYINT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo((short) 0); assertThat(table.columnWithName("B").defaultValue()).isEqualTo((short) 10); assertThat(table.columnWithName("C").isOptional()).isEqualTo(true); assertThat(table.columnWithName("C").hasDefaultValue()).isTrue(); assertThat(table.columnWithName("C").defaultValue()).isNull(); assertThat(table.columnWithName("D").isOptional()).isEqualTo(false); assertThat(table.columnWithName("D").hasDefaultValue()).isFalse(); assertThat(table.columnWithName("E").isOptional()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo((short) 0); assertThat(table.columnWithName("F").defaultValue()).isEqualTo((short) 0); assertThat(table.columnWithName("G").defaultValue()).isEqualTo((short) 255); }
@Test @FixFor("DBZ-870") public void shouldAcceptZeroAsDefaultValueForDateColumn() { String ddl = "CREATE TABLE data(id INT, nullable_date date default 0, not_nullable_date date not null default 0, PRIMARY KEY (id))"; parser.parse(ddl, tables); Table table = tables.forTable(new TableId(null, null, "data")); assertThat(table.columnWithName("nullable_date").hasDefaultValue()).isTrue(); // zero date should be mapped to null for nullable column assertThat(table.columnWithName("nullable_date").defaultValue()).isNull(); assertThat(table.columnWithName("not_nullable_date").hasDefaultValue()).isTrue(); // zero date should be mapped to epoch for non-nullable column (expecting Date, as this test is using "connect" // mode) assertThat(table.columnWithName("not_nullable_date").defaultValue()).isEqualTo(getEpochDate()); }
@Test public void parseUnsignedIntDefaultValue() { String sql = "CREATE TABLE UNSIGNED_INT_TABLE (\n" + " A INT UNSIGNED NULL DEFAULT 0,\n" + " B INT UNSIGNED NULL DEFAULT '10',\n" + " C INT UNSIGNED NULL,\n" + " D INT UNSIGNED NOT NULL,\n" + " E INT UNSIGNED NOT NULL DEFAULT 0,\n" + " F INT UNSIGNED NOT NULL DEFAULT '0',\n" + " G INT UNSIGNED NOT NULL DEFAULT '4294967295'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_INT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(0L); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(10L); assertThat(table.columnWithName("C").isOptional()).isEqualTo(true); assertThat(table.columnWithName("C").hasDefaultValue()).isTrue(); assertThat(table.columnWithName("D").isOptional()).isEqualTo(false); assertThat(table.columnWithName("D").hasDefaultValue()).isFalse(); assertThat(table.columnWithName("E").isOptional()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(0L); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(0L); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(4294967295L); }
@Test public void parseUnsignedSmallintDefaultValue() { String sql = "CREATE TABLE UNSIGNED_SMALLINT_TABLE (\n" + " A SMALLINT UNSIGNED NULL DEFAULT 0,\n" + " B SMALLINT UNSIGNED NULL DEFAULT '10',\n" + " C SMALLINT UNSIGNED NULL,\n" + " D SMALLINT UNSIGNED NOT NULL,\n" + " E SMALLINT UNSIGNED NOT NULL DEFAULT 0,\n" + " F SMALLINT UNSIGNED NOT NULL DEFAULT '0',\n" + " G SMALLINT UNSIGNED NOT NULL DEFAULT '65535'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_SMALLINT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(10); assertThat(table.columnWithName("C").isOptional()).isEqualTo(true); assertThat(table.columnWithName("C").hasDefaultValue()).isTrue(); assertThat(table.columnWithName("D").isOptional()).isEqualTo(false); assertThat(table.columnWithName("D").hasDefaultValue()).isFalse(); assertThat(table.columnWithName("E").isOptional()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(65535); }
@Test public void parseUnsignedMediumintDefaultValue() { String sql = "CREATE TABLE UNSIGNED_MEDIUMINT_TABLE (\n" + " A MEDIUMINT UNSIGNED NULL DEFAULT 0,\n" + " B MEDIUMINT UNSIGNED NULL DEFAULT '10',\n" + " C MEDIUMINT UNSIGNED NULL,\n" + " D MEDIUMINT UNSIGNED NOT NULL,\n" + " E MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,\n" + " F MEDIUMINT UNSIGNED NOT NULL DEFAULT '0',\n" + " G MEDIUMINT UNSIGNED NOT NULL DEFAULT '16777215'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_MEDIUMINT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(10); assertThat(table.columnWithName("C").isOptional()).isEqualTo(true); assertThat(table.columnWithName("C").hasDefaultValue()).isTrue(); assertThat(table.columnWithName("D").isOptional()).isEqualTo(false); assertThat(table.columnWithName("D").hasDefaultValue()).isFalse(); assertThat(table.columnWithName("E").isOptional()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(0); assertThat(table.columnWithName("G").defaultValue()).isEqualTo(16777215); }
@Test public void parseUnsignedBigIntDefaultValueToLong() { String sql = "CREATE TABLE UNSIGNED_BIGINT_TABLE (\n" + " A BIGINT UNSIGNED NULL DEFAULT 0,\n" + " B BIGINT UNSIGNED NULL DEFAULT '10',\n" + " C BIGINT UNSIGNED NULL,\n" + " D BIGINT UNSIGNED NOT NULL,\n" + " E BIGINT UNSIGNED NOT NULL DEFAULT 0,\n" + " F BIGINT UNSIGNED NOT NULL DEFAULT '0'\n" + ");"; parser.parse(sql, tables); Table table = tables.forTable(new TableId(null, null, "UNSIGNED_BIGINT_TABLE")); assertThat(table.columnWithName("A").defaultValue()).isEqualTo(0L); assertThat(table.columnWithName("B").defaultValue()).isEqualTo(10L); assertThat(table.columnWithName("C").isOptional()).isEqualTo(true); assertThat(table.columnWithName("C").hasDefaultValue()).isTrue(); assertThat(table.columnWithName("D").isOptional()).isEqualTo(false); assertThat(table.columnWithName("D").hasDefaultValue()).isFalse(); assertThat(table.columnWithName("E").isOptional()).isEqualTo(false); assertThat(table.columnWithName("E").defaultValue()).isEqualTo(0L); assertThat(table.columnWithName("F").defaultValue()).isEqualTo(0L); }
@Test @FixFor("DBZ-860") public void shouldTreatPrimaryKeyColumnsImplicitlyAsNonNull() { String ddl = "CREATE TABLE data(id INT, PRIMARY KEY (id))" + "CREATE TABLE datadef(id INT DEFAULT 0, PRIMARY KEY (id))"; parser.parse(ddl, tables); Table table = tables.forTable(new TableId(null, null, "data")); assertThat(table.columnWithName("id").isOptional()).isEqualTo(false); assertThat(table.columnWithName("id").hasDefaultValue()).isEqualTo(false); Table tableDef = tables.forTable(new TableId(null, null, "datadef")); assertThat(tableDef.columnWithName("id").isOptional()).isEqualTo(false); assertThat(tableDef.columnWithName("id").hasDefaultValue()).isEqualTo(true); assertThat(tableDef.columnWithName("id").defaultValue()).isEqualTo(0); ddl = "CREATE TABLE data(id INT DEFAULT 1, PRIMARY KEY (id))"; parser.parse(ddl, tables); table = tables.forTable(new TableId(null, null, "data")); assertThat(table.columnWithName("id").isOptional()).isEqualTo(false); assertThat(table.columnWithName("id").hasDefaultValue()).isEqualTo(true); assertThat(table.columnWithName("id").defaultValue()).isEqualTo(1); }