Source code

001/*
002 * Copyright (c) 1998-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: crawl $'
006 * '$Date: 2016-06-27 19:15:31 +0000 (Mon, 27 Jun 2016) $' 
007 * '$Revision: 34499 $'
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.sdm.spa;
031
032import java.sql.PreparedStatement;
033import java.sql.ResultSet;
034import java.sql.SQLException;
035import java.sql.Statement;
036import java.util.LinkedHashMap;
037import java.util.LinkedList;
038import java.util.List;
039
040import org.geon.DatabaseAccessor;
041import org.jdom.Document;
042import org.jdom.Element;
043import org.jdom.input.DOMBuilder;
044
045import ptolemy.actor.TypedIOPort;
046import ptolemy.actor.parameters.PortParameter;
047import ptolemy.data.ArrayToken;
048import ptolemy.data.BooleanToken;
049import ptolemy.data.DateToken;
050import ptolemy.data.DoubleToken;
051import ptolemy.data.IntToken;
052import ptolemy.data.LongToken;
053import ptolemy.data.MatrixToken;
054import ptolemy.data.RecordToken;
055import ptolemy.data.StringToken;
056import ptolemy.data.Token;
057import ptolemy.data.XMLToken;
058import ptolemy.data.expr.Parameter;
059import ptolemy.data.type.BaseType;
060import ptolemy.data.type.Type;
061import ptolemy.kernel.CompositeEntity;
062import ptolemy.kernel.util.IllegalActionException;
063import ptolemy.kernel.util.NameDuplicationException;
064import ptolemy.kernel.util.Workspace;
065
066//////////////////////////////////////////////////////////////////////////
067//// DatabaseWriter
068
069/**
070 * This actor performs database updates and returns the number of updated rows.
071 * If <i>input</i> is an SQL string, the SQL is run on the database.
072 * <p>
073 * If the input is a record token, the name-value pairs are inserted into the
074 * table specfied by <i>table</i>. Optionally, auto-increments for a column will
075 * be done when <i>autoIncColumnName</i> is specified; the incremented value is
076 * output in <i>autoIncValue</i>.
077 * </p>
078 * 
079 * @author Yang Zhao, Daniel Crawl
080 * @version $Id: DatabaseWriter.java 34499 2016-06-27 19:15:31Z crawl $
081 */
082
083public class DatabaseWriter extends DatabaseAccessor {
084
085        /**
086         * Construct an actor with the given container and name.
087         * 
088         * @param container
089         *            The container.
090         * @param name
091         *            The name of this actor.
092         * @exception IllegalActionException
093         *                If the actor cannot be contained by the proposed
094         *                container.
095         * @exception NameDuplicationException
096         *                If the container already has an actor with this name.
097         */
098        public DatabaseWriter(CompositeEntity container, String name)
099                        throws IllegalActionException, NameDuplicationException {
100                super(container, name);
101
102                input = new PortParameter(this, "input");
103
104                table = new PortParameter(this, "table");
105                table.setStringMode(true);
106                table.getPort().setTypeEquals(BaseType.STRING);
107
108                autoIncColumnName = new PortParameter(this, "autoIncColumnName");
109                autoIncColumnName.setStringMode(true);
110                autoIncColumnName.getPort().setTypeEquals(BaseType.STRING);
111
112                autoIncValue = new TypedIOPort(this, "autoIncValue", false, true);
113                autoIncValue.setTypeEquals(BaseType.INT);
114
115                usePreparedStatement = new Parameter(this, "usePreparedStatement");
116                usePreparedStatement.setTypeEquals(BaseType.BOOLEAN);
117                usePreparedStatement.setToken(BooleanToken.FALSE);
118                
119                // the output
120                result = new TypedIOPort(this, "result", false, true);
121                result.setTypeEquals(BaseType.INT);
122                
123                _domBuilder = new DOMBuilder();
124        }
125
126        // /////////////////////////////////////////////////////////////////
127        // // ports and parameters ////
128
129        /** The input for the update. */
130        public PortParameter input;
131
132        /** The number of rows successfully updated. */
133        public TypedIOPort result;
134
135        /** Name of table. */
136        public PortParameter table;
137
138        /** Name of column to auto-increment. */
139        public PortParameter autoIncColumnName;
140
141        /** Auto-increment value. */
142        public TypedIOPort autoIncValue;
143        
144        /** If true, use a prepared statement instead of statement to perform
145         *  the SQL command. Currently only supported for record token inputs.
146         */
147        public Parameter usePreparedStatement;
148        
149        // /////////////////////////////////////////////////////////////////
150        // // public methods ////
151
152        @Override
153        public Object clone(Workspace workspace) throws CloneNotSupportedException {
154            DatabaseWriter newObject = (DatabaseWriter) super.clone(workspace);
155            newObject._domBuilder = new DOMBuilder();
156            return newObject;
157        }
158        
159        /**
160         * Read a string token for the query from the input port, execute it on the
161         * database and output the query result.
162         * 
163         * @exception IllegalActionException
164         *                If there is error to execute the query or if the base
165         *                class throw it.
166         */
167        @Override
168    public void fire() throws IllegalActionException {
169                super.fire();
170
171                String cmd = "";
172                int incVal = -1;
173
174                
175                final boolean usePreparedStatementVal = 
176                ((BooleanToken)usePreparedStatement.getToken()).booleanValue();
177                
178                try {
179                    
180                    int nresult = -1;
181                    
182                        // read the input
183                        input.update();
184                        Token token = input.getToken();
185                        Type type = token.getType();
186
187                        if (_debugging) {
188                                _debug("input: " + token);
189                        }
190
191                        // see what kind of input it is
192                        if (type == BaseType.STRING) {
193                                cmd = ((StringToken) token).stringValue();
194                        } else if (type == BaseType.XMLTOKEN
195                                        || token instanceof RecordToken) {
196                                LinkedHashMap<String, String> map = null;
197                                String tableName;
198
199                                if (type == BaseType.XMLTOKEN) {
200                                        org.w3c.dom.Document d = ((XMLToken) token).getDomTree();
201                                        Document doc = _domBuilder.build(d);
202                                        Element root = doc.getRootElement();
203                                        tableName = root.getName();
204                                        map = _parseXMLCmd(doc);
205                                } else if(token instanceof RecordToken) {
206                                        table.update();
207                                        tableName = ((StringToken) table.getToken()).stringValue();
208                                        
209                                        if(usePreparedStatementVal) {
210                                            nresult = _insertRecord(tableName, (RecordToken)token);
211                                        } else {
212                                            map = _parseRecordCmd((RecordToken) token);
213                                        }
214                                } else {
215                                    throw new IllegalActionException(this,
216                                    "Unsupported type of input token: " + token.getType());
217                                }
218
219                                if (tableName.equals("")) {
220                                        throw new IllegalActionException(this,
221                                                        "No value for required table name");
222                                }
223
224
225                                if(!usePreparedStatementVal) {                          
226                        // check for auto increment column name
227                        incVal = _checkAutoIncColumn(tableName, map);
228                                    cmd = _convertMapToSQLInsert(tableName, map);
229                                }
230
231                        } else {
232                                throw new IllegalActionException(this,
233                                                "Unknown type of token in updateSQL: " + type);
234                        }
235
236                        if(!usePreparedStatementVal) {
237                if (_debugging) {
238                    _debug("sql command: " + cmd);
239                }
240    
241                try(Statement st = _db.createStatement();) {
242                    nresult = st.executeUpdate(cmd);
243                    result.broadcast(new IntToken(nresult));
244                }                           
245                        }
246                        
247                        if (incVal > -1) {
248                                autoIncValue.broadcast(new IntToken(incVal));
249                        }
250                } catch (SQLException e) {
251                        throw new IllegalActionException(this, e,
252                                        "failed to execute the command: " + cmd);
253                }
254        }
255        
256        // /////////////////////////////////////////////////////////////////
257        // // private methods ////
258
259        /** Convert an XML document into a map. */
260        private LinkedHashMap<String, String> _parseXMLCmd(Document doc) {
261                LinkedHashMap<String, String> retval = new LinkedHashMap<String, String>();
262                Element root = doc.getRootElement();
263                List<?> l = root.getChildren();
264                for (int i = 0; i < l.size(); i++) {
265                        Element child = (Element) l.get(i);
266                        retval.put(child.getName(), child.getText());
267                }
268                return retval;
269        }
270
271        /** Convert a record token into a map. */
272        private LinkedHashMap<String, String> _parseRecordCmd(RecordToken token) {
273                LinkedHashMap<String, String> retval = new LinkedHashMap<String, String>();
274                Object labels[] = token.labelSet().toArray();
275
276                for (int i = 0; i < labels.length; i++) {
277                        String str = (String) labels[i];
278                        Token val = token.get(str);
279                        String value = _convertTokenToSQL(val);
280                        if (value != null){
281                                retval.put(str, value);
282                        }
283                        else{
284                                retval.put(str, null);
285                        }
286                }
287
288                return retval;
289        }
290
291        /** Convert a token's value to an SQL string. */
292        private String _convertTokenToSQL(Token token) {
293                String retval = null;
294
295                if (token.isNil()){
296                        return null;
297                }
298                else if (token instanceof StringToken) {
299                        retval = ((StringToken) token).stringValue();
300                } else if ((token instanceof IntToken)
301                                || (token instanceof DoubleToken)) {
302                        retval = token.toString();
303                } else if (token instanceof ptolemy.data.DateToken) {
304                        long ms = ((DateToken) token).getValue();
305                        java.sql.Timestamp ts = new java.sql.Timestamp(ms);
306                        retval = ts.toString();
307                } else if (token instanceof ArrayToken) {
308                        ArrayToken array = (ArrayToken) token;
309                        StringBuffer buf = new StringBuffer("{");
310                        for (int i = 0; i < array.length(); i++) {
311                                String str = _convertTokenToSQL(array.getElement(i));
312                                buf.append(str + ", ");
313                        }
314                        // remove the last comma and space
315                        buf = buf.delete(buf.length() - 2, buf.length());
316
317                        buf.append("}");
318                        retval = buf.toString();
319                        if (retval.equals("{}")){
320                                return null;
321                        }
322                } else if (token instanceof MatrixToken) {
323                        ArrayToken array = MatrixToken.matrixToArray((MatrixToken) token);
324                        retval = _convertTokenToSQL(array);
325                } else {
326                        System.out.println("WARNING: unhandled token type "
327                                        + "converted to sql: " + token.getType());
328                        retval = token.toString();
329                }
330
331                return retval;
332        }
333
334        /**
335         * Convert a map of key-values into a SQL insert string.
336         * 
337         * @param tableName
338         *            the name of the table
339         * @param map
340         *            the map of column names and values
341         * @return an SQL insert string
342         */
343        private String _convertMapToSQLInsert(String tableName,
344                        LinkedHashMap<String, String> map) {
345                StringBuffer retval = new StringBuffer();
346
347                retval.append("INSERT INTO " + tableName + " (");
348
349                for (String name : map.keySet()) {
350                        retval.append(name + ", ");
351                }
352
353                // remove the last comma and space
354                retval = retval.delete(retval.length() - 2, retval.length());
355
356                retval.append(") VALUES (");
357
358                for (String name : map.keySet()) {
359                        if (map.get(name) == null){
360                                retval.append(null + ", ");
361                        }
362                        else{
363                                retval.append("'" + map.get(name) + "', ");
364                        }
365                }
366
367                // remove the last comma and space
368                retval = retval.delete(retval.length() - 2, retval.length());
369
370                retval.append(")");
371
372                return retval.toString();
373        }
374
375        /**
376         * Perform an auto-increment. Find the largest value in the column
377         * <i>colName</i>, return this value incremented by one and place it into the
378         * map.
379         */
380        private int _checkAutoIncColumn(String tableName,
381                        LinkedHashMap<String, String> map) throws IllegalActionException,
382                        SQLException {
383                int retval = -1;
384                autoIncColumnName.update();
385                String colName = ((StringToken) autoIncColumnName.getToken())
386                                .stringValue();
387
388                // see if auto-inc column name was given
389                if (colName.length() > 0) {
390                        String cmd = "SELECT max(" + colName + ") from " + tableName;
391                        Statement st = _db.createStatement();
392                        ResultSet rs = st.executeQuery(cmd);
393                        int val = 0;
394
395                        // see if it exists
396                        if (rs.next()) {
397                                val = rs.getInt(1) + 1;
398                        }
399
400                        rs.close();
401                        st.close();
402
403                        retval = val;
404
405                        map.put(colName, String.valueOf(val));
406                }
407
408                return retval;
409        }
410
411        /** Insert the values in a record token using a prepared statement. */
412        private int _insertRecord(String tableName, RecordToken token)
413                throws SQLException, IllegalActionException {
414            
415            StringBuilder buf = new StringBuilder("INSERT INTO ")
416                    .append(tableName)
417                    .append(" (");
418
419            StringBuilder paramsBuf = new StringBuilder();
420
421            List<String> names = new LinkedList<String>(token.labelSet());
422            for(int i = 0; i < names.size() - 1; i++) {
423                buf.append(names.get(i))
424                .append(",");
425                paramsBuf.append("?,");
426            }
427            buf.append(names.get(names.size() - 1))
428            .append(") VALUES (");
429
430            paramsBuf.append("?");
431
432            buf.append(paramsBuf)
433            .append(")");
434
435            if(_debugging) {
436                _debug("Executing: " + buf);
437            }
438
439            try(PreparedStatement ps = _db.prepareStatement(buf.toString())) {
440                for(int i = 0; i < names.size(); i++) {
441                    _setPreparedStatementValue(ps, i + 1, token.get(names.get(i)));
442                }
443
444                return ps.executeUpdate();
445            }
446
447        }
448
449        // /////////////////////////////////////////////////////////////////
450        // // private members ////
451
452        /** Helper object to convert from w3c Document to jdom Document */
453        private DOMBuilder _domBuilder = null;
454                
455}