/*
* 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.jdbc.hana;
import org.junit.BeforeClass;
import org.junit.Test;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.jdbc.TranslationHelper;
import org.teiid.util.Version;
@SuppressWarnings("nls")
public class TestHanaTranslator {
private static HanaExecutionFactory TRANSLATOR;
@BeforeClass public static void setupOnce() throws Exception {
TRANSLATOR = new HanaExecutionFactory();
TRANSLATOR.setUseBindVariables(false);
TRANSLATOR.setDatabaseVersion(Version.DEFAULT_VERSION);
TRANSLATOR.start();
}
public String getTestVDB() {
return TranslationHelper.PARTS_VDB;
}
private String getTestBQTVDB() {
return TranslationHelper.BQT_VDB;
}
public void helpTestVisitor(String vdb, String input, String expectedOutput) throws TranslatorException {
TranslationHelper.helpTestVisitor(vdb, input, expectedOutput, TRANSLATOR);
}
@Test public void testConversion1() throws Exception {
String input = "SELECT char(convert(PART_WEIGHT, integer) + 100) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT to_nvarchar((cast(PARTS.PART_WEIGHT AS integer) + 100)) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion2() throws Exception {
String input = "SELECT convert(PART_WEIGHT, long) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS bigint) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion3() throws Exception {
String input = "SELECT convert(PART_WEIGHT, short) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS smallint) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion4() throws Exception {
String input = "SELECT convert(PART_WEIGHT, float) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS float) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion5() throws Exception {
String input = "SELECT convert(PART_WEIGHT, double) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS double) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion6() throws Exception {
String input = "SELECT convert(PART_WEIGHT, biginteger) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS bigint) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion7() throws Exception {
String input = "SELECT convert(PART_WEIGHT, bigdecimal) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS decimal) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion8() throws Exception {
String input = "SELECT convert(PART_WEIGHT, boolean) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(PARTS.PART_WEIGHT AS boolean) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion8a() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, boolean), long) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(cast(PARTS.PART_WEIGHT AS boolean) AS tinyint) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion9() throws Exception {
String input = "SELECT convert(PART_WEIGHT, date) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD') FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion10() throws Exception {
String input = "SELECT convert(PART_WEIGHT, time) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT to_time(PARTS.PART_WEIGHT, 'HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion11() throws Exception {
String input = "SELECT convert(PART_WEIGHT, timestamp) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion12() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, time), string) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_time(PARTS.PART_WEIGHT, 'HH24:MI:SS') AS nvarchar) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion13() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, timestamp), string) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS') AS nvarchar) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion14() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, date), string) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD') AS nvarchar) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion15() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, timestamp), date) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS') AS date) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion16() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, timestamp), time) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS') AS time) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion17() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, time), timestamp) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_time(PARTS.PART_WEIGHT, 'HH24:MI:SS') AS timestamp) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testConversion18() throws Exception {
String input = "SELECT convert(convert(PART_WEIGHT, date), timestamp) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD') AS timestamp) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testLog() throws Exception {
String input = "SELECT log(convert(PART_WEIGHT, double)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT log(10, cast(PARTS.PART_WEIGHT AS double)) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
input = "SELECT log10(convert(PART_WEIGHT, double)) FROM PARTS"; //$NON-NLS-1$
output = "SELECT log(10, cast(PARTS.PART_WEIGHT AS double)) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testLeft() throws Exception {
String input = "SELECT left(PART_WEIGHT, 2) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT left(PARTS.PART_WEIGHT, 2) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testRight() throws Exception {
String input = "SELECT right(PART_WEIGHT, 2) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT right(PARTS.PART_WEIGHT, 2) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testDayOfWeek() throws Exception {
String input = "SELECT dayofweek(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT (MOD((WEEKDAY(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'))+1),7)+1) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testDayOfMonth() throws Exception {
String input = "SELECT dayofmonth(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT dayofmonth(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testDayOfYear() throws Exception {
String input = "SELECT dayofyear(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT dayofyear(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testHour() throws Exception {
String input = "SELECT hour(convert(PART_WEIGHT, time)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT hour(to_time(PARTS.PART_WEIGHT, 'HH24:MI:SS')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testMinute() throws Exception {
String input = "SELECT minute(convert(PART_WEIGHT, time)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT minute(to_time(PARTS.PART_WEIGHT, 'HH24:MI:SS')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testMonth() throws Exception {
String input = "SELECT month(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT month(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testQuarter() throws Exception {
String input = "SELECT quarter(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT ((month(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'))+2)/3) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testSecond() throws Exception {
String input = "SELECT second(convert(PART_WEIGHT, time)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT second(to_time(PARTS.PART_WEIGHT, 'HH24:MI:SS')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testWeek() throws Exception {
String input = "SELECT week(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cast(substring(isoweek(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')), 7, 2) as integer) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testYear() throws Exception {
String input = "SELECT year(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT year(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testDayName() throws Exception {
String input = "SELECT dayname(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT initcap(lower(dayname(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')))) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testMonthName() throws Exception {
String input = "SELECT monthname(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT monthname(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testIfnull() throws Exception {
String input = "SELECT ifnull(PART_WEIGHT, 'otherString') FROM PARTS"; //$NON-NLS-1$
String output = "SELECT ifnull(PARTS.PART_WEIGHT, 'otherString') FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testSubstring1() throws Exception {
String input = "SELECT substring(PART_WEIGHT, 1) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT substring(PARTS.PART_WEIGHT, 1) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testSubstring2() throws Exception {
String input = "SELECT substring(PART_WEIGHT, 1, 5) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT substring(PARTS.PART_WEIGHT, 1, 5) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testRowLimit2() throws Exception {
String input = "select intkey from bqt1.smalla limit 100"; //$NON-NLS-1$
String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 100"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testRowLimit3() throws Exception {
String input = "select intkey from bqt1.smalla limit 50, 100"; //$NON-NLS-1$
String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 100 OFFSET 50"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testBitFunctions() throws Exception {
String input = "select bitand(intkey, intnum), bitnot(intkey), bitor(intnum, intkey), bitxor(intnum, intkey) from bqt1.smalla"; //$NON-NLS-1$
String output = "SELECT bitand(SmallA.IntKey, SmallA.IntNum), bitnot(SmallA.IntKey), bitor(SmallA.IntNum, SmallA.IntKey), bitxor(SmallA.IntNum, SmallA.IntKey) FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA</code>
*
* @throws Exception
*/
@Test public void testLocate() throws Exception {
String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT locate(substring('chimp', 1), cast(SmallA.IntNum AS nvarchar)) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA</code>
*
* @throws Exception
*/
@Test public void testLocate2() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT locate('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26</code>
*
* @throws Exception
*/
@Test public void testLocate3() throws Exception {
String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$
String output = "SELECT locate(substring('234567890', 1), cast(SmallA.IntNum AS nvarchar)) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA</code>
*
* @throws Exception
*/
@Test public void testLocate4() throws Exception {
String input = "SELECT locate('h', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT 2 FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA</code>
*
* @throws Exception
*/
@Test public void testLocate5() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT locate(substring('chimp', 1), SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
@Test public void testLocate5a() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', 2) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT locate(substring('chimp', 2), SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA</code>
*
* @throws Exception
*/
@Test public void testLocate6() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT locate(substring('chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END), SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/**
* Test the translator's ability to rewrite the LOCATE() function in a form
* suitable for the data source.
* <p>
* <code>SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA</code>
*
* @throws Exception
*/
@Test public void testLocate7() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT locate(substring('chimp', CASE WHEN (locate('chimp', SmallA.StringNum) + 1) < 1 THEN 1 ELSE (locate('chimp', SmallA.StringNum) + 1) END), SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
@Test public void testAggregate() throws Exception {
String input = "SELECT count(*), min(intnum), max(intnum) FROM BQT1.SMALLA"; //$NON-NLS-1$
String output = "SELECT COUNT(*), MIN(SmallA.IntNum), MAX(SmallA.IntNum) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
/*
* Geospatial tests
*/
@Test
public void testSTDistance() throws Exception {
String input = "select ST_Distance(shape, shape) from cola_markets"; //$NON-NLS-1$
String output = "SELECT COLA_MARKETS.SHAPE.st_distance(COLA_MARKETS.SHAPE) FROM COLA_MARKETS"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTDisjoint() throws Exception {
String input = "select ST_Disjoint(shape, shape) from cola_markets"; //$NON-NLS-1$
String output = "SELECT COLA_MARKETS.SHAPE.st_disjoint(COLA_MARKETS.SHAPE) FROM COLA_MARKETS"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTIntersects() throws Exception {
String input = "select ST_Intersects(shape, shape) from cola_markets"; //$NON-NLS-1$
String output = "SELECT COLA_MARKETS.SHAPE.st_intersects(COLA_MARKETS.SHAPE) FROM COLA_MARKETS"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTInsert() throws Exception {
String input = "insert into cola_markets(name,shape) values('foo124', ST_GeomFromText('POINT (300 100)', 8307))"; //$NON-NLS-1$
String output = "INSERT INTO COLA_MARKETS (NAME, SHAPE) VALUES ('foo124', st_geomfromwkb(?, 8307))"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTInsertNull() throws Exception {
String input = "insert into cola_markets(name,shape) values('foo124', null)"; //$NON-NLS-1$
String output = "INSERT INTO COLA_MARKETS (NAME, SHAPE) VALUES ('foo124', ?)"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTSrid() throws Exception {
String input = "select st_srid(shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_srid() FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTAsBinary() throws Exception {
String input = "select st_asbinary(shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_asbinary() FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTAsWKT() throws Exception {
String input = "select st_asewkt(shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_asewkt() FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTAsText() throws Exception {
String input = "select st_astext(shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_astext() FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTAsGeoJSON() throws Exception {
String input = "select st_asgeojson(shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_asgeojson() FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTContains() throws Exception {
String input = "select st_contains(shape, shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_contains(c.SHAPE) FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTCrosses() throws Exception {
String input = "select st_crosses(shape, shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_crosses(c.SHAPE) FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTEquals() throws Exception {
String input = "select st_equals(shape, shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_equals(c.SHAPE) FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test
public void testSTTouches() throws Exception {
String input = "select st_touches(shape, shape) from cola_markets c"; //$NON-NLS-1$
String output = "SELECT c.SHAPE.st_touches(c.SHAPE) FROM COLA_MARKETS AS c"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test public void testVarbinaryComparison() throws Exception {
String input = "select bin_col from binary_test where bin_col = x'ab'"; //$NON-NLS-1$
String output = "SELECT binary_test.BIN_COL FROM binary_test WHERE binary_test.BIN_COL = to_binary('AB')"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test public void testVarbinaryInsert() throws Exception {
String input = "insert into binary_test (bin_col) values (x'bc')"; //$NON-NLS-1$
String output = "INSERT INTO binary_test (BIN_COL) VALUES (to_binary('BC'))"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
/*
* Pushdown function tests
*/
@Test public void testPushDownAddDays() throws Exception {
String input = "SELECT add_days(convert(PART_WEIGHT, date), 30) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT add_days(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), 30) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownAddMonths() throws Exception {
String input = "SELECT add_months(convert(PART_WEIGHT, date), 10) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT add_months(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), 10) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownAddSeconds() throws Exception {
String input = "SELECT add_seconds(convert(PART_WEIGHT, timestamp), 10) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT add_seconds(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS'), 10) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownAddYears() throws Exception {
String input = "SELECT add_years(convert(PART_WEIGHT, date), 10) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT add_years(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), 10) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownCurrentUTCTime() throws Exception {
String input = "SELECT current_utctime()"; //$NON-NLS-1$
String output = "SELECT current_utctime()"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownCurrentUTCTimeStamp() throws Exception {
String input = "SELECT current_utctimestamp()"; //$NON-NLS-1$
String output = "SELECT current_utctimestamp()"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownDaysBetween() throws Exception {
String input = "SELECT days_between(convert(PART_WEIGHT, date), convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT days_between(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownIsoWeek() throws Exception {
String input = "SELECT isoweek(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT isoweek(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownLastDay() throws Exception {
String input = "SELECT last_day(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT last_day(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownLocalToUtc() throws Exception {
String input = "SELECT localtoutc(convert(PART_WEIGHT, timestamp), 'EST') FROM PARTS"; //$NON-NLS-1$
String output = "SELECT localtoutc(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS'), 'EST') FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownNano100Between() throws Exception {
String input = "SELECT nano100_between(convert(PART_WEIGHT, date), convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT nano100_between(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownNextDay() throws Exception {
String input = "SELECT next_day(convert(PART_WEIGHT, date)) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT next_day(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD')) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownCosh() throws Exception {
String input = "SELECT cosh(0.5) FROM PARTS"; //$NON-NLS-1$
String output = "SELECT cosh(0.5) FROM PARTS"; //$NON-NLS-1$
helpTestVisitor(getTestVDB(),
input,
output);
}
@Test public void testPushDownBitset() throws Exception {
String input = "SELECT bitset(convert(bin_col, varbinary), 1, 3) FROM binary_test"; //$NON-NLS-1$
String output = "SELECT bitset(binary_test.BIN_COL, 1, 3) FROM binary_test"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testMaxBoolean() throws Exception {
String input = "SELECT max(booleanvalue) FROM bqt1.smalla"; //$NON-NLS-1$
String output = "SELECT cast(MAX(to_tinyint(SmallA.BooleanValue)) as boolean) FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testCountBoolean() throws Exception {
String input = "SELECT count(booleanvalue) FROM bqt1.smalla"; //$NON-NLS-1$
String output = "SELECT COUNT(SmallA.BooleanValue) FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testBooleanCastString() throws Exception {
String input = "SELECT cast(booleanvalue as string) FROM bqt1.smalla"; //$NON-NLS-1$
String output = "SELECT CASE WHEN SmallA.BooleanValue = true THEN 'true' WHEN SmallA.BooleanValue IS NOT NULL THEN 'false' END FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testBooleanExpression() throws Exception {
String input = "SELECT (CASE WHEN BooleanValue THEN 'a' ELSE 'b' END) FROM BQT1.smalla"; //$NON-NLS-1$
String output = "SELECT CASE WHEN SmallA.BooleanValue = true THEN 'a' ELSE 'b' END FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
@Test public void testObjectCast() throws Exception {
String input = "SELECT cast(intkey as object) FROM BQT1.smalla"; //$NON-NLS-1$
String output = "SELECT SmallA.IntKey FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestBQTVDB(),
input,
output);
}
}