airavata-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chath...@apache.org
Subject [1/4] git commit: adding database start up while server starts - AIRAVATA-1020
Date Thu, 13 Feb 2014 15:12:14 GMT
Updated Branches:
  refs/heads/master 41da886a3 -> 5dd40195f


adding database start up while server starts - AIRAVATA-1020


Project: http://git-wip-us.apache.org/repos/asf/airavata/repo
Commit: http://git-wip-us.apache.org/repos/asf/airavata/commit/6555180e
Tree: http://git-wip-us.apache.org/repos/asf/airavata/tree/6555180e
Diff: http://git-wip-us.apache.org/repos/asf/airavata/diff/6555180e

Branch: refs/heads/master
Commit: 6555180ecc23d501b72f494c464bcb87e1bd155c
Parents: b766421
Author: chathuri <chathuri@apache.org>
Authored: Wed Feb 12 16:42:55 2014 -0500
Committer: chathuri <chathuri@apache.org>
Committed: Wed Feb 12 16:42:55 2014 -0500

----------------------------------------------------------------------
 airavata-api/airavata-api-server/pom.xml        |   5 +
 .../airavata/api/server/AiravataServer.java     |   6 +-
 .../api/server/util/ConnectionPool.java         | 381 +++++++++++++++++++
 .../api/server/util/DatabaseCreator.java        | 340 +++++++++++++++++
 .../airavata/api/server/util/JdbcStorage.java   | 177 +++++++++
 .../api/server/util/OrchestratorInitUtil.java   |  25 ++
 .../api/server/util/RegistryInitUtil.java       | 173 +++++++++
 .../main/resources/airavata-registry-derby.sql  | 325 ++++++++++++++++
 .../main/resources/airavata-registry-mysql.sql  | 327 ++++++++++++++++
 .../main/resources/airavata-server.properties   |  30 +-
 .../experimentModel.thrift                      |  42 ++
 .../registry/jpa/impl/RegistryImpl.java         |  39 +-
 .../apache/airavata/registry/cpi/Registry.java  |  14 +-
 13 files changed, 1854 insertions(+), 30 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/pom.xml
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/pom.xml b/airavata-api/airavata-api-server/pom.xml
index 3eb2434..b9334c9 100644
--- a/airavata-api/airavata-api-server/pom.xml
+++ b/airavata-api/airavata-api-server/pom.xml
@@ -33,6 +33,11 @@
             <version>${project.version}</version>
         </dependency>
         <dependency>
+            <groupId>org.apache.airavata</groupId>
+            <artifactId>airavata-common-utils</artifactId>
+            <version>${project.version}</version>
+        </dependency>
+        <dependency>
             <groupId>org.apache.thrift</groupId>
             <artifactId>libthrift</artifactId>
             <version>${thrift.version}</version>

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/AiravataServer.java
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/AiravataServer.java b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/AiravataServer.java
index a81bfd6..5ee8443 100644
--- a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/AiravataServer.java
+++ b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/AiravataServer.java
@@ -25,6 +25,7 @@ import org.apache.airavata.api.Airavata;
 import org.apache.airavata.api.error.AiravataErrorType;
 import org.apache.airavata.api.error.AiravataSystemException;
 import org.apache.airavata.api.server.handler.MockAiravataServerHandler;
+import org.apache.airavata.api.server.util.RegistryInitUtil;
 import org.apache.thrift.server.TServer;
 import org.apache.thrift.server.TSimpleServer;
 import org.apache.thrift.transport.TServerSocket;
