Menu
▾
▴
[Openutils-svn] SF.net SVN: openutils: [311] tags
|
From: <fg...@us...> - 2007-02-26 22:16:14
|
Revision: 311
http://svn.sourceforge.net/openutils/?rev=311&view=rev
Author: fgiust
Date: 2007-02-26 14:16:09 -0800 (Mon, 26 Feb 2007)
Log Message:
-----------
[maven-scm] copy for tag openutils-dbmigration-0.3
Added Paths:
-----------
tags/openutils-dbmigration-0.3/
tags/openutils-dbmigration-0.3/pom.xml
tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java
tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
Removed Paths:
-------------
tags/openutils-dbmigration-0.3/pom.xml
tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java
tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
Copied: tags/openutils-dbmigration-0.3 (from rev 283, trunk/openutils-dbmigration)
Deleted: tags/openutils-dbmigration-0.3/pom.xml
===================================================================
--- trunk/openutils-dbmigration/pom.xml 2007-02-22 14:15:58 UTC (rev 283)
+++ tags/openutils-dbmigration-0.3/pom.xml 2007-02-26 22:16:09 UTC (rev 311)
@@ -1,87 +0,0 @@
-<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
- <modelVersion>4.0.0</modelVersion>
- <parent>
- <groupId>net.sourceforge.openutils</groupId>
- <artifactId>openutils</artifactId>
- <version>3</version>
- <relativePath>..</relativePath>
- </parent>
- <artifactId>openutils-dbmigration</artifactId>
- <name>openutils db migration framework</name>
- <version>1.0-SNAPSHOT</version>
- <description />
- <dependencies>
- <dependency>
- <groupId>commons-lang</groupId>
- <artifactId>commons-lang</artifactId>
- <version>2.3</version>
- </dependency>
- <dependency>
- <groupId>commons-io</groupId>
- <artifactId>commons-io</artifactId>
- <version>1.3</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-jdbc</artifactId>
- <version>2.0.1</version>
- <exclusions>
- <exclusion>
- <groupId>avalon-framework</groupId>
- <artifactId>avalon-framework</artifactId>
- </exclusion>
- <exclusion>
- <groupId>logkit</groupId>
- <artifactId>logkit</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-beans</artifactId>
- <version>2.0.1</version>
- <exclusions>
- <exclusion>
- <groupId>avalon-framework</groupId>
- <artifactId>avalon-framework</artifactId>
- </exclusion>
- <exclusion>
- <groupId>logkit</groupId>
- <artifactId>logkit</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-core</artifactId>
- <version>2.0.1</version>
- <exclusions>
- <exclusion>
- <groupId>avalon-framework</groupId>
- <artifactId>avalon-framework</artifactId>
- </exclusion>
- <exclusion>
- <groupId>logkit</groupId>
- <artifactId>logkit</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.slf4j</groupId>
- <artifactId>slf4j-log4j12</artifactId>
- <version>1.2</version>
- </dependency>
- <dependency>
- <groupId>poi</groupId>
- <artifactId>poi</artifactId>
- <version>2.5.1-final-20040804</version>
- <optional>true</optional>
- </dependency>
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- <version>4.0</version>
- <scope>test</scope>
- </dependency>
- </dependencies>
-</project>
\ No newline at end of file
Copied: tags/openutils-dbmigration-0.3/pom.xml (from rev 310, trunk/openutils-dbmigration/pom.xml)
===================================================================
--- tags/openutils-dbmigration-0.3/pom.xml (rev 0)
+++ tags/openutils-dbmigration-0.3/pom.xml 2007-02-26 22:16:09 UTC (rev 311)
@@ -0,0 +1,93 @@
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
+ <modelVersion>4.0.0</modelVersion>
+ <parent>
+ <groupId>net.sourceforge.openutils</groupId>
+ <artifactId>openutils</artifactId>
+ <version>3</version>
+ <relativePath>..</relativePath>
+ </parent>
+ <artifactId>openutils-dbmigration</artifactId>
+ <name>openutils db migration framework</name>
+ <version>0.3</version>
+ <description />
+ <dependencies>
+ <dependency>
+ <groupId>commons-lang</groupId>
+ <artifactId>commons-lang</artifactId>
+ <version>2.3</version>
+ </dependency>
+ <dependency>
+ <groupId>commons-io</groupId>
+ <artifactId>commons-io</artifactId>
+ <version>1.3</version>
+ </dependency>
+ <dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-jdbc</artifactId>
+ <version>2.0.1</version>
+ <exclusions>
+ <exclusion>
+ <groupId>avalon-framework</groupId>
+ <artifactId>avalon-framework</artifactId>
+ </exclusion>
+ <exclusion>
+ <groupId>logkit</groupId>
+ <artifactId>logkit</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-beans</artifactId>
+ <version>2.0.1</version>
+ <exclusions>
+ <exclusion>
+ <groupId>avalon-framework</groupId>
+ <artifactId>avalon-framework</artifactId>
+ </exclusion>
+ <exclusion>
+ <groupId>logkit</groupId>
+ <artifactId>logkit</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-core</artifactId>
+ <version>2.0.1</version>
+ <exclusions>
+ <exclusion>
+ <groupId>avalon-framework</groupId>
+ <artifactId>avalon-framework</artifactId>
+ </exclusion>
+ <exclusion>
+ <groupId>logkit</groupId>
+ <artifactId>logkit</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.slf4j</groupId>
+ <artifactId>slf4j-log4j12</artifactId>
+ <version>1.2</version>
+ </dependency>
+ <dependency>
+ <groupId>poi</groupId>
+ <artifactId>poi</artifactId>
+ <version>2.5.1-final-20040804</version>
+ <optional>true</optional>
+ </dependency>
+ <dependency>
+ <groupId>junit</groupId>
+ <artifactId>junit</artifactId>
+ <version>4.0</version>
+ <scope>test</scope>
+ </dependency>
+ </dependencies>
+
+ <scm>
+ <connection>scm:svn:https://openutils.svn.sourceforge.net/svnroot/openutils/tags/openutils-dbmigration-0.3</connection>
+ <developerConnection>scm:svn:https://openutils.svn.sourceforge.net/svnroot/openutils/tags/openutils-dbmigration-0.3</developerConnection>
+ <url>http://openutils.svn.sourceforge.net/viewcvs.cgi/openutils/tags/openutils-dbmigration-0.3</url>
+ </scm>
+</project>
\ No newline at end of file
Deleted: tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/DbSetupManagerImpl.java 2007-02-22 14:15:58 UTC (rev 283)
+++ tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java 2007-02-26 22:16:09 UTC (rev 311)
@@ -1,202 +0,0 @@
-/*
- * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
- */
-package it.openutils.migration;
-
-import it.openutils.migration.task.setup.DbTask;
-import it.openutils.migration.task.update.DbUpdate;
-
-import java.util.Comparator;
-import java.util.List;
-import java.util.Set;
-import java.util.TreeSet;
-
-import javax.sql.DataSource;
-
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-import org.springframework.dao.DataAccessException;
-import org.springframework.transaction.TransactionStatus;
-import org.springframework.transaction.support.TransactionCallbackWithoutResult;
-import org.springframework.transaction.support.TransactionTemplate;
-
-
-/**
- * DB setup manager: handles db setup and upgrade.
- * @author fgiust
- * @version $Id$
- */
-public class DbSetupManagerImpl implements DbSetupManager
-{
-
- /**
- * Logger.
- */
- private static Logger log = LoggerFactory.getLogger(DbSetupManagerImpl.class);
-
- private List<DbTask> setupTasks;
-
- private List<DbUpdate> updateTasks;
-
- private DataSource dataSource;
-
- private DbVersionManager versionManager;
-
- private TransactionTemplate transactionTemplate;
-
- private boolean enabled = true;
-
- /**
- * Sets the enabled.
- * @param enabled the enabled to set
- */
- public void setEnabled(boolean enabled)
- {
- this.enabled = enabled;
- }
-
- /**
- * Sets the versionManager.
- * @param versionManager the versionManager to set
- */
- public void setVersionManager(DbVersionManager versionManager)
- {
- this.versionManager = versionManager;
- }
-
- /**
- * Sets the transactionTemplate.
- * @param transactionTemplate the transactionTemplate to set
- */
- public void setTransactionTemplate(TransactionTemplate transactionTemplate)
- {
- this.transactionTemplate = transactionTemplate;
- }
-
- /**
- * Setter for <code>dataSource</code>.
- * @param dataSource The dataSource to set.
- */
- public void setDataSource(DataSource dataSource)
- {
- this.dataSource = dataSource;
- }
-
- /**
- * Sets the tasks.
- * @param setupTasks the tasks to set
- */
- public void setSetupTasks(List<DbTask> setupTasks)
- {
- this.setupTasks = setupTasks;
- }
-
- /**
- * Sets the updateTasks.
- * @param updateTasks the updateTasks to set
- */
- public void setUpdateTasks(List<DbUpdate> updateTasks)
- {
- this.updateTasks = updateTasks;
- }
-
- /**
- * @see it.openutils.dbupdate.DbSetupManager#updateDDL()
- */
- public void updateDDL()
- {
-
- if (!enabled)
- {
- log.info("DB migration is disabled, not running tasks.");
- }
-
- transactionTemplate.execute(new TransactionCallbackWithoutResult()
- {
-
- @Override
- protected void doInTransactionWithoutResult(TransactionStatus status)
- {
- if (setupTasks != null)
- {
- executeSetupTasks();
- }
- if (updateTasks != null)
- {
- executeUpdateTasks();
- }
- }
- });
-
- }
-
- /**
- *
- */
- private void executeSetupTasks()
- {
- log.info("Preparing db, checking {} setup tasks.", setupTasks.size());
- for (DbTask task : setupTasks)
- {
- log.info(task.getDescription());
- task.execute(dataSource);
- }
-
- }
-
- /**
- *
- */
- private void executeUpdateTasks()
- {
-
- int initialVersion = versionManager.getCurrentVersion();
- int currentVersion = initialVersion;
-
- Set<DbUpdate> sortedMigrations = new TreeSet<DbUpdate>(new Comparator<DbUpdate>()
- {
-
- public int compare(DbUpdate o1, DbUpdate o2)
- {
- return o1.getVersion() - o2.getVersion();
- }
-
- });
- sortedMigrations.addAll(updateTasks);
-
- log.info("Found {} migrations, looking for updates to run...", updateTasks.size());
- for (DbUpdate update : sortedMigrations)
- {
- if (update.getVersion() > currentVersion)
- {
- currentVersion = update.getVersion();
-
- log.info("Preparing migration to version {}. {}", update.getVersion(), update.getDescription());
- try
- {
- update.execute(dataSource);
- }
- catch (DataAccessException e)
- {
- log.error("\n***********\n\n\nDatabase upgrade from version "
- + initialVersion
- + " to version "
- + currentVersion
- + " FAILED!\n\n\n***********\n", e);
- }
- versionManager.setNewVersion(currentVersion);
- }
- }
- if (currentVersion != initialVersion)
- {
- log.info("Database upgraded from version {} to version {}", initialVersion, currentVersion);
- }
- else
- {
- log.info("No Database upgrade is needed. Current version is {} ", initialVersion);
- }
- // org.springframework.jdbc.BadSqlGrammarException:
- // java.sql.SQLException: ORA-00959: tablespace 'XDM_DATA' inesistente
-
- }
-}
Copied: tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java (from rev 309, trunk/openutils-dbmigration/src/main/java/it/openutils/migration/DbSetupManagerImpl.java)
===================================================================
--- tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java (rev 0)
+++ tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/DbSetupManagerImpl.java 2007-02-26 22:16:09 UTC (rev 311)
@@ -0,0 +1,203 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration;
+
+import it.openutils.migration.task.setup.DbTask;
+import it.openutils.migration.task.update.DbUpdate;
+
+import java.util.Comparator;
+import java.util.List;
+import java.util.Set;
+import java.util.TreeSet;
+
+import javax.sql.DataSource;
+
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.dao.DataAccessException;
+import org.springframework.transaction.TransactionStatus;
+import org.springframework.transaction.support.TransactionCallbackWithoutResult;
+import org.springframework.transaction.support.TransactionTemplate;
+
+
+/**
+ * DB setup manager: handles db setup and upgrade.
+ * @author fgiust
+ * @version $Id$
+ */
+public class DbSetupManagerImpl implements DbSetupManager
+{
+
+ /**
+ * Logger.
+ */
+ private static Logger log = LoggerFactory.getLogger(DbSetupManagerImpl.class);
+
+ private List<DbTask> setupTasks;
+
+ private List<DbUpdate> updateTasks;
+
+ private DataSource dataSource;
+
+ private DbVersionManager versionManager;
+
+ private TransactionTemplate transactionTemplate;
+
+ private boolean enabled = true;
+
+ /**
+ * Sets the enabled.
+ * @param enabled the enabled to set
+ */
+ public void setEnabled(boolean enabled)
+ {
+ this.enabled = enabled;
+ }
+
+ /**
+ * Sets the versionManager.
+ * @param versionManager the versionManager to set
+ */
+ public void setVersionManager(DbVersionManager versionManager)
+ {
+ this.versionManager = versionManager;
+ }
+
+ /**
+ * Sets the transactionTemplate.
+ * @param transactionTemplate the transactionTemplate to set
+ */
+ public void setTransactionTemplate(TransactionTemplate transactionTemplate)
+ {
+ this.transactionTemplate = transactionTemplate;
+ }
+
+ /**
+ * Setter for <code>dataSource</code>.
+ * @param dataSource The dataSource to set.
+ */
+ public void setDataSource(DataSource dataSource)
+ {
+ this.dataSource = dataSource;
+ }
+
+ /**
+ * Sets the tasks.
+ * @param setupTasks the tasks to set
+ */
+ public void setSetupTasks(List<DbTask> setupTasks)
+ {
+ this.setupTasks = setupTasks;
+ }
+
+ /**
+ * Sets the updateTasks.
+ * @param updateTasks the updateTasks to set
+ */
+ public void setUpdateTasks(List<DbUpdate> updateTasks)
+ {
+ this.updateTasks = updateTasks;
+ }
+
+ /**
+ * @see it.openutils.dbupdate.DbSetupManager#updateDDL()
+ */
+ public void updateDDL()
+ {
+
+ if (!enabled)
+ {
+ log.info("DB migration is disabled, not running tasks.");
+ return;
+ }
+
+ transactionTemplate.execute(new TransactionCallbackWithoutResult()
+ {
+
+ @Override
+ protected void doInTransactionWithoutResult(TransactionStatus status)
+ {
+ if (setupTasks != null)
+ {
+ executeSetupTasks();
+ }
+ if (updateTasks != null)
+ {
+ executeUpdateTasks();
+ }
+ }
+ });
+
+ }
+
+ /**
+ *
+ */
+ private void executeSetupTasks()
+ {
+ log.info("Preparing db, checking {} setup tasks.", setupTasks.size());
+ for (DbTask task : setupTasks)
+ {
+ log.info(task.getDescription());
+ task.execute(dataSource);
+ }
+
+ }
+
+ /**
+ *
+ */
+ private void executeUpdateTasks()
+ {
+
+ int initialVersion = versionManager.getCurrentVersion();
+ int currentVersion = initialVersion;
+
+ Set<DbUpdate> sortedMigrations = new TreeSet<DbUpdate>(new Comparator<DbUpdate>()
+ {
+
+ public int compare(DbUpdate o1, DbUpdate o2)
+ {
+ return o1.getVersion() - o2.getVersion();
+ }
+
+ });
+ sortedMigrations.addAll(updateTasks);
+
+ log.info("Found {} migrations, looking for updates to run...", updateTasks.size());
+ for (DbUpdate update : sortedMigrations)
+ {
+ if (update.getVersion() > currentVersion)
+ {
+ currentVersion = update.getVersion();
+
+ log.info("Preparing migration to version {}. {}", update.getVersion(), update.getDescription());
+ try
+ {
+ update.execute(dataSource);
+ }
+ catch (DataAccessException e)
+ {
+ log.error("\n***********\n\n\nDatabase upgrade from version "
+ + initialVersion
+ + " to version "
+ + currentVersion
+ + " FAILED!\n\n\n***********\n", e);
+ }
+ versionManager.setNewVersion(currentVersion);
+ }
+ }
+ if (currentVersion != initialVersion)
+ {
+ log.info("Database upgraded from version {} to version {}", initialVersion, currentVersion);
+ }
+ else
+ {
+ log.info("No Database upgrade is needed. Current version is {} ", initialVersion);
+ }
+ // org.springframework.jdbc.BadSqlGrammarException:
+ // java.sql.SQLException: ORA-00959: tablespace 'XDM_DATA' inesistente
+
+ }
+}
Deleted: tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2007-02-22 14:15:58 UTC (rev 283)
+++ tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2007-02-26 22:16:09 UTC (rev 311)
@@ -1,355 +0,0 @@
-/*
- * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
- */
-package it.openutils.migration.task.setup;
-
-import java.io.IOException;
-import java.io.InputStream;
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Types;
-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.ArrayUtils;
-import org.apache.commons.lang.StringUtils;
-import org.apache.poi.hssf.usermodel.HSSFCell;
-import org.apache.poi.hssf.usermodel.HSSFRow;
-import org.apache.poi.hssf.usermodel.HSSFSheet;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-import org.springframework.core.io.Resource;
-import org.springframework.dao.DataAccessException;
-import org.springframework.dao.DataIntegrityViolationException;
-import org.springframework.jdbc.BadSqlGrammarException;
-import org.springframework.jdbc.core.ConnectionCallback;
-import org.springframework.jdbc.core.JdbcTemplate;
-
-
-/**
- * @author fgiust
- * @version $Id$
- */
-public class ExcelConfigurationTask extends BaseDbTask implements DbTask
-{
-
- /**
- * Logger.
- */
- private Logger log = LoggerFactory.getLogger(ScriptBasedUnconditionalTask.class);
-
- private Resource script;
-
- private Map<String, ExcelConfigurationTask.QueryConfig> config;
-
- /**
- * Sets the script.
- * @param script the script to set
- */
- public void setScript(Resource script)
- {
- this.script = script;
- }
-
- /**
- * Sets the config.
- * @param config the config to set
- */
- public void setConfig(Map<String, ExcelConfigurationTask.QueryConfig> config)
- {
- this.config = config;
- }
-
- /**
- * {@inheritDoc}
- */
- public void execute(DataSource dataSource)
- {
- if (script == null || !script.exists())
- {
- log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
- return;
- }
-
- InputStream is = null;
- try
- {
- is = script.getInputStream();
- POIFSFileSystem fs = new POIFSFileSystem(is);
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
- int sheetNums = hssfworkbook.getNumberOfSheets();
- for (int j = 0; j < sheetNums; j++)
- {
- HSSFSheet sheet = hssfworkbook.getSheetAt(j);
- String tableName = hssfworkbook.getSheetName(j);
-
- QueryConfig conf = config.get(tableName);
- if (conf == null)
- {
- log.error("Unable to handle table {}", tableName);
- continue;
- }
- processSheet(sheet, tableName, conf, dataSource);
-
- }
-
- }
- catch (IOException e)
- {
- log.error(e.getMessage(), e);
- }
- finally
- {
- IOUtils.closeQuietly(is);
- }
-
- }
-
- /**
- * @param sheet
- * @param tableName
- */
- private void processSheet(HSSFSheet sheet, final String tableName, QueryConfig con, DataSource dataSource)
- {
- final List<String> columns = new ArrayList<String>();
-
- HSSFRow row = sheet.getRow(0);
- for (short k = 0; k < row.getLastCellNum(); k++)
- {
- String columnName = row.getCell(k).getStringCellValue();
- if (StringUtils.isNotBlank(columnName))
- {
- columns.add(StringUtils.trim(columnName));
- }
- else
- {
- break;
- }
- }
-
- log.debug("Table: {}, Columns: {}", tableName, columns);
-
- final List<Integer> types = new ArrayList<Integer>();
-
- boolean result = (Boolean) new JdbcTemplate(dataSource).execute(new ConnectionCallback()
- {
-
- public Object doInConnection(Connection con) throws SQLException, DataAccessException
- {
- for (String column : columns)
- {
- ResultSet res = con.getMetaData().getColumns(null, null, tableName, column);
- if (res.next())
- {
- types.add(res.getInt("DATA_TYPE"));
- }
- else
- {
- log.warn("Unable to determine type for column '{}' in table '{}'", column, tableName);
- return false;
- }
- res.close();
- }
- return true;
- }
- });
-
- if (!result)
- {
- log.warn("Skipping sheet {} ", tableName);
- }
-
- String checkStatement = StringUtils.remove(StringUtils.trim(con.getCheckQuery()), "\n");
- String insertStatement = StringUtils.remove(StringUtils.trim(con.getInsertQuery()), "\n");
-
- processRecords(
- sheet,
- columns,
- ArrayUtils.toPrimitive(types.toArray(new Integer[types.size()]), Types.NULL),
- checkStatement,
- insertStatement,
- dataSource,
- tableName);
- }
-
- /**
- * @param sheet
- * @param columns
- * @param checkStatement
- * @param insertStatement
- */
- private void processRecords(HSSFSheet sheet, List<String> columns, int[] types, String checkStatement,
- String insertStatement, DataSource dataSource, String tableName)
- {
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
- int checkNum = StringUtils.countMatches(checkStatement, "?");
- int insertNum = StringUtils.countMatches(insertStatement, "?");
-
- HSSFRow row;
- for (short rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++)
- {
- row = sheet.getRow(rowNum);
- if (row == null)
- {
- return;
- }
-
- List<String> values = new ArrayList<String>();
-
- for (short k = 0; k < columns.size() && k <= row.getLastCellNum(); k++)
- {
- HSSFCell cell = row.getCell(k);
- String value = null;
-
- if (cell == null)
- {
- value = StringUtils.EMPTY;
- }
- else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
- {
- value = cell.getStringCellValue();
- }
- else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
- {
- double valueDouble = cell.getNumericCellValue();
- // when need to really check if it is a double or an int
- double fraction = valueDouble % 1;
- if (fraction == 0)
- {
- value = Integer.toString((int) valueDouble);
- }
- else
- {
- value = Double.toString(valueDouble);
- }
- }
-
- if (StringUtils.isEmpty(value))
- {
- value = StringUtils.EMPTY;
- }
-
- if ("<NULL>".equalsIgnoreCase(value))
- {
- value = null;
- }
-
- values.add(value);
- }
-
- Object[] checkParams = ArrayUtils.subarray(values.toArray(), 0, checkNum);
- for (int i = 0; i < checkParams.length; i++)
- {
- if (StringUtils.isEmpty((String) checkParams[i]))
- {
- return;
- }
- }
-
- int existing;
- try
- {
- existing = jdbcTemplate.queryForInt(checkStatement, checkParams);
- }
- catch (BadSqlGrammarException bsge)
- {
- log.error("Error executing check query, current sheet will be skipped. {} Query in error: {}", bsge
- .getMessage(), checkStatement);
- return;
- }
-
- if (existing == 0)
- {
- Object[] insertParams = ArrayUtils.subarray(values.toArray(), 0, insertNum);
- int[] insertTypes = ArrayUtils.subarray(types, 0, insertNum);
- if (log.isDebugEnabled())
- {
- log.debug("Missing record with key {}; inserting {}", ArrayUtils.toString(checkParams), ArrayUtils
- .toString(insertParams));
- }
-
- if (insertParams.length != insertTypes.length)
- {
- log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
- tableName,
- insertParams.length,
- insertTypes.length });
- }
-
- try
- {
- jdbcTemplate.update(insertStatement, insertParams, insertTypes);
- }
- catch (DataIntegrityViolationException bsge)
- {
- log
- .error(
- "Error executing insert, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
- new Object[]{
- tableName,
- rowNum + 1,
- insertStatement,
- ArrayUtils.toString(insertParams),
- bsge.getMessage() });
- continue;
- }
- }
-
- }
- }
-
- /**
- * @author fgiust
- * @version $Id$
- */
- public static class QueryConfig
- {
-
- private String checkQuery;
-
- private String insertQuery;
-
- /**
- * Returns the checkQuery.
- * @return the checkQuery
- */
- public String getCheckQuery()
- {
- return checkQuery;
- }
-
- /**
- * Sets the checkQuery.
- * @param checkQuery the checkQuery to set
- */
- public void setCheckQuery(String checkQuery)
- {
- this.checkQuery = checkQuery;
- }
-
- /**
- * Returns the insertQuery.
- * @return the insertQuery
- */
- public String getInsertQuery()
- {
- return insertQuery;
- }
-
- /**
- * Sets the insertQuery.
- * @param insertQuery the insertQuery to set
- */
- public void setInsertQuery(String insertQuery)
- {
- this.insertQuery = insertQuery;
- }
- }
-}
Copied: tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java (from rev 308, trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java)
===================================================================
--- tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java (rev 0)
+++ tags/openutils-dbmigration-0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2007-02-26 22:16:09 UTC (rev 311)
@@ -0,0 +1,359 @@
+/*
+ * Copyright (c) Openmind. All rights reserved. http://www.openmindonline.it
+ */
+package it.openutils.migration.task.setup;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+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.ArrayUtils;
+import org.apache.commons.lang.StringUtils;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.poifs.filesystem.POIFSFileSystem;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.core.io.Resource;
+import org.springframework.dao.DataAccessException;
+import org.springframework.dao.DataIntegrityViolationException;
+import org.springframework.jdbc.BadSqlGrammarException;
+import org.springframework.jdbc.core.ConnectionCallback;
+import org.springframework.jdbc.core.JdbcTemplate;
+
+
+/**
+ * @author fgiust
+ * @version $Id$
+ */
+public class ExcelConfigurationTask extends BaseDbTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(ScriptBasedUnconditionalTask.class);
+
+ private Resource script;
+
+ private Map<String, ExcelConfigurationTask.QueryConfig> config;
+
+ /**
+ * Sets the script.
+ * @param script the script to set
+ */
+ public void setScript(Resource script)
+ {
+ this.script = script;
+ }
+
+ /**
+ * Sets the config.
+ * @param config the config to set
+ */
+ public void setConfig(Map<String, ExcelConfigurationTask.QueryConfig> config)
+ {
+ this.config = config;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+ if (script == null || !script.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
+ return;
+ }
+
+ InputStream is = null;
+ try
+ {
+ is = script.getInputStream();
+ POIFSFileSystem fs = new POIFSFileSystem(is);
+ HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
+ int sheetNums = hssfworkbook.getNumberOfSheets();
+ for (int j = 0; j < sheetNums; j++)
+ {
+ HSSFSheet sheet = hssfworkbook.getSheetAt(j);
+ String tableName = hssfworkbook.getSheetName(j);
+
+ QueryConfig conf = config.get(tableName);
+ if (conf == null)
+ {
+ log.error("Unable to handle table {}", tableName);
+ continue;
+ }
+ processSheet(sheet, tableName, conf, dataSource);
+
+ }
+
+ }
+ catch (IOException e)
+ {
+ log.error(e.getMessage(), e);
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+
+ }
+
+ /**
+ * @param sheet
+ * @param tableName
+ */
+ private void processSheet(HSSFSheet sheet, final String tableName, QueryConfig con, DataSource dataSource)
+ {
+ 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;
+ }
+ }
+ }
+
+ log.debug("Table: {}, Columns: {}", tableName, columns);
+
+ final List<Integer> types = new ArrayList<Integer>();
+
+ boolean result = (Boolean) new JdbcTemplate(dataSource).execute(new ConnectionCallback()
+ {
+
+ public Object doInConnection(Connection con) throws SQLException, DataAccessException
+ {
+ for (String column : columns)
+ {
+ ResultSet res = con.getMetaData().getColumns(null, null, tableName, column);
+ if (res.next())
+ {
+ types.add(res.getInt("DATA_TYPE"));
+ }
+ else
+ {
+ log.warn("Unable to determine type for column '{}' in table '{}'", column, tableName);
+ return false;
+ }
+ res.close();
+ }
+ return true;
+ }
+ });
+
+ if (!result)
+ {
+ log.warn("Skipping sheet {} ", tableName);
+ }
+
+ String checkStatement = StringUtils.remove(StringUtils.trim(con.getCheckQuery()), "\n");
+ String insertStatement = StringUtils.remove(StringUtils.trim(con.getInsertQuery()), "\n");
+
+ processRecords(
+ sheet,
+ columns,
+ ArrayUtils.toPrimitive(types.toArray(new Integer[types.size()]), Types.NULL),
+ checkStatement,
+ insertStatement,
+ dataSource,
+ tableName);
+ }
+
+ /**
+ * @param sheet
+ * @param columns
+ * @param checkStatement
+ * @param insertStatement
+ */
+ private void processRecords(HSSFSheet sheet, List<String> columns, int[] types, String checkStatement,
+ String insertStatement, DataSource dataSource, String tableName)
+ {
+ JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
+ int checkNum = StringUtils.countMatches(checkStatement, "?");
+ int insertNum = StringUtils.countMatches(insertStatement, "?");
+
+ HSSFRow row;
+ for (short rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++)
+ {
+ row = sheet.getRow(rowNum);
+ if (row == null)
+ {
+ return;
+ }
+
+ List<String> values = new ArrayList<String>();
+
+ for (short k = 0; k < columns.size() && k <= row.getLastCellNum(); k++)
+ {
+ HSSFCell cell = row.getCell(k);
+ String value = null;
+
+ if (cell == null)
+ {
+ value = StringUtils.EMPTY;
+ }
+ else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
+ {
+ value = cell.getStringCellValue();
+ }
+ else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
+ {
+ double valueDouble = cell.getNumericCellValue();
+ // when need to really check if it is a double or an int
+ double fraction = valueDouble % 1;
+ if (fraction == 0)
+ {
+ value = Integer.toString((int) valueDouble);
+ }
+ else
+ {
+ value = Double.toString(valueDouble);
+ }
+ }
+
+ if (StringUtils.isEmpty(value))
+ {
+ value = StringUtils.EMPTY;
+ }
+
+ if ("<NULL>".equalsIgnoreCase(value))
+ {
+ value = null;
+ }
+
+ values.add(value);
+ }
+
+ Object[] checkParams = ArrayUtils.subarray(values.toArray(), 0, checkNum);
+ for (int i = 0; i < checkParams.length; i++)
+ {
+ if (StringUtils.isEmpty((String) checkParams[i]))
+ {
+ return;
+ }
+ }
+
+ int existing;
+ try
+ {
+ existing = jdbcTemplate.queryForInt(checkStatement, checkParams);
+ }
+ catch (BadSqlGrammarException bsge)
+ {
+ log.error("Error executing check query, current sheet will be skipped. {} Query in error: {}", bsge
+ .getMessage(), checkStatement);
+ return;
+ }
+
+ if (existing == 0)
+ {
+ Object[] insertParams = ArrayUtils.subarray(values.toArray(), 0, insertNum);
+ int[] insertTypes = ArrayUtils.subarray(types, 0, insertNum);
+ if (log.isDebugEnabled())
+ {
+ log.debug("Missing record with key {}; inserting {}", ArrayUtils.toString(checkParams), ArrayUtils
+ .toString(insertParams));
+ }
+
+ if (insertParams.length != insertTypes.length)
+ {
+ log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
+ tableName,
+ insertParams.length,
+ insertTypes.length });
+ }
+
+ try
+ {
+ jdbcTemplate.update(insertStatement, insertParams, insertTypes);
+ }
+ catch (DataIntegrityViolationException bsge)
+ {
+ log
+ .error(
+ "Error executing insert, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
+ new Object[]{
+ tableName,
+ rowNum + 1,
+ insertStatement,
+ ArrayUtils.toString(insertParams),
+ bsge.getMessage() });
+ continue;
+ }
+ }
+
+ }
+ }
+
+ /**
+ * @author fgiust
+ * @version $Id$
+ */
+ public static class QueryConfig
+ {
+
+ private String checkQuery;
+
+ private String insertQuery;
+
+ /**
+ * Returns the checkQuery.
+ * @return the checkQuery
+ */
+ public String getCheckQuery()
+ {
+ return checkQuery;
+ }
+
+ /**
+ * Sets the checkQuery.
+ * @param checkQuery the checkQuery to set
+ */
+ public void setCheckQuery(String checkQuery)
+ {
+ this.checkQuery = checkQuery;
+ }
+
+ /**
+ * Returns the insertQuery.
+ * @return the insertQuery
+ */
+ public String getInsertQuery()
+ {
+ return insertQuery;
+ }
+
+ /**
+ * Sets the insertQuery.
+ * @param insertQuery the insertQuery to set
+ */
+ public void setInsertQuery(String insertQuery)
+ {
+ this.insertQuery = insertQuery;
+ }
+ }
+}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
