From 0fe16d84ca548951ac499a66af25b8a0b37c7bd0 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Tue, 13 Sep 2016 18:37:24 +0200 Subject: [PATCH] SONAR-8025 add support for autoincrement column to CreateTableBuilder --- .../sonar/db/version/CreateTableBuilder.java | 125 ++++++++++++++- .../CreateTableBuilderDbTesterTest.java | 75 +++++++-- .../db/version/CreateTableBuilderTest.java | 149 +++++++++++++++++- 3 files changed, 324 insertions(+), 25 deletions(-) diff --git a/sonar-db/src/main/java/org/sonar/db/version/CreateTableBuilder.java b/sonar-db/src/main/java/org/sonar/db/version/CreateTableBuilder.java index b5677eb782f..2a23ccb2653 100644 --- a/sonar-db/src/main/java/org/sonar/db/version/CreateTableBuilder.java +++ b/sonar-db/src/main/java/org/sonar/db/version/CreateTableBuilder.java @@ -19,24 +19,35 @@ */ package org.sonar.db.version; +import com.google.common.collect.HashMultimap; +import com.google.common.collect.Multimap; import java.util.ArrayList; -import java.util.Collections; +import java.util.Arrays; +import java.util.Collection; import java.util.Iterator; import java.util.List; import java.util.Locale; +import java.util.stream.Stream; import javax.annotation.CheckForNull; import org.sonar.core.util.stream.Collectors; import org.sonar.db.dialect.Dialect; +import org.sonar.db.dialect.H2; +import org.sonar.db.dialect.MsSql; +import org.sonar.db.dialect.MySql; import org.sonar.db.dialect.Oracle; +import org.sonar.db.dialect.PostgreSql; +import static com.google.common.base.Preconditions.checkArgument; import static com.google.common.base.Preconditions.checkState; import static java.util.Objects.requireNonNull; +import static java.util.stream.Stream.of; public class CreateTableBuilder { private final Dialect dialect; private final String tableName; private final List columnDefs = new ArrayList<>(); private final List pkColumnDefs = new ArrayList<>(2); + private final Multimap flagsByColumn = HashMultimap.create(1, 1); @CheckForNull private String pkConstraintName; @@ -48,7 +59,32 @@ public class CreateTableBuilder { public List build() { checkState(!columnDefs.isEmpty() || !pkColumnDefs.isEmpty(), "at least one column must be specified"); - return Collections.singletonList(createTableStatement()); + return Stream.concat(of(createTableStatement()), createOracleAutoIncrementStatements()) + .collect(Collectors.toList()); + } + + private Stream createOracleAutoIncrementStatements() { + if (!Oracle.ID.equals(dialect.getId())) { + return Stream.empty(); + } + return pkColumnDefs.stream() + .filter(this::isAutoIncrement) + .flatMap(columnDef -> of(createSequenceFor(tableName), createTriggerFor(tableName))); + } + + private String createSequenceFor(String tableName) { + return "CREATE SEQUENCE " + tableName + "_seq START WITH 1 INCREMENT BY 1"; + } + + private static String createTriggerFor(String tableName) { + return "CREATE OR REPLACE TRIGGER " + tableName + "_idt" + + " BEFORE INSERT ON " + tableName + + " FOR EACH ROW" + + " BEGIN" + + " IF :new.id IS null THEN" + + " SELECT " + tableName + "_seq.nextval INTO :new.id FROM dual;" + + " END IF;" + + " END;"; } public CreateTableBuilder addColumn(ColumnDef columnDef) { @@ -56,11 +92,35 @@ public class CreateTableBuilder { return this; } - public CreateTableBuilder addPkColumn(ColumnDef columnDef) { + public CreateTableBuilder addPkColumn(ColumnDef columnDef, ColumnFlag... flags) { pkColumnDefs.add(requireNonNull(columnDef, "column def can't be null")); + addFlags(columnDef, flags); return this; } + private void addFlags(ColumnDef columnDef, ColumnFlag[] flags) { + Arrays.stream(flags) + .forEach(flag -> { + requireNonNull(flag, "flag can't be null"); + if (flag == ColumnFlag.AUTO_INCREMENT) { + validateColumnDefForAutoIncrement(columnDef); + } + flagsByColumn.put(columnDef, flag); + }); + } + + private void validateColumnDefForAutoIncrement(ColumnDef columnDef) { + checkArgument("id".equals(columnDef.getName()), + "Auto increment column name must be id"); + checkArgument(columnDef instanceof BigIntegerColumnDef + || columnDef instanceof IntegerColumnDef, + "Auto increment column must either be BigInteger or Integer"); + checkArgument(!columnDef.isNullable(), + "Auto increment column can't be nullable"); + checkState(pkColumnDefs.stream().filter(this::isAutoIncrement).count() == 0, + "There can't be more than one auto increment column"); + } + public CreateTableBuilder withPkConstraintName(String pkConstraintName) { this.pkConstraintName = requireNonNull(pkConstraintName, "primary key constraint name can't be null"); return this; @@ -85,22 +145,44 @@ public class CreateTableBuilder { } } - private static void addColumns(StringBuilder res, Dialect dialect, List columnDefs) { + private void addColumns(StringBuilder res, Dialect dialect, List columnDefs) { if (columnDefs.isEmpty()) { return; } Iterator columnDefIterator = columnDefs.iterator(); while (columnDefIterator.hasNext()) { ColumnDef columnDef = columnDefIterator.next(); - res.append(columnDef.getName()).append(' ').append(columnDef.generateSqlType(dialect)); - addNullConstraint(res, columnDef); + res.append(columnDef.getName()); + res.append(' '); + appendDataType(res, dialect, columnDef); + appendNullConstraint(res, columnDef); + appendColumnFlags(res, dialect, columnDef); if (columnDefIterator.hasNext()) { res.append(','); } } } - private static void addNullConstraint(StringBuilder res, ColumnDef columnDef) { + private void appendDataType(StringBuilder res, Dialect dialect, ColumnDef columnDef) { + if (PostgreSql.ID.equals(dialect.getId()) && isAutoIncrement(columnDef)) { + if (columnDef instanceof BigIntegerColumnDef) { + res.append("BIGSERIAL"); + } else if (columnDef instanceof IntegerColumnDef) { + res.append("SERIAL"); + } else { + throw new IllegalStateException("Column with autoincrement is neither BigInteger nor Integer"); + } + } else { + res.append(columnDef.generateSqlType(dialect)); + } + } + + private boolean isAutoIncrement(ColumnDef columnDef) { + Collection columnFlags = this.flagsByColumn.get(columnDef); + return columnFlags != null && columnFlags.contains(ColumnFlag.AUTO_INCREMENT); + } + + private static void appendNullConstraint(StringBuilder res, ColumnDef columnDef) { if (columnDef.isNullable()) { res.append(" NULL"); } else { @@ -108,6 +190,31 @@ public class CreateTableBuilder { } } + private void appendColumnFlags(StringBuilder res, Dialect dialect, ColumnDef columnDef) { + Collection columnFlags = this.flagsByColumn.get(columnDef); + if (columnFlags != null && columnFlags.contains(ColumnFlag.AUTO_INCREMENT)) { + switch (dialect.getId()) { + case Oracle.ID: + // no auto increment on Oracle, must use a sequence + break; + case PostgreSql.ID: + // no specific clause on PostgreSQL but a specific type + break; + case MsSql.ID: + res.append(" IDENTITY (0,1)"); + break; + case MySql.ID: + res.append(" AUTO_INCREMENT"); + break; + case H2.ID: + res.append(" AUTO_INCREMENT (0,1)"); + break; + default: + throw new IllegalArgumentException("Unsupported dialect id " + dialect.getId()); + } + } + } + private void addPkConstraint(StringBuilder res) { if (pkColumnDefs.isEmpty()) { return; @@ -155,4 +262,8 @@ public class CreateTableBuilder { } } + public enum ColumnFlag { + AUTO_INCREMENT + } + } diff --git a/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderDbTesterTest.java b/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderDbTesterTest.java index dc55da2bc03..797f26afecc 100644 --- a/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderDbTesterTest.java +++ b/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderDbTesterTest.java @@ -19,29 +19,39 @@ */ package org.sonar.db.version; +import java.util.Map; import org.junit.ClassRule; import org.junit.Test; import org.sonar.api.utils.System2; import org.sonar.db.DbTester; +import org.sonar.db.dialect.Dialect; +import static org.assertj.core.api.AssertionsForInterfaceTypes.assertThat; import static org.sonar.db.version.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; import static org.sonar.db.version.BlobColumnDef.newBlobColumnDefBuilder; import static org.sonar.db.version.BooleanColumnDef.newBooleanColumnDefBuilder; import static org.sonar.db.version.ClobColumnDef.newClobColumnDefBuilder; +import static org.sonar.db.version.CreateTableBuilder.ColumnFlag.AUTO_INCREMENT; import static org.sonar.db.version.DecimalColumnDef.newDecimalColumnDefBuilder; +import static org.sonar.db.version.IntegerColumnDef.newIntegerColumnDefBuilder; import static org.sonar.db.version.VarcharColumnDef.newVarcharColumnDefBuilder; public class CreateTableBuilderDbTesterTest { @ClassRule public static final DbTester dbTester = DbTester.create(System2.INSTANCE); + private Dialect dialect = dbTester.getDbClient().getDatabase().getDialect(); + private static int tableNameGenerator = 0; + @Test public void create_no_primary_key_table() { - String createTableStmt = new CreateTableBuilder(dbTester.getDbClient().getDatabase().getDialect(), "TABLE_1") + newCreateTableBuilder() .addColumn(newBooleanColumnDefBuilder().setColumnName("bool_col_1").build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("bool_col_2").setIsNullable(false).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_1").build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_2").setIsNullable(false).build()) + .addColumn(newIntegerColumnDefBuilder().setColumnName("i_col_1").build()) + .addColumn(newIntegerColumnDefBuilder().setColumnName("i_col_2").setIsNullable(false).build()) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("bi_col_1").build()) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("bi_col_2").setIsNullable(false).build()) .addColumn(newClobColumnDefBuilder().setColumnName("clob_col_1").build()) .addColumn(newClobColumnDefBuilder().setColumnName("clob_col_2").setIsNullable(false).build()) .addColumn(newDecimalColumnDefBuilder().setColumnName("dec_col_1").build()) @@ -53,31 +63,70 @@ public class CreateTableBuilderDbTesterTest { .addColumn(newBlobColumnDefBuilder().setColumnName("blob_col_1").build()) .addColumn(newBlobColumnDefBuilder().setColumnName("blob_col_2").setIsNullable(false).build()) .build() - .iterator().next(); - - dbTester.executeDdl(createTableStmt); + .forEach(dbTester::executeDdl); } @Test public void create_single_column_primary_key_table() { - String createTableStmt = new CreateTableBuilder(dbTester.getDbClient().getDatabase().getDialect(), "TABLE_2") + newCreateTableBuilder() .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_1").setIsNullable(false).build()) .addColumn(newVarcharColumnDefBuilder().setColumnName("varchar_col_2").setLimit(40).setIsNullable(false).build()) .build() - .iterator().next(); - - dbTester.executeDdl(createTableStmt); + .forEach(dbTester::executeDdl); } @Test public void create_multi_column_primary_key_table() { - String createTableStmt = new CreateTableBuilder(dbTester.getDbClient().getDatabase().getDialect(), "TABLE_3") + newCreateTableBuilder() .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_1").setIsNullable(false).build()) .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_2").setIsNullable(false).build()) .addColumn(newVarcharColumnDefBuilder().setColumnName("varchar_col_2").setLimit(40).setIsNullable(false).build()) .build() - .iterator().next(); + .forEach(dbTester::executeDdl); + } + + @Test + public void create_autoincrement_notnullable_integer_primary_key_table() { + String tableName = createTableName(); + new CreateTableBuilder(dialect, tableName) + .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .addColumn(valColumnDef()) + .build() + .forEach(dbTester::executeDdl); + + verifyAutoIncrementIsWorking(tableName); + } + + @Test + public void create_autoincrement_notnullable_biginteger_primary_key_table() { + String tableName = createTableName(); + new CreateTableBuilder(dialect, tableName) + .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .addColumn(valColumnDef()) + .build() + .forEach(dbTester::executeDdl); + + verifyAutoIncrementIsWorking(tableName); + } + + private static VarcharColumnDef valColumnDef() { + return newVarcharColumnDefBuilder().setColumnName("val").setLimit(10).setIsNullable(false).build(); + } + + private void verifyAutoIncrementIsWorking(String tableName) { + dbTester.executeInsert(tableName, "val", "toto"); + dbTester.commit(); + + Map row = dbTester.selectFirst("select id as \"id\", val as \"val\" from " + tableName); + assertThat(row.get("id")).isNotNull(); + assertThat(row.get("val")).isEqualTo("toto"); + } + + private CreateTableBuilder newCreateTableBuilder() { + return new CreateTableBuilder(dialect, createTableName()); + } - dbTester.executeDdl(createTableStmt); + private static String createTableName() { + return "table_" + tableNameGenerator++; } } diff --git a/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderTest.java b/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderTest.java index 44a19c84cbc..5fd45810c0d 100644 --- a/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderTest.java +++ b/sonar-db/src/test/java/org/sonar/db/version/CreateTableBuilderTest.java @@ -32,17 +32,24 @@ import org.sonar.db.dialect.Oracle; import org.sonar.db.dialect.PostgreSql; import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.fail; import static org.mockito.Mockito.mock; import static org.sonar.db.version.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; import static org.sonar.db.version.BlobColumnDef.newBlobColumnDefBuilder; import static org.sonar.db.version.BooleanColumnDef.newBooleanColumnDefBuilder; import static org.sonar.db.version.ClobColumnDef.newClobColumnDefBuilder; +import static org.sonar.db.version.CreateTableBuilder.ColumnFlag.AUTO_INCREMENT; +import static org.sonar.db.version.DecimalColumnDef.newDecimalColumnDefBuilder; +import static org.sonar.db.version.IntegerColumnDef.newIntegerColumnDefBuilder; import static org.sonar.db.version.VarcharColumnDef.newVarcharColumnDefBuilder; public class CreateTableBuilderTest { - private static final H2 H2_DIALECT = new H2(); + private static final H2 H2 = new H2(); private static final Oracle ORACLE = new Oracle(); - private static final Dialect[] ALL_DIALECTS = {H2_DIALECT, new MySql(), new MsSql(), new PostgreSql(), ORACLE}; + private static final PostgreSql POSTGRESQL = new PostgreSql(); + private static final MsSql MS_SQL = new MsSql(); + private static final MySql MY_SQL = new MySql(); + private static final Dialect[] ALL_DIALECTS = {H2, MY_SQL, MS_SQL, POSTGRESQL, ORACLE}; private static final String TABLE_NAME = "table_42"; @Rule @@ -90,6 +97,138 @@ public class CreateTableBuilderTest { underTest.addPkColumn(null); } + @Test + public void addPkColumn_throws_IAE_when_AUTO_INCREMENT_flag_is_provided_with_column_name_other_than_id() { + expectedException.expect(IllegalArgumentException.class); + expectedException.expectMessage("Auto increment column name must be id"); + + underTest.addPkColumn(newIntegerColumnDefBuilder().setColumnName("toto").build(), AUTO_INCREMENT); + } + + @Test + public void addPkColumn_throws_ISE_when_adding_multiple_autoincrement_columns() { + underTest.addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT); + + expectedException.expect(IllegalStateException.class); + expectedException.expectMessage("There can't be more than one auto increment column"); + + underTest.addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT); + } + + @Test + public void addPkColumn_throws_IAE_when_AUTO_INCREMENT_flag_is_provided_with_def_other_than_Integer_and_BigInteger() { + ColumnDef[] columnDefs = { + newBooleanColumnDefBuilder().setColumnName("id").build(), + newClobColumnDefBuilder().setColumnName("id").build(), + newDecimalColumnDefBuilder().setColumnName("id").build(), + new TinyIntColumnDef.Builder().setColumnName("id").build(), + newVarcharColumnDefBuilder().setColumnName("id").setLimit(40).build(), + newBlobColumnDefBuilder().setColumnName("id").build() + }; + Arrays.stream(columnDefs) + .forEach(columnDef -> { + try { + underTest.addPkColumn(columnDef, AUTO_INCREMENT); + fail("A IllegalArgumentException should have been raised"); + } catch (IllegalArgumentException e) { + assertThat(e).hasMessage("Auto increment column must either be BigInteger or Integer"); + } + }); + } + + @Test + public void addPkColumn_throws_IAE_when_AUTO_INCREMENT_flag_is_provided_and_column_is_nullable() { + ColumnDef[] columnDefs = { + newIntegerColumnDefBuilder().setColumnName("id").build(), + newBigIntegerColumnDefBuilder().setColumnName("id").build() + }; + Arrays.stream(columnDefs) + .forEach(columnDef -> { + try { + underTest.addPkColumn(columnDef, AUTO_INCREMENT); + fail("A IllegalArgumentException should have been raised"); + } catch (IllegalArgumentException e) { + assertThat(e).hasMessage("Auto increment column can't be nullable"); + } + }); + } + + @Test + public void build_sets_type_SERIAL_for_autoincrement_integer_pk_column_on_Postgresql() { + List stmts = new CreateTableBuilder(POSTGRESQL, TABLE_NAME) + .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .build(); + assertThat(stmts).hasSize(1); + assertThat(stmts.iterator().next()) + .isEqualTo( + "CREATE TABLE table_42 (id SERIAL NOT NULL, CONSTRAINT pk_table_42 PRIMARY KEY (id))"); + } + + @Test + public void build_sets_type_BIGSERIAL_for_autoincrement_biginteger_pk_column_on_Postgresql() { + List stmts = new CreateTableBuilder(POSTGRESQL, TABLE_NAME) + .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .build(); + assertThat(stmts).hasSize(1); + assertThat(stmts.iterator().next()) + .isEqualTo( + "CREATE TABLE table_42 (id BIGSERIAL NOT NULL, CONSTRAINT pk_table_42 PRIMARY KEY (id))"); + } + + @Test + public void build_generates_a_create_trigger_statement_when_an_autoincrement_pk_column_is_specified_and_on_Oracle() { + List stmts = new CreateTableBuilder(ORACLE, TABLE_NAME) + .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .build(); + assertThat(stmts).hasSize(3); + assertThat(stmts.get(0)) + .isEqualTo("CREATE TABLE table_42 (id INTEGER NOT NULL, CONSTRAINT pk_table_42 PRIMARY KEY (id))"); + assertThat(stmts.get(1)) + .isEqualTo("CREATE SEQUENCE table_42_seq START WITH 1 INCREMENT BY 1"); + assertThat(stmts.get(2)) + .isEqualTo("CREATE OR REPLACE TRIGGER table_42_idt" + + " BEFORE INSERT ON table_42" + + " FOR EACH ROW" + + " BEGIN" + + " IF :new.id IS null THEN" + + " SELECT table_42_seq.nextval INTO :new.id FROM dual;" + + " END IF;" + + " END;"); + } + + @Test + public void build_adds_IDENTITY_clause_on_MsSql() { + List stmts = new CreateTableBuilder(MS_SQL, TABLE_NAME) + .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .build(); + assertThat(stmts).hasSize(1); + assertThat(stmts.iterator().next()) + .isEqualTo( + "CREATE TABLE table_42 (id INT NOT NULL IDENTITY (0,1), CONSTRAINT pk_table_42 PRIMARY KEY (id))"); + } + + @Test + public void build_adds_AUTO_INCREMENT_clause_on_H2() { + List stmts = new CreateTableBuilder(H2, TABLE_NAME) + .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .build(); + assertThat(stmts).hasSize(1); + assertThat(stmts.iterator().next()) + .isEqualTo( + "CREATE TABLE table_42 (id INTEGER NOT NULL AUTO_INCREMENT (0,1), CONSTRAINT pk_table_42 PRIMARY KEY (id))"); + } + + @Test + public void build_adds_AUTO_INCREMENT_clause_on_MySql() { + List stmts = new CreateTableBuilder(MY_SQL, TABLE_NAME) + .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .build(); + assertThat(stmts).hasSize(1); + assertThat(stmts.iterator().next()) + .isEqualTo( + "CREATE TABLE table_42 (id INTEGER NOT NULL AUTO_INCREMENT, CONSTRAINT pk_table_42 PRIMARY KEY (id))"); + } + @Test public void withPkConstraintName_throws_NPE_if_ColumnDef_is_null() { expectedException.expect(NullPointerException.class); @@ -100,7 +239,7 @@ public class CreateTableBuilderTest { @Test public void build_lowers_case_of_table_name() { - List stmts = new CreateTableBuilder(H2_DIALECT, "SOmE_TABLe_NamE") + List stmts = new CreateTableBuilder(H2, "SOmE_TABLe_NamE") .addColumn(newBooleanColumnDefBuilder().setColumnName("bool_col").build()) .build(); assertThat(stmts).hasSize(1); @@ -146,7 +285,7 @@ public class CreateTableBuilderTest { @Test public void build_of_single_column_table() { - List stmts = new CreateTableBuilder(H2_DIALECT, TABLE_NAME) + List stmts = new CreateTableBuilder(H2, TABLE_NAME) .addColumn(newBooleanColumnDefBuilder().setColumnName("bool_col_1").build()) .build(); assertThat(stmts).hasSize(1); @@ -156,7 +295,7 @@ public class CreateTableBuilderTest { @Test public void build_table_with_pk() { - List stmts = new CreateTableBuilder(H2_DIALECT, TABLE_NAME) + List stmts = new CreateTableBuilder(H2, TABLE_NAME) .addPkColumn(newBooleanColumnDefBuilder().setColumnName("bool_col").build()) .addColumn(newVarcharColumnDefBuilder().setColumnName("varchar_col").setLimit(40).build()) .build(); -- 2.39.5