/*
* 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.netezza;
import org.junit.BeforeClass;
import org.junit.Test;
import org.teiid.language.LanguageFactory;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.jdbc.TranslationHelper;
/**
*/
@SuppressWarnings("nls")
public class TestNetezzaTranslatorSourceSystemFunctions {
private static NetezzaExecutionFactory TRANSLATOR;
private static final LanguageFactory LANG_FACTORY = new LanguageFactory();
@BeforeClass public static void oneTimeSetup() throws TranslatorException {
TRANSLATOR = new NetezzaExecutionFactory();
TRANSLATOR.setUseBindVariables(false);
TRANSLATOR.start();
}
/////////////////UTILLITY FUNCTIONS/////////
////////////////////////////////////////////
private String getTestBQTVDB() {
return TranslationHelper.BQT_VDB;
}
/////SOURCESYSTEM FUNCTION TESTCASES//////////////
///////////////////////////////////////////////
//////////////////BEGIN---STRING FUNCTIONS TESTCASES///////////////////
@Test
public void testLcaseUcase() throws Exception {
String input = "select lcase(StringKey), ucase(StringKey) FROM BQT1.SmallA";
String output = "SELECT lower(SmallA.StringKey), upper(SmallA.StringKey) FROM SmallA";
TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output,TRANSLATOR);
}
@Test public void testPad() throws Exception {
String input = "select lpad(smalla.stringkey, 18), rpad(smalla.stringkey, 12) from bqt1.smalla"; //$NON-NLS-1$
String output = "SELECT lpad(SmallA.StringKey, 18), rpad(SmallA.StringKey, 12) FROM SmallA"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input,
output, TRANSLATOR);
}
@Test
public void testIFNull() throws Exception {
String input = "SELECT ifnull(StringKey, 'otherString') FROM BQT1.SmallA";
String output = "SELECT NVL(SmallA.StringKey, 'otherString') FROM SmallA";
//SELECT IFNULL(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT
//SELECT nvl(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT
TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR);
}
@Test public void testSubstring1() throws Exception {
//////////BOTH SUBSTRING AND SUBSTR work in NETEZZA//
//////////////////////////////////////////////////////
String input = "SELECT substring(StringKey, 1) FROM BQT1.SmallA";
String output = "SELECT substring(SmallA.StringKey, 1) FROM SmallA";
//SELECT substring(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT
//SELECT substr(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT
TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR);
}
@Test public void testSubstring2() throws Exception {
//////////BOTH SUBSTRING AND SUBSTR work in NETEZZA//
//////////////////////////////////////////////////////
String input = "SELECT substring(StringKey, 1, 5) FROM BQT1.SmallA";
String output = "SELECT substring(SmallA.StringKey, 1, 5) FROM SmallA";
TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR);
}
@Test public void testConcat_withLiteral() throws Exception {
// String sql = "select stringnum || '1' from BQT1.Smalla";
// String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA";
// helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected);
String input = "select stringnum || '1' from BQT1.Smalla";
String output = "SELECT (SmallA.StringNum || '1') FROM SmallA";
TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR);
}
////BEGIN-LOCATE FUNCTION
@Test public void testLocate() throws Exception {
String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA";
String output = "SELECT INSTR('chimp', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
@Test public void testLocate2() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA";
String output = "SELECT INSTR('chimp', SmallA.StringNum) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input, output,
TRANSLATOR);
}
@Test public void testLocate3() throws Exception {
String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26";
String output = "SELECT INSTR('234567890', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA WHERE SmallA.IntKey = 26";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test public void testLocate4() throws Exception {
String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA";
String output = "SELECT 1 FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test public void testLocate5() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA";
String output = "SELECT INSTR('chimp', SmallA.StringNum, 1) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test public void testLocate6() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA";
String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
@Test public void testLocate7() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA";
String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN (INSTR('chimp', SmallA.StringNum) + 1) < 1 THEN 1 ELSE (INSTR('chimp', SmallA.StringNum) + 1) END) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
}
////END-LOCATE FUNCTION
//////////////////BEGIN---NUMERIC FUNCTIONS TESTCASES///////////////////
@Test public void testCeil() throws Exception {
//select ceiling(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT
//select ceil(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT
String input = "SELECT ceiling(sqrt(INTKEY)) FROM BQT1.SMALLA";
String output = "SELECT ceil(sqrt(SmallA.IntKey)) FROM SmallA";
TranslationHelper.helpTestVisitor(getTestBQTVDB(),
input,
output, TRANSLATOR);
}
@Test public void testPower() throws Exception {
//select power(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT
//select pow(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT
String input = "SELECT power(INTKEY, 2) FROM BQT1.SMALLA";
String output = "SELECT pow(SmallA.IntKey, 2) FROM SmallA";
TranslationHelper.helpTestVisitor(getTestBQTVDB(),
input,
output, TRANSLATOR);
}
//////////////////END---NUMERIC FUNCTIONS TESTCASES///////////////////
//////////////////BEGIN---BIT FUNCTIONS CONVERSION TESTCASES///////////////////
///////////////////////////////////////////////////////////////////
@Test public void testBitAnd() throws Exception {
String input = "select bitand(intkey, intnum) from bqt1.smalla";
String output = "SELECT int4and(SmallA.IntKey, SmallA.IntNum) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input,
output, TRANSLATOR);
}
@Test public void testBitNot() throws Exception {
String input = "select bitnot(intkey) from bqt1.smalla";
String output = "SELECT int4not(SmallA.IntKey) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input,
output, TRANSLATOR);
}
@Test public void testBitOr() throws Exception {
String input = "select bitor(intkey, intnum) from bqt1.smalla";
String output = "SELECT int4or(SmallA.IntKey, SmallA.IntNum) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input,
output, TRANSLATOR);
}
@Test public void testBitXor() throws Exception {
String input = "select bitxor(intkey, intnum) from bqt1.smalla";
String output = "SELECT int4xor(SmallA.IntKey, SmallA.IntNum) FROM SmallA";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB,
input,
output, TRANSLATOR);
}
//////////////////END---BIT FUNCTIONS CONVERSION TESTCASES///////////////////
/////////////////////////////////////////////////////////////////////////////
}