/*
* JBoss, Home of Professional Open Source.
* See the COPYRIGHT.txt file distributed with this work for information
* regarding copyright ownership. Some portions may be licensed
* to Red Hat, Inc. under one or more contributor license agreements.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/
package org.teiid.translator.mongodb;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.mockito.ArgumentCaptor;
import org.mockito.Mockito;
import org.mockito.invocation.InvocationOnMock;
import org.mockito.stubbing.Answer;
import org.teiid.cdk.api.TranslationUtility;
import org.teiid.core.types.ClobImpl;
import org.teiid.core.types.ClobType;
import org.teiid.core.types.DataTypeManager;
import org.teiid.core.types.GeometryType;
import org.teiid.core.util.ObjectConverterUtil;
import org.teiid.core.util.UnitTestUtil;
import org.teiid.language.ColumnReference;
import org.teiid.language.Command;
import org.teiid.language.Comparison;
import org.teiid.language.DerivedColumn;
import org.teiid.language.Function;
import org.teiid.language.Literal;
import org.teiid.language.NamedTable;
import org.teiid.language.QueryExpression;
import org.teiid.language.Select;
import org.teiid.language.TableReference;
import org.teiid.metadata.FunctionMethod;
import org.teiid.metadata.FunctionParameter;
import org.teiid.metadata.MetadataFactory;
import org.teiid.metadata.Table;
import org.teiid.mongodb.MongoDBConnection;
import org.teiid.query.function.FunctionTree;
import org.teiid.query.function.GeometryUtils;
import org.teiid.query.function.UDFSource;
import org.teiid.query.metadata.MetadataValidator;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.parser.TestDDLParser;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.validator.ValidatorReport;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.ResultSetExecution;
import org.teiid.translator.TranslatorException;
import com.mongodb.AggregationOptions;
import com.mongodb.AggregationOutput;
import com.mongodb.BasicDBList;
import com.mongodb.BasicDBObject;
import com.mongodb.BasicDBObjectBuilder;
import com.mongodb.Cursor;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.QueryBuilder;
@SuppressWarnings("nls")
public class TestMongoDBQueryExecution {
private MongoDBExecutionFactory translator;
private TranslationUtility utility;
private static AggregationOptions options = AggregationOptions.builder()
.batchSize(256)
.outputMode(AggregationOptions.OutputMode.CURSOR)
.allowDiskUse(true)
.build();
@Before
public void setUp() throws Exception {
this.translator = new MongoDBExecutionFactory();
this.translator.setDatabaseVersion("2.6");
this.translator.start();
MetadataFactory mf = TestDDLParser.helpParse(ObjectConverterUtil.convertFileToString(UnitTestUtil.getTestDataFile("northwind.ddl")), "northwind");
TransformationMetadata metadata = RealMetadataFactory.createTransformationMetadata(mf.asMetadataStore(), "sakila", new FunctionTree("mongo", new UDFSource(translator.getPushDownFunctions())));
ValidatorReport report = new MetadataValidator().validate(metadata.getVdbMetaData(), metadata.getMetadataStore());
if (report.hasItems()) {
throw new RuntimeException(report.getFailureMessage());
}
this.utility = new TranslationUtility(metadata);
}
private DBCollection helpExecute(String query, String[] expectedCollection, int expectedParameters) throws TranslatorException {
Command cmd = this.utility.parseCommand(query);
return helpExecute(cmd, expectedCollection, expectedParameters);
}
private DBCollection helpExecute(Command cmd, String[] expectedCollection, int expectedParameters) throws TranslatorException {
ExecutionContext context = Mockito.mock(ExecutionContext.class);
Mockito.stub(context.getBatchSize()).toReturn(256);
MongoDBConnection connection = Mockito.mock(MongoDBConnection.class);
DB db = Mockito.mock(DB.class);
DBCollection dbCollection = Mockito.mock(DBCollection.class);
for(String collection:expectedCollection) {
Mockito.stub(db.getCollection(collection)).toReturn(dbCollection);
}
AggregationOutput output = Mockito.mock(AggregationOutput.class);
Mockito.stub(output.results()).toReturn(new ArrayList<DBObject>());
ArrayList<DBObject> params = new ArrayList<DBObject>();
for (int i = 0; i < expectedParameters; i++) {
params.add(Mockito.any(DBObject.class));
}
Mockito.stub(dbCollection.aggregate(params.remove(0), params.toArray(new DBObject[params.size()]))).toReturn(output);
Mockito.stub(db.collectionExists(Mockito.anyString())).toReturn(true);
Mockito.stub(connection.getDatabase()).toReturn(db);
Mockito.stub(db.getCollectionFromString(Mockito.anyString())).toReturn(dbCollection);
ResultSetExecution execution = this.translator.createResultSetExecution((QueryExpression)cmd, context,
this.utility.createRuntimeMetadata(), connection);
execution.execute();
return dbCollection;
}
@Test
public void testSimpleSelectNoAssosiations() throws Exception {
String query = "SELECT * FROM Customers";
DBCollection dbCollection = helpExecute(query, new String[]{"Customers"}, 1);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$CompanyName");
result.append( "_m2","$ContactName");
result.append( "_m3","$ContactTitle");
result.append( "_m4","$Address");
result.append( "_m5","$City");
result.append( "_m6","$Region");
result.append( "_m7","$PostalCode");
result.append( "_m8","$Country");
result.append( "_m9","$Phone");
result.append( "_m10","$Fax");
List<DBObject> pipeline = buildArray(new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSimpleWhere() throws Exception {
String query = "SELECT CompanyName, ContactTitle FROM Customers WHERE Country='USA'";
DBCollection dbCollection = helpExecute(query, new String[]{"Customers"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$CompanyName");
result.append( "_m1","$ContactTitle");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", new BasicDBObject("Country", "USA")),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectEmbeddable() throws Exception {
String query = "SELECT CategoryName FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 1);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectEmbeddableWithWhere_ON_NONPK() throws Exception {
String query = "SELECT CategoryName FROM Categories WHERE CategoryName = 'Drinks'";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match",new BasicDBObject("CategoryName", "Drinks")),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectEmbeddableWithWhere_ON_PK() throws Exception {
String query = "SELECT CategoryName FROM Categories WHERE CategoryID = 10";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match",new BasicDBObject("_id", 10)),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectFromMerged() throws Exception {
String query = "SELECT UnitPrice FROM OrderDetails";
DBCollection dbCollection = helpExecute(query, new String[]{"Orders"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$OrderDetails.UnitPrice");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$unwind","$OrderDetails"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // one-2-many
public void testSelectMergedWithWhere_ON_NON_PK() throws Exception {
String query = "SELECT Quantity FROM OrderDetails WHERE UnitPrice = '0.99'";
DBCollection dbCollection = helpExecute(query, new String[]{"Orders"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$OrderDetails.Quantity");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$unwind","$OrderDetails"),
new BasicDBObject("$match", new BasicDBObject("OrderDetails.UnitPrice", 0.99)),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // one-2-one
public void testSelectMergedWithWhere_ON_NON_PK_one_to_one() throws Exception {
String query = "SELECT cust_id, zip FROM Address WHERE Street = 'Highway 100'";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$address.zip");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("address").exists("true").notEquals(null).get()),
new BasicDBObject("$match", new BasicDBObject("address.street", "Highway 100")),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // one-2-one
public void testSelectONE_TO_ONE() throws Exception {
String query = "SELECT c.name, a.zip " +
"FROM customer c join address a " +
"on c.customer_id=a.cust_id";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$name");
result.append( "_m1","$address.zip");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", new BasicDBObject("address", new BasicDBObject("$exists", "true").append("$ne", null))),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // one-2-one
public void testSelectMergedWithNOWhere_one_to_one() throws Exception {
String query = "SELECT cust_id, zip FROM Address";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$address.zip");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("address").exists("true").notEquals(null).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testTwoTableInnerJoinEmbeddableAssosiationOne() throws Exception {
String query = "select p.ProductName, c.CategoryName from Products p " +
"join Categories c on p.CategoryID = c.CategoryID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Categories.CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("Categories").exists("true").notEquals(null).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testTwoTableInnerJoinEmbeddableWithWhere() throws Exception {
String query = "select p.ProductName, c.CategoryName from Products p " +
"JOIN Categories c on p.CategoryID = c.CategoryID " +
"WHERE p.CategoryID = 1 AND c.CategoryID = 1";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Categories.CategoryName");
DBObject exists = QueryBuilder.start("Categories").exists("true").notEquals(null).get();
DBObject p1 = QueryBuilder.start("CategoryID").is(1).get();
DBObject p2 = QueryBuilder.start("CategoryID").is(1).get();
DBObject match = QueryBuilder.start().and(p1, p2).get(); // duplicate criteria, mongo should ignore it
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", exists),
new BasicDBObject("$match", match),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectNestedEmbedding() throws Exception {
String query = "select T1.e1, T1.e2, T2.t2e1, T2.t2e2, T3.t3e1, T3.t3e2 from T1 "
+ "JOIN T2 ON T1.e1=T2.t2e1 JOIN T3 ON T2.t2e1 = T3.t3e1";
DBCollection dbCollection = helpExecute(query, new String[]{"T1", "T2", "T3"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$e1");
result.append( "_m1","$_id");
result.append( "_m2","$e1");
result.append( "_m3","$T2.t2e2");
result.append( "_m4","$e1");
result.append( "_m5","$T3.t3e2");
DBObject t2 = QueryBuilder.start("T2").exists("true").notEquals(null).get();
DBObject t3 = QueryBuilder.start("T3").exists("true").notEquals(null).get();
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", t2),
new BasicDBObject("$match", t3),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectNestedMerge() throws Exception {
String query = "select * from payment";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$rental.payment._id");
result.append( "_m1","$rental._id");
result.append( "_m2","$rental.payment.amount");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$unwind","$rental"),
new BasicDBObject("$unwind","$rental.payment"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // embedded means always nested as doc not as array
public void testEmbeddedJoin_INNER() throws Exception {
String query = "SELECT p.ProductName,s.CompanyName " +
"FROM Suppliers s " +
"JOIN " +
"Products p " +
"ON s.SupplierID = p.SupplierID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Suppliers.CompanyName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("Suppliers").exists("true").notEquals(null).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // embedded means always nested as doc not as array
public void testEmbeddedJoin_INNER_REVERSE() throws Exception {
String query = "SELECT p.ProductName,s.CompanyName " +
"FROM Products p " +
"JOIN " +
"Suppliers s " +
"ON s.SupplierID = p.SupplierID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Suppliers.CompanyName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("Suppliers").exists("true").notEquals(null).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test(expected=TranslatorException.class) // embedded means always nested as doc not as array
public void testEmbeddedJoin_LEFTOUTER() throws Exception {
String query = "SELECT p.ProductName,s.CompanyName " +
"FROM Suppliers s " +
"LEFT OUTER JOIN " +
"Products p " +
"ON s.SupplierID = p.SupplierID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Suppliers.CompanyName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("SupplierID").notEquals(null).and(QueryBuilder.start("Suppliers._id").notEquals(null).get()).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // embedded means always nested as doc not as array
public void testEmbeddedJoin_LEFTOUTER2() throws Exception {
String query = "SELECT p.ProductName,s.CompanyName " +
"FROM Products p " +
"LEFT OUTER JOIN " +
"Suppliers s " +
"ON s.SupplierID = p.SupplierID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 1);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Suppliers.CompanyName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // embedded means always nested as doc not as array
public void testEmbeddedJoin_RIGHTOUTER() throws Exception {
String query = "SELECT p.ProductName,s.CompanyName " +
"FROM Suppliers s " +
"RIGHT OUTER JOIN " +
"Products p " +
"ON s.SupplierID = p.SupplierID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 1);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Suppliers.CompanyName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
// embedded means always nested as doc not as array
@Test(expected=TranslatorException.class)
public void testEmbeddedJoin_RIGHTOUTER2() throws Exception {
String query = "SELECT p.ProductName,s.CompanyName " +
"FROM Products p " +
"RIGHT OUTER JOIN " +
"Suppliers s " +
"ON s.SupplierID = p.SupplierID";
DBCollection dbCollection = helpExecute(query, new String[]{"Products"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$ProductName");
result.append( "_m1","$Suppliers.CompanyName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("_id").notEquals(null).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // merge where one to many relation
public void testMERGE_ONE_TO_MANY_Join_INNER() throws Exception {
String query = "SELECT c.name,n.Comment,n.CustomerId " +
"FROM customer c " +
"JOIN " +
"Notes n " +
"ON c.customer_id = n.CustomerId";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$name");
result.append( "_m1","$Notes.Comment");
result.append( "_m2","$_id");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$unwind", "$Notes"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // merge where one to many relation
public void testMERGE_ONE_TO_ONE_Join_INNER_ORDERBY() throws Exception {
String query = "SELECT N2.e1 AS c_0, "
+ "N1.e1 AS c_1, N1.e2 AS c_2, "
+ "N1.e3 AS c_3, N2.e2 AS c_4, N2.e3 AS c_5 "
+ "FROM N1 INNER JOIN N2 ON N1.e1 = N2.e1 "
+ "ORDER BY c_0";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "c_0","$_id"); // same expr
result.append( "c_1","$_id"); // same expr
result.append( "c_2","$e2");
result.append( "c_3","$e3");
result.append( "c_4","$N2.e2");
result.append( "c_5","$N2.e3");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("N2").exists("true").notEquals(null).get()),
new BasicDBObject("$project", result),
// note c_0, c_1 represent same expressions, so it does not matter
new BasicDBObject("$sort", new BasicDBObject("c_1", 1)));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // merge where one to many relation
public void testMERGE_ONE_TO_MANY_Join_LEFT_OUTER() throws Exception {
String query = "SELECT c.name,n.Comment " +
"FROM customer c " +
"LEFT JOIN " +
"Notes n " +
"ON c.customer_id = n.CustomerId";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$name");
result.append( "_m1","$__NN_Notes.Comment");
BasicDBObject ifnull = buildIfNullExpression("Notes");
BasicDBObject project = new BasicDBObject();
project.append("customer_id", 1);
project.append("name", 1);
project.append("__NN_Notes", ifnull);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", project),
new BasicDBObject("$unwind", "$__NN_Notes"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // merge where one to many relation - equal to inner join with doc format teiid has
public void testMERGE_ONE_TO_MANY_Join_LEFT_OUTER4() throws Exception {
String query = "SELECT c.name,n.Comment " +
"FROM customer c " +
"RIGHT JOIN " +
"Notes n " +
"ON c.customer_id = n.CustomerId";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$name");
result.append( "_m1","$Notes.Comment");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$unwind", "$Notes"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // merge where one to many relation - equal to inner join with doc format teiid has
public void testMERGE_ONE_TO_MANY_Join_LEFT_OUTER3() throws Exception {
String query = "SELECT c.name,n.Comment " +
"FROM Notes n " +
"LEFT JOIN " +
"Customer c " +
"ON c.customer_id = n.CustomerId";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 3);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$name");
result.append( "_m1","$Notes.Comment");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$unwind", "$Notes"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // merge where one to many relation (2 merged tables into customer)
public void testMERGE_ONE_TO_MANY_Join_INNER_OUTER2() throws Exception {
String query = "SELECT c.name,n.Comment ,r.amount " +
"FROM customer c " +
"LEFT JOIN " +
"Notes n " +
"ON c.customer_id = n.CustomerId " +
"LEFT JOIN rental r ON r.customer_id = c.customer_id";
//[{ "$project" : { "customer_id" : 1 , "name" : 1 ,
//"__NN_Notes" : { "$ifNull" : [ "$Notes" , [ { }]]} ,
//"__NN_rental" : { "customer_id" : 1 , "name" : 1 , "__NN_rental" : { "$ifNull" : [ "$rental" , [ { }]]}}}}, { "$unwind" : "$__NN_Notes"}, { "$unwind" : "$__NN_rental"}, { "$project" : { "_m0" : "$name" , "_m1" : "$__NN_Notes.Comment" , "_m2" : "$__NN_rental.amount"}}],
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 4);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$name");
result.append( "_m1","$__NN_Notes.Comment");
result.append( "_m2","$__NN_rental.amount");
BasicDBObject project = new BasicDBObject();
project.append("customer_id", 1);
project.append("name", 1);
project.append("__NN_Notes", buildIfNullExpression("Notes"));
project.append("__NN_rental", buildIfNullExpression("rental"));
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", project),
new BasicDBObject("$unwind", "$__NN_Notes"),
new BasicDBObject("$unwind", "$__NN_rental"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
private BasicDBObject buildIfNullExpression(String table) {
BasicDBList exprs = new BasicDBList();
exprs.add("$"+table); //$NON-NLS-1$
BasicDBList list = new BasicDBList();
list.add(new BasicDBObject());
exprs.add(list);
BasicDBObject ifnull = new BasicDBObject("$ifNull", exprs); //$NON-NLS-1$
return ifnull;
}
@Test
public void testSimpleGroupBy() throws Exception {
String query = "SELECT Country FROM Customers GROUP BY Country";
DBCollection dbCollection = helpExecute(query, new String[]{"Customers"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id._c0");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", new BasicDBObject("_id", new BasicDBObject("_c0", "$Country"))),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testMultipleGroupBy() throws Exception {
String query = "SELECT Country,City FROM Customers GROUP BY Country,City";
DBCollection dbCollection = helpExecute(query, new String[]{"Customers"}, 2);
BasicDBObject project = new BasicDBObject();
project.append( "_m0","$_id._c0");
project.append( "_m1","$_id._c1");
BasicDBObject group = new BasicDBObject();
group.append( "_c0","$Country");
group.append( "_c1","$City");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", new BasicDBObject("_id", group)),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testDistinctSingle() throws Exception {
String query = "SELECT DISTINCT Country FROM Customers";
DBCollection dbCollection = helpExecute(query, new String[]{"Customers"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id._m0");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", new BasicDBObject("_id", new BasicDBObject("_m0", "$Country"))),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testDistinctMulti() throws Exception {
String query = "SELECT DISTINCT Country, City FROM Customers";
DBCollection dbCollection = helpExecute(query, new String[]{"Customers"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id._m0");
result.append( "_m1","$_id._m1");
BasicDBObject group = new BasicDBObject();
group.append( "_m0","$Country");
group.append( "_m1","$City");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", new BasicDBObject("_id", group)),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // embedded means always nested as doc not as array
public void testONE_TO_ONE_WithGroupBy() throws Exception {
String query = "SELECT c.name, a.zip " +
"FROM customer c join address a " +
"on c.customer_id=a.cust_id " +
"GROUP BY c.name, a.zip";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 3);
BasicDBObject project = new BasicDBObject();
project.append( "_m0","$_id._c0");
project.append( "_m1","$_id._c1");
BasicDBObject group = new BasicDBObject();
group.append( "_c0","$name");
group.append( "_c1","$address.zip");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", new BasicDBObject("address", new BasicDBObject("$exists", "true").append("$ne", null))),
new BasicDBObject("$group", new BasicDBObject("_id", group)),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test // embedded means always nested as doc not as array
public void testONE_TO_ONE_WithGroupByOrderBy() throws Exception {
String query = "SELECT c.name, a.zip " +
"FROM customer c join address a " +
"on c.customer_id=a.cust_id " +
"GROUP BY c.name, a.zip " +
"ORDER BY c.name, a.zip " +
"limit 2";
DBCollection dbCollection = helpExecute(query, new String[]{"customer"}, 6);
BasicDBObject project = new BasicDBObject();
project.append( "_m0","$_id._c0");
project.append( "_m1","$_id._c1");
BasicDBObject group = new BasicDBObject();
group.append( "_c0","$name");
group.append( "_c1","$address.zip");
BasicDBObject sort = new BasicDBObject();
sort.append( "_m0",1);
sort.append( "_m1",1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", new BasicDBObject("address", new BasicDBObject("$exists", "true").append("$ne", null))),
new BasicDBObject("$group", new BasicDBObject("_id", group)),
new BasicDBObject("$project", project),
new BasicDBObject("$sort", sort),
new BasicDBObject("$skip", 0),
new BasicDBObject("$limit", 2));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSumWithGroupBy() throws Exception {
String query = "SELECT SUM(age) as total FROM users GROUP BY user_id";
DBCollection dbCollection = helpExecute(query, new String[]{"users"}, 2);
BasicDBObject id = new BasicDBObject();
id.append( "_c0","$user_id");
BasicDBObject group = new BasicDBObject("_id", id);
group.append("total", new BasicDBObject("$sum", "$age"));
BasicDBObject project = new BasicDBObject();
project.append( "total",1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSumWithGroupBy2() throws Exception {
String query = "SELECT user_id, status, SUM(age) as total FROM users GROUP BY user_id, status";
DBCollection dbCollection = helpExecute(query, new String[]{"users"}, 2);
BasicDBObject project = new BasicDBObject();
project.append( "_m0","$_id._c0");
project.append( "_m1","$_id._c1");
project.append( "total",1);
BasicDBObject id = new BasicDBObject();
id.append( "_c0","$user_id");
id.append( "_c1","$status");
BasicDBObject group = new BasicDBObject("_id", id);
group.append("total", new BasicDBObject("$sum", "$age"));
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSumWithGroupBy3() throws Exception {
String query = "SELECT user_id, SUM(age) as total FROM users GROUP BY user_id";
DBCollection dbCollection = helpExecute(query, new String[]{"users"}, 2);
BasicDBObject project = new BasicDBObject();
project.append( "_m0","$_id._c0");
project.append( "total",1);
BasicDBObject id = new BasicDBObject();
id.append( "_c0","$user_id");
BasicDBObject group = new BasicDBObject("_id", id);
group.append("total", new BasicDBObject("$sum", "$age"));
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testAggregateWithHaving() throws Exception {
String query = "SELECT SUM(age) as total FROM users GROUP BY user_id HAVING SUM(age) > 250";
DBCollection dbCollection = helpExecute(query, new String[]{"users"}, 3);
BasicDBObject project = new BasicDBObject();
project.append( "total",1);
BasicDBObject id = new BasicDBObject();
id.append( "_c0","$user_id");
BasicDBObject group = new BasicDBObject("_id", id);
group.append("total", new BasicDBObject("$sum", "$age"));
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$match", QueryBuilder.start("total").greaterThan(250).get()),
new BasicDBObject("$project", project));
ArgumentCaptor<List> actualCapture = ArgumentCaptor.forClass(List.class);
Mockito.verify(dbCollection).aggregate(actualCapture.capture(), Mockito.any(AggregationOptions.class));
Assert.assertEquals(pipeline.toString(), actualCapture.getValue().toString());
}
@Test
public void testAggregateWithHavingAndWhere() throws Exception {
String query = "SELECT SUM(age) as total FROM users WHERE age > 45 GROUP BY user_id HAVING SUM(age) > 250";
DBCollection dbCollection = helpExecute(query, new String[]{"users"}, 4);
BasicDBObject project = new BasicDBObject();
project.append( "total",1);
BasicDBObject id = new BasicDBObject();
id.append( "_c0","$user_id");
BasicDBObject group = new BasicDBObject("_id", id);
group.append("total", new BasicDBObject("$sum", "$age"));
ArrayList<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("age").greaterThan(45).get()),
new BasicDBObject("$group", group),
new BasicDBObject("$match", QueryBuilder.start("total").greaterThan(250).get()),
new BasicDBObject("$project", project));
ArgumentCaptor<List> actualCapture = ArgumentCaptor.forClass(List.class);
ArgumentCaptor<AggregationOptions> optionsCapture = ArgumentCaptor.forClass(AggregationOptions.class);
Mockito.verify(dbCollection).aggregate(actualCapture.capture(), optionsCapture.capture());
Assert.assertEquals(pipeline.toString(), actualCapture.getValue().toString());
Assert.assertEquals(options.toString(), optionsCapture.getValue().toString());
}
public static ArrayList<DBObject> buildArray(DBObject ...basicDBObjects){
ArrayList<DBObject> list = new ArrayList<DBObject>();
for (DBObject obj:basicDBObjects) {
list.add(obj);
}
return list;
}
@Test
public void testCountStar() throws Exception {
String query = "SELECT count(*) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 2);
BasicDBObject group = new BasicDBObject();
group.append( "_id", null);
group.append( "_m0", new BasicDBObject("$sum", 1));
BasicDBObject result = new BasicDBObject();
result.append( "_m0", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testCountOnColumn() throws Exception {
String query = "SELECT count(CategoryName) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 3);
BasicDBList eq = new BasicDBList();
eq.add(0, "$CategoryName");
eq.add(1, null);
BasicDBList values = new BasicDBList();
values.add(0, new BasicDBObject("$eq", eq)); //$NON-NLS-1$
values.add(1, 0);
values.add(2, 1);
BasicDBObject expr = new BasicDBObject("$sum",new BasicDBObject("$cond", values)); //$NON-NLS-1$ //$NON-NLS-2$
BasicDBObject group = new BasicDBObject();
group.append( "_m0", expr);
group.append( "_id", null);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testCountOnmultipleDifferentColumns() throws Exception {
String query = "SELECT count(CategoryName), avg(CategoryID) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 3);
BasicDBList eq = new BasicDBList();
eq.add(0, "$CategoryName");
eq.add(1, null);
BasicDBList values = new BasicDBList();
values.add(0, new BasicDBObject("$eq", eq)); //$NON-NLS-1$
values.add(1, 0);
values.add(2, 1);
BasicDBObject expr = new BasicDBObject("$sum",new BasicDBObject("$cond", values)); //$NON-NLS-1$ //$NON-NLS-2$
BasicDBObject group = new BasicDBObject();
group.append( "_m0", expr);
group.append( "_m1", new BasicDBObject("$avg", "$_id"));
group.append( "_id", null);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", 1);
result.append( "_m1", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testMultipleAggregateWithCountOnSameColumn() throws Exception {
String query = "SELECT count(CategoryName), avg(CategoryName) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 3);
BasicDBList eq = new BasicDBList();
eq.add(0, "$CategoryName");
eq.add(1, null);
BasicDBList values = new BasicDBList();
values.add(0, new BasicDBObject("$eq", eq)); //$NON-NLS-1$
values.add(1, 0);
values.add(2, 1);
BasicDBObject expr = new BasicDBObject("$sum",new BasicDBObject("$cond", values)); //$NON-NLS-1$ //$NON-NLS-2$
BasicDBObject group = new BasicDBObject();
group.append( "_m0", expr);
group.append( "_m1", new BasicDBObject("$avg", "$CategoryName"));
group.append( "_id", null);
BasicDBObject project = new BasicDBObject();
project.append( "_m0", 1);
project.append( "_m1", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testMultipleAggregateOnSameColumn() throws Exception {
String query = "SELECT sum(CategoryName), avg(CategoryName) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 3);
BasicDBObject group = new BasicDBObject();
group.append( "_id", null);
group.append( "_m0", new BasicDBObject("$sum", "$CategoryName"));
group.append( "_m1", new BasicDBObject("$avg", "$CategoryName"));
BasicDBObject project = new BasicDBObject();
project.append( "_m0", 1);
project.append( "_m1", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testMultipleAggregateOnSameColumnWithGroupBy() throws Exception {
String query = "SELECT sum(CategoryName), avg(CategoryName) FROM Categories Group By Picture";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 3);
BasicDBObject group = new BasicDBObject();
group.append( "_id", new BasicDBObject("_c0", "$Picture"));
group.append( "_m0", new BasicDBObject("$sum", "$CategoryName"));
group.append( "_m1", new BasicDBObject("$avg", "$CategoryName"));
BasicDBObject project = new BasicDBObject();
project.append( "_m0", 1);
project.append( "_m1", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testMultipleAggregateOnSameColumn_withCountSTAR() throws Exception {
String query = "SELECT count(*), avg(CategoryName) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 3);
BasicDBObject group = new BasicDBObject();
group.append( "_id", null);
group.append( "_m0", new BasicDBObject("$sum", 1));
group.append( "_m1", new BasicDBObject("$avg", "$CategoryName"));
BasicDBObject project = new BasicDBObject();
project.append( "_m0", 1);
project.append( "_m1", 1);
List<DBObject> pipeline = buildArray(
new BasicDBObject("$group", group),
new BasicDBObject("$project", project));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testFunctionInWhere() throws Exception {
String query = "SELECT CategoryName FROM Categories WHERE CONCAT(CategoryName, '2') = '2'";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 2);
// { "$project" : { "_m0" : { "$concat" : [ "$CategoryName" , "2"]} , "_m1" : "$CategoryName"}},
BasicDBList params = new BasicDBList();
params.add("$CategoryName");
params.add("2");
BasicDBObject result = new BasicDBObject();
result.append( "_m0", new BasicDBObject("$concat", params));
result.append( "_m1", "$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result),
new BasicDBObject("$match", QueryBuilder.start("_m0").is("2").get()));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSubStr() throws Exception {
String query = "SELECT SUBSTRING(CategoryName, 3) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 1);
//{ "$subtract" : [ 3 , 1]}
BasicDBList subtract = new BasicDBList();
subtract.add(3);
subtract.add(1);
BasicDBList params = new BasicDBList();
params.add("$CategoryName");
params.add(new BasicDBObject("$subtract", subtract));
params.add(4000);
DBObject ne = buildNE("$CategoryName", null);
BasicDBObject func = new BasicDBObject("$substr", params);
BasicDBObject expr = buildCondition(ne, func, null);
//{ "$project" : { "_m0" : { "$substr" : [ "$CategoryName" , 1 , 4000]}}}
BasicDBObject result = new BasicDBObject();
result.append( "_m0", expr);
List<DBObject> pipeline = buildArray(new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testToLower() throws Exception {
String query = "SELECT LCASE(CategoryName) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 1);
DBObject ne = buildNE("$CategoryName", null);
BasicDBObject func = new BasicDBObject("$toLower", "$CategoryName");
BasicDBObject expr = buildCondition(ne, func, null);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", expr);
List<DBObject> pipeline = buildArray(new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
private BasicDBObject buildCondition(Object expr, Object trueExpr, Object falseExpr) {
BasicDBList values = new BasicDBList();
values.add(0, expr);
values.add(1, trueExpr);
values.add(2, falseExpr);
return new BasicDBObject("$cond", values);
}
private BasicDBObject buildNE(Object leftExpr, Object rightExpr) {
BasicDBList values = new BasicDBList();
values.add(0, leftExpr);
values.add(1, rightExpr);
return new BasicDBObject("$ne", values);
}
@Test
public void testSubStr2() throws Exception {
String query = "SELECT SUBSTRING(CategoryName, CategoryID, 4) FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 1);
BasicDBList subtract = new BasicDBList();
subtract.add("$_id");
subtract.add(1);
BasicDBList params = new BasicDBList();
params.add("$CategoryName");
params.add(new BasicDBObject("$subtract", subtract));
params.add(4);
DBObject ne = buildNE("$CategoryName", null);
BasicDBObject func = new BasicDBObject("$substr", params);
BasicDBObject expr = buildCondition(ne, func, null);
//{ "$project" : { "_m0" : { "$substr" : [ "$CategoryName" , 1 , 4000]}}}
BasicDBObject result = new BasicDBObject();
result.append( "_m0", expr);
List<DBObject> pipeline = buildArray(new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testSelectConstant() throws Exception {
String query = "SELECT 'hit' FROM Categories";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 1);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", new BasicDBObject("$literal", "hit"));
List<DBObject> pipeline = buildArray(new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testOffsetWithoutLimit() throws Exception {
String query = "SELECT CategoryName FROM Categories OFFSET 45 ROWS";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", "$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result),
new BasicDBObject("$skip", 45));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testArrtyType() throws Exception {
String query = "SELECT * FROM ArrayTest";
DBCollection dbCollection = helpExecute(query, new String[]{"ArrayTest"}, 1);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", "$id");
result.append("_m1", "$column1");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testArrtyTypeInWhere() throws Exception {
String query = "SELECT * FROM ArrayTest where column1 is not null";
DBCollection dbCollection = helpExecute(query, new String[]{"ArrayTest"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0", "$id");
result.append("_m1", "$column1");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("column1").notEquals(null).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testGeoFunctionInWhere() throws Exception {
String query = "SELECT CategoryName FROM Categories WHERE mongo.geoWithin(CategoryName, 'Polygon', ((cast(1.0 as double), cast(2.0 as double)),(cast(3.0 as double), cast(4.0 as double)))) or CategoryID=1";
DBCollection dbCollection = helpExecute(query, new String[]{"Categories"}, 2);
BasicDBObjectBuilder builder = new BasicDBObjectBuilder();
builder.push("CategoryName");
builder.push("$geoWithin");//$NON-NLS-1$
builder.push("$geometry");//$NON-NLS-1$
builder.add("type", "Polygon");//$NON-NLS-1$
BasicDBList coordinates = new BasicDBList();
BasicDBList pointOne = new BasicDBList();
pointOne.add(new Double("1.0"));
pointOne.add(new Double("2.0"));
BasicDBList pointTwo = new BasicDBList();
pointTwo.add(new Double("3.0"));
pointTwo.add(new Double("4.0"));
BasicDBList points = new BasicDBList();
points.add(pointOne);
points.add(pointTwo);
coordinates.add(points);
builder.add("coordinates", coordinates); //$NON-NLS-1$
QueryBuilder qb = QueryBuilder.start().or(builder.get(), new BasicDBObject("_id", 1));
BasicDBObject result = new BasicDBObject();
result.append( "_m1", "$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", qb.get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
private FunctionMethod getFunctionMethod(String name) {
for (FunctionMethod fm: this.translator.getPushDownFunctions()) {
if (fm.getName().equalsIgnoreCase(name)) {
for (FunctionParameter fp:fm.getInputParameters()) {
if (fp.getType().equals(DataTypeManager.DefaultDataTypes.GEOMETRY)) {
return fm;
}
}
}
}
return null;
}
@Test
public void testGeoFunctionInWhereWithGeometry() throws Exception {
Table table = this.utility.createRuntimeMetadata().getTable("northwind.Categories");
NamedTable namedTable = new NamedTable("Categories", "g0", table);
ColumnReference colRef = new ColumnReference(namedTable, "CategoryName", table.getColumnByName("CategoryName"), String.class);
DerivedColumn col = new DerivedColumn("CategoryName", colRef);
Select select = new Select();
select.setDerivedColumns(Arrays.asList(col));
List<TableReference> tables = new ArrayList<TableReference>();
tables.add(namedTable);
select.setFrom(tables);
final GeometryType geo = GeometryUtils.geometryFromClob(new ClobType(new ClobImpl("POLYGON ((1.0 2.0,3.0 4.0,5.0 6.0,1.0 2.0))")));
Function function = new Function("mongo.geoWithin", Arrays.asList(colRef, new Literal(geo, GeometryType.class)), //$NON-NLS-1$
Boolean.class); //$NON-NLS-2$
function.setMetadataObject(getFunctionMethod("mongo.geoWithin"));
Comparison c = new Comparison(function, new Literal(true, Boolean.class), Comparison.Operator.EQ);
select.setWhere(c);
DBCollection dbCollection = helpExecute(select, new String[]{"Categories"}, 2);
BasicDBObjectBuilder builder = new BasicDBObjectBuilder();
builder.push("CategoryName");
builder.push("$geoWithin");//$NON-NLS-1$
builder.add("$geometry", "{\"type\":\"Polygon\",\"coordinates\":[[[1.0,2.0],[3.0,4.0],[5.0,6.0],[1.0,2.0]]]}");//$NON-NLS-1$
BasicDBObject result = new BasicDBObject();
result.append( "CategoryName", "$CategoryName");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", builder.get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test(expected=TranslatorException.class)
public void testGeoFunctionInWhereWithFalse() throws Exception {
String query = "SELECT CategoryName FROM Categories WHERE mongo.geoWithin(CategoryName, 'Polygon', ((cast(1.0 as double), cast(2.0 as double)),(cast(3.0 as double), cast(4.0 as double)))) = false";
helpExecute(query, new String[]{"Categories"}, 2);
}
@Test
public void testAdd() throws Exception {
String query = "SELECT SupplierID+1 FROM Suppliers";
DBCollection dbCollection = helpExecute(query, new String[]{"Suppliers"}, 1);
//{ "$project" : { "_m0" : { "$add" : [ "$_id" , 1]}}}
BasicDBObject result = new BasicDBObject();
result.append( "_m0",new BasicDBObject("$add", buildObjectArray("$_id", 1)));
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
ArrayList<Object> buildObjectArray(Object ...objs){
ArrayList<Object> list = new ArrayList<Object>();
for (Object obj:objs) {
list.add(obj);
}
return list;
}
@Test
public void testNextWithGroupAndOrder() throws Exception {
String query = "select \"FirstName\" from \"TeiidArray\" group by \"FirstName\" order by \"FirstName\" limit 1000";
String[] expectedCollection = new String[]{"TeiidArray"};
TransformationMetadata metadata = RealMetadataFactory.fromDDL("CREATE FOREIGN TABLE TeiidArray (ID String PRIMARY KEY, FirstName varchar(25), LastName varchar(25), Score object[]) OPTIONS(UPDATABLE 'TRUE');", "x", "y");
TranslationUtility util = new TranslationUtility(metadata);
Command cmd = util.parseCommand(query);
ExecutionContext context = Mockito.mock(ExecutionContext.class);
Mockito.stub(context.getBatchSize()).toReturn(256);
MongoDBConnection connection = Mockito.mock(MongoDBConnection.class);
DB db = Mockito.mock(DB.class);
DBCollection dbCollection = Mockito.mock(DBCollection.class);
for(String collection:expectedCollection) {
Mockito.stub(db.getCollection(collection)).toReturn(dbCollection);
}
Cursor c = Mockito.mock(Cursor.class);
Mockito.stub(c.hasNext()).toAnswer(new Answer<Boolean>() {
boolean next = true;
@Override
public Boolean answer(InvocationOnMock invocation) throws Throwable {
if (next) {
next = false;
return true;
}
return false;
}
});
DBObject dbo = Mockito.mock(DBObject.class);
Mockito.stub(c.next()).toReturn(dbo);
Mockito.stub(dbCollection.aggregate((List<DBObject>)Mockito.anyList(), (AggregationOptions)Mockito.anyObject())).toReturn(c);
Mockito.stub(db.collectionExists(Mockito.anyString())).toReturn(true);
Mockito.stub(connection.getDatabase()).toReturn(db);
Mockito.stub(db.getCollectionFromString(Mockito.anyString())).toReturn(dbCollection);
ResultSetExecution execution = this.translator.createResultSetExecution((QueryExpression)cmd, context,
util.createRuntimeMetadata(), connection);
execution.execute();
execution.next();
}
@Test
public void testNestedMergeSelect_one_2_one() throws Exception {
String query = "SELECT e1, e2, e3 FROM N3";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$N2.N3.e2");
result.append( "_m2","$N2.N3.e3");
DBObject n2 = QueryBuilder.start("N2").exists("true").notEquals(null).get();
DBObject n3 = QueryBuilder.start("N2.N3").exists("true").notEquals(null).get();
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match",n2),
new BasicDBObject("$match",n3),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testNestedMergeSelect_one_2_one_inner_joined() throws Exception {
String query = "select N1.e1, N2.e2, N3.e3 FROM N1 JOIN N2 ON N1.e1=N2.e1 JOIN N3 ON N2.e1 = N3.e1";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$N2.e2");
result.append( "_m2","$N2.N3.e3");
DBObject n2 = QueryBuilder.start("N2").exists("true").notEquals(null).get();
DBObject n3 = QueryBuilder.start("N2.N3").exists("true").notEquals(null).get();
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match",n2),
new BasicDBObject("$match",n3),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testNestedMergeSelect_one_2_many() throws Exception {
String query = "SELECT e1, e2, e3 FROM N4 Where N4.e3 = 4";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$N2.N4._id");
result.append( "_m1","$_id");
result.append( "_m2","$N2.N4.e3");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("N2").exists("true").notEquals(null).get()),
new BasicDBObject("$unwind", "$N2.N4"),
new BasicDBObject("$match", QueryBuilder.start("N2.N4.e3").is(4).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testNestedMergeSelect_one_2_many_onid() throws Exception {
String query = "SELECT e1, e2, e3 FROM N4 Where N4.e2 = 4";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$N2.N4._id");
result.append( "_m1","$_id");
result.append( "_m2","$N2.N4.e3");
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", QueryBuilder.start("N2").exists("true").notEquals(null).get()),
new BasicDBObject("$unwind", "$N2.N4"),
new BasicDBObject("$match", QueryBuilder.start("_id").is(4).get()),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testNestedMergeSelect_one_2_many_inner_joined() throws Exception {
String query = "select N1.e1, N2.e2, N4.e3 FROM N1 JOIN N2 ON N1.e1=N2.e1 JOIN N4 ON N2.e1 = N4.e2 Order by N1.e1";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$N2.e2");
result.append( "_m2","$N2.N4.e3");
DBObject n2 = QueryBuilder.start("N2").exists("true").notEquals(null).get();
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", n2),
new BasicDBObject("$unwind", "$N2.N4"),
new BasicDBObject("$project", result),
new BasicDBObject("$sort", new BasicDBObject("_m0", 1)));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testNestedMergeSelect_one_2_many2() throws Exception {
String query = "select N2.*, N4.* FROM N2 LEFT OUTER JOIN N4 ON N2.e1 = N4.e2";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
// [{ "$project" : { "N2.e1" : 1 , "N2.e2" : 1 , "N2.e3" : 1 , "__NN_N4" : { "$ifNull" : [ "$N2.N4" , [ { }]]}}},
//{ "$match" : { "N2" : { "$exists" : "true" , "$ne" : null }}}, { "$unwind" : "$__NN_N4"}, { "$project" : { "c_0" : "$_id" , "c_1" : "$N2.e2" , "c_2" : "$N2.e3" , "c_3" : "$__NN_N4._id" , "c_4" : "$_id" , "c_5" : "$__NN_N4.e3"}}, { "$skip" : 0}, { "$limit" : 100}]
BasicDBObject projection = new BasicDBObject();
projection.append( "N2.e1", 1);
projection.append( "N2.e2", 1);
projection.append( "N2.e3", 1);
projection.append("__NN_N4", buildIfNullExpression("N2.N4"));
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$N2.e2");
result.append( "_m2","$N2.e3");
result.append( "_m3","$__NN_N4._id");
result.append( "_m4","$_id");
result.append( "_m5","$__NN_N4.e3");
DBObject n2 = QueryBuilder.start("N2").exists("true").notEquals(null).get();
List<DBObject> pipeline = buildArray(
new BasicDBObject("$project", projection),
new BasicDBObject("$match", n2),
new BasicDBObject("$unwind", "$__NN_N4"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
@Test
public void testNestedMergeSelect_inner_and_left_joined() throws Exception {
String query = "select N1.e1, N2.e2, N4.e1, N4.e2, N4.e3 "
+ "FROM N1 JOIN N2 ON N1.e1=N2.e1 "
+ "LEFT JOIN N4 ON N2.e1 = N4.e2 ";
DBCollection dbCollection = helpExecute(query, new String[]{"N1"}, 2);
BasicDBObject result = new BasicDBObject();
result.append( "_m0","$_id");
result.append( "_m1","$N2.e2");
result.append( "_m2","$__NN_N4._id");
result.append( "_m3","$_id");
result.append( "_m4","$__NN_N4.e3");
BasicDBObject projection = new BasicDBObject();
projection.append( "e1", 1);
projection.append( "e2", 1);
projection.append( "e3", 1);
projection.append("__NN_N4", buildIfNullExpression("N2.N4"));
DBObject n2 = QueryBuilder.start("N2").exists("true").notEquals(null).get();
List<DBObject> pipeline = buildArray(
new BasicDBObject("$match", n2),
new BasicDBObject("$project", projection),
new BasicDBObject("$unwind", "$__NN_N4"),
new BasicDBObject("$project", result));
Mockito.verify(dbCollection).aggregate(Mockito.eq(pipeline), Mockito.any(AggregationOptions.class));
}
}