reportbuilder/java/org/libreoffice/report/SDBCReportDataFactory.java

   1 /*
   2  * This file is part of the LibreOffice project.
   3  *
   4  * This Source Code Form is subject to the terms of the Mozilla Public
   5  * License, v. 2.0. If a copy of the MPL was not distributed with this
   6  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   7  *
   8  * This file incorporates work covered by the following license notice:
   9  *
  10  *   Licensed to the Apache Software Foundation (ASF) under one or more
  11  *   contributor license agreements. See the NOTICE file distributed
  12  *   with this work for additional information regarding copyright
  13  *   ownership. The ASF licenses this file to you under the Apache
  14  *   License, Version 2.0 (the "License"); you may not use this file
  15  *   except in compliance with the License. You may obtain a copy of
  16  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  17  */
  18 package org.libreoffice.report;
  19
  20 import com.sun.star.awt.XWindow;
  21 import com.sun.star.beans.NamedValue;
  22 import com.sun.star.beans.PropertyVetoException;
  23 import com.sun.star.beans.UnknownPropertyException;
  24 import com.sun.star.beans.XPropertySet;
  25 import com.sun.star.container.NoSuchElementException;
  26 import com.sun.star.container.XChild;
  27 import com.sun.star.container.XIndexAccess;
  28 import com.sun.star.container.XNameAccess;
  29 import com.sun.star.frame.XController;
  30 import com.sun.star.frame.XFrame;
  31 import com.sun.star.frame.XModel;
  32 import com.sun.star.lang.IllegalArgumentException;
  33 import com.sun.star.lang.IndexOutOfBoundsException;
  34 import com.sun.star.lang.WrappedTargetException;
  35 import com.sun.star.sdb.CommandType;
  36 import com.sun.star.sdb.XCompletedExecution;
  37 import com.sun.star.sdb.XDocumentDataSource;
  38 import com.sun.star.sdb.XParametersSupplier;
  39 import com.sun.star.sdb.XQueriesSupplier;
  40 import com.sun.star.sdb.XSingleSelectQueryComposer;
  41 import com.sun.star.sdb.tools.XConnectionTools;
  42 import com.sun.star.sdbc.SQLException;
  43 import com.sun.star.sdbc.XConnection;
  44 import com.sun.star.sdbc.XParameters;
  45 import com.sun.star.sdbc.XRowSet;
  46 import com.sun.star.task.XInteractionHandler;
  47 import com.sun.star.uno.Exception;
  48 import com.sun.star.uno.UnoRuntime;
  49 import com.sun.star.uno.XComponentContext;
  50
  51 import java.math.BigDecimal;
  52
  53 import java.util.ArrayList;
  54 import java.util.HashMap;
  55 import java.util.Iterator;
  56 import java.util.List;
  57 import java.util.Map;
  58 import java.util.logging.Level;
  59 import java.util.logging.Logger;
  60
  61
  62 /**
  63  * Very primitive implementation, just to show how this could be used ...
  64  *
  65  */
  66 public class SDBCReportDataFactory implements DataSourceFactory
  67 {
  68
  69     private static final String ESCAPEPROCESSING = "EscapeProcessing";
  70
  71     private static class RowSetProperties
  72     {
  73
  74         final Boolean escapeProcessing;
  75         final int commandType;
  76         final Integer maxRows;
  77         final String command;
  78         final String filter;
  79
  80         public RowSetProperties(final Boolean escapeProcessing, final int commandType, final String command, final String filter, final Integer maxRows)
  81         {
  82             this.escapeProcessing = escapeProcessing;
  83             this.commandType = commandType;
  84             this.command = command;
  85             this.filter = filter;
  86             this.maxRows = maxRows;
  87         }
  88
  89         @Override
  90         public boolean equals(Object obj)
  91         {
  92             if (obj == null)
  93             {
  94                 return false;
  95             }
  96             if (getClass() != obj.getClass())
  97             {
  98                 return false;
  99             }
 100             final RowSetProperties other = (RowSetProperties) obj;
 101             if ((this.escapeProcessing == null) ? (other.escapeProcessing != null) : !this.escapeProcessing.equals(other.escapeProcessing))
 102             {
 103                 return false;
 104             }
 105             if (this.commandType != other.commandType)
 106             {
 107                 return false;
 108             }
 109             if ((this.maxRows == null) ? (other.maxRows != null) : !this.maxRows.equals(other.maxRows))
 110             {
 111                 return false;
 112             }
 113             if ((this.command == null) ? (other.command != null) : !this.command.equals(other.command))
 114             {
 115                 return false;
 116             }
 117             if ((this.filter == null) ? (other.filter != null) : !this.filter.equals(other.filter))
 118             {
 119                 return false;
 120             }
 121             return true;
 122         }
 123
 124         @Override
 125         public int hashCode()
 126         {
 127             int hash = 3;
 128             hash = 59 * hash + (this.escapeProcessing != null ? this.escapeProcessing.hashCode() : 0);
 129             hash = 59 * hash + this.commandType;
 130             hash = 59 * hash + (this.maxRows != null ? this.maxRows.hashCode() : 0);
 131             hash = 59 * hash + (this.command != null ? this.command.hashCode() : 0);
 132             hash = 59 * hash + (this.filter != null ? this.filter.hashCode() : 0);
 133             return hash;
 134         }
 135     }
 136
 137     private static class ParameterDefinition
 138     {
 139         private int parameterCount = 0;
 140         private final ArrayList<Integer> parameterIndex = new ArrayList<Integer>();
 141     }
 142     private static final Logger LOGGER = Logger.getLogger(SDBCReportDataFactory.class.getName());
 143     public static final String COMMAND_TYPE = "command-type";
 144     public static final String ESCAPE_PROCESSING = "escape-processing";
 145     public static final String SORT_EXPRESSIONS = "sort-expressions";
 146     public static final String GROUP_EXPRESSIONS = "group-expressions";
 147     public static final String MASTER_VALUES = "master-values";
 148     public static final String MASTER_COLUMNS = "master-columns";
 149     public static final String DETAIL_COLUMNS = "detail-columns";
 150     public static final String UNO_FILTER = "Filter";
 151     private static final String APPLY_FILTER = "ApplyFilter";
 152     private static final String UNO_COMMAND = "Command";
 153     private static final String UNO_COMMAND_TYPE = "CommandType";
 154     private final XConnection connection;
 155     private final XComponentContext m_cmpCtx;
 156     private final Map<RowSetProperties,XRowSet> rowSetProperties = new HashMap<RowSetProperties,XRowSet>();
 157     private final Map<RowSetProperties,ParameterDefinition> parameterMap = new HashMap<RowSetProperties,ParameterDefinition>();
 158     private boolean rowSetCreated = false;
 159
 160     public SDBCReportDataFactory(final XComponentContext cmpCtx, final XConnection connection)
 161     {
 162         this.connection = connection;
 163         m_cmpCtx = cmpCtx;
 164     }
 165
 166     public XWindow getParentWindow()
 167     {
 168         final XChild child = UnoRuntime.queryInterface(XChild.class, connection);
 169         if (child == null)
 170             return null;
 171         final XDocumentDataSource docSource = UnoRuntime.queryInterface(XDocumentDataSource.class, child.getParent());
 172         if (docSource == null)
 173             return null;
 174         final XModel model = UnoRuntime.queryInterface(XModel.class, docSource.getDatabaseDocument());
 175         if (model == null)
 176             return null;
 177         final XController controller = model.getCurrentController();
 178         if (controller == null)
 179             return null;
 180         final XFrame frame = controller.getFrame();
 181         if (frame == null)
 182             return null;
 183         return frame.getContainerWindow();
 184     }
 185
 186     public DataSource queryData(final String command, final Map<String,Object> parameters) throws DataSourceException
 187     {
 188         try
 189         {
 190             if (command == null)
 191             {
 192                 return new SDBCReportData(null);
 193             }
 194             int commandType = CommandType.COMMAND;
 195             final String commandTypeValue = (String) parameters.get(COMMAND_TYPE);
 196             if (commandTypeValue != null)
 197             {
 198                 if ("query".equals(commandTypeValue))
 199                 {
 200                     commandType = CommandType.QUERY;
 201                 }
 202                 else if ("table".equals(commandTypeValue))
 203                 {
 204                     commandType = CommandType.TABLE;
 205                 }
 206                 else
 207                 {
 208                     commandType = CommandType.COMMAND;
 209                 }
 210             }
 211             final Boolean escapeProcessing = (Boolean) parameters.get(ESCAPE_PROCESSING);
 212             final String filter = (String) parameters.get(UNO_FILTER);
 213             final Integer maxRows = (Integer) parameters.get("MaxRows");
 214             final RowSetProperties rowSetProps = new RowSetProperties(escapeProcessing, commandType, command, filter, maxRows);
 215
 216             final Object[] p = createRowSet(rowSetProps, parameters);
 217             final XRowSet rowSet = (XRowSet) p[0];
 218
 219             if (command.length() != 0)
 220             {
 221                 final ParameterDefinition paramDef = (ParameterDefinition) p[1];
 222                 fillParameter(parameters, rowSet, paramDef);
 223                 rowSetCreated = rowSetCreated && (maxRows == null || maxRows == 0);
 224
 225                 final XCompletedExecution execute = UnoRuntime.queryInterface(XCompletedExecution.class, rowSet);
 226                 if (rowSetCreated && execute != null && paramDef.parameterCount > 0)
 227                 {
 228                     final XWindow window = getParentWindow();
 229                     final XInteractionHandler handler = UnoRuntime.queryInterface(XInteractionHandler.class, m_cmpCtx.getServiceManager().createInstanceWithArgumentsAndContext("com.sun.star.sdb.InteractionHandler", new Object[] { new NamedValue("Parent", window) }, m_cmpCtx));
 230                     execute.executeWithCompletion(handler);
 231                 }
 232                 else
 233                 {
 234                     rowSet.execute();
 235                 }
 236             }
 237
 238             rowSetCreated = false;
 239             return new SDBCReportData(rowSet);
 240         }
 241         catch (Exception ex)
 242         {
 243             rowSetCreated = false;
 244             throw new DataSourceException(ex.getMessage(), ex);
 245         }
 246     }
 247
 248     private String getOrderStatement(final List sortExpressions)
 249     {
 250         final StringBuffer order = new StringBuffer();
 251         final int count = sortExpressions.size();
 252         String quote;
 253         try
 254         {
 255             quote = connection.getMetaData().getIdentifierQuoteString();
 256         }
 257         catch (SQLException ex)
 258         {
 259             LOGGER.severe("ReportProcessing failed / getOrderStatement could not get quote character: " + ex);
 260             // fall back to the SQL standard
 261             quote="";
 262         }
 263         for (int i = 0; i < count; i++)
 264         {
 265             final Object[] pair = (Object[]) sortExpressions.get(i);
 266             String expression = (String) pair[0];
 267
 268             // LEM FIXME: ${EXPLETIVE}! Either the values we get are *always* already quoted
 269             // (and then this whole work is not useful)
 270             // or they are *never* quoted
 271             // (and then just quote them unconditionally)
 272             // The current mess gives an ambiguity when the column name starts with a quote character.
 273             // It *seems* they are never quoted, but this needs further testing.
 274             if (!expression.startsWith(quote))
 275             {
 276                 expression = quote + expression + quote;
 277                 // LEM TODO: we should escape quotes in expression?
 278             }
 279             expression = expression.trim(); // Trim away white spaces
 280
 281             if (expression.length() > 0)
 282             {
 283                 order.append(expression);
 284                 if (order.length() > 0)
 285                 {
 286                     order.append(' ');
 287                 }
 288                 final String sorting = (String) pair[1];
 289                 if (sorting == null || sorting.equals(OfficeToken.FALSE))
 290                 {
 291                     order.append("DESC");
 292                 }
 293                 if ((i + 1) < count)
 294                 {
 295                     order.append(", ");
 296                 }
 297             }
 298         }
 299         return order.toString();
 300     }
 301
 302     private XSingleSelectQueryComposer getComposer(final XConnectionTools tools,
 303             final String command,
 304             final int commandType)
 305     {
 306         return tools.getComposer(commandType, command);
 307     }
 308
 309     private void fillParameter(final Map parameters,
 310             final XRowSet rowSet, final ParameterDefinition paramDef)
 311             throws SQLException,
 312             IllegalArgumentException
 313     {
 314         final ArrayList<?> masterValues = (ArrayList<?>) parameters.get(MASTER_VALUES);
 315         if (masterValues != null && !masterValues.isEmpty())
 316         {
 317             final XParameters para = UnoRuntime.queryInterface(XParameters.class, rowSet);
 318
 319             for (int i = 0;
 320                     i < masterValues.size();
 321                     i++)
 322             {
 323                 Object object = masterValues.get(i);
 324                 if (object instanceof BigDecimal)
 325                 {
 326                     object = ((BigDecimal) object).toString();
 327                 }
 328                 final Integer pos = paramDef.parameterIndex.get(i);
 329                 para.setObject(pos + 1, object);
 330             }
 331         }
 332     }
 333
 334     private final Object[] createRowSet(final RowSetProperties rowSetProps, final Map<String,Object> parameters)
 335             throws Exception
 336     {
 337         final ArrayList<?> detailColumns = (ArrayList<?>) parameters.get(DETAIL_COLUMNS);
 338         if (rowSetProperties.containsKey(rowSetProps) && detailColumns != null && !detailColumns.isEmpty())
 339         {
 340             return new Object[]
 341                     {
 342                         rowSetProperties.get(rowSetProps), parameterMap.get(rowSetProps)
 343                     };
 344         }
 345
 346         rowSetCreated = true;
 347         final XRowSet rowSet = UnoRuntime.queryInterface(XRowSet.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.RowSet", m_cmpCtx));
 348         final XPropertySet rowSetProp = UnoRuntime.queryInterface(XPropertySet.class, rowSet);
 349
 350         rowSetProp.setPropertyValue("ActiveConnection", connection);
 351         rowSetProp.setPropertyValue(ESCAPEPROCESSING, rowSetProps.escapeProcessing);
 352         rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(rowSetProps.commandType));
 353         rowSetProp.setPropertyValue(UNO_COMMAND, rowSetProps.command);
 354
 355         if (rowSetProps.filter != null)
 356         {
 357             rowSetProp.setPropertyValue("Filter", rowSetProps.filter);
 358             rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.valueOf(rowSetProps.filter.length() != 0));
 359         }
 360         else
 361         {
 362             rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.FALSE);
 363         }
 364
 365         if (rowSetProps.maxRows != null)
 366         {
 367             rowSetProp.setPropertyValue("MaxRows", rowSetProps.maxRows);
 368         }
 369
 370         final XConnectionTools tools = UnoRuntime.queryInterface(XConnectionTools.class, connection);
 371         fillOrderStatement(rowSetProps.command, rowSetProps.commandType, parameters, tools, rowSetProp);
 372         final ParameterDefinition paramDef = createParameter(parameters, tools, rowSetProps, rowSet);
 373
 374         rowSetProperties.put(rowSetProps, rowSet);
 375         parameterMap.put(rowSetProps, paramDef);
 376
 377         return new Object[]
 378                 {
 379                     rowSet, paramDef
 380                 };
 381     }
 382
 383     private ParameterDefinition createParameter(final Map parameters,
 384             final XConnectionTools tools,
 385             RowSetProperties rowSetProps, final XRowSet rowSet)
 386             throws SQLException,
 387             UnknownPropertyException,
 388             PropertyVetoException,
 389             IllegalArgumentException,
 390             WrappedTargetException
 391     {
 392         final ParameterDefinition paramDef = new ParameterDefinition();
 393         final XSingleSelectQueryComposer composer = getComposer(tools, rowSetProps.command, rowSetProps.commandType);
 394         if (composer != null)
 395         {
 396             final XPropertySet rowSetProp = UnoRuntime.queryInterface(XPropertySet.class, rowSet);
 397             if ((Boolean) rowSetProp.getPropertyValue(APPLY_FILTER))
 398             {
 399                 composer.setFilter((String) rowSetProp.getPropertyValue("Filter"));
 400             }
 401             // get old parameter count
 402             final ArrayList<?> detailColumns = (ArrayList<?>) parameters.get(DETAIL_COLUMNS);
 403             final ArrayList<String> handledColumns = new ArrayList<String>();
 404             final XParametersSupplier paraSup = UnoRuntime.queryInterface(XParametersSupplier.class, composer);
 405             if (paraSup != null)
 406             {
 407                 final XIndexAccess params = paraSup.getParameters();
 408                 if (params != null)
 409                 {
 410                     final int oldParameterCount = params.getCount();
 411                     paramDef.parameterCount = oldParameterCount;
 412                     if (detailColumns != null)
 413                     {
 414                         for (int i = 0; i < oldParameterCount; i++)
 415                         {
 416                             try
 417                             {
 418                                 final XPropertySet parameter = UnoRuntime.queryInterface(XPropertySet.class, params.getByIndex(i));
 419                                 if (parameter != null)
 420                                 {
 421                                     final String name = (String) parameter.getPropertyValue("Name");
 422                                     for (int j = 0; j < detailColumns.size(); j++)
 423                                     {
 424                                         if (name.equals(detailColumns.get(j)))
 425                                         {
 426                                             handledColumns.add(name);
 427                                             paramDef.parameterIndex.add(i);
 428                                             --paramDef.parameterCount;
 429                                             break;
 430                                         }
 431                                     }
 432                                 }
 433                             }
 434                             catch (IndexOutOfBoundsException ex)
 435                             {
 436                                 Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex);
 437                             }
 438                         }
 439                     }
 440                 }
 441             }
 442             final ArrayList<?> masterValues = (ArrayList<?>) parameters.get(MASTER_VALUES);
 443             if (masterValues != null && !masterValues.isEmpty() && paramDef.parameterIndex.size() != detailColumns.size())
 444             {
 445                 // create the new filter
 446                 final String quote = connection.getMetaData().getIdentifierQuoteString();
 447                 final StringBuffer oldFilter = new StringBuffer();
 448                 oldFilter.append(composer.getFilter());
 449                 if (oldFilter.length() != 0)
 450                 {
 451                     oldFilter.append(" AND ");
 452                 }
 453                 int newParamterCounter = 1;
 454                 for (final Iterator it = detailColumns.iterator(); it.hasNext();
 455                         ++newParamterCounter)
 456                 {
 457                     final String detail = (String) it.next();
 458                     if (!handledColumns.contains(detail))
 459                     {
 460                         oldFilter.append(quote);
 461                         oldFilter.append(detail);
 462                         oldFilter.append(quote);
 463                         oldFilter.append(" = :link_");
 464                         oldFilter.append(newParamterCounter);
 465                         if (it.hasNext())
 466                         {
 467                             oldFilter.append(" AND ");
 468                         }
 469                         paramDef.parameterIndex.add(newParamterCounter + paramDef.parameterCount - 1);
 470                     }
 471                 }
 472
 473                 composer.setFilter(oldFilter.toString());
 474
 475                 final String sQuery = composer.getQuery();
 476                 rowSetProp.setPropertyValue(UNO_COMMAND, sQuery);
 477                 rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND));
 478             }
 479         }
 480         return paramDef;
 481     }
 482
 483     private void fillOrderStatement(final String command,
 484             final int commandType, final Map parameters,
 485             final XConnectionTools tools,
 486             final XPropertySet rowSetProp)
 487             throws SQLException,
 488             UnknownPropertyException,
 489             PropertyVetoException,
 490             IllegalArgumentException,
 491             WrappedTargetException,
 492             NoSuchElementException
 493     {
 494         final StringBuffer order = new StringBuffer(getOrderStatement((ArrayList<?>) parameters.get(SORT_EXPRESSIONS)));
 495         if (order.length() > 0 && commandType != CommandType.TABLE)
 496         {
 497             String statement = command;
 498             final XSingleSelectQueryComposer composer = getComposer(tools, command, commandType);
 499             if (composer != null)
 500             {
 501                 statement = composer.getQuery();
 502                 composer.setQuery(statement);
 503                 final String sOldOrder = composer.getOrder();
 504                 if (sOldOrder.length() > 0)
 505                 {
 506                     order.append(',');
 507                     order.append(sOldOrder);
 508                     composer.setOrder("");
 509                     statement = composer.getQuery();
 510                 }
 511             }
 512             else
 513             {
 514                 if (commandType == CommandType.QUERY)
 515                 {
 516                     final XQueriesSupplier xSupplyQueries = UnoRuntime.queryInterface(XQueriesSupplier.class, connection);
 517                     final XNameAccess queries = xSupplyQueries.getQueries();
 518                     if (queries.hasByName(command))
 519                     {
 520                         final XPropertySet prop = UnoRuntime.queryInterface(XPropertySet.class, queries.getByName(command));
 521                         final Boolean escape = (Boolean) prop.getPropertyValue(ESCAPEPROCESSING);
 522                         rowSetProp.setPropertyValue(ESCAPEPROCESSING, escape);
 523                         final String queryCommand = (String) prop.getPropertyValue(UNO_COMMAND);
 524                         statement = "SELECT * FROM (" + queryCommand + ") \"__LibreOffice_report_result\"";
 525                     }
 526
 527                 }
 528                 else
 529                 {
 530                     statement = "SELECT * FROM (" + command + ") \"__LibreOffice_report_result\"";
 531                 }
 532             }
 533             rowSetProp.setPropertyValue(UNO_COMMAND, statement);
 534             rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND));
 535         }
 536         rowSetProp.setPropertyValue("Order", order.toString());
 537     }
 538 }
 539