Menu
▾
▴
[Openutils-svn] SF.net SVN: openutils: [452] trunk/openutils-dbmigration/src/main/java/it/ openutils/migration
|
From: <fg...@us...> - 2007-09-24 20:09:23
|
Revision: 452
http://openutils.svn.sourceforge.net/openutils/?rev=452&view=rev
Author: fgiust
Date: 2007-09-24 13:09:22 -0700 (Mon, 24 Sep 2007)
Log Message:
-----------
sqlserver specific tasks
Added Paths:
-----------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerScriptBasedUnconditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerSynonymCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTableCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTriggerCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreationTask.java
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreationTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,26 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import javax.sql.DataSource;
+
+
+/**
+ * @author Danilo Ghirardelli
+ * @version $Id:SqlServerFunctionCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerFunctionCreationTask extends SqlServerObjCreationTask
+{
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public void execute(DataSource dataSource)
+ {
+ setQualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and xtype in (N'FN', N'IF', N'TF')");
+ setUnqualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and xtype in (N'FN', N'IF', N'TF')");
+ super.execute(dataSource);
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,240 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import it.openutils.migration.task.setup.GenericConditionalTask;
+
+import java.io.IOException;
+import java.io.InputStream;
+
+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;
+
+
+/**
+ * This class is used for any alter task needed. The triggerValue is the value (only numeric) of result query that
+ * activates the alter script. Default is zero. Both the condition query and the alter query can be script or embedded
+ * in the xml.
+ * @author Danilo Ghirardelli
+ * @version $Id:SqlServerGenericAlterTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerGenericAlterTask extends GenericConditionalTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(SqlServerGenericAlterTask.class);
+
+ /**
+ * The condition can be in a string or in a script file.
+ */
+ private Resource checkScript;
+
+ /**
+ * Override of the corresponding string.
+ */
+ private String ddl;
+
+ /**
+ * The alter script can be embedded in the xml file or in a script file.
+ */
+ private Resource ddlScript;
+
+ /**
+ * The value that will let the ddl script start. Default is zero.
+ */
+ private Integer triggerValue = 0;
+
+ /**
+ * The db with the objects, synonyms will point to this db.
+ */
+ private String sourceDb;
+
+ /**
+ * Sets the checkScript.
+ * @param checkScript the checkScript to set
+ */
+ public void setCheckScript(Resource checkScript)
+ {
+ this.checkScript = checkScript;
+ }
+
+ /**
+ * Sets the ddl.
+ * @param ddl the ddl to set
+ */
+ @Override
+ public void setDdl(String ddl)
+ {
+ this.ddl = ddl;
+ }
+
+ /**
+ * Sets the ddlScript.
+ * @param ddlScript the ddlScript to set
+ */
+ public void setDdlScript(Resource ddlScript)
+ {
+ this.ddlScript = ddlScript;
+ }
+
+ /**
+ * Sets the triggerValue.
+ * @param triggerValue the triggerValue to set
+ */
+ public void setTriggerValue(Integer triggerValue)
+ {
+ this.triggerValue = triggerValue;
+ }
+
+ /**
+ * Sets the sourceDb.
+ * @param sourceDb the sourceDb to set
+ */
+ public void setSourceDb(String sourceDb)
+ {
+ this.sourceDb = sourceDb;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public String getDescription()
+ {
+
+ if (StringUtils.isNotEmpty(super.getDescription()))
+ {
+ return super.getDescription();
+ }
+
+ StringBuffer result = new StringBuffer();
+ if (StringUtils.isNotBlank(getCheck()))
+ {
+ result.append("Checking alter task condition:\n" + getCheck());
+ }
+ else
+ {
+ result.append("Checking alter task condition in script:\n" + checkScript);
+ }
+ return result.toString();
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public void execute(DataSource dataSource)
+ {
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+
+ String resultQuery;
+ if (StringUtils.isNotBlank(getCheck()))
+ {
+ // Simple query embedded in xml.
+ resultQuery = getCheck();
+ resultQuery = StringUtils.isNotBlank(this.sourceDb) ? StringUtils.replace(
+ resultQuery,
+ "${sourceDb}",
+ this.sourceDb) : resultQuery;
+ }
+ else
+ {
+ // Check query in a script file.
+ if (checkScript == null || !checkScript.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), checkScript);
+ return;
+ }
+ InputStream is = null;
+ String scriptContent;
+ try
+ {
+ is = checkScript.getInputStream();
+ scriptContent = IOUtils.toString(is, "UTF8");
+ }
+ catch (IOException e)
+ {
+ log.error(
+ "Unable to execute db task \"{}\", script \"{}\" can't be read.",
+ getDescription(),
+ checkScript);
+ return;
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+ resultQuery = scriptContent;
+ resultQuery = StringUtils.isNotBlank(this.sourceDb) ? StringUtils.replace(
+ resultQuery,
+ "${sourceDb}",
+ this.sourceDb) : resultQuery;
+ }
+
+ if ((triggerValue != null) && (jdbcTemplate.queryForInt(resultQuery) == triggerValue))
+ {
+ log.info("Executing Alter Task: {}", getDescription());
+ String scriptContent;
+
+ if (StringUtils.isNotBlank(ddl))
+ {
+ scriptContent = ddl;
+ }
+ else
+ {
+ if (ddlScript == null || !ddlScript.exists())
+ {
+ log
+ .error(
+ "Unable to execute db task \"{}\", script \"{}\" not found.",
+ getDescription(),
+ ddlScript);
+ return;
+ }
+ InputStream is = null;
+ try
+ {
+ is = ddlScript.getInputStream();
+ scriptContent = IOUtils.toString(is, "UTF8");
+ }
+ catch (IOException e)
+ {
+ log.error(
+ "Unable to execute db task \"{}\", script \"{}\" can't be read.",
+ getDescription(),
+ ddlScript);
+ return;
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+ }
+
+ String[] ddls = StringUtils.split(scriptContent, ";");
+
+ for (String ddl : ddls)
+ {
+ if (StringUtils.isNotBlank(ddl))
+ {
+ String ddlReplaced = ddl;
+ ddlReplaced = StringUtils.isNotBlank(this.sourceDb) ? StringUtils.replace(
+ ddlReplaced,
+ "${sourceDb}",
+ this.sourceDb) : ddlReplaced;
+ log.debug("Executing:\n{}", ddlReplaced);
+ jdbcTemplate.update(ddlReplaced);
+ }
+ }
+ }
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,162 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+
+import java.io.IOException;
+import java.io.InputStream;
+
+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;
+
+
+/**
+ * @author fgiust
+ * @version $Id:SqlServerObjCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerObjCreationTask extends GenericScriptBasedConditionalTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(SqlServerObjCreationTask.class);
+
+ /**
+ * Query to check with standard objects' name.
+ */
+ private String unqualifiedObjQuery;
+
+ /**
+ * Query to check with full database objects' hierarchy.
+ */
+ private String qualifiedObjQuery;
+
+ /**
+ * The db with the objects, synonyms will point to this db.
+ */
+ private String sourceDb;
+
+ /**
+ * Returns the qualifiedObjQuery.
+ * @return the qualifiedObjQuery
+ */
+ public String getQualifiedObjQuery()
+ {
+ return qualifiedObjQuery;
+ }
+
+ /**
+ * Sets the qualifiedObjQuery.
+ * @param qualifiedObjQuery the qualifiedObjQuery to set
+ */
+ public void setQualifiedObjQuery(String qualifiedObjQuery)
+ {
+ this.qualifiedObjQuery = qualifiedObjQuery;
+ }
+
+ /**
+ * Sets the sourceDb.
+ * @param sourceDb the sourceDb to set
+ */
+ public void setSourceDb(String sourceDb)
+ {
+ this.sourceDb = sourceDb;
+ }
+
+ /**
+ * Returns the unqualifiedObjQuery.
+ * @return the unqualifiedObjQuery
+ */
+ public String getUnqualifiedObjQuery()
+ {
+ return unqualifiedObjQuery;
+ }
+
+ /**
+ * Sets the unqualifiedObjQuery.
+ * @param unqualifiedObjQuery the unqualifiedObjQuery to set
+ */
+ public void setUnqualifiedObjQuery(String unqualifiedObjQuery)
+ {
+ this.unqualifiedObjQuery = unqualifiedObjQuery;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public void execute(DataSource dataSource)
+ {
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+ for (Resource script : scripts)
+ {
+ String fqTableName = this.objectNameFromFileName(script);
+
+ int result = 0;
+ if (StringUtils.contains(fqTableName, "."))
+ {
+ String[] tokens = StringUtils.split(fqTableName, ".");
+ result = jdbcTemplate.queryForInt(qualifiedObjQuery, new Object[]{tokens[1], tokens[0] });
+ }
+ else
+ {
+ result = jdbcTemplate.queryForInt(unqualifiedObjQuery, fqTableName);
+ }
+
+ if (result == 0)
+ {
+ if (script == null || !script.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
+ return;
+ }
+
+ 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;
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+
+ String[] ddls = StringUtils.split(scriptContent, ";");
+
+ for (String ddl : ddls)
+ {
+ if (StringUtils.isNotBlank(ddl))
+ {
+ String ddlReplaced = ddl;
+ ddlReplaced = StringUtils.isNotBlank(this.sourceDb) ? StringUtils.replace(
+ ddlReplaced,
+ "${sourceDb}",
+ this.sourceDb) : ddlReplaced;
+ log.debug("Executing:\n{}", ddlReplaced);
+ jdbcTemplate.update(ddlReplaced);
+ }
+ }
+ }
+ }
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerScriptBasedUnconditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerScriptBasedUnconditionalTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerScriptBasedUnconditionalTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,104 @@
+package it.openutils.migration.sqlserver;
+
+import it.openutils.migration.task.setup.BaseDbTask;
+import it.openutils.migration.task.setup.DbTask;
+import it.openutils.migration.task.setup.ScriptBasedUnconditionalTask;
+
+import java.io.IOException;
+import java.util.List;
+
+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;
+
+
+/**
+ * @author fgiust
+ * @version $Id:SqlServerScriptBasedUnconditionalTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerScriptBasedUnconditionalTask extends BaseDbTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(ScriptBasedUnconditionalTask.class);
+
+ private List<Resource> scripts;
+
+ /**
+ * Sets the scripts.
+ * @param scripts the scripts to set
+ */
+ public void setScripts(List<Resource> scripts)
+ {
+ this.scripts = scripts;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+
+ for (Resource script : scripts)
+ {
+ if (script == null || !script.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
+ return;
+ }
+
+ String scriptContent;
+
+ try
+ {
+ // @todo we should read line by line, avoiding to cache all the script in memory
+ scriptContent = IOUtils.toString(script.getInputStream(), "UTF8");
+ }
+ catch (IOException e)
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" can't be read.", getDescription(), script);
+ return;
+ }
+
+ String[] ddls = splitStatements(scriptContent);
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+
+ for (final String ddl : ddls)
+ {
+ if (StringUtils.isNotBlank(ddl))
+ {
+ log.debug("Executing:\n{}", ddl);
+
+ jdbcTemplate.update(ddl);
+ }
+ }
+
+ }
+
+ }
+
+ /**
+ * @param scriptContent
+ * @return
+ */
+ private String[] splitStatements(String scriptContent)
+ {
+ String[] ddls;
+ if (scriptContent.indexOf(';') > -1)
+ {
+ ddls = StringUtils.splitByWholeSeparator(scriptContent, ";");
+ }
+ else
+ {
+ ddls = StringUtils.splitByWholeSeparator(scriptContent, "\nGO");
+ }
+ return ddls;
+ }
+}
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerSynonymCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerSynonymCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerSynonymCreationTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,78 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import it.openutils.migration.task.setup.DbTask;
+
+import java.util.List;
+
+import javax.sql.DataSource;
+
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+/**
+ * @author Danilo Ghirardelli
+ * @version $Id:SqlServerSynonymCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerSynonymCreationTask implements DbTask
+{
+
+ private String source;
+
+ private List<String> objects;
+
+ /**
+ * Sets the source.
+ * @param source the source to set
+ */
+ public void setSource(String source)
+ {
+ this.source = source;
+ }
+
+ /**
+ * Sets the objects.
+ * @param objects the objects to set
+ */
+ public void setObjects(List<String> objects)
+ {
+ this.objects = objects;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+
+ for (String objectName : objects)
+ {
+ int result = jdbcTemplate.queryForInt(
+ "select count(*) from dbo.sysobjects where id = object_id(?) and xtype = N'SN'",
+ objectName);
+ if (result == 0)
+ {
+ jdbcTemplate.update("CREATE SYNONYM [dbo].["
+ + objectName
+ + "] FOR ["
+ + source
+ + "].[dbo].["
+ + objectName
+ + "]");
+ }
+ }
+
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public String getDescription()
+ {
+ return "Creating synonyms from " + source;
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTableCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTableCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTableCreationTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,26 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import javax.sql.DataSource;
+
+
+/**
+ * @author fgiust
+ * @version $Id:SqlServerTableCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerTableCreationTask extends SqlServerObjCreationTask
+{
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public void execute(DataSource dataSource)
+ {
+ setQualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsUserTable') = 1");
+ setUnqualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsUserTable') = 1");
+ super.execute(dataSource);
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTriggerCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTriggerCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerTriggerCreationTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,26 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import javax.sql.DataSource;
+
+
+/**
+ * @author Danilo Ghirardelli
+ * @version $Id:SqlServerTriggerCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerTriggerCreationTask extends SqlServerObjCreationTask
+{
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public void execute(DataSource dataSource)
+ {
+ setQualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsTrigger') = 1");
+ setUnqualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsTrigger') = 1");
+ super.execute(dataSource);
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,156 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.List;
+
+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;
+
+
+/**
+ * @author Danilo Ghirardelli
+ * @version $Id: SqlServerViewCreateOrUpdateTask.java 391 2007-07-10 17:25:42Z fgiust $
+ */
+public class SqlServerViewCreateOrUpdateTask extends GenericScriptBasedConditionalTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(SqlServerObjCreationTask.class);
+
+ /**
+ * The db with the objects, synonyms will point to this db.
+ */
+ private String sourceDb;
+
+ /**
+ * Sets the sourceDb.
+ * @param sourceDb the sourceDb to set
+ */
+ public void setSourceDb(String sourceDb)
+ {
+ this.sourceDb = sourceDb;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @SuppressWarnings("unchecked")
+ @Override
+ public void execute(DataSource dataSource)
+ {
+
+ String checkQuery = "select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsView') = 1";
+
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+ for (Resource script : scripts)
+ {
+ String viewName = this.objectNameFromFileName(script);
+
+ int result = jdbcTemplate.queryForInt(checkQuery, 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
+ {
+
+ List<String> previousDDlList = jdbcTemplate.getJdbcOperations().queryForList(
+ "exec sp_helptext ?",
+ new Object[]{viewName },
+ String.class);
+
+ String previousDDl = StringUtils.join(previousDDlList.toArray(new String[previousDDlList.size()]));
+
+ if (!StringUtils.equals(previousDDl, scriptContent))
+ {
+ log.info(
+ "Previous definition of view {} differs from actual. Dropping and recreating view",
+ new Object[]{viewName });
+
+ jdbcTemplate.update("DROP VIEW [dbo].[" + viewName + "]");
+
+ createView(jdbcTemplate, scriptContent);
+ }
+ }
+ }
+
+ }
+
+ /**
+ * @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 StringUtils.stripEnd(
+ StringUtils.trim(StringUtils.replace(scriptContent, "${sourceDb}", this.sourceDb)),
+ ";");
+ }
+}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreationTask.java 2007-09-24 20:09:22 UTC (rev 452)
@@ -0,0 +1,26 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.sqlserver;
+
+import javax.sql.DataSource;
+
+
+/**
+ * @author Danilo Ghirardelli
+ * @version $Id:SqlServerViewCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public class SqlServerViewCreationTask extends SqlServerObjCreationTask
+{
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public void execute(DataSource dataSource)
+ {
+ setQualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsView') = 1");
+ setUnqualifiedObjQuery("select count(*) from dbo.sysobjects where id = object_id(?) and OBJECTPROPERTY(id, N'IsView') = 1");
+ super.execute(dataSource);
+ }
+}
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
