public void testSplitJoinQuery() throws Exception {
Connection con = getTestDbConnection();
DataContext dc = new JdbcDataContext(con);
Schema schema = dc.getDefaultSchema();
Table productsTable = schema.getTableByName("PRODUCTS");
Relationship[] relationships = productsTable.getRelationships();
Relationship relationship = relationships[0];
assertEquals(
"Relationship[primaryTable=PRODUCTS,primaryColumns=[PRODUCTCODE],foreignTable=ORDERFACT,foreignColumns=[PRODUCTCODE]]",
relationship.toString());
Query q =
new Query()
.from(new FromItem(JoinType.LEFT, relationship))
.select(relationship.getForeignColumns())
.select(relationship.getPrimaryColumns());
assertEquals(
"SELECT _ORDERFACT_._PRODUCTCODE_, _PRODUCTS_._PRODUCTCODE_ FROM PUBLIC._PRODUCTS_ LEFT JOIN PUBLIC._ORDERFACT_ ON _PRODUCTS_._PRODUCTCODE_ = _ORDERFACT_._PRODUCTCODE_",
q.toString().replace('\"', '_'));
QuerySplitter qs = new QuerySplitter(dc, q);
assertEquals(2997, qs.getRowCount());
List<Query> splitQueries = qs.setMaxRows(1500).splitQuery();
assertSameCount(dc, qs, splitQueries);
assertEquals(3, splitQueries.size());
assertEquals("[1415, 902, 680]", Arrays.toString(getCounts(dc, splitQueries)));
}
@Test
public void testUpdateRow() throws Exception {
final Schema schema = dataContext.getDefaultSchema();
final CreateTable createTable = new CreateTable(schema, "testCreateTable");
createTable.withColumn("foo").ofType(ColumnType.STRING);
createTable.withColumn("bar").ofType(ColumnType.NUMBER);
dataContext.executeUpdate(createTable);
final Table table = schema.getTableByName("testCreateTable");
try {
dataContext.executeUpdate(
new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
callback.insertInto(table).value("foo", "hello").value("bar", 42).execute();
callback.insertInto(table).value("foo", "world").value("bar", 43).execute();
}
});
dataContext.executeUpdate(new Update(table).value("foo", "howdy").where("bar").eq(42));
DataSet dataSet =
dataContext.query().from(table).select("foo", "bar").orderBy("bar").execute();
assertTrue(dataSet.next());
assertEquals("Row[values=[howdy, 42]]", dataSet.getRow().toString());
assertTrue(dataSet.next());
assertEquals("Row[values=[world, 43]]", dataSet.getRow().toString());
assertFalse(dataSet.next());
dataSet.close();
} finally {
dataContext.executeUpdate(new DropTable(table));
}
}
@Test
public void testDeleteUnsupportedQueryType() throws Exception {
final Schema schema = dataContext.getDefaultSchema();
final CreateTable createTable = new CreateTable(schema, "testCreateTable");
createTable.withColumn("foo").ofType(ColumnType.STRING);
createTable.withColumn("bar").ofType(ColumnType.NUMBER);
dataContext.executeUpdate(createTable);
final Table table = schema.getTableByName("testCreateTable");
try {
dataContext.executeUpdate(
new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
callback.insertInto(table).value("foo", "hello").value("bar", 42).execute();
callback.insertInto(table).value("foo", "world").value("bar", 43).execute();
}
});
// greater than is not yet supported
try {
dataContext.executeUpdate(new DeleteFrom(table).where("bar").gt(40));
fail("Exception expected");
} catch (UnsupportedOperationException e) {
assertEquals(
"Could not push down WHERE items to delete by query request: [testCreateTable.bar > 40]",
e.getMessage());
}
} finally {
dataContext.executeUpdate(new DropTable(table));
}
}
@Test
public void testDeleteByQuery() throws Exception {
final Schema schema = dataContext.getDefaultSchema();
final CreateTable createTable = new CreateTable(schema, "testCreateTable");
createTable.withColumn("foo").ofType(ColumnType.STRING);
createTable.withColumn("bar").ofType(ColumnType.NUMBER);
dataContext.executeUpdate(createTable);
final Table table = schema.getTableByName("testCreateTable");
dataContext.executeUpdate(
new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
callback.insertInto(table).value("foo", "hello").value("bar", 42).execute();
callback.insertInto(table).value("foo", "world").value("bar", 43).execute();
}
});
dataContext.executeUpdate(new DeleteFrom(table).where("foo").eq("hello").where("bar").eq(42));
Row row =
MetaModelHelper.executeSingleRowQuery(
dataContext, dataContext.query().from(table).select("foo", "bar").toQuery());
assertEquals("Row[values=[world, 43]]", row.toString());
dataContext.executeUpdate(new DropTable(table));
}
public void testSplitSubQuery() throws Exception {
Connection con = getTestDbConnection();
DataContext dc = new JdbcDataContext(con);
Schema schema = dc.getDefaultSchema();
Table employeesTable = schema.getTableByName("EMPLOYEES");
Table customersTable = schema.getTableByName("CUSTOMERS");
Query sq = new Query().from(employeesTable, "e").from(customersTable, "c");
SelectItem empSelectItem = new SelectItem(employeesTable.getColumns()[0]);
SelectItem custSelectItem = new SelectItem(customersTable.getColumns()[0]);
sq.select(empSelectItem, custSelectItem);
assertEquals(
"SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c",
sq.toString().replace('\"', '_'));
Query q = new Query();
FromItem sqItem = new FromItem(sq).setAlias("sq");
custSelectItem.setAlias("c_num");
empSelectItem.setAlias("e_num");
q.from(sqItem);
q.select(new SelectItem(custSelectItem, sqItem), new SelectItem(empSelectItem, sqItem));
assertEquals(
"SELECT sq.c_num, sq.e_num FROM (SELECT e._EMPLOYEENUMBER_ AS e_num, c._CUSTOMERNUMBER_ AS c_num FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c) sq",
q.toString().replace('\"', '_'));
QuerySplitter qs = new QuerySplitter(dc, q);
assertEquals(2806, qs.getRowCount());
List<Query> splitQueries = qs.setMaxRows(1000).splitQuery();
assertSameCount(dc, qs, splitQueries);
assertEquals(5, splitQueries.size());
assertEquals("[793, 610, 366, 714, 323]", Arrays.toString(getCounts(dc, splitQueries)));
splitQueries = qs.setMaxRows(2000).splitQuery();
assertSameCount(dc, qs, splitQueries);
assertEquals(2, splitQueries.size());
assertEquals("[1403, 1403]", Arrays.toString(getCounts(dc, splitQueries)));
DataSet data = qs.executeQueries();
int count = 0;
while (data.next()) {
if (count == 2) {
assertEquals("Row[values=[114, 1002]]", data.getRow().toString());
}
count++;
}
data.close();
assertEquals(2806, count);
}
public void testProductTable() throws Exception {
Schema schema = dc.getDefaultSchema();
assertEquals("developers.mdb", schema.getName());
Table table = schema.getTableByName("product");
assertEquals("[id, name, version, founder_developer]", Arrays.toString(table.getColumnNames()));
Column idCol = table.getColumnByName("id");
assertEquals(
"Column[name=id,columnNumber=0,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]",
idCol.toString());
Column nameCol = table.getColumnByName("name");
assertEquals(
"Column[name=name,columnNumber=1,type=VARCHAR,nullable=null,nativeType=TEXT,columnSize=100]",
nameCol.toString());
Column versionCol = table.getColumnByName("version");
assertEquals(
"Column[name=version,columnNumber=2,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]",
versionCol.toString());
Column founderCol = table.getColumnByName("founder_developer");
assertEquals(
"Column[name=founder_developer,columnNumber=3,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]",
founderCol.toString());
DataSet ds;
ds = dc.executeQuery(new Query().select(nameCol, versionCol, founderCol).from(table));
assertTrue(ds.next());
assertEquals("Anthons Algorithms", ds.getRow().getValue(nameCol).toString());
assertEquals(11, ds.getRow().getValue(versionCol));
assertEquals(1, ds.getRow().getValue(founderCol));
assertTrue(ds.next());
assertEquals("Barbaras Basic Bundle", ds.getRow().getValue(nameCol).toString());
assertEquals(2, ds.getRow().getValue(versionCol));
assertEquals(2, ds.getRow().getValue(founderCol));
assertFalse(ds.next());
ds.close();
ds = dc.query().from(table).selectCount().execute();
assertTrue(ds.next());
assertEquals("Row[values=[2]]", ds.getRow().toString());
assertFalse(ds.next());
ds.close();
}
@Test
public void testCreateTableInsertQueryAndDrop() throws Exception {
final Schema schema = dataContext.getDefaultSchema();
final CreateTable createTable = new CreateTable(schema, "testCreateTable");
createTable.withColumn("foo").ofType(ColumnType.STRING);
createTable.withColumn("bar").ofType(ColumnType.NUMBER);
dataContext.executeUpdate(createTable);
final Table table = schema.getTableByName("testCreateTable");
assertEquals(
"[" + ElasticSearchDataContext.FIELD_ID + ", foo, bar]",
Arrays.toString(table.getColumnNames()));
final Column fooColumn = table.getColumnByName("foo");
final Column idColumn = table.getPrimaryKeys()[0];
assertEquals(
"Column[name=_id,columnNumber=0,type=STRING,nullable=null,nativeType=null,columnSize=null]",
idColumn.toString());
dataContext.executeUpdate(
new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
callback.insertInto(table).value("foo", "hello").value("bar", 42).execute();
callback.insertInto(table).value("foo", "world").value("bar", 43).execute();
}
});
dataContext.refreshSchemas();
try (DataSet ds = dataContext.query().from(table).selectAll().orderBy("bar").execute()) {
assertTrue(ds.next());
assertEquals("hello", ds.getRow().getValue(fooColumn).toString());
assertNotNull(ds.getRow().getValue(idColumn));
assertTrue(ds.next());
assertEquals("world", ds.getRow().getValue(fooColumn).toString());
assertNotNull(ds.getRow().getValue(idColumn));
assertFalse(ds.next());
}
dataContext.executeUpdate(new DropTable(table));
dataContext.refreshSchemas();
assertNull(dataContext.getTableByQualifiedLabel(table.getName()));
}
public void testDeveloperTable() throws Exception {
Schema schema = dc.getDefaultSchema();
assertEquals("developers.mdb", schema.getName());
assertEquals("[developer, product]", Arrays.toString(schema.getTableNames()));
Table table = schema.getTableByName("developer");
assertEquals(
"[id, name, email, male, developer_since]", Arrays.toString(table.getColumnNames()));
Column[] primaryKeys = table.getPrimaryKeys();
assertEquals(
"[Column[name=id,columnNumber=0,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]]",
Arrays.toString(primaryKeys));
Column nameCol = table.getColumnByName("name");
assertEquals(
"Column[name=name,columnNumber=1,type=VARCHAR,nullable=null,nativeType=TEXT,columnSize=100]",
nameCol.toString());
Column maleCol = table.getColumnByName("male");
assertEquals(
"Column[name=male,columnNumber=3,type=BOOLEAN,nullable=null,nativeType=BOOLEAN,columnSize=1]",
maleCol.toString());
Column developerSinceCol = table.getColumnByName("developer_since");
assertEquals(
"Column[name=developer_since,columnNumber=4,type=TIMESTAMP,nullable=null,nativeType=SHORT_DATE_TIME,columnSize=8]",
developerSinceCol.toString());
DataSet ds =
dc.executeQuery(new Query().select(nameCol, maleCol, developerSinceCol).from(table));
while (ds.next()) {
Row row = ds.getRow();
assertEquals(3, row.getValues().length);
Object value = row.getValue(0);
assertEquals(String.class, value.getClass());
value = row.getValue(1);
assertEquals(Boolean.class, value.getClass());
value = row.getValue(2);
assertTrue(value instanceof Date);
}
}
public void testGetTableFromItems() throws Exception {
Schema schema = getExampleSchema();
Table contributorTable = schema.getTableByName(TABLE_CONTRIBUTOR);
Table projectTable = schema.getTableByName(TABLE_PROJECT);
Table projectContributorTable = schema.getTableByName(TABLE_PROJECT_CONTRIBUTOR);
FromItem sqFromItem =
new FromItem(new Query().from(projectTable).from(projectContributorTable));
FromItem fromItem =
new FromItem(
JoinType.INNER,
new FromItem(contributorTable),
sqFromItem,
new SelectItem[0],
new SelectItem[0]);
Query q = new Query().from(fromItem);
FromItem[] fromItems = MetaModelHelper.getTableFromItems(q);
assertEquals(3, fromItems.length);
assertEquals(
"[MetaModelSchema.contributor, MetaModelSchema.project, MetaModelSchema.project_contributor]",
Arrays.toString(fromItems));
}
public void testGroupByQuery() throws Exception {
Connection con = getTestDbConnection();
DataContext dc = new JdbcDataContext(con);
Schema schema = dc.getDefaultSchema();
Table employeesTable = schema.getTableByName("EMPLOYEES");
Table orderDetailsTable = schema.getTableByName("ORDERDETAILS");
Query q = new Query().from(employeesTable, "e").from(orderDetailsTable, "c");
q.select(orderDetailsTable.getColumns()[0])
.select(new SelectItem(FunctionType.MAX, employeesTable.getColumns()[0]));
q.groupBy(orderDetailsTable.getColumns()[0]);
assertEquals(
"SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c GROUP BY c._ORDERNUMBER_",
q.toString().replace('\"', '_'));
QuerySplitter qs = new QuerySplitter(dc, q);
assertEquals(326, qs.getRowCount());
List<Query> splitQueries = qs.setMaxRows(250).splitQuery();
assertEquals(
"[SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ < 10262 OR c._ORDERNUMBER_ IS NULL) GROUP BY c._ORDERNUMBER_, SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ > 10262 OR c._ORDERNUMBER_ = 10262) GROUP BY c._ORDERNUMBER_]",
Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
assertSameCount(dc, qs, splitQueries);
assertEquals(2, splitQueries.size());
assertEquals("[162, 164]", Arrays.toString(getCounts(dc, splitQueries)));
DataSet data = qs.executeQueries();
int count = 0;
while (data.next()) {
if (count == 2) {
assertEquals("Row[values=[10102, 1702]]", data.getRow().toString());
}
count++;
}
data.close();
assertEquals(326, count);
}
public void testSimpleQuerySplit() throws Exception {
Connection con = getTestDbConnection();
DataContext dc = new JdbcDataContext(con);
Schema schema = dc.getSchemaByName("PUBLIC");
Table employeesTable = schema.getTableByName("EMPLOYEES");
Table customersTable = schema.getTableByName("CUSTOMERS");
Query q = new Query().from(employeesTable, "e").from(customersTable, "c");
q.select(employeesTable.getColumns()[0], customersTable.getColumns()[0]);
assertEquals(
"SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c",
q.toString().replace('\"', '_'));
QuerySplitter qs = new QuerySplitter(dc, q);
long rowCount = qs.getRowCount();
assertEquals(2806, rowCount);
qs.setMaxRows(1000);
List<Query> splitQueries = qs.splitQuery();
assertEquals("[793, 610, 366, 714, 323]", Arrays.toString(getCounts(dc, splitQueries)));
assertEquals(
"[SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ < 299 OR c._CUSTOMERNUMBER_ IS NULL) AND (e._EMPLOYEENUMBER_ < 1352 OR e._EMPLOYEENUMBER_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ < 299 OR c._CUSTOMERNUMBER_ IS NULL) AND (e._EMPLOYEENUMBER_ > 1352 OR e._EMPLOYEENUMBER_ = 1352), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ < 1072 OR e._REPORTSTO_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ > 1072 OR e._REPORTSTO_ = 1072) AND (c._SALESREPEMPLOYEENUMBER_ < 1433 OR c._SALESREPEMPLOYEENUMBER_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ > 1072 OR e._REPORTSTO_ = 1072) AND (c._SALESREPEMPLOYEENUMBER_ > 1433 OR c._SALESREPEMPLOYEENUMBER_ = 1433)]",
Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
assertSameCount(dc, qs, splitQueries);
assertEquals(5, splitQueries.size());
splitQueries = qs.setMaxRows(300).splitQuery();
assertSameCount(dc, qs, splitQueries);
assertEquals(
"[299, 221, 170, 299, 276, 253, 102, 289, 253, 138, 368, 138]",
Arrays.toString(getCounts(dc, splitQueries)));
splitQueries = qs.setMaxRows(5000).splitQuery();
assertEquals(1, splitQueries.size());
assertSame(q, splitQueries.get(0));
}