@@ -42,6 +43,7 @@ public class AiravataServer {
 
     public static void StartAiravataServer(Airavata.Processor<MockAiravataServerHandler> mockAiravataServer) throws AiravataSystemException {
         try {
+            RegistryInitUtil.initializeDB();
             TServerTransport serverTransport = new TServerSocket(THRIFT_SERVER_PORT);
             TServer server = new TSimpleServer(
                     new TServer.Args(serverTransport).processor(mockAiravataServer));
@@ -50,9 +52,7 @@ public class AiravataServer {
             server.serve();
         } catch (TTransportException e) {
             logger.error(e.getMessage());
-            AiravataSystemException airavataSystemException
-                    = new AiravataSystemException(AiravataErrorType.INTERNAL_ERROR);
-            throw airavataSystemException;
+            throw new AiravataSystemException(AiravataErrorType.INTERNAL_ERROR);
         }
     }
 

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/ConnectionPool.java
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/ConnectionPool.java b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/ConnectionPool.java
new file mode 100644
index 0000000..b0f1e77
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/ConnectionPool.java
@@ -0,0 +1,381 @@
+/*
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+*/
+
+package org.apache.airavata.api.server.util;
+
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.Stack;
+import java.util.concurrent.Semaphore;
+
+
+/**
+ * A class for preallocating, recycling, and managing JDBC connections.
+ */
+public class ConnectionPool {
+    private static final Logger logger = LoggerFactory.getLogger(ConnectionPool.class);
+
+    private long MAX_IDLE_TIME = 5 * 60 * 1000; // 5 minutes
+
+    private String driver;
+    private String url;
+    private String username;
+    private String password;
+    private String jdbcUrl;
+
+    private int maxConnections;
+
+    private boolean autoCommit = true;
+    private boolean waitIfBusy;
+
+    private Semaphore needConnection = new Semaphore(0);
+    private boolean stop;
+
+    private Stack<Connection> availableConnections;
+    private Stack<Connection> busyConnections;
+
+    private HashMap<Connection, Long> lastAccessTimeRecord = new HashMap<Connection, Long>();
+
+    private String urlType = "";
+
+    private DataSource datasource;
+
+    private int transactionIsolation = Connection.TRANSACTION_NONE;
+
+    private Thread clenupThread;
+    private Thread producerThread;
+
+    public ConnectionPool(String driver, String url, String username, String password, int initialConnections,
+                          int maxConnections, boolean waitIfBusy) throws SQLException {
+        this.driver = driver;
+        this.url = url;
+        this.username = username;
+        this.password = password;
+        this.urlType = "speratedURL";
+        initialize(initialConnections, maxConnections, waitIfBusy);
+    }
+
+    public ConnectionPool(String driver, String jdbcUrl, int initialConnections, int maxConnections,
+                          boolean waitIfBusy, boolean autoCommit, int transactionIsolation) throws SQLException {
+        this.driver = driver;
+        this.jdbcUrl = jdbcUrl;
+        this.urlType = "simpleURL";
+        this.autoCommit = autoCommit;
+        this.transactionIsolation = transactionIsolation;
+        initialize(initialConnections, maxConnections, waitIfBusy);
+    }
+
+    public ConnectionPool(String driver, String jdbcUrl, int initialConnections, int maxConnections, boolean waitIfBusy)
+            throws SQLException {
+        this.driver = driver;
+        this.jdbcUrl = jdbcUrl;
+        this.urlType = "simpleURL";
+        initialize(initialConnections, maxConnections, waitIfBusy);
+    }
+
+    public ConnectionPool(DataSource dataSource, int initialConnections, int maxConnections, boolean waitIfBusy)
+            throws SQLException {
+        this.urlType = "dataSource";
+        this.datasource = dataSource;
+        initialize(initialConnections, maxConnections, waitIfBusy);
+    }
+
+    /**
+     * Check if this connection pool is auto commit or not
+     *
+     * @return
+     */
+    public boolean isAutoCommit() {
+        return this.autoCommit;
+    }
+
+    private void initialize(int initialConnections, int maxConnections, boolean waitIfBusy) throws SQLException {
+        this.maxConnections = maxConnections;
+        this.waitIfBusy = waitIfBusy;
+
+        int sizeOfConnections = (initialConnections > maxConnections) ? maxConnections : initialConnections;
+
+        availableConnections = new Stack<Connection>();
+        busyConnections = new Stack<Connection>();
+
+        for (int i = 0; i < sizeOfConnections; i++) {
+            Connection con = makeNewConnection();
+            setTimeStamp(con);
+            availableConnections.push(con);
+
+        }
+
+        producerThread = new Thread(new FillUpThread());
+        producerThread.start();
+
+        clenupThread = new Thread(new CleanUpThread());
+        clenupThread.start();
+    }
+
+    public synchronized Connection getConnection() throws SQLException {
+        if (!availableConnections.isEmpty()) {
+            Connection existingConnection = availableConnections.pop();
+
+            // If connection on available list is closed (e.g.,
+            // it timed out), then remove it from available list
+            // and race for a connection again.
+            if (existingConnection.isClosed()) {
+                lastAccessTimeRecord.remove(existingConnection);
+                // notifyAll for fairness
+                notifyAll();
+            } else {
+                busyConnections.push(existingConnection);
+                setTimeStamp(existingConnection);
+                return existingConnection;
+            }
+        } else if (!waitIfBusy && busyConnections.size() >= maxConnections) {
+            // You reached maxConnections limit and waitIfBusy flag is false.
+            // Throw SQLException in such a case.
+            throw new SQLException("Connection limit reached");
+        } else {
+
+            if (busyConnections.size() < maxConnections) {
+                // available connection is empty, but total number of connection
+                // doesn't reach maxConnection. Request for more connection
+                needConnection.release();
+            }
+
+            try {
+                // wait for free connection
+                wait();
+            } catch (InterruptedException ie) {
+            }
+        }
+        // always race for connection forever
+        return getConnection();
+    }
+
+    // This explicitly makes a new connection. Called in
+    // the foreground when initializing the ConnectionPool,
+    // and called in the background when running.
+    private Connection makeNewConnection() throws SQLException {
+        try {
+            // Load database driver if not already loaded
+            Class.forName(driver);
+            Connection connection;
+            // Establish network connection to database
+            if (urlType.equals("speratedURL")) {
+                connection = DriverManager.getConnection(url, username, password);
+            } else if (urlType.equals("simpleURL")) {
+                connection = DriverManager.getConnection(jdbcUrl);
+            } else { // if(urlType.equals("dataSource")){
+                connection = datasource.getConnection();
+            }
+            connection.setTransactionIsolation(this.transactionIsolation);
+            connection.setAutoCommit(this.autoCommit);
+            return connection;
+        } catch (ClassNotFoundException cnfe) {
+            // Simplify try/catch blocks of people using this by
+            // throwing only one exception type.
+            throw new SQLException("Can't find class for driver: " + driver);
+        }
+    }
+
+    private synchronized void fillUpConnection(Connection conn) {
+        setTimeStamp(conn);
+        availableConnections.push(conn);
+
+        // notify all since new connection is created
+        notifyAll();
+    }
+
+    private void setTimeStamp(Connection connection) {
+        lastAccessTimeRecord.put(connection, System.currentTimeMillis());
+    }
+
+    // The database connection cannot be left idle for too long, otherwise TCP
+    // connection will be broken.
+    /**
+     * From http://forums.mysql.com/read.php?39,28450,57460#msg-57460 Okay, then it looks like wait_timeout on the
+     * server is killing your connection (it is set to 8 hours of idle time by default). Either set that value higher on
+     * your server, or configure your connection pool to not hold connections idle that long (I prefer the latter). Most
+     * folks I know that run MySQL with a connection pool in high-load production environments only let connections sit
+     * idle for a matter of minutes, since it only takes a few milliseconds to open a connection, and the longer one
+     * sits idle the more chance it will go "bad" because of a network hiccup or the MySQL server being restarted.
+     *
+     * @throws java.sql.SQLException
+     */
+    private boolean isConnectionStale(Connection connection) {
+        long currentTime = System.currentTimeMillis();
+        long lastAccess = lastAccessTimeRecord.get(connection);
+        if (currentTime - lastAccess > MAX_IDLE_TIME) {
+            return true;
+        } else
+            return false;
+    }
+
+    private synchronized void closeStaleConnections() {
+        // close idle connections
+        Iterator<Connection> iter = availableConnections.iterator();
+        while (iter.hasNext()) {
+            Connection existingConnection = iter.next();
+            if (isConnectionStale(existingConnection)) {
+                try {
+                    existingConnection.close();
+                    iter.remove();
+                } catch (SQLException sql) {
+                    logger.error(sql.getMessage(), sql);
+                }
+            }
+        }
+        // close busy connections that have been checked out for too long.
+        // This should not happen since this means program has bug for not
+        // releasing connections .
+        iter = busyConnections.iterator();
+        while (iter.hasNext()) {
+            Connection busyConnection = iter.next();
+            if (isConnectionStale(busyConnection)) {
+                try {
+                    busyConnection.close();
+                    iter.remove();
+                    logger.warn("****Connection has checked out too long. Forced release. Check the program for calling release connection [free(Connection) method]");
+                } catch (SQLException sql) {
+                    logger.error(sql.getMessage(), sql);
+                }
+            }
+        }
+    }
+
+    public synchronized void free(Connection connection) {
+        busyConnections.removeElement(connection);
+        availableConnections.addElement(connection);
+        // Wake up threads that are waiting for a connection
+        notifyAll();
+    }
+
+    /**
+     * Close all the connections. Use with caution: be sure no connections are in use before calling. Note that you are
+     * not <I>required</I> to call this when done with a ConnectionPool, since connections are guaranteed to be closed
+     * when garbage collected. But this method gives more control regarding when the connections are closed.
+     */
+    public synchronized void dispose() {
+        logger.info("Connection Pool Shutting down");
+
+        // stop clean up thread
+        this.stop = true;
+        this.clenupThread.interrupt();
+
+        // stop producer up thread
+        this.producerThread.interrupt();
+
+        // close all connection
+        closeConnections(availableConnections);
+        availableConnections = new Stack<Connection>();
+        closeConnections(busyConnections);
+        busyConnections = new Stack<Connection>();
+        lastAccessTimeRecord.clear();
+
+        logger.info("All connection is closed");
+
+        try {
+            this.clenupThread.join();
+            this.producerThread.join();
+        } catch (Exception e) {
+            logger.error("Cannot shutdown cleanup thread", e);
+        }
+
+        logger.info("Connection Pool Shutdown");
+    }
+
+    private void closeConnections(Stack<Connection> connections) {
+        while (!connections.isEmpty()) {
+            Connection connection = connections.pop();
+            try {
+                if (!connection.isClosed()) {
+                    connection.close();
+                }
+            } catch (SQLException sqle) {
+                // Ignore errors; garbage collect anyhow
+                logger.warn(sqle.getMessage());
+            }
+        }
+    }
+
+    public synchronized String toString() {
+        String info = "ConnectionPool(" + url + "," + username + ")" + ", available=" + availableConnections.size()
+                + ", busy=" + busyConnections.size() + ", max=" + maxConnections;
+        return (info);
+    }
+
+    class CleanUpThread implements Runnable {
+        public void run() {
+            while (!stop) {
+                try {
+                    Thread.sleep(MAX_IDLE_TIME);
+                    closeStaleConnections();
+                } catch (InterruptedException e) {
+                    logger.info("Clean up thread is interrupted to close");
+                }
+            }
+        }
+    }
+
+    class FillUpThread implements Runnable {
+        public void run() {
+            while (!stop) {
+                try {
+                    // block until get
+                    needConnection.acquire();
+
+                    Connection conn = makeNewConnection();
+                    fillUpConnection(conn);
+                } catch (SQLException e) {
+                    // cannot create connection (increase semaphore value back)
+                    needConnection.release();
+                    logger.error(e.getMessage(), e);
+                } catch (InterruptedException e) {
+                    logger.info("Fill up thread is interrupted to close");
+                    break;
+                }
+            }
+        }
+    }
+
+    public void shutdown(){
+        for (Connection c : availableConnections) {
+            try {
+                c.close();
+            } catch (SQLException e) {
+
+            }
+        }
+
+        for (Connection c : busyConnections) {
+            try {
+                c.close();
+            } catch (SQLException e) {
+                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
+            }
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/DatabaseCreator.java
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/DatabaseCreator.java b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/DatabaseCreator.java
new file mode 100644
index 0000000..4666b5a
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/DatabaseCreator.java
@@ -0,0 +1,340 @@
+/*
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+*/
+package org.apache.airavata.api.server.util;
+
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.io.*;
+import java.sql.*;
+import java.util.StringTokenizer;
+
+/**
+ * This class creates the database tables required for airavata with default configuration this
+ * class creates derby database in server mode. User can specify required database in appropriate
+ * properties files.
+ */
+public class DatabaseCreator {
+
+    public enum DatabaseType {
+        derby("(?i).*derby.*"), mysql("(?i).*mysql.*"), other("");
+
+        private String pattern;
+
+        private DatabaseType(String matchingPattern) {
+            this.pattern = matchingPattern;
+        }
+
+        public String getMatchingPattern() {
+            return this.pattern;
+        }
+    }
+
+    private static DatabaseType[] supportedDatabase = new DatabaseType[] { DatabaseType.derby, DatabaseType.mysql };
+
+    private static Logger log = LoggerFactory.getLogger(DatabaseCreator.class);
+    private static final String delimiter = ";";
+
+    /**
+     * Creates database
+     *
+     * @throws Exception
+     */
+    public static void createRegistryDatabase(Connection conn) throws Exception {
+        createDatabase("airavata-registry", conn);
+    }
+
+
+
+    /**
+     * Checks whether database tables are created by using select * on given table name
+     *
+     * @param tableName
+     *            Table which should be existed
+     * @return <code>true</core> if checkSQL is success, else <code>false</code> .
+     */
+    public static boolean isDatabaseStructureCreated(String tableName, Connection conn) {
+        try {
+
+            log.debug("Running a query to test the database tables existence.");
+
+            // check whether the tables are already created with a query
+            Statement statement = null;
+            try {
+                statement = conn.createStatement();
+                ResultSet rs = statement.executeQuery("select * from " + tableName);
+                if (rs != null) {
+                    rs.close();
+                }
+            } finally {
+                try {
+                    if (statement != null) {
+                        statement.close();
+                    }
+                } catch (SQLException e) {
+                    return false;
+                }
+            }
+        } catch (SQLException e) {
+            return false;
+        }
+
+        return true;
+    }
+
+    /**
+     * executes given sql
+     *
+     * @param sql
+     * @throws Exception
+     */
+    private static void executeSQL(String sql, Connection conn) throws Exception {
+        // Check and ignore empty statements
+        if ("".equals(sql.trim())) {
+            return;
+        }
+
+        Statement statement = null;
+        try {
+            log.debug("SQL : " + sql);
+
+            boolean ret;
+            int updateCount = 0, updateCountTotal = 0;
+            statement = conn.createStatement();
+            ret = statement.execute(sql);
+            updateCount = statement.getUpdateCount();
+            do {
+                if (!ret) {
+                    if (updateCount != -1) {
+                        updateCountTotal += updateCount;
+                    }
+                }
+                ret = statement.getMoreResults();
+                if (ret) {
+                    updateCount = statement.getUpdateCount();
+                }
+            } while (ret);
+
+            log.debug(sql + " : " + updateCountTotal + " rows affected");
+
+            SQLWarning warning = conn.getWarnings();
+            while (warning != null) {
+                log.info(warning + " sql warning");
+                warning = warning.getNextWarning();
+            }
+            conn.clearWarnings();
+        } catch (SQLException e) {
+            if (e.getSQLState().equals("X0Y32")) {
+                // eliminating the table already exception for the derby
+                // database
+                log.info("Table Already Exists", e);
+            } else {
+                throw new Exception("Error occurred while executing : " + sql, e);
+            }
+        } finally {
+            if (statement != null) {
+                try {
+                    statement.close();
+                } catch (SQLException e) {
+                    log.error("Error occurred while closing result set.", e);
+                }
+            }
+        }
+    }
+
+    /**
+     * computes relatational database type using database name
+     *
+     * @return DatabaseType
+     * @throws Exception
+     *
+     */
+    public static DatabaseType getDatabaseType(Connection conn) throws Exception {
+        try {
+            if (conn != null && (!conn.isClosed())) {
+                DatabaseMetaData metaData = conn.getMetaData();
+                String databaseProductName = metaData.getDatabaseProductName();
+                return checkType(databaseProductName);
+            }
+        } catch (SQLException e) {
+            String msg = "Failed to create Airavatadatabase." + e.getMessage();
+            log.error(msg, e);
+            throw new Exception(msg, e);
+        }
+        return DatabaseType.other;
+    }
+
+    /**
+     * Overloaded method with String input
+     *
+     * @return DatabaseType
+     * @throws Exception
+     *
+     */
+    public static DatabaseType getDatabaseType(String dbUrl) throws Exception {
+        return checkType(dbUrl);
+    }
+
+    private static DatabaseType checkType(String text) throws Exception {
+        try {
+            if (text != null) {
+                for (DatabaseType type : supportedDatabase) {
+                    if (text.matches(type.getMatchingPattern()))
+                        return type;
+                }
+            }
+            String msg = "Unsupported database: " + text
+                    + ". Database will not be created automatically by the Airavata. "
+                    + "Please create the database using appropriate database scripts for " + "the database.";
+            throw new Exception(msg);
+
+        } catch (SQLException e) {
+            String msg = "Failed to create Airavatadatabase." + e.getMessage();
+            log.error(msg, e);
+            throw new Exception(msg, e);
+        }
+    }
+
+    /**
+     * Get scripts location which is prefix + "-" + databaseType + ".sql"
+     *
+     * @param prefix
+     * @param databaseType
+     * @return script location
+     */
+    private static String getScriptLocation(String prefix, DatabaseType databaseType) {
+        String scriptName = prefix + "-" + databaseType + ".sql";
+        log.debug("Loading database script from :" + scriptName);
+        return "database_scripts" + File.separator + scriptName;
+    }
+
+    private static void createDatabase(String prefix, Connection conn) throws Exception {
+        Statement statement = null;
+        try {
+            conn.setAutoCommit(false);
+            statement = conn.createStatement();
+            executeSQLScript(getScriptLocation(prefix, DatabaseCreator.getDatabaseType(conn)), conn);
+            conn.commit();
+            log.debug("Tables are created successfully.");
+        } catch (SQLException e) {
+            String msg = "Failed to create database tables for Airavata resource store. " + e.getMessage();
+            log.error(msg, e);
+            conn.rollback();
+            throw new Exception(msg, e);
+        } finally {
+            conn.setAutoCommit(true);
+            try {
+                if (statement != null) {
+                    statement.close();
+                }
+            } catch (SQLException e) {
+                log.error("Failed to close statement.", e);
+            }
+        }
+    }
+
+    private static void executeSQLScript(String dbscriptName, Connection conn) throws Exception {
+        StringBuffer sql = new StringBuffer();
+        BufferedReader reader = null;
+
+        try {
+            InputStream is = DatabaseCreator.class.getClassLoader().getResourceAsStream(dbscriptName);
+            reader = new BufferedReader(new InputStreamReader(is));
+            String line;
+            while ((line = reader.readLine()) != null) {
+                line = line.trim();
+                if (line.startsWith("//")) {
+                    continue;
+                }
+                if (line.startsWith("--")) {
+                    continue;
+                }
+                StringTokenizer st = new StringTokenizer(line);
+                if (st.hasMoreTokens()) {
+                    String token = st.nextToken();
+                    if ("REM".equalsIgnoreCase(token)) {
+                        continue;
+                    }
+                }
+                sql.append(" ").append(line);
+
+                // SQL defines "--" as a comment to EOL
+                // and in Oracle it may contain a hint
+                // so we cannot just remove it, instead we must end it
+                if (line.indexOf("--") >= 0) {
+                    sql.append("\n");
+                }
+                if ((checkStringBufferEndsWith(sql, delimiter))) {
+                    executeSQL(sql.substring(0, sql.length() - delimiter.length()), conn);
+                    sql.replace(0, sql.length(), "");
+                }
+            }
+            // Catch any statements not followed by ;
+            if (sql.length() > 0) {
+                executeSQL(sql.toString(), conn);
+            }
+        } catch (IOException e) {
+            log.error("Error occurred while executing SQL script for creating Airavata database", e);
+            throw new Exception("Error occurred while executing SQL script for creating Airavata database", e);
+
+        } finally {
+            if (reader != null) {
+                reader.close();
+            }
+        }
+    }
+
+    /**
+     * Checks that a string buffer ends up with a given string. It may sound trivial with the existing JDK API but the
+     * various implementation among JDKs can make those methods extremely resource intensive and perform poorly due to
+     * massive memory allocation and copying. See
+     *
+     * @param buffer
+     *            the buffer to perform the check on
+     * @param suffix
+     *            the suffix
+     * @return <code>true</code> if the character sequence represented by the argument is a suffix of the character
+     *         sequence represented by the StringBuffer object; <code>false</code> otherwise. Note that the result will
+     *         be <code>true</code> if the argument is the empty string.
+     */
+    public static boolean checkStringBufferEndsWith(StringBuffer buffer, String suffix) {
+        if (suffix.length() > buffer.length()) {
+            return false;
+        }
+        // this loop is done on purpose to avoid memory allocation performance
+        // problems on various JDKs
+        // StringBuffer.lastIndexOf() was introduced in jdk 1.4 and
+        // implementation is ok though does allocation/copying
+        // StringBuffer.toString().endsWith() does massive memory
+        // allocation/copying on JDK 1.5
+        // See http://issues.apache.org/bugzilla/show_bug.cgi?id=37169
+        int endIndex = suffix.length() - 1;
+        int bufferIndex = buffer.length() - 1;
+        while (endIndex >= 0) {
+            if (buffer.charAt(bufferIndex) != suffix.charAt(endIndex)) {
+                return false;
+            }
+            bufferIndex--;
+            endIndex--;
+        }
+        return true;
+    }
+}

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/JdbcStorage.java
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/JdbcStorage.java b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/JdbcStorage.java
new file mode 100644
index 0000000..f3bf475
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/JdbcStorage.java
@@ -0,0 +1,177 @@
+/*
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+*/
+package org.apache.airavata.api.server.util;
+
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.sql.*;
+
+public class JdbcStorage {
+    private static Logger log = LoggerFactory.getLogger(JdbcStorage.class);
+
+    private ConnectionPool connectionPool;
+
+    public JdbcStorage(String jdbcUrl, String jdbcDriver) {
+        // default init connection and max connection
+        this(3, 50, jdbcUrl, jdbcDriver, true);
+    }
+
+    public JdbcStorage(int initCon, int maxCon, String url, String driver, boolean enableTransactions) {
+        try {
+            if (enableTransactions) {
+                connectionPool = new ConnectionPool(driver, url, initCon, maxCon, true, false,
+                        Connection.TRANSACTION_SERIALIZABLE);
+            } else {
+                connectionPool = new ConnectionPool(driver, url, initCon, maxCon, true);
+            }
+        } catch (Exception e) {
+            throw new RuntimeException("Failed to create database connection pool.", e);
+        }
+    }
+
+    /**
+     * Check if this connection pool is auto commit or not
+     *
+     * @return
+     */
+    public boolean isAutoCommit() {
+        return connectionPool.isAutoCommit();
+    }
+
+    public void commit(Connection conn) {
+        try {
+            if (conn != null && !conn.getAutoCommit()) {
+                conn.commit();
+            }
+        } catch (SQLException sqle) {
+            log.error("Cannot commit data", sqle);
+        }
+    }
+
+    public void commitAndFree(Connection conn) {
+        commit(conn);
+        closeConnection(conn);
+    }
+
+    public void rollback(Connection conn) {
+        try {
+            if (conn != null && !conn.getAutoCommit()) {
+                conn.rollback();
+            }
+        } catch (SQLException sqle) {
+            log.error("Cannot Rollback data", sqle);
+        }
+    }
+
+    public void rollbackAndFree(Connection conn) {
+        rollback(conn);
+        closeConnection(conn);
+    }
+
+    public Connection connect() {
+
+        Connection conn = null;
+        try {
+            conn = connectionPool.getConnection();
+        } catch (SQLException e) {
+            e.printStackTrace();
+            log.error(e.getMessage(), e);
+        }
+        return conn;
+    }
+
+    /**
+     * This method is provided so that you can have better control over the statement. For example: You can use
+     * stmt.setString to convert quotation mark automatically in an UPDATE statement
+     *
+     * NOTE: Statement is closed after execution
+     */
+    public int executeUpdateAndClose(PreparedStatement stmt) throws SQLException {
+        int rows = 0;
+        try {
+            rows = stmt.executeUpdate();
+            if (rows == 0) {
+                log.info("Problem: 0 rows affected by insert/update/delete statement.");
+            }
+        } finally {
+            stmt.close();
+        }
+        return rows;
+    }
+
+    public int countRow(String tableName, String columnName) throws SQLException {
+        String query = new String("SELECT COUNT(" + columnName + ") FROM " + tableName);
+        int count = -1;
+        Connection conn = null;
+        PreparedStatement stmt = null;
+        try {
+            conn = connectionPool.getConnection();
+            stmt = conn.prepareStatement(query);
+            ResultSet rs = stmt.executeQuery();
+            rs.next();
+            count = rs.getInt(1);
+            commit(conn);
+        } catch (SQLException sql) {
+            rollback(conn);
+            throw sql;
+        } finally {
+            try {
+                if (stmt != null && !stmt.isClosed()) {
+                    stmt.close();
+                }
+            } finally {
+                closeConnection(conn);
+            }
+        }
+        return count;
+    }
+
+    public void quietlyClose(Connection conn, Statement... stmts) {
+        if (stmts != null) {
+            for (Statement stmt : stmts) {
+                try {
+                    if (stmt != null && !stmt.isClosed()) {
+                        stmt.close();
+                    }
+                } catch (SQLException sql) {
+                    log.error(sql.getMessage(), sql);
+                }
+            }
+        }
+        closeConnection(conn);
+    }
+
+    public void closeConnection(Connection conn) {
+        if (conn != null) {
+            connectionPool.free(conn);
+        }
+    }
+
+    public void closeAllConnections() {
+        if (connectionPool != null)
+            connectionPool.dispose();
+    }
+
+    public void shutdown() {
+        connectionPool.shutdown();
+    }
+}

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/OrchestratorInitUtil.java
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/OrchestratorInitUtil.java b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/OrchestratorInitUtil.java
new file mode 100644
index 0000000..5add438
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/OrchestratorInitUtil.java
@@ -0,0 +1,25 @@
+/*
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ */
+
+package org.apache.airavata.api.server.util;
+
+public class OrchestratorInitUtil {
+}

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/RegistryInitUtil.java
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/RegistryInitUtil.java b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/RegistryInitUtil.java
new file mode 100644
index 0000000..dfcc2e2
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/java/org/apache/airavata/api/server/util/RegistryInitUtil.java
@@ -0,0 +1,173 @@
+/*
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements.  See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership.  The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License.  You may obtain a copy of the License at
+*
+*   http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied.  See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+*/
+
+package org.apache.airavata.api.server.util;
+
+import java.io.IOException;
+import java.net.InetAddress;
+import java.net.URI;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import org.apache.airavata.common.exception.ApplicationSettingsException;
+import org.apache.airavata.common.utils.ServerSettings;
+import org.apache.airavata.persistance.registry.jpa.ResourceType;
+import org.apache.airavata.persistance.registry.jpa.resources.GatewayResource;
+import org.apache.airavata.persistance.registry.jpa.resources.ProjectResource;
+import org.apache.airavata.persistance.registry.jpa.resources.UserResource;
+import org.apache.airavata.persistance.registry.jpa.resources.WorkerResource;
+import org.apache.derby.drda.NetworkServerControl;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+public class RegistryInitUtil {
+    public static final String CONFIGURATION_TABLE = "Configuration";
+    private static final Logger logger = LoggerFactory.getLogger(RegistryInitUtil.class);
+    public static final String REGISTRY_JDBC_DRIVER = "registry.jdbc.driver";
+    public static final String REGISTRY_JDBC_URL = "registry.jdbc.url";
+    public static final String REGISTRY_JDBC_USER = "registry.jdbc.user";
+    public static final String REGISTRY_JDBC_PASSWORD = "registry.jdbc.password";
+    public static final String START_DERBY_ENABLE = "start.derby.server.mode";
+    public static final String DERBY_SERVER_MODE_SYS_PROPERTY = "derby.drda.startNetworkServer";
+    public static final String DEFAULT_PROJECT_NAME = "default";
+    private static NetworkServerControl server;
+    private static JdbcStorage db;
+    private static String jdbcURl;
+    private static String jdbcDriver;
+    private static String jdbcUser;
+    private static String jdbcPassword;
+
+
+    public static void initializeDB() {
+        System.setProperty("registry.initialize.state", "0");
+        try{
+            jdbcDriver = ServerSettings.getSetting(REGISTRY_JDBC_DRIVER);
+            jdbcURl = ServerSettings.getSetting(REGISTRY_JDBC_URL);
+            jdbcUser = ServerSettings.getSetting(REGISTRY_JDBC_USER);
+            jdbcPassword = ServerSettings.getSetting(REGISTRY_JDBC_PASSWORD);
+            jdbcURl = jdbcURl + "?" + "user=" + jdbcUser + "&" + "password=" + jdbcPassword;
+        } catch (ApplicationSettingsException e) {
+            logger.error("Unable to read airavata server properties", e.getMessage());
+        }
+
+        if (getDBType(jdbcURl).equals("derby") && isDerbyStartEnabled()) {
+            startDerbyInServerMode();
+        }
+        db = new JdbcStorage(10, 50, jdbcURl, jdbcDriver, true);
+
+        Connection conn = null;
+        try {
+            conn = db.connect();
+            if (!DatabaseCreator.isDatabaseStructureCreated(CONFIGURATION_TABLE, conn)) {
+                DatabaseCreator.createRegistryDatabase(conn);
+                logger.info("New Database created for Registry");
+            } else {
+                logger.info("Database already created for Registry!");
+            }
+            try{
+                GatewayResource gatewayResource = new GatewayResource();
+                gatewayResource.setGatewayName(ServerSettings.getSystemUserGateway());
+                gatewayResource.setOwner(ServerSettings.getSystemUser());
+                gatewayResource.save();
+                UserResource userResource = (UserResource) gatewayResource.create(ResourceType.USER);
+                userResource.setUserName(ServerSettings.getSystemUser());
+                userResource.setPassword(ServerSettings.getSystemUserPassword());
+                userResource.save();
+                WorkerResource workerResource = (WorkerResource) gatewayResource.create(ResourceType.GATEWAY_WORKER);
+                workerResource.setUser(userResource.getUserName());
+                workerResource.save();
+                ProjectResource projectResource = workerResource.createProject(DEFAULT_PROJECT_NAME);
+                projectResource.setGateway(gatewayResource);
+                projectResource.save();
+            } catch (ApplicationSettingsException e) {
+                logger.error("Unable to read airavata-server properties...", e.getMessage());
+            }
+        } catch (Exception e) {
+            logger.error(e.getMessage(), e);
+            throw new RuntimeException("Database failure");
+        } finally {
+            db.closeConnection(conn);
+            try {
+                if(conn != null){
+                    if (!conn.getAutoCommit()) {
+                        conn.commit();
+                    }
+                    conn.close();
+                }
+            } catch (SQLException e) {
+                logger.error("Error while closing database connection...", e.getMessage(), e);
+            }
+        }
+        System.setProperty("registry.initialize.state", "1");
+    }
+
+    public static String getDBType(String jdbcUrl){
+        try{
+            String cleanURI = jdbcUrl.substring(5);
+            URI uri = URI.create(cleanURI);
+            return uri.getScheme();
+        } catch (Exception e) {
+            logger.error(e.getMessage(), e);
+            return null;
+        }
+    }
+
+    public static boolean isDerbyStartEnabled(){
+        try {
+            String s = ServerSettings.getSetting(START_DERBY_ENABLE);
+            if("true".equals(s)){
+                return true;
+            }
+        }  catch (ApplicationSettingsException e) {
+            logger.error("Unable to read airavata server properties", e.getMessage(), e);
+            return false;
+        }
+        return false;
+    }
+
+    public static void startDerbyInServerMode() {
+        try {
+            System.setProperty(DERBY_SERVER_MODE_SYS_PROPERTY, "true");
+            server = new NetworkServerControl(InetAddress.getByName("0.0.0.0"),
+                    getPort(jdbcURl),
+                    jdbcUser, jdbcPassword);
+            java.io.PrintWriter consoleWriter = new java.io.PrintWriter(System.out, true);
+            server.start(consoleWriter);
+        } catch (IOException e) {
+            logger.error("Unable to start Apache derby in the server mode! Check whether " +
+                    "specified port is available");
+        } catch (Exception e) {
+            logger.error("Unable to start Apache derby in the server mode! Check whether " +
+                    "specified port is available");
+        }
+    }
+
+    public static int getPort(String jdbcURL){
+        try{
+            String cleanURI = jdbcURL.substring(5);
+            URI uri = URI.create(cleanURI);
+            return uri.getPort();
+        } catch (Exception e) {
+            logger.error(e.getMessage(), e);
+            return -1;
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/resources/airavata-registry-derby.sql
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/resources/airavata-registry-derby.sql b/airavata-api/airavata-api-server/src/main/resources/airavata-registry-derby.sql
new file mode 100644
index 0000000..04e25c0
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/resources/airavata-registry-derby.sql
@@ -0,0 +1,325 @@
+/*
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ */
+CREATE TABLE GATEWAY
+(
+        GATEWAY_NAME VARCHAR(255),
+	      OWNER VARCHAR(255),
+        PRIMARY KEY (GATEWAY_NAME)
+);
+
+CREATE TABLE CONFIGURATION
+(
+        CONFIG_KEY VARCHAR(255),
+        CONFIG_VAL VARCHAR(255),
+        EXPIRE_DATE TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+        CATEGORY_ID VARCHAR (255),
+        PRIMARY KEY(CONFIG_KEY, CONFIG_VAL, CATEGORY_ID)
+);
+
+INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VAL, EXPIRE_DATE, CATEGORY_ID) VALUES('registry.version', '0.12', CURRENT_TIMESTAMP ,'SYSTEM');
+
+CREATE TABLE USERS
+(
+        USER_NAME VARCHAR(255),
+        PASSWORD VARCHAR(255),
+        PRIMARY KEY(USER_NAME)
+);
+
+CREATE TABLE GATEWAY_WORKER
+(
+        GATEWAY_NAME VARCHAR(255),
+        USER_NAME VARCHAR(255),
+        PRIMARY KEY (GATEWAY_NAME, USER_NAME),
+        FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+        FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PROJECT
+(
+         GATEWAY_NAME VARCHAR(255),
+         USER_NAME VARCHAR(255),
+         PROJECT_NAME VARCHAR(255),
+         PRIMARY KEY (PROJECT_NAME),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PUBLISHED_WORKFLOW
+(
+         GATEWAY_NAME VARCHAR(255),
+         CREATED_USER VARCHAR(255),
+         PUBLISH_WORKFLOW_NAME VARCHAR(255),
+         VERSION VARCHAR(255),
+         PUBLISHED_DATE TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+         PATH VARCHAR (255),
+         WORKFLOW_CONTENT BLOB,
+         PRIMARY KEY(GATEWAY_NAME, PUBLISH_WORKFLOW_NAME),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (CREATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE USER_WORKFLOW
+(
+         GATEWAY_NAME VARCHAR(255),
+         OWNER VARCHAR(255),
+         TEMPLATE_NAME VARCHAR(255),
+         LAST_UPDATED_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP,
+         PATH VARCHAR (255),
+         WORKFLOW_GRAPH BLOB,
+         PRIMARY KEY(GATEWAY_NAME, OWNER, TEMPLATE_NAME),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (OWNER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE HOST_DESCRIPTOR
+(
+         GATEWAY_NAME VARCHAR(255),
+         UPDATED_USER VARCHAR(255),
+         HOST_DESCRIPTOR_ID VARCHAR(255),
+         HOST_DESCRIPTOR_XML BLOB,
+         PRIMARY KEY(GATEWAY_NAME, HOST_DESCRIPTOR_ID),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE SERVICE_DESCRIPTOR
+(
+         GATEWAY_NAME VARCHAR(255),
+         UPDATED_USER VARCHAR(255),
+         SERVICE_DESCRIPTOR_ID VARCHAR(255),
+         SERVICE_DESCRIPTOR_XML BLOB,
+         PRIMARY KEY(GATEWAY_NAME,SERVICE_DESCRIPTOR_ID),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE APPLICATION_DESCRIPTOR
+(
+         GATEWAY_NAME VARCHAR(255),
+         UPDATED_USER VARCHAR(255),
+         APPLICATION_DESCRIPTOR_ID VARCHAR(255),
+         HOST_DESCRIPTOR_ID VARCHAR(255),
+         SERVICE_DESCRIPTOR_ID VARCHAR(255),
+         APPLICATION_DESCRIPTOR_XML BLOB,
+         PRIMARY KEY(GATEWAY_NAME,APPLICATION_DESCRIPTOR_ID),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_METADATA
+(
+          EXPERIMENT_ID VARCHAR(255) NOT NULL,
+          EXPERIMENT_NAME VARCHAR(255) NOT NULL,
+          DESCRIPTION VARCHAR(255),
+          SUBMITTED_DATE TIMESTAMP DEFAULT CURRENT TIMESTAMP,
+          EXECUTION_USER VARCHAR(255),
+          GATEWAY_NAME VARCHAR(255),
+          PROJECT_NAME VARCHAR(255),
+          SHARE_EXPERIMENT SMALLINT,
+          PRIMARY KEY (EXPERIMENT_ID),
+          FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+          FOREIGN KEY (PROJECT_NAME) REFERENCES PROJECT(PROJECT_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_SUMMARY
+(
+          EXPERIMENT_ID VARCHAR(255) NOT NULL,
+          STATUS VARCHAR(255),
+          LAST_UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+          PRIMARY KEY(EXPERIMENT_ID),
+          FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_CONFIGURATION_DATA
+(
+          EXPERIMENT_ID VARCHAR(255) NOT NULL,
+          APPLICATION_ID VARCHAR(255),
+          APPLICATION_VERSION VARCHAR(255),
+          WORKFLOW_TEMPLATE_ID VARCHAR(255),
+          WORKFLOW_TEMPLATE_VERSION VARCHAR(255),
+          RESOURCE_HOST_ID VARCHAR (255),
+          TOTAL_CPU_COUNT INTEGER,
+          NODE_COUNT INTEGER,
+          NUMBER_OF_THREADS INTEGER,
+          QUEUE_NAME VARCHAR (255),
+          WALLTIME_LIMIT INTEGER,
+          JOB_START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+          TOTAL_PHYSICAL_MEMORY INTEGER,
+          COMPUTATIONAL_PROJECT_ACCOUNT VARCHAR(255),
+          AIRAVATA_AUTO_SCHEDULE SMALLINT,
+          OVERRIDE_MANUAL_SCHEDULE_PARAMS SMALLINT,
+          WORKING_DIR_PARENT VARCHAR(255),
+          UNIQUE_WORKING_DIR VARCHAR(255),
+          STAGE_INPUT_FILES_TO_WORKING_DIR SMALLINT,
+          OUTPUT_DATA_DIR VARCHAR(255),
+          DATA_REG_URL VARCHAR (255),
+          PERSIST_OUTPUT_DATA SMALLINT,
+          CLEAN_AFTER_JOB SMALLINT,
+          START_EXECUTION_AT VARCHAR(255),
+          EXECUTE_BEFORE VARCHAR(255),
+          NUMBER_OF_RETRIES INTEGER,
+          EXPERIMENT_CONFIG_DATA BLOB,
+          PRIMARY KEY (EXPERIMENT_ID),
+          FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_INPUT
+(
+        EXPERIMENT_ID VARCHAR(255) NOT NULL,
+        EX_KEY VARCHAR (255) NOT NULL,
+        VALUE VARCHAR (255),
+        PRIMARY KEY (EXPERIMENT_ID, EX_KEY),
+        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_OUTPUT
+(
+        EXPERIMENT_ID VARCHAR(255) NOT NULL,
+        EX_KEY VARCHAR (255) NOT NULL,
+        VALUE VARCHAR (255),
+        PRIMARY KEY (EXPERIMENT_ID, EX_KEY),
+        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE WORKFLOW_DATA
+(
+       EXPERIMENT_ID VARCHAR(255),
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       TEMPLATE_NAME VARCHAR(255),
+       STATUS VARCHAR(255),
+       START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       LAST_UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+       PRIMARY KEY(WORKFLOW_INSTANCE_ID),
+       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE NODE_DATA
+(
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       NODE_TYPE VARCHAR(255),
+       INPUTS BLOB,
+       OUTPUTS BLOB,
+       STATUS VARCHAR(255),
+       START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       LAST_UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+       EXECUTION_INDEX INTEGER NOT NULL,
+       PRIMARY KEY(WORKFLOW_INSTANCE_ID, NODE_ID, EXECUTION_INDEX),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE
+);
+
+-- need to remove this in future
+CREATE TABLE GRAM_DATA
+(
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       RSL BLOB,
+       INVOKED_HOST VARCHAR(255),
+       LOCAL_JOB_ID VARCHAR(255),
+       PRIMARY KEY(WORKFLOW_INSTANCE_ID, NODE_ID),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE GFAC_JOB_DATA
+(
+       EXPERIMENT_ID VARCHAR(255),
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       APPLICATION_DESC_ID VARCHAR(255),
+       HOST_DESC_ID VARCHAR(255),
+       SERVICE_DESC_ID VARCHAR(255),
+       JOB_DATA CLOB,
+       LOCAL_JOB_ID VARCHAR(255) NOT NULL,
+       SUBMITTED_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       STATUS VARCHAR(255),
+       METADATA CLOB,
+       PRIMARY KEY(LOCAL_JOB_ID),
+       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID)
+);
+
+CREATE TABLE GFAC_JOB_STATUS
+(
+       LOCAL_JOB_ID VARCHAR(255) NOT NULL,
+       STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       STATUS VARCHAR(255),
+       FOREIGN KEY (LOCAL_JOB_ID) REFERENCES GFAC_JOB_DATA(LOCAL_JOB_ID)
+);
+
+
+CREATE TABLE COMMUNITY_USER
+(
+        GATEWAY_NAME VARCHAR(256) NOT NULL,
+        COMMUNITY_USER_NAME VARCHAR(256) NOT NULL,
+        TOKEN_ID VARCHAR(256) NOT NULL,
+        COMMUNITY_USER_EMAIL VARCHAR(256) NOT NULL,
+        PRIMARY KEY (GATEWAY_NAME, COMMUNITY_USER_NAME, TOKEN_ID)
+);
+
+
+CREATE TABLE CREDENTIALS
+(
+        GATEWAY_ID VARCHAR(256) NOT NULL,
+        TOKEN_ID VARCHAR(256) NOT NULL,
+        CREDENTIAL BLOB NOT NULL,
+        PORTAL_USER_ID VARCHAR(256) NOT NULL,
+        TIME_PERSISTED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+        PRIMARY KEY (GATEWAY_ID, TOKEN_ID)
+);
+
+CREATE TABLE EXECUTION_ERROR
+(
+       ERROR_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
+       EXPERIMENT_ID VARCHAR(255),
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       GFAC_JOB_ID VARCHAR(255),
+       SOURCE_TYPE VARCHAR(255),
+       ERROR_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+       ERROR_MSG CLOB,
+       ERROR_DESC CLOB,
+       ERROR_CODE VARCHAR(255),
+       ERROR_REPORTER VARCHAR(255),
+       ERROR_LOCATION VARCHAR(255),
+       ACTION_TAKEN VARCHAR(255),
+       ERROR_REFERENCE INTEGER,
+       PRIMARY KEY(ERROR_ID),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE,
+       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE ORCHESTRATOR
+(
+        EXPERIMENT_ID VARCHAR(255) NOT NULL,
+        USERNAME VARCHAR(255),
+        STATUS VARCHAR(255),
+        STATE VARCHAR(255),
+        GFAC_EPR VARCHAR(255),
+        APPLICATION_NAME VARCHAR(255),
+        JOBREQUEST CLOB,
+        SUBMITTED_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+        STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+        PRIMARY KEY (EXPERIMENT_ID),
+        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/resources/airavata-registry-mysql.sql
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/resources/airavata-registry-mysql.sql b/airavata-api/airavata-api-server/src/main/resources/airavata-registry-mysql.sql
new file mode 100644
index 0000000..ac38407
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/resources/airavata-registry-mysql.sql
@@ -0,0 +1,327 @@
+/*
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ */
+CREATE TABLE GATEWAY
+(
+        GATEWAY_NAME VARCHAR(255),
+	      OWNER VARCHAR(255),
+        PRIMARY KEY (GATEWAY_NAME)
+);
+
+
+CREATE TABLE CONFIGURATION
+(
+        CONFIG_KEY VARCHAR(255),
+        CONFIG_VAL VARCHAR(255),
+        EXPIRE_DATE TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+        CATEGORY_ID VARCHAR (255),
+        PRIMARY KEY(CONFIG_KEY, CONFIG_VAL, CATEGORY_ID)
+);
+
+INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VAL, EXPIRE_DATE, CATEGORY_ID) VALUES('registry.version', '0.12', CURRENT_TIMESTAMP ,'SYSTEM');
+
+CREATE TABLE USERS
+(
+        USER_NAME VARCHAR(255),
+        PASSWORD VARCHAR(255),
+        PRIMARY KEY(USER_NAME)
+);
+
+CREATE TABLE GATEWAY_WORKER
+(
+        GATEWAY_NAME VARCHAR(255),
+        USER_NAME VARCHAR(255),
+        PRIMARY KEY (GATEWAY_NAME, USER_NAME),
+        FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+        FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PROJECT
+(
+         GATEWAY_NAME VARCHAR(255),
+         USER_NAME VARCHAR(255),
+         PROJECT_NAME VARCHAR(255),
+         PRIMARY KEY (PROJECT_NAME),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PUBLISHED_WORKFLOW
+(
+         GATEWAY_NAME VARCHAR(255),
+         CREATED_USER VARCHAR(255),
+         PUBLISH_WORKFLOW_NAME VARCHAR(255),
+         VERSION VARCHAR(255),
+         PUBLISHED_DATE TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+         PATH VARCHAR (255),
+         WORKFLOW_CONTENT BLOB,
+         PRIMARY KEY(GATEWAY_NAME, PUBLISH_WORKFLOW_NAME),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (CREATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE USER_WORKFLOW
+(
+         GATEWAY_NAME VARCHAR(255),
+         OWNER VARCHAR(255),
+         TEMPLATE_NAME VARCHAR(255),
+         LAST_UPDATED_TIME TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
+         PATH VARCHAR (255),
+         WORKFLOW_GRAPH BLOB,
+         PRIMARY KEY(GATEWAY_NAME, OWNER, TEMPLATE_NAME),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (OWNER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE HOST_DESCRIPTOR
+(
+         GATEWAY_NAME VARCHAR(255),
+         UPDATED_USER VARCHAR(255),
+         HOST_DESCRIPTOR_ID VARCHAR(255),
+         HOST_DESCRIPTOR_XML BLOB,
+         PRIMARY KEY(GATEWAY_NAME, HOST_DESCRIPTOR_ID),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE SERVICE_DESCRIPTOR
+(
+         GATEWAY_NAME VARCHAR(255),
+         UPDATED_USER VARCHAR(255),
+         SERVICE_DESCRIPTOR_ID VARCHAR(255),
+         SERVICE_DESCRIPTOR_XML BLOB,
+         PRIMARY KEY(GATEWAY_NAME,SERVICE_DESCRIPTOR_ID),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE APPLICATION_DESCRIPTOR
+(
+         GATEWAY_NAME VARCHAR(255),
+         UPDATED_USER VARCHAR(255),
+         APPLICATION_DESCRIPTOR_ID VARCHAR(255),
+         HOST_DESCRIPTOR_ID VARCHAR(255),
+         SERVICE_DESCRIPTOR_ID VARCHAR(255),
+         APPLICATION_DESCRIPTOR_XML BLOB,
+         PRIMARY KEY(GATEWAY_NAME,APPLICATION_DESCRIPTOR_ID),
+         FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+         FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_METADATA
+(
+          EXPERIMENT_ID VARCHAR(255) NOT NULL,
+          EXPERIMENT_NAME VARCHAR(255) NOT NULL,
+          DESCRIPTION VARCHAR(255),
+          SUBMITTED_DATE TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
+          EXECUTION_USER VARCHAR(255),
+          GATEWAY_NAME VARCHAR(255),
+          SHARE_EXPERIMENT SMALLINT,
+          PROJECT_NAME VARCHAR(255),
+          PRIMARY KEY (EXPERIMENT_ID),
+          FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE,
+          FOREIGN KEY (EXECUTION_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE,
+          FOREIGN KEY (PROJECT_NAME) REFERENCES PROJECT(PROJECT_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_SUMMARY
+(
+          EXPERIMENT_ID VARCHAR(255) NOT NULL,
+          STATUS VARCHAR(255),
+          LAST_UPDATED_TIME TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
+          PRIMARY KEY(EXPERIMENT_ID),
+          FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_CONFIGURATION_DATA
+(
+          EXPERIMENT_ID VARCHAR(255) NOT NULL,
+          APPLICATION_ID VARCHAR(255),
+          APPLICATION_VERSION VARCHAR(255),
+          WORKFLOW_TEMPLATE_ID VARCHAR(255),
+          WORKFLOW_TEMPLATE_VERSION VARCHAR(255),
+          RESOURCE_HOST_ID VARCHAR (255),
+          TOTAL_CPU_COUNT INT(11),
+          NODE_COUNT INT(11),
+          NUMBER_OF_THREADS INT(11),
+          QUEUE_NAME VARCHAR (255),
+          WALLTIME_LIMIT INT(11),
+          JOB_START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+          TOTAL_PHYSICAL_MEMORY INT(11),
+          COMPUTATIONAL_PROJECT_ACCOUNT VARCHAR(255),
+          AIRAVATA_AUTO_SCHEDULE SMALLINT,
+          OVERRIDE_MANUAL_SCHEDULE_PARAMS SMALLINT,
+          WORKING_DIR_PARENT VARCHAR(255),
+          UNIQUE_WORKING_DIR VARCHAR(255),
+          STAGE_INPUT_FILES_TO_WORKING_DIR SMALLINT,
+          OUTPUT_DATA_DIR VARCHAR(255),
+          DATA_REG_URL VARCHAR (255),
+          PERSIST_OUTPUT_DATA SMALLINT,
+          CLEAN_AFTER_JOB SMALLINT,
+          START_EXECUTION_AT VARCHAR(255),
+          EXECUTE_BEFORE VARCHAR(255),
+          NUMBER_OF_RETRIES INTEGER,
+          EXPERIMENT_CONFIG_DATA BLOB,
+          PRIMARY KEY (EXPERIMENT_ID),
+          FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_INPUT
+(
+        EXPERIMENT_ID VARCHAR(255) NOT NULL,
+        EX_KEY VARCHAR(255) NOT NULL,
+        VALUE VARCHAR(255),
+        PRIMARY KEY (EXPERIMENT_ID, EX_KEY),
+        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_OUTPUT
+(
+        EXPERIMENT_ID VARCHAR(255) NOT NULL,
+        EX_KEY VARCHAR (255) NOT NULL,
+        VALUE VARCHAR (255),
+        PRIMARY KEY (EXPERIMENT_ID, EX_KEY),
+        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE WORKFLOW_DATA
+(
+       EXPERIMENT_ID VARCHAR(255),
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       TEMPLATE_NAME VARCHAR(255),
+       STATUS VARCHAR(255),
+       START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       LAST_UPDATE_TIME TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
+       PRIMARY KEY(WORKFLOW_INSTANCE_ID),
+       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE NODE_DATA
+(
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       NODE_TYPE VARCHAR(255),
+       INPUTS BLOB,
+       OUTPUTS BLOB,
+       STATUS VARCHAR(255),
+       START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       LAST_UPDATE_TIME TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
+       EXECUTION_INDEX INT(11) NOT NULL,
+       PRIMARY KEY(WORKFLOW_INSTANCE_ID, NODE_ID, EXECUTION_INDEX),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE
+);
+
+-- need to remove this in future
+CREATE TABLE GRAM_DATA
+(
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       RSL BLOB,
+       INVOKED_HOST VARCHAR(255),
+       LOCAL_JOB_ID VARCHAR(255),
+       PRIMARY KEY(WORKFLOW_INSTANCE_ID, NODE_ID),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE GFAC_JOB_DATA
+(
+       EXPERIMENT_ID VARCHAR(255),
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       APPLICATION_DESC_ID VARCHAR(255),
+       HOST_DESC_ID VARCHAR(255),
+       SERVICE_DESC_ID VARCHAR(255),
+       JOB_DATA LONGTEXT,
+       LOCAL_JOB_ID VARCHAR(255) NOT NULL,
+       SUBMITTED_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       STATUS VARCHAR(255),
+       METADATA LONGTEXT,
+       PRIMARY KEY(LOCAL_JOB_ID),
+       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID)
+);
+
+CREATE TABLE GFAC_JOB_STATUS
+(
+       LOCAL_JOB_ID VARCHAR(255) NOT NULL,
+       STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+       STATUS VARCHAR(255),
+       FOREIGN KEY (LOCAL_JOB_ID) REFERENCES GFAC_JOB_DATA(LOCAL_JOB_ID)
+);
+
+
+CREATE TABLE COMMUNITY_USER
+(
+        GATEWAY_NAME VARCHAR(256) NOT NULL,
+        COMMUNITY_USER_NAME VARCHAR(256) NOT NULL,
+        TOKEN_ID VARCHAR(256) NOT NULL,
+        COMMUNITY_USER_EMAIL VARCHAR(256) NOT NULL,
+        PRIMARY KEY (GATEWAY_NAME, COMMUNITY_USER_NAME, TOKEN_ID)
+);
+
+
+CREATE TABLE CREDENTIALS
+(
+        GATEWAY_ID VARCHAR(256) NOT NULL,
+        TOKEN_ID VARCHAR(256) NOT NULL,
+        CREDENTIAL BLOB NOT NULL,
+        PORTAL_USER_ID VARCHAR(256) NOT NULL,
+        TIME_PERSISTED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+        PRIMARY KEY (GATEWAY_ID, TOKEN_ID)
+);
+
+CREATE TABLE EXECUTION_ERROR
+(
+       ERROR_ID INT(11) NOT NULL AUTO_INCREMENT,
+       EXPERIMENT_ID VARCHAR(255),
+       WORKFLOW_INSTANCE_ID VARCHAR(255),
+       NODE_ID VARCHAR(255),
+       GFAC_JOB_ID VARCHAR(255),
+       SOURCE_TYPE VARCHAR(255),
+       ERROR_DATE TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
+       ERROR_MSG LONGTEXT,
+       ERROR_DESC LONGTEXT,
+       ERROR_CODE VARCHAR(255),
+       ERROR_REPORTER VARCHAR(255),
+       ERROR_LOCATION VARCHAR(255),
+       ACTION_TAKEN VARCHAR(255),
+       ERROR_REFERENCE INT,
+       PRIMARY KEY(ERROR_ID),
+       FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE,
+       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE ORCHESTRATOR
+(
+        EXPERIMENT_ID VARCHAR(255) NOT NULL,
+        USERNAME VARCHAR(255),
+        STATUS VARCHAR(255),
+        STATE VARCHAR(255),
+        GFAC_EPR VARCHAR(255),
+        APPLICATION_NAME VARCHAR(255),
+        JOBREQUEST LONGTEXT,
+        SUBMITTED_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+        STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00',
+        PRIMARY KEY (EXPERIMENT_ID),
+        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/airavata-api-server/src/main/resources/airavata-server.properties
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/resources/airavata-server.properties b/airavata-api/airavata-api-server/src/main/resources/airavata-server.properties
index 08cc006..2dbc50f 100644
--- a/airavata-api/airavata-api-server/src/main/resources/airavata-server.properties
+++ b/airavata-api/airavata-api-server/src/main/resources/airavata-server.properties
@@ -25,4 +25,32 @@
 #
 ###########################################################################
 
-api.server.port = 8930
\ No newline at end of file
+api.server.port = 8930
+
+#This property will enable https and stops http, during the url registration, https urls will be stored and
+# http urls will not get registered, because currently airavata supports only one url for each service
+#enable.https=false
+#system properties used by services to register service URLs
+system.gateway=default
+system.user=admin
+system.password=admin
+
+###########################################################################
+# Airavata Registry Configuration
+###########################################################################
+#for derby [AiravataJPARegistry]
+registry.jdbc.driver=org.apache.derby.jdbc.ClientDriver
+registry.jdbc.url=jdbc:derby://localhost:1527/persistent_data;create=true;user=airavata;password=airavata
+# MySql database configuration
+#registry.jdbc.driver=com.mysql.jdbc.Driver
+#registry.jdbc.url=jdbc:mysql://localhost:3306/persistent_data
+registry.jdbc.user=airavata
+registry.jdbc.password=airavata
+start.derby.server.mode=true
+validationQuery=SELECT 1 from Configuration
+jpa.connection.properties=MaxActive=10,MaxIdle=5,MinIdle=2,MaxWait=60000,testWhileIdle=true,testOnBorrow=true
+# Properties to setup registry service
+default.registry.user=admin
+default.registry.password=admin
+default.registry.password.hash.method=SHA
+default.registry.gateway=default

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/airavata-api/thrift-interface-descriptions/experimentModel.thrift
----------------------------------------------------------------------
diff --git a/airavata-api/thrift-interface-descriptions/experimentModel.thrift b/airavata-api/thrift-interface-descriptions/experimentModel.thrift
index c1878ff..59c75a0 100644
--- a/airavata-api/thrift-interface-descriptions/experimentModel.thrift
+++ b/airavata-api/thrift-interface-descriptions/experimentModel.thrift
@@ -140,3 +140,45 @@ struct ConfigurationData {
   9: optional AdvancedOutputDataHandling advanceOutputDataHandling,
   10: optional QualityOfServiceParams qosParams
 }
+
+/**
+* A structure holding the node data.
+*
+*
+*/
+struct NodeData {
+    1: required string nodeName,
+    2: required string nodeInstanceId = "DO_NOT_SET",
+    3: required string executionIndex,
+    4: optional string nodeType,
+    5: optional string nodeInput,
+    6: optional string nodeOutput,
+    7: optional string nodeStatus,
+    8: optional int32 startTime,
+    9: optional int32 lastUpdateTime
+}
+
+/**
+* A structure to hold experiment output
+*
+*/
+struct ExperimentOutput {
+    1: required string outputKey,
+    2: optional string outputType,
+    3: optional string outputkeyValue
+}
+
+/**
+* A structure to hold generated data
+*
+*/
+struct GeneratedData {
+    1: required string experimentId,
+    2: required string workflowTemplateId,
+    3: optional string workflowTemplateName,
+    4: optional string status,
+    5: optional int32 startTime,
+    6: optional int32 lastUpdateTime,
+    7: optional list<ExperimentOutput> experimentOutputs,
+    8: optional list<NodeData> nodes
+}

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/modules/registry/airavata-jpa-registry/src/main/java/org/apache/airavata/persistance/registry/jpa/impl/RegistryImpl.java
----------------------------------------------------------------------
diff --git a/modules/registry/airavata-jpa-registry/src/main/java/org/apache/airavata/persistance/registry/jpa/impl/RegistryImpl.java b/modules/registry/airavata-jpa-registry/src/main/java/org/apache/airavata/persistance/registry/jpa/impl/RegistryImpl.java
index bc076b9..4657283 100644
--- a/modules/registry/airavata-jpa-registry/src/main/java/org/apache/airavata/persistance/registry/jpa/impl/RegistryImpl.java
+++ b/modules/registry/airavata-jpa-registry/src/main/java/org/apache/airavata/persistance/registry/jpa/impl/RegistryImpl.java
@@ -30,6 +30,7 @@ import org.apache.airavata.registry.cpi.Registry;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
+import javax.sql.rowset.serial.SerialStruct;
 import java.util.ArrayList;
 import java.util.List;
 
@@ -69,7 +70,7 @@ public class RegistryImpl implements Registry {
      *                            null
      */
     @Override
-    public void add(ChildDataType dataType, Object newObjectToAdd, Object dependentIdentifier) {
+    public void add(ChildDataType dataType, Object newObjectToAdd, String dependentIdentifier) {
         switch (dataType){
             case EXPERIMENT_CONFIGURATION_DATA:
                 experimentRegistry.add((ConfigurationData)newObjectToAdd, (String)dependentIdentifier);
@@ -101,13 +102,13 @@ public class RegistryImpl implements Registry {
      *                       other fields that need to be updated.
      */
     @Override
-    public void update(DataType dataType, Object newObjectToUpdate, Object identifier) {
+    public void update(DataType dataType, Object newObjectToUpdate, String identifier) {
         switch (dataType){
             case EXPERIMENT_BASIC_DATA:
-                experimentRegistry.update(newObjectToUpdate, (String)identifier);
+                experimentRegistry.update(newObjectToUpdate, identifier);
                 break;
             case EXPERIMENT_CONFIGURATION_DATA:
-                experimentRegistry.update(newObjectToUpdate, (String)identifier);
+                experimentRegistry.update(newObjectToUpdate, identifier);
                 break;
             default:
                 logger.error("Unsupported data type...", new UnsupportedOperationException());
@@ -129,13 +130,13 @@ public class RegistryImpl implements Registry {
      *              updated by given value
      */
     @Override
-    public void update(DataType dataType, Object identifier, String fieldName, Object value) {
+    public void update(DataType dataType, String identifier, String fieldName, Object value) {
         switch (dataType){
             case EXPERIMENT_BASIC_DATA:
-                experimentRegistry.updateExpBasicMetadataField((String) identifier, fieldName, value);
+                experimentRegistry.updateExpBasicMetadataField(identifier, fieldName, value);
                 break;
             case EXPERIMENT_CONFIGURATION_DATA:
-                experimentRegistry.updateExpConfigDataField((String) identifier, fieldName, value);
+                experimentRegistry.updateExpConfigDataField(identifier, fieldName, value);
                 break;
             default:
                 logger.error("Unsupported data type...", new UnsupportedOperationException());
@@ -153,13 +154,13 @@ public class RegistryImpl implements Registry {
      * @return object according to the given identifier.
      */
     @Override
-    public Object get(DataType dataType, Object identifier) {
+    public Object get(DataType dataType, String identifier) {
 
         switch (dataType){
             case EXPERIMENT_BASIC_DATA:
-                return experimentRegistry.getBasicMetaData((String)identifier, null);
+                return experimentRegistry.getBasicMetaData(identifier, null);
             case EXPERIMENT_CONFIGURATION_DATA:
-                return experimentRegistry.getConfigData((String)identifier, null);
+                return experimentRegistry.getConfigData(identifier, null);
             default:
                 logger.error("Unsupported data type...", new UnsupportedOperationException());
                 throw new UnsupportedOperationException();
@@ -209,12 +210,12 @@ public class RegistryImpl implements Registry {
      *         given
      */
     @Override
-    public Object getValue(DataType dataType, Object identifier, String field) {
+    public Object getValue(DataType dataType, String identifier, String field) {
         switch (dataType){
             case EXPERIMENT_BASIC_DATA:
-                return experimentRegistry.getBasicMetaData((String) identifier, field);
+                return experimentRegistry.getBasicMetaData(identifier, field);
             case EXPERIMENT_CONFIGURATION_DATA:
-                return experimentRegistry.getConfigData((String) identifier, field);
+                return experimentRegistry.getConfigData(identifier, field);
             default:
                 logger.error("Unsupported data type...", new UnsupportedOperationException());
                 throw new UnsupportedOperationException();
@@ -253,13 +254,13 @@ public class RegistryImpl implements Registry {
      *                   identifier will be generated experimentID
      */
     @Override
-    public void remove(DataType dataType, Object identifier) {
+    public void remove(DataType dataType, String identifier) {
         switch (dataType){
             case EXPERIMENT_BASIC_DATA:
-                experimentRegistry.removeExperiment((String)identifier);
+                experimentRegistry.removeExperiment(identifier);
                 break;
             case EXPERIMENT_CONFIGURATION_DATA:
-                experimentRegistry.removeExperimentConfigData((String)identifier);
+                experimentRegistry.removeExperimentConfigData(identifier);
             default:
                 logger.error("Unsupported data type...", new UnsupportedOperationException());
                 throw new UnsupportedOperationException();
@@ -275,12 +276,12 @@ public class RegistryImpl implements Registry {
      * @return whether the given data type exists or not
      */
     @Override
-    public boolean isExist(DataType dataType, Object identifier) {
+    public boolean isExist(DataType dataType, String identifier) {
         switch (dataType){
             case EXPERIMENT_BASIC_DATA:
-                return experimentRegistry.isExperimentBasicDataExist((String)identifier);
+                return experimentRegistry.isExperimentBasicDataExist(identifier);
             case EXPERIMENT_CONFIGURATION_DATA:
-                return experimentRegistry.isExperimentConfigDataExist((String)identifier);
+                return experimentRegistry.isExperimentConfigDataExist(identifier);
         }
         return false;
     }

http://git-wip-us.apache.org/repos/asf/airavata/blob/6555180e/modules/registry/registry-cpi/src/main/java/org/apache/airavata/registry/cpi/Registry.java
----------------------------------------------------------------------
diff --git a/modules/registry/registry-cpi/src/main/java/org/apache/airavata/registry/cpi/Registry.java b/modules/registry/registry-cpi/src/main/java/org/apache/airavata/registry/cpi/Registry.java
index cae3e54..cf2ee91 100644
--- a/modules/registry/registry-cpi/src/main/java/org/apache/airavata/registry/cpi/Registry.java
+++ b/modules/registry/registry-cpi/src/main/java/org/apache/airavata/registry/cpi/Registry.java
@@ -29,7 +29,7 @@ public interface Registry {
      *                            level object in the data model. If it is a top level object, programmer can pass it as
      *                            null
      */
-    public void add(ChildDataType dataType, Object newObjectToAdd, Object dependentIdentifier);
+    public void add(ChildDataType dataType, Object newObjectToAdd, String dependentIdentifier);
 
     /**
      * This method is to update the whole object in registry
@@ -41,7 +41,7 @@ public interface Registry {
      *                       have to fill the whole object. He needs to only fill the mandatory fields and whatever the
      *                       other fields that need to be updated.
      */
-    public void update(DataType dataType, Object newObjectToUpdate, Object identifier);
+    public void update(DataType dataType, Object newObjectToUpdate, String identifier);
 
 
     /**
@@ -56,7 +56,7 @@ public interface Registry {
      * @param value Value by which the given field need to be updated. If the field is "description", that field will be
      *              updated by given value
      */
-    public void update(DataType dataType, Object identifier, String fieldName, Object value);
+    public void update(DataType dataType, String identifier, String fieldName, Object value);
 
     /**
      * This method is to retrieve object according to the identifier. In the experiment basic data type, if you give the
@@ -67,7 +67,7 @@ public interface Registry {
      *                   identifier will be generated experimentID
      * @return object according to the given identifier.
      */
-    public Object get(DataType dataType, Object identifier);
+    public Object get(DataType dataType, String identifier);
 
     /**
      * This method is to retrieve list of objects according to a given criteria
@@ -91,7 +91,7 @@ public interface Registry {
      * @return return the value for the specific field where data model is identified by the unique identifier that has
      *         given
      */
-    public Object getValue (DataType dataType, Object identifier, String field);
+    public Object getValue (DataType dataType, String identifier, String field);
 
     /**
      * This method is to retrieve all the identifiers according to given filtering criteria. For an example, if you want
@@ -113,7 +113,7 @@ public interface Registry {
      * @param identifier Identifier which will uniquely identify the data model. For example, in Experiment_Basic_Type,
      *                   identifier will be generated experimentID
      */
-    public void remove (DataType dataType, Object identifier);
+    public void remove (DataType dataType, String identifier);
 
     /**
      * This method will check whether a given data type which can be identified with the identifier exists or not
@@ -123,7 +123,7 @@ public interface Registry {
      *                   identifier will be generated experimentID
      * @return whether the given data type exists or not
      */
-    public boolean isExist(DataType dataType, Object identifier);
+    public boolean isExist(DataType dataType, String identifier);
 
 
 }


Mime
View raw message