Author: shawkins Date: 2010-01-21 10:42:22 -0500 (Thu, 21 Jan 2010) New Revision: 1765 Added: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQL5Translator.java Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQL5Translator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java Log: TEIID-938 fix for mysql 5 char function Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQL5Translator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQL5Translator.java 2010-01-21 05:11:37 UTC (rev 1764) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQL5Translator.java 2010-01-21 15:42:22 UTC (rev 1765) @@ -22,11 +22,31 @@ package org.teiid.connector.jdbc.mysql; +import java.util.Arrays; +import java.util.List; + import org.teiid.connector.api.ConnectorCapabilities; +import org.teiid.connector.api.ConnectorEnvironment; +import org.teiid.connector.api.ConnectorException; +import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.jdbc.translator.FunctionModifier; +import org.teiid.connector.language.IFunction; -public class MySQL5Translator extends MySQLTranslator { +public class MySQL5Translator extends MySQLTranslator { @Override + public void initialize(ConnectorEnvironment env) throws ConnectorException { + super.initialize(env); + registerFunctionModifier(SourceSystemFunctions.CHAR, new FunctionModifier() { + + @Override + public List<?> translate(IFunction function) { + return Arrays.asList("char(", function.getParameters().get(0), " USING ASCII)"); //$NON-NLS-1$ //$NON-NLS-2$ + } + }); + } + + @Override public Class<? extends ConnectorCapabilities> getDefaultCapabilities() { return MySQL5Capabilities.class; } Added: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQL5Translator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQL5Translator.java (rev 0) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQL5Translator.java 2010-01-21 15:42:22 UTC (rev 1765) @@ -0,0 +1,54 @@ +/* + * 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.connector.jdbc.mysql; + +import java.util.Properties; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.connector.api.ConnectorException; +import org.teiid.connector.jdbc.TranslationHelper; + +import com.metamatrix.cdk.api.EnvironmentUtility; + +/** + */ +public class TestMySQL5Translator { + + private static MySQL5Translator TRANSLATOR; + + @BeforeClass public static void oneTimeSetup() throws ConnectorException { + TRANSLATOR = new MySQL5Translator(); + TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + } + + @Test public void testChar() throws Exception { + String input = "SELECT intkey, CHR(CONVERT(bigintegervalue, integer)) FROM BQT1.MediumA"; //$NON-NLS-1$ + String output = "SELECT MediumA.IntKey, char(cast(MediumA.BigIntegerValue AS signed) USING ASCII) FROM MediumA"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + +} Property changes on: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQL5Translator.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java 2010-01-21 05:11:37 UTC (rev 1764) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java 2010-01-21 15:42:22 UTC (rev 1765) @@ -20,8 +20,8 @@ * 02110-1301 USA. */ -package org.teiid.connector.jdbc.mysql; - +package org.teiid.connector.jdbc.mysql; + import java.util.Properties; import org.junit.BeforeClass; @@ -30,102 +30,102 @@ import org.teiid.connector.jdbc.TranslationHelper; import com.metamatrix.cdk.api.EnvironmentUtility; - -/** - */ -public class TestMySQLTranslator { - - private static MySQLTranslator TRANSLATOR; + +/** + */ +public class TestMySQLTranslator { + + private static MySQLTranslator TRANSLATOR; - @BeforeClass public static void oneTimeSetup() throws ConnectorException { - TRANSLATOR = new MySQLTranslator(); + @BeforeClass public static void oneTimeSetup() throws ConnectorException { + TRANSLATOR = new MySQLTranslator(); TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); - } - - private String getTestVDB() { - return TranslationHelper.PARTS_VDB; - } - - private String getTestBQTVDB() { - return TranslationHelper.BQT_VDB; - } - + } + + private String getTestVDB() { + return TranslationHelper.PARTS_VDB; + } + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + @Test public void testConversion1() throws Exception { - String input = "SELECT char(convert(PART_WEIGHT, integer) + 100) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT char((cast(PARTS.PART_WEIGHT AS signed) + 100)) FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } - + String input = "SELECT char(convert(PART_WEIGHT, integer) + 100) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT char((cast(PARTS.PART_WEIGHT AS signed) + 100)) FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } + @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 signed) FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } - + String input = "SELECT convert(PART_WEIGHT, long) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT cast(PARTS.PART_WEIGHT AS signed) FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } + @Test public void testConversion3() throws Exception { - String input = "SELECT convert(convert(PART_WEIGHT, long), string) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT cast(cast(PARTS.PART_WEIGHT AS signed) AS char) FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } - + String input = "SELECT convert(convert(PART_WEIGHT, long), string) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT cast(cast(PARTS.PART_WEIGHT AS signed) AS char) FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } + @Test public void testConversion4() throws Exception { - String input = "SELECT convert(convert(PART_WEIGHT, date), string) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_format(DATE(PARTS.PART_WEIGHT), '%Y-%m-%d') FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + String input = "SELECT convert(convert(PART_WEIGHT, date), string) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT date_format(DATE(PARTS.PART_WEIGHT), '%Y-%m-%d') FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @Test public void testConversion5() throws Exception { - String input = "SELECT convert(convert(PART_WEIGHT, time), string) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_format(TIME(PARTS.PART_WEIGHT), '%H:%i:%S') FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + String input = "SELECT convert(convert(PART_WEIGHT, time), string) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT date_format(TIME(PARTS.PART_WEIGHT), '%H:%i:%S') FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @Test public void testConversion6() throws Exception { - String input = "SELECT convert(convert(PART_WEIGHT, timestamp), string) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_format(TIMESTAMP(PARTS.PART_WEIGHT), '%Y-%m-%d %H:%i:%S.%f') FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + String input = "SELECT convert(convert(PART_WEIGHT, timestamp), string) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT date_format(TIMESTAMP(PARTS.PART_WEIGHT), '%Y-%m-%d %H:%i:%S.%f') FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @Test public void testConversion8() 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$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + 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$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @Test public void testConversion7() throws Exception { - String input = "SELECT convert(convert(PART_WEIGHT, integer), string) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT cast(cast(PARTS.PART_WEIGHT AS signed) AS char) FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + String input = "SELECT convert(convert(PART_WEIGHT, integer), string) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT cast(cast(PARTS.PART_WEIGHT AS signed) AS char) FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @Test public void testInsert() throws Exception { - String input = "SELECT insert(PART_WEIGHT, 1, 5, 'chimp') FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT insert(PARTS.PART_WEIGHT, 1, 5, 'chimp') FROM PARTS"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } - + String input = "SELECT insert(PART_WEIGHT, 1, 5, 'chimp') FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT insert(PARTS.PART_WEIGHT, 1, 5, 'chimp') FROM PARTS"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } + /** * Test the translator's ability to rewrite the LOCATE() function in a form * suitable for the data source. @@ -209,7 +209,7 @@ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } + } /** * Test the translator's ability to rewrite the LOCATE() function in a form @@ -246,48 +246,48 @@ } @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$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + 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$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @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$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } + 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$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); + } @Test public void testUnionWithOrderBy() throws Exception { - String input = "SELECT PART_ID FROM PARTS UNION SELECT PART_ID FROM PARTS ORDER BY PART_ID"; //$NON-NLS-1$ - String output = "(SELECT PARTS.PART_ID FROM PARTS) UNION (SELECT PARTS.PART_ID FROM PARTS) ORDER BY PART_ID"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); - } - - @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$ - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); + String input = "SELECT PART_ID FROM PARTS UNION SELECT PART_ID FROM PARTS ORDER BY PART_ID"; //$NON-NLS-1$ + String output = "(SELECT PARTS.PART_ID FROM PARTS) UNION (SELECT PARTS.PART_ID FROM PARTS) ORDER BY PART_ID"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestVDB(), + input, + output, TRANSLATOR); } - - @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 50, 100"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); + + @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$ + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); } + @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 50, 100"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testBitAnd() throws Exception { String input = "select bitand(intkey, intnum) from bqt1.smalla"; //$NON-NLS-1$ String output = "SELECT cast((SmallA.IntKey & SmallA.IntNum) AS signed) FROM SmallA"; //$NON-NLS-1$ @@ -322,5 +322,15 @@ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } -} + } + + @Test public void testChar() throws Exception { + String input = "SELECT intkey, CHR(CONVERT(bigintegervalue, integer)) FROM BQT1.MediumA"; //$NON-NLS-1$ + String output = "SELECT MediumA.IntKey, char(cast(MediumA.BigIntegerValue AS signed)) FROM MediumA"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + +}