Menu
▾
▴
[Openutils-svn] SF.net SVN: openutils: [682] trunk/openutils-dbmigration/src
|
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.
|
