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 com.sun.star.wizards.db; 24 25 26 // import com.sun.star.lang.IllegalArgumentException; 27 // import com.sun.star.lang.WrappedTargetException; 28 import com.sun.star.lang.XMultiServiceFactory; 29 import com.sun.star.beans.*; 30 // import com.sun.star.container.NoSuchElementException; 31 import com.sun.star.container.XIndexAccess; 32 import com.sun.star.container.XNameAccess; 33 import com.sun.star.sdbcx.XColumnsSupplier; 34 // import com.sun.star.sdb.XColumn; 35 import com.sun.star.sdb.XSingleSelectQueryComposer; 36 import com.sun.star.sdb.XSingleSelectQueryAnalyzer; 37 import com.sun.star.ui.dialogs.XExecutableDialog; 38 import com.sun.star.uno.AnyConverter; 39 import com.sun.star.uno.Exception; 40 import com.sun.star.uno.UnoRuntime; 41 import com.sun.star.sdbc.SQLException; 42 import com.sun.star.lang.XInitialization; 43 import com.sun.star.awt.XWindow; 44 import com.sun.star.sdb.SQLFilterOperator; 45 46 import com.sun.star.wizards.common.*; 47 import java.util.ArrayList; 48 49 public class SQLQueryComposer 50 { 51 52 public XColumnsSupplier xColSuppl; 53 // XSQLQueryComposer xSQLQueryComposer; 54 QueryMetaData CurDBMetaData; 55 // String m_sSelectClause; 56 // String m_sFromClause; 57 public XSingleSelectQueryAnalyzer m_xQueryAnalyzer; 58 ArrayList<CommandName> composedCommandNames = new ArrayList<CommandName>(1); 59 private XSingleSelectQueryComposer m_queryComposer; 60 XMultiServiceFactory xMSF; 61 boolean bincludeGrouping = true; 62 SQLQueryComposer(QueryMetaData _CurDBMetaData)63 public SQLQueryComposer(QueryMetaData _CurDBMetaData) 64 { 65 try 66 { 67 this.CurDBMetaData = _CurDBMetaData; 68 xMSF = UnoRuntime.queryInterface(XMultiServiceFactory.class, CurDBMetaData.DBConnection); 69 final Object oQueryComposer = xMSF.createInstance("com.sun.star.sdb.SingleSelectQueryComposer"); 70 m_xQueryAnalyzer = UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class, oQueryComposer); 71 m_queryComposer = UnoRuntime.queryInterface(XSingleSelectQueryComposer.class, m_xQueryAnalyzer); 72 } 73 catch (Exception exception) 74 { 75 exception.printStackTrace(System.out); 76 } 77 } 78 addtoSelectClause(String DisplayFieldName)79 private boolean addtoSelectClause(String DisplayFieldName) throws SQLException 80 { 81 return !(bincludeGrouping && CurDBMetaData.xDBMetaData.supportsGroupByUnrelated() && CurDBMetaData.GroupFieldNames != null && JavaTools.FieldInList(CurDBMetaData.GroupFieldNames, DisplayFieldName) > -1); 82 } 83 getSelectClause(boolean _baddAliasFieldNames)84 public String getSelectClause(boolean _baddAliasFieldNames) throws SQLException 85 { 86 // getFromClause() must be called first to populate composedCommandNames, 87 // but it's idempotent, so let's call it now in case the caller didn't already: 88 getFromClause(); 89 90 String sSelectBaseClause = "SELECT "; 91 String sSelectClause = sSelectBaseClause; 92 for (int i = 0; i < CurDBMetaData.FieldColumns.length; i++) 93 { 94 if (addtoSelectClause(CurDBMetaData.FieldColumns[i].getDisplayFieldName())) 95 { 96 int iAggregate = CurDBMetaData.getAggregateIndex(CurDBMetaData.FieldColumns[i].getDisplayFieldName()); 97 if (iAggregate > -1) 98 { 99 sSelectClause += CurDBMetaData.AggregateFieldNames[iAggregate][1] + "(" + getComposedAliasFieldName(CurDBMetaData.AggregateFieldNames[iAggregate][0]) + ")"; 100 if (_baddAliasFieldNames) 101 { 102 sSelectClause += getAliasFieldNameClause(CurDBMetaData.AggregateFieldNames[iAggregate][0]); 103 } 104 } 105 else 106 { 107 sSelectClause += getComposedAliasFieldName(CurDBMetaData.FieldColumns[i].getDisplayFieldName()); 108 if (_baddAliasFieldNames) 109 { 110 sSelectClause += getAliasFieldNameClause(CurDBMetaData.FieldColumns[i].getDisplayFieldName()); 111 } 112 } 113 sSelectClause += ", "; 114 } 115 } 116 // TODO: little bit unhandy version of remove the append 'comma' at the end 117 if (sSelectClause.equals(sSelectBaseClause)) 118 { 119 sSelectClause = sSelectClause.substring(0, sSelectClause.length() - 1); 120 } 121 else 122 { 123 sSelectClause = sSelectClause.substring(0, sSelectClause.length() - 2); 124 } 125 return sSelectClause; 126 } 127 getAliasFieldNameClause(String _FieldName)128 public String getAliasFieldNameClause(String _FieldName) 129 { 130 String FieldTitle = CurDBMetaData.getFieldTitle(_FieldName); 131 if (!FieldTitle.equals(_FieldName)) 132 { 133 return " AS " + CommandName.quoteName(FieldTitle, CurDBMetaData.getIdentifierQuote()); 134 } 135 else 136 { 137 return ""; 138 } 139 } 140 appendFilterConditions()141 public void appendFilterConditions() throws SQLException 142 { 143 try 144 { 145 for (int i = 0; i < CurDBMetaData.getFilterConditions().length; i++) 146 { 147 m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); 148 } 149 } 150 catch (Exception exception) 151 { 152 exception.printStackTrace(System.out); 153 } 154 } 155 prependSortingCriteria()156 public void prependSortingCriteria() throws SQLException 157 { 158 XIndexAccess xColumnIndexAccess = m_xQueryAnalyzer.getOrderColumns(); 159 m_queryComposer.setOrder(""); 160 for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++) 161 { 162 appendSortingCriterion(i, false); 163 } 164 for (int i = 0; i < xColumnIndexAccess.getCount(); i++) 165 { 166 try 167 { 168 XPropertySet xColumnPropertySet = UnoRuntime.queryInterface(XPropertySet.class, xColumnIndexAccess.getByIndex(i)); 169 String sName = (String) xColumnPropertySet.getPropertyValue(PropertyNames.PROPERTY_NAME); 170 if (JavaTools.FieldInTable(CurDBMetaData.getSortFieldNames(), sName) == -1) 171 { 172 boolean bascend = AnyConverter.toBoolean(xColumnPropertySet.getPropertyValue("IsAscending")); 173 m_queryComposer.appendOrderByColumn(xColumnPropertySet, bascend); 174 } 175 } 176 catch (Exception e) 177 { 178 e.printStackTrace(System.out); 179 } 180 } 181 } 182 appendSortingCriterion(int _SortIndex, boolean _baddAliasFieldNames)183 private void appendSortingCriterion(int _SortIndex, boolean _baddAliasFieldNames) throws SQLException 184 { 185 String sSortValue = CurDBMetaData.getSortFieldNames()[_SortIndex][0]; 186 XPropertySet xColumn = CurDBMetaData.getColumnObjectByFieldName(sSortValue, _baddAliasFieldNames); 187 188 String sSort = CurDBMetaData.getSortFieldNames()[_SortIndex][1]; 189 boolean bascend = (sSort.equals("ASC")); 190 m_queryComposer.appendOrderByColumn(xColumn, bascend); 191 } 192 appendSortingcriteria(boolean _baddAliasFieldNames)193 public void appendSortingcriteria(boolean _baddAliasFieldNames) throws SQLException 194 { 195 String sOrder = ""; 196 m_queryComposer.setOrder(""); 197 for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++) 198 { 199 String sSortValue = CurDBMetaData.getSortFieldNames()[i][0]; 200 int iAggregate = CurDBMetaData.getAggregateIndex(sSortValue); 201 if (iAggregate > -1) 202 { 203 sOrder = m_xQueryAnalyzer.getOrder(); 204 if (sOrder.length() > 0) 205 { 206 sOrder += ", "; 207 } 208 sOrder += CurDBMetaData.AggregateFieldNames[iAggregate][1] + "(" + CurDBMetaData.AggregateFieldNames[iAggregate][0] + ")"; 209 sOrder += " " + CurDBMetaData.getSortFieldNames()[i][1]; 210 m_queryComposer.setOrder(sOrder); 211 } 212 else 213 { 214 appendSortingCriterion(i, _baddAliasFieldNames); 215 } 216 sOrder = m_xQueryAnalyzer.getOrder(); 217 } 218 // just for debug! 219 sOrder = m_queryComposer.getOrder(); 220 } 221 appendGroupByColumns(boolean _baddAliasFieldNames)222 public void appendGroupByColumns(boolean _baddAliasFieldNames) throws SQLException 223 { 224 for (int i = 0; i < CurDBMetaData.GroupFieldNames.length; i++) 225 { 226 XPropertySet xColumn = CurDBMetaData.getColumnObjectByFieldName(CurDBMetaData.GroupFieldNames[i], _baddAliasFieldNames); 227 m_queryComposer.appendGroupByColumn(xColumn); 228 } 229 } 230 setDBMetaData(QueryMetaData _oDBMetaData)231 public void setDBMetaData(QueryMetaData _oDBMetaData) 232 { 233 this.CurDBMetaData = _oDBMetaData; 234 } 235 replaceConditionsByAlias(PropertyValue _filterconditions[][])236 private PropertyValue[][] replaceConditionsByAlias(PropertyValue _filterconditions[][]) 237 { 238 XColumnsSupplier columnSup = UnoRuntime.queryInterface(XColumnsSupplier.class, m_xQueryAnalyzer); 239 XNameAccess columns = columnSup.getColumns(); 240 for (int n = 0; n < _filterconditions.length; n++) 241 { 242 for (int m = 0; m < _filterconditions[n].length; m++) 243 { 244 // _filterconditions[n][m].Name = getComposedAliasFieldName(_filterconditions[n][m].Name); 245 final String aliasName = getComposedAliasFieldName(_filterconditions[n][m].Name); 246 if ( columns.hasByName(aliasName)) 247 _filterconditions[n][m].Name = aliasName; 248 } 249 } 250 return _filterconditions; 251 } 252 getQuery()253 public String getQuery() 254 { 255 return m_xQueryAnalyzer.getQuery(); 256 } 257 getFromClause()258 public StringBuilder getFromClause() 259 { 260 StringBuilder sFromClause = new StringBuilder("FROM"); 261 composedCommandNames.clear(); 262 String[] sCommandNames = CurDBMetaData.getIncludedCommandNames(); 263 for (int i = 0; i < sCommandNames.length; i++) 264 { 265 CommandName curCommandName = new CommandName(CurDBMetaData, sCommandNames[i]); //(setComposedCommandName) 266 curCommandName.setAliasName(getuniqueAliasName(curCommandName.getTableName())); 267 sFromClause.append(" ").append(curCommandName.getComposedName()).append(" ").append(quoteName(curCommandName.getAliasName())); 268 if (i < sCommandNames.length - 1) 269 { 270 sFromClause.append(", "); 271 } 272 // fill composedCommandNames 273 composedCommandNames.add(curCommandName); 274 } 275 return sFromClause; 276 } 277 setQueryCommand(XWindow _xParentWindow, boolean _bincludeGrouping, boolean _baddAliasFieldNames)278 public boolean setQueryCommand(XWindow _xParentWindow, boolean _bincludeGrouping, boolean _baddAliasFieldNames) 279 { 280 return setQueryCommand(_xParentWindow, _bincludeGrouping, _baddAliasFieldNames, true); 281 } 282 setQueryCommand(XWindow _xParentWindow, boolean _bincludeGrouping, boolean _baddAliasFieldNames, boolean addQuery)283 public boolean setQueryCommand(XWindow _xParentWindow, boolean _bincludeGrouping, boolean _baddAliasFieldNames, boolean addQuery) 284 { 285 try 286 { 287 bincludeGrouping = _bincludeGrouping; 288 if (addQuery) 289 { 290 StringBuilder fromClause = getFromClause(); 291 String sSelectClause = getSelectClause(_baddAliasFieldNames); 292 StringBuilder queryclause = new StringBuilder(sSelectClause).append(" ").append(fromClause); 293 m_xQueryAnalyzer.setQuery(queryclause.toString()); 294 if (CurDBMetaData.getFilterConditions() != null && CurDBMetaData.getFilterConditions().length > 0) 295 { 296 CurDBMetaData.setFilterConditions(replaceConditionsByAlias(CurDBMetaData.getFilterConditions())); 297 m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); 298 } 299 } 300 if (_bincludeGrouping) 301 { 302 appendGroupByColumns(_baddAliasFieldNames); 303 if (CurDBMetaData.GroupByFilterConditions.length > 0) 304 { 305 m_queryComposer.setStructuredHavingClause(CurDBMetaData.GroupByFilterConditions); 306 } 307 } 308 appendSortingcriteria(_baddAliasFieldNames); 309 310 return true; 311 } 312 catch (Exception exception) 313 { 314 exception.printStackTrace(System.out); 315 displaySQLErrorDialog(exception, _xParentWindow); 316 return false; 317 } 318 } 319 getComposedAliasFieldName(String _fieldname)320 private String getComposedAliasFieldName(String _fieldname) 321 { 322 FieldColumn CurFieldColumn = CurDBMetaData.getFieldColumnByDisplayName(_fieldname); 323 CommandName curComposedCommandName = getComposedCommandByDisplayName(CurFieldColumn.getCommandName()); 324 if (curComposedCommandName == null) 325 { 326 return _fieldname; 327 } 328 String curAliasName = curComposedCommandName.getAliasName(); 329 return quoteName(curAliasName) + "." + quoteName(CurFieldColumn.getFieldName()); 330 } 331 getComposedCommandByAliasName(String _AliasName)332 private CommandName getComposedCommandByAliasName(String _AliasName) 333 { 334 if (composedCommandNames != null) 335 { 336 for (CommandName commandName : composedCommandNames) 337 { 338 if (commandName.getAliasName().equals(_AliasName)) 339 { 340 return commandName; 341 } 342 } 343 } 344 return null; 345 } 346 getComposedCommandByDisplayName(String _DisplayName)347 public CommandName getComposedCommandByDisplayName(String _DisplayName) 348 { 349 if (composedCommandNames != null) 350 { 351 for (CommandName commandName : composedCommandNames) 352 { 353 if (commandName.getDisplayName().equals(_DisplayName)) 354 { 355 return commandName; 356 } 357 } 358 } 359 return null; 360 } 361 getuniqueAliasName(String _TableName)362 public String getuniqueAliasName(String _TableName) 363 { 364 int a = 0; 365 String AliasName = ""; 366 boolean bAliasNameexists = true; 367 String locAliasName = _TableName; 368 while (bAliasNameexists) 369 { 370 bAliasNameexists = (getComposedCommandByAliasName(locAliasName) != null); 371 if (bAliasNameexists) 372 { 373 a++; 374 locAliasName = _TableName + "_" + String.valueOf(a); 375 } 376 else 377 { 378 AliasName = locAliasName; 379 } 380 } 381 return AliasName; 382 } 383 quoteName(String _sname)384 private String quoteName(String _sname) 385 { 386 return CommandName.quoteName(_sname, CurDBMetaData.getIdentifierQuote()); 387 } 388 displaySQLErrorDialog(Exception _exception, XWindow _xParentWindow)389 public void displaySQLErrorDialog(Exception _exception, XWindow _xParentWindow) 390 { 391 try 392 { 393 Object oErrorDialog = CurDBMetaData.xMSF.createInstance("com.sun.star.sdb.ErrorMessageDialog"); 394 XInitialization xInitialize = UnoRuntime.queryInterface(XInitialization.class, oErrorDialog); 395 XExecutableDialog xExecute = UnoRuntime.queryInterface(XExecutableDialog.class, oErrorDialog); 396 PropertyValue[] rDispatchArguments = new PropertyValue[3]; 397 rDispatchArguments[0] = Properties.createProperty(PropertyNames.PROPERTY_TITLE, Configuration.getProductName(CurDBMetaData.xMSF) + " Base"); 398 rDispatchArguments[1] = Properties.createProperty("ParentWindow", _xParentWindow); 399 rDispatchArguments[2] = Properties.createProperty("SQLException", _exception); 400 xInitialize.initialize(rDispatchArguments); 401 xExecute.execute(); 402 //TODO dispose??? 403 } 404 catch (Exception typeexception) 405 { 406 typeexception.printStackTrace(System.out); 407 } 408 } 409 410 /** 411 * retrieves a normalized structured filter 412 * 413 * <p>XSingleSelectQueryComposer.getStructuredFilter has a strange habit of returning the predicate (equal, not equal, etc) 414 * effectively twice: Once as SQLFilterOperator, and once in the value. That is, if you have a term "column <> 3", then 415 * you'll get an SQLFilterOperator.NOT_EQUAL (which is fine), <strong>and</strong> the textual value of the condition 416 * will read "<> 3". The latter is strange enough, but even more strange is that this behavior is not even consistent: 417 * for SQLFilterOperator.EQUAL, the "=" sign is not include in the textual value.</p> 418 * 419 * <p>To abstract from this weirdness, use this function here, which strips the unwanted tokens from the textual value 420 * representation.</p> 421 */ getNormalizedStructuredFilter()422 public PropertyValue[][] getNormalizedStructuredFilter() 423 { 424 final PropertyValue[][] structuredFilter = m_queryComposer.getStructuredFilter(); 425 for (int i = 0; i < structuredFilter.length; ++i) 426 { 427 for (int j = 0; j < structuredFilter[i].length; ++j) 428 { 429 if (!(structuredFilter[i][j].Value instanceof String)) 430 { 431 continue; 432 } 433 final StringBuffer textualValue = new StringBuffer((String) structuredFilter[i][j].Value); 434 switch (structuredFilter[i][j].Handle) 435 { 436 case SQLFilterOperator.EQUAL: 437 break; 438 case SQLFilterOperator.NOT_EQUAL: 439 case SQLFilterOperator.LESS_EQUAL: 440 case SQLFilterOperator.GREATER_EQUAL: 441 textualValue.delete(0, 2); 442 break; 443 case SQLFilterOperator.LESS: 444 case SQLFilterOperator.GREATER: 445 textualValue.delete(0, 1); 446 break; 447 case SQLFilterOperator.NOT_LIKE: 448 textualValue.delete(0, 8); 449 break; 450 case SQLFilterOperator.LIKE: 451 textualValue.delete(0, 4); 452 break; 453 case SQLFilterOperator.SQLNULL: 454 textualValue.delete(0, 7); 455 break; 456 case SQLFilterOperator.NOT_SQLNULL: 457 textualValue.delete(0, 11); 458 break; 459 } 460 structuredFilter[i][j].Value = textualValue.toString().trim(); 461 } 462 } 463 return structuredFilter; 464 } 465 getQueryComposer()466 public XSingleSelectQueryComposer getQueryComposer() 467 { 468 return m_queryComposer; 469 } 470 } 471