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

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