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