play.db.evolutions.EvolutionQuery Maven / Gradle / Ivy

package play.db.evolutions;

import play.Logger;
import play.Play;
import play.db.Configuration;
import play.db.DB;
import play.db.SQLSplitter;
import play.db.jpa.JPAPlugin;
import play.exceptions.UnexpectedException;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import org.apache.commons.lang.StringUtils;

import com.sun.rowset.CachedRowSetImpl;


public class EvolutionQuery{
    
    public static void createTable(String dbName) throws SQLException {
        // If you are having problems with the default datatype text (clob for Oracle), you can
        // specify your own datatype using the 'evolution.PLAY_EVOLUTIONS.textType'-property
        String textDataType = Play.configuration.getProperty("evolution.PLAY_EVOLUTIONS.textType");
        if (textDataType == null) {
            if (isOracleDialectInUse(dbName)) {
                textDataType = "clob";
            } else {
                textDataType = "text";
            }
        }
        
        execute(dbName, "create table play_evolutions (id int not null, hash varchar(255) not null, applied_at timestamp not null, apply_script " + textDataType + ", revert_script " + textDataType + ", state varchar(255), last_problem " + textDataType + ", module_key varchar(255), constraint pk_id_module_key primary key (id, module_key))");
    }
    
    public static void alterForModuleSupport(String dbName, Connection connection) throws SQLException{
        // Add new column
        PreparedStatement ps1 = connection.prepareStatement("alter table play_evolutions add module_key varchar(255);");
        ps1.execute();
        closeStatement(ps1);
        
        // Set default value Assigning any existing evolutions to the parent project
        System.out.println("!!! - Assigning any existing evolutions to the parent project - !!!");
        PreparedStatement statement = connection.prepareStatement("update play_evolutions set module_key = ? where module_key is null");
        statement.setString(1, Play.configuration.getProperty("application.name"));
        statement.execute();
        closeStatement(statement);
       
        
        if(isMySqlDialectInUse(dbName)){
            // Drop previous primary key
            PreparedStatement ps2 = connection.prepareStatement( "alter table play_evolutions drop primary key;");
            ps2.execute();
            closeStatement(ps2);
        }else{
            // Drop previous primary key
            PreparedStatement ps3 = connection.prepareStatement("alter table play_evolutions drop constraint play_evolutions_pkey;");  
            ps3.execute();
            closeStatement(ps3);
        }
        
        // Add new primary key
        PreparedStatement ps4 = connection.prepareStatement("alter table play_evolutions add constraint pk_id_module_key primary key (id,module_key);");
        ps4.execute();
        closeStatement(ps4);
    }
    
    public static void resolve(String dbName, int revision, String moduleKey) throws SQLException {
	Connection connection = getNewConnection(dbName);
        PreparedStatement ps = connection.prepareStatement("update play_evolutions set state = ?, last_problem = ?  where state = ? and id = ? and module_key = ?" );
        ps.setString(1, EvolutionState.APPLIED.getStateWord() );
        ps.setString(2, "");
        ps.setString(3, EvolutionState.APPLYING_UP.getStateWord() );
        ps.setInt(4, revision);
        ps.setString(5, moduleKey);
        ps.execute();
        closeStatement(ps);
        
        PreparedStatement ps2 = connection.prepareStatement("delete from play_evolutions where state = ? and id = ? and module_key = ?" );
        ps2.setString(1, EvolutionState.APPLYING_DOWN.getStateWord() );
        ps2.setInt(2, revision);
        ps2.setString(3, moduleKey);
        ps2.execute();
        closeStatement(ps2);
    }
    
    public static void apply(Connection connection, boolean runScript, Evolution evolution, String moduleKey) throws SQLException {
        if (evolution.applyUp) {
            PreparedStatement ps = connection.prepareStatement("insert into play_evolutions values(?, ?, ?, ?, ?, ?, ?, ?)");
            ps.setInt(1, evolution.revision);
            ps.setString(2, evolution.hash);
            ps.setDate(3, new Date(System.currentTimeMillis()));
            ps.setString(4, evolution.sql_up);
            ps.setString(5, evolution.sql_down);
            ps.setString(6, EvolutionState.APPLYING_UP.getStateWord());
            ps.setString(7, "");
            ps.setString(8, moduleKey);
            ps.execute();
        } else {
            PreparedStatement ps = connection.prepareStatement("update play_evolutions set state = ? where id = ? and module_key = ?" );
            ps.setString(1, EvolutionState.APPLYING_DOWN.getStateWord() );
            ps.setInt(2, evolution.revision);
            ps.setString(3, moduleKey);
            ps.execute();
        }
       
        // Execute script
        if (runScript) {
           for (CharSequence sql : new SQLSplitter((evolution.applyUp ? evolution.sql_up : evolution.sql_down))) {
                final String s = sql.toString().trim();
                if (StringUtils.isEmpty(s)) {
                    continue;
                }              
                connection.createStatement().execute(s);
            }
        }
        // Insert into logs
        if (evolution.applyUp) {
            PreparedStatement ps = connection.prepareStatement("update play_evolutions set state = ?, last_problem = ? where id = ? and module_key = ?");
            ps.setString(1, EvolutionState.APPLIED.getStateWord() );
            ps.setString(2, "");
            ps.setInt(3, evolution.revision);
            ps.setString(4, moduleKey);
            ps.execute();
        } else {
            PreparedStatement ps = connection.prepareStatement("delete from play_evolutions where id = ? and module_key = ?");
            ps.setInt(1, evolution.revision);
            ps.setString(2, moduleKey);
            ps.execute();
        }
    }
    
