cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/types/SqlServer2005DatabaseType.java

   1 /**
   2 * Copyright (C) 2007 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9
  10 package eu.etaxonomy.cdm.database.types;
  11
  12 import java.sql.ResultSet;
  13 import java.sql.SQLException;
  14
  15 import eu.etaxonomy.cdm.database.CdmDataSource;
  16 import eu.etaxonomy.cdm.database.ICdmDataSource;
  17
  18
  19 /**
  20  * @author a.mueller
  21  *
  22  */
  23 public class SqlServer2005DatabaseType extends DatabaseTypeBase {
  24
  25         //name
  26         protected String typeName = "SQL Server";
  27
  28         //driver class
  29         protected String classString = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  30
  31         //url
  32         protected String urlString = "jdbc:sqlserver://";
  33
  34         //[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
  35
  36     //default port
  37     protected int defaultPort = 1433;
  38
  39     //hibernate dialect
  40     protected String hibernateDialect = "SQLServer2005Dialect";
  41
  42     public String getConnectionString(ICdmDataSource ds, int port){
  43         return getConnectionString(ds, port, null);
  44     }
  45
  46     public String getConnectionString(ICdmDataSource ds, int port, String instanceName){
  47         String instance = "";
  48         if (instanceName != null && ! instanceName.equals("")){
  49                         instance = "\\" + instanceName;
  50                 }
  51         return urlString + ds.getServer() + instance + ":" + port + ";databaseName=" + ds.getDatabase() +";SelectMethod=cursor";
  52     }
  53
  54
  55     /* (non-Javadoc)
  56          * @see eu.etaxonomy.cdm.database.types.DatabaseTypeBase#getServerNameByConnectionString(java.lang.String)
  57          */
  58         @Override
  59         public String getServerNameByConnectionString(String connectionString) {
  60                 String dbSeparator = ";";
  61                 return super.getServerNameByConnectionString(connectionString, urlString, dbSeparator);
  62         }
  63
  64
  65         /* (non-Javadoc)
  66          * @see eu.etaxonomy.cdm.database.types.DatabaseTypeBase#getPortByConnectionString(java.lang.String)
  67          */
  68         @Override
  69         public int getPortByConnectionString(String connectionString) {
  70                 String dbSeparator = ";";
  71                 return getPortByConnectionString(connectionString, urlString, dbSeparator);
  72         }
  73
  74     /* (non-Javadoc)
  75      * @see eu.etaxonomy.cdm.database.types.DatabaseTypeBase#getServerNameByConnectionString(java.lang.String)
  76      */
  77     @Override
  78     public String getDatabaseNameByConnectionString(String connectionString){
  79         String result;
  80         String dbStart = ";databaseName=";
  81         int posDbStart = connectionString.indexOf(dbStart);
  82         result = connectionString.substring(posDbStart + dbStart.length());
  83         int posNextAttr = result.indexOf(";");
  84         if (posNextAttr != 0){
  85                 result = result.substring(0, posNextAttr);
  86         }
  87         return result;
  88     }
  89
  90         //Constructor
  91     public SqlServer2005DatabaseType() {
  92         init (typeName, classString, urlString, defaultPort,  hibernateDialect );
  93         }
  94
  95
  96     /**
  97      * Deletes all foreign keys between tables in a sql server database.
  98      * This makes deleting tables easier.
  99      * @param sqlServerDataSource
 100      * @return
 101      * @throws SQLException
 102      */
 103     public boolean deleteForeignKeys(CdmDataSource sqlServerDataSource) throws SQLException{
 104         String sql = "SELECT name, id FROM sys.sysobjects WHERE (xtype = 'U')"; //all tables
 105                 ResultSet rs = sqlServerDataSource.executeQuery(sql);
 106                 while (rs.next()){
 107                         String tableName = rs.getString("name");
 108                         long tableId = rs.getLong("id");
 109                         sql = "SELECT name FROM sys.sysobjects WHERE xtype='F' and parent_obj = " +  tableId;//get foreignkeys
 110                         ResultSet rsFk = sqlServerDataSource.executeQuery(sql);
 111                         while (rsFk.next()){
 112                                 String fk = rsFk.getString("name");
 113                                 sql = " ALTER TABLE "+tableName+" DROP CONSTRAINT "+fk + "";
 114                                 sqlServerDataSource.executeUpdate(sql);
 115                         }
 116
 117                 }
 118                 return true;
 119     }
 120
 121 }