it.openutils.migration.oracle.OracleViewCreateOrUpdateTask Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of openutils-dbmigration Show documentation
DB Migration is a framework based on Spring for handling automatic db schema updates. With DBMigration you can bundle upgrade scripts in you application. Its purpose is to manage db updates consistently both for development than for production releases.
There is a newer version: 2.2.0
Show newest version
package it.openutils.migration.oracle;

import it.openutils.migration.task.setup.DbTask;

import java.io.IOException;
import java.io.InputStream;
import java.text.MessageFormat;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;


/**
 * 
 * Db tasks that handles the initial setup of views.
 * 
 * 
 * Limitations:
 * 
 * 
 *  * not supported in field list
 *  fields must be enclosed in quotes
 * 
 * @author albertoq
 * @version $Id: OracleViewCreateOrUpdateTask.java 684 2008-02-21 21:03:49Z fgiust $
 */
public class OracleViewCreateOrUpdateTask implements DbTask
{

    /**
     * Logger.
     */
    private Logger log = LoggerFactory.getLogger(OracleViewCreateOrUpdateTask.class);

    /**
     * Script list to execute
     */
    protected List scripts;

    /**
     * Query to verify view existence
     */
    private String selectUserViewExistence;

    /**
     * Query to retrieve view ddl
     */
    private String selectUserViewDDL;

    /**
     * Statement to drop a view
     */
    private String dropView;

    /**
     * {@inheritDoc}
     */
    public void execute(DataSource dataSource)
    {

        SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
        for (Resource script : scripts)
        {
            String viewName = this.objectNameFromFileName(script);

            int result = jdbcTemplate.queryForInt(getSelectUserViewExistence(), viewName);

            String scriptContent = readFully(script);

            if (scriptContent == null)
            {
                continue;
            }

            if (result == 0)
            {
                log.info("View {} not existing. Creating new view", viewName);

                createView(jdbcTemplate, scriptContent);
            }
            else
            {
                String scriptBody = extractViewBody(scriptContent);
                if (scriptBody == null)
                {
                    continue;
                }

                String previousDDl = (String) jdbcTemplate.getJdbcOperations().queryForObject(
                    getSelectUserViewDDL(),
                    new Object[]{viewName},
                    String.class);

                if (!StringUtils.equals(previousDDl.trim(), scriptBody.trim()))
                {
                    log.info(
                        "Previous definition of view {} differs from actual. Dropping and recreating view",
                        new Object[]{viewName});

                    jdbcTemplate.update(MessageFormat.format(getDropView(), new Object[]{viewName}));

                    createView(jdbcTemplate, scriptContent);
                }
            }
        }

    }

    /**
     * @param script The script resource
     * @return The script name
     */
    protected String objectNameFromFileName(Resource script)
    {
        return StringUtils.substringBeforeLast(script.getFilename(), ".");
    }

    /**
     * @param scriptContent
     * @return
     */
    private String extractViewBody(String scriptContent)
    {
        Pattern pattern = Pattern.compile(".*\\s+AS\\s+(.*)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher matcher = pattern.matcher(scriptContent);
        boolean bodyFound = matcher.find();
        if (bodyFound)
        {
            return matcher.group(1);
        }
        else
        {
            return null;
        }
    }

    /**
     * @param jdbcTemplate
     * @param script
     * @return
     */
    private void createView(SimpleJdbcTemplate jdbcTemplate, String script)
    {

        String[] ddls = StringUtils.split(script, ";");

        for (String ddl : ddls)
        {
            if (StringUtils.isNotBlank(ddl))
            {
                log.debug("Executing:\n{}", ddl);
                jdbcTemplate.update(ddl);
            }
        }
    }

    /**
     * @param script
     * @return
     */
    private String readFully(Resource script)
    {
        if (script == null || !script.exists())
        {
            log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
            return null;
        }

        String scriptContent;
        InputStream is = null;

        try
        {
            is = script.getInputStream();
            scriptContent = IOUtils.toString(is, "UTF8");
        }
        catch (IOException e)
        {
            log.error("Unable to execute db task \"{}\", script \"{}\" can't be read.", getDescription(), script);
            return null;
        }
        finally
        {
            IOUtils.closeQuietly(is);
        }
        return scriptContent;
    }

    public String getDescription()
    {
        return "Checking Views";
    }

    public List getScripts()
    {
        return scripts;
    }

    public void setScripts(List scripts)
    {
        this.scripts = scripts;
    }

    public String getSelectUserViewExistence()
    {
        return selectUserViewExistence;
    }

    public void setSelectUserViewExistence(String selectUserViewExistence)
    {
        this.selectUserViewExistence = selectUserViewExistence;
    }

    public String getSelectUserViewDDL()
    {
        return selectUserViewDDL;
    }

    public void setSelectUserViewDDL(String selectUserViewDDL)
    {
        this.selectUserViewDDL = selectUserViewDDL;
    }

    public String getDropView()
    {
        return dropView;
    }

    public void setDropView(String dropView)
    {
        this.dropView = dropView;
    }
}