/**
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.apache.metamodel.dialects;
import java.util.Arrays;
import junit.framework.TestCase;
import org.apache.metamodel.jdbc.dialects.DB2QueryRewriter;
import org.apache.metamodel.query.FunctionType;
import org.apache.metamodel.query.OperatorType;
import org.apache.metamodel.query.Query;
import org.apache.metamodel.schema.ColumnType;
import org.apache.metamodel.schema.MutableColumn;
import org.apache.metamodel.schema.MutableSchema;
import org.apache.metamodel.schema.MutableTable;
import org.apache.metamodel.schema.TableType;
public class DB2QueryRewriterTest extends TestCase {
private MutableSchema schema;
private MutableTable table;
private MutableColumn col;
@Override
protected void setUp() throws Exception {
super.setUp();
schema = new MutableSchema("sch");
table = new MutableTable("foo").setSchema(schema);
schema.addTable(table);
col = new MutableColumn("bar").setTable(table);
table.addColumn(col);
}
public void testRewriteMaxRowsNoFirstRow() throws Exception {
Query q = new Query().from(table).select(col).setMaxRows(400);
String str = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.foo.bar FROM sch.foo FETCH FIRST 400 ROWS ONLY", str);
}
public void testRewriteMaxRowsFirstRowIsOne() throws Exception {
Query q = new Query().from(table).select(col).setMaxRows(200).setFirstRow(1);
String str = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.foo.bar FROM sch.foo FETCH FIRST 200 ROWS ONLY", str);
}
public void testRewriteFirstRow() throws Exception {
Query q = new Query().from(table).select(col).setFirstRow(401);
String str = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals(
"SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER() AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number > 400",
str);
}
public void testRewriteFirstRowAndMaxRows() throws Exception {
Query q = new Query().from(table).select(col).setFirstRow(401).setMaxRows(400);
String str = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals(
"SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER() AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number BETWEEN 401 AND 800",
str);
}
public void testRewriteColumnType() throws Exception {
assertEquals("SMALLINT", new DB2QueryRewriter(null).rewriteColumnType(ColumnType.BOOLEAN));
assertEquals("VARCHAR", new DB2QueryRewriter(null).rewriteColumnType(ColumnType.VARCHAR));
}
public void testRewriteSelectItems() throws Exception {
Query q = new Query().from(table).select(col).where(col, OperatorType.EQUALS_TO, "foob");
String queryString = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.foo.bar FROM sch.foo WHERE sch.foo.bar = 'foob'", queryString);
}
public void testEscapeFilterItemQuotes() throws Exception {
Query q = new Query().from(table).select(col).where(col, OperatorType.EQUALS_TO, "foo'bar");
String queryString = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.foo.bar FROM sch.foo WHERE sch.foo.bar = 'foo\\'bar'", queryString);
q = new Query().from(table).select(col)
.where(col, OperatorType.IN, Arrays.asList("foo'bar", "foo", "bar", "eobject's"));
queryString = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.foo.bar FROM sch.foo WHERE sch.foo.bar IN ('foo\\'bar' , 'foo' , 'bar' , 'eobject\\'s')",
queryString);
}
public void testFullyQualifiedColumnNames() throws Exception {
final MutableSchema schema = new MutableSchema("sch");
final MutableTable table = new MutableTable("tab", TableType.TABLE, schema);
final MutableColumn nameColumn = new MutableColumn("name", ColumnType.VARCHAR).setTable(table);
final MutableColumn ageColumn = new MutableColumn("age", ColumnType.INTEGER).setTable(table);
schema.addTable(table);
table.addColumn(nameColumn);
table.addColumn(ageColumn);
final Query q = new Query();
q.select(ageColumn).selectCount();
q.from(table);
q.where(ageColumn, OperatorType.GREATER_THAN, 18);
q.groupBy(ageColumn);
q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
q.orderBy(ageColumn);
final String sql = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > 18 "
+ "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);
}
public void testFullyQualifiedColumnNamesWithFilterItemContainingTimestamp() throws Exception {
final MutableSchema schema = new MutableSchema("sch");
final MutableTable table = new MutableTable("tab", TableType.TABLE, schema);
final MutableColumn nameColumn = new MutableColumn("name", ColumnType.VARCHAR).setTable(table);
final MutableColumn dateColumn = new MutableColumn("age", ColumnType.TIMESTAMP).setTable(table);
schema.addTable(table);
table.addColumn(nameColumn);
table.addColumn(dateColumn);
final Query q = new Query();
q.select(dateColumn).selectCount();
q.from(table);
q.where(dateColumn, OperatorType.GREATER_THAN, "2012-10-31 08:09:54");
q.groupBy(dateColumn);
q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
q.orderBy(dateColumn);
final String sql = new DB2QueryRewriter(null).rewriteQuery(q);
assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > TIMESTAMP ('2012-10-31 08:09:54') "
+ "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);
}
}