001/*
002 *  Copyright 2010 Anyware Services
003 *
004 *  Licensed under the Apache License, Version 2.0 (the "License");
005 *  you may not use this file except in compliance with the License.
006 *  You may obtain a copy of the License at
007 *
008 *      http://www.apache.org/licenses/LICENSE-2.0
009 *
010 *  Unless required by applicable law or agreed to in writing, software
011 *  distributed under the License is distributed on an "AS IS" BASIS,
012 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 *  See the License for the specific language governing permissions and
014 *  limitations under the License.
015 */
016package org.ametys.plugins.externaldata.data.sql;
017
018import java.sql.Connection;
019import java.sql.PreparedStatement;
020import java.sql.ResultSet;
021import java.sql.SQLException;
022import java.util.Arrays;
023import java.util.Collection;
024import java.util.Collections;
025import java.util.List;
026import java.util.Map;
027
028import org.apache.avalon.framework.service.ServiceException;
029import org.apache.avalon.framework.service.ServiceManager;
030import org.apache.avalon.framework.service.Serviceable;
031import org.apache.commons.lang.StringUtils;
032
033import org.ametys.core.datasource.AbstractDataSourceManager.DataSourceDefinition;
034import org.ametys.core.datasource.ConnectionHelper;
035import org.ametys.core.datasource.DataSourceClientInteraction.DataSourceType;
036import org.ametys.core.datasource.SQLDataSourceManager;
037import org.ametys.core.datasource.dbtype.SQLDatabaseTypeExtensionPoint;
038import org.ametys.plugins.externaldata.data.DataInclusionException;
039import org.ametys.plugins.externaldata.data.DataSourceFactory;
040import org.ametys.plugins.externaldata.data.Query;
041import org.ametys.plugins.externaldata.data.Query.ResultType;
042import org.ametys.runtime.plugin.component.PluginAware;
043
044/**
045 * SQL Data Source & Query Factory.
046 * Must provide these additional configuration information for the Data Source :
047 * - "driver" : JDBC driver.
048 * - "url" : JDBC url.
049 * - "user" : JDBC user.
050 * - "password" : JDBC password.
051 * Must provide these additional configuration information for the Query :
052 * - "queryString" : the SQL query as a String, which can contain parameters in the form ${parameterName}.
053 */
054public class SqlDataSourceFactory implements DataSourceFactory<SqlQuery, SqlQueryResult>, PluginAware, Serviceable
055{
056    /** Query string configuration parameter. */
057    public static final String QUERY_CONFIGURATION_QUERYSTRING = "queryString";
058    
059    /** Query configuration parameters. */
060    public static final List<String> QUERY_CONFIGURATION_PARAMETERS = Arrays.asList(QUERY_CONFIGURATION_QUERYSTRING);
061
062    private String _id;
063
064    private SQLDatabaseTypeExtensionPoint _sqlDatabaseTypeExtensionPoint;
065    private SQLDataSourceManager _sqlDataSourceManager;
066    
067    public void setPluginInfo(String pluginName, String featureName, String id)
068    {
069        _id = id;
070    }
071    
072    @Override
073    public void service(ServiceManager manager) throws ServiceException
074    {
075        _sqlDataSourceManager = (SQLDataSourceManager) manager.lookup(SQLDataSourceManager.ROLE);
076        _sqlDatabaseTypeExtensionPoint = (SQLDatabaseTypeExtensionPoint) manager.lookup(SQLDatabaseTypeExtensionPoint.ROLE);
077    }
078    
079    @Override
080    public Collection<DataSourceType> getHandledTypes()
081    {
082        return Collections.singleton(DataSourceType.SQL);
083    }
084    
085    @Override
086    public Collection<String> getQueryConfigurationParameters(String type)
087    {
088        return QUERY_CONFIGURATION_PARAMETERS;
089    }
090    
091    @Override
092    public SqlQuery buildQuery(String id, String type, String name, String description, ResultType resultType, String dataSourceId, Map<String, String> additionalConfiguration) throws DataInclusionException
093    {
094        String queryString = additionalConfiguration.get(QUERY_CONFIGURATION_QUERYSTRING);
095        
096        if (StringUtils.isBlank(queryString))
097        {
098            throw new DataInclusionException("Unable to build the SQL query : the query is required");
099        }
100        
101        SqlQuery query = new SqlQuery();
102        
103        query.setFactory(this._id);
104        query.setId(id);
105        query.setName(name);
106        query.setDescription(description);
107        query.setQueryString(queryString);
108        query.setResultType(resultType);
109        query.setDataSourceId(dataSourceId);
110        
111        return query;
112    }
113    
114    public SqlQueryResult execute(SqlQuery query, Map<String, String> parameterValues) throws DataInclusionException
115    {
116        return execute(query, parameterValues, 0, Integer.MAX_VALUE);
117    }
118    
119    @Override
120    public SqlQueryResult execute(SqlQuery sqlQuery, Map<String, String> parameterValues, int offset, int limit) throws DataInclusionException
121    {
122        String query = sqlQuery.getQueryString();
123        for (String parameterName : sqlQuery.getParameters().keySet())
124        {
125            if (!parameterValues.containsKey(parameterName))
126            {
127                String regexp = "[^ =,\\s]*=[^$ ]*\\$\\{" + parameterName + "(\\[[^\\]]*\\])?\\}[^ ]*";
128                query = query.replaceAll(regexp, "1=1");
129            }
130        }
131        
132        // Replace all the parameters by "?" placeholders.
133        query = query.replaceAll(Query.PARAMETER_PATTERN, "?");
134        
135        if (offset != 0 || limit != Integer.MAX_VALUE)
136        {
137            String dataSourceId = sqlQuery.getDataSourceId();
138            DataSourceDefinition sqlDefinition = _sqlDataSourceManager.getDataSourceDefinition(dataSourceId);
139            String defUrl = (String) sqlDefinition.getParameters().get("url");
140            String dbType = ConnectionHelper.getDatabaseType(defUrl);
141            
142            query = _sqlDatabaseTypeExtensionPoint.languageLimitQuery(dbType, query, Integer.toString(limit), Integer.toString(offset));
143        }
144        
145        ResultSet resultSet = null;
146        
147        try (Connection connection = ConnectionHelper.getConnection(sqlQuery.getDataSourceId()); PreparedStatement stmt = connection.prepareStatement(query))
148        {
149            // Set the parameters.
150            int paramIndex = 1;
151            for (String parameterName : sqlQuery.getParameters().keySet())
152            {
153                if (parameterValues.containsKey(parameterName))
154                {
155                    String value = parameterValues.get(parameterName);
156                    stmt.setString(paramIndex, value);
157                    paramIndex++;
158                }
159            }
160            
161            resultSet = stmt.executeQuery();
162            
163            SqlQueryResult result = new SqlQueryResult(resultSet);
164            
165            // At this point, all the data is loaded into the result object, so
166            // the resultSet and JDBC objects can safely be cleaned up.
167            
168            return result;
169        }
170        catch (SQLException e)
171        {
172            throw new DataInclusionException("Unable to execute the query.", e);
173        }
174        finally
175        {
176            ConnectionHelper.cleanup(resultSet);
177        }
178    }
179}