@Test
  public void createTableWithForeignKey() {
    Table toCreate = new Table("A_SCHEMA", "A_TABLE");
    Table toReference = new Table("A_SCHEMA", "ANOTHER_TABLE");

    CreateTable createTable = new CreateTable(toCreate);

    List<TableElement> columns = new ArrayList<TableElement>();
    columns.add(new ColumnDefinition("id", new NumericDataType()));
    columns.add(new ColumnDefinition("fk", new NumericDataType()));
    columns.add(
        new ReferentialConstraintDefinition(
            "FK",
            Arrays.asList(new String[] {"fk"}),
            toReference,
            Arrays.asList(new String[] {"id"})));

    createTable.setTableElements(columns);

    String sql = sqlGenerator.generateSQL(createTable);

    Assert.assertNotNull(sql);
    Assert.assertEquals(
        "CREATE TABLE A_SCHEMA.A_TABLE ( id NUMERIC,\nfk NUMERIC,\nCONSTRAINT FK FOREIGN KEY (fk) REFERENCES A_SCHEMA.ANOTHER_TABLE(id) )",
        sql);
  }
  @Test
  public void testCreateTableWithoutColumns() {
    CreateTable ct = new CreateTable(new Table("A_SCHEMA", "SIMPLE_TABLE"));
    ct.setTableElements(new ArrayList<TableElement>());

    String sql = sqlGenerator.generateSQL(ct);

    Assert.assertNotNull(sql);
    Assert.assertEquals("CREATE TABLE A_SCHEMA.SIMPLE_TABLE", sql);
  }
  @Test
  public void testCreateTableWithSchemaAndSingleColumn() {
    CreateTable ct = new CreateTable(new Table("A_SCHEMA", "SIMPLE_TABLE"));
    List<TableElement> elements = new ArrayList<TableElement>();
    ColumnDefinition cd = new ColumnDefinition("field1", new CharacterDataType(25), true);
    elements.add(cd);
    ct.setTableElements(elements);

    String sql = sqlGenerator.generateSQL(ct);
    Assert.assertNotNull(sql);
    Assert.assertEquals("CREATE TABLE A_SCHEMA.SIMPLE_TABLE ( field1 VARCHAR(25) NOT NULL )", sql);
  }
  @Test
  public void createTableWithUniqueColumn() {
    CreateTable createTable = new CreateTable(new Table("A_SCHEMA", "A_TABLE"));

    List<TableElement> columns = new ArrayList<TableElement>();
    columns.add(new ColumnDefinition("field1", new DateDataType()));
    columns.add(
        new UniqueConstraintDefinition(
            UniqueConstraintType.unique, Arrays.asList(new String[] {"field1"})));
    createTable.setTableElements(columns);

    String sql = sqlGenerator.generateSQL(createTable);

    Assert.assertNotNull(createTable);
    Assert.assertEquals("CREATE TABLE A_SCHEMA.A_TABLE ( field1 DATE,\nUNIQUE (field1) )", sql);
  }
  @Test
  public void createTableOneTypeEachColumn() {
    CreateTable createTable = new CreateTable(new Table("A_SCHEMA", "A_TABLE"));

    List<TableElement> columns = new ArrayList<TableElement>();
    columns.add(new ColumnDefinition("field1", new CharacterDataType(250)));
    columns.add(new ColumnDefinition("field2", new DateDataType()));
    columns.add(new ColumnDefinition("field3", new NumericDataType()));

    createTable.setTableElements(columns);

    String sql = sqlGenerator.generateSQL(createTable);

    Assert.assertNotNull(sql);
    Assert.assertEquals(
        "CREATE TABLE A_SCHEMA.A_TABLE ( field1 VARCHAR(250),\nfield2 DATE,\nfield3 NUMERIC )",
        sql);
  }
  @Test
  public void createTemporaryTableAsSelect() {
    Table table = new Table("A_SCHEMA", "A_TABLE_AS_SELECT");
    CreateTable ct = new CreateTable(table);
    ct.setTemporary(true);

    PlainSelect select = new PlainSelect();
    Table selectTable = new Table("A_SCHEMA", "Table", "Alias");
    select.setSelectItems(Arrays.asList(new ISelectItem[] {new AllTableColumns(selectTable)}));
    select.setTableReference(selectTable);
    ct.setSelect(select);

    String sql = sqlGenerator.generateSQL(ct);

    Assert.assertNotNull(sql);
    Assert.assertEquals(
        "CREATE GLOBAL TEMPORARY TABLE A_SCHEMA.A_TABLE_AS_SELECT ON COMMIT PRESERVE ROWS AS SELECT Alias.* FROM A_SCHEMA.Table AS Alias",
        sql);
  }
Exemple #7
0
  @Test
  public void update() {
    Update update = new Update();
    Table table = new Table("A_SCHEMA", "A_TABLE");
    update.setTable(table);

    update.setSetClauses(
        Arrays.asList(
            new SetClause[] {
              new SetClause(
                  new Column(table, "column"),
                  SetClauseValueType.expression,
                  new StringValue("foo"))
            }));

    String sql = sqlGenerator.generateSQL(update);

    Assert.assertNotNull(sql);
    Assert.assertEquals("UPDATE A_SCHEMA.A_TABLE SET column='foo'", sql);
  }
  @Test
  public void testCreateTemporaryComplexTableWithPrimaryKey() {
    CreateTable createTable = new CreateTable(new Table("A_SCHEMA", "COMPLEX_TABLE"));
    createTable.setTemporary(true);

    List<TableElement> columns = new ArrayList<TableElement>();
    columns.add(new ColumnDefinition("field1", new CharacterDataType(25), true));
    columns.add(new ColumnDefinition("field2", new CharacterDataType(25), true));
    columns.add(new ColumnDefinition("field3", new CharacterDataType(25)));
    columns.add(
        new UniqueConstraintDefinition(
            UniqueConstraintType.primaryKey, Arrays.asList(new String[] {"field1", "field2"})));
    createTable.setTableElements(columns);

    String sql = sqlGenerator.generateSQL(createTable);

    Assert.assertNotNull(sql);
    Assert.assertEquals(
        "CREATE GLOBAL TEMPORARY TABLE A_SCHEMA.COMPLEX_TABLE ( field1 VARCHAR(25) NOT NULL,\nfield2 VARCHAR(25) NOT NULL,\nfield3 VARCHAR(25),\nPRIMARY KEY (field1, field2) ) ON COMMIT PRESERVE ROWS",
        sql);
  }