    public static void setProblem(Connection connection, int applying,
	    String moduleKey, String message) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("update play_evolutions set last_problem = ? where id = ? and module_key = ?");
        ps.setString(1, message);
        ps.setInt(2, applying);
        ps.setString(3, moduleKey);
        ps.execute();
	
    }
    
    
    public static RowSet getEvolutionsToApply(Connection connection, String moduleKey) throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet  = null;
        try {
            statement = connection
                    .prepareStatement("select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where module_key = ? and state like 'applying_%'");
            statement.setString(1, moduleKey);
            resultSet = statement.executeQuery();
            // Need to use a CachedRowSet that caches its rows in memory, which
            // makes it possible to operate without always being connected to
            // its data source
            CachedRowSet rowset = new CachedRowSetImpl();
            rowset.populate(resultSet);
            return rowset;
        } catch (SQLException e) {
            throw e;
        } finally {
            closeResultSet(resultSet);
            closeStatement(statement);
        }
    }
    
    public static RowSet getEvolutions(Connection connection, String moduleKey) throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet  = null;
        try {
            statement = connection
                    .prepareStatement("select id, hash, apply_script, revert_script from play_evolutions where module_key = ?");
            statement.setString(1, moduleKey);
            resultSet = statement.executeQuery();
            // Need to use a CachedRowSet that caches its rows in memory, which
            // makes it possible to operate without always being connected to
            // its data source
            CachedRowSet rowset = new CachedRowSetImpl();
            rowset.populate(resultSet);
            return rowset;
        } catch (SQLException e) {
            throw e;
        } finally {
            closeResultSet(resultSet);
            closeStatement(statement);
        }
    }
    
    // JDBC Utils
    private static void execute(String dbName, String sql) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getNewConnection(dbName);
            statement = connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            closeStatement(statement);
            closeConnection(connection);
        }
    }
    
    public static Connection getNewConnection() throws SQLException {
        return getNewConnection(DB.DEFAULT, true); // Yes we want auto-commit
    }
    
    public static Connection getNewConnection(String dbName) throws SQLException {
        return getNewConnection(dbName, true); // Yes we want auto-commit
    }

    public static Connection getNewConnection(String dbName, boolean autoCommit) throws SQLException {
        Connection connection = DB.getDataSource(dbName).getConnection();
        connection.setAutoCommit(autoCommit); 
        return connection;
    }
    
    public static void closeResultSet(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (Exception e) {
            throw new UnexpectedException(e);
        }
    }
    
    public static void closeStatement(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (Exception e) {
            throw new UnexpectedException(e);
        }
    }

    public static void closeConnection(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            throw new UnexpectedException(e);
        }
    }

    private synchronized static boolean isOracleDialectInUse(String dbName) {
        boolean isOracle = false;
        Configuration dbConfig = new Configuration(dbName);
        String jpaDialect = JPAPlugin.getDefaultDialect(dbConfig.getProperty("db.driver")); 
        if (jpaDialect != null) {
            try {
                Class dialectClass = Play.classloader.loadClass(jpaDialect);
			
                // Oracle 8i dialect is the base class for oracle dialects (at least for now)
                isOracle = org.hibernate.dialect.Oracle8iDialect.class.isAssignableFrom(dialectClass);
            } catch (ClassNotFoundException e) {
                // swallow
                Logger.warn("jpa.dialect class %s not found", jpaDialect);
            }
        }
        return isOracle;
    }
    
    private static boolean isMySqlDialectInUse(String dbName) {
        boolean isMySQl = false;
        Configuration dbConfig = new Configuration(dbName);
        String jpaDialect = JPAPlugin.getDefaultDialect(dbConfig.getProperty("db.driver"));
        if (jpaDialect != null) {
            try {
                Class dialectClass = Play.classloader.loadClass(jpaDialect);

                // MySQLDialect is the base class for MySQL dialects
                isMySQl = org.hibernate.dialect.MySQLDialect.class.isAssignableFrom(dialectClass);
            } catch (ClassNotFoundException e) {
                // swallow
                Logger.warn("jpa.dialect class %s not found", jpaDialect);
            }
        }
        return isMySQl;
    }

}