Source code

001package org.kepler.actor.database;
002/*
003 * Copyright (c) 2009-2010 The Regents of the University of California.
004 * All rights reserved.
005 *
006 * '$Author: welker $'
007 * '$Date: 2010-05-05 22:21:26 -0700 (Wed, 05 May 2010) $' 
008 * '$Revision: 24234 $'
009 * 
010 * Permission is hereby granted, without written agreement and without
011 * license or royalty fees, to use, copy, modify, and distribute this
012 * software and its documentation for any purpose, provided that the above
013 * copyright notice and the following two paragraphs appear in all copies
014 * of this software.
015 *
016 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
017 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
018 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
019 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
020 * SUCH DAMAGE.
021 *
022 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
023 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
024 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
025 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
026 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
027 * ENHANCEMENTS, OR MODIFICATIONS.
028 *
029 */
030
031
032import java.sql.Connection;
033import java.sql.ResultSet;
034import java.sql.SQLException;
035import java.sql.Statement;
036
037import org.geon.OpenDBConnection;
038
039import ptolemy.actor.TypedIOPort;
040import ptolemy.actor.lib.LimitedFiringSource;
041import ptolemy.actor.parameters.PortParameter;
042import ptolemy.data.BooleanToken;
043import ptolemy.data.RecordToken;
044import ptolemy.data.StringToken;
045import ptolemy.data.type.BaseType;
046import ptolemy.data.type.Type;
047import ptolemy.kernel.CompositeEntity;
048import ptolemy.kernel.util.IllegalActionException;
049import ptolemy.kernel.util.NameDuplicationException;
050
051/**
052 * Create a database table if the table doesn't exist.
053 * 
054 * @author Jing Tao
055 */
056
057public class DatabaseTableCreator extends LimitedFiringSource {
058  
059  public DatabaseTableCreator(CompositeEntity container, String name)
060      throws NameDuplicationException, IllegalActionException {
061    super(container, name);
062    tableNameParam = new PortParameter(this, "Table Name");
063    tableNameParam.setStringMode(true);
064    tableNameParam.setTypeEquals(BaseType.STRING);
065    tableNameParam.getPort().setTypeEquals(BaseType.STRING);
066    
067    createTableIfNotExistParam = new PortParameter(this, "Create the table if it doesn't exist");
068    createTableIfNotExistParam.setStringMode(false);
069    createTableIfNotExistParam.setTypeEquals(BaseType.BOOLEAN);
070    createTableIfNotExistParam.getPort().setTypeEquals(BaseType.BOOLEAN);
071    createTableIfNotExistParam.setExpression("true");
072    
073    sqlScriptParam = new PortParameter(this, "SQL Script");
074    sqlScriptParam.setStringMode(true);
075    sqlScriptParam.setTypeEquals(BaseType.STRING);
076    sqlScriptParam.getPort().setTypeEquals(BaseType.STRING);
077    
078    dbParams = new PortParameter(this, "Database Param");
079    Type type = OpenDBConnection.getDBParamsType();
080    dbParams.setTypeEquals(type);
081    dbParams.getPort().setTypeEquals(type);
082    dbParams.setExpression("{driver = \"org.hsqldb.jdbcDriver\", password = \"pass\", url = \"jdbc:hsqldb:hsql://localhost/hsqldb\", user = \"sa\"}");
083    boolean isInput = false;
084    boolean isOutput = true;
085    statusPort = new TypedIOPort(this, "Status", isInput, isOutput);
086    statusPort.setMultiport(false);
087    statusPort.setTypeEquals(BaseType.BOOLEAN);
088    
089  }
090
091  // /////////////////////////////////////////////////////////////////
092  // // ports and parameters ////
093
094 
095  /** Output of this actor. Ture if the table exists or was created successfully, false otherwise*/
096  public TypedIOPort statusPort;
097  
098  /** The name of the table which will be created**/
099  public PortParameter tableNameParam;
100  
101  /** Flag indicates to create the table if the table doesn't exist*/
102  public PortParameter createTableIfNotExistParam;
103  
104  /** A sql script to create the table**/
105  public PortParameter sqlScriptParam;
106   
107
108  /** Parameter to access a database. It includes user name, password, 
109        driver name and db url**/
110  public PortParameter dbParams;
111  
112 
113
114  // /////////////////////////////////////////////////////////////////
115  // // public methods ////
116
117  /** Close the connection if open. */
118  public void initialize() throws IllegalActionException {
119    super.initialize();
120    _closeConnection();
121  }
122  
123  /**
124   * Reconfigure actor when certain attributes change.
125   * 
126   * @param attribute
127   *            The changed Attribute.
128   * @throws ptolemy.kernel.util.IllegalActionException
129   * 
130   */
131  public void attributeChanged(ptolemy.kernel.util.Attribute attribute)
132      throws ptolemy.kernel.util.IllegalActionException {
133        
134    if (attribute == tableNameParam) {
135      if (tableNameParam != null && tableNameParam.getToken() != null){
136          tableName = ((StringToken)tableNameParam.getToken()).stringValue();
137          //System.out.println("get the table name "+tableName+" from attributedChanged method");
138        }
139    } else if (attribute == createTableIfNotExistParam) {
140      if (createTableIfNotExistParam != null && createTableIfNotExistParam.getToken() != null){
141        createTableIfNotExisted = ((BooleanToken)createTableIfNotExistParam.getToken()).booleanValue();
142        //System.out.println("get the flag createTableIfNotExisted "+createTableIfNotExisted+" from attributedChanged method");
143      }
144    } else if(attribute == sqlScriptParam){
145      if (sqlScriptParam != null && sqlScriptParam.getToken() != null){
146        sqlScript = ((StringToken)sqlScriptParam.getToken()).stringValue();
147        //System.out.println("get the sql "+sqlScript+" from attributedChanged method");
148      }
149    } else if(attribute == dbParams){
150      dbParams.update();
151    } else {
152      super.attributeChanged(attribute);
153    }
154  }
155  
156
157  /** If connection is closed, open a new one. */
158  public void fire() throws IllegalActionException {
159    super.fire();
160    //System.out.println("the fire in DatabaseTable creator !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
161    boolean ifTableExist = true;
162    if (tableNameParam != null && tableNameParam.getToken() != null) {
163      tableName = ((StringToken)tableNameParam.getToken()).stringValue();
164      //System.out.println("get the table name "+tableName+" from fire method");
165    }
166    if (createTableIfNotExistParam != null && createTableIfNotExistParam.getToken() != null) {
167      createTableIfNotExisted = ((BooleanToken)createTableIfNotExistParam.getToken()).booleanValue();
168      //System.out.println("get the flag createTableIfNotExisted "+createTableIfNotExisted+" from fire method");
169    }
170    if (sqlScriptParam != null && sqlScriptParam.getToken() != null) {
171      sqlScript = ((StringToken)sqlScriptParam.getToken()).stringValue();
172      //System.out.println("get the sql "+sqlScript+" from fire method");
173    }
174    if (_db == null) {
175      getConnection();
176    }
177    
178    //System.out.println("final table name: "+tableName);
179    String selectionSQL = _createSelectSQL(tableName);
180   
181    ResultSet rs;
182    try {
183      Statement st = _db.createStatement();
184      rs = st.executeQuery(selectionSQL);
185    } catch (SQLException e) {
186      ifTableExist = false;
187    }
188    if(ifTableExist){
189      //table does exist, sent status "true" to output
190      //System.out.println("The table "+tableName+" does exist");
191      statusPort.send(0, new BooleanToken(true));
192    } else{
193      //System.out.println("the final createTableIfNotExisted is "+createTableIfNotExisted);
194      //System.out.println("the final sql is "+sqlScript);
195      if(!createTableIfNotExisted){
196        //since we don't need create the table, just send the false status
197        //System.out.println("The table "+tableName+" doesn't exist but we don't need to create it");
198        statusPort.send(0, new BooleanToken(false));
199      } else {
200        try {
201          
202          Statement st = _db.createStatement();
203          rs = st.executeQuery(sqlScript);
204          //System.out.println("Successfully created the table "+tableName+" with sql "+sqlScript);
205          statusPort.send(0, new BooleanToken(true));
206        } catch (SQLException e) {
207          //System.out.println("Failed to create the table "+tableName+" with sql "+sqlScript);
208          ifTableExist = false;
209        }
210      }
211      
212    }
213  }
214
215  /** Close the connection if open. */
216  public void wrapup() throws IllegalActionException {
217    super.wrapup();
218    _closeConnection();
219  }
220
221  // /////////////////////////////////////////////////////////////////
222  // // protected methods ////
223
224  /** Get the database connection. */
225  protected void getConnection() throws IllegalActionException {
226   dbParams.update();
227    RecordToken params = (RecordToken) dbParams.getToken();
228    if (params != null) {
229      _db = OpenDBConnection.getConnection(params);
230    } else {
231      throw new IllegalActionException(this, 
232          "Please specify the database parameters. Actor couldn't access the database without those paramters."+
233          "The parameter looks like: {driver = \"org.hsqldb.jdbcDriver\", password = \"pass\", url = \"jdbc:hsqldb:hsql://localhost/hsqldb\", user = \"sa\"}");
234      } 
235  }
236
237  // /////////////////////////////////////////////////////////////////
238  // // protected variables ////
239
240  /** A JDBC database connection. */
241  protected Connection _db = null;
242  protected String tableName = null;
243  protected boolean createTableIfNotExisted= false;
244  protected String sqlScript = null;
245
246  // /////////////////////////////////////////////////////////////////
247  // // private methods ////
248
249  /** Close the connection if open. */
250  private void _closeConnection() throws IllegalActionException {
251    try {
252      if (_db != null) {
253        _db.close();
254      }
255      _db = null;
256    } catch (SQLException e) {
257      throw new IllegalActionException(this, "SQLException: "
258          + e.getMessage());
259    }
260  }
261  
262  /** Create the select sql command with the given table name */
263  private String _createSelectSQL(String tableName) throws IllegalActionException {
264    String selectionSql = null;
265    if(tableName != null && !tableName.trim().equals("")){
266      selectionSql = "SELECT * FROM "+tableName;
267    } else {
268      throw new IllegalActionException(this, "Exception: Please specify the table name which you want to create. ");
269    }
270    return selectionSql;
271  }
272}