1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 23 package complex.dbaccess; 24 25 import com.sun.star.beans.XPropertySet; 26 import com.sun.star.container.XIndexAccess; 27 import com.sun.star.sdb.XParametersSupplier; 28 import com.sun.star.sdb.XSingleSelectQueryComposer; 29 import com.sun.star.sdbc.DataType; 30 import com.sun.star.sdbc.SQLException; 31 import com.sun.star.uno.Exception; 32 import com.sun.star.uno.UnoRuntime; 33 34 35 // ---------- junit imports ----------------- 36 import org.junit.Test; 37 import static org.junit.Assert.*; 38 // ------------------------------------------ 39 40 public class Parser extends CRMBasedTestCase 41 { 42 // -------------------------------------------------------------------------------------------------------- 43 @Override createTestCase()44 protected void createTestCase() 45 { 46 try 47 { 48 super.createTestCase(); 49 m_database.getDatabase().getDataSource().createQuery( "query products", "SELECT * FROM \"products\"" ); 50 } 51 catch ( Exception e ) 52 { 53 e.printStackTrace( System.err ); 54 fail( "caught an exception (" + e.getMessage() + ") while creating the test case"); 55 } 56 } 57 58 // -------------------------------------------------------------------------------------------------------- 59 @Test checkWhere()60 public void checkWhere() throws Exception 61 { 62 final XSingleSelectQueryComposer composer = createQueryComposer(); 63 final String SELECT = "SELECT \"products\".\"Name\" FROM \"products\" WHERE "; 64 final String[] queries = new String[] 65 { 66 "\"ID\" in ( 1,2,3,4)" 67 ,"not ( \"ID\" in ( 1,2,3,4))" 68 ,"(1 = 1) is true" 69 ,"(1 = 1) is not false" 70 ,"(1 = 1) is not null" 71 ,"not ( (1 = 1) is not null)" 72 ,"'a' like 'a%'" 73 ,"not ( 'a' like 'a%')" 74 ,"'a' not like 'a%'" 75 ,"1 between 0 and 2" 76 ,"not ( 1 between 0 and 2 )" 77 ,"1 not between 3 and 4" 78 ,"1 not between ( select \"ID\" from \"categories\") and ( select \"ID\" from \"categories\")" 79 ,"1 = 1" 80 ,"0 < 1" 81 ,"not(0 < 1)" 82 ,"1 > 0" 83 ,"not(1 > 0)" 84 ,"1 <> 0" 85 ,"(1 <> 0 and 'a' = 'a' and 'c' = 'd') or (1 = 1 and 2 = 2 and 3 = 4)" 86 ,"not ( 1 <> 0 )" 87 ,"\"CategoryID\" in ( select \"ID\" from \"categories\")" 88 ,"not (\"CategoryID\" in ( select \"ID\" from \"categories\"))" 89 ,"\"CategoryID\" not in ( select \"ID\" from \"categories\")" 90 }; 91 for (int i = 0; i < queries.length; i++) 92 { 93 composer.setQuery( SELECT + queries[i]); 94 } 95 } 96 // -------------------------------------------------------------------------------------------------------- 97 /** verifies that aliases for inner queries work as expected 98 */ 99 @Test checkJoinSyntax()100 public void checkJoinSyntax() throws Exception 101 { 102 final XSingleSelectQueryComposer composer = createQueryComposer(); 103 104 // feed the composer with some statements. If any of those cannot be parsed, the composer 105 // will throw an exception - which is a regression then 106 composer.setQuery( 107 "SELECT \"categories\".\"Name\", " + 108 "\"products\".\"Name\" " + 109 "FROM \"products\" RIGHT OUTER JOIN \"categories\" AS \"categories\" ON \"products\".\"CategoryID\" = \"categories\".\"ID\"" ); 110 111 composer.setQuery( 112 "SELECT \"categories\".\"Name\", " + 113 "\"products\".\"Name\" " + 114 "FROM \"products\" LEFT OUTER JOIN \"categories\" AS \"categories\" ON \"products\".\"CategoryID\" = \"categories\".\"ID\"" ); 115 116 composer.setQuery( 117 "SELECT \"categories\".\"Name\", " + 118 "\"products\".\"Name\" " + 119 "FROM \"products\" CROSS JOIN \"categories\" AS \"categories\"" ); 120 121 composer.setQuery( 122 "SELECT \"categories\".\"Name\", " + 123 "\"products\".\"Name\" " + 124 "FROM \"products\" INNER JOIN \"categories\" AS \"categories\" ON \"products\".\"CategoryID\" = \"categories\".\"ID\"" ); 125 126 // just to be sure the composer *really* parses upon setting the query: feed it with 127 // an unparseable statement 128 boolean caughtExpected = false; 129 try 130 { 131 composer.setQuery( "NONSENSE" ); 132 } 133 catch( SQLException e ) 134 { 135 caughtExpected = true; 136 } 137 assertTrue( "pre-condition not met: parser should except on unparseable statements, else the complete" + 138 "test is bogus!", caughtExpected ); 139 } 140 141 // -------------------------------------------------------------------------------------------------------- impl_checkParameters( final String _statement, final String[] _expectedParameterNames, final int[] _expectedParameterTypes,final String _context )142 private void impl_checkParameters( final String _statement, final String[] _expectedParameterNames, final int[] _expectedParameterTypes,final String _context ) throws Exception 143 { 144 final XSingleSelectQueryComposer composer = createQueryComposer(); 145 composer.setQuery( _statement ); 146 147 assertEquals( "checkParameterTypes: internal error", _expectedParameterNames.length, _expectedParameterTypes.length ); 148 149 final XParametersSupplier paramSupp = UnoRuntime.queryInterface(XParametersSupplier.class, composer); 150 final XIndexAccess parameters = paramSupp.getParameters(); 151 152 assertEquals( "(ctx: " + _context + ") unexpected parameter count", _expectedParameterNames.length, parameters.getCount() ); 153 for ( int i=0; i<parameters.getCount(); ++i ) 154 { 155 final XPropertySet parameter = UnoRuntime.queryInterface(XPropertySet.class, parameters.getByIndex(i)); 156 157 final String name = (String)parameter.getPropertyValue( "Name" ); 158 assertEquals( "(ctx: " + _context + ") unexpected parameter name for parameter number " + ( i + 1 ), _expectedParameterNames[i], name ); 159 160 final int type = ((Integer)parameter.getPropertyValue( "Type" )).intValue(); 161 assertEquals( "(ctx: " + _context + ") unexpected data type for parameter number " + ( i + 1 ), _expectedParameterTypes[i], type ); 162 } 163 } 164 165 // -------------------------------------------------------------------------------------------------------- 166 /** verifies that the parser properly recognizes the types of parameters 167 */ 168 @Test checkParameterTypes()169 public void checkParameterTypes() throws Exception 170 { 171 impl_checkParameters( 172 "SELECT * FROM \"all orders\" " + 173 "WHERE ( \"Order Date\" >= :order_date ) " + 174 " AND ( ( \"Customer Name\" LIKE :customer ) " + 175 " OR ( \"Product Name\" LIKE ? ) " + 176 " )", 177 new String[] { "order_date", "customer", "Product Name" }, 178 new int[] { DataType.DATE, DataType.VARCHAR, DataType.VARCHAR }, 179 ">= && LIKE" 180 ); 181 182 impl_checkParameters( 183 "SELECT * FROM \"categories\" " + 184 "WHERE \"ID\" BETWEEN :id_lo AND :id_hi", 185 new String[] { "id_lo", "id_hi" }, 186 new int[] { DataType.INTEGER, DataType.INTEGER }, 187 "BETWEEN" 188 ); 189 190 impl_checkParameters( 191 "SELECT CONCAT( :prefix, CONCAT( \"Name\", :suffix ) ) FROM \"customers\"", 192 new String[] { "prefix", "suffix" }, 193 new int[] { DataType.VARCHAR, DataType.VARCHAR }, 194 "CONCAT" 195 ); 196 } 197 } 198