001/*
002 * Copyright (c) 1998-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: jianwu $'
006 * '$Date: 2010-12-10 00:09:01 +0000 (Fri, 10 Dec 2010) $' 
007 * '$Revision: 26473 $'
008 * 
009 * Permission is hereby granted, without written agreement and without
010 * license or royalty fees, to use, copy, modify, and distribute this
011 * software and its documentation for any purpose, provided that the above
012 * copyright notice and the following two paragraphs appear in all copies
013 * of this software.
014 *
015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
019 * SUCH DAMAGE.
020 *
021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
026 * ENHANCEMENTS, OR MODIFICATIONS.
027 *
028 */
029
030package org.geon;
031
032import java.sql.Connection;
033import java.sql.ResultSet;
034import java.sql.Statement;
035import java.util.Iterator;
036import java.util.Set;
037import java.util.TreeSet;
038
039import ptolemy.actor.TypedAtomicActor;
040import ptolemy.actor.TypedIOPort;
041import ptolemy.actor.parameters.PortParameter;
042import ptolemy.data.ArrayToken;
043import ptolemy.data.BooleanToken;
044import ptolemy.data.StringToken;
045import ptolemy.data.Token;
046import ptolemy.data.expr.Parameter;
047import ptolemy.data.expr.StringParameter;
048import ptolemy.data.type.ArrayType;
049import ptolemy.data.type.BaseType;
050import ptolemy.kernel.CompositeEntity;
051import ptolemy.kernel.util.IllegalActionException;
052import ptolemy.kernel.util.NameDuplicationException;
053
054//////////////////////////////////////////////////////////////////////////
055//// TransitiveClosureDBQuery
056/**
057 * Receives a string query and a database connection reference. Produces the
058 * transitive closure of the query.
059 * 
060 * @author Efrat Jaeger
061 * @version $Id: TransitiveClosureDBQuery.java 12876 2006-05-18 23:17:52Z mangal
062 *          $
063 * @since Ptolemy II 3.0.2
064 */
065public class TransitiveClosureDBQuery extends TypedAtomicActor {
066
067        /**
068         * Construct an actor with the given container and name.
069         * 
070         * @param container
071         *            The container.
072         * @param name
073         *            The name of this actor.
074         * @exception IllegalActionException
075         *                If the actor cannot be contained by the proposed
076         *                container.
077         * @exception NameDuplicationException
078         *                If the container already has an actor with this name.
079         */
080        public TransitiveClosureDBQuery(CompositeEntity container, String name)
081                        throws NameDuplicationException, IllegalActionException {
082                super(container, name);
083
084                // Ports
085                dbcon = new TypedIOPort(this, "dbcon", true, false);
086                dbcon.setTypeEquals(DBConnectionToken.DBCONNECTION);
087
088                initialSet = new TypedIOPort(this, "initialSet", true, false);
089                initialSet.setDisplayName("initial set");
090                initialSet.setTypeEquals(new ArrayType(BaseType.GENERAL));
091
092                result = new TypedIOPort(this, "result set", false, true);
093
094                outputEachRowSeparately = new Parameter(this,
095                                "outputEachRowSeparately", new BooleanToken(false));
096                outputEachRowSeparately.setTypeEquals(BaseType.BOOLEAN);
097
098                fieldInSet = new StringParameter(this, "fieldInSet");
099                fieldInSet.setDisplayName("field in set");
100
101                query = new PortParameter(this, "query");
102                query.setStringMode(true);
103
104                _attachText("_iconDescription", "<svg>\n"
105                                + "<ellipse cx=\"0\" cy=\"-30\" " + "rx=\"20\" ry=\"10\"/>\n"
106                                + "<line x1=\"20\" y1=\"0\" " + "x2=\"20\" y2=\"-30\"/>\n"
107                                + "<line x1=\"-20\" y1=\"0\" " + "x2=\"-20\" y2=\"-30\"/>\n"
108                                + "<line x1=\"-20\" y1=\"0\" " + "x2=\"20\" y2=\"0\"/>\n"
109                                + "</svg>\n");
110        }
111
112        // /////////////////////////////////////////////////////////////////
113        // // ports and parameters ////
114
115        /** Initial input set */
116        public TypedIOPort initialSet;
117
118        /** Result set */
119        public TypedIOPort result;
120
121        /**
122         * Specify whether to display the complete result at once or each row
123         * separately.
124         */
125        public Parameter outputEachRowSeparately;
126
127        /**
128         * A reference to the database connection.
129         */
130        public TypedIOPort dbcon;
131
132        /**
133         * A query string.
134         */
135        public PortParameter query;
136
137        /** Field in set condition field */
138        public StringParameter fieldInSet;
139
140        // /////////////////////////////////////////////////////////////////
141        // // public methods ////
142
143        /**
144         * Set the output type
145         * 
146         * @param attribute
147         *            The attribute that changed.
148         * @exception IllegalActionException
149         *                If the output type is not recognized.
150         */
151        public void preinitialize() throws IllegalActionException {
152                super.preinitialize();
153
154                // Set the output type.
155                _separate = ((BooleanToken) outputEachRowSeparately.getToken())
156                                .booleanValue();
157                if (_separate) {
158                        result.setTypeEquals(BaseType.STRING);
159                } else {
160                        result.setTypeEquals(new ArrayType(BaseType.STRING));
161                }
162        }
163
164        /**
165         * Consumes a query and a database connection reference. Compute the query
166         * result according to the specified output format.
167         * 
168         * @exception IllegalActionException
169         *                If there is no director.
170         */
171        public void fire() throws IllegalActionException {
172
173                if (dbcon.hasToken(0) && initialSet.hasToken(0)) {
174                        DBConnectionToken _dbcon = (DBConnectionToken) dbcon.get(0);
175                        Connection _con;
176                        try {
177                                _con = _dbcon.getValue();
178                        } catch (Exception e) {
179                                throw new IllegalActionException(this, e, "CONNECTION FAILURE");
180                        }
181
182                        // Initial set.
183                        ArrayToken initSetArray = (ArrayToken) initialSet.get(0);
184                        Set s1 = new TreeSet();
185                        // Set s2 = new TreeSet();
186
187                        String set = "(";
188                        for (int i = 0; i < initSetArray.length(); i++) {
189                                String input = ((StringToken) initSetArray.getElement(i))
190                                                .stringValue();
191                                s1.add(input);
192                                set += input + ",";
193                        }
194                        set = set.trim().substring(0, set.length() - 1) + ")";
195
196                        // the select query has to be of a single attribute that has to be
197                        // specified
198                        query.update();
199                        String _query = ((StringToken) query.getToken()).stringValue();
200
201                        // Leave just the first column.
202                        // int selectInd = _query.toLowerCase().indexOf("select");
203                        int fromInd = _query.toLowerCase().indexOf("from");
204                        int commaInd = _query.indexOf(",");
205                        if (commaInd > -1 && commaInd < fromInd) {
206                                _query = _query.substring(0, commaInd)
207                                                + _query.substring(fromInd - 1);
208                        }
209                        // String _field = _query.substring(selectInd+7, fromInd-1).trim();
210
211                        String originalQuery = _query;
212                        String _fieldInSet = ((StringToken) fieldInSet.getToken())
213                                        .stringValue();
214                        // _query = addFieldInSetCondition(originalQuery, _fieldInSet, set);
215
216                        try {
217                                Statement st = _con.createStatement();
218                                ResultSet rs;
219
220                                boolean contained = false;
221                                if (s1.isEmpty()) {
222                                        contained = true;
223                                }
224
225                                while (!contained) {
226                                        try {
227                                                contained = true;
228                                                _query = addFieldInSetCondition(originalQuery,
229                                                                _fieldInSet, set);
230                                                rs = st.executeQuery(_query);
231                                                set = "(";
232                                        } catch (Exception e1) {
233                                                throw new IllegalActionException(this, e1,
234                                                                "SQL executeQuery exception");
235                                        }
236                                        while (rs.next()) {
237                                                String val = rs.getString(1);
238                                                if (val == null) // is this necessary?
239                                                        val = "";
240                                                if (!s1.contains(val)) {
241                                                        s1.add(val);
242                                                        set += val + ",";
243                                                        contained = false;
244                                                }
245                                        }
246                                        set = set.substring(0, set.length() - 1) + ")";
247                                        rs.close();
248                                        st.close();
249                                }
250                                int i = 0;
251                                Token resultSet[] = new Token[s1.size()];
252                                Iterator sIt = s1.iterator();
253                                while (sIt.hasNext()) {
254                                        String val = (String) sIt.next();
255                                        if (_separate)
256                                                result.broadcast(new StringToken(val));
257                                        else {
258                                                resultSet[i++] = new StringToken(val);
259                                        }
260                                }
261                                if (!_separate) {
262                                        result.broadcast(new ArrayToken(resultSet));
263                                }
264                        } catch (Exception ex) {
265                                throw new IllegalActionException(this, ex, "exception in SQL");
266                        }
267                }
268        }
269
270        // /////////////////////////////////////////////////////////////////
271        // // private variables ////
272
273        /**
274         * Add field in set condition to the query
275         */
276
277        String addFieldInSetCondition(String query, String field, String set) {
278                String cond = field + " in " + set;
279                int whereInd = query.toLowerCase().indexOf("where");
280                if (whereInd > -1) { // NOTE! Ignoring inline queries.
281                        query = query.substring(0, whereInd + 6) + cond + " and "
282                                        + query.substring(whereInd + 6);
283                } else { // NOTE! for the mean time, ignoring other operations such as
284                                        // order by, group by, limit.
285                        query = query + " where " + cond;
286                }
287                return query;
288        }
289
290        // /////////////////////////////////////////////////////////////////
291        // // private variables ////
292
293        /**
294         * Output indicator parameter.
295         */
296        private boolean _separate;
297
298}