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