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.PropertyState; 26 import com.sun.star.sdb.SQLFilterOperator; 27 import com.sun.star.beans.PropertyAttribute; 28 import com.sun.star.beans.XPropertySet; 29 import com.sun.star.beans.XPropertyContainer; 30 import com.sun.star.beans.NamedValue; 31 import com.sun.star.container.XNameAccess; 32 import com.sun.star.sdbcx.XTablesSupplier; 33 import com.sun.star.sdb.XParametersSupplier; 34 import com.sun.star.beans.PropertyValue; 35 import com.sun.star.sdbcx.XColumnsSupplier; 36 import com.sun.star.container.XIndexAccess; 37 import com.sun.star.sdb.CommandType; 38 import com.sun.star.sdb.XSingleSelectQueryComposer; 39 import com.sun.star.uno.UnoRuntime; 40 41 import com.sun.star.sdbc.DataType; 42 import java.lang.reflect.InvocationTargetException; 43 import java.lang.reflect.Method; 44 45 // ---------- junit imports ----------------- 46 import org.junit.Test; 47 import static org.junit.Assert.*; 48 // ------------------------------------------ 49 50 public class SingleSelectQueryComposer extends CRMBasedTestCase 51 { 52 53 private XSingleSelectQueryComposer m_composer = null; 54 private final static String COMPLEXFILTER = "( \"ID\" = 1 AND \"Postal\" = '4' )" 55 + " OR ( \"ID\" = 2 AND \"Postal\" = '5' )" 56 + " OR ( \"ID\" = 3 AND \"Postal\" = '6' AND \"Address\" = '7' )" 57 + " OR ( \"Address\" = '8' )" 58 + " OR ( \"Postal\" = '9' )" 59 + " OR ( NOW( ) = {D '2010-01-01' } )"; 60 private final static String INNERPRODUCTSQUERY = "products (inner)"; 61 62 // -------------------------------------------------------------------------------------------------------- createQueries()63 private void createQueries() throws Exception 64 { 65 m_database.getDatabase().getDataSource().createQuery(INNERPRODUCTSQUERY, "SELECT * FROM \"products\""); 66 } 67 68 // -------------------------------------------------------------------------------------------------------- 69 @Override createTestCase()70 protected void createTestCase() 71 { 72 try 73 { 74 super.createTestCase(); 75 76 createQueries(); 77 78 m_composer = createQueryComposer(); 79 80 } 81 catch (Exception e) 82 { 83 fail("caught an exception (" + e.getMessage() + ") while creating the test case"); 84 } 85 } 86 87 // -------------------------------------------------------------------------------------------------------- checkAttributeAccess(String _attributeName, String _attributeValue)88 private void checkAttributeAccess(String _attributeName, String _attributeValue) 89 { 90 System.out.println("setting " + _attributeName + " to " + _attributeValue); 91 String realValue = null; 92 try 93 { 94 final Class composerClass = m_composer.getClass(); 95 final Method attributeGetter = composerClass.getMethod("get" + _attributeName, new Class[] 96 { 97 }); 98 final Method attributeSetter = composerClass.getMethod("set" + _attributeName, new Class[] 99 { 100 String.class 101 }); 102 103 attributeSetter.invoke(m_composer, new Object[] 104 { 105 _attributeValue 106 }); 107 realValue = (String) attributeGetter.invoke(m_composer, new Object[] 108 { 109 }); 110 } 111 catch (NoSuchMethodException e) 112 { 113 } 114 catch (IllegalAccessException e) 115 { 116 } 117 catch (InvocationTargetException e) 118 { 119 } 120 assertTrue("set/get" + _attributeName + " not working as expected (set: " + _attributeValue + ", get: " + (realValue != null ? realValue : "null") + ")", 121 realValue.equals(_attributeValue)); 122 System.out.println(" (results in " + m_composer.getQuery() + ")"); 123 } 124 125 /** tests setCommand of the composer 126 */ 127 @Test testSetCommand()128 public void testSetCommand() 129 { 130 System.out.println("testing SingleSelectQueryComposer's setCommand"); 131 132 try 133 { 134 final String table = "SELECT * FROM \"customers\""; 135 m_composer.setCommand("customers", CommandType.TABLE); 136 assertTrue("setCommand/getQuery TABLE inconsistent", m_composer.getQuery().equals(table)); 137 138 m_database.getDatabase().getDataSource().createQuery("set command test", "SELECT * FROM \"orders for customer\" \"a\", \"customers\" \"b\" WHERE \"a\".\"Product Name\" = \"b\".\"Name\""); 139 m_composer.setCommand("set command test", CommandType.QUERY); 140 assertTrue("setCommand/getQuery QUERY inconsistent", m_composer.getQuery().equals(m_database.getDatabase().getDataSource().getQueryDefinition("set command test").getCommand())); 141 142 final String sql = "SELECT * FROM \"orders for customer\" WHERE \"Product Name\" = 'test'"; 143 m_composer.setCommand(sql, CommandType.COMMAND); 144 assertTrue("setCommand/getQuery COMMAND inconsistent", m_composer.getQuery().equals(sql)); 145 } 146 catch (Exception e) 147 { 148 fail("Exception caught: " + e); 149 } 150 } 151 152 /** tests accessing attributes of the composer (order, filter, group by, having) 153 */ 154 @Test testAttributes()155 public void testAttributes() 156 { 157 System.out.println("testing SingleSelectQueryComposer's attributes (order, filter, group by, having)"); 158 159 try 160 { 161 System.out.println("check setElementaryQuery"); 162 163 final String simpleQuery2 = "SELECT * FROM \"customers\" WHERE \"Name\" = 'oranges'"; 164 m_composer.setElementaryQuery(simpleQuery2); 165 assertTrue("setElementaryQuery/getQuery inconsistent", m_composer.getQuery().equals(simpleQuery2)); 166 167 System.out.println("check setQuery"); 168 final String simpleQuery = "SELECT * FROM \"customers\""; 169 m_composer.setQuery(simpleQuery); 170 assertTrue("set/getQuery inconsistent", m_composer.getQuery().equals(simpleQuery)); 171 172 checkAttributeAccess("Filter", "\"Name\" = 'oranges'"); 173 checkAttributeAccess("Group", "\"City\""); 174 checkAttributeAccess("Order", "\"Address\""); 175 checkAttributeAccess("HavingClause", "\"ID\" <> 4"); 176 177 final XIndexAccess orderColumns = m_composer.getOrderColumns(); 178 assertTrue("Order columns doesn't exist: \"Address\"", 179 orderColumns != null && orderColumns.getCount() == 1 && orderColumns.getByIndex(0) != null); 180 181 final XIndexAccess groupColumns = m_composer.getGroupColumns(); 182 assertTrue("Group columns doesn't exist: \"City\"", 183 groupColumns != null && groupColumns.getCount() == 1 && groupColumns.getByIndex(0) != null); 184 185 // XColumnsSupplier 186 final XColumnsSupplier xSelectColumns = UnoRuntime.queryInterface(XColumnsSupplier.class, m_composer); 187 assertTrue("no select columns, or wrong number of select columns", 188 xSelectColumns != null && xSelectColumns.getColumns() != null && xSelectColumns.getColumns().getElementNames().length == 6); 189 190 // structured filter 191 m_composer.setQuery("SELECT \"ID\", \"Postal\", \"Address\" FROM \"customers\""); 192 m_composer.setFilter(COMPLEXFILTER); 193 final PropertyValue[][] aStructuredFilter = m_composer.getStructuredFilter(); 194 m_composer.setFilter(""); 195 m_composer.setStructuredFilter(aStructuredFilter); 196 if (!m_composer.getFilter().equals(COMPLEXFILTER)) 197 { 198 System.out.println(COMPLEXFILTER); 199 System.out.println(m_composer.getFilter()); 200 } 201 assertTrue("Structured Filter not identical", m_composer.getFilter().equals(COMPLEXFILTER)); 202 203 // structured having clause 204 m_composer.setHavingClause(COMPLEXFILTER); 205 final PropertyValue[][] aStructuredHaving = m_composer.getStructuredHavingClause(); 206 m_composer.setHavingClause(""); 207 m_composer.setStructuredHavingClause(aStructuredHaving); 208 assertTrue("Structured Having Clause not identical", m_composer.getHavingClause().equals(COMPLEXFILTER)); 209 } 210 catch (Exception e) 211 { 212 fail("Exception caught: " + e); 213 } 214 } 215 216 /** test various sub query related features ("queries in queries") 217 */ 218 @Test testSubQueries()219 public void testSubQueries() throws Exception 220 { 221 m_composer.setQuery("SELECT * from \"" + INNERPRODUCTSQUERY + "\""); 222 final XTablesSupplier suppTables = UnoRuntime.queryInterface(XTablesSupplier.class, m_composer); 223 final XNameAccess tables = suppTables.getTables(); 224 assertTrue("a simple SELECT * FROM <query> could not be parsed", 225 tables != null && tables.hasByName(INNERPRODUCTSQUERY)); 226 227 final String sInnerCommand = m_database.getDatabase().getDataSource().getQueryDefinition(INNERPRODUCTSQUERY).getCommand(); 228 final String sExecutableQuery = m_composer.getQueryWithSubstitution(); 229 assertTrue("simple query containing a sub query improperly parsed to SDBC level statement: \n1. " + sExecutableQuery + "\n2. " + "SELECT * FROM ( " + sInnerCommand + " ) AS \"" + INNERPRODUCTSQUERY + "\"", 230 sExecutableQuery.equals("SELECT * FROM ( " + sInnerCommand + " ) AS \"" + INNERPRODUCTSQUERY + "\"")); 231 } 232 233 /** tests the XParametersSupplier functionality 234 */ 235 @Test testParameters()236 public void testParameters() 237 { 238 try 239 { 240 // "orders for customers" is a query with a named parameter (based on another query) 241 m_database.getDatabase().getDataSource().createQuery("orders for customer", "SELECT * FROM \"all orders\" WHERE \"Customer Name\" LIKE :cname"); 242 // "orders for customer and product" is query based on "orders for customers", adding an additional, 243 // anonymous parameter 244 m_database.getDatabase().getDataSource().createQuery("orders for customer and product", "SELECT * FROM \"orders for customer\" WHERE \"Product Name\" LIKE ?"); 245 246 m_composer.setQuery(m_database.getDatabase().getDataSource().getQueryDefinition("orders for customer and product").getCommand()); 247 final XParametersSupplier suppParams = UnoRuntime.queryInterface(XParametersSupplier.class, m_composer); 248 final XIndexAccess parameters = suppParams.getParameters(); 249 250 final String expectedParamNames[] = 251 252 { 253 "cname", 254 "Product Name" 255 }; 256 257 final int paramCount = parameters.getCount(); 258 assertTrue("composer did find wrong number of parameters in the nested queries.", 259 paramCount == expectedParamNames.length); 260 261 for (int i = 0; i < paramCount; ++i) 262 { 263 final XPropertySet parameter = UnoRuntime.queryInterface(XPropertySet.class, parameters.getByIndex(i)); 264 final String paramName = (String) parameter.getPropertyValue("Name"); 265 assertTrue("wrong parameter name at position " + (i + 1) + " (expected: " + expectedParamNames[i] + ", found: " + paramName + ")", 266 paramName.equals(expectedParamNames[i])); 267 268 } 269 } 270 catch (Exception e) 271 { 272 fail("caught an exception: " + e); 273 } 274 } 275 276 @Test testConditionByColumn()277 public void testConditionByColumn() 278 { 279 try 280 { 281 m_composer.setQuery("SELECT * FROM \"customers\""); 282 283 final Object initArgs[] = 284 285 { 286 new NamedValue("AutomaticAddition", Boolean.valueOf(true)) 287 }; 288 final String serviceName = "com.sun.star.beans.PropertyBag"; 289 final XPropertyContainer filter = UnoRuntime.queryInterface(XPropertyContainer.class, getMSF().createInstanceWithArguments(serviceName, initArgs)); 290 filter.addProperty("Name", PropertyAttribute.MAYBEVOID, "Comment"); 291 filter.addProperty("RealName", PropertyAttribute.MAYBEVOID, "Comment"); 292 filter.addProperty("TableName", PropertyAttribute.MAYBEVOID, "customers"); 293 filter.addProperty("Value", PropertyAttribute.MAYBEVOID, "Good one."); 294 filter.addProperty("Type", PropertyAttribute.MAYBEVOID, Integer.valueOf(DataType.LONGVARCHAR)); 295 final XPropertySet column = UnoRuntime.queryInterface(XPropertySet.class, filter); 296 297 m_composer.appendFilterByColumn(column, true, SQLFilterOperator.LIKE); 298 assertTrue("At least one row should exist", m_database.getConnection().createStatement().executeQuery(m_composer.getQuery()).next()); 299 300 } 301 catch (Exception e) 302 { 303 // this is an error: the query is expected to be parseable 304 fail("caught an exception: " + e); 305 } 306 } 307 impl_testDisjunctiveNormalForm(String _query, PropertyValue[][] _expectedDNF)308 private void impl_testDisjunctiveNormalForm(String _query, PropertyValue[][] _expectedDNF) 309 { 310 try 311 { 312 m_composer.setQuery(_query); 313 } 314 catch (Exception e) 315 { 316 // this is an error: the query is expected to be parseable 317 fail("caught an exception: " + e); 318 } 319 320 final PropertyValue[][] disjunctiveNormalForm = m_composer.getStructuredFilter(); 321 322 assertEquals("DNF: wrong number of rows", _expectedDNF.length, disjunctiveNormalForm.length); 323 for (int i = 0; i < _expectedDNF.length; ++i) 324 { 325 assertEquals("DNF: wrong number of columns in row " + i, _expectedDNF[i].length, disjunctiveNormalForm[i].length); 326 for (int j = 0; j < _expectedDNF[i].length; ++j) 327 { 328 assertEquals("DNF: wrong content in column " + j + ", row " + i, 329 _expectedDNF[i][j].Name, disjunctiveNormalForm[i][j].Name); 330 } 331 } 332 } 333 334 /** tests the disjunctive normal form functionality, aka the structured filter, 335 * of the composer 336 */ 337 @Test testDisjunctiveNormalForm()338 public void testDisjunctiveNormalForm() 339 { 340 // a simple case: WHERE clause simply is a combination of predicates knitted with AND 341 String query = 342 "SELECT \"customers\".\"Name\", " 343 + "\"customers\".\"Address\", " 344 + "\"customers\".\"City\", " 345 + "\"customers\".\"Postal\", " 346 + "\"products\".\"Name\" " 347 + "FROM \"orders\", \"customers\", \"orders_details\", \"products\" " 348 + "WHERE ( \"orders\".\"CustomerID\" = \"customers\".\"ID\" " 349 + "AND \"orders_details\".\"OrderID\" = \"orders\".\"ID\" " 350 + "AND \"orders_details\".\"ProductID\" = \"products\".\"ID\" " 351 + ") "; 352 353 impl_testDisjunctiveNormalForm(query, new PropertyValue[][] 354 { 355 new PropertyValue[] 356 { 357 new PropertyValue("CustomerID", SQLFilterOperator.EQUAL, "\"customers\".\"ID\"", PropertyState.DIRECT_VALUE), 358 new PropertyValue("OrderID", SQLFilterOperator.EQUAL, "\"orders\".\"ID\"", PropertyState.DIRECT_VALUE), 359 new PropertyValue("ProductID", SQLFilterOperator.EQUAL, "\"products\".\"ID\"", PropertyState.DIRECT_VALUE) 360 } 361 }); 362 363 // somewhat more challenging: One of the conjunction terms is a disjunction itself 364 query = 365 "SELECT \"customers\".\"Name\", " 366 + "\"customers\".\"Address\", " 367 + "\"customers\".\"City\", " 368 + "\"customers\".\"Postal\", " 369 + "\"products\".\"Name\" " 370 + "FROM \"orders\", \"customers\", \"orders_details\", \"products\" " 371 + "WHERE ( \"orders\".\"CustomerID\" = \"customers\".\"ID\" " 372 + "AND \"orders_details\".\"OrderID\" = \"orders\".\"ID\" " 373 + "AND \"orders_details\".\"ProductID\" = \"products\".\"ID\" " 374 + ") " 375 + "AND " 376 + "( \"products\".\"Name\" = 'Apples' " 377 + "OR \"products\".\"ID\" = 2 " 378 + ")"; 379 380 impl_testDisjunctiveNormalForm(query, new PropertyValue[][] 381 { 382 new PropertyValue[] 383 { 384 new PropertyValue("CustomerID", SQLFilterOperator.EQUAL, "\"customers\".\"ID\"", PropertyState.DIRECT_VALUE), 385 new PropertyValue("OrderID", SQLFilterOperator.EQUAL, "\"orders\".\"ID\"", PropertyState.DIRECT_VALUE), 386 new PropertyValue("ProductID", SQLFilterOperator.EQUAL, "\"products\".\"ID\"", PropertyState.DIRECT_VALUE), 387 new PropertyValue("Name", SQLFilterOperator.EQUAL, "Apples", PropertyState.DIRECT_VALUE) 388 }, 389 new PropertyValue[] 390 { 391 new PropertyValue("CustomerID", SQLFilterOperator.EQUAL, "\"customers\".\"ID\"", PropertyState.DIRECT_VALUE), 392 new PropertyValue("OrderID", SQLFilterOperator.EQUAL, "\"orders\".\"ID\"", PropertyState.DIRECT_VALUE), 393 new PropertyValue("ProductID", SQLFilterOperator.EQUAL, "\"products\".\"ID\"", PropertyState.DIRECT_VALUE), 394 new PropertyValue("ID", SQLFilterOperator.EQUAL, Integer.valueOf(2), PropertyState.DIRECT_VALUE) 395 } 396 }); 397 398 } 399 } 400