package org.yaac.server.egql;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.yaac.server.egql.TestUtil.parser;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import org.antlr.runtime.RecognitionException;
import org.junit.Test;
import org.yaac.server.egql.processor.DatastoreLoader;
import org.yaac.server.egql.processor.ProcessContext;
import org.yaac.server.egql.processor.ProcessData;
import org.yaac.server.egql.processor.ProcessData.ProcessDataRecord;
import org.yaac.server.egql.processor.Processor;
import org.yaac.server.egql.processor.SelectProccesor;
import org.yaac.shared.egql.EGQLConstant;
import org.yaac.test.GAETestBase;
import com.google.appengine.api.datastore.DatastoreServiceFactory;
import com.google.appengine.api.datastore.Entity;
import com.google.appengine.api.datastore.Key;
/**
* specifically test SelectQuery.process
*
* @author Max Zhu (thebbsky@gmail.com)
*
*/
public class SelectQueryProcessTest extends GAETestBase {
/**
* @param query
* @param entities
* @return
* @throws RecognitionException
*/
private ProcessData process(String query, String kind,
String [] propertyNames, String [] keyNames, Object [][] rows) throws RecognitionException {
// step 1 : prepare data
DatastoreServiceFactory.getDatastoreService().put(toEntities(kind, propertyNames, keyNames, rows));
// step 2 : parse statement
SelectStatement stmt = parser(query).select_statement().stmt;
// step 3 : run job
ProcessContext context = new ProcessContext();
context.setClientId("user@test.com");
Processor loader = new DatastoreLoader(kind, EGQLConstant.DEFAULT_BATCH_SIZE);
Processor selector = new SelectProccesor(stmt);
return selector.process(context, loader.process(context, null));
}
/**
* @param kind
* @param propertyNames
* @param rows
* @return
*/
private List<Entity> toEntities(String kind, String [] propertyNames,
String [] keyNames,
Object [][] rows) {
List<Entity> result = new ArrayList<Entity>(rows.length);
for (int j = 0 ; j < rows.length ; j ++) {
Entity e = new Entity(kind, keyNames[j]);
for (int i = 0 ; i < propertyNames.length ; i ++) {
e.setProperty(propertyNames[i], rows[j][i]);
}
result.add(e);
}
return result;
}
/**
* test normal case
*
* @throws RecognitionException
*/
@Test
public void test1() throws RecognitionException {
String query = "select __key__, state from job";
List<ProcessDataRecord> actualCells = process(query, "job",
new String[]{"state"},
new String[]{"key1", "key2"},
new Object[][] {
new Object[] {"S1"},
new Object[] {"S2"},
}).getRecords();
// expected result
// key("key1"), S1
// key("key2"), S2
assertEquals(2, actualCells.size());
assertTrue(actualCells.get(0).lookup("__key__").getPayload() instanceof Key);
assertTrue(actualCells.get(1).lookup("__key__").getPayload() instanceof Key);
assertEquals("S1", actualCells.get(0).lookup("state").getPayload());
assertEquals("S2", actualCells.get(1).lookup("state").getPayload());
}
/**
* with where clause
*
* @throws RecognitionException
*/
@Test
public void test2() throws RecognitionException {
String query = "select __key__, state from job where state like '%2'";
List<ProcessDataRecord> actualCells = process(query, "job",
new String[]{"state"},
new String[]{"key1", "key2"},
new Object[][] {
new Object[] {"S1"},
new Object[] {"S2"},
}).getRecords();
// expected result
// key("key2"), S2
assertEquals(1, actualCells.size());
assertTrue(actualCells.get(0).lookup("__key__").getPayload() instanceof Key);
assertEquals("S2", actualCells.get(0).lookup("state").getPayload());
}
/**
* with aggregation functions
*
* @throws RecognitionException
*/
@Test
public void test3() throws RecognitionException {
String query = "select count(__key__) from job where state > 'S3'";
List<ProcessDataRecord> actualCells = process(query, "job",
new String[]{"state"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1"},
new Object[] {"S2"},
new Object[] {"S3"},
new Object[] {"S4"},
new Object[] {"S5"},
new Object[] {"S6"},
new Object[] {"S7"},
new Object[] {"S8"},
new Object[] {"S9"},
new Object[] {"S0"},
}).getRecords();
// expected result
// 6
assertEquals(1, actualCells.size());
assertEquals(new BigDecimal("6"), actualCells.get(0).lookup("count(__key__)").getPayload());
}
/**
* with group by clause
*
* @throws RecognitionException
*/
@Test
public void test4() throws RecognitionException {
String query = "select state, sum(size) from job group by state";
List<ProcessDataRecord> actualCells = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L},
new Object[] {"S1", 2L},
new Object[] {"S1", 3l},
new Object[] {"S2", 4l},
new Object[] {"S2", 5l},
new Object[] {"S2", 6l},
new Object[] {"S2", 7l},
new Object[] {"S2", 8l},
new Object[] {"S2", 9l},
new Object[] {"S3", 10l},
}).getRecords();
// expected result
// S1, 6.0
// S2, 35.0
// S3, 10.0
assertEquals(3, actualCells.size());
// because of the way hashmap works, S2 goes first
// this part need to be refactored
assertEquals("S2", actualCells.get(0).lookup("state").getPayload());
assertEquals("S1", actualCells.get(1).lookup("state").getPayload());
assertEquals(new BigDecimal("39"), actualCells.get(0).lookup("sum(size)").getPayload());
assertEquals(new BigDecimal("6"), actualCells.get(1).lookup("sum(size)").getPayload());
assertEquals(new BigDecimal("10"), actualCells.get(2).lookup("sum(size)").getPayload());
}
/**
* with having clause
*
* @throws RecognitionException
*/
@Test
public void test5() throws RecognitionException {
String query = "select state, sum(size) from job group by state having sum(size) > 20";
List<ProcessDataRecord> actualCells = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L},
new Object[] {"S1", 2L},
new Object[] {"S1", 3l},
new Object[] {"S2", 4l},
new Object[] {"S2", 5l},
new Object[] {"S2", 6l},
new Object[] {"S2", 7l},
new Object[] {"S2", 8l},
new Object[] {"S2", 9l},
new Object[] {"S3", 10l},
}).getRecords();
// expected result
// S2, 35.0
assertEquals(1, actualCells.size());
assertEquals("S2", actualCells.get(0).lookup("state").getPayload());
assertEquals(new BigDecimal("39"), actualCells.get(0).lookup("sum(size)").getPayload());
}
/**
* with expressions
*
* @throws RecognitionException
*/
@Test
public void test6() throws RecognitionException {
String query = "select state, sum(size) / (count(size) * 2 + 1) from job group by state having (sum(size) + 5) / 2 > 10";
List<ProcessDataRecord> actualCells = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L},
new Object[] {"S1", 2L},
new Object[] {"S1", 3l},
new Object[] {"S2", 4l},
new Object[] {"S2", 5l},
new Object[] {"S2", 6l},
new Object[] {"S2", 7l},
new Object[] {"S2", 8l},
new Object[] {"S2", 9l},
new Object[] {"S3", 10l},
}).getRecords();
// expected result
// S2, 35.0
assertEquals(1, actualCells.size());
assertEquals("S2", actualCells.get(0).lookup("state").getPayload());
assertEquals(new BigDecimal("3"), actualCells.get(0).lookup("sum(size)/(count(size)*2+1)").getPayload());
}
/**
* duplicate column selection
*
* @throws RecognitionException
*/
// not supported any more after we use map in SelectProcessor
// @Test
// public void test7() throws RecognitionException {
// String query = "select state, state from job";
//
// List<ProcessDataRecord> actual = process(query, "job",
// new String[]{"state"},
// new String[]{"key1", "key2"},
// new Object[][] {
// new Object[] {"S1"},
// new Object[] {"S2"},
// }).getRecords();
//
// // expected result
// // state, state_1
// // S1, S1
// // S2, S2
// assertEquals(2, actual.size());
//
// assertEquals("S1", actual.get(0).lookup("state").getPayload());
// assertEquals("S2", actual.get(1).lookup("state").getPayload());
//
// assertEquals("state_1", actual.get(0).lookup("state_1").getTitle());
// }
/**
* aggregation functions only query returns empty result
*
* @throws RecognitionException
*/
@Test
public void test8() throws RecognitionException {
String query = "select count(__key__) from job where len(state) > 10";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1"},
new Object[] {"S2"},
new Object[] {"S3"},
new Object[] {"S4"},
new Object[] {"S5"},
new Object[] {"S6"},
new Object[] {"S7"},
new Object[] {"S8"},
new Object[] {"S9"},
new Object[] {"S0"},
}).getRecords();
// expected result
// null
assertNull(processedData.get(0).lookup("count(__key__)").getPayload());
}
/**
* nested aggregation functions
*
* SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;
*
* @throws RecognitionException
*/
// TODO : unsupported now
// @Test
public void test9() throws RecognitionException {
String query = "select sum(max(size)) from job group by state";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L},
new Object[] {"S1", 2L},
new Object[] {"S1", 3l},
new Object[] {"S2", 4l},
new Object[] {"S2", 5l},
new Object[] {"S2", 6l},
new Object[] {"S2", 7l},
new Object[] {"S2", 8l},
new Object[] {"S2", 9l},
new Object[] {"S3", 10l},
}).getRecords();
// expected result
// 0
assertEquals(new BigDecimal("22"), processedData.get(0).lookup("sum(max(size))"));
}
/**
* having without group by
*
* eg, select count(a) from c having count(a) > 10
*
* @throws RecognitionException
*/
@Test
public void test10() throws RecognitionException {
String query = "select count(state) from job having count(state) > 0";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1"},
new Object[] {"S2"},
new Object[] {"S3"},
new Object[] {"S4"},
new Object[] {"S5"},
new Object[] {"S6"},
new Object[] {"S7"},
new Object[] {"S8"},
new Object[] {"S9"},
new Object[] {"S0"},
}).getRecords();
// expected result
// 10
assertEquals(new BigDecimal("10"), processedData.get(0).lookup("count(state)").getPayload());
}
/**
* having clause with extra group by
*
* eg, select count(a) from c having count(a) > 10
*
* @throws RecognitionException
*/
@Test
public void test11() throws RecognitionException {
String query = "select count(size) from job group by state having sum(size) >= 10";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L},
new Object[] {"S1", 2L},
new Object[] {"S1", 3l},
new Object[] {"S2", 4l},
new Object[] {"S2", 5l},
new Object[] {"S2", 6l},
new Object[] {"S2", 7l},
new Object[] {"S2", 8l},
new Object[] {"S2", 9l},
new Object[] {"S3", 10l},
}).getRecords();
// expected result
// count(size),
// 39
// 10
assertEquals(2, processedData.size());
assertEquals(new BigDecimal("6"), processedData.get(0).lookup("count(size)").getPayload());
assertEquals(new BigDecimal("1"), processedData.get(1).lookup("count(size)").getPayload());
}
/**
* group query without aggregation function
*
* @throws RecognitionException
*/
@Test
public void test12() throws RecognitionException {
String query = "select state, size from job group by state, size";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
new Object[] {"S2", 1l}, //pair 3
new Object[] {"S2", 1l},
new Object[] {"S2", 2l}, //pair 4
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S3", 1l}, //pair 5
}).getRecords();
// expected result
// count(size),
// 39
// 10
assertEquals(5, processedData.size());
}
/**
* with extra having clause
*
* eg, select a from c group by a having a = 1
*
* @throws RecognitionException
*/
@Test
public void test13() throws RecognitionException {
String query = "select state, size from job group by state, size having size < 2";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
new Object[] {"S2", 1l}, //pair 3
new Object[] {"S2", 1l},
new Object[] {"S2", 2l}, //pair 4
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S3", 1l}, //pair 5
}).getRecords();
// expected result
// state, size
// S1, 1
// S2, 1
// S3, 1
assertEquals(3, processedData.size());
}
/**
* select a, count(b) from c group by a having sum(c) > 10
*
* @throws RecognitionException
*/
@Test
public void test14() throws RecognitionException {
String query = "select state, count(*) from job group by state having sum(size) > 20";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L},
new Object[] {"S1", 2L},
new Object[] {"S1", 3l},
new Object[] {"S2", 4l},
new Object[] {"S2", 5l},
new Object[] {"S2", 6l},
new Object[] {"S2", 7l},
new Object[] {"S2", 8l},
new Object[] {"S2", 9l},
new Object[] {"S3", 10l},
}).getRecords();
// expected result
// S2, 6,
assertEquals("S2", processedData.get(0).lookup("state").getPayload());
assertEquals(new BigDecimal("6"), processedData.get(0).lookup("count(*)").getPayload());
}
/**
* aggregation function with non-field selection
*
* select 'any string', count(state) from job
*
* @throws RecognitionException
*/
@Test
public void test15() throws RecognitionException {
String query = "select 'any string', count(state) from job";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
new Object[] {"S2", 1l}, //pair 3
new Object[] {"S2", 1l},
new Object[] {"S2", 2l}, //pair 4
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S3", 1l}, //pair 5
}).getRecords();
// expected result
// 'any string', count(size),
// 'any string', 10
assertEquals("any string", processedData.get(0).lookup("'anystring'").getPayload());
assertEquals(new BigDecimal("10"), processedData.get(0).lookup("count(state)").getPayload());
}
/**
* select all
*
* select * from job
*
* @throws RecognitionException
*/
@Test
public void test16() throws RecognitionException {
String query = "select * from job";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
}).getRecords();
// expected result
// key, S1, 1,
// key, S1, 1,
// key, S1, 2,
assertTrue(processedData.get(0).lookup("__key__").getPayload() instanceof Key);
assertEquals("S1", processedData.get(0).lookup("state").getPayload());
}
/**
* with other conditions
*
* @throws RecognitionException
*/
@Test
public void test17() throws RecognitionException {
String query = "select 'any string', * from job where state = 'S1'";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
new Object[] {"S2", 1l}, //pair 3
new Object[] {"S2", 1l},
new Object[] {"S2", 2l}, //pair 4
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S3", 1l}, //pair 5
}).getRecords();
// expected result
// 'any string', key, S1, 1,
// 'any string', key, S1, 1,
// 'any string', key, S1, 2,
assertEquals("any string", processedData.get(0).lookup("'anystring'").getPayload());
assertEquals("S1", processedData.get(0).lookup("state").getPayload());
}
/**
* with aggregation functions count
*
* @throws RecognitionException
*/
@Test
public void test18() throws RecognitionException {
String query = "select count(*) from job";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
new Object[] {"S2", 1l}, //pair 3
new Object[] {"S2", 1l},
new Object[] {"S2", 2l}, //pair 4
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S3", 1l}, //pair 5
}).getRecords();
// expected result
// 10
assertEquals(new BigDecimal("10"), processedData.get(0).lookup("count(*)").getPayload());
}
/**
* with aggregation functions sum
*
* @throws RecognitionException
*/
@Test
public void test19() throws RecognitionException {
String query = "select sum(*) from job";
List<ProcessDataRecord> processedData = process(query, "job",
new String[]{"state", "size"},
new String[]{"key1", "key2", "key3", "key4", "key5",
"key6", "key7", "key8", "key9", "key0"},
new Object[][] {
new Object[] {"S1", 1L}, //pair 1
new Object[] {"S1", 1L},
new Object[] {"S1", 2l}, //pair 2
new Object[] {"S2", 1l}, //pair 3
new Object[] {"S2", 1l},
new Object[] {"S2", 2l}, //pair 4
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S2", 2l},
new Object[] {"S3", 1l}, //pair 5
}).getRecords();
// expected result
// 0
// nothing will be summed here
assertEquals(new BigDecimal("0"), processedData.get(0).lookup("sum(*)").getPayload());
}
}