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.workflow;
017
018import java.sql.Connection;
019import java.sql.PreparedStatement;
020import java.sql.ResultSet;
021import java.sql.SQLException;
022import java.sql.Statement;
023import java.util.ArrayList;
024import java.util.LinkedList;
025import java.util.List;
026import java.util.Map;
027
028import javax.sql.DataSource;
029
030import org.apache.commons.logging.Log;
031import org.apache.commons.logging.LogFactory;
032
033import org.ametys.core.datasource.ConnectionHelper;
034
035import com.opensymphony.module.propertyset.PropertySet;
036import com.opensymphony.workflow.StoreException;
037import com.opensymphony.workflow.query.Expression;
038import com.opensymphony.workflow.query.NestedExpression;
039
040/**
041 * JDBC implementation for PropertySet without JNDI.<br>
042 * Use a cocoon connection pool instead.
043 */
044public class JDBCPropertySet extends com.opensymphony.module.propertyset.database.JDBCPropertySet
045{
046    /** Prefix for osworkflow entries */
047    public static final String OSWF_PREFIX = "oswf_";
048
049    private static final String __TABLE_NAME = "OS_PROPERTYENTRY";
050
051    private static final String __GLOBAL_KEY_COL = "GLOBAL_KEY";
052
053    private static final String __ITEM_KEY_COL = "ITEM_KEY";
054
055    private static final String __ITEM_TYPE_COL = "ITEM_TYPE";
056
057    private static final String __STRING_COL = "STRING_VALUE";
058    
059    // For logging
060    private static Log __log = LogFactory.getLog(JDBCPropertySet.class);
061    
062    /** datasource */
063    protected DataSource _ds;
064    
065    @Override
066    public void init(Map config, Map args)
067    {
068        // args
069        globalKey = (String) args.get("globalKey");
070
071        // config
072        _ds = (DataSource) config.get("datasource");
073        
074        tableName = __TABLE_NAME;
075        colGlobalKey = __GLOBAL_KEY_COL;
076        colItemKey = __ITEM_KEY_COL;
077        colItemType = __ITEM_TYPE_COL;
078        colString = __STRING_COL;
079        colDate = "DATE_VALUE";
080        colData = "DATA_VALUE";
081        colFloat = "FLOAT_VALUE";
082        colNumber = "NUMBER_VALUE";
083    }
084
085    @Override
086    protected Connection getConnection() throws SQLException
087    {
088        // Si faux consomme tout le pool de connexions
089        closeConnWhenDone = true;
090        // Ne pas utiliser JNDI mais un pool de connexions de cocoon
091        return _ds.getConnection();
092    }
093
094    @Override
095    protected void cleanup(Connection connection, Statement statement, ResultSet result)
096    {
097        ConnectionHelper.cleanup(result);
098        ConnectionHelper.cleanup(statement);
099
100        if (closeConnWhenDone)
101        {
102            ConnectionHelper.cleanup(connection);
103        }
104    }
105
106    /**
107     * Test if an expression contains only nested PropertySet expression.
108     * @param expr The nested expression to test.
109     * @return <code>true</code> if the expression contains only PropertySet expression, <code>false</code> otherwise.
110     * @throws StoreException If an error occurs.
111     */
112    public static boolean isPropertySetExpressionsNested(NestedExpression expr) throws StoreException
113    {
114        int count = expr.getExpressionCount();
115
116        for (int i = 0; i < count; i++)
117        {
118            Expression e = expr.getExpression(i);
119
120            if (e instanceof NestedExpression)
121            {
122                if (!isPropertySetExpressionsNested((NestedExpression) e))
123                {
124                    // Il y a une expression imbriqué qui n'est pas de type PropertySet
125                    return false;
126                }
127            }
128            else if (!(e instanceof PropertySetExpression))
129            {
130                // Il y a une FieldExpression
131                return false;
132            }
133        }
134
135        return true;
136    }
137
138    /**
139     * Process a query from an osworkflow expression.
140     * @param ds The datasource
141     * @param e The expression.
142     * @return The result as a List of Long (Workflow ID).
143     * @throws StoreException If an error occurs.
144     */
145    public static List query(DataSource ds, Expression e) throws StoreException
146    {
147        List<String> values = new LinkedList<>();
148        StringBuffer query = new StringBuffer();
149
150        query.append("SELECT DISTINCT ");
151        query.append(__GLOBAL_KEY_COL);
152        query.append(" FROM ");
153        query.append(__TABLE_NAME);
154        query.append(" WHERE ");
155        query.append(_getCondition(ds, e, values));
156
157        return __doExpressionQuery(ds, query.toString(), values);
158    }
159
160    private static String _getCondition(DataSource ds, Expression e, List<String> values) throws StoreException
161    {
162        if (e instanceof NestedExpression)
163        {
164            NestedExpression nestedExpr = (NestedExpression) e;
165            int operator = nestedExpr.getExpressionOperator();
166
167            if (operator != NestedExpression.AND && operator != NestedExpression.OR)
168            {
169                throw new StoreException("Invalid nested operator " + operator);
170            }
171
172            StringBuffer condition = new StringBuffer();
173
174            if (nestedExpr.isNegate())
175            {
176                condition.append("NOT ");
177            }
178
179            condition.append("(");
180
181            int count = nestedExpr.getExpressionCount();
182
183            for (int i = 0; i < count; i++)
184            {
185                Expression expr = nestedExpr.getExpression(i);
186
187                // Ajouter la condition sur l'expression courante
188                condition.append(_getCondition(ds, expr, values));
189
190                if (i + 1 != count)
191                {
192                    if (operator == NestedExpression.AND)
193                    {
194                        condition.append(" AND ");
195                    }
196                    else
197                    {
198                        condition.append(" OR ");
199                    }
200                }
201            }
202
203            condition.append(")");
204
205            return condition.toString();
206        }
207        else if (e instanceof PropertySetExpression)
208        {
209            return _getCondition((PropertySetExpression) e, values);
210        }
211        else
212        {
213            throw new StoreException("Unsupported expression with mixed expressions");
214        }
215    }
216
217    private static String _getCondition(PropertySetExpression e, List<String> values) throws StoreException
218    {
219        String value = null;
220        String columnName;
221
222        if (e.getType() == PropertySet.STRING)
223        {
224            columnName = __STRING_COL;
225
226            if (!(e.getValue() instanceof String))
227            {
228                throw new StoreException("Query with value: " + e.getValue().getClass() + " is not supported");
229            }
230
231            value = (String) e.getValue();
232        }
233        else
234        {
235            throw new StoreException("Query of type: " + e.getType() + " is not supported");
236        }
237
238        StringBuffer query = new StringBuffer("(");
239
240        query.append(__ITEM_KEY_COL);
241        query.append(" = ? AND ");
242        query.append(columnName);
243
244        switch (e.getOperator())
245        {
246            case PropertySetExpression.EQUALS:
247                if (e.isNegate())
248                {
249                    query.append(" <> ");
250                }
251                else
252                {
253                    query.append(" = ");
254                }
255                break;
256            case PropertySetExpression.WILDCARD_EQUALS:
257                if (e.isNegate())
258                {
259                    query.append(" NOT LIKE ");
260                }
261                else
262                {
263                    query.append(" LIKE ");
264                }
265
266                // Remplacer les jokers * par %
267                value = value.replace('*', '%');
268                break;
269            default:
270                throw new StoreException("Query with operator: " + e.getOperator() + " is not supported");
271        }
272
273        query.append("?)");
274
275        // Ajouter la clé à la liste
276        values.add(e.getKey());
277        // Ajouter la valeur de test à la liste
278        values.add(value);
279
280        return query.toString();
281    }
282
283    private static List __doExpressionQuery(DataSource ds, String query, List values) throws StoreException
284    {
285        List<Long> results = new ArrayList<>();
286        Connection con = null;
287        PreparedStatement stmt = null;
288        ResultSet rs = null;
289
290        try
291        {
292            // Ne pas utiliser JNDI mais un pool de connexions de cocoon
293            con = ds.getConnection();
294            stmt = con.prepareStatement(query);
295
296            for (int i = 0; i < values.size(); i++)
297            {
298                stmt.setObject(i + 1, values.get(i));
299            }
300
301            if (__log.isDebugEnabled())
302            {
303                __log.debug("Query is: " + query + "\n" + values);
304            }
305
306            rs = stmt.executeQuery();
307
308            while (rs.next())
309            {
310                Long workflowID = __getWorkflowID(rs.getString(1));
311
312                if (workflowID != null)
313                {
314                    results.add(workflowID);
315                }
316            }
317
318            return results;
319        }
320        catch (SQLException ex)
321        {
322            throw new StoreException("SQL Exception in query: " + query, ex);
323        }
324        finally
325        {
326            ConnectionHelper.cleanup(rs);
327            ConnectionHelper.cleanup(stmt);
328            ConnectionHelper.cleanup(con);
329        }
330    }
331
332    private static Long __getWorkflowID(String globalKey)
333    {
334        if (globalKey == null)
335        {
336            return null;
337        }
338
339        if (globalKey.startsWith(OSWF_PREFIX))
340        {
341            return new Long(globalKey.substring(OSWF_PREFIX.length()));
342        }
343
344        return null;
345    }
346}