Source code

001/*
002 * Copyright (c) 2002-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: crawl $'
006 * '$Date: 2012-06-15 21:28:15 +0000 (Fri, 15 Jun 2012) $' 
007 * '$Revision: 29958 $'
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.DatabaseMetaData;
034import java.sql.DriverManager;
035import java.sql.ResultSet;
036import java.sql.SQLException;
037import java.util.Iterator;
038import java.util.List;
039
040import ptolemy.actor.IOPort;
041import ptolemy.actor.TypedAtomicActor;
042import ptolemy.actor.TypedIOPort;
043import ptolemy.data.RecordToken;
044import ptolemy.data.StringToken;
045import ptolemy.data.Token;
046import ptolemy.data.expr.StringParameter;
047import ptolemy.data.type.BaseType;
048import ptolemy.data.type.RecordType;
049import ptolemy.data.type.Type;
050import ptolemy.kernel.CompositeEntity;
051import ptolemy.kernel.Entity;
052import ptolemy.kernel.Port;
053import ptolemy.kernel.util.Attribute;
054import ptolemy.kernel.util.IllegalActionException;
055import ptolemy.kernel.util.NameDuplicationException;
056import util.DBUtil;
057
058//////////////////////////////////////////////////////////////////////////
059//// OpenDBConnection
060/**
061 * This actor opens a database connection using the database format, database
062 * URL, username and password, and sends a reference to it.
063 * 
064 * @author Efrat Jaeger
065 * @version $Id: OpenDBConnection.java 29958 2012-06-15 21:28:15Z crawl $
066 * @since Ptolemy II 3.0.2
067 */
068public class OpenDBConnection extends TypedAtomicActor {
069
070        /**
071         * Construct an actor with the given container and name.
072         * 
073         * @param container
074         *            The container.
075         * @param name
076         *            The name of this actor.
077         * @exception IllegalActionException
078         *                If the actor cannot be contained by the proposed
079         *                container.
080         * @exception NameDuplicationException
081         *                If the container already has an actor with this name.
082         */
083
084        public OpenDBConnection(CompositeEntity container, String name)
085                        throws NameDuplicationException, IllegalActionException {
086
087                super(container, name);
088
089                trigger = new TypedIOPort(this, "trigger", true, false);
090                trigger.setMultiport(true);
091
092                dbcon = new TypedIOPort(this, "dbcon", false, true);
093                // Set the type constraint.
094                dbcon.setTypeEquals(DBConnectionToken.DBCONNECTION);
095
096                // catalog = new StringAttribute(this, "catalog");
097                // catalog.setExpression("");
098                // _catalog = _none;
099
100                databaseFormat = new StringParameter(this, "databaseFormat");
101                databaseFormat.setDisplayName("database format");
102                databaseFormat.setExpression("Oracle");
103                databaseFormat.addChoice("Oracle");
104                databaseFormat.addChoice("DB2");
105                databaseFormat.addChoice("Local MS Access");
106                databaseFormat.addChoice("MS SQL Server");
107                databaseFormat.addChoice("PostgreSQL");
108                databaseFormat.addChoice("MySQL");
109                databaseFormat.addChoice("HSQL");
110                databaseFormat.addChoice("SQLite");
111                _dbFormat = _DBType._ORCL;
112
113                // driverName = new StringAttribute(this, "driverName");
114                databaseURL = new StringParameter(this, "databaseURL");
115
116                username = new StringParameter(this, "username");
117                password = new StringParameter(this, "password");
118
119                dbParams = new TypedIOPort(this, "dbParams", false, true);
120                dbParams.setTypeEquals(getDBParamsType());
121
122                _attachText("_iconDescription", "<svg>\n"
123                                + "<ellipse cx=\"0\" cy=\"-30\" " + "rx=\"20\" ry=\"10\"/>\n"
124                                + "<line x1=\"20\" y1=\"0\" " + "x2=\"20\" y2=\"-30\"/>\n"
125                                + "<line x1=\"-20\" y1=\"0\" " + "x2=\"-20\" y2=\"-30\"/>\n"
126                                + "<line x1=\"-20\" y1=\"0\" " + "x2=\"20\" y2=\"0\"/>\n"
127                                + "</svg>\n");
128
129        }
130
131        // /////////////////////////////////////////////////////////////////
132        // // ports and parameters ////
133
134        /**
135         * Input trigger: if connected, actor will only run once token is available.
136         */
137        public TypedIOPort trigger;
138
139        /** A reference to a db connection */
140        public TypedIOPort dbcon;
141
142        /** A record containing parameters to create a db connection. */
143        public TypedIOPort dbParams;
144
145        // public StringAttribute catalog;
146        public StringParameter databaseFormat;
147        // public StringAttribute driverName;
148        public StringParameter databaseURL;
149        public StringParameter username;
150        public StringParameter password;
151
152        public String strFileOrURL;
153
154        // /////////////////////////////////////////////////////////////////
155        // // public methods ////
156
157        /**
158         * Callback for changes in attribute values. If the dbFormat has changed,
159         * points to the new driver path. At any change in the connection
160         * attributes, tries to connect to the referenced database to get the schema
161         * and send it to connected actors.
162         * 
163         * @param at
164         *            The attribute that changed.
165         * @exception IllegalActionException
166         *                If the offsets array is not nondecreasing and nonnegative.
167         */
168        public void attributeChanged(Attribute at) throws IllegalActionException {
169                if (at == databaseFormat) {
170                        String dbFormat = databaseFormat.stringValue();
171                        _driverName = DBUtil.get(dbFormat.trim().toLowerCase());
172
173                        if (dbFormat.equals("Oracle")) {
174                                _dbFormat = _DBType._ORCL;
175                        } else if (dbFormat.equals("DB2")) {
176                                _dbFormat = _DBType._DB2;
177                        } else if (dbFormat.equals("Local MS Access")) {
178                                _dbFormat = _DBType._LACCS;
179                        } else if (dbFormat.equals("MS SQL Server")) {
180                                _dbFormat = _DBType._MSSQL;
181                        } else if (dbFormat.equals("PostgreSQL")) {
182                                _dbFormat = _DBType._PGSQL;
183                        } else if (dbFormat.equals("MySQL")) {
184                                _dbFormat = _DBType._MYSQL;
185                        } else if (dbFormat.equals("HSQL")) {
186                                _dbFormat = _DBType._HSQL;
187                        }  else if (dbFormat.equals("SQLite")){
188                                _dbFormat = _DBType._SQLite;
189                        }else {
190                                throw new IllegalActionException(this,
191                                                "No jdbc driver within the system for " + dbFormat);
192                        }
193                        if (!_driverName.equals(this._prevDriver)) {
194                                _setDBURL();
195                                _prevDriver = _driverName;
196                                _getAndSendSchema();
197                        }
198                } else if (at == databaseURL) {
199                        _setDBURL();
200                        if (!_databaseURL.equals(_prevDBURL)) {
201                                _prevDBURL = _databaseURL;
202                                _getAndSendSchema();
203                        }
204                } else if (at == username) {
205                        _username = username.stringValue();
206                        if (!_username.equals(_prevUser)) {
207                                _prevUser = _username;
208                                _getAndSendSchema();
209                        }
210                } else if (at == password) {
211                        _password = password.stringValue();
212                        if (!_password.equals(_prevPasswd)) {
213                                _prevPasswd = _password;
214                                _getAndSendSchema();
215                        }
216
217                } else {
218                        super.attributeChanged(at);
219                }
220        }
221
222        /**
223         * When connecting the dbcon port, trigger the connectionsChanged of the
224         * connected actor.
225         */
226        public void connectionsChanged(Port port) {
227                super.connectionsChanged(port);
228                if (port == dbcon) {
229                        List<?> conPortsList = dbcon.connectedPortList();
230                        Iterator<?> conPorts = conPortsList.iterator();
231                        while (conPorts.hasNext()) {
232                                IOPort p = (IOPort) conPorts.next();
233                                if (p.isInput()) {
234                                        Entity container = (Entity) p.getContainer();
235                                        container.connectionsChanged(p);
236                                }
237                        }
238                }
239        }
240
241        /**
242         * Connect to a database outputs a reference to the DB connection.
243         */
244
245        public void fire() throws IllegalActionException {
246
247                // consume tokens on trigger port if connected.
248                for (int i = 0; i < trigger.getWidth(); i++) {
249                        if (trigger.hasToken(i)) {
250                                trigger.get(i);
251                        }
252                }
253
254                try {
255
256                        // String _username = username.stringValue();
257                        // String _password = password.stringValue();
258
259                        // _setDBURL();
260
261                        Connection con = _connect("fire");
262                        dbcon.broadcast(new DBConnectionToken(con));
263
264                        RecordToken token = _createParamsToken(_driverName, _databaseURL,
265                                        _username, _password);
266                        dbParams.broadcast(token);
267
268                } catch (Exception ex) {
269                        throw new IllegalActionException(this, ex,
270                                        "fire exception DB connection");
271                }
272        }
273
274        /**
275         * postfiring the actor.
276         * 
277         */
278        public boolean postfire() {
279                return false;
280        }
281
282        /**
283         * Returns the database schema. This function is called from the dbcon port
284         * connected actors.
285         */
286        public String sendSchemaToConnected() throws IllegalActionException {
287                if (_schema.equals("")) {
288                        _schema = _getSchema();
289                }
290                return _schema;
291        }
292
293        /** Get the type of database parameter token. */
294        public static Type getDBParamsType() {
295                return new RecordType(_paramsLabels, new Type[] { BaseType.STRING,
296                                BaseType.STRING, BaseType.STRING, BaseType.STRING });
297        }
298
299        /** Get a JDBC Connection from a database parameter token. */
300        public static Connection getConnection(RecordToken params)
301                        throws IllegalActionException {
302            
303                try {
304
305                        Connection retval = null;
306                        String driver = ((StringToken) params.get("driver")).stringValue();
307
308            try {
309                Class.forName(driver).newInstance();
310            } catch (ClassNotFoundException e) {
311                                throw new IllegalActionException("The JDBC class " + driver
312                                                + " was not found on the classpath. One "
313                                                + "possible reason for this error is "
314                                                + "the JDBC jar is missing. Several JDBC "
315                                                + "jars are released under licenses "
316                                                + "incompatible with Kepler's license such "
317                                                + "as the Oracle JDBC jar. In "
318                                                + "these cases, the jar must be downloaded "
319                                                + "manually and placed in core/lib/jar/dbdrivers.");
320                        }
321                
322                        String user = _getParamField(params, "user");
323                        String passwd = _getParamField(params, "password");
324                        String url = _getParamField(params, "url");
325
326                        if (user.length() == 0 && passwd.length() == 0) {
327                                retval = DriverManager.getConnection(url);
328                        } else {
329                                retval = DriverManager.getConnection(url, user, passwd);
330                        }
331
332                        return retval;
333
334                } catch (InstantiationException e) {
335                        throw new IllegalActionException(e.getClass().getName() + ": "
336                                        + e.getMessage());
337                } catch (IllegalAccessException e) {
338                        throw new IllegalActionException(e.getClass().getName() + ": "
339                                        + e.getMessage());
340                } catch (SQLException e) {
341                        throw new IllegalActionException(e.getClass().getName() + ": "
342                                        + e.getMessage());
343                }
344        }
345
346        // /////////////////////////////////////////////////////////////////
347        // // private methods ////
348
349        /**
350         * Connecting to the database and returning a database connection reference.
351         * The context is either "fire" or "other", if unable to connect from a
352         * "fire" context throws an exception (for connecting from other context it
353         * might be the case that not all the connection attributes have already
354         * been set).
355         * 
356         * @param context
357         *       * @throws IllegalActionException
358         */
359        private Connection _connect(String context) throws IllegalActionException {
360                Connection retval = null;
361                try {
362                        RecordToken token = _createParamsToken(_driverName, _databaseURL,
363                                        _username, _password);
364                        retval = getConnection(token);
365                } catch (IllegalActionException e) {
366                        if (context.equals("fire"))
367                                throw new IllegalActionException(this, e.getCause(), e.getMessage());
368                }
369
370                return retval;
371        }
372
373        /**
374         * Called from attributeChanged. Once a connection attribute has been
375         * changed tries to connect to the database, get the schema and forward it
376         * to dbcon port connected actors.
377         * 
378         * @throws IllegalActionException
379         */
380        private void _getAndSendSchema() throws IllegalActionException {
381                _getSchema();
382
383                // send schema to connected
384                if (!_schema.equals(""))
385                        connectionsChanged(dbcon);
386        }
387
388        /**
389         * Gets the database schema in an XML format readable by the query builder.
390         * 
391         * @param con
392         *            - the database connection object.
393         */
394        private void _getDBSchema(Connection con) {
395                StringBuffer schema = new StringBuffer();
396                schema.append("<schema>\n");
397                StringBuffer table = new StringBuffer();
398                String prevTable = "";
399                String prevSchema = "";
400                int numTables = 0;
401                int numFields = 0;
402
403                try {
404                        DatabaseMetaData dmd = con.getMetaData();
405                        ResultSet schemas;
406
407                        String subname = null;
408
409                        if (_dbFormat == _DBType._MYSQL) {
410                                schemas = dmd.getCatalogs();
411                                subname = _databaseURL
412                                                .substring(_databaseURL.lastIndexOf("/") + 1);
413                        } else
414                                schemas = dmd.getSchemas();
415
416                        while (schemas.next()) {
417                                String schemaName = schemas.getString(1);
418
419                                // ignore certain schemas
420                                if (_dbFormat == _DBType._ORCL) {
421                                        // system schema will be the same as username.
422                                        if (!schemaName.toLowerCase().equals(_username))
423                                                continue;
424                                } else if (_dbFormat == _DBType._PGSQL) {
425                                        // system schemas
426                                        if (schemaName.startsWith("pg_catalog")
427                                                        || schemaName.startsWith("information_schema"))
428                                                continue;
429                                } else if (_dbFormat == _DBType._MYSQL) {
430                                        // skip schemas with a different subname
431                                        if (!schemaName.equals(subname))
432                                                continue;
433                                } else if (schemaName.toLowerCase().startsWith("sys")) {
434                                        // system schemas
435                                        continue;
436                                }
437
438                                ResultSet rs = dmd.getColumns(null, schemaName, "%", "%");
439
440                                // ResultSetMetaData md = rs.getMetaData();
441                                while (rs.next()) {
442
443                                        // String schemaName = rs.getString(2);
444                                        String tableName = rs.getString(3);
445                                        String columnName = rs.getString(4);
446                                        String columnType = rs.getString(6);
447
448                                        if (tableName.equals(""))
449                                                continue;
450                                        if (!tableName.equals(prevTable)
451                                                        || !schemaName.equals(prevSchema)) {
452                                                // new table, closing a previous one if exists
453                                                if (numFields > 0) {
454                                                        table.append("  </table>\n");
455                                                        numTables++;
456                                                        schema.append(table.toString());
457                                                }
458
459                                                table = new StringBuffer();
460                                                table.append("  <table name=\"");
461                                                if (!schemaName.toLowerCase().equals("null")) {
462                                                        table.append(schemaName + ".");
463                                                }
464                                                table.append(tableName + "\">\n");
465                                                numFields = 0;
466                                                prevTable = tableName;
467                                                prevSchema = schemaName;
468                                        }
469                                        if (columnName.equals(""))
470                                                continue;
471                                        else {
472                                                table.append("    <field name=\"" + columnName
473                                                                + "\" dataType=\"" + columnType + "\"/>\n");
474                                                numFields++;
475                                        }
476                                }
477                        }
478                        table.append("  </table>\n");
479                        if (numFields > 0) {
480                                numTables++;
481                                schema.append(table.toString());
482                        }
483                        schema.append("</schema>");
484                        if (numTables > 0) {
485                                _schema = schema.toString();
486                        }
487                } catch (SQLException ex) {
488                        _schema = "";
489                        System.out.println("SQLException: " + ex.getMessage());
490                }
491        }
492
493        /**
494         * Connects to the database and get the schema.
495         * 
496         * @return database schema string
497         * @throws IllegalActionException
498         */
499        private String _getSchema() throws IllegalActionException {
500                Connection con = _connect("other");
501                if (con != null) {
502                        _getDBSchema(con);
503                        try {
504                                con.close();
505                        } catch (SQLException e) {
506                                con = null;
507                                System.out.println("SQLException closing connection: "
508                                                + e.getMessage());
509                        }
510                        if (!_schema.equals(""))
511                                return _schema;
512                }
513                return "";
514        }
515
516        /**
517         * Set the absolute database URL depending on the database driver.
518         * 
519         * @throws IllegalActionException
520         */
521        private void _setDBURL() throws IllegalActionException {
522                _databaseURL = databaseURL.stringValue();
523                switch (_dbFormat) {
524                case _ORCL:
525                        if (!_databaseURL.trim().startsWith("jdbc:oracle:thin:@")) {
526                                if (_databaseURL.trim().startsWith("jdbc:oracle:")) {// a
527                                                                                                                                                // different
528                                                                                                                                                // driver
529                                                                                                                                                // type
530                                                                                                                                                // is
531                                                                                                                                                // spcified.
532                                        int ind = _databaseURL.indexOf("@");
533                                        if (ind > -1) {
534                                                _databaseURL = "jdbc:oracle:thin:@"
535                                                                + _databaseURL.substring(ind);
536                                        } else
537                                                throw new IllegalActionException(this,
538                                                                "Illegal database URL: " + _databaseURL);
539                                } else {
540                                        _databaseURL = "jdbc:oracle:thin:@" + _databaseURL;
541                                }
542                        }
543                        break;
544                case _DB2:
545                        if (!_databaseURL.trim().startsWith("jdbc:db2:")) {
546                                _databaseURL = "jdbc:db2:" + _databaseURL;
547                        }
548                        break;
549                case _LACCS:
550                        if (!_databaseURL.trim().startsWith("jdbc:odbc:")) {
551                                _databaseURL = "jdbc:odbc:" + _databaseURL;
552                        }
553                        break;
554                case _MSSQL:
555                        if (!_databaseURL.trim().startsWith("jdbc:sqlserver:")) {
556                                _databaseURL = "jdbc:sqlserver:" + _databaseURL;
557                        }
558                        _databaseURL = _databaseURL + ";User=" + _username + ";Password="
559                                        + _password;
560                        // _username = null; _password = null;
561                        break;
562                case _PGSQL:
563                        if (!_databaseURL.trim().startsWith("jdbc:postgresql:")) {
564                                _databaseURL = "jdbc:postgresql:" + _databaseURL;
565                        }
566                        break;
567                case _MYSQL:
568                        if (!_databaseURL.trim().startsWith("jdbc:mysql:")) {
569                                _databaseURL = "jdbc:mysql:" + _databaseURL;
570                        }
571                        break;
572                case _HSQL:
573                        if (!_databaseURL.trim().startsWith("jdbc:hsqldb:")) {
574                                _databaseURL = "jdbc:hsqldb:" + _databaseURL;
575                        }
576                        break;
577                case _SQLite:
578                        if (!_databaseURL.trim().startsWith("jdbc:sqlite:"))
579                        {
580                                _databaseURL = "jdbc:sqlite:" + _databaseURL;
581                        }
582                        break;
583                default:
584                        System.out.println(databaseFormat.getExpression()
585                                        + " is not supported");
586                }
587        }
588
589        /** Get the value of a field in a database parameter token. */
590        private static String _getParamField(RecordToken record, String name) {
591                StringToken token = (StringToken) record.get(name);
592                if (token == null) {
593                        return "";
594                } else {
595                        return token.stringValue();
596                }
597        }
598
599        /** Create a database parameter token. */
600        private static RecordToken _createParamsToken(String driver, String url,
601                        String user, String passwd) throws IllegalActionException {
602
603                return new RecordToken(_paramsLabels, new Token[] {
604                                new StringToken(driver), new StringToken(url),
605                                new StringToken(user), new StringToken(passwd) });
606        }
607
608        // /////////////////////////////////////////////////////////////////
609        // // private variables ////
610
611        // An indicator for the db format.
612        private _DBType _dbFormat;
613        private String _databaseURL = "";
614        private String _driverName = "";
615        private String _username = "";
616        private String _password = "";
617
618        // Saving previous values to track changes.
619        private String _prevDriver = "";
620        private String _prevDBURL = "";
621        private String _prevUser = "";
622        private String _prevPasswd = "";
623
624        // the database schema
625        private String _schema = "";
626
627        // database types
628        private enum _DBType {
629                _ORCL, _DB2, _LACCS, _MSSQL, _PGSQL, _MYSQL, _HSQL, _SQLite
630        };
631
632        /** The database parameter token labels. */
633        private static String[] _paramsLabels = new String[] { "driver", "url",
634                        "user", "password" };
635}