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