001/*
002 * Copyright (c) 2003-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: welker $'
006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 
007 * '$Revision: 24234 $'
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.kepler.dataproxy.datasource.geon;
031
032import java.io.File;
033import java.io.FileInputStream;
034import java.io.InputStream;
035import java.net.URL;
036import java.sql.DriverManager;
037
038import javax.xml.parsers.DocumentBuilder;
039import javax.xml.parsers.DocumentBuilderFactory;
040
041import org.apache.commons.logging.Log;
042import org.apache.commons.logging.LogFactory;
043import org.apache.xpath.XPathAPI;
044import org.ecoinformatics.seek.datasource.EcogridMetaDataCacheItem;
045import org.ecoinformatics.seek.ecogrid.quicksearch.ResultTreeRoot;
046import org.ecoinformatics.seek.querybuilder.DBQueryDef;
047import org.geon.DatabaseQuery;
048import org.kepler.configuration.ConfigurationManager;
049import org.kepler.configuration.ConfigurationProperty;
050import org.kepler.dataproxy.datasource.DataSourceInterface;
051import org.kepler.dataproxy.metadata.ADN.ADNMetadataSpecification;
052import org.kepler.objectmanager.cache.DataCacheListener;
053import org.kepler.objectmanager.cache.DataCacheManager;
054import org.kepler.objectmanager.cache.DataCacheObject;
055import org.kepler.objectmanager.data.DataSourceControllerFactory;
056import org.w3c.dom.Document;
057import org.w3c.dom.Element;
058import org.w3c.dom.Node;
059import org.w3c.dom.NodeList;
060
061import ptolemy.actor.gui.style.TextStyle;
062import ptolemy.data.BooleanToken;
063import ptolemy.data.expr.SingletonParameter;
064import ptolemy.kernel.CompositeEntity;
065import ptolemy.kernel.util.IllegalActionException;
066import ptolemy.kernel.util.NameDuplicationException;
067import ptolemy.kernel.util.Settable;
068import ptolemy.kernel.util.StringAttribute;
069import ptolemy.vergil.icon.EditorIcon;
070import ptolemy.vergil.kernel.attributes.EllipseAttribute;
071import ptolemy.vergil.kernel.attributes.ResizablePolygonAttribute;
072import ptolemy.vergil.kernel.attributes.TextAttribute;
073
074/**
075 * An actor that serves as a proxy to query a specific database (defined by geon
076 * id).
077 */
078public class GEONDatabaseResource extends DatabaseQuery implements
079                DataCacheListener, DataSourceInterface {
080
081        private static final String SCHEMAENTITY = "//databaseMetaData/schema";
082        private static final String TABLEENTITY = "//databaseMetaData/schema/table";
083        private static final String VIEWENTITY = "//databaseMetaData/schema/view";
084        private static final String SCHEMANAMEATT = "name";
085        private static final String TABLENAMEATT = "name";
086        private static final String FIELDTAG = "column";
087        private static final String FIELDATT = "name";
088        private static final String FIELDDATATYPE = "datatype";
089        private static final String FIELDSIZE = "size";
090        private static final String FIELDRESTRICTION = "nullable";
091
092        private static final String EMAILPATH = "//sqlEngine[sqlEngineName=\"geon\"]/email";
093        private static final String USERNAMEPATH = "//sqlEngine[sqlEngineName=\"geon\"]/userName";
094        private static final String DRIVERPATH = "//sqlEngine[sqlEngineName=\"geon\"]/dbDriver";
095        private static final String JDBCCONNECTPATH = "//sqlEngine[sqlEngineName=\"geon\"]/jdbcConnect";
096
097        private DataSourceControllerFactory _nodeController = null;
098
099        // /////////////////////////////////////////////////////////////////
100        // // private variables ////
101
102        private DBQueryDef _queryDef = null;
103
104        private EcogridMetaDataCacheItem _cacheMetaDataItem = null;
105
106        // /////////////////////////////////////////////////////////////////
107        // // private variables ////
108
109        /** Icon indicating the communication region. */
110        protected EllipseAttribute _elli;
111        protected ResizablePolygonAttribute _rect;
112        protected TextAttribute _text;
113
114        // /////////////////////////////////////////////////////////////////
115        // // ports and parameters ////
116
117        public StringAttribute _idAtt = null;
118
119        public StringAttribute _endpointAtt = null;
120
121        public StringAttribute _namespaceAtt = null;
122
123        public StringAttribute _descriptionAtt = null;
124
125        public SingletonParameter hideCon;
126
127        public SingletonParameter _hideNS;
128
129        protected final static Log log;
130        static {
131                log = LogFactory
132                                .getLog("org.kepler.dataproxy.datasource.geon.GEONDatabaseResource");
133        }
134
135        /**
136         * Construct an actor with the given container and name.
137         * 
138         * @param container
139         *            The container.
140         * @param name
141         *            The name of this actor.
142         * @exception IllegalActionException
143         *                If the actor cannot be contained by the proposed
144         *                container.
145         * @exception NameDuplicationException
146         *                If the container already has an actor with this name. s * @since
147         */
148        public GEONDatabaseResource(CompositeEntity container, String name)
149                        throws NameDuplicationException, IllegalActionException {
150                super(container, name);
151
152                TextStyle queryDefTS = new TextStyle(query, "query");
153
154                hideCon = new SingletonParameter(dbcon, "_hide");
155                hideCon.setToken(BooleanToken.TRUE);
156
157                // _sqlAttr = new StringAttribute(this, "sqlDef");
158                // _sqlAttr.setVisibility(Settable.NONE);
159                // TextStyle sqlDefTS = new TextStyle(_sqlAttr, "sqlDef");
160
161                _idAtt = new StringAttribute(this, RECORDID);
162                _idAtt.setVisibility(Settable.NOT_EDITABLE);
163
164                _endpointAtt = new StringAttribute(this, ENDPOINT);
165                _endpointAtt.setVisibility(Settable.NOT_EDITABLE);
166
167                _namespaceAtt = new StringAttribute(this, NAMESPACE);
168                _namespaceAtt.setVisibility(Settable.NONE);
169
170                _descriptionAtt = new StringAttribute(this, "description");
171                // _descriptionAtt.setVisibility(Settable.NOT_EDITABLE);
172                TextStyle descTSatt = new TextStyle(_descriptionAtt, "descriptionTS");
173
174                // create tableau for editting the SQL String
175                // _qbTableauFactory = new QBTableauFactory(this, "_tableauFactory");
176
177                // Create a node controller to control the context menu
178                _nodeController = new DataSourceControllerFactory(this,
179                                "_controllerFactory");
180
181                EditorIcon node_icon = new EditorIcon(this, "_icon");
182
183                _rect = new ResizablePolygonAttribute(node_icon, "inner");
184                // _rect.rounding.setToken("5.0");
185                _rect.vertices.setToken("{-20,0, -20,40, 20,40, 20,0}");
186                _rect.width.setToken("40");
187                _rect.height.setToken("40");
188                _rect.centered.setToken("false");
189                _rect.lineColor.setToken(BLACK);
190
191                // Create the folder
192                _elli = new EllipseAttribute(node_icon, "outer");
193                // _elli.
194                // _poly1.vertices.setToken("{7,-27, 7,-8, 28,-10, 28,-27, 16,-27, 15,-29, 7,-29}");
195                // _elli.vertices.setToken("{0,0, 8,-2, 9,1, 24,-2, 24,20, 0,24, 0,0}");
196                _elli.width.setToken("40");
197                _elli.height.setToken("30");
198                _elli.centered.setToken("true");
199                _elli.lineColor.setToken(BLACK);
200
201                _text = new TextAttribute(node_icon, "text");
202                _text.textSize.setExpression("11");
203
204                setIconStatus(TITLE_BINARY, YELLOW);
205
206                node_icon.setPersistent(false);
207                /*
208                 * if (_TypeHash.size() == 0) { for (int i = 0; i < DATATYPES.length;
209                 * i++) { _TypeHash.put(DATATYPES[i], BASETYPES[i]); } }
210                 */}
211
212        /**
213         * Callback for changes in attribute values.
214         */
215        public void attributeChanged(ptolemy.kernel.util.Attribute attribute)
216                        throws ptolemy.kernel.util.IllegalActionException {
217                // dbg.print("In attribute change method", 2);
218                /*
219                 * if (attribute == _sqlAttr) { if (_sqlAttr != null &&
220                 * !_sqlAttr.equals("")) { String sqlXMLStr = ((Settable)
221                 * _sqlAttr).getExpression(); DBQueryDef queryDef =
222                 * DBQueryDefParserEmitter.parseQueryDef( _schemaDef, sqlXMLStr); String
223                 * sqlStr = DBQueryDefParserEmitter.createSQL(_schemaDef, queryDef); if
224                 * (sqlStr != null) { query.setToken(new StringToken(sqlStr)); } } }
225                 * else if (attribute == _schemaAttr) { String schemaDef = ((Settable)
226                 * _schemaAttr).getExpression();
227                 * 
228                 * if (schemaDef.length() > 0) { dbg.print("schemaDef >>" + schemaDef +
229                 * "<<", 2); _schemaDef =
230                 * DBSchemaParserEmitter.parseSchemaDef(schemaDef); } }
231                 */
232                if ((attribute.getName().equals(RECORDID) || attribute.getName()
233                                .equals(ENDPOINT))
234                                // && this.hasConnectionValues()
235                                && !(attribute.getContainer().getContainer() instanceof ResultTreeRoot)) {
236                        log.debug("change recorid or endpoints");
237                        setIconStatus(TITLE_BUSY, RED);
238                        String _recordId = getRecordId();
239                        String _endpoint = getEndpoint();
240                        if (_recordId != null && !_recordId.equals("") && _endpoint != null
241                                        && !_endpoint.equals("")) {
242                                _cacheMetaDataItem = (EcogridMetaDataCacheItem) DataCacheManager
243                                                .getCacheItem(this, "MetaData " + _recordId, _endpoint,
244                                                                EcogridMetaDataCacheItem.class.getName());
245                                _cacheMetaDataItem.setEndPoint(_endpoint);
246                                _cacheMetaDataItem.setRecordId(_recordId);
247                                _cacheMetaDataItem.start();
248                        }
249                } else
250                        super.attributeChanged(attribute);
251        }
252
253        /**
254         * Get the database connection.
255         * 
256         * @throws IllegalActionException
257         */
258        public void getConnection() throws IllegalActionException {
259
260    ConfigurationManager confMan = ConfigurationManager.getInstance();
261    ConfigurationProperty commonProperty = confMan.getProperty(ConfigurationManager.getModule("common"));
262    ConfigurationProperty geonSqlEngine = commonProperty
263      .getProperty("sqlEngines").findProperties("sqlEngineName", "geon", true).get(0);
264    
265    String dbURL = geonSqlEngine.getProperty("jdbcConnect").getValue() + "/" + getRecordId();
266
267                try {
268                        Class.forName(geonSqlEngine.getProperty("dbDriver").getValue()).newInstance();
269                        _db = DriverManager.getConnection(dbURL,
270                                        geonSqlEngine.getProperty("email").getValue(),
271          geonSqlEngine.getProperty("userName").getValue());
272                } catch (Exception e) {
273                        e.printStackTrace();
274                        throw new IllegalActionException(this, e, "CONNECTION FAILURE");
275                }
276        }
277
278        /**
279         * Get a URL pointer to the ADN documentation for this data source.
280         * 
281         * @return URL the URL of the HTML file containing the documentation
282         */
283        public URL getDocumentation() // TODO:: MODIFY THIS FUNCTION!!!
284        {
285                try {
286                        URL htmlDoc = ADNMetadataSpecification
287                                        .getDocumentation(getRecordId());
288                        return htmlDoc;
289                } catch (Exception ex) {
290                        return null;
291                }
292        }
293
294        /**
295         * Get the endpoint of this record. The endpoint indicates where the service
296         * generating the record can be accessed.
297         * 
298         * @return endpoint the URL of the service that contains the record
299         */
300        public String getEndpoint() {
301                String value = null;
302                try {
303                        StringAttribute attribute = (StringAttribute) this
304                                        .getAttribute(ENDPOINT);
305                        value = attribute.getExpression();
306                } catch (Exception e) {
307                        System.err.println("getEndpoint - ENDPOINT attr is null.");
308                }
309                return value;
310        }
311
312        /**
313         * Get the namespace of this record.
314         * 
315         * @return namespace the URL of the service that contains the record
316         */
317        public String getNamespace() {
318                String value = null;
319                try {
320                        StringAttribute attribute = (StringAttribute) this
321                                        .getAttribute(NAMESPACE);
322                        value = attribute.getExpression();
323                } catch (Exception e) {
324                        System.err.println("getNamespace - NAMESPACE attr is null.");
325                }
326                return value;
327        }
328
329        /**
330         * Get the identifier of this record.
331         * 
332         * @return the String that uniquely identifies the record
333         */
334        public String getRecordId() {
335                String value = null;
336                try {
337                        StringAttribute attribute = (StringAttribute) this
338                                        .getAttribute(RECORDID);
339                        value = attribute.getExpression();
340                } catch (Exception e) {
341                        System.err.println("getRecordId - RECORDID attr is null.");
342                }
343                return value;
344        }
345
346        /**
347         * Creates the schema definition from the cached metadata file
348         */
349        private void createSchemaFromData(File cachedMetaDataFile) {
350                StringBuffer schema = new StringBuffer();
351                schema.append("<schema>\n");
352                try {
353                        DocumentBuilderFactory factory = DocumentBuilderFactory
354                                        .newInstance();
355                        factory.setValidating(false);
356                        DocumentBuilder builder = factory.newDocumentBuilder();
357                        InputStream is = new FileInputStream(cachedMetaDataFile);
358                        Document doc = builder.parse(is);
359
360                        int numTables = 0;
361
362                        // NodeList tables = XPathAPI.selectNodeList(doc, TABLEENTITY);
363                        NodeList schemas = XPathAPI.selectNodeList(doc, SCHEMAENTITY);
364                        for (int x = 0; x < schemas.getLength(); x++) {
365                                String schemaName = ((Element) schemas.item(x))
366                                                .getAttribute(SCHEMANAMEATT);
367                                NodeList tables = schemas.item(x).getChildNodes();
368                                for (int i = 0; i < tables.getLength(); i++) {
369                                        Node child = tables.item(i);
370                                        if (!(child instanceof Element))
371                                                continue;
372                                        // get table name
373                                        String tableName = ((Element) tables.item(i))
374                                                        .getAttribute(TABLENAMEATT);
375                                        if (tableName == null || tableName.equals(""))
376                                                continue; // table with no name
377                                        else {
378                                                // get fields
379                                                if (schemaName != null && !schemaName.equals("")
380                                                                && schemas.getLength() > 1)
381                                                        tableName = schemaName + "." + tableName;
382                                                StringBuffer table = new StringBuffer();
383                                                table.append("  <table name=\"" + tableName + "\">\n");
384                                                int numFields = 0;
385
386                                                NodeList fields = ((Element) tables.item(i))
387                                                                .getElementsByTagName(FIELDTAG);
388                                                for (int j = 0; j < fields.getLength(); j++) {
389                                                        Element field = (Element) fields.item(j);
390                                                        // get field name
391                                                        String fieldName = field.getAttribute(FIELDATT);
392                                                        if (fieldName == null || fieldName.equals(""))
393                                                                continue;
394                                                        else {
395                                                                numFields++;
396
397                                                                // get field type
398                                                                NodeList datatypes = field
399                                                                                .getElementsByTagName(FIELDDATATYPE);
400                                                                String datatype = datatypes.item(0)
401                                                                                .getFirstChild().getNodeValue();
402                                                                if (datatype == null || datatype.equals(""))
403                                                                        datatype = "UNKNOWN";
404                                                                NodeList fieldsizes = field
405                                                                                .getElementsByTagName(FIELDSIZE);
406                                                                String size = fieldsizes.item(0)
407                                                                                .getFirstChild().getNodeValue();
408                                                                if (size != null && !size.equals(""))
409                                                                        datatype += "(" + size + ")";
410                                                                NodeList constraints = field
411                                                                                .getElementsByTagName(FIELDRESTRICTION);
412                                                                String constraint = constraints.item(0)
413                                                                                .getFirstChild().getNodeValue();
414                                                                if (constraint != null
415                                                                                && constraint.trim().toLowerCase()
416                                                                                                .startsWith("no"))
417                                                                        datatype += "  not null";
418                                                                table.append("    <field name=\"" + fieldName
419                                                                                + "\" dataType=\"" + datatype
420                                                                                + "\"/>\n");
421                                                        }
422                                                }
423                                                table.append("  </table>\n");
424                                                if (numFields > 0) {
425                                                        numTables++;
426                                                        schema.append(table.toString());
427                                                }
428                                        }
429                                }
430                        }
431                        schema.append("</schema>");
432                        if (numTables > 0) {
433                                _schemaAttr.setExpression(schema.toString());
434                        } else {
435                                setIconStatus(TITLE_ERROR, MAGENTA);
436                        }
437
438                } catch (Exception ex) {
439                        System.out.println("Unable to populate schema: " + ex.getMessage());
440                }
441
442        }
443
444        /**
445         * Set the text and color of the icon
446         * 
447         * @param aText
448         * @param aColor
449         */
450        private void setIconStatus(String aText, String aColor) {
451                try {
452                        _text.text.setExpression(aText);
453                        _elli.fillColor.setToken(aColor);
454                        _rect.fillColor.setToken(aColor);
455                } catch (Exception e) {
456                }
457        }
458
459        // ------------------------------------------------------------------------
460        // -- DataCacheListener
461        // ------------------------------------------------------------------------
462
463        public void complete(DataCacheObject aItem) {
464                log.debug("complete: " + this);
465
466                aItem.removeListener(this);
467
468                // setIconStatus(TITLE_BUSY, RED);
469                if (aItem.isReady()) {
470
471                        boolean resultsetWasOK = false;
472                        try {
473                                String cachedFileName = aItem.getAbsoluteFileName();
474                                if (cachedFileName != null && cachedFileName.length() > 0) {
475                                        File cachedMetaDataFile = new File(cachedFileName);
476                                        if (cachedMetaDataFile.length() > 0) {
477                                                createSchemaFromData(cachedMetaDataFile);
478                                                setIconStatus(TITLE_BINARY, YELLOW);
479                                        } else {
480                                                setIconStatus(TITLE_ERROR, MAGENTA);
481                                                System.err.println("File " + cachedFileName
482                                                                + " is empty.");
483                                        }
484                                } else {
485                                        setIconStatus(TITLE_ERROR, MAGENTA);
486                                        System.err
487                                                        .println("Cached File Name (LocalName) is null or empty!");
488                                }
489                        } catch (Exception e) {
490                                setIconStatus(TITLE_ERROR, MAGENTA);
491                        }
492
493                        // } else {
494                        // setIconStatus(TITLE_ERROR, MAGENTA);
495                }
496        }
497}