/**************************************************************
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*
*************************************************************/
package com.sun.star.report;
import com.sun.star.beans.PropertyVetoException;
import com.sun.star.beans.UnknownPropertyException;
import com.sun.star.beans.XPropertySet;
import com.sun.star.container.NoSuchElementException;
import com.sun.star.container.XIndexAccess;
import com.sun.star.container.XNameAccess;
import com.sun.star.lang.IllegalArgumentException;
import com.sun.star.lang.IndexOutOfBoundsException;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.lang.XComponent;
import com.sun.star.sdb.CommandType;
import com.sun.star.sdb.XCompletedExecution;
import com.sun.star.sdb.XParametersSupplier;
import com.sun.star.sdb.XQueriesSupplier;
import com.sun.star.sdb.XSingleSelectQueryComposer;
import com.sun.star.sdb.tools.XConnectionTools;
import com.sun.star.sdbc.SQLException;
import com.sun.star.sdbc.XConnection;
import com.sun.star.sdbc.XParameters;
import com.sun.star.sdbc.XRowSet;
import com.sun.star.task.XInteractionHandler;
import com.sun.star.uno.Exception;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* Very primitive implementation, just to show how this could be used ...
*
*/
public class SDBCReportDataFactory implements DataSourceFactory
{
private static final String ESCAPEPROCESSING = "EscapeProcessing";
private class RowSetProperties
{
final Boolean escapeProcessing;
final int commandType;
final Integer maxRows;
final String command;
final String filter;
public RowSetProperties(final Boolean escapeProcessing, final int commandType, final String command, final String filter, final Integer maxRows)
{
this.escapeProcessing = escapeProcessing;
this.commandType = commandType;
this.command = command;
this.filter = filter;
this.maxRows = maxRows;
}
public boolean equals(Object obj)
{
if (obj == null)
{
return false;
}
if (getClass() != obj.getClass())
{
return false;
}
final RowSetProperties other = (RowSetProperties) obj;
if (this.escapeProcessing != other.escapeProcessing && (this.escapeProcessing == null || !this.escapeProcessing.equals(other.escapeProcessing)))
{
return false;
}
if (this.commandType != other.commandType)
{
return false;
}
if (this.maxRows != other.maxRows && (this.maxRows == null || !this.maxRows.equals(other.maxRows)))
{
return false;
}
if ((this.command == null) ? (other.command != null) : !this.command.equals(other.command))
{
return false;
}
if ((this.filter == null) ? (other.filter != null) : !this.filter.equals(other.filter))
{
return false;
}
return true;
}
public int hashCode()
{
int hash = 3;
hash = 59 * hash + (this.escapeProcessing != null ? this.escapeProcessing.hashCode() : 0);
hash = 59 * hash + this.commandType;
hash = 59 * hash + (this.maxRows != null ? this.maxRows.hashCode() : 0);
hash = 59 * hash + (this.command != null ? this.command.hashCode() : 0);
hash = 59 * hash + (this.filter != null ? this.filter.hashCode() : 0);
return hash;
}
}
class ParameterDefinition
{
int parameterCount = 0;
private ArrayList parameterIndex = new ArrayList();
}
private static final Log LOGGER = LogFactory.getLog(SDBCReportDataFactory.class);
public static final String COMMAND_TYPE = "command-type";
public static final String ESCAPE_PROCESSING = "escape-processing";
public static final String GROUP_EXPRESSIONS = "group-expressions";
public static final String MASTER_VALUES = "master-values";
public static final String MASTER_COLUMNS = "master-columns";
public static final String DETAIL_COLUMNS = "detail-columns";
public static final String UNO_FILTER = "Filter";
private static final String APPLY_FILTER = "ApplyFilter";
private static final String UNO_COMMAND = "Command";
private static final String UNO_ORDER = "Order";
private static final String UNO_APPLY_FILTER = "ApplyFilter";
private static final String UNO_COMMAND_TYPE = "CommandType";
private final XConnection connection;
private final XComponentContext m_cmpCtx;
private static final int FAILED = 0;
private static final int DONE = 1;
private static final int RETRIEVE_COLUMNS = 2;
private static final int RETRIEVE_OBJECT = 3;
private static final int HANDLE_QUERY = 4;
private static final int HANDLE_TABLE = 5;
private static final int HANDLE_SQL = 6;
private final Map rowSetProperties = new HashMap();
private final Map parameterMap = new HashMap();
private boolean rowSetCreated = false;
public SDBCReportDataFactory(final XComponentContext cmpCtx, final XConnection connection)
{
this.connection = connection;
m_cmpCtx = cmpCtx;
}
public DataSource queryData(final String command, final Map parameters) throws DataSourceException
{
try
{
if (command == null)
{
return new SDBCReportData(null);
}
int commandType = CommandType.COMMAND;
final String commandTypeValue = (String) parameters.get(COMMAND_TYPE);
if (commandTypeValue != null)
{
if ("query".equals(commandTypeValue))
{
commandType = CommandType.QUERY;
}
else if ("table".equals(commandTypeValue))
{
commandType = CommandType.TABLE;
}
else
{
commandType = CommandType.COMMAND;
}
}
final Boolean escapeProcessing = (Boolean) parameters.get(ESCAPE_PROCESSING);
final String filter = (String) parameters.get(UNO_FILTER);
final Integer maxRows = (Integer) parameters.get("MaxRows");
final RowSetProperties rowSetProps = new RowSetProperties(escapeProcessing, commandType, command, filter, maxRows);
final Object[] p = createRowSet(rowSetProps, parameters);
final XRowSet rowSet = (XRowSet) p[0];
if (command.length() != 0)
{
final ParameterDefinition paramDef = (ParameterDefinition) p[1];
fillParameter(parameters, rowSet, paramDef);
rowSetCreated = rowSetCreated && (maxRows == null || maxRows == 0);
final XCompletedExecution execute = (XCompletedExecution) UnoRuntime.queryInterface(XCompletedExecution.class, rowSet);
if (rowSetCreated && execute != null && paramDef.parameterCount > 0)
{
final XInteractionHandler handler = (XInteractionHandler) UnoRuntime.queryInterface(XInteractionHandler.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.InteractionHandler", m_cmpCtx));
execute.executeWithCompletion(handler);
}
else
{
rowSet.execute();
}
}
rowSetCreated = false;
return new SDBCReportData(rowSet);
}
catch (Exception ex)
{
rowSetCreated = false;
throw new DataSourceException(ex.getMessage(), ex);
}
}
private String getOrderStatement(final int commandType, final String command, final List groupExpressions)
{
final StringBuffer order = new StringBuffer();
final int count = groupExpressions.size();
if (count != 0)
{
try
{
final String quote = connection.getMetaData().getIdentifierQuoteString();
final XComponent[] hold = new XComponent[1];
final XNameAccess columns = getFieldsByCommandDescriptor(commandType, command, hold);
if (columns != null)
{
for (int i = 0; i < count; i++)
{
final Object[] pair = (Object[]) groupExpressions.get(i);
String expression = (String) pair[0];
if (!expression.startsWith(quote) && columns.hasByName(expression))
{
XPropertySet column;
try
{
column = UnoRuntime.queryInterface(XPropertySet.class, columns.getByName(expression));
expression = quote + column.getPropertyValue("TableName") + quote + "." + quote + expression + quote;
}
catch (Exception ex)
{
Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex);
expression = quote + expression + quote;
}
}
expression = expression.trim(); // Trim away white spaces
if (expression.length() > 0)
{
order.append(expression);
if (order.length() > 0)
{
order.append(' ');
}
final String sorting = (String) pair[1];
if (sorting == null || sorting.equals(OfficeToken.FALSE))
{
order.append("DESC");
}
if ((i + 1) < count)
{
order.append(", ");
}
}
}
}
}
catch (SQLException ex)
{
LOGGER.error("ReportProcessing failed", ex);
}
}
return order.toString();
}
private XNameAccess getFieldsByCommandDescriptor(final int commandType, final String command, final XComponent[] out) throws SQLException
{
final Class[] parameter = new Class[3];
parameter[0] = int.class;
parameter[1] = String.class;
parameter[2] = out.getClass();
final XConnectionTools tools = (XConnectionTools) UnoRuntime.queryInterface(XConnectionTools.class, connection);
try
{
tools.getClass().getMethod("getFieldsByCommandDescriptor", parameter);
return tools.getFieldsByCommandDescriptor(commandType, command, out);
}
catch (NoSuchMethodException ex)
{
}
throw new SQLException();
}
private XSingleSelectQueryComposer getComposer(final XConnectionTools tools,
final String command,
final int commandType)
{
final Class[] parameter = new Class[2];
parameter[0] = int.class;
parameter[1] = String.class;
try
{
final Object[] param = new Object[2];
param[0] = commandType;
param[1] = command;
final Method call = tools.getClass().getMethod("getComposer", parameter);
return (XSingleSelectQueryComposer) call.invoke(tools, param);
}
catch (NoSuchMethodException ex)
{
}
catch (IllegalAccessException ex)
{
// should not happen
// assert False
}
catch (java.lang.reflect.InvocationTargetException ex)
{
// should not happen
// assert False
}
return null;
}
private void fillParameter(final Map parameters,
final XRowSet rowSet, final ParameterDefinition paramDef)
throws SQLException,
UnknownPropertyException,
PropertyVetoException,
IllegalArgumentException,
WrappedTargetException
{
final ArrayList masterValues = (ArrayList) parameters.get(MASTER_VALUES);
if (masterValues != null && !masterValues.isEmpty())
{
final XParameters para = (XParameters) UnoRuntime.queryInterface(XParameters.class, rowSet);
for (int i = 0;
i < masterValues.size();
i++)
{
Object object = masterValues.get(i);
if (object instanceof BigDecimal)
{
object = ((BigDecimal) object).toString();
}
final Integer pos = (Integer) paramDef.parameterIndex.get(i);
para.setObject(pos + 1, object);
}
}
}
private final Object[] createRowSet(final RowSetProperties rowSetProps, final Map parameters)
throws Exception
{
final ArrayList detailColumns = (ArrayList) parameters.get(DETAIL_COLUMNS);
if (rowSetProperties.containsKey(rowSetProps) && detailColumns != null && !detailColumns.isEmpty())
{
return new Object[]
{
rowSetProperties.get(rowSetProps), parameterMap.get(rowSetProps)
};
}
rowSetCreated = true;
final XRowSet rowSet = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.RowSet", m_cmpCtx));
final XPropertySet rowSetProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, rowSet);
rowSetProp.setPropertyValue("ActiveConnection", connection);
rowSetProp.setPropertyValue(ESCAPEPROCESSING, rowSetProps.escapeProcessing);
rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(rowSetProps.commandType));
rowSetProp.setPropertyValue(UNO_COMMAND, rowSetProps.command);
if (rowSetProps.filter != null)
{
rowSetProp.setPropertyValue("Filter", rowSetProps.filter);
rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.valueOf(rowSetProps.filter.length() != 0));
}
else
{
rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.FALSE);
}
if (rowSetProps.maxRows != null)
{
rowSetProp.setPropertyValue("MaxRows", rowSetProps.maxRows);
}
final XConnectionTools tools = (XConnectionTools) UnoRuntime.queryInterface(XConnectionTools.class, connection);
fillOrderStatement(rowSetProps.command, rowSetProps.commandType, parameters, tools, rowSetProp);
final ParameterDefinition paramDef = createParameter(parameters, tools, rowSetProps, rowSet);
rowSetProperties.put(rowSetProps, rowSet);
parameterMap.put(rowSetProps, paramDef);
return new Object[]
{
rowSet, paramDef
};
}
private ParameterDefinition createParameter(final Map parameters,
final XConnectionTools tools,
RowSetProperties rowSetProps, final XRowSet rowSet)
throws SQLException,
UnknownPropertyException,
PropertyVetoException,
IllegalArgumentException,
WrappedTargetException
{
final ParameterDefinition paramDef = new ParameterDefinition();
final XSingleSelectQueryComposer composer = getComposer(tools, rowSetProps.command, rowSetProps.commandType);
if (composer != null)
{
final XPropertySet rowSetProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, rowSet);
if ((Boolean) rowSetProp.getPropertyValue(APPLY_FILTER))
{
composer.setFilter((String) rowSetProp.getPropertyValue("Filter"));
}
// get old parameter count
final ArrayList detailColumns = (ArrayList) parameters.get(DETAIL_COLUMNS);
final ArrayList handledColumns = new ArrayList();
final XParametersSupplier paraSup = (XParametersSupplier) UnoRuntime.queryInterface(XParametersSupplier.class, composer);
if (paraSup != null)
{
final XIndexAccess params = paraSup.getParameters();
if (params != null)
{
final int oldParameterCount = params.getCount();
paramDef.parameterCount = oldParameterCount;
if (detailColumns != null)
{
for (int i = 0; i < oldParameterCount; i++)
{
try
{
final XPropertySet parameter = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, params.getByIndex(i));
if (parameter != null)
{
final String name = (String) parameter.getPropertyValue("Name");
for (int j = 0; j < detailColumns.size(); j++)
{
if (name.equals(detailColumns.get(j)))
{
handledColumns.add(name);
paramDef.parameterIndex.add(i);
--paramDef.parameterCount;
break;
}
}
}
}
catch (IndexOutOfBoundsException ex)
{
Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}
final ArrayList masterValues = (ArrayList) parameters.get(MASTER_VALUES);
if (masterValues != null && !masterValues.isEmpty() && paramDef.parameterIndex.size() != detailColumns.size())
{
// Vector masterColumns = (Vector) parameters.get("master-columns");
// create the new filter
final String quote = connection.getMetaData().getIdentifierQuoteString();
final StringBuffer oldFilter = new StringBuffer();
oldFilter.append(composer.getFilter());
if (oldFilter.length() != 0)
{
oldFilter.append(" AND ");
}
int newParamterCounter = 1;
for (final Iterator it = detailColumns.iterator(); it.hasNext();
++newParamterCounter)
{
final String detail = (String) it.next();
if (!handledColumns.contains(detail))
{
//String master = (String) masterIt.next();
oldFilter.append(quote);
oldFilter.append(detail);
oldFilter.append(quote);
oldFilter.append(" = :link_");
oldFilter.append(newParamterCounter);
if (it.hasNext())
{
oldFilter.append(" AND ");
}
paramDef.parameterIndex.add(newParamterCounter + paramDef.parameterCount - 1);
}
}
composer.setFilter(oldFilter.toString());
final String sQuery = composer.getQuery();
rowSetProp.setPropertyValue(UNO_COMMAND, sQuery);
rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND));
}
}
return paramDef;
}
void fillOrderStatement(final String command,
final int commandType, final Map parameters,
final XConnectionTools tools,
final XPropertySet rowSetProp)
throws SQLException,
UnknownPropertyException,
PropertyVetoException,
IllegalArgumentException,
WrappedTargetException,
NoSuchElementException
{
final StringBuffer order = new StringBuffer(getOrderStatement(commandType, command, (ArrayList) parameters.get(GROUP_EXPRESSIONS)));
if (order.length() > 0 && commandType != CommandType.TABLE)
{
String statement = command;
final XSingleSelectQueryComposer composer = getComposer(tools, command, commandType);
if (composer != null)
{
statement = composer.getQuery();
composer.setQuery(statement);
final String sOldOrder = composer.getOrder();
if (sOldOrder.length() > 0)
{
order.append(',');
order.append(sOldOrder);
composer.setOrder("");
statement = composer.getQuery();
}
}
else
{
if (commandType == CommandType.QUERY)
{
final XQueriesSupplier xSupplyQueries = (XQueriesSupplier) UnoRuntime.queryInterface(XQueriesSupplier.class, connection);
final XNameAccess queries = xSupplyQueries.getQueries();
if (queries.hasByName(command))
{
final XPropertySet prop = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, queries.getByName(command));
final Boolean escape = (Boolean) prop.getPropertyValue(ESCAPEPROCESSING);
rowSetProp.setPropertyValue(ESCAPEPROCESSING, escape);
final String queryCommand = (String) prop.getPropertyValue(UNO_COMMAND);
statement = "SELECT * FROM (" + queryCommand + ")";
}
}
else
{
statement = "SELECT * FROM (" + command + ")";
}
}
rowSetProp.setPropertyValue(UNO_COMMAND, statement);
rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND));
}
rowSetProp.setPropertyValue("Order", order.toString());
}
}