Menu
▾
▴
[Openutils-svn] SF.net SVN: openutils: [568] trunk/openutils-dbmigration/src
|
From: <fg...@us...> - 2008-01-27 15:25:42
|
Revision: 568
http://openutils.svn.sourceforge.net/openutils/?rev=568&view=rev
Author: fgiust
Date: 2008-01-27 07:25:47 -0800 (Sun, 27 Jan 2008)
Log Message:
-----------
a deep cleanup
Modified Paths:
--------------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcTableCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcViewCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreateOrUpdateTask.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/SqlServerProcedureCreateOrUpdateTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseDbTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericScriptBasedConditionalTask.java
trunk/openutils-dbmigration/src/site/apt/index.apt
Added Paths:
-----------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java
trunk/openutils-dbmigration/src/site/apt/guide.apt
Removed Paths:
-------------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcConditionalTask.java
Deleted: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcConditionalTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcConditionalTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -1,78 +0,0 @@
-/*
- * Copyright Openmind http://www.openmindonline.it
- *
- * Licensed 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 it.openutils.migration.generic;
-
-import it.openutils.migration.task.setup.BaseDbTask;
-import it.openutils.migration.task.setup.DbTask;
-
-import javax.sql.DataSource;
-
-import org.apache.commons.lang.StringUtils;
-import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
-
-
-/**
- * @author fgiust
- * @version $Id:SqlServerObjCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
- */
-public abstract class JdbcConditionalTask extends BaseDbTask implements DbTask
-{
-
- private String ddl;
-
- private boolean not;
-
- /**
- * {@inheritDoc}
- */
- public void setDdl(String ddls)
- {
- this.ddl = ddls;
- }
-
- /**
- * Sets the not.
- * @param not the not to set
- */
- public void setNot(boolean not)
- {
- this.not = not;
- }
-
- public abstract boolean check(SimpleJdbcTemplate jdbcTemplate);
-
- /**
- * {@inheritDoc}
- */
- public void execute(DataSource dataSource)
- {
- SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
-
- if (check(jdbcTemplate) ^ !not)
- {
-
- String[] ddls = StringUtils.split(ddl, ';');
- for (String statement : ddls)
- {
- if (StringUtils.isNotBlank(statement))
- {
- jdbcTemplate.update(statement);
- }
- }
- }
- }
-
-}
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -1,5 +1,7 @@
package it.openutils.migration.generic;
+import it.openutils.migration.task.setup.BaseConditionalTask;
+
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
@@ -15,7 +17,7 @@
* @author fgiust
* @version $Id: $
*/
-public class JdbcIfColumnExistsConditionalTask extends JdbcConditionalTask
+public class JdbcIfColumnExistsConditionalTask extends BaseConditionalTask
{
private String column;
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,7 +15,7 @@
*/
package it.openutils.migration.generic;
-import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+import it.openutils.migration.task.setup.GenericConditionalTask;
import java.io.IOException;
import java.io.InputStream;
@@ -28,8 +28,6 @@
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.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
@@ -41,18 +39,13 @@
* @author fgiust
* @version $Id:SqlServerObjCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
*/
-public class JdbcObjectCreationTask extends GenericScriptBasedConditionalTask
+public abstract class JdbcObjectCreationTask extends GenericConditionalTask
{
- /**
- * Logger.
- */
- protected Logger log = LoggerFactory.getLogger(getClass());
-
- protected String objectType = "TABLE";
-
protected String catalog;
+ abstract String getObjectType();
+
/**
* {@inheritDoc}
*/
@@ -95,7 +88,7 @@
{
DatabaseMetaData dbMetadata = con.getMetaData();
- ResultSet rs = dbMetadata.getTables(catalog, schema, tableName, new String[]{objectType });
+ ResultSet rs = dbMetadata.getTables(catalog, schema, tableName, new String[]{getObjectType() });
boolean tableExists = rs.first();
rs.close();
@@ -128,7 +121,7 @@
String[] ddls = StringUtils.split(scriptContent, ";");
- log.info("Creating new {} {}", objectType, tableName);
+ log.info("Creating new {} {}", getObjectType(), tableName);
for (String ddl : ddls)
{
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcTableCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcTableCreationTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcTableCreationTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -22,5 +22,12 @@
public class JdbcTableCreationTask extends JdbcObjectCreationTask
{
- protected String objectType = "TABLE";
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ String getObjectType()
+ {
+ return "TABLE";
+ }
}
\ No newline at end of file
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcViewCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcViewCreationTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcViewCreationTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -22,5 +22,12 @@
public class JdbcViewCreationTask extends JdbcObjectCreationTask
{
- protected String objectType = "VIEW";
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ String getObjectType()
+ {
+ return "VIEW";
+ }
}
\ No newline at end of file
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreateOrUpdateTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreateOrUpdateTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerFunctionCreateOrUpdateTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,7 +15,7 @@
*/
package it.openutils.migration.sqlserver;
-import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+import it.openutils.migration.task.setup.GenericConditionalTask;
import java.io.IOException;
import java.io.InputStream;
@@ -25,8 +25,6 @@
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;
@@ -38,29 +36,10 @@
* @author Danilo Ghirardelli
* @version $Id$
*/
-public class SqlServerFunctionCreateOrUpdateTask extends GenericScriptBasedConditionalTask
+public class SqlServerFunctionCreateOrUpdateTask extends GenericConditionalTask
{
/**
- * Logger.
- */
- private Logger log = LoggerFactory.getLogger(SqlServerObjCreationTask.class);
-
- /**
- * The db with the objects, may differ from the current.
- */
- private String sourceDb;
-
- /**
- * Sets the sourceDb.
- * @param sourceDb the sourceDb to set
- */
- public void setSourceDb(String sourceDb)
- {
- this.sourceDb = sourceDb;
- }
-
- /**
* {@inheritDoc}
*/
@SuppressWarnings("unchecked")
@@ -153,8 +132,6 @@
{
IOUtils.closeQuietly(is);
}
- return StringUtils.stripEnd(
- StringUtils.trim(StringUtils.replace(scriptContent, "${sourceDb}", this.sourceDb)),
- ";");
+ return StringUtils.stripEnd(StringUtils.trim(performSubstitution(scriptContent)), ";");
}
}
\ No newline at end of file
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerGenericAlterTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -17,236 +17,25 @@
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.
+ * activates the alter script. Default is zero. The alter query can be an external script or embedded in the xml.
+ * @deprecated
* @author Danilo Ghirardelli
* @version $Id:SqlServerGenericAlterTask.java 3143 2007-09-24 19:50:49Z fgiust $
*/
+@Deprecated
public class SqlServerGenericAlterTask extends GenericConditionalTask
{
/**
- * Logger.
+ * Constructor.
*/
- 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)
+ public SqlServerGenericAlterTask()
{
- this.checkScript = checkScript;
+ super();
+ log.warn(getClass().getName() + " is deprecated, please use " + GenericConditionalTask.class.getName());
}
- /**
- * 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
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerObjCreationTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,7 +15,7 @@
*/
package it.openutils.migration.sqlserver;
-import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+import it.openutils.migration.task.setup.GenericConditionalTask;
import java.io.IOException;
import java.io.InputStream;
@@ -24,8 +24,6 @@
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;
@@ -34,15 +32,10 @@
* @author fgiust
* @version $Id:SqlServerObjCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
*/
-public class SqlServerObjCreationTask extends GenericScriptBasedConditionalTask
+public class SqlServerObjCreationTask extends GenericConditionalTask
{
/**
- * Logger.
- */
- private Logger log = LoggerFactory.getLogger(SqlServerObjCreationTask.class);
-
- /**
* Query to check with standard objects' name.
*/
private String unqualifiedObjQuery;
@@ -53,11 +46,6 @@
private String qualifiedObjQuery;
/**
- * The db with the objects, synonyms will point to this db.
- */
- private String sourceDb;
-
- /**
* Returns the qualifiedObjQuery.
* @return the qualifiedObjQuery
*/
@@ -76,15 +64,6 @@
}
/**
- * Sets the sourceDb.
- * @param sourceDb the sourceDb to set
- */
- public void setSourceDb(String sourceDb)
- {
- this.sourceDb = sourceDb;
- }
-
- /**
* Returns the unqualifiedObjQuery.
* @return the unqualifiedObjQuery
*/
@@ -159,11 +138,7 @@
{
if (StringUtils.isNotBlank(ddl))
{
- String ddlReplaced = ddl;
- ddlReplaced = StringUtils.isNotBlank(this.sourceDb) ? StringUtils.replace(
- ddlReplaced,
- "${sourceDb}",
- this.sourceDb) : ddlReplaced;
+ String ddlReplaced = performSubstitution(ddl);
log.debug("Executing:\n{}", ddlReplaced);
jdbcTemplate.update(ddlReplaced);
}
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerProcedureCreateOrUpdateTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerProcedureCreateOrUpdateTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerProcedureCreateOrUpdateTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,7 +15,7 @@
*/
package it.openutils.migration.sqlserver;
-import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+import it.openutils.migration.task.setup.GenericConditionalTask;
import java.io.IOException;
import java.io.InputStream;
@@ -25,8 +25,6 @@
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;
@@ -38,29 +36,10 @@
* @author Danilo Ghirardelli
* @version $Id$
*/
-public class SqlServerProcedureCreateOrUpdateTask extends GenericScriptBasedConditionalTask
+public class SqlServerProcedureCreateOrUpdateTask extends GenericConditionalTask
{
/**
- * Logger.
- */
- private Logger log = LoggerFactory.getLogger(SqlServerObjCreationTask.class);
-
- /**
- * The db with the objects, may differ from the current.
- */
- private String sourceDb;
-
- /**
- * Sets the sourceDb.
- * @param sourceDb the sourceDb to set
- */
- public void setSourceDb(String sourceDb)
- {
- this.sourceDb = sourceDb;
- }
-
- /**
* {@inheritDoc}
*/
@SuppressWarnings("unchecked")
@@ -152,8 +131,6 @@
{
IOUtils.closeQuietly(is);
}
- return StringUtils.stripEnd(
- StringUtils.trim(StringUtils.replace(scriptContent, "${sourceDb}", this.sourceDb)),
- ";");
+ return StringUtils.stripEnd(StringUtils.trim(performSubstitution(scriptContent)), ";");
}
}
\ No newline at end of file
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/sqlserver/SqlServerViewCreateOrUpdateTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,7 +15,7 @@
*/
package it.openutils.migration.sqlserver;
-import it.openutils.migration.task.setup.GenericScriptBasedConditionalTask;
+import it.openutils.migration.task.setup.GenericConditionalTask;
import java.io.IOException;
import java.io.InputStream;
@@ -25,8 +25,6 @@
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;
@@ -35,29 +33,10 @@
* @author Danilo Ghirardelli
* @version $Id: SqlServerViewCreateOrUpdateTask.java 391 2007-07-10 17:25:42Z fgiust $
*/
-public class SqlServerViewCreateOrUpdateTask extends GenericScriptBasedConditionalTask
+public class SqlServerViewCreateOrUpdateTask extends GenericConditionalTask
{
/**
- * 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")
@@ -162,8 +141,6 @@
{
IOUtils.closeQuietly(is);
}
- return StringUtils.stripEnd(
- StringUtils.trim(StringUtils.replace(scriptContent, "${sourceDb}", this.sourceDb)),
- ";");
+ return StringUtils.stripEnd(StringUtils.trim(performSubstitution(scriptContent)), ";");
}
}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -0,0 +1,240 @@
+/*
+ * Copyright Openmind http://www.openmindonline.it
+ *
+ * Licensed 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 it.openutils.migration.task.setup;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+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;
+
+
+/**
+ * A base conditional task that executes a task only if an expected condition is met.
+ * @author fgiust
+ * @version $Id:SqlServerObjCreationTask.java 3143 2007-09-24 19:50:49Z fgiust $
+ */
+public abstract class BaseConditionalTask extends BaseDbTask
+{
+
+ /**
+ * Logger.
+ */
+ protected Logger log = LoggerFactory.getLogger(getClass());
+
+ /**
+ * DDL to run when the condition is met.
+ */
+ protected String ddl;
+
+ /**
+ * If <code>true</code> executes only if check returned <code>false</code>
+ */
+ protected boolean not;
+
+ /**
+ * Map of key-value that will be replaced in ddl.
+ */
+ protected Map<String, String> variables;
+
+ /**
+ * Script list to execute
+ */
+ protected List<Resource> scripts;
+
+ /**
+ * Sets the scripts.
+ * @param scripts the scripts to set
+ */
+ public void setScripts(List<Resource> scripts)
+ {
+ this.scripts = scripts;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public final void setDdl(String ddls)
+ {
+ this.ddl = ddls;
+ }
+
+ /**
+ * Sets the not.
+ * @param not the not to set
+ */
+ public final void setNot(boolean not)
+ {
+ this.not = not;
+ }
+
+ /**
+ * Sets the ddlScript.
+ * @param ddlScript the ddlScript to set
+ * @deprecated use the "scripts" property
+ */
+ @Deprecated
+ public final void setDdlScript(Resource ddlScript)
+ {
+ log.warn("ddlScript is deprecated, please use \"scripts\"");
+ if (scripts == null)
+ {
+ scripts = new ArrayList<Resource>(1);
+ }
+ scripts.add(ddlScript);
+ }
+
+ /**
+ * Subclasses need to override this method and provide specific checks.
+ * @param jdbcTemplate SimpleJdbcTemplate
+ * @return true if the condition is met
+ */
+ public abstract boolean check(SimpleJdbcTemplate jdbcTemplate);
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public String getDescription()
+ {
+
+ if (StringUtils.isNotEmpty(super.getDescription()))
+ {
+ return super.getDescription();
+ }
+
+ if (scripts != null && !scripts.isEmpty())
+ {
+ StringBuffer result = new StringBuffer();
+ result.append("Checking :\n");
+ for (Resource script : scripts)
+ {
+ result.append(" - " + objectNameFromFileName(script) + "\n");
+ }
+ return result.toString();
+
+ }
+
+ return getClass().getName();
+ }
+
+ /**
+ * @param script The script resource
+ * @return The script name
+ */
+ protected String objectNameFromFileName(Resource script)
+ {
+ return StringUtils.substringBeforeLast(script.getFilename(), ".");
+ }
+
+ /**
+ * Perform sobstitution in the given string.
+ * @param string Original String
+ * @return processed string
+ */
+ protected String performSubstitution(String string)
+ {
+ if (variables == null || variables.isEmpty())
+ {
+ return string;
+ }
+
+ String stringReplaced = string;
+ for (String key : variables.keySet())
+ {
+ stringReplaced = StringUtils.replace(stringReplaced, "${" + key + "}", variables.get(key));
+ }
+
+ return stringReplaced;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+
+ if (check(jdbcTemplate) ^ !not)
+ {
+ log.info("Executing Alter Task: {}", getDescription());
+
+ if (StringUtils.isNotBlank(ddl))
+ {
+ executeSingle(jdbcTemplate, ddl);
+ }
+ else
+ {
+ if (scripts == null || scripts.isEmpty())
+ {
+ log.error("Unable to execute db task \"{}\", no ddl or scripts configured.", getDescription());
+ return;
+ }
+
+ for (Resource script : scripts)
+ {
+ String scriptContent = ddl;
+ 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);
+ }
+ executeSingle(jdbcTemplate, scriptContent);
+ }
+ }
+ }
+ }
+
+ /**
+ * @param jdbcTemplate
+ * @param scriptContent
+ */
+ private void executeSingle(SimpleJdbcTemplate jdbcTemplate, String scriptContent)
+ {
+ String[] ddls = StringUtils.split(performSubstitution(scriptContent), ';');
+ for (String statement : ddls)
+ {
+ if (StringUtils.isNotBlank(statement))
+ {
+ log.debug("Executing:\n{}", statement);
+ jdbcTemplate.update(statement);
+ }
+ }
+ }
+
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Author Date Id Revision
Name: svn:eol-style
+ native
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseDbTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseDbTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseDbTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -22,7 +22,10 @@
public abstract class BaseDbTask implements DbTask
{
- private String description;
+ /**
+ * An readable description for what this task is doing.
+ */
+ protected String description;
/**
* {@inheritDoc}
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -225,6 +225,7 @@
* @param updateStatement
* @param selectStatement
*/
+ @SuppressWarnings("unchecked")
private void processRecords(HSSFSheet sheet, List<String> columns, int[] types, String checkStatement,
String insertStatement, String selectStatement, String updateStatement, DataSource dataSource, String tableName)
{
@@ -351,8 +352,11 @@
{
RowMapper rowMapper = new ColumnMapRowMapper();
Object[] selectParams = ArrayUtils.subarray(values.toArray(), 0, selectNum);
- List selectResult = jdbcTemplate.query(selectStatement, selectParams, rowMapper);
- Map<String, Object> fetchedColumns = (Map<String, Object>) selectResult.get(0);
+ List<Map<String, Object>> selectResult = jdbcTemplate.query(
+ selectStatement,
+ selectParams,
+ rowMapper);
+ Map<String, Object> fetchedColumns = selectResult.get(0);
int i = 0;
boolean updateNeeded = false;
for (String columnName : columns)
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericConditionalTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericConditionalTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,35 +15,35 @@
*/
package it.openutils.migration.task.setup;
-import javax.sql.DataSource;
+import java.util.HashMap;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
/**
+ * A siple update task that executes a query and apply a DDL only if the query retuns a certain value (by default
+ * <code>0</code>)
* @author fgiust
* @version $Id$
*/
-public class GenericConditionalTask extends BaseDbTask implements DbTask
+public class GenericConditionalTask extends BaseConditionalTask
{
- private String check;
+ /**
+ * Query for the check condition.
+ */
+ protected String check;
- private String ddl;
-
/**
- * {@inheritDoc}
+ * The value that will let the ddl script start. Default is <code>0</code>.
*/
- public void setDdl(String ddls)
- {
- this.ddl = ddls;
- }
+ protected Integer triggerValue = 0;
/**
* {@inheritDoc}
*/
- public void setCheck(String name)
+ public final void setCheck(String name)
{
this.check = name;
}
@@ -58,24 +58,61 @@
}
/**
+ * Sets the triggerValue.
+ * @param triggerValue the triggerValue to set
+ */
+ public final void setTriggerValue(Integer triggerValue)
+ {
+ this.triggerValue = triggerValue;
+ }
+
+ /**
* {@inheritDoc}
*/
- public void execute(DataSource dataSource)
+ @Override
+ public String getDescription()
{
- SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+ String supDesc = super.getDescription();
+ if (StringUtils.isNotEmpty(supDesc) && !StringUtils.equals(supDesc, getClass().getName()))
+ {
+ return super.getDescription();
+ }
- int result = jdbcTemplate.queryForInt(getCheck());
- if (result == 0)
+ if (StringUtils.isNotBlank(getCheck()))
{
- String[] ddls = StringUtils.split(ddl, ';');
- for (String statement : ddls)
- {
- if (StringUtils.isNotBlank(statement))
- {
- jdbcTemplate.update(statement);
- }
- }
+ return "Checking alter task condition: " + getCheck();
}
+
+ return getClass().getName();
}
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public boolean check(SimpleJdbcTemplate jdbcTemplate)
+ {
+ int result = jdbcTemplate.queryForInt(performSubstitution(getCheck()));
+ return result == triggerValue;
+ }
+
+ /**
+ * Sets the sourceDb.
+ * @param sourceDb the sourceDb to set
+ * @deprecated
+ */
+ @Deprecated
+ public final void setSourceDb(String sourceDb)
+ {
+ log.warn("sourceDb in "
+ + getClass().getName()
+ + " is deprecated, please use the more generic \"variables\" property");
+
+ if (this.variables == null)
+ {
+ variables = new HashMap<String, String>(1);
+ }
+ variables.put("sourceDb", sourceDb);
+ }
+
}
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericScriptBasedConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericScriptBasedConditionalTask.java 2008-01-27 11:23:33 UTC (rev 567)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/GenericScriptBasedConditionalTask.java 2008-01-27 15:25:47 UTC (rev 568)
@@ -15,163 +15,13 @@
*/
package it.openutils.migration.task.setup;
-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 fgiust
* @version $Id$
+ * @deprecated use GenericConditionalTask
*/
-public class GenericScriptBasedConditionalTask implements DbTask
+@Deprecated
+public class GenericScriptBasedConditionalTask extends GenericConditionalTask
{
- /**
- * Logger.
- */
- private static Logger log = LoggerFactory.getLogger(GenericScriptBasedConditionalTask.class);
-
- /**
- * Script list to execute
- */
- protected List<Resource> scripts;
-
- /**
- * Check statement.
- */
- protected String check;
-
- /**
- * Custom description.
- */
- protected String description;
-
- /**
- * Sets the scripts.
- * @param scripts the scripts to set
- */
- public void setScripts(List<Resource> scripts)
- {
- this.scripts = scripts;
- }
-
- /**
- * Sets the check.
- * @param check the check to set
- */
- public void setCheck(String check)
- {
- this.check = check;
- }
-
- /**
- * Sets the description.
- * @param description the description to set
- */
- public void setDescription(String description)
- {
- this.description = description;
- }
-
- /**
- * @param script The script resource
- * @return The script name
- */
- protected String objectNameFromFileName(Resource script)
- {
- return StringUtils.substringBeforeLast(script.getFilename(), ".");
- }
-
- /**
- * {@inheritDoc}
- */
- public String getDescription()
- {
- if (StringUtils.isNotEmpty(description))
- {
- return description;
- }
-
- if (scripts == null)
- {
- return "Nothing to do, no scripts configured";
- }
-
- StringBuffer result = new StringBuffer();
- if (!scripts.isEmpty())
- {
- result.append("Checking :\n");
- for (Resource script : scripts)
- {
- result.append(" - " + objectNameFromFileName(script) + "\n");
- }
-
- }
- return result.toString();
- }
-
- /**
- * {@inheritDoc}
- */
- public void execute(DataSource dataSource)
- {
-
- SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
-
- for (Resource script : scripts)
- {
- int result = jdbcTemplate.queryForInt(check, this.objectNameFromFileName(script));
- 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))
- {
- log.debug("Executing:\n{}", ddl);
- jdbcTemplate.update(ddl);
- }
- }
- }
- }
-
- }
}
Added: trunk/openutils-dbmigration/src/site/apt/guide.apt
===================================================================
--- trunk/openutils-dbmigration/src/site/apt/guide.apt (rev 0)
+++ trunk/openutils-dbmigration/src/site/apt/guide.apt 2008-01-27 15:25:47 UTC (rev 568)
@@ -0,0 +1,310 @@
+ --------------------------
+ O p e n u t i l s
+ --------------------------
+ Fabrizio Giustina
+ --------------------------
+
+how to
+
+
+ Dbmigration è configurato in un file xml che trovate sotto src/main/resources dei progetti db
+ In questo file xml trovate le configurazioni base più una lista di task, che è la parte che dovrete andare ad editare.
+
++-------------------------------------+
+ ....
+ <property name="setupTasks">
+ <list>
+ <bean class="it.openutils.dbmigration.SqlServerTableCreationTask">
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/tables/BILLING_ADDRESS.sql</value>
+ </list>
+ </property>
+ </bean>
+ <bean class="it.openutils.dbmigration.SqlServerTableCreationTask">
+ ...
+ </bean>
+ </list>
++-------------------------------------+
+
+
+ Tutti i bean che sono presenti in questa lista implementano un'interfaccia DbTask che non fa nient'altro che definire l'esecuzione di un qualcosa su db. Partendo da questo sono stati creati una serie di task specifici per le nostre esigenze (e questo vuol dire che possono essere scritti altri task o modificati quelli attuale al momento in cui emerge qualche altra esigenza).
+
+ == I task fondamentali: creazione tabella ==
+
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerTableCreationTask">
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/tables/BILLING_ADDRESS.sql</value>
+ <value>classpath:/dbmigration/tables/CUSTOMER.sql</value>
+ </list>
+ </property>
+ </bean>
++-------------------------------------+
+
+ Il task '''it.openutils.dbmigration.SqlServerTableCreationTask''' si occupa della creazione di tabelle. Viene configurato un singolo creation task per ogni db e questo contiene l'elenco di tutte le tabelle che ci servono per quel db.
+ Gli script di creazione delle tabelle vanno messi in un file sql separato nel classpath (in sottodirectory dentro a src/main/resources/dbmigration*) e richiamati con la sintassi che vedete nell'esempio.
+ Quando il task parte utilizza il nome del file per verificare se sullo schema del db esiste già una tabella con il nome del file indicato. Se e solo se non esiste la tabella viene creata lanciando lo script relativo.
+
+ Importante:
+ * negli script non ci devono essere drop, vengono comunque lanciati solo se la tabella non esiste già.
+ * questi script devono essere sempre mantenuti aggiornati quando modificate le tabelle.
+ * l'ordine degli script '''conta'''. In generale gli oggetti che hanno dipendenze da altri oggetti del database devono trovarsi dopo quelli da cui dipendono, all'interno della lista indicata nel file xml. Può essere anche necessario creare più task per rispettare l'ordine corretto tra oggetti diversi.
+
+ == I task fondamentali: creazione altri oggetti del db ==
+
+ Può essere necessario creare anche altri oggetti nel database, e sono già disponibili una serie di script per gli oggetti fondamentali, elencati qui di seguito. Valgono le stesse regole usate per le tabelle: lo script controlla prima l'esistenza di un oggetto nel database con il nome uguale a quello del file dello script (case sensitive!), e se manca esegue lo script corrispondente.
+
+ Per tutti i seguenti script è possibile specificare un database di origine (sourceDb) e un database di destinazione (currentDb). Nel file xml spring sostituisce autamaticamente i placeholders con gli opportuni valori dei file di properties. Il bean si occupa successivamente di sostituire i placeholder ${sourceDb} e ${currentDb} '''all'interno dello script''' con i valori specificati, prima di eseguirlo.
+ Questo può servire in tutti i casi in cui serve referenziare tramite nomi fully qualified oggetti di database diversi all'interno degli script. Per il momento non è mai stato necessario referenziare più di due database contemporaneamente.
+
+ === Creazione di viste ===
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerViewCreationTask">
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/dbProdotto/views/TREE_LEVEL.sql</value>
+ <value>classpath:/dbmigration/dbProdotto/views/PROMO_DETTAGLI_V.sql</value>
+ </list>
+ </property>
+ </bean>
++-------------------------------------+
+
+ === Creazione di sinonimi ===
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerSynonymCreationTask">
+ <property name="sourceDb" value="${dbmigration.databaseName.dbProdotto}" />
+ <property name="currentDb" value="${dbmigration.databaseName.dbWeb}" />
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/dbWeb/synonyms/BRANI.sql</value>
+ <value>classpath:/dbmigration/dbWeb/synonyms/IVA.sql</value>
+ <value>classpath:/dbmigration/dbWeb/synonyms/LINGUA.sql</value>
+ <value>classpath:/dbmigration/dbWeb/synonyms/PAESE.sql</value>
+ <value>classpath:/dbmigration/dbWeb/synonyms/PERSONE.sql</value>
+ <value>classpath:/dbmigration/dbWeb/synonyms/RUOLO.sql</value>
+ </list>
+ </property>
+ </bean>
++-------------------------------------+
+
+ Per gli script dei sinonimi è '''necessario''' specificare il db in cui si trova l'oggetto di cui verrà creato il sinonimo (sourceDb), e il db di "destinazione" in cui verrà creato il sinonimo (currentDb). Nel file xml spring sostituisce autamaticamente i placeholders con gli opportuni valori dei file di properties. Il bean si occupa successivamente di sostituire i placeholder ${sourceDb} e ${currentDb} '''all'interno dello script''' con i valori specificati, prima di eseguirlo.
+ I sinonimi non possono avere dipendenze tra loro e di conseguenza l'ordine all'interno di questa lista è irrilevante, ma è l'unico caso.
+
+ === Creazione di trigger ===
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerTriggerCreationTask">
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/dbProdotto/triggers/IVA_INSERT.sql</value>
+ <value>classpath:/dbmigration/dbProdotto/triggers/IVA_UPDATE.sql</value>
+ </list>
+ </property>
+ </bean>
++-------------------------------------+
+
+ === Creazione di funzioni ===
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerFunctionCreationTask">
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/dbProdotto/functions/fn_recupera_persone.sql</value>
+ <value>classpath:/dbmigration/dbProdotto/functions/FN_Calc_Isbn_From_EAN.sql</value>
+ </list>
+ </property>
+ </bean>
++-------------------------------------+
+
+ == I task fondamentali: alter di una tabella ==
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerGenericAlterTask">
+ <property name="currentDb" value="${dbmigration.databaseName.dbUsers}" />
+ <property name="triggerValue" value="0" />
+ <property name="check">
+ <value>
+ <![CDATA[SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='SHIPPING_ADDRESS' AND COLUMN_NAME ='NOTES']]>
+ </value>
+ </property>
+ <property name="ddl">
+ <value>ALTER TABLE [dbo].[SHIPPING_ADDRESS] ADD [NOTES] varchar(400) NULL;</value>
+ </property>
+ </bean>
++-------------------------------------+
+
+ Il principio fondamentale di questo task è semplice: quando faccio una modifica ad una tabella verifico se tale modifica è già presente su db e se non è così la applico.
+ Questo task lavora esattamente così:
+ * esegue una query di check su db
+ * se il risultato di questa query è uguale ad un valore definito (triggerValue)...
+ * ... viene eseguito lo script per applicare l'alter
+
+ La logica deve essere la più semplice possibile: SE una colonna manca ALLORA creo la colonna. Questo sistema è risultato già in passato molto più semplice ed efficace rispetto ad utilizzare numeri di versione nel db etc.
+
+ Anche in questo caso è possibile (ma facoltativo) indicare i parametri sourceDb e currentDb, visto che il task è di alter generico e può riguardare qualsiasi oggetto del database. I placeholder ${sourceDb} e ${currentDb} all'interno del file di script verrà sostituito prima dell'esecuzione con il valore indicato. ${currentDb} per default è il database raggiungibile come default secondo il DataSource di spring in uso per il task, quindi non è praticamente mai necessario specificarlo.
+
+ È opportuno segnalare che è possibile utilizzare degli script sql al posto delle istruzioni scritte direttamente all'interno del file xml, specialmente se la parte di alter è piuttosto corposa. Per indicare gli script basta utilizzare le properties checkScript e ddlScript:
+ <pre>
+ <property name="checkScript">
+ <value>classpath:/dbmigration/dbProdotto/alter/checkAlter.sql</value>
+ </property>
+ <property name="ddlScript">
+ <value>classpath:/dbmigration/dbProdotto/alter/ddlAlter.sql</value>
+ </property>
+ </pre>
+ Si può indicare indifferentemente una condizione di check nell'xml e uno script per l'alter, o anche viceversa. Tuttavia quando si specifica una condizione direttamente nell'xml questa ha la precedenza sull'eventuale script, che viene pertanto ignorato.
+ In ogni caso, sia nell'xml che nello script, '''non''' è possibile eseguire istruzioni come IF EXIST. Anche per questo motivo esiste questo sistema di script condizionali.
+
+ == Esempio: se aggiungo una colonna ==
+
+
+ Partiamo dall'esempio di avere creato una tabella su db con questo task:
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerTableCreationTask">
+ <property name="scripts">
+ <list>
+ <value>classpath:/dbmigration/tables/PIPPO.sql</value>
+ </list>
+ </property>
+ </bean>
+
+
+
+
+ PIPPO.sql:
+ CREATE TABLE dbo.PIPPO (
+ ID_PIPPO numeric(19,0) IDENTITY(1,1) NOT NULL,
+ TEST varchar(1000) NULL)
+ ;
+
++-------------------------------------+
+
+
+ Se ad un certo punto avete le necessità di aggiungere una colonna NEWCOL, visto che questa aggiunta dovrà essere trasparente sia per chi ha già la vecchia tabella su db sia per chi parte con un nuovo db dovete fare le seguenti cose:
+
+ * Modificare lo script iniziale:
+
++-------------------------------------+
+ PIPPO.sql:
+ CREATE TABLE dbo.PIPPO (
+ ID_PIPPO numeric(19,0) IDENTITY(1,1) NOT NULL,
+ TEST varchar(1000) NULL),
+ NEWCOL varchar(1000) NULL)
+ ;
+
++-------------------------------------+
+
+
+ * Aggiungere un task di alter:
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerGenericAlterTask">
+ <property name="currentDb" value="${dbmigration.databaseName.dbUsers}" />
+ <property name="triggerValue" value="0" />
+ <property name="check">
+ <value>
+ SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='PIPPO' AND COLUMN_NAME ='NEWCOL'
+ </value>
+ </property>
+ <property name="ddl">
+ <value>ALTER TABLE [dbo].[PIPPO] ADD [NEWCOL] varchar(1000) NULL;</value>
+ </property>
+ </bean>
++-------------------------------------+
+
+ Questo task esegue la query:
+
++-------------------------------------+
+ <property name="check">
+ <value>
+ SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='PIPPO' AND COLUMN_NAME ='NEWCOL'
+ </value>
+ </property>
++-------------------------------------+
+
+ che verifica se la colonna NEWCOL esiste già sullo schema interrogando le tabelle di sistema di sql server.
+
+ Se il risultato matcha quello configurato in
+
++-------------------------------------+
+ <property name="triggerValue" value="0" />
++-------------------------------------+
+
+ e cioè in questo caso se il count torna 0 e quindi la colonna non esiste viene applicato l'alter:
+
++-------------------------------------+
+ <property name="ddl">
+ <value>ALTER TABLE [dbo].[PIPPO] ADD [NEWCOL] varchar(1000) NULL;</value>
+ </property>
++-------------------------------------+
+
+
+ That's all, dovrebbe essere semplice.
+
+ == Esempio: aggiunta di un constraint ==
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerGenericAlterTask">
+ <property name="currentDb" value="${dbmigration.databaseName.dbUsers}" />
+ <property name="triggerValue" value="0" />
+ <property name="check">
+ <value>
+ <![CDATA[SELECT count(*) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='CUSTOMER' AND COLUMN_NAME ='USERNAME';]]>
+ </value>
+ </property>
+ <property name="ddl">
+ <value><![CDATA[ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [UNIQUE_CUSTOMER_USERNAME] UNIQUE ([USERNAME])]]></value>
+ </property>
+ </bean>
++-------------------------------------+
+
+ == Esempio: Modifica della lunghezza di un campo esistente ==
+
++-------------------------------------+
+ <bean class="it.openutils.dbmigration.SqlServerGenericAlterTask">
+ <property name="currentDb" value="${dbmigration.databaseName.dbUsers}" />
+ <property name="triggerValue" value="1" />
+ <property name="check">
+ <value>
+ <![CDATA[select count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='BILLING_ADDRESS' AND COLUMN_NAME ='NAME' and character_maximum_length = 200]]>
+ </value>
+ </property>
+ <property name="ddl">
+ <value>
+ <![CDATA[
+ ALTER TABLE [dbo].[BILLING_ADDRESS] ALTER COLUMN [NAME] varchar(100) NULL;
+ ]]>
+ </value>
+ </property>
+ </bean>
++-------------------------------------+
+
+ == Esempio: query utili ==
+
+ Alcune query utili nelle condizioni di check:
+ * Verifica esistenza di una funzione
+ select count(*) from dbo.sysobjects where id = object_id('NOME_FUNZIONE') and xtype in (N'FN', N'IF', N'TF')
+ * Verifica esistenza di un sinonimo
+ select count(*) from dbo.sysobjects where id = object_id('NOME_SINONIMO') and xtype = N'SN'
+ * Verifica esistenza di una tabella
+ select count(*) from dbo.sysobjects where id = object_id('NOME_TABELLA') and OBJECTPROPERTY(id, N'IsUserTable') = 1
+ * Verifica esistenza di un trigger
+ select count(*) from dbo.sysobjects where id = object_id('NOME_TRIGGER') and OBJECTPROPERTY(id, N'IsTrigger') = 1
+ * Verifica esistenza di una vista
+ select count(*) from dbo.sysobjects where id = object_id('NOME_VISTA') and OBJECTPROPERTY(id, N'IsView') = 1
+ * Verifica esistenza di una colonna in una tabella
+ select count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='NOME_TABELLA' AND COLUMN_NAME ='NOME_COLONNA'
+ * Verifica se una colonna amme...
[truncated message content] |
