Author: vhalbert(a)redhat.com Date: 2010-09-22 17:11:32 -0400 (Wed, 22 Sep 2010) New Revision: 2596 Added: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeSQLVisitor.java branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeUtil.java branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/PathFunctionModifier.java branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestPathFunctionModifier.java Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeExecutionFactory.java branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestModeShapeSqlTranslator.java branches/7.1.x/connectors/translator-jdbc/src/test/resources/ModeShape.vdb Log: TEIID-1106 adding logic to support more JCR Sql Syntax Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeExecutionFactory.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeExecutionFactory.java 2010-09-22 16:59:20 UTC (rev 2595) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeExecutionFactory.java 2010-09-22 21:11:32 UTC (rev 2596) @@ -35,8 +35,10 @@ import org.teiid.language.LanguageObject; import org.teiid.language.Literal; import org.teiid.language.NamedTable; -import org.teiid.translator.SourceSystemFunctions; +import org.teiid.logging.LogConstants; +import org.teiid.logging.LogManager; import org.teiid.translator.ExecutionContext; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; import org.teiid.translator.jdbc.ConvertModifier; @@ -60,24 +62,7 @@ public void start() throws TranslatorException { super.start(); - registerFunctionModifier("PATH", new FunctionModifier() { //$NON-NLS-1$ - - @Override - public List<?> translate(Function function) { - List<Object> objs = new ArrayList<Object>(); - - List<Expression> parms = function.getParameters(); - - for (Expression s : parms) - { - String v = s.toString(); - v.replace('\'', ' '); - objs.add(v); - } - - return objs; - } - } ); + registerFunctionModifier("PATH", new PathFunctionModifier()); //add in type conversion ConvertModifier convertModifier = new ConvertModifier(); @@ -127,6 +112,10 @@ }, FunctionModifier.BOOLEAN); registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); + + + LogManager.logTrace(LogConstants.CTX_CONNECTOR, "Started"); //$NON-NLS-1$ + } /** @@ -136,48 +125,35 @@ * @return the {@link SQLConversionVisitor} */ public SQLConversionVisitor getSQLConversionVisitor() { - return new SQLConversionVisitor(this); + return new ModeShapeSQLVisitor(this); } @Override public List<?> translate(LanguageObject obj, ExecutionContext context) { - if (obj instanceof NamedTable) { NamedTable nt = (NamedTable) obj; List<String> ntlist = new ArrayList<String>(1); - ntlist.add("[" + trimTics(nt.getMetadataObject().getNameInSource()) + "]"); //$NON-NLS-1$ //$NON-NLS-2$ + ntlist.add(ModeShapeUtil.createJCRName(nt.getMetadataObject().getNameInSource())); return ntlist; - } - - if (obj instanceof ColumnReference) { + } else if (obj instanceof ColumnReference) { ColumnReference elem = (ColumnReference) obj; - List<String> ntlist = new ArrayList<String>(1); - ntlist.add("[" + trimTics(elem.getMetadataObject().getNameInSource()) + "]"); //$NON-NLS-1$ //$NON-NLS-2$ - return ntlist; - } + + String nameInSource = "NoNameInSource"; + if (elem.getMetadataObject() != null) { + nameInSource = elem.getMetadataObject().getNameInSource(); + + List<String> ntlist = new ArrayList<String>(1); + ntlist.add(ModeShapeUtil.createJCRName(nameInSource)); + + return ntlist; + } + } return super.translate(obj, context); } - - /** - * Because the Teiid Designer Import from JDBC adds tic's to a nameInSource that has special characters, - * they have to be removed when building the sql syntax - * @param name - * @return - */ - private String trimTics(String name) { - String rtn = name; - if (rtn.startsWith("'")) { - rtn = rtn.substring(1); - } - if (rtn.endsWith("'")) { - rtn = rtn.substring(0, rtn.indexOf("'")); - } - return rtn; - } - + @Override public String translateLiteralBoolean(Boolean booleanValue) { if(booleanValue.booleanValue()) { Added: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeSQLVisitor.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeSQLVisitor.java (rev 0) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeSQLVisitor.java 2010-09-22 21:11:32 UTC (rev 2596) @@ -0,0 +1,95 @@ +package org.teiid.translator.jdbc.modeshape; + +import static org.teiid.language.SQLConstants.Reserved.BY; +import static org.teiid.language.SQLConstants.Reserved.ORDER; + +import java.util.HashMap; +import java.util.Iterator; +import java.util.List; +import java.util.Map; + +import org.teiid.language.ColumnReference; +import org.teiid.language.DerivedColumn; +import org.teiid.language.Expression; +import org.teiid.language.OrderBy; +import org.teiid.language.Select; +import org.teiid.language.SortSpecification; +import org.teiid.language.SQLConstants.Tokens; +import org.teiid.metadata.Column; +import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.SQLConversionVisitor; + +public class ModeShapeSQLVisitor extends SQLConversionVisitor { + + private Map<String, Column> columnMap = new HashMap<String, Column>(); + private Map<String, Column> aliasMap = new HashMap<String, Column>(); + + public ModeShapeSQLVisitor(JDBCExecutionFactory ef) { + super(ef); + + } + + public void visit(Select query) { + + // if the query has an order by, then + // need to cache the columns so that the + // order by column name can be replaced by its + // correlating select column that has the nameInSource + if (query.getOrderBy() == null) { + super.visit(query); + return; + } + + List<DerivedColumn> selectSymbols = query.getDerivedColumns(); + Iterator<DerivedColumn> symbolIter = selectSymbols.iterator(); + while (symbolIter.hasNext()) { + DerivedColumn symbol = symbolIter.next(); + Expression expression = symbol.getExpression(); + + if (symbol.getAlias() != null) { + + } + // cache the columns so that order by + if (expression instanceof ColumnReference) { + ColumnReference colRef = (ColumnReference) expression; + if (colRef.getMetadataObject() != null) { + Column element = colRef.getMetadataObject(); + if (symbol.getAlias() != null) { + aliasMap.put(symbol.getAlias(), element); + } + columnMap.put(element.getName(), element); + } + } + } + + super.visit(query); + } + + public void visit(OrderBy obj) { + buffer.append(ORDER) + .append(Tokens.SPACE) + .append(BY) + .append(Tokens.SPACE); + + List<SortSpecification> specs = obj.getSortSpecifications(); + for (SortSpecification spec : specs) { + String specName = spec.getExpression().toString(); + Column col = null; + + col = aliasMap.get(specName); + if (col == null) { + col = columnMap.get(specName); + } + if (col != null) { + buffer.append(ModeShapeUtil.createJCRName(col.getNameInSource())) + .append(" ") + .append(spec.getOrdering().toString()); + + } else { + buffer.append(obj.getSortSpecifications()); + } + } + + } + +} Property changes on: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeSQLVisitor.java ___________________________________________________________________ Name: svn:keywords + Id Revision Name: svn:eol-style + LF Added: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeUtil.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeUtil.java (rev 0) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeUtil.java 2010-09-22 21:11:32 UTC (rev 2596) @@ -0,0 +1,27 @@ +package org.teiid.translator.jdbc.modeshape; + +public class ModeShapeUtil { + + public static final String createJCRName(String name) { + return "[" + ModeShapeUtil.trimTics(name) + "]"; + } + + /** + * Because the Teiid Designer Import from JDBC adds tic's to a nameInSource that has special characters, + * they have to be removed when building the sql syntax + * @param name + * @return + */ + public static final String trimTics(String name) { + String rtn = name; + if (rtn.startsWith("'")) { + rtn = rtn.substring(1); + } + + if (rtn.endsWith("'")) { + rtn = rtn.substring(0, rtn.indexOf("'")); + } + return rtn; + } + +} Property changes on: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/ModeShapeUtil.java ___________________________________________________________________ Name: svn:keywords + Id Revision Name: svn:eol-style + LF Added: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/PathFunctionModifier.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/PathFunctionModifier.java (rev 0) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/PathFunctionModifier.java 2010-09-22 21:11:32 UTC (rev 2596) @@ -0,0 +1,58 @@ +/* + * 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.modeshape; + +import java.util.ArrayList; +import java.util.List; + +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.translator.jdbc.FunctionModifier; + + +/** + * Function to translate the PATH function + * @since 7.1 + */ +public class PathFunctionModifier extends FunctionModifier { + + public PathFunctionModifier() { + super(); + } + + public List<?> translate(Function function) { + List<Object> objs = new ArrayList<Object>(); + + List<Expression> parms = function.getParameters(); + + for (Expression s : parms) + { + String v = s.toString(); + v.replace('\'', ' '); + objs.add(v); + } + + return objs; + } + +} Property changes on: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/modeshape/PathFunctionModifier.java ___________________________________________________________________ Name: svn:keywords + Id Revision Name: svn:eol-style + LF Modified: branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestModeShapeSqlTranslator.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestModeShapeSqlTranslator.java 2010-09-22 16:59:20 UTC (rev 2595) +++ branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestModeShapeSqlTranslator.java 2010-09-22 21:11:32 UTC (rev 2596) @@ -22,15 +22,15 @@ package org.teiid.translator.jdbc.modeshape; -import static org.junit.Assert.*; +import static org.junit.Assert.assertEquals; import org.junit.BeforeClass; -import org.junit.Ignore; import org.junit.Test; import org.mockito.Mockito; import org.teiid.cdk.api.TranslationUtility; import org.teiid.core.util.UnitTestUtil; import org.teiid.language.Command; +import org.teiid.language.LanguageFactory; import org.teiid.translator.ExecutionContext; import org.teiid.translator.TranslatorException; import org.teiid.translator.jdbc.TranslatedCommand; @@ -46,14 +46,16 @@ .getTestDataPath() : "src/test/resources") + "/ModeShape.vdb"; + + + @BeforeClass + public static void setUp() throws TranslatorException { + TRANSLATOR = new ModeShapeExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); - @BeforeClass - public static void setUp() throws TranslatorException { - TRANSLATOR = new ModeShapeExecutionFactory(); - TRANSLATOR.start(); + } - } - public void helpTestVisitor(TranslationUtility util, String input, String expectedOutput) throws TranslatorException { // Convert from sql to objects @@ -68,77 +70,51 @@ @Test public void testSelectAllFromBase() throws Exception { String input = "select * from nt_base"; //$NON-NLS-1$ - String output = "SELECT [jcr:mixinTypes] FROM [nt:base]"; //$NON-NLS-1$ + String output = "SELECT [jcr:primaryType] FROM [nt:base]"; //$NON-NLS-1$ - // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); } @Test public void testSelectColumnFromBase() throws Exception { - String input = "select jcr_mixinTypes from nt_base"; //$NON-NLS-1$ - String output = "SELECT [jcr:mixinTypes] FROM [nt:base]"; //$NON-NLS-1$ + String input = "select jcr_primaryType from nt_base"; //$NON-NLS-1$ + String output = "SELECT [jcr:primaryType] FROM [nt:base]"; //$NON-NLS-1$ - // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); } -// -// @Test -// public void testSimpleSelect() throws Exception { -// String input = "select Model from Car"; //$NON-NLS-1$ -// String output = "SELECT [car:Model] FROM [car:Car]"; //$NON-NLS-1$ -// -// // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), -// helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); -// -// } -// -// @Test -// public void testWhereClause() throws Exception { -// -// String input = "select Model from Car WHERE Make = 'Honda'"; //$NON-NLS-1$ -// String output = "SELECT [car:Model] FROM [car:Car] WHERE [car:Make] = 'Honda'"; //$NON-NLS-1$ -// -// // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), -// helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); -// -// } -// -// @Test -// public void testOrderBy() throws Exception { -// -// String input = "select Model from Car ORDER BY Make"; //$NON-NLS-1$ -// String output = "SELECT [car:Model] FROM [car:Car] ORDER BY [car:Make]"; //$NON-NLS-1$ -// -// // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), -// helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); -// -// } -// -// @Ignore -// @Test -// public void testUsingAlias() throws Exception { -// -// String input = "select c.Model from Car As c"; //$NON-NLS-1$ -// String output = "SELECT c.[car:Model] FROM [car:Car] As c"; //$NON-NLS-1$ -// -// // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), -// helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); -// -// } -// -// @Ignore -// @Test -// public void testUsingNameFunction() throws Exception { -// -// String input = "select Model from Car as car WHERE PATH('car') LIKE '%/Hybrid/%'"; //$NON-NLS-1$ -// String output = "SELECT [car:Model] FROM [car:Car] WHERE PATH(car:Car) LIKE '%/Hybrid/%'"; //$NON-NLS-1$ -// -// // FakeTranslationFactory.getInstance().getExampleTranslationUtility(), -// helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); -// -// } + @Test + public void testWhereClause() throws Exception { + + String input = "SELECT jcr_primaryType from nt_base WHERE jcr_primaryType = 'relational:column'"; //$NON-NLS-1$ + String output = "SELECT [jcr:primaryType] FROM [nt:base] WHERE [jcr:primaryType] = 'relational:column'"; //$NON-NLS-1$ + + helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); + + } + + @Test + public void testOrderBy() throws Exception { + + String input = "SELECT jcr_primaryType from nt_base ORDER BY jcr_primaryType"; //$NON-NLS-1$ + String output = "SELECT [jcr:primaryType] FROM [nt:base] ORDER BY [jcr:primaryType] ASC"; //$NON-NLS-1$ + + helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); + + } + + @Test + public void testUsingLike() throws Exception { + + String input = "SELECT jcr_primaryType from nt_base WHERE jcr_primaryType LIKE '%relational%'"; //$NON-NLS-1$ + String output = "SELECT [jcr:primaryType] FROM [nt:base] WHERE [jcr:primaryType] LIKE '%relational%'"; //$NON-NLS-1$ + + helpTestVisitor(new TranslationUtility(MODESHAPE_VDB), input, output); + + } + + + } Added: branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestPathFunctionModifier.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestPathFunctionModifier.java (rev 0) +++ branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestPathFunctionModifier.java 2010-09-22 21:11:32 UTC (rev 2596) @@ -0,0 +1,87 @@ +/* + * 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.modeshape; + +import java.util.Arrays; +import java.util.Collections; + +import junit.framework.TestCase; + +import org.junit.Test; +import org.teiid.cdk.api.TranslationUtility; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.language.Literal; +import org.teiid.translator.jdbc.SQLConversionVisitor; + + +/** + */ +public class TestPathFunctionModifier extends TestCase { + + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + /** + * Constructor for TestMonthFunctionModifier. + * @param name + */ + public TestPathFunctionModifier(String name) { + super(name); + } + + public void helpTestMod(Expression c, String expectedStr, String target) throws Exception { + Function func = null; + if (c != null) { + func = LANG_FACTORY.createFunction(target, + Arrays.asList(c), + String.class); + } else { + func = LANG_FACTORY.createFunction(target, + Collections.EMPTY_LIST, + String.class); + + } + + ModeShapeExecutionFactory trans = new ModeShapeExecutionFactory(); + trans.start(); + + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + + sqlVisitor.append(func); + assertEquals(expectedStr, sqlVisitor.toString()); + } + + + public void test1() throws Exception { + Literal arg1 = LANG_FACTORY.createLiteral("car", String.class); //$NON-NLS-1$ + helpTestMod(arg1, "PATH('car')", "PATH"); //$NON-NLS-1$ + } + + public void test2() throws Exception { + helpTestMod(null, "PATH()", "PATH"); //$NON-NLS-1$ + } + + +} + Property changes on: branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/modeshape/TestPathFunctionModifier.java ___________________________________________________________________ Name: svn:keywords + Id Revision Name: svn:eol-style + LF Modified: branches/7.1.x/connectors/translator-jdbc/src/test/resources/ModeShape.vdb =================================================================== (Binary files differ)