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.report;
24 
25 import com.sun.star.beans.PropertyVetoException;
26 import com.sun.star.beans.UnknownPropertyException;
27 import com.sun.star.beans.XPropertySet;
28 import com.sun.star.container.NoSuchElementException;
29 import com.sun.star.container.XIndexAccess;
30 import com.sun.star.container.XNameAccess;
31 import com.sun.star.lang.IllegalArgumentException;
32 import com.sun.star.lang.IndexOutOfBoundsException;
33 import com.sun.star.lang.WrappedTargetException;
34 import com.sun.star.lang.XComponent;
35 import com.sun.star.sdb.CommandType;
36 import com.sun.star.sdb.XCompletedExecution;
37 import com.sun.star.sdb.XParametersSupplier;
38 import com.sun.star.sdb.XQueriesSupplier;
39 import com.sun.star.sdb.XSingleSelectQueryComposer;
40 import com.sun.star.sdb.tools.XConnectionTools;
41 import com.sun.star.sdbc.SQLException;
42 import com.sun.star.sdbc.XConnection;
43 import com.sun.star.sdbc.XParameters;
44 import com.sun.star.sdbc.XRowSet;
45 import com.sun.star.task.XInteractionHandler;
46 import com.sun.star.uno.Exception;
47 import com.sun.star.uno.UnoRuntime;
48 import com.sun.star.uno.XComponentContext;
49 
50 import java.lang.reflect.Method;
51 
52 import java.math.BigDecimal;
53 
54 import java.util.ArrayList;
55 import java.util.HashMap;
56 import java.util.Iterator;
57 import java.util.List;
58 import java.util.Map;
59 import java.util.logging.Level;
60 import java.util.logging.Logger;
61 
62 import org.apache.commons.logging.Log;
63 import org.apache.commons.logging.LogFactory;
64 
65 
66 /**
67  * Very primitive implementation, just to show how this could be used ...
68  *
69  */
70 public class SDBCReportDataFactory implements DataSourceFactory
71 {
72 
73     private static final String ESCAPEPROCESSING = "EscapeProcessing";
74 
75     private class RowSetProperties
76     {
77 
78         final Boolean escapeProcessing;
79         final int commandType;
80         final Integer maxRows;
81         final String command;
82         final String filter;
83 
RowSetProperties(final Boolean escapeProcessing, final int commandType, final String command, final String filter, final Integer maxRows)84         public RowSetProperties(final Boolean escapeProcessing, final int commandType, final String command, final String filter, final Integer maxRows)
85         {
86             this.escapeProcessing = escapeProcessing;
87             this.commandType = commandType;
88             this.command = command;
89             this.filter = filter;
90             this.maxRows = maxRows;
91         }
92 
equals(Object obj)93         public boolean equals(Object obj)
94         {
95             if (obj == null)
96             {
97                 return false;
98             }
99             if (getClass() != obj.getClass())
100             {
101                 return false;
102             }
103             final RowSetProperties other = (RowSetProperties) obj;
104             if (this.escapeProcessing != other.escapeProcessing && (this.escapeProcessing == null || !this.escapeProcessing.equals(other.escapeProcessing)))
105             {
106                 return false;
107             }
108             if (this.commandType != other.commandType)
109             {
110                 return false;
111             }
112             if (this.maxRows != other.maxRows && (this.maxRows == null || !this.maxRows.equals(other.maxRows)))
113             {
114                 return false;
115             }
116             if ((this.command == null) ? (other.command != null) : !this.command.equals(other.command))
117             {
118                 return false;
119             }
120             if ((this.filter == null) ? (other.filter != null) : !this.filter.equals(other.filter))
121             {
122                 return false;
123             }
124             return true;
125         }
126 
hashCode()127         public int hashCode()
128         {
129             int hash = 3;
130             hash = 59 * hash + (this.escapeProcessing != null ? this.escapeProcessing.hashCode() : 0);
131             hash = 59 * hash + this.commandType;
132             hash = 59 * hash + (this.maxRows != null ? this.maxRows.hashCode() : 0);
133             hash = 59 * hash + (this.command != null ? this.command.hashCode() : 0);
134             hash = 59 * hash + (this.filter != null ? this.filter.hashCode() : 0);
135             return hash;
136         }
137     }
138 
139     class ParameterDefinition
140     {
141 
142         int parameterCount = 0;
143         private ArrayList parameterIndex = new ArrayList();
144     }
145     private static final Log LOGGER = LogFactory.getLog(SDBCReportDataFactory.class);
146     public static final String COMMAND_TYPE = "command-type";
147     public static final String ESCAPE_PROCESSING = "escape-processing";
148     public static final String GROUP_EXPRESSIONS = "group-expressions";
149     public static final String MASTER_VALUES = "master-values";
150     public static final String MASTER_COLUMNS = "master-columns";
151     public static final String DETAIL_COLUMNS = "detail-columns";
152     public static final String UNO_FILTER = "Filter";
153     private static final String APPLY_FILTER = "ApplyFilter";
154     private static final String UNO_COMMAND = "Command";
155     private static final String UNO_ORDER = "Order";
156     private static final String UNO_APPLY_FILTER = "ApplyFilter";
157     private static final String UNO_COMMAND_TYPE = "CommandType";
158     private final XConnection connection;
159     private final XComponentContext m_cmpCtx;
160     private static final int FAILED = 0;
161     private static final int DONE = 1;
162     private static final int RETRIEVE_COLUMNS = 2;
163     private static final int RETRIEVE_OBJECT = 3;
164     private static final int HANDLE_QUERY = 4;
165     private static final int HANDLE_TABLE = 5;
166     private static final int HANDLE_SQL = 6;
167     private final Map rowSetProperties = new HashMap();
168     private final Map parameterMap = new HashMap();
169     private boolean rowSetCreated = false;
170 
SDBCReportDataFactory(final XComponentContext cmpCtx, final XConnection connection)171     public SDBCReportDataFactory(final XComponentContext cmpCtx, final XConnection connection)
172     {
173         this.connection = connection;
174         m_cmpCtx = cmpCtx;
175     }
176 
queryData(final String command, final Map parameters)177     public DataSource queryData(final String command, final Map parameters) throws DataSourceException
178     {
179         try
180         {
181             if (command == null)
182             {
183                 return new SDBCReportData(null);
184             }
185             int commandType = CommandType.COMMAND;
186             final String commandTypeValue = (String) parameters.get(COMMAND_TYPE);
187             if (commandTypeValue != null)
188             {
189                 if ("query".equals(commandTypeValue))
190                 {
191                     commandType = CommandType.QUERY;
192                 }
193                 else if ("table".equals(commandTypeValue))
194                 {
195                     commandType = CommandType.TABLE;
196                 }
197                 else
198                 {
199                     commandType = CommandType.COMMAND;
200                 }
201             }
202             final Boolean escapeProcessing = (Boolean) parameters.get(ESCAPE_PROCESSING);
203             final String filter = (String) parameters.get(UNO_FILTER);
204             final Integer maxRows = (Integer) parameters.get("MaxRows");
205             final RowSetProperties rowSetProps = new RowSetProperties(escapeProcessing, commandType, command, filter, maxRows);
206 
207             final Object[] p = createRowSet(rowSetProps, parameters);
208             final XRowSet rowSet = (XRowSet) p[0];
209 
210             if (command.length() != 0)
211             {
212                 final ParameterDefinition paramDef = (ParameterDefinition) p[1];
213                 fillParameter(parameters, rowSet, paramDef);
214                 rowSetCreated = rowSetCreated && (maxRows == null || maxRows == 0);
215 
216                 final XCompletedExecution execute = (XCompletedExecution) UnoRuntime.queryInterface(XCompletedExecution.class, rowSet);
217                 if (rowSetCreated && execute != null && paramDef.parameterCount > 0)
218                 {
219                     final XInteractionHandler handler = (XInteractionHandler) UnoRuntime.queryInterface(XInteractionHandler.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.InteractionHandler", m_cmpCtx));
220                     execute.executeWithCompletion(handler);
221                 }
222                 else
223                 {
224                     rowSet.execute();
225                 }
226             }
227 
228             rowSetCreated = false;
229             return new SDBCReportData(rowSet);
230         }
231         catch (Exception ex)
232         {
233             rowSetCreated = false;
234             throw new DataSourceException(ex.getMessage(), ex);
235         }
236     }
237 
getOrderStatement(final int commandType, final String command, final List groupExpressions)238     private String getOrderStatement(final int commandType, final String command, final List groupExpressions)
239     {
240         final StringBuffer order = new StringBuffer();
241         final int count = groupExpressions.size();
242         if (count != 0)
243         {
244             try
245             {
246                 final String quote = connection.getMetaData().getIdentifierQuoteString();
247                 final XComponent[] hold = new XComponent[1];
248                 final XNameAccess columns = getFieldsByCommandDescriptor(commandType, command, hold);
249                 if (columns != null)
250                 {
251                     for (int i = 0; i < count; i++)
252                     {
253                         final Object[] pair = (Object[]) groupExpressions.get(i);
254                         String expression = (String) pair[0];
255 
256                         if (!expression.startsWith(quote) && columns.hasByName(expression))
257                         {
258                             XPropertySet column;
259                             try
260                             {
261                                 column = UnoRuntime.queryInterface(XPropertySet.class, columns.getByName(expression));
262                                 expression = quote + column.getPropertyValue("TableName") + quote + "." + quote + expression + quote;
263                             }
264                             catch (Exception ex)
265                             {
266                                 Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex);
267                                 expression = quote + expression + quote;
268                             }
269                         }
270                         expression = expression.trim(); // Trim away white spaces
271 
272                         if (expression.length() > 0)
273                         {
274                             order.append(expression);
275                             if (order.length() > 0)
276                             {
277                                 order.append(' ');
278                             }
279                             final String sorting = (String) pair[1];
280                             if (sorting == null || sorting.equals(OfficeToken.FALSE))
281                             {
282                                 order.append("DESC");
283                             }
284                             if ((i + 1) < count)
285                             {
286                                 order.append(", ");
287                             }
288                         }
289                     }
290                 }
291             }
292             catch (SQLException ex)
293             {
294                 LOGGER.error("ReportProcessing failed", ex);
295             }
296         }
297         return order.toString();
298     }
299 
getFieldsByCommandDescriptor(final int commandType, final String command, final XComponent[] out)300     private XNameAccess getFieldsByCommandDescriptor(final int commandType, final String command, final XComponent[] out) throws SQLException
301     {
302         final Class[] parameter = new Class[3];
303         parameter[0] = int.class;
304         parameter[1] = String.class;
305         parameter[2] = out.getClass();
306         final XConnectionTools tools = (XConnectionTools) UnoRuntime.queryInterface(XConnectionTools.class, connection);
307         try
308         {
309             tools.getClass().getMethod("getFieldsByCommandDescriptor", parameter);
310             return tools.getFieldsByCommandDescriptor(commandType, command, out);
311         }
312         catch (NoSuchMethodException ex)
313         {
314         }
315 
316         throw new SQLException();
317     }
318 
getComposer(final XConnectionTools tools, final String command, final int commandType)319     private XSingleSelectQueryComposer getComposer(final XConnectionTools tools,
320             final String command,
321             final int commandType)
322     {
323         final Class[] parameter = new Class[2];
324         parameter[0] = int.class;
325         parameter[1] = String.class;
326         try
327         {
328             final Object[] param = new Object[2];
329             param[0] = commandType;
330             param[1] = command;
331             final Method call = tools.getClass().getMethod("getComposer", parameter);
332             return (XSingleSelectQueryComposer) call.invoke(tools, param);
333         }
334         catch (NoSuchMethodException ex)
335         {
336         }
337         catch (IllegalAccessException ex)
338         {
339             // should not happen
340             // assert False
341         }
342         catch (java.lang.reflect.InvocationTargetException ex)
343         {
344             // should not happen
345             // assert False
346         }
347 
348         return null;
349     }
350 
fillParameter(final Map parameters, final XRowSet rowSet, final ParameterDefinition paramDef)351     private void fillParameter(final Map parameters,
352             final XRowSet rowSet, final ParameterDefinition paramDef)
353             throws SQLException,
354             UnknownPropertyException,
355             PropertyVetoException,
356             IllegalArgumentException,
357             WrappedTargetException
358     {
359         final ArrayList masterValues = (ArrayList) parameters.get(MASTER_VALUES);
360         if (masterValues != null && !masterValues.isEmpty())
361         {
362             final XParameters para = (XParameters) UnoRuntime.queryInterface(XParameters.class, rowSet);
363 
364             for (int i = 0;
365                     i < masterValues.size();
366                     i++)
367             {
368                 Object object = masterValues.get(i);
369                 if (object instanceof BigDecimal)
370                 {
371                     object = ((BigDecimal) object).toString();
372                 }
373                 final Integer pos = (Integer) paramDef.parameterIndex.get(i);
374                 para.setObject(pos + 1, object);
375             }
376         }
377     }
378 
createRowSet(final RowSetProperties rowSetProps, final Map parameters)379     private final Object[] createRowSet(final RowSetProperties rowSetProps, final Map parameters)
380             throws Exception
381     {
382         final ArrayList detailColumns = (ArrayList) parameters.get(DETAIL_COLUMNS);
383         if (rowSetProperties.containsKey(rowSetProps) && detailColumns != null && !detailColumns.isEmpty())
384         {
385             return new Object[]
386                     {
387                         rowSetProperties.get(rowSetProps), parameterMap.get(rowSetProps)
388                     };
389         }
390 
391         rowSetCreated = true;
392         final XRowSet rowSet = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.RowSet", m_cmpCtx));
393         final XPropertySet rowSetProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, rowSet);
394 
395         rowSetProp.setPropertyValue("ActiveConnection", connection);
396         rowSetProp.setPropertyValue(ESCAPEPROCESSING, rowSetProps.escapeProcessing);
397         rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(rowSetProps.commandType));
398         rowSetProp.setPropertyValue(UNO_COMMAND, rowSetProps.command);
399 
400         if (rowSetProps.filter != null)
401         {
402             rowSetProp.setPropertyValue("Filter", rowSetProps.filter);
403             rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.valueOf(rowSetProps.filter.length() != 0));
404         }
405         else
406         {
407             rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.FALSE);
408         }
409 
410         if (rowSetProps.maxRows != null)
411         {
412             rowSetProp.setPropertyValue("MaxRows", rowSetProps.maxRows);
413         }
414 
415         final XConnectionTools tools = (XConnectionTools) UnoRuntime.queryInterface(XConnectionTools.class, connection);
416         fillOrderStatement(rowSetProps.command, rowSetProps.commandType, parameters, tools, rowSetProp);
417         final ParameterDefinition paramDef = createParameter(parameters, tools, rowSetProps, rowSet);
418 
419         rowSetProperties.put(rowSetProps, rowSet);
420         parameterMap.put(rowSetProps, paramDef);
421 
422         return new Object[]
423                 {
424                     rowSet, paramDef
425                 };
426     }
427 
createParameter(final Map parameters, final XConnectionTools tools, RowSetProperties rowSetProps, final XRowSet rowSet)428     private ParameterDefinition createParameter(final Map parameters,
429             final XConnectionTools tools,
430             RowSetProperties rowSetProps, final XRowSet rowSet)
431             throws SQLException,
432             UnknownPropertyException,
433             PropertyVetoException,
434             IllegalArgumentException,
435             WrappedTargetException
436     {
437         final ParameterDefinition paramDef = new ParameterDefinition();
438         final XSingleSelectQueryComposer composer = getComposer(tools, rowSetProps.command, rowSetProps.commandType);
439         if (composer != null)
440         {
441             final XPropertySet rowSetProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, rowSet);
442             if ((Boolean) rowSetProp.getPropertyValue(APPLY_FILTER))
443             {
444                 composer.setFilter((String) rowSetProp.getPropertyValue("Filter"));
445             }
446             // get old parameter count
447             final ArrayList detailColumns = (ArrayList) parameters.get(DETAIL_COLUMNS);
448             final ArrayList handledColumns = new ArrayList();
449             final XParametersSupplier paraSup = (XParametersSupplier) UnoRuntime.queryInterface(XParametersSupplier.class, composer);
450             if (paraSup != null)
451             {
452                 final XIndexAccess params = paraSup.getParameters();
453                 if (params != null)
454                 {
455                     final int oldParameterCount = params.getCount();
456                     paramDef.parameterCount = oldParameterCount;
457                     if (detailColumns != null)
458                     {
459                         for (int i = 0; i < oldParameterCount; i++)
460                         {
461                             try
462                             {
463                                 final XPropertySet parameter = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, params.getByIndex(i));
464                                 if (parameter != null)
465                                 {
466                                     final String name = (String) parameter.getPropertyValue("Name");
467                                     for (int j = 0; j < detailColumns.size(); j++)
468                                     {
469                                         if (name.equals(detailColumns.get(j)))
470                                         {
471                                             handledColumns.add(name);
472                                             paramDef.parameterIndex.add(i);
473                                             --paramDef.parameterCount;
474                                             break;
475                                         }
476                                     }
477                                 }
478                             }
479                             catch (IndexOutOfBoundsException ex)
480                             {
481                                 Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex);
482                             }
483                         }
484                     }
485                 }
486             }
487             final ArrayList masterValues = (ArrayList) parameters.get(MASTER_VALUES);
488             if (masterValues != null && !masterValues.isEmpty() && paramDef.parameterIndex.size() != detailColumns.size())
489             {
490                 // Vector masterColumns = (Vector) parameters.get("master-columns");
491 
492                 // create the new filter
493                 final String quote = connection.getMetaData().getIdentifierQuoteString();
494                 final StringBuffer oldFilter = new StringBuffer();
495                 oldFilter.append(composer.getFilter());
496                 if (oldFilter.length() != 0)
497                 {
498                     oldFilter.append(" AND ");
499                 }
500                 int newParamterCounter = 1;
501                 for (final Iterator it = detailColumns.iterator(); it.hasNext();
502                         ++newParamterCounter)
503                 {
504                     final String detail = (String) it.next();
505                     if (!handledColumns.contains(detail))
506                     {
507                         //String master = (String) masterIt.next();
508                         oldFilter.append(quote);
509                         oldFilter.append(detail);
510                         oldFilter.append(quote);
511                         oldFilter.append(" = :link_");
512                         oldFilter.append(newParamterCounter);
513                         if (it.hasNext())
514                         {
515                             oldFilter.append(" AND ");
516                         }
517                         paramDef.parameterIndex.add(newParamterCounter + paramDef.parameterCount - 1);
518                     }
519                 }
520 
521                 composer.setFilter(oldFilter.toString());
522 
523                 final String sQuery = composer.getQuery();
524                 rowSetProp.setPropertyValue(UNO_COMMAND, sQuery);
525                 rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND));
526             }
527         }
528         return paramDef;
529     }
530 
fillOrderStatement(final String command, final int commandType, final Map parameters, final XConnectionTools tools, final XPropertySet rowSetProp)531     void fillOrderStatement(final String command,
532             final int commandType, final Map parameters,
533             final XConnectionTools tools,
534             final XPropertySet rowSetProp)
535             throws SQLException,
536             UnknownPropertyException,
537             PropertyVetoException,
538             IllegalArgumentException,
539             WrappedTargetException,
540             NoSuchElementException
541     {
542         final StringBuffer order = new StringBuffer(getOrderStatement(commandType, command, (ArrayList) parameters.get(GROUP_EXPRESSIONS)));
543         if (order.length() > 0 && commandType != CommandType.TABLE)
544         {
545             String statement = command;
546             final XSingleSelectQueryComposer composer = getComposer(tools, command, commandType);
547             if (composer != null)
548             {
549                 statement = composer.getQuery();
550                 composer.setQuery(statement);
551                 final String sOldOrder = composer.getOrder();
552                 if (sOldOrder.length() > 0)
553                 {
554                     order.append(',');
555                     order.append(sOldOrder);
556                     composer.setOrder("");
557                     statement = composer.getQuery();
558                 }
559             }
560             else
561             {
562                 if (commandType == CommandType.QUERY)
563                 {
564                     final XQueriesSupplier xSupplyQueries = (XQueriesSupplier) UnoRuntime.queryInterface(XQueriesSupplier.class, connection);
565                     final XNameAccess queries = xSupplyQueries.getQueries();
566                     if (queries.hasByName(command))
567                     {
568                         final XPropertySet prop = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, queries.getByName(command));
569                         final Boolean escape = (Boolean) prop.getPropertyValue(ESCAPEPROCESSING);
570                         rowSetProp.setPropertyValue(ESCAPEPROCESSING, escape);
571                         final String queryCommand = (String) prop.getPropertyValue(UNO_COMMAND);
572                         statement = "SELECT * FROM (" + queryCommand + ")";
573                     }
574 
575                 }
576                 else
577                 {
578                     statement = "SELECT * FROM (" + command + ")";
579                 }
580             }
581             rowSetProp.setPropertyValue(UNO_COMMAND, statement);
582             rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND));
583         }
584         rowSetProp.setPropertyValue("Order", order.toString());
585     }
586 }
587 
588