Author: loleary Date: 2009-08-10 16:36:00 -0400 (Mon, 10 Aug 2009) New Revision: 1228 Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java Log: Converted to use JUnit4 framework Added test methods from R050504c branch Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-10 18:26:58 UTC (rev 1227) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-10 20:36:00 UTC (rev 1228) @@ -24,8 +24,9 @@ import java.util.Properties; -import junit.framework.TestCase; - +import org.junit.BeforeClass; +import org.junit.Ignore; +import org.junit.Test; import org.teiid.connector.api.ConnectorException; import org.teiid.connector.jdbc.MetadataFactory; import org.teiid.connector.jdbc.translator.TranslatedCommand; @@ -35,39 +36,49 @@ import com.metamatrix.cdk.api.EnvironmentUtility; import com.metamatrix.cdk.unittest.FakeTranslationFactory; -public class TestOracleTranslator extends TestCase { +public class TestOracleTranslator { - private static Translator TRANSLATOR; + /** + * An instance of {@link Translator} which has already been initialized. + */ + private static Translator TRANSLATOR; - static { - try { - TRANSLATOR = new OracleSQLTranslator(); - TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); - } catch(ConnectorException e) { - e.printStackTrace(); - } + /** + * Performs setup tasks that should be executed prior to an instance of this + * class being created. This method should only be executed once and does + * not protect from multiple executions. It is intended to be executed by + * the JUnit4 test framework. + * <p> + * This method sets {@link TestOracleTranslator#TRANSLATOR} to an instance + * of {@link OracleSQLTranslator} and then calls its {@link OracleSQLTranslator#initialize(ConnectorEnvironment)} + * method. + * @throws Exception + */ + @BeforeClass public static void oneTimeSetup() throws Exception { + TRANSLATOR = new OracleSQLTranslator(); + TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); } - public void helpTestVisitor(String input, String expectedOutput) throws ConnectorException { + private void helpTestVisitor(String input, String expectedOutput) throws ConnectorException { // Convert from sql to objects ICommand obj = FakeTranslationFactory.getInstance().getAutoIncrementTranslationUtility().parseCommand(input); - TranslatedCommand tc = new TranslatedCommand(EnvironmentUtility.createSecurityContext("user"), TRANSLATOR); + TranslatedCommand tc = new TranslatedCommand(EnvironmentUtility.createSecurityContext("user"), TRANSLATOR); //$NON-NLS-1$ tc.translateCommand(obj); // Check stuff - assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); //$NON-NLS-1$ + org.junit.Assert.assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); //$NON-NLS-1$ } - public void testInsertWithSequnce() throws Exception { - helpTestVisitor("insert into test.group (e0) values (1)", "INSERT INTO group (e0, e1) VALUES (1, MYSEQUENCE.nextVal)"); + @Test public void testInsertWithSequnce() throws Exception { + helpTestVisitor("insert into test.group (e0) values (1)", "INSERT INTO group (e0, e1) VALUES (1, MYSEQUENCE.nextVal)"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testInsertWithSequnce1() throws Exception { - helpTestVisitor("insert into test.group (e0, e1) values (1, 'x')", "INSERT INTO group (e0, e1) VALUES (1, 'x')"); + @Test public void testInsertWithSequnce1() throws Exception { + helpTestVisitor("insert into test.group (e0, e1) values (1, 'x')", "INSERT INTO group (e0, e1) VALUES (1, 'x')"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testJoins() throws Exception { + @Test public void testJoins() throws Exception { String input = "select smalla.intkey from bqt1.smalla inner join bqt1.smallb on smalla.stringkey=smallb.stringkey cross join bqt1.mediuma"; //$NON-NLS-1$ String output = "SELECT SmallA.IntKey FROM SmallA INNER JOIN SmallB ON SmallA.StringKey = SmallB.StringKey CROSS JOIN MediumA"; //$NON-NLS-1$ @@ -76,7 +87,7 @@ output, TRANSLATOR); } - public void testJoins2() throws Exception { + @Test public void testJoins2() throws Exception { String input = "select smalla.intkey from bqt1.smalla cross join (bqt1.smallb cross join bqt1.mediuma)"; //$NON-NLS-1$ String output = "SELECT SmallA.IntKey FROM SmallA CROSS JOIN (SmallB CROSS JOIN MediumA)"; //$NON-NLS-1$ @@ -84,4 +95,199 @@ input, output, TRANSLATOR); } + + @Test public void testRewriteConversion1() throws Exception { + String input = "SELECT char(convert(STRINGNUM, integer) + 100) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT chr((to_number(SmallA.StringNum) + 100)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testRewriteConversion2() throws Exception { + String input = "SELECT convert(STRINGNUM, long) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_number(SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testRewriteConversion3() throws Exception { + String input = "SELECT convert(convert(STRINGNUM, long), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_char(to_number(SmallA.StringNum)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testRewriteConversion4() throws Exception { + String input = "SELECT convert(convert(TIMESTAMPVALUE, date), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_char(trunc(SmallA.TimestampValue), 'YYYY-MM-DD') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteConversion5() throws Exception { + String input = "SELECT convert(convert(TIMESTAMPVALUE, time), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_char(to_date(('1970-01-01 ' || to_char(SmallA.TimestampValue, 'HH24:MI:SS')), 'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteConversion6() throws Exception { + String input = "SELECT convert(convert(TIMEVALUE, timestamp), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_char(cast(SmallA.TimeValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteConversion8() throws Exception { + String input = "SELECT nvl(INTNUM, 'otherString') FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT nvl(to_char(SmallA.IntNum), 'otherString') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteConversion7() throws Exception { + String input = "SELECT convert(convert(STRINGNUM, integer), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_char(to_number(SmallA.StringNum)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is") + @Test public void testRewriteLocate() throws Exception { + // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT instr('chimp', to_char(SmallA.IntNum), 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT instr('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is") + @Test public void testRewriteLocate3() throws Exception { + // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ + String output = "SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is 1 if a value of < 1 is given") + @Test public void testRewriteLocate5() throws Exception { + // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is 1 if a value of < 1 is given + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT instr('chimp', SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteSubstring1() throws Exception { + String input = "SELECT substring(StringNum, 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT substr(SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteSubstring2() throws Exception { + String input = "SELECT substring(StringNum, 1, 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT substr(SmallA.StringNum, 1, 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRewriteUnionWithOrderBy() throws Exception { + String input = "SELECT IntKey FROM BQT1.SMALLA UNION SELECT IntKey FROM BQT1.SMALLB ORDER BY IntKey"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallB.IntKey FROM SmallB ORDER BY IntKey NULLS FIRST"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRowLimit1() throws Exception { + String input = "select intkey from bqt1.smalla limit 10, 0"; //$NON-NLS-1$ + String output = "SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT SmallA.IntKey FROM SmallA) VIEW_FOR_LIMIT WHERE ROWNUM <= 10) WHERE ROWNUM_ > 10"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRowLimit2() throws Exception { + String input = "select intkey from bqt1.smalla limit 0, 10"; //$NON-NLS-1$ + String output = "SELECT * FROM (SELECT SmallA.IntKey FROM SmallA) WHERE ROWNUM <= 10"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRowLimit3() throws Exception { + String input = "select intkey from bqt1.smalla limit 1, 10"; //$NON-NLS-1$ + String output = "SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT SmallA.IntKey FROM SmallA) VIEW_FOR_LIMIT WHERE ROWNUM <= 11) WHERE ROWNUM_ > 1"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRowLimit4() throws Exception { + String input = "select intkey from bqt1.mediuma limit 100"; //$NON-NLS-1$ + String output = "SELECT * FROM (SELECT MediumA.IntKey FROM MediumA) WHERE ROWNUM <= 100"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testRowLimit5() throws Exception { + String input = "select intkey from bqt1.mediuma limit 50, 100"; //$NON-NLS-1$ + String output = "SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT MediumA.IntKey FROM MediumA) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testConcat2_useLiteral() throws Exception { + String input = "select concat2(stringnum,'_xx') from bqt1.Smalla"; //$NON-NLS-1$ + String output = "SELECT concat(nvl(SmallA.StringNum, ''), '_xx') FROM SmallA"; //$NON-NLS-1$ + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testConcat2() throws Exception { + String input = "select concat2(stringnum, stringkey) from bqt1.Smalla"; //$NON-NLS-1$ + String output = "SELECT CASE WHEN (SmallA.StringNum IS NULL) AND (SmallA.StringKey IS NULL) THEN NULL ELSE concat(nvl(SmallA.StringNum, ''), nvl(SmallA.StringKey, '')) END FROM SmallA"; //$NON-NLS-1$ + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + }