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