source.dussan.org Git - sonarqube.git/blob

   1 /*
   2  * SonarQube
   3  * Copyright (C) 2009-2023 SonarSource SA
   4  * mailto:info AT sonarsource DOT com
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU Lesser General Public
   8  * License as published by the Free Software Foundation; either
   9  * version 3 of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14  * Lesser General Public License for more details.
  15  *
  16  * You should have received a copy of the GNU Lesser General Public License
  17  * along with this program; if not, write to the Free Software Foundation,
  18  * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  19  */
  20 package org.sonar.server.platform.db.migration.sql;
  21
  22 import com.tngtech.java.junit.dataprovider.DataProvider;
  23 import com.tngtech.java.junit.dataprovider.DataProviderRunner;
  24 import com.tngtech.java.junit.dataprovider.UseDataProvider;
  25 import java.util.Arrays;
  26 import java.util.List;
  27 import org.junit.Test;
  28 import org.junit.runner.RunWith;
  29 import org.sonar.db.dialect.Dialect;
  30 import org.sonar.db.dialect.H2;
  31 import org.sonar.db.dialect.MsSql;
  32 import org.sonar.db.dialect.Oracle;
  33 import org.sonar.db.dialect.PostgreSql;
  34 import org.sonar.server.platform.db.migration.def.ColumnDef;
  35 import org.sonar.server.platform.db.migration.def.TinyIntColumnDef;
  36
  37 import static org.assertj.core.api.Assertions.assertThat;
  38 import static org.assertj.core.api.Assertions.assertThatThrownBy;
  39 import static org.assertj.core.api.Assertions.fail;
  40 import static org.mockito.Mockito.mock;
  41 import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder;
  42 import static org.sonar.server.platform.db.migration.def.BlobColumnDef.newBlobColumnDefBuilder;
  43 import static org.sonar.server.platform.db.migration.def.BooleanColumnDef.newBooleanColumnDefBuilder;
  44 import static org.sonar.server.platform.db.migration.def.ClobColumnDef.newClobColumnDefBuilder;
  45 import static org.sonar.server.platform.db.migration.def.DecimalColumnDef.newDecimalColumnDefBuilder;
  46 import static org.sonar.server.platform.db.migration.def.IntegerColumnDef.newIntegerColumnDefBuilder;
  47 import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder;
  48 import static org.sonar.server.platform.db.migration.sql.CreateTableBuilder.ColumnFlag.AUTO_INCREMENT;
  49
  50 @RunWith(DataProviderRunner.class)
  51 public class CreateTableBuilderTest {
  52   private static final H2 H2 = new H2();
  53   private static final Oracle ORACLE = new Oracle();
  54   private static final PostgreSql POSTGRESQL = new PostgreSql();
  55   private static final MsSql MS_SQL = new MsSql();
  56   private static final Dialect[] ALL_DIALECTS = {H2, MS_SQL, POSTGRESQL, ORACLE};
  57   private static final String TABLE_NAME = "table_42";
  58   private CreateTableBuilder underTest = new CreateTableBuilder(mock(Dialect.class), TABLE_NAME);
  59
  60   @Test
  61   public void constructor_fails_with_NPE_if_dialect_is_null() {
  62     assertThatThrownBy(() -> new CreateTableBuilder(null, TABLE_NAME))
  63       .isInstanceOf(NullPointerException.class)
  64       .hasMessageContaining("dialect can't be null");
  65   }
  66
  67   @Test
  68   public void constructor_fails_with_NPE_if_tablename_is_null() {
  69     assertThatThrownBy(() -> new CreateTableBuilder(mock(Dialect.class), null))
  70       .isInstanceOf(NullPointerException.class)
  71       .hasMessageContaining("Table name can't be null");
  72   }
  73
  74   @Test
  75   public void constructor_throws_IAE_if_table_name_is_not_lowercase() {
  76     assertThatThrownBy(() -> new CreateTableBuilder(mock(Dialect.class), "Tooo"))
  77       .isInstanceOf(IllegalArgumentException.class)
  78       .hasMessageContaining("Table name must be lower case and contain only alphanumeric chars or '_', got 'Tooo");
  79   }
  80
  81   @Test
  82   public void constructor_throws_IAE_if_table_name_is_26_chars_long() {
  83     assertThatThrownBy(() -> new CreateTableBuilder(mock(Dialect.class), "abcdefghijklmnopqrstuvwxyz"))
  84       .isInstanceOf(IllegalArgumentException.class)
  85       .hasMessageContaining("Table name length can't be more than 25");
  86   }
  87
  88   @Test
  89   public void constructor_does_not_fail_if_table_name_is_25_chars_long() {
  90     new CreateTableBuilder(mock(Dialect.class), "abcdefghijklmnopqrstuvwxy");
  91   }
  92
  93   @Test
  94   public void constructor_does_not_fail_if_table_name_contains_ascii_letters() {
  95     new CreateTableBuilder(mock(Dialect.class), "abcdefghijklmnopqrstuvwxy");
  96     new CreateTableBuilder(mock(Dialect.class), "z");
  97   }
  98
  99   @Test
 100   public void constructor_throws_IAE_if_table_name_starts_with_underscore() {
 101     assertThatThrownBy(() -> new CreateTableBuilder(mock(Dialect.class), "_a"))
 102       .isInstanceOf(IllegalArgumentException.class)
 103       .hasMessageContaining("Table name must not start by a number or '_', got '_a'");
 104   }
 105
 106   @Test
 107   @UseDataProvider("digitCharsDataProvider")
 108   public void constructor_throws_IAE_if_table_name_starts_with_number(char number) {
 109     assertThatThrownBy(() -> new CreateTableBuilder(mock(Dialect.class), number + "a"))
 110       .isInstanceOf(IllegalArgumentException.class)
 111       .hasMessageContaining("Table name must not start by a number or '_', got '" + number + "a'");
 112   }
 113
 114   @DataProvider
 115   public static Object[][] digitCharsDataProvider() {
 116     return new Object[][]{
 117       {'0'},
 118       {'1'},
 119       {'2'},
 120       {'3'},
 121       {'4'},
 122       {'5'},
 123       {'6'},
 124       {'7'},
 125       {'8'},
 126       {'9'},
 127     };
 128   }
 129
 130   @Test
 131   public void constructor_does_not_fail_if_table_name_contains_underscore_or_numbers() {
 132     new CreateTableBuilder(mock(Dialect.class), "a1234567890");
 133     new CreateTableBuilder(mock(Dialect.class), "a_");
 134   }
 135
 136   @Test
 137   public void build_throws_ISE_if_no_column_has_been_set() {
 138     assertThatThrownBy(() -> underTest.build())
 139       .isInstanceOf(IllegalStateException.class)
 140       .hasMessageContaining("at least one column must be specified");
 141   }
 142
 143   @Test
 144   public void addColumn_throws_NPE_if_ColumnDef_is_null() {
 145     assertThatThrownBy(() -> underTest.addColumn(null))
 146       .isInstanceOf(NullPointerException.class)
 147       .hasMessageContaining("column def can't be null");
 148   }
 149
 150   @Test
 151   public void addPkColumn_throws_NPE_if_ColumnDef_is_null() {
 152     assertThatThrownBy(() -> underTest.addPkColumn(null))
 153       .isInstanceOf(NullPointerException.class)
 154       .hasMessageContaining("column def can't be null");
 155   }
 156
 157   @Test
 158   public void addPkColumn_throws_IAE_when_AUTO_INCREMENT_flag_is_provided_with_column_name_other_than_id() {
 159     assertThatThrownBy(() -> underTest.addPkColumn(newIntegerColumnDefBuilder().setColumnName("toto").build(), AUTO_INCREMENT))
 160       .isInstanceOf(IllegalArgumentException.class)
 161       .hasMessageContaining("Auto increment column name must be id");
 162   }
 163
 164   @Test
 165   public void addPkColumn_throws_ISE_when_adding_multiple_autoincrement_columns() {
 166     underTest.addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT);
 167
 168     assertThatThrownBy(() -> underTest.addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT))
 169       .isInstanceOf(IllegalStateException.class)
 170       .hasMessageContaining("There can't be more than one auto increment column");
 171   }
 172
 173   @Test
 174   public void addPkColumn_throws_IAE_when_AUTO_INCREMENT_flag_is_provided_with_def_other_than_Integer_and_BigInteger() {
 175     ColumnDef[] columnDefs = {
 176       newBooleanColumnDefBuilder().setColumnName("id").build(),
 177       newClobColumnDefBuilder().setColumnName("id").build(),
 178       newDecimalColumnDefBuilder().setColumnName("id").build(),
 179       new TinyIntColumnDef.Builder().setColumnName("id").build(),
 180       newVarcharColumnDefBuilder().setColumnName("id").setLimit(40).build(),
 181       newBlobColumnDefBuilder().setColumnName("id").build()
 182     };
 183     Arrays.stream(columnDefs)
 184       .forEach(columnDef -> {
 185         try {
 186           underTest.addPkColumn(columnDef, AUTO_INCREMENT);
 187           fail("A IllegalArgumentException should have been raised");
 188         } catch (IllegalArgumentException e) {
 189           assertThat(e).hasMessage("Auto increment column must either be BigInteger or Integer");
 190         }
 191       });
 192   }
 193
 194   @Test
 195   public void addPkColumn_throws_IAE_when_AUTO_INCREMENT_flag_is_provided_and_column_is_nullable() {
 196     ColumnDef[] columnDefs = {
 197       newIntegerColumnDefBuilder().setColumnName("id").build(),
 198       newBigIntegerColumnDefBuilder().setColumnName("id").build()
 199     };
 200     Arrays.stream(columnDefs)
 201       .forEach(columnDef -> {
 202         try {
 203           underTest.addPkColumn(columnDef, AUTO_INCREMENT);
 204           fail("A IllegalArgumentException should have been raised");
 205         } catch (IllegalArgumentException e) {
 206           assertThat(e).hasMessage("Auto increment column can't be nullable");
 207         }
 208       });
 209   }
 210
 211   @Test
 212   public void build_sets_type_SERIAL_for_autoincrement_integer_pk_column_on_Postgresql() {
 213     List<String> stmts = new CreateTableBuilder(POSTGRESQL, TABLE_NAME)
 214       .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT)
 215       .build();
 216     assertThat(stmts).hasSize(1);
 217     assertThat(stmts.iterator().next())
 218       .isEqualTo(
 219         "CREATE TABLE table_42 (id SERIAL NOT NULL, CONSTRAINT pk_table_42 PRIMARY KEY (id))");
 220   }
 221
 222   @Test
 223   public void build_sets_type_BIGSERIAL_for_autoincrement_biginteger_pk_column_on_Postgresql() {
 224     List<String> stmts = new CreateTableBuilder(POSTGRESQL, TABLE_NAME)
 225       .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT)
 226       .build();
 227     assertThat(stmts).hasSize(1);
 228     assertThat(stmts.iterator().next())
 229       .isEqualTo(
 230         "CREATE TABLE table_42 (id BIGSERIAL NOT NULL, CONSTRAINT pk_table_42 PRIMARY KEY (id))");
 231   }
 232
 233   @Test
 234   public void build_generates_a_create_trigger_statement_when_an_autoincrement_pk_column_is_specified_and_on_Oracle() {
 235     List<String> stmts = new CreateTableBuilder(ORACLE, TABLE_NAME)
 236       .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT)
 237       .build();
 238     assertThat(stmts).hasSize(3);
 239     assertThat(stmts.get(0))
 240       .isEqualTo("CREATE TABLE table_42 (id NUMBER(38,0) NOT NULL, CONSTRAINT pk_table_42 PRIMARY KEY (id))");
 241     assertThat(stmts.get(1))
 242       .isEqualTo("CREATE SEQUENCE table_42_seq START WITH 1 INCREMENT BY 1");
 243     assertThat(stmts.get(2))
 244       .isEqualTo("CREATE OR REPLACE TRIGGER table_42_idt" +
 245         " BEFORE INSERT ON table_42" +
 246         " FOR EACH ROW" +
 247         " BEGIN" +
 248         " IF :new.id IS null THEN" +
 249         " SELECT table_42_seq.nextval INTO :new.id FROM dual;" +
 250         " END IF;" +
 251         " END;");
 252   }
 253
 254   @Test
 255   public void build_adds_IDENTITY_clause_on_MsSql() {
 256     List<String> stmts = new CreateTableBuilder(MS_SQL, TABLE_NAME)
 257       .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT)
 258       .build();
 259     assertThat(stmts).hasSize(1);
 260     assertThat(stmts.iterator().next())
 261       .isEqualTo(
 262         "CREATE TABLE table_42 (id INT NOT NULL IDENTITY (1,1), CONSTRAINT pk_table_42 PRIMARY KEY (id))");
 263   }
 264
 265   @Test
 266   public void build_adds_AUTO_INCREMENT_clause_on_H2() {
 267     List<String> stmts = new CreateTableBuilder(H2, TABLE_NAME)
 268       .addPkColumn(newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT)
 269       .build();
 270     assertThat(stmts).hasSize(1);
 271     assertThat(stmts.iterator().next())
 272       .isEqualTo(
 273         "CREATE TABLE table_42 (id INTEGER NOT NULL AUTO_INCREMENT (1,1), CONSTRAINT pk_table_42 PRIMARY KEY (id))");
 274   }
 275
 276   @Test
 277   public void withPkConstraintName_throws_NPE_if_name_is_null() {
 278     assertThatThrownBy(() -> underTest.withPkConstraintName(null))
 279       .isInstanceOf(NullPointerException.class)
 280       .hasMessageContaining("Constraint name can't be null");
 281   }
 282
 283   @Test
 284   public void withPkConstraintName_throws_IAE_if_name_is_not_lowercase() {
 285     assertThatThrownBy(() -> underTest.withPkConstraintName("Too"))
 286       .isInstanceOf(IllegalArgumentException.class)
 287       .hasMessageContaining("Constraint name must be lower case and contain only alphanumeric chars or '_', got 'Too'");
 288   }
 289
 290   @Test
 291   public void withPkConstraintName_throws_IAE_if_name_is_more_than_30_char_long() {
 292     assertThatThrownBy(() -> underTest.withPkConstraintName("abcdefghijklmnopqrstuvwxyzabcdf"))
 293       .isInstanceOf(IllegalArgumentException.class)
 294       .hasMessageContaining("Constraint name length can't be more than 30");
 295   }
 296
 297   @Test
 298   public void withPkConstraintName_throws_IAE_if_name_starts_with_underscore() {
 299     assertThatThrownBy(() -> underTest.withPkConstraintName("_a"))
 300       .isInstanceOf(IllegalArgumentException.class)
 301       .hasMessageContaining("Constraint name must not start by a number or '_', got '_a'");
 302   }
 303
 304   @Test
 305   @UseDataProvider("digitCharsDataProvider")
 306   public void withPkConstraintName_throws_IAE_if_name_starts_with_number(char number) {
 307     assertThatThrownBy(() -> underTest.withPkConstraintName(number + "a"))
 308       .isInstanceOf(IllegalArgumentException.class)
 309       .hasMessageContaining("Constraint name must not start by a number or '_', got '" + number + "a'");
 310   }
 311
 312   @Test
 313   public void withPkConstraintName_does_not_fail_if_name_is_30_char_long() {
 314     underTest.withPkConstraintName("abcdefghijklmnopqrstuvwxyzabcd");
 315   }
 316
 317   @Test
 318   public void withPkConstraintName_does_not_fail_if_name_contains_ascii_letters() {
 319     underTest.withPkConstraintName("abcdefghijklmnopqrstuvwxyz");
 320   }
 321
 322   @Test
 323   public void withPkConstraintName_does_not_fail_if_name_contains_underscore() {
 324     underTest.withPkConstraintName("a_");
 325   }
 326
 327   @Test
 328   public void withPkConstraintName_does_not_fail_if_name_contains_numbers() {
 329     underTest.withPkConstraintName("a0123456789");
 330   }
 331
 332   @Test
 333   public void build_adds_NULL_when_column_is_nullable_for_all_DBs() {
 334     Arrays.stream(ALL_DIALECTS)
 335       .forEach(dialect -> {
 336         List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 337           .addColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col").build())
 338           .build();
 339         assertThat(stmts).hasSize(1);
 340
 341         assertThat(stmts.iterator().next())
 342           .startsWith("CREATE TABLE " + TABLE_NAME + " (" +
 343             "bg_col " +
 344             bigIntSqlType(dialect) + " NULL" +
 345             ")");
 346       });
 347   }
 348
 349   @Test
 350   public void build_adds_NOT_NULL_when_column_is_not_nullable_for_all_DBs() {
 351     Arrays.stream(ALL_DIALECTS)
 352       .forEach(dialect -> {
 353         List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 354           .addColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col").setIsNullable(false).build())
 355           .build();
 356         assertThat(stmts).hasSize(1);
 357
 358         assertThat(stmts.iterator().next())
 359           .startsWith("CREATE TABLE " + TABLE_NAME + " (" +
 360             "bg_col " +
 361             bigIntSqlType(dialect) +
 362             " NOT NULL" +
 363             ")");
 364       });
 365   }
 366
 367   @Test
 368   public void build_of_single_column_table() {
 369     List<String> stmts = new CreateTableBuilder(H2, TABLE_NAME)
 370       .addColumn(newBooleanColumnDefBuilder().setColumnName("bool_col_1").build())
 371       .build();
 372     assertThat(stmts).hasSize(1);
 373
 374     assertThat(stmts.iterator().next()).isEqualTo("CREATE TABLE table_42 (bool_col_1 BOOLEAN NULL)");
 375   }
 376
 377   @Test
 378   public void build_table_with_pk() {
 379     List<String> stmts = new CreateTableBuilder(H2, TABLE_NAME)
 380       .addPkColumn(newBooleanColumnDefBuilder().setColumnName("bool_col").build())
 381       .addColumn(newVarcharColumnDefBuilder().setColumnName("varchar_col").setLimit(40).build())
 382       .build();
 383     assertThat(stmts).hasSize(1);
 384
 385     assertThat(stmts.iterator().next())
 386       .isEqualTo("CREATE TABLE " + TABLE_NAME + " (" +
 387         "bool_col BOOLEAN NULL," +
 388         "varchar_col VARCHAR (40) NULL," +
 389         " CONSTRAINT pk_" + TABLE_NAME + " PRIMARY KEY (bool_col)" +
 390         ")");
 391
 392   }
 393
 394   @Test
 395   public void build_adds_PRIMARY_KEY_constraint_on_single_column_with_name_computed_from_tablename() {
 396     Arrays.asList(ALL_DIALECTS)
 397       .forEach(dialect -> {
 398         List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 399           .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col").setIsNullable(false).build())
 400           .build();
 401         assertThat(stmts).hasSize(1);
 402
 403         assertThat(stmts.iterator().next())
 404           .startsWith("CREATE TABLE " + TABLE_NAME + " (" +
 405             "bg_col " + bigIntSqlType(dialect) + " NOT NULL," +
 406             " CONSTRAINT pk_" + TABLE_NAME + " PRIMARY KEY (bg_col)" +
 407             ")");
 408       });
 409   }
 410
 411   @Test
 412   public void build_adds_PRIMARY_KEY_constraint_on_single_column_with_lower_case_of_specified_name() {
 413     Arrays.asList(ALL_DIALECTS)
 414       .forEach(dialect -> {
 415         List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 416           .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col").setIsNullable(false).build())
 417           .withPkConstraintName("my_pk")
 418           .build();
 419         assertThat(stmts).hasSize(1);
 420
 421         assertThat(stmts.iterator().next())
 422           .startsWith("CREATE TABLE " + TABLE_NAME + " (" +
 423             "bg_col " +
 424             bigIntSqlType(dialect) +
 425             " NOT NULL," +
 426             " CONSTRAINT my_pk PRIMARY KEY (bg_col)" +
 427             ")");
 428       });
 429   }
 430
 431   @Test
 432   public void build_adds_PRIMARY_KEY_constraint_on_multiple_columns_with_name_computed_from_tablename() {
 433     Arrays.asList(ALL_DIALECTS)
 434       .forEach(dialect -> {
 435         List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 436           .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_1").setIsNullable(false).build())
 437           .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_2").setIsNullable(false).build())
 438           .build();
 439         assertThat(stmts).hasSize(1);
 440
 441         assertThat(stmts.iterator().next())
 442           .startsWith("CREATE TABLE " + TABLE_NAME + " (" +
 443             "bg_col_1 " + bigIntSqlType(dialect) + " NOT NULL," +
 444             "bg_col_2 " + bigIntSqlType(dialect) + " NOT NULL," +
 445             " CONSTRAINT pk_" + TABLE_NAME + " PRIMARY KEY (bg_col_1,bg_col_2)" +
 446             ")");
 447       });
 448   }
 449
 450   @Test
 451   public void build_adds_PRIMARY_KEY_constraint_on_multiple_columns_with_lower_case_of_specified_name() {
 452     Arrays.asList(ALL_DIALECTS)
 453       .forEach(dialect -> {
 454         List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 455           .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_1").setIsNullable(false).build())
 456           .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("bg_col_2").setIsNullable(false).build())
 457           .withPkConstraintName("my_pk")
 458           .build();
 459         assertThat(stmts).hasSize(1);
 460
 461         assertThat(stmts.iterator().next())
 462           .startsWith("CREATE TABLE " + TABLE_NAME + " (" +
 463             "bg_col_1 " + bigIntSqlType(dialect) + " NOT NULL," +
 464             "bg_col_2 " + bigIntSqlType(dialect) + " NOT NULL," +
 465             " CONSTRAINT my_pk PRIMARY KEY (bg_col_1,bg_col_2)" +
 466             ")");
 467       });
 468   }
 469
 470   @Test
 471   public void build_adds_DEFAULT_clause_on_varchar_column_on_H2() {
 472     verifyDefaultClauseOnVarcharColumn(H2, "CREATE TABLE table_42 (status VARCHAR (1) DEFAULT 'P' NOT NULL)");
 473   }
 474
 475   @Test
 476   public void build_adds_DEFAULT_clause_on_varchar_column_on_MSSQL() {
 477     verifyDefaultClauseOnVarcharColumn(MS_SQL, "CREATE TABLE table_42 (status NVARCHAR (1) DEFAULT 'P' NOT NULL)");
 478   }
 479
 480   @Test
 481   public void build_adds_DEFAULT_clause_on_varchar_column_on_Oracle() {
 482     verifyDefaultClauseOnVarcharColumn(ORACLE, "CREATE TABLE table_42 (status VARCHAR2 (1 CHAR) DEFAULT 'P' NOT NULL)");
 483   }
 484
 485   @Test
 486   public void build_adds_DEFAULT_clause_on_varchar_column_on_PostgreSQL() {
 487     verifyDefaultClauseOnVarcharColumn(POSTGRESQL, "CREATE TABLE table_42 (status VARCHAR (1) DEFAULT 'P' NOT NULL)");
 488   }
 489
 490   private static void verifyDefaultClauseOnVarcharColumn(Dialect dialect, String expectedSql) {
 491     List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 492       .addColumn(newVarcharColumnDefBuilder().setColumnName("status").setLimit(1).setIsNullable(false).setDefaultValue("P").build())
 493       .build();
 494     assertThat(stmts).containsExactly(expectedSql);
 495   }
 496
 497   @Test
 498   public void build_adds_DEFAULT_clause_on_boolean_column_on_H2() {
 499     verifyDefaultClauseOnBooleanColumn(H2, "CREATE TABLE table_42 (enabled BOOLEAN DEFAULT true NOT NULL)");
 500   }
 501
 502   @Test
 503   public void build_adds_DEFAULT_clause_on_boolean_column_on_MSSQL() {
 504     verifyDefaultClauseOnBooleanColumn(MS_SQL, "CREATE TABLE table_42 (enabled BIT DEFAULT 1 NOT NULL)");
 505   }
 506
 507   @Test
 508   public void build_adds_DEFAULT_clause_on_boolean_column_on_Oracle() {
 509     verifyDefaultClauseOnBooleanColumn(ORACLE, "CREATE TABLE table_42 (enabled NUMBER(1) DEFAULT 1 NOT NULL)");
 510   }
 511
 512   @Test
 513   public void build_adds_DEFAULT_clause_on_boolean_column_on_PostgreSQL() {
 514     verifyDefaultClauseOnBooleanColumn(POSTGRESQL, "CREATE TABLE table_42 (enabled BOOLEAN DEFAULT true NOT NULL)");
 515   }
 516
 517   private static void verifyDefaultClauseOnBooleanColumn(Dialect dialect, String expectedSql) {
 518     List<String> stmts = new CreateTableBuilder(dialect, TABLE_NAME)
 519       .addColumn(newBooleanColumnDefBuilder().setColumnName("enabled").setIsNullable(false).setDefaultValue(true).build())
 520       .build();
 521     assertThat(stmts).containsExactly(expectedSql);
 522   }
 523
 524   private static String bigIntSqlType(Dialect dialect) {
 525     return Oracle.ID.equals(dialect.getId()) ? "NUMBER (38)" : "BIGINT";
 526   }
 527
 528 }