Menu
▾
▴
openutils-svn
|
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] |
|
From: <fg...@us...> - 2008-02-11 20:47:50
|
Revision: 611
http://openutils.svn.sourceforge.net/openutils/?rev=611&view=rev
Author: fgiust
Date: 2008-02-11 12:47:48 -0800 (Mon, 11 Feb 2008)
Log Message:
-----------
ready for 2.0.2
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
Added Paths:
-----------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
trunk/openutils-dbmigration/src/site/changes/
trunk/openutils-dbmigration/src/site/changes/changes.xml
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-02-11 20:42:17 UTC (rev 610)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java 2008-02-11 20:47:48 UTC (rev 611)
@@ -22,7 +22,29 @@
private String column;
+ protected String catalog;
+
+ protected String schema;
+
/**
+ * Sets the catalog.
+ * @param catalog the catalog to set
+ */
+ public void setCatalog(String catalog)
+ {
+ this.catalog = catalog;
+ }
+
+ /**
+ * Sets the schema.
+ * @param schema the schema to set
+ */
+ public void setSchema(String schema)
+ {
+ this.schema = schema;
+ }
+
+ /**
* Sets the column.
* @param column the column to set
*/
@@ -37,8 +59,6 @@
@Override
public boolean check(SimpleJdbcTemplate jdbcTemplate)
{
- final String catalog = null;
- final String schema = null;
String columnTrim = StringUtils.trim(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-02-11 20:42:17 UTC (rev 610)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java 2008-02-11 20:47:48 UTC (rev 611)
@@ -1,3 +1,4 @@
+// temporary patch until a new openutils-dbmigration release is outF
/*
* Copyright Openmind http://www.openmindonline.it
*
@@ -44,9 +45,29 @@
protected String catalog;
+ protected String schema;
+
abstract String getObjectType();
/**
+ * Sets the catalog.
+ * @param catalog the catalog to set
+ */
+ public void setCatalog(String catalog)
+ {
+ this.catalog = catalog;
+ }
+
+ /**
+ * Sets the schema.
+ * @param schema the schema to set
+ */
+ public void setSchema(String schema)
+ {
+ this.schema = schema;
+ }
+
+ /**
* {@inheritDoc}
*/
@Override
@@ -65,7 +86,7 @@
String fqTableName = this.objectNameFromFileName(script);
String tmptableName = null;
- String tmpschema = null;
+ String tmpschema = schema;
if (StringUtils.contains(fqTableName, "."))
{
@@ -88,7 +109,7 @@
{
DatabaseMetaData dbMetadata = con.getMetaData();
- ResultSet rs = dbMetadata.getTables(catalog, schema, tableName, new String[]{getObjectType() });
+ ResultSet rs = dbMetadata.getTables(catalog, schema, tableName, new String[]{getObjectType()});
boolean tableExists = rs.next();
rs.close();
@@ -132,6 +153,10 @@
}
}
}
+ else
+ {
+ log.debug("{} {} already existing", getObjectType(), tableName);
+ }
}
}
}
\ No newline at end of file
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-02-11 20:47:48 UTC (rev 611)
@@ -0,0 +1,134 @@
+/*
+ * 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.oracle;
+
+import it.openutils.migration.task.setup.DbTask;
+
+import java.text.MessageFormat;
+import java.util.List;
+
+import javax.sql.DataSource;
+
+import org.apache.commons.lang.StringUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+/**
+ * Db tasks that handles the initial setup of sequences.
+ * @author fgiust
+ * @version $Id$
+ */
+public class OracleSequenceCreationTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(OracleSequenceCreationTask.class);
+
+ private List<String> sequences;
+
+ private String creationQuery;
+
+ private String selectUserSequences;
+
+ private String selectAllSequences;
+
+ private int startsWith;
+
+ /**
+ * Sets the sequences.
+ * @param sequences the sequences to set
+ */
+ public void setSequences(List<String> sequences)
+ {
+ this.sequences = sequences;
+ }
+
+ /**
+ * Sets the creationQuery.
+ * @param creationQuery the creationQuery to set
+ */
+ public void setCreationQuery(String creationQuery)
+ {
+ this.creationQuery = creationQuery;
+ }
+
+ /**
+ * Sets the selectAllSequences.
+ * @param selectAllSequences the selectAllSequences to set
+ */
+ public void setSelectAllSequences(String selectAllSequences)
+ {
+ this.selectAllSequences = selectAllSequences;
+ }
+
+ /**
+ * Sets the startsWith.
+ * @param startsWith the startsWith to set
+ */
+ public void setStartsWith(int startsWith)
+ {
+ this.startsWith = startsWith;
+ }
+
+ /**
+ * Sets the selectUserSequences.
+ * @param selectUserSequences the selectUserSequences to set
+ */
+ public void setSelectUserSequences(String selectUserSequences)
+ {
+ this.selectUserSequences = selectUserSequences;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public String getDescription()
+ {
+ return "Checking Sequences";
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+ for (String sequenceName : sequences)
+ {
+ int result = 0;
+
+ if (StringUtils.contains(sequenceName, "."))
+ {
+ String[] tokens = StringUtils.split(sequenceName, ".");
+ result = jdbcTemplate.queryForInt(selectAllSequences, new Object[]{tokens[1], tokens[0]});
+ }
+ else
+ {
+ result = jdbcTemplate.queryForInt(selectUserSequences, sequenceName);
+ }
+
+ if (result <= 0)
+ {
+ log.info("Creating new {}", sequenceName);
+ jdbcTemplate.update(MessageFormat.format(creationQuery, new Object[]{sequenceName, startsWith}));
+ }
+ }
+ }
+}
\ No newline at end of file
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Author Date Id Revision
Name: svn:eol-style
+ native
Added: trunk/openutils-dbmigration/src/site/changes/changes.xml
===================================================================
--- trunk/openutils-dbmigration/src/site/changes/changes.xml (rev 0)
+++ trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-02-11 20:47:48 UTC (rev 611)
@@ -0,0 +1,19 @@
+<?xml version="1.0"?>
+<!--
+ "type" attribute can be: add, remove, update or fix.
+-->
+<document>
+ <properties>
+ <title>Changes</title>
+ <author email="fgiust(at)users.sourceforge.net">Fabrizio Giustina</author>
+ </properties>
+ <body>
+ <release version="2.0.2" date="2008-02-11" description="2.0.2">
+ <action type="add" dev="fgiust">New it.openutils.migration.oracle.OracleSequenceCreationTask</action>
+ <action type="update" dev="fgiust">
+ Added ability to set a schema name in it.openutils.migration.generic.JdbcObjectCreationTask and
+ it.openutils.migration.generic.JdbcIfColumnExistsConditionalTask
+ </action>
+ </release>
+ </body>
+</document>
\ No newline at end of file
Property changes on: trunk/openutils-dbmigration/src/site/changes/changes.xml
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Author Date Id Revision
Name: svn:eol-style
+ native
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <fg...@us...> - 2008-02-21 21:01:16
|
Revision: 682
http://openutils.svn.sourceforge.net/openutils/?rev=682&view=rev
Author: fgiust
Date: 2008-02-21 13:00:56 -0800 (Thu, 21 Feb 2008)
Log Message:
-----------
ready for 2.0.3
Modified Paths:
--------------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
trunk/openutils-dbmigration/src/site/changes/changes.xml
Added Paths:
-----------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-02-21 14:57:33 UTC (rev 681)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-02-21 21:00:56 UTC (rev 682)
@@ -45,6 +45,8 @@
private String creationQuery;
+ private String dropQuery;
+
private String selectUserSequences;
private String selectAllSequences;
@@ -70,6 +72,15 @@
}
/**
+ * Sets the dropQuery.
+ * @param dropQuery the dropQuery to set
+ */
+ public void setDropQuery(String dropQuery)
+ {
+ this.dropQuery = dropQuery;
+ }
+
+ /**
* Sets the selectAllSequences.
* @param selectAllSequences the selectAllSequences to set
*/
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java 2008-02-21 21:00:56 UTC (rev 682)
@@ -0,0 +1,242 @@
+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;
+
+
+/**
+ * <p>
+ * Db tasks that handles the initial setup of views.
+ * </p>
+ * <p>
+ * Limitations:
+ * </p>
+ * <ol>
+ * <li> * not supported in field list</li>
+ * <li> fields must be enclosed in quotes</li>
+ * </ol>
+ * @author albertoq
+ * @version $Id$
+ */
+public class OracleViewCreateOrUpdateTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(OracleViewCreateOrUpdateTask.class);
+
+ /**
+ * Script list to execute
+ */
+ protected List<Resource> 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<Resource> getScripts()
+ {
+ return scripts;
+ }
+
+ public void setScripts(List<Resource> 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;
+ }
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.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/ExcelConfigurationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-02-21 14:57:33 UTC (rev 681)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-02-21 21:00:56 UTC (rev 682)
@@ -21,7 +21,10 @@
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
import java.util.ArrayList;
+import java.util.Iterator;
import java.util.List;
import java.util.Map;
@@ -65,12 +68,22 @@
private Map<String, ExcelConfigurationTask.QueryConfig> config;
/**
+ * Enable this task.
+ */
+ private boolean enabled = true;
+
+ /**
* If true, when a record already exists and an updated query is defined it will be updated. Set it to false to only
* insert new records.
*/
private boolean updateEnabled = true;
/**
+ * Date format for ISO dates
+ */
+ private SimpleDateFormat isodateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
+
+ /**
* Sets the script.
* @param script the script to set
*/
@@ -89,6 +102,15 @@
}
/**
+ * Sets the enabled.
+ * @param enabled the enabled to set
+ */
+ public void setEnabled(boolean enabled)
+ {
+ this.enabled = enabled;
+ }
+
+ /**
* Sets the updateEnabled.
* @param updateEnabled the updateEnabled to set
*/
@@ -102,6 +124,11 @@
*/
public void execute(DataSource dataSource)
{
+ if (!enabled)
+ {
+ return;
+ }
+
if (script == null || !script.exists())
{
log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
@@ -123,7 +150,7 @@
QueryConfig conf = config.get(tableName);
if (conf == null)
{
- log.error("Unable to handle table {}", tableName);
+ suggestSheetConfig(sheet, tableName, conf, dataSource);
continue;
}
processSheet(sheet, tableName, conf, dataSource);
@@ -142,6 +169,85 @@
}
+ public void suggestSheetConfig(HSSFSheet sheet, final String tableName, QueryConfig con, DataSource dataSource)
+ {
+ log.error("Unable to handle table {}", tableName);
+
+ if (!log.isDebugEnabled())
+ {
+ return;
+ }
+
+ final List<String> columns = new ArrayList<String>();
+
+ HSSFRow row = sheet.getRow(0);
+ for (short k = 0; k < row.getLastCellNum(); k++)
+ {
+ HSSFCell cell = row.getCell(k);
+ if (cell != null)
+ {
+ String columnName = cell.getStringCellValue();
+ if (StringUtils.isNotBlank(columnName))
+ {
+ columns.add(StringUtils.trim(columnName));
+ }
+ else
+ {
+ break;
+ }
+ }
+ }
+
+ if (columns.isEmpty())
+ {
+ return;
+ }
+
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(" <entry key=\"");
+ buffer.append(tableName);
+ buffer.append("\">\n"
+ + " <bean class=\"it.openutils.migration.task.setup.ExcelConfigurationTask$QueryConfig\">\n"
+ + " <property name=\"checkQuery\">\n"
+ + " <value>");
+
+ String initialCol = columns.get(0);
+ buffer.append("select count(" + initialCol + ") from " + tableName + " where " + initialCol + " = ?");
+ buffer.append("</value>\n"
+ + " </property>\n"
+ + " <property name=\"insertQuery\">\n"
+ + " <value>");
+
+ buffer.append("INSERT INTO ");
+ buffer.append(tableName);
+ buffer.append(" (");
+
+ StringBuffer colNames = new StringBuffer();
+ StringBuffer parNames = new StringBuffer();
+
+ for (Iterator<String> iterator = columns.iterator(); iterator.hasNext();)
+ {
+ String string = iterator.next();
+ colNames.append(string);
+ parNames.append("?");
+ if (iterator.hasNext())
+ {
+ colNames.append(", ");
+ parNames.append(", ");
+ }
+
+ }
+
+ buffer.append(colNames);
+ buffer.append(") VALUES (");
+ buffer.append(parNames);
+ buffer.append(")");
+ buffer.append("</value>\n" + " </property>\n" + " </bean>\n" + " </entry>");
+
+ log.debug("You can use the following suggested config as template:\n{}", buffer.toString());
+ }
+
/**
* @param sheet
* @param tableName
@@ -312,6 +418,40 @@
{
Object[] insertParams = ArrayUtils.subarray(values.toArray(), 0, insertNum);
int[] insertTypes = ArrayUtils.subarray(types, 0, insertNum);
+
+ // empty strings must be converted to nulls if the columns is numeric or date
+ // Cannot convert class java.lang.String to SQL type requested due to java.lang.NumberFormatException -
+ // For input string: ""
+ for (int j = 0; j < insertTypes.length; j++)
+ {
+ int tip = insertTypes[j];
+ if (tip != Types.CHAR
+ && tip != Types.LONGNVARCHAR
+ && tip != Types.LONGVARCHAR
+ && tip != Types.NCHAR
+ && tip != Types.NVARCHAR
+ && tip != Types.VARCHAR
+ && "".equals(insertParams[j]))
+ {
+ insertParams[j] = null;
+ }
+
+ if (tip == Types.DATE || tip == Types.TIME || tip == Types.TIMESTAMP && insertParams[j] != null)
+ {
+ synchronized (isodateformat)
+ {
+ try
+ {
+ insertParams[j] = isodateformat.parse((String) insertParams[j]);
+ }
+ catch (ParseException e)
+ {
+ log.debug("Cannot parse date \"{}\"", insertParams[j]);
+ }
+ }
+ }
+ }
+
if (log.isDebugEnabled())
{
log.debug("Missing record with key {}; inserting {}", ArrayUtils.toString(checkParams), ArrayUtils
@@ -323,7 +463,7 @@
log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
tableName,
insertParams.length,
- insertTypes.length });
+ insertTypes.length});
}
try
@@ -340,7 +480,7 @@
rowNum + 1,
insertStatement,
ArrayUtils.toString(insertParams),
- bsge.getMessage() });
+ bsge.getMessage()});
continue;
}
}
@@ -391,7 +531,7 @@
log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
tableName,
updateParams.length,
- insertTypes.length });
+ insertTypes.length});
}
try
@@ -416,7 +556,7 @@
rowNum + 1,
insertStatement,
ArrayUtils.toString(updateParams),
- bsge.getMessage() });
+ bsge.getMessage()});
continue;
}
}
Modified: trunk/openutils-dbmigration/src/site/changes/changes.xml
===================================================================
--- trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-02-21 14:57:33 UTC (rev 681)
+++ trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-02-21 21:00:56 UTC (rev 682)
@@ -8,6 +8,15 @@
<author email="fgiust(at)users.sourceforge.net">Fabrizio Giustina</author>
</properties>
<body>
+ <release version="2.0.3" date="2008-02-21" description="2.0.3">
+ <action type="add" dev="fgiust">New it.openutils.migration.oracle.OracleViewCreateOrUpdateTask</action>
+ <action type="fix" dev="fgiust">Fix handling of empty strings for numeric types in ExcelConfigurationTask</action>
+ <action type="update" dev="fgiust">
+ Dates are now always processed using an ISO8601 date format in ExcelConfigurationTask (previously the parsing
+ was left to the jdbc driver, leading to different results with different drivers)
+ </action>
+ <action type="add" dev="fgiust">Added a new "enabled" flag in ExcelConfigurationTask</action>
+ </release>
<release version="2.0.2" date="2008-02-11" description="2.0.2">
<action type="add" dev="fgiust">New it.openutils.migration.oracle.OracleSequenceCreationTask</action>
<action type="update" dev="fgiust">
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <fg...@us...> - 2008-04-27 14:24:37
|
Revision: 777
http://openutils.svn.sourceforge.net/openutils/?rev=777&view=rev
Author: fgiust
Date: 2008-04-27 07:24:19 -0700 (Sun, 27 Apr 2008)
Log Message:
-----------
a bunch of new tasks
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/oracle/OracleSequenceCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java
trunk/openutils-dbmigration/src/site/changes/changes.xml
Added Paths:
-----------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/IfColumnIsNotIdentityConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnIsNotNullableConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OraclePackageCreationTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleStoredProcedureCallTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleTriggerTask.java
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/IfColumnIsNotIdentityConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/IfColumnIsNotIdentityConditionalTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/IfColumnIsNotIdentityConditionalTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,39 @@
+/*
+ * 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 java.util.Map;
+
+
+/**
+ * Tasks that check if a given column is an identity (IS_AUTOINCREMENT = YES).
+ * @author fgiust
+ * @version $Id$
+ */
+public class IfColumnIsNotIdentityConditionalTask extends JdbcColumnBasedConditionalTask
+{
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ protected boolean checkColumnMetadata(Map<String, Object> metadata)
+ {
+
+ String isAutoincrement = (String) metadata.get("IS_AUTOINCREMENT");
+ return !"YES".equals(isAutoincrement);
+ }
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/IfColumnIsNotIdentityConditionalTask.java
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Author Date Id Revision
Name: svn:eol-style
+ native
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,178 @@
+/*
+ * 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.BaseConditionalTask;
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.Map;
+
+import org.apache.commons.lang.StringUtils;
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.core.ConnectionCallback;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+/**
+ * Base conditional task that operates on conditions related to a specific column. This base task takes care of
+ * retrieving the column metadata, so that subclasses only need to override <code>checkColumnMetadata()</code>.
+ * @author fgiust
+ * @version $Id$
+ */
+public abstract class JdbcColumnBasedConditionalTask extends BaseConditionalTask
+{
+
+ /**
+ * Column name
+ */
+ protected String column;
+
+ /**
+ * Catalog name
+ */
+ protected String catalog;
+
+ /**
+ * Schema name
+ */
+ protected String schema;
+
+ /**
+ * Sets the catalog.
+ * @param catalog the catalog to set
+ */
+ public void setCatalog(String catalog)
+ {
+ this.catalog = catalog;
+ }
+
+ /**
+ * Sets the schema.
+ * @param schema the schema to set
+ */
+ public void setSchema(String schema)
+ {
+ this.schema = schema;
+ }
+
+ /**
+ * Sets the column.
+ * @param column the column to set
+ */
+ public void setColumn(String column)
+ {
+ this.column = column;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public boolean check(SimpleJdbcTemplate jdbcTemplate)
+ {
+
+ String columnTrim = StringUtils.trim(column);
+
+ final String tableName = StringUtils.substringBefore(columnTrim, ".");
+ final String columnName = StringUtils.substringAfter(columnTrim, ".");
+ return (Boolean) jdbcTemplate.getJdbcOperations().execute(new ConnectionCallback()
+ {
+
+ public Object doInConnection(Connection con) throws SQLException, DataAccessException
+ {
+ DatabaseMetaData dbMetadata = con.getMetaData();
+ ResultSet rs = dbMetadata.getColumns(catalog, schema, tableName, columnName);
+ boolean conditionMet = rs.next();
+ if (conditionMet)
+ {
+ ResultSetMetaData rsmeta = rs.getMetaData();
+ int colcount = rsmeta.getColumnCount();
+ Map<String, Object> params = new HashMap<String, Object>();
+ for (int j = 1; j <= colcount; j++)
+ {
+ params.put(rsmeta.getColumnName(j), rs.getObject(j));
+ }
+ conditionMet = !checkColumnMetadata(params);
+ }
+ rs.close();
+ return conditionMet;
+ }
+ });
+ }
+
+ /**
+ * <p>
+ * Check if a specific condition is met depending on column metadata.
+ * </p>
+ * Column attributes included in the Map are:
+ * <ol>
+ * <li><strong>TABLE_CAT</strong> String => table catalog (may be <code>null</code>)</li>
+ * <li><strong>TABLE_SCHEM</strong> String => table schema (may be <code>null</code>)</li>
+ * <li><strong>TABLE_NAME</strong> String => table name</li>
+ * <li><strong>COLUMN_NAME</strong> String => column name</li>
+ * <li><strong>DATA_TYPE</strong> int => SQL type from java.sql.Types</li>
+ * <li><strong>TYPE_NAME</strong> String => Data source dependent type name, for a UDT the type name is fully
+ * qualified</li>
+ * <li><strong>COLUMN_SIZE</strong> int => column size.</li>
+ * <li><strong>BUFFER_LENGTH</strong> is not used.</li>
+ * <li><strong>DECIMAL_DIGITS</strong> int => the number of fractional digits. Null is returned for data types
+ * where DECIMAL_DIGITS is not applicable.</li>
+ * <li><strong>NUM_PREC_RADIX</strong> int => Radix (typically either 10 or 2)</li>
+ * <li><strong>NULLABLE</strong> int => is NULL allowed.</li>
+ * <ul>
+ * <li> columnNoNulls - might not allow <code>NULL</code> values</li>
+ * <li> columnNullable - definitely allows <code>NULL</code> values</li>
+ * <li> columnNullableUnknown - nullability unknown</li>
+ * </ul>
+ * <li><strong>REMARKS</strong> String => comment describing column (may be <code>null</code>)</li>
+ * <li><strong>COLUMN_DEF</strong> String => default value for the column, which should be interpreted as a string
+ * when the value is enclosed in single quotes (may be <code>null</code>)</li>
+ * <li><strong>SQL_DATA_TYPE</strong> int => unused</li>
+ * <li><strong>SQL_DATETIME_SUB</strong> int => unused</li>
+ * <li><strong>CHAR_OCTET_LENGTH</strong> int => for char types the maximum number of bytes in the column</li>
+ * <li><strong>ORDINAL_POSITION</strong> int => index of column in table (starting at 1)</li>
+ * <li><strong>IS_NULLABLE</strong> String => ISO rules are used to determine the nullability for a column.</li>
+ * <ul>
+ * <li> YES --- if the parameter can include NULLs</li>
+ * <li> NO --- if the parameter cannot include NULLs</li>
+ * <li> empty string --- if the nullability for the parameter is unknown</li>
+ * </ul>
+ * <li><strong>SCOPE_CATLOG</strong> String => catalog of table that is the scope of a reference attribute (<code>null</code>
+ * if DATA_TYPE isn't REF)</li>
+ * <li><strong>SCOPE_SCHEMA</strong> String => schema of table that is the scope of a reference attribute (<code>null</code>
+ * if the DATA_TYPE isn't REF)</li>
+ * <li><strong>SCOPE_TABLE</strong> String => table name that this the scope of a reference attribure (<code>null</code>
+ * if the DATA_TYPE isn't REF)</li>
+ * <li><strong>SOURCE_DATA_TYPE</strong> short => source type of a distinct type or user-generated Ref type, SQL
+ * type from java.sql.Types (<code>null</code> if DATA_TYPE isn't DISTINCT or user-generated REF)</li>
+ * <li><strong>IS_AUTOINCREMENT</strong> String => Indicates whether this column is auto incremented</li>
+ * <ul>
+ * <li> YES --- if the column is auto incremented</li>
+ * <li> NO --- if the column is not auto incremented</li>
+ * <li> empty string --- if it cannot be determined whether the column is auto incremented parameter is unknown</li>
+ * </ul>
+ * </ol>
+ * @param metadata column metadata
+ * @return <code>true</code> if the condition is met
+ */
+ protected abstract boolean checkColumnMetadata(Map<String, Object> metadata);
+
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.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/generic/JdbcIfColumnExistsConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java 2008-04-27 13:19:02 UTC (rev 776)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -1,3 +1,18 @@
+/*
+ * 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.BaseConditionalTask;
@@ -14,16 +29,26 @@
/**
+ * Task that executes if a given column exists.
* @author fgiust
* @version $Id: $
*/
public class JdbcIfColumnExistsConditionalTask extends BaseConditionalTask
{
- private String column;
+ /**
+ * Column name.
+ */
+ protected String column;
+ /**
+ * Catalog.
+ */
protected String catalog;
+ /**
+ * Schema.
+ */
protected String schema;
/**
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnIsNotNullableConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnIsNotNullableConditionalTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnIsNotNullableConditionalTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,39 @@
+/*
+ * 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 java.util.Map;
+
+
+/**
+ * Task that executes if a given column is not nullable (IS_NULLABLE = NO).
+ * @author fgiust
+ * @version $Id$
+ */
+public class JdbcIfColumnIsNotNullableConditionalTask extends JdbcColumnBasedConditionalTask
+{
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ protected boolean checkColumnMetadata(Map<String, Object> metadata)
+ {
+
+ String isNullable = (String) metadata.get("IS_NULLABLE");
+ return "NO".equals(isNullable);
+ }
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnIsNotNullableConditionalTask.java
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Author Date Id Revision
Name: svn:eol-style
+ native
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,105 @@
+/*
+ * 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.BaseConditionalTask;
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import org.apache.commons.lang.StringUtils;
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.core.ConnectionCallback;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+/**
+ * Task that executes if a named foreign key eists.
+ * @author fgiust
+ * @version $Id$
+ */
+public class JdbcIfForeignKeyExistsConditionalTask extends BaseConditionalTask
+{
+
+ private String fkName;
+
+ private String catalog;
+
+ private String schema;
+
+ /**
+ * Sets the catalog.
+ * @param catalog the catalog to set
+ */
+ public void setCatalog(String catalog)
+ {
+ this.catalog = catalog;
+ }
+
+ /**
+ * Sets the schema.
+ * @param schema the schema to set
+ */
+ public void setSchema(String schema)
+ {
+ this.schema = schema;
+ }
+
+ /**
+ * Sets the fkName (TABLE.FKNAME).
+ * @param fkName the fkName to set
+ */
+ public void setFkName(String fkName)
+ {
+ this.fkName = fkName;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public boolean check(SimpleJdbcTemplate jdbcTemplate)
+ {
+
+ String fkNameTrim = StringUtils.trim(fkName);
+
+ final String tableName = StringUtils.substringBefore(fkNameTrim, ".");
+ final String fkName = StringUtils.substringAfter(fkNameTrim, ".");
+ return (Boolean) jdbcTemplate.getJdbcOperations().execute(new ConnectionCallback()
+ {
+
+ public Object doInConnection(Connection con) throws SQLException, DataAccessException
+ {
+ boolean fkExists = false;
+ DatabaseMetaData dbMetadata = con.getMetaData();
+ ResultSet rs = dbMetadata.getExportedKeys(catalog, schema, tableName);
+ while (rs.next())
+ {
+ if (StringUtils.equals(fkName, rs.getString("FK_NAME")))
+ {
+ fkExists = true;
+ }
+ }
+ rs.close();
+
+ return !fkExists;
+ }
+ });
+ }
+
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.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/generic/JdbcObjectCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java 2008-04-27 13:19:02 UTC (rev 776)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcObjectCreationTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -1,4 +1,3 @@
-// temporary patch until a new openutils-dbmigration release is outF
/*
* Copyright Openmind http://www.openmindonline.it
*
@@ -43,8 +42,14 @@
public abstract class JdbcObjectCreationTask extends GenericConditionalTask
{
+ /**
+ * Catalog.
+ */
protected String catalog;
+ /**
+ * Schema.
+ */
protected String schema;
abstract String getObjectType();
@@ -109,7 +114,7 @@
{
DatabaseMetaData dbMetadata = con.getMetaData();
- ResultSet rs = dbMetadata.getTables(catalog, schema, tableName, new String[]{getObjectType()});
+ ResultSet rs = dbMetadata.getTables(catalog, schema, tableName, new String[]{getObjectType() });
boolean tableExists = rs.next();
rs.close();
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OraclePackageCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OraclePackageCreationTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OraclePackageCreationTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,213 @@
+/*
+ * 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.oracle;
+
+import it.openutils.migration.task.setup.DbTask;
+
+import java.io.IOException;
+import java.io.InputStream;
+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;
+
+
+/**
+ * Db tasks that handles the initial setup of packages.
+ * @author quario
+ * @version $Id$
+ */
+public class OraclePackageCreationTask implements DbTask
+{
+
+ private Logger log = LoggerFactory.getLogger(OraclePackageCreationTask.class);
+
+ private List<Resource> scripts;
+
+ private String selectUserPackages = "select COUNT(*) FROM USER_OBJECTS WHERE OBJECT_NAME = ? and OBJECT_TYPE='PACKAGE'";
+
+ private String selectAllPackages = "SELECT COUNT(*) from ALL_OBJECTS where OBJECT_NAME = ? AND OBJECT_TYPE='PACKAGE' AND OWNER = ?";
+
+ protected Map<String, String> variables;
+
+ /**
+ * Sets the packages.
+ * @param packages the packages to set
+ */
+ public void setScripts(List<Resource> packages)
+ {
+ this.scripts = packages;
+ }
+
+ /**
+ * Sets the selectAllPackages.
+ * @param selectAllPackages the selectAllPackages to set
+ */
+ public void setSelectAllPackages(String selectAllPackages)
+ {
+ this.selectAllPackages = selectAllPackages;
+ }
+
+ /**
+ * Sets the selectUserPackages.
+ * @param selectUserPackages the selectUserPackages to set
+ */
+ public void setSelectUserPackages(String selectUserPackages)
+ {
+ this.selectUserPackages = selectUserPackages;
+ }
+
+ /**
+ * Map of key-value that will be replaced in ddl.
+ */
+ public void setVariables(Map<String, String> variables)
+ {
+ this.variables = variables;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public String getDescription()
+ {
+ return "Checking Packages";
+ }
+
+ /**
+ * @param script The script resource
+ * @return The script name
+ */
+ protected String objectNameFromFileName(Resource script)
+ {
+ return StringUtils.substringBeforeLast(script.getFilename(), ".");
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+
+ for (Resource script : scripts)
+ {
+
+ if (script == null || !script.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
+ return;
+ }
+
+ String fqPackageName = this.objectNameFromFileName(script);
+ String tmpPackageName = null;
+ String tmpowner = null;
+
+ if (StringUtils.contains(fqPackageName, "."))
+ {
+ String[] tokens = StringUtils.split(fqPackageName, ".");
+ tmpPackageName = tokens[1];
+ tmpowner = tokens[0];
+ }
+ else
+ {
+ tmpPackageName = fqPackageName;
+ }
+
+ final String packageName = tmpPackageName;
+ final String owner = tmpowner;
+
+ int result = 0;
+
+ if (StringUtils.isNotBlank(owner))
+ {
+ result = jdbcTemplate.queryForInt(selectAllPackages, new Object[]{packageName, owner });
+ }
+ else
+ {
+ result = jdbcTemplate.queryForInt(selectUserPackages, packageName);
+ }
+
+ if (result <= 0)
+ {
+ String scriptContent;
+ InputStream is = null;
+
+ try
+ {
+ is = script.getInputStream();
+ scriptContent = IOUtils.toString(is, "UTF8");
+ scriptContent = scriptContent.replaceAll("\\s*\n\\s*", "\n");
+ }
+ catch (IOException e)
+ {
+ log.error(
+ "Unable to execute db task \"{}\", script \"{}\" can't be read.",
+ getDescription(),
+ script);
+ return;
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+
+ String[] scriptSections = scriptContent.split("/");
+ String packageHeader = performSubstitution(scriptSections[0].trim());
+ String packageBody = performSubstitution(scriptSections[1].trim());
+
+ if (StringUtils.isNotBlank(packageHeader) && StringUtils.isNotBlank(packageBody))
+ {
+ log.info("Creating new package header for {}", packageName);
+ jdbcTemplate.update(packageHeader);
+ log.info("Creating new package body for {}", packageName);
+ jdbcTemplate.update(packageBody);
+ }
+ }
+ else
+ {
+ log.debug("Package {} already existing", packageName);
+ }
+ }
+ }
+
+ /**
+ * 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;
+ }
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OraclePackageCreationTask.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/oracle/OracleSequenceCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-04-27 13:19:02 UTC (rev 776)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -43,16 +43,14 @@
private List<String> sequences;
- private String creationQuery;
+ private String creationQuery = "CREATE SEQUENCE {0} INCREMENT BY 1 START WITH {1} MAXVALUE 1E28 MINVALUE 1 NOCACHE NOCYCLE ORDER";
- private String dropQuery;
+ private String selectUserSequences = "SELECT COUNT(*) FROM USER_SEQUENCES WHERE SEQUENCE_NAME = ?";
- private String selectUserSequences;
+ private String selectAllSequences = "SELECT COUNT(*) FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = ? AND SEQUENCE_OWNER = ?";
- private String selectAllSequences;
+ private int startsWith = 1;
- private int startsWith;
-
/**
* Sets the sequences.
* @param sequences the sequences to set
@@ -72,15 +70,6 @@
}
/**
- * Sets the dropQuery.
- * @param dropQuery the dropQuery to set
- */
- public void setDropQuery(String dropQuery)
- {
- this.dropQuery = dropQuery;
- }
-
- /**
* Sets the selectAllSequences.
* @param selectAllSequences the selectAllSequences to set
*/
@@ -128,7 +117,7 @@
if (StringUtils.contains(sequenceName, "."))
{
String[] tokens = StringUtils.split(sequenceName, ".");
- result = jdbcTemplate.queryForInt(selectAllSequences, new Object[]{tokens[1], tokens[0]});
+ result = jdbcTemplate.queryForInt(selectAllSequences, new Object[]{tokens[1], tokens[0] });
}
else
{
@@ -138,7 +127,9 @@
if (result <= 0)
{
log.info("Creating new {}", sequenceName);
- jdbcTemplate.update(MessageFormat.format(creationQuery, new Object[]{sequenceName, startsWith}));
+ jdbcTemplate.update(MessageFormat.format(creationQuery, new Object[]{
+ sequenceName,
+ String.valueOf(startsWith) }));
}
}
}
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleStoredProcedureCallTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleStoredProcedureCallTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleStoredProcedureCallTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,62 @@
+/*
+ * 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.oracle;
+
+import it.openutils.migration.task.setup.GenericConditionalTask;
+
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.core.ConnectionCallback;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+import org.springframework.jdbc.support.JdbcUtils;
+
+
+public class OracleStoredProcedureCallTask extends GenericConditionalTask
+{
+ @Override
+ protected void executeSingle(SimpleJdbcTemplate jdbcTemplate, final String scriptContent)
+ {
+ jdbcTemplate.getJdbcOperations().execute(new ConnectionCallback()
+ {
+ public Object doInConnection(Connection con) throws SQLException, DataAccessException
+ {
+ CallableStatement cs = null;
+ try
+ {
+ cs = con.prepareCall("{call " + scriptContent.trim() + "}");
+ cs.execute();
+ }
+ finally
+ {
+ JdbcUtils.closeStatement(cs);
+ }
+ return null;
+ }
+ });
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public String getDescription()
+ {
+ return "calling stored procedures";
+ }
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleStoredProcedureCallTask.java
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Author Date Id Revision
Name: svn:eol-style
+ native
Added: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleTriggerTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleTriggerTask.java (rev 0)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleTriggerTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -0,0 +1,43 @@
+/*
+ * 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.oracle;
+
+import it.openutils.migration.task.setup.GenericConditionalTask;
+
+import org.apache.commons.lang.StringUtils;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+public class OracleTriggerTask extends GenericConditionalTask
+{
+ @Override
+ protected void executeSingle(SimpleJdbcTemplate jdbcTemplate, final String scriptContent)
+ {
+ if (StringUtils.isNotBlank(scriptContent))
+ {
+ jdbcTemplate.update(scriptContent.trim());
+ }
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ public String getDescription()
+ {
+ return "triggers";
+ }
+}
Property changes on: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleTriggerTask.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/oracle/OracleViewCreateOrUpdateTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java 2008-04-27 13:19:02 UTC (rev 776)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -1,3 +1,18 @@
+/*
+ * 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.oracle;
import it.openutils.migration.task.setup.DbTask;
@@ -44,7 +59,7 @@
/**
* Script list to execute
*/
- protected List<Resource> scripts;
+ private List<Resource> scripts;
/**
* Query to verify view existence
@@ -61,6 +76,31 @@
*/
private String dropView;
+ public String getDescription()
+ {
+ return "Checking Views";
+ }
+
+ public void setScripts(List<Resource> scripts)
+ {
+ this.scripts = scripts;
+ }
+
+ public void setSelectUserViewExistence(String selectUserViewExistence)
+ {
+ this.selectUserViewExistence = selectUserViewExistence;
+ }
+
+ public void setSelectUserViewDDL(String selectUserViewDDL)
+ {
+ this.selectUserViewDDL = selectUserViewDDL;
+ }
+
+ public void setDropView(String dropView)
+ {
+ this.dropView = dropView;
+ }
+
/**
* {@inheritDoc}
*/
@@ -72,7 +112,7 @@
{
String viewName = this.objectNameFromFileName(script);
- int result = jdbcTemplate.queryForInt(getSelectUserViewExistence(), viewName);
+ int result = jdbcTemplate.queryForInt(this.selectUserViewExistence, viewName);
String scriptContent = readFully(script);
@@ -96,17 +136,17 @@
}
String previousDDl = (String) jdbcTemplate.getJdbcOperations().queryForObject(
- getSelectUserViewDDL(),
- new Object[]{viewName},
+ this.selectUserViewDDL,
+ 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});
+ new Object[]{viewName });
- jdbcTemplate.update(MessageFormat.format(getDropView(), new Object[]{viewName}));
+ jdbcTemplate.update(MessageFormat.format(this.dropView, new Object[]{viewName }));
createView(jdbcTemplate, scriptContent);
}
@@ -116,21 +156,12 @@
}
/**
- * @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);
+ Pattern pattern = Pattern.compile(".*?\\s+AS\\s+(.*);", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
Matcher matcher = pattern.matcher(scriptContent);
boolean bodyFound = matcher.find();
if (bodyFound)
@@ -164,6 +195,15 @@
}
/**
+ * @param script The script resource
+ * @return The script name
+ */
+ protected String objectNameFromFileName(Resource script)
+ {
+ return StringUtils.substringBeforeLast(script.getFilename(), ".");
+ }
+
+ /**
* @param script
* @return
*/
@@ -195,48 +235,4 @@
return scriptContent;
}
- public String getDescription()
- {
- return "Checking Views";
- }
-
- public List<Resource> getScripts()
- {
- return scripts;
- }
-
- public void setScripts(List<Resource> 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;
- }
}
Modified: 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 2008-04-27 13:19:02 UTC (rev 776)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java 2008-04-27 14:24:19 UTC (rev 777)
@@ -224,7 +224,7 @@
* @param jdbcTemplate
* @param scriptContent
*/
- private void executeSingle(SimpleJdbcTemplate jdbcTemplate, String scriptContent)
+ protected void executeSingle(SimpleJdbcTemplate jdbcTemplate, String scriptContent)
{
String[] ddls = StringUtils.split(performSubstitution(scriptContent), ';');
for (String statement : ddls)
Modified: trunk/openutils-dbmigration/src/site/changes/changes.xml
===================================================================
--- trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-04-27 13:19:02 UTC (rev 776)
+++ trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-04-27 14:24:19 UTC (rev 777)
@@ -8,21 +8,26 @@
<author email="fgiust(at)users.sourceforge.net">Fabrizio Giustina</author>
</properties>
<body>
+ <release version="2.0.4" date="in svn" description="2.0.4">
+ <action type="add" dev="fgiust">New jdbc generic tasks: IfColumnIsNotIdentityConditionalTask,
+ JdbcIfColumnIsNotNullableConditionalTask, JdbcIfForeignKeyExistsConditionalTask</action>
+ <action type="add" dev="fgiust">New oracle specific tasks: OraclePackageCreationTask,
+ OracleStoredProcedureCallTask</action>
+ </release>
<release version="2.0.3" date="2008-02-21" description="2.0.3">
<action type="add" dev="fgiust">New it.openutils.migration.oracle.OracleViewCreateOrUpdateTask</action>
- <action type="fix" dev="fgiust">Fix handling of empty strings for numeric types in ExcelConfigurationTask</action>
- <action type="update" dev="fgiust">
- Dates are now always processed using an ISO8601 date format in ExcelConfigurationTask (previously the parsing
- was left to the jdbc driver, leading to different results with different drivers)
+ <action type="fix" dev="fgiust">Fix handling of empty strings for numeric types in ExcelConfigurationTask
</action>
+ <action type="update" dev="fgiust"> Dates are now always processed using an ISO8601 date format in
+ ExcelConfigurationTask (previously the parsing was left to the jdbc driver, leading to different results with
+ different drivers)</action>
<action type="add" dev="fgiust">Added a new "enabled" flag in ExcelConfigurationTask</action>
</release>
<release version="2.0.2" date="2008-02-11" description="2.0.2">
<action type="add" dev="fgiust">New it.openutils.migration.oracle.OracleSequenceCreationTask</action>
- <action type="update" dev="fgiust">
- Added ability to set a schema name in it.openutils.migration.generic.JdbcObjectCreationTask and
- it.openutils.migration.generic.JdbcIfColumnExistsConditionalTask
- </action>
+ <action type="update" dev="fgiust"> Added ability to set a schema name in
+ it.openutils.migration.generic.JdbcObjectCreationTask and
+ it.openutils.migration.generic.JdbcIfColumnExistsConditionalTask</action>
</release>
</body>
</document>
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <fg...@us...> - 2008-06-11 21:19:25
|
Revision: 834
http://openutils.svn.sourceforge.net/openutils/?rev=834&view=rev
Author: fgiust
Date: 2008-06-11 14:18:57 -0700 (Wed, 11 Jun 2008)
Log Message:
-----------
fixed wrong usage of "not" condition in BaseConditionalTask
Modified Paths:
--------------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java
trunk/openutils-dbmigration/src/site/changes/changes.xml
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java 2008-06-09 16:48:00 UTC (rev 833)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcColumnBasedConditionalTask.java 2008-06-11 21:18:57 UTC (rev 834)
@@ -110,7 +110,7 @@
{
params.put(rsmeta.getColumnName(j), rs.getObject(j));
}
- conditionMet = !checkColumnMetadata(params);
+ conditionMet = checkColumnMetadata(params);
}
rs.close();
return conditionMet;
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-06-09 16:48:00 UTC (rev 833)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfColumnExistsConditionalTask.java 2008-06-11 21:18:57 UTC (rev 834)
@@ -100,7 +100,7 @@
boolean tableExists = rs.next();
rs.close();
- return !tableExists;
+ return tableExists;
}
});
}
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java 2008-06-09 16:48:00 UTC (rev 833)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/generic/JdbcIfForeignKeyExistsConditionalTask.java 2008-06-11 21:18:57 UTC (rev 834)
@@ -97,7 +97,7 @@
}
rs.close();
- return !fkExists;
+ return fkExists;
}
});
}
Modified: 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 2008-06-09 16:48:00 UTC (rev 833)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/BaseConditionalTask.java 2008-06-11 21:18:57 UTC (rev 834)
@@ -177,7 +177,7 @@
{
SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
- if (check(jdbcTemplate) ^ !not)
+ if (check(jdbcTemplate) ^ not)
{
log.info("Executing Alter Task: {}", getDescription());
Modified: trunk/openutils-dbmigration/src/site/changes/changes.xml
===================================================================
--- trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-06-09 16:48:00 UTC (rev 833)
+++ trunk/openutils-dbmigration/src/site/changes/changes.xml 2008-06-11 21:18:57 UTC (rev 834)
@@ -9,6 +9,9 @@
</properties>
<body>
<release version="2.0.4" date="in svn" description="2.0.4">
+ <action type="fix" dev="fgiust"> Warning: fixed the usage of the "not" condition in
+ it.openutils.migration.task.setup.BaseConditionalTask that previosly caused unexpected (inverted) results. You
+ may need to review your conditional tasks in order to check if the condition is still valid!</action>
<action type="add" dev="fgiust">New jdbc generic tasks: IfColumnIsNotIdentityConditionalTask,
JdbcIfColumnIsNotNullableConditionalTask, JdbcIfForeignKeyExistsConditionalTask</action>
<action type="add" dev="fgiust">New oracle specific tasks: OraclePackageCreationTask,
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X