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 0d5f212a01ad9ccaad08cd24b70c6915b36aad39..1110f0d0cb515ccbb2c41f5eae6678f775f8a65a 100644 (file)
--- 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 @@
 \r
 package eu.etaxonomy.cdm.database;\r
 \r
+import java.sql.ResultSet;\r
+\r
 import org.hibernate.cache.CacheProvider;\r
 import org.springframework.beans.factory.config.BeanDefinition;\r
 \r
@@ -85,6 +87,19 @@ public interface ICdmDataSource {
        \r
        public String getPassword();\r
        \r
+        /**\r
+     * Executes a query and returns the ResultSet.\r
+     * @return ResultSet for the query.\r
+     */\r
+       public ResultSet executeQuery (String query);\r
+       \r
+    /**\r
+     * Executes an update\r
+     * @return return code\r
+     */\r
+       public int executeUpdate (String sqlUpdate);\r
+\r
+\r
        \r
 //\r
 //     public void setFilePath(String filePath);\r

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 6ef24b9981cdcc65774a62929ff3490a80053988..a5312f9181167a91dc3b6d215f0b56bbcf5cd549 100644 (file)
--- 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 @@
 \r
 package eu.etaxonomy.cdm.database.types;\r
 \r
+import java.sql.ResultSet;\r
+import java.sql.SQLException;\r
+\r
+import eu.etaxonomy.cdm.database.CdmDataSource;\r
 import eu.etaxonomy.cdm.database.ICdmDataSource;\r
 \r
 \r
@@ -87,5 +91,31 @@ public class SqlServer2005DatabaseType extends DatabaseTypeBase {
     public SqlServer2005DatabaseType() {\r
        init (typeName, classString, urlString, defaultPort,  hibernateDialect );\r
        }\r
+    \r
+    \r
+    /**\r
+     * Deletes all foreign keys between tables in a sql server database.\r
+     * This make deleting tables easier.\r
+     * @param sqlServerDataSource\r
+     * @return\r
+     * @throws SQLException\r
+     */\r
+    public boolean deleteForeignKeys(CdmDataSource sqlServerDataSource) throws SQLException{\r
+       String sql = "SELECT name, id FROM sys.sysobjects WHERE (xtype = 'U')"; //all tables\r
+               ResultSet rs = sqlServerDataSource.executeQuery(sql);\r
+               while (rs.next()){\r
+                       String tableName = rs.getString("name");\r
+                       long tableId = rs.getLong("id");\r
+                       sql = "SELECT name FROM sys.sysobjects WHERE xtype='F' and parent_obj = " +  tableId;//get foreignkeys\r
+                       ResultSet rsFk = sqlServerDataSource.executeQuery(sql);\r
+                       while (rsFk.next()){\r
+                               String fk = rsFk.getString("name");\r
+                               sql = " ALTER TABLE "+tableName+" DROP CONSTRAINT "+fk + "";\r
+                               sqlServerDataSource.executeUpdate(sql);\r
+                       }\r
+                       \r
+               }\r
+               return true;\r
+    }\r
 \r
 }
\ No newline at end of file