From: Andreas Müller <a.mueller@bgbm.org>
Date: Tue, 7 Jul 2009 14:42:17 +0000 (+0000)
Subject: delete FKs in SQL Server
X-Git-Tag: 3.0.3~2160
X-Git-Url: https://dev.e-taxonomy.eu/gitweb/cdmlib.git/commitdiff_plain/1b33bb03fb521ba2015a043cf141737a4072cf01

delete FKs in SQL Server
---

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/ICdmDataSource.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/ICdmDataSource.java
index 0d5f212a01..1110f0d0cb 100644
--- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/ICdmDataSource.java
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/ICdmDataSource.java
@@ -9,6 +9,8 @@
 
 package eu.etaxonomy.cdm.database;
 
+import java.sql.ResultSet;
+
 import org.hibernate.cache.CacheProvider;
 import org.springframework.beans.factory.config.BeanDefinition;
 
@@ -85,6 +87,19 @@ public interface ICdmDataSource {
 	
 	public String getPassword();
 	
+	 /**
+     * Executes a query and returns the ResultSet.
+     * @return ResultSet for the query.
+     */
+	public ResultSet executeQuery (String query);
+	
+    /**
+     * Executes an update
+     * @return return code
+     */
+	public int executeUpdate (String sqlUpdate);
+
+
 	
 //
 //	public void setFilePath(String filePath);
diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/types/SqlServer2005DatabaseType.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/types/SqlServer2005DatabaseType.java
index 6ef24b9981..a5312f9181 100644
--- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/types/SqlServer2005DatabaseType.java
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/types/SqlServer2005DatabaseType.java
@@ -9,6 +9,10 @@
 
 package eu.etaxonomy.cdm.database.types;
 
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import eu.etaxonomy.cdm.database.CdmDataSource;
 import eu.etaxonomy.cdm.database.ICdmDataSource;
 
 
@@ -87,5 +91,31 @@ public class SqlServer2005DatabaseType extends DatabaseTypeBase {
     public SqlServer2005DatabaseType() {
     	init (typeName, classString, urlString, defaultPort,  hibernateDialect );
 	}
+    
+    
+    /**
+     * Deletes all foreign keys between tables in a sql server database.
+     * This make deleting tables easier.
+     * @param sqlServerDataSource
+     * @return
+     * @throws SQLException
+     */
+    public boolean deleteForeignKeys(CdmDataSource sqlServerDataSource) throws SQLException{
+    	String sql = "SELECT name, id FROM sys.sysobjects WHERE (xtype = 'U')"; //all tables
+		ResultSet rs = sqlServerDataSource.executeQuery(sql);
+		while (rs.next()){
+			String tableName = rs.getString("name");
+			long tableId = rs.getLong("id");
+			sql = "SELECT name FROM sys.sysobjects WHERE xtype='F' and parent_obj = " +  tableId;//get foreignkeys
+			ResultSet rsFk = sqlServerDataSource.executeQuery(sql);
+			while (rsFk.next()){
+				String fk = rsFk.getString("name");
+				sql = " ALTER TABLE "+tableName+" DROP CONSTRAINT "+fk + "";
+				sqlServerDataSource.executeUpdate(sql);
+			}
+			
+		}
+		return true;
+    }
 
 }
\ No newline at end of file