/**
* Sort on table
*
* @throws Exception
*/
@Test
public void testFilterOnTable() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1", "testColumn2", "AMOUNT1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.COUNTRY");
se[1] = new ScriptExpression("dataSetRow.CITY");
se[2] = new ScriptExpression("dataSetRow.AMOUNT");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
ScriptExpression filterExpr = new ScriptExpression("row.AMOUNT1>100");
FilterDefinition filterDefn = new FilterDefinition(filterExpr);
queryDefn.addFilter(filterDefn);
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
}
/** @throws Exception */
private void testInvalidFilter(int filterIndex) throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"rownum1", "rownum2", "rownum3"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("row.__rownum");
se[1] = new ScriptExpression("row.rownum1");
se[2] = new ScriptExpression("row[\"rownum2\"]");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
FilterDefinition[] filter = new FilterDefinition[name.length + 1];
filter[0] = new FilterDefinition(new ScriptExpression("row.rownum1 == 1"));
filter[1] = new FilterDefinition(new ScriptExpression("row.rownum2 == 1"));
filter[2] = new FilterDefinition(new ScriptExpression("row.rownum3 == 1"));
filter[3] = new FilterDefinition(new ScriptExpression("row.__rownum == 1"));
queryDefn.addFilter(filter[filterIndex]);
try {
executeQuery(queryDefn);
fail("Should not arrive here");
} catch (DataException e) {
}
}
/** @throws Exception */
private void genSerializable() throws Exception {
Context context = Context.enter();
Scriptable scope = context.initStandardObjects();
Context.exit();
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"serializable"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("new java.lang.StringBuffer(\"ss\")");
se[0].setDataType(DataType.JAVA_OBJECT_TYPE);
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
// generation
IQueryResults qr = myGenDataEngine.prepare(queryDefn).execute(scope);
// important step
queryResultID = qr.getID();
IResultIterator ri = qr.getResultIterator();
assertEquals(DataType.JAVA_OBJECT_TYPE, ri.getResultMetaData().getColumnType(1));
while (ri.next()) {
assertTrue(ri.getValue("serializable") instanceof StringBuffer);
assertEquals("ss", ri.getValue("serializable").toString());
}
ri.close();
qr.close();
myGenDataEngine.shutdown();
}
/** @throws Exception */
@Test
public void testSpecialExpression3() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"AMOUNT", "testColumn1", "testColumn2"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.AMOUNT");
se[1] =
new ScriptExpression(
"var p=dataSetRow.AMOUNT+1;if( p >200 ){\"A large amount!\"; } else{ \"A small amount!\";}");
se[2] = new ScriptExpression("row[\"testColumn1\"]+dataSetRow.AMOUNT");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
checkOutputFile();
}
/** @throws Exception */
@Test
public void testSpecialExpression2() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"AMOUNT", "testColumn1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.AMOUNT");
se[1] =
new ScriptExpression(
"if ( row.AMOUNT >200 ){ Total.runningSum(row.AMOUNT); }else{ row.AMOUNT;}");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
checkOutputFile();
}
/**
* Filtering on data set, without total
*
* @throws Exception
*/
@Test
public void testFilterOnDateType() throws Exception {
FilterDefinition filterDefn =
new FilterDefinition(
new ConditionalExpression(
"row.SALE_DATE",
ConditionalExpression.OP_BETWEEN,
"\'2004-05-01 00:00:00\'",
"\'2004-06-05 00:00:00\'"));
this.dataSet.addFilter(filterDefn);
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1", "testColumn2", "SALE_DATE"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.COUNTRY");
se[1] = new ScriptExpression("dataSetRow.CITY");
se[2] = new ScriptExpression("dataSetRow.SALE_DATE");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
checkOutputFile();
}
/**
* Computed column on data set, without total
*
* @throws Exception
*/
@Test
public void testComputedOnDataSet() throws Exception {
IComputedColumn cc = new ComputedColumn("AMOUNT2", "row.AMOUNT*2");
this.dataSet.addComputedColumn(cc);
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1", "testColumn2", "AMOUNT1", "AMOUNT2"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.COUNTRY");
se[1] = new ScriptExpression("dataSetRow.CITY");
se[2] = new ScriptExpression("dataSetRow.AMOUNT");
se[3] = new ScriptExpression("dataSetRow.AMOUNT2");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
checkOutputFile();
}
/** @throws Exception */
@Test
public void testBindingNameWithDoubleQuote() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"test\"Column1", "test\"Column2", "AMOUNT1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.COUNTRY");
se[1] = new ScriptExpression("row[\"test\\\"Column1\"]");
se[2] = new ScriptExpression("dataSetRow.AMOUNT");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
checkOutputFile();
}
/** @throws Exception */
private void testInvalidSort(int sortIndex) throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"rownum1", "rownum2", "rownum3"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("row.__rownum");
se[1] = new ScriptExpression("row.rownum1");
se[2] = new ScriptExpression("row[\"rownum2\"]");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
SortDefinition[] sort = new SortDefinition[name.length + 1];
sort[0] = new SortDefinition();
sort[0].setExpression("row.rownum1");
sort[1] = new SortDefinition();
sort[1].setExpression("row.rownum2");
sort[2] = new SortDefinition();
sort[2].setExpression("row.rownum3");
sort[3] = new SortDefinition();
sort[3].setExpression("row.__rownum");
queryDefn.addSort(sort[sortIndex]);
try {
executeQuery(queryDefn);
fail("Should not arrive here");
} catch (DataException e) {
}
}
/** @throws Exception */
@Test
public void testSpecialExpression() throws Exception {
IComputedColumn cc = new ComputedColumn("AMOUNT2", "row.AMOUNT*2");
this.dataSet.addComputedColumn(cc);
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("if ( 2<1 ){ true; }else{ false;}");
SortDefinition[] sortDefn = new SortDefinition[] {new SortDefinition()};
sortDefn[0].setExpression("row.testColumn1");
sortDefn[0].setSortDirection(ISortDefinition.SORT_DESC);
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
for (int i = 0; i < sortDefn.length; i++) {
queryDefn.addSort(sortDefn[i]);
}
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
checkOutputFile();
}
/**
* @param name
* @param dataType
* @param se
* @throws BirtException
*/
private void basicTestNoDataSet(String[] name, int[] dataType, ScriptExpression[] se)
throws BirtException {
DataEngine dataEngine =
new DataEngineImpl(
DataEngineContext.newInstance(DataEngineContext.DIRECT_PRESENTATION, null, null, null));
QueryDefinition queryDefn = new QueryDefinition();
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = dataEngine.prepare(queryDefn).execute(null).getResultIterator();
if (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
Object value = ri.getValue(name[i]);
str += value;
if (i < name.length - 1) str += ", ";
if (dataType[i] == DataType.INTEGER_TYPE)
assertTrue(value.getClass().equals(Integer.class));
else if (dataType[i] == DataType.DOUBLE_TYPE)
assertTrue(value.getClass().equals(Double.class));
else if (dataType[i] == DataType.DATE_TYPE) assertTrue(value.getClass().equals(Date.class));
}
testPrintln(str);
}
}
/** @throws Exception */
@Test
public void testNoDataSetWithSubQuery() throws Exception {
// outer query without data set
int[] dataType = new int[] {DataType.DATE_TYPE};
String[] name = new String[] {"testColumn1"};
IResultIterator ri2 = null;
{
ScriptExpression[] se =
new ScriptExpression[] {
new ScriptExpression("new Date()", dataType[0]),
};
QueryDefinition queryDefn = new QueryDefinition();
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
// sub query
String subQueryName = "TEST";
SubqueryDefinition subQueryDefn = new SubqueryDefinition(subQueryName, queryDefn);
for (int i = 0; i < name.length; i++)
subQueryDefn.addBinding(
new Binding(name[i], new ScriptExpression("row._outer." + name[i], dataType[i])));
queryDefn.addSubquery(subQueryDefn);
DataEngine myDataEngine =
new DataEngineImpl(
DataEngineContext.newInstance(
DataEngineContext.DIRECT_PRESENTATION, null, null, null));
IResultIterator ri = myDataEngine.prepare(queryDefn).execute(null).getResultIterator();
ri.next();
ri2 = ri.getSecondaryIterator(subQueryName, null);
}
if (ri2.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
Object value = ri2.getValue(name[i]);
str += value;
if (i < name.length - 1) str += ", ";
if (dataType[0] == DataType.DATE_TYPE) assertTrue(value.getClass().equals(Date.class));
}
testPrintln(str);
}
ri2.close();
}
/**
* Test the consistency of Data Type Info.
*
* @throws Exception
*/
@Test
public void testBasic1() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"AMOUNT1", "AMOUNT2"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.AMOUNT");
se[0].setDataType(DataType.STRING_TYPE);
se[1] = new ScriptExpression("row.AMOUNT1");
se[1].setDataType(DataType.UNKNOWN_TYPE);
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
assertTrue(ri.getValue("AMOUNT2") instanceof String);
}
}
/** @throws Exception */
@Test
public void testNoDataSetWithNestedQuery() throws Exception {
// outer query without data set
String[] name = new String[] {"testColumn1"};
IQueryResults queryResult = null;
{
int[] dataType = new int[] {DataType.DATE_TYPE};
ScriptExpression[] se =
new ScriptExpression[] {
new ScriptExpression("new Date()", dataType[0]),
};
DataEngine myDataEngine =
new DataEngineImpl(
DataEngineContext.newInstance(
DataEngineContext.DIRECT_PRESENTATION, null, null, null));
QueryDefinition queryDefn = new QueryDefinition();
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = myDataEngine.prepare(queryDefn).execute(null).getResultIterator();
queryResult = ri.getQueryResults();
}
// inner query with data set
QueryDefinition queryDefn2 = this.newReportQuery();
for (int i = 0; i < name.length; i++)
queryDefn2.addBinding(new Binding(name[i], new ScriptExpression("row._outer." + name[i])));
IResultIterator ri2 =
this.dataEngine.prepare(queryDefn2).execute(queryResult, null).getResultIterator();
if (ri2.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
Object value = ri2.getValue(name[i]);
str += value;
if (i < name.length - 1) str += ", ";
}
testPrintln(str);
}
ri2.close();
}
/** @throws Exception */
private void genDummy2() throws Exception {
expectedValue = new ArrayList();
Context context = Context.enter();
Scriptable scope = context.initStandardObjects();
Context.exit();
// ------------generation----------------
QueryDefinition qd = new QueryDefinition();
// prepare
IBaseExpression[] rowBeArray = getDummyRowExpr();
prepareExprNameAndQuery(rowBeArray, null, qd);
// prepare sub query
SubqueryDefinition subQueryDefn = new SubqueryDefinition(subName, qd);
qd.addSubquery(subQueryDefn);
IBaseExpression[] rowBeArray2 = getDummyRowExpr();
prepareExprNameAndQuery(rowBeArray2, null, subQueryDefn);
// generation
IQueryResults qr = myGenDataEngine.prepare(qd).execute(scope);
// important step
queryResultID = qr.getID();
IResultIterator ri = qr.getResultIterator();
while (ri.next()) {
for (int i = 0; i < rowBeArray.length; i++)
expectedValue.add(ri.getValue(this.rowExprName[i]));
IResultIterator ri2 = ri.getSecondaryIterator(subName, scope);
while (ri2.next()) {
for (int i = 0; i < rowBeArray2.length; i++)
expectedValue.add(ri2.getValue(this.rowExprName[i]));
}
ri2.close();
}
ri.close();
qr.close();
myGenDataEngine.shutdown();
}
private IResultMetaData getRuntimeMetaData(DataSetHandle dataSetHandle) throws BirtException {
Map dataSetBindingMap = new HashMap();
Map dataSourceBindingMap = new HashMap();
DataSetHandle handle = null;
if (sessionContext.getModuleHandle() != null
&& sessionContext.getModuleHandle().getAllDataSets() != null) {
for (Object o : sessionContext.getModuleHandle().getAllDataSets()) {
DataSetHandle dsh = (DataSetHandle) o;
if (dsh.getQualifiedName().equals(dataSetHandle.getQualifiedName())) {
handle = dsh;
break;
}
}
}
// First clear all property bindings so that the data set can be executed against design time
// properties
clearPropertyBindingMap(handle, dataSetBindingMap, dataSourceBindingMap);
try {
QueryDefinition query = new QueryDefinition();
query.setDataSetName(dataSetHandle.getQualifiedName());
query.setMaxRows(1);
query.setAutoBinding(true);
IResultMetaData metaData =
new QueryExecutionHelper(dataEngine, modelAdaptor, sessionContext, false, this.session)
.executeQuery(query)
.getResultMetaData();
addResultSetColumn(dataSetHandle, metaData);
if (MetaDataPopulator.needsUseResultHint(dataSetHandle, metaData)) {
metaData =
new QueryExecutionHelper(dataEngine, modelAdaptor, sessionContext, true, this.session)
.executeQuery(query)
.getResultMetaData();
}
return metaData;
} finally {
// restore property bindings
resetPropertyBinding(handle, dataSetBindingMap, dataSourceBindingMap);
}
}
/** @throws Exception */
@Test
public void testInvalidBinding() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.COUNTRY");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
if (ri.next()) {
try {
ri.getValue(name[0]);
} catch (BirtException e) {
assertTrue(e.getErrorCode() == ResourceConstants.INVALID_JS_EXPR);
}
}
ri.close();
}
/**
* Test Java Object data type
*
* @throws Exception
*/
@Test
public void testObjectTypeBasic() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name =
new String[] {
"ObjectType",
};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("new java.lang.StringBuffer(\"ss\")");
se[0].setDataType(DataType.JAVA_OBJECT_TYPE);
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
IResultIterator ri = executeQuery(queryDefn);
assertEquals(DataType.JAVA_OBJECT_TYPE, ri.getResultMetaData().getColumnType(1));
while (ri.next()) {
assertTrue(ri.getValue("ObjectType") instanceof StringBuffer);
assertEquals("ss", ri.getValue("ObjectType").toString());
}
}
/** @throws Exception */
@Test
public void testBlankExpression() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression(null);
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
DataException error = null;
try {
executeQuery(queryDefn);
fail("Should not arrive here");
} catch (DataException e) {
// assertTrue( e.getErrorCode( ) == ResourceConstants.EXPRESSION_CANNOT_BE_NULL_OR_BLANK );
error = e;
}
assertNotNull(error);
}
/** @throws Exception */
private void genUnserializable() throws Exception {
Context context = Context.enter();
Scriptable scope = context.initStandardObjects();
Context.exit();
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"unserializable"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("new java.lang.ThreadGroup(\"ss\")");
se[0].setDataType(DataType.JAVA_OBJECT_TYPE);
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
// generation
IQueryResults qr = myGenDataEngine.prepare(queryDefn).execute(scope);
// important step
queryResultID = qr.getID();
try {
IResultIterator ri = qr.getResultIterator();
assertEquals(DataType.JAVA_OBJECT_TYPE, ri.getResultMetaData().getColumnType(1));
while (ri.next()) {
assertTrue(ri.getValue("unserializable") instanceof ThreadGroup);
}
ri.close();
assertTrue(false);
} catch (Exception e) {
// Currently, unserializable objects can't be saved in report doc
e.printStackTrace();
} finally {
qr.close();
myGenDataEngine.shutdown();
}
}
/** @throws Exception */
@Test
public void testAccessGroupColumn() throws Exception {
QueryDefinition queryDefn = newReportQuery();
// column mapping
String[] name = new String[] {"testColumn1", "testColumn2", "AMOUNT1"};
ScriptExpression[] se = new ScriptExpression[name.length];
se[0] = new ScriptExpression("dataSetRow.COUNTRY");
se[1] = new ScriptExpression("dataSetRow.CITY");
se[2] = new ScriptExpression("dataSetRow.AMOUNT");
for (int i = 0; i < name.length; i++) queryDefn.addBinding(new Binding(name[i], se[i]));
GroupDefinition groupDefn = new GroupDefinition("group1");
groupDefn.setKeyColumn("testColumn1");
String name2 = "testColumn3";
ScriptExpression se2 = new ScriptExpression("Total.sum(dataSetRow.AMOUNT)");
se2.setGroupName("group1");
// groupDefn.addResultSetExpression( name2, se2 );
queryDefn.addBinding(new Binding(name2, se2));
queryDefn.addGroup(groupDefn);
IResultIterator ri = executeQuery(queryDefn);
while (ri.next()) {
String str = "";
for (int i = 0; i < name.length; i++) {
str += ri.getValue(name[i]);
str += ", ";
}
str += ri.getValue(name2);
testPrintln(str);
}
checkOutputFile();
}
public static void addParameterSortBy(
QueryDefinition queryDefn, AbstractScalarParameterHandle parameter, IModelAdapter adapter) {
String sortBy = parameter.getSortByColumn();
if (sortBy != null) {
String sortDirection = parameter.getSortDirection();
if (sortDirection != null) {
org.eclipse.birt.report.model.api.Expression mexpr =
(org.eclipse.birt.report.model.api.Expression)
parameter
.getExpressionProperty(AbstractScalarParameter.SORT_BY_COLUMN_PROP)
.getValue();
ScriptExpression dexpr = adapter.adaptExpression(mexpr);
SortDefinition sort = new SortDefinition();
sort.setExpression(dexpr);
boolean direction =
DesignChoiceConstants.SORT_DIRECTION_ASC.equalsIgnoreCase(sortDirection);
sort.setSortDirection(direction ? ISortDefinition.SORT_ASC : ISortDefinition.SORT_DESC);
queryDefn.addSort(sort);
}
}
}
public static void addBinding(
QueryDefinition queryDefinition, String columnName, ScriptExpression expr)
throws DataException {
IBinding binding = new Binding(columnName, expr);
queryDefinition.addBinding(binding);
}
