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