@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);
}
@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);
}
