/**
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.apache.metamodel.jdbc;
import java.sql.Connection;
import java.util.Arrays;
import java.util.List;
import org.apache.metamodel.DataContext;
import org.apache.metamodel.data.DataSet;
import org.apache.metamodel.query.FromItem;
import org.apache.metamodel.query.FunctionType;
import org.apache.metamodel.query.JoinType;
import org.apache.metamodel.query.Query;
import org.apache.metamodel.query.SelectItem;
import org.apache.metamodel.schema.Relationship;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
public class QuerySplitterTest extends JdbcTestCase {
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));
}
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 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)));
}
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);
}
/**
* Utility method for asserting that a query and it's splitted queries have
* the same total count
*/
private void assertSameCount(DataContext dc, QuerySplitter qs,
List<Query> queries) {
long count1 = qs.getRowCount();
long count2 = 0;
for (Query q : queries) {
count2 += getCount(dc, q);
}
assertEquals(count1, count2);
}
public long[] getCounts(DataContext dc, List<Query> queries) {
long[] result = new long[queries.size()];
for (int i = 0; i < result.length; i++) {
result[i] = getCount(dc, queries.get(i));
}
return result;
}
/**
* Gets the count of a query
*/
private long getCount(DataContext dc, Query query) {
return new QuerySplitter(dc, query).getRowCount();
}
}