001/* 002 * (C) Copyright 2006-2011 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 */ 019 020package org.nuxeo.ecm.core.storage.sql; 021 022import java.sql.Connection; 023import java.sql.DriverManager; 024import java.sql.ResultSet; 025import java.sql.SQLException; 026import java.sql.Statement; 027import java.util.HashMap; 028import java.util.Map; 029 030import org.nuxeo.runtime.api.Framework; 031 032/** 033 * @author Florent Guillaume 034 */ 035public class DatabaseSQLServer extends DatabaseHelper { 036 037 public static DatabaseHelper INSTANCE = new DatabaseSQLServer(); 038 039 private boolean supportsXA; 040 041 private boolean supportsSequences; 042 043 private static final String DEF_SERVER = "localhost"; 044 045 private static final String DEF_PORT = "1433"; 046 047 private static final String DEF_DATABASE = "nuxeojunittests"; 048 049 private static final String DEF_USER = "nuxeo"; 050 051 private static final String DEF_PASSWORD = "nuxeo"; 052 053 private static final String CONTRIB_XML = "OSGI-INF/test-repo-repository-mssql-contrib.xml"; 054 055 // true for the Microsoft JDBC driver 056 // false for the jTDS JDBC driver (open source) 057 private static final boolean MSFT = false; 058 059 private static final String DRIVER = MSFT ? "com.microsoft.sqlserver.jdbc.SQLServerDriver" 060 : "net.sourceforge.jtds.jdbc.Driver"; 061 062 private static final String XA_DATASOURCE = MSFT ? "com.microsoft.sqlserver.jdbc.SQLServerXADataSource" 063 : "net.sourceforge.jtds.jdbcx.JtdsDataSource"; 064 065 private void setProperties() { 066 setProperty(SERVER_PROPERTY, DEF_SERVER); 067 setProperty(PORT_PROPERTY, DEF_PORT); 068 setProperty(DATABASE_PROPERTY, DEF_DATABASE); 069 setProperty(USER_PROPERTY, DEF_USER); 070 setProperty(PASSWORD_PROPERTY, DEF_PASSWORD); 071 setProperty(XA_DATASOURCE_PROPERTY, XA_DATASOURCE); 072 // for sql directory tests 073 setProperty(DRIVER_PROPERTY, DRIVER); 074 String url; 075 if (DRIVER.startsWith("com.microsoft")) { 076 url = String.format("jdbc:sqlserver://%s:%s;databaseName=%s;user=%s;password=%s", 077 Framework.getProperty(SERVER_PROPERTY), Framework.getProperty(PORT_PROPERTY), 078 Framework.getProperty(DATABASE_PROPERTY), Framework.getProperty(USER_PROPERTY), 079 Framework.getProperty(PASSWORD_PROPERTY)); 080 081 } else { 082 url = String.format("jdbc:jtds:sqlserver://%s:%s;databaseName=%s;user=%s;password=%s", 083 Framework.getProperty(SERVER_PROPERTY), Framework.getProperty(PORT_PROPERTY), 084 Framework.getProperty(DATABASE_PROPERTY), Framework.getProperty(USER_PROPERTY), 085 Framework.getProperty(PASSWORD_PROPERTY)); 086 } 087 setProperty(URL_PROPERTY, url); 088 setProperty(ID_TYPE_PROPERTY, DEF_ID_TYPE); 089 } 090 091 @Override 092 public void setUp() throws SQLException { 093 super.setUp(); 094 try { 095 Class.forName(DRIVER); 096 } catch (ReflectiveOperationException e) { 097 throw new RuntimeException(e); 098 } 099 setProperties(); 100 Connection connection = DriverManager.getConnection(Framework.getProperty(URL_PROPERTY)); 101 try { 102 doOnAllTables(connection, null, null, "DROP TABLE [%s]"); // no CASCADE... 103 checkSupports(connection); 104 // SEQUENCE in SQL Server 2012, but not Azure 105 if (supportsSequences) { 106 Statement st = connection.createStatement(); 107 executeSql(st, "IF EXISTS (SELECT 1 FROM sys.sequences WHERE name = 'hierarchy_seq')" 108 + " DROP SEQUENCE hierarchy_seq"); 109 st.close(); 110 } 111 } finally { 112 connection.close(); 113 } 114 } 115 116 @Override 117 public String getDeploymentContrib() { 118 return CONTRIB_XML; 119 } 120 121 @Override 122 public RepositoryDescriptor getRepositoryDescriptor() { 123 RepositoryDescriptor descriptor = new RepositoryDescriptor(); 124 descriptor.xaDataSourceName = XA_DATASOURCE; 125 Map<String, String> properties = new HashMap<String, String>(); 126 properties.put("ServerName", Framework.getProperty(SERVER_PROPERTY)); 127 properties.put("PortNumber", Framework.getProperty(PORT_PROPERTY)); 128 properties.put("DatabaseName", Framework.getProperty(DATABASE_PROPERTY)); 129 properties.put("User", Framework.getProperty(USER_PROPERTY)); 130 properties.put("Password", Framework.getProperty(PASSWORD_PROPERTY)); 131 properties.put("UseCursors", "true"); 132 descriptor.properties = properties; 133 descriptor.setFulltextAnalyzer("French"); 134 descriptor.setFulltextCatalog("nuxeo"); 135 descriptor.idType = Framework.getProperty(ID_TYPE_PROPERTY); 136 return descriptor; 137 } 138 139 // MS SQL Server has asynchronous indexing of fulltext 140 @Override 141 public void sleepForFulltext() { 142 try { 143 Thread.sleep(10 * 1000); 144 } catch (InterruptedException e) { 145 } 146 } 147 148 protected void checkSupports(Connection connection) throws SQLException { 149 Statement st = connection.createStatement(); 150 try { 151 ResultSet rs = st.executeQuery("SELECT SERVERPROPERTY('ProductVersion'), CONVERT(NVARCHAR(100), SERVERPROPERTY('EngineEdition'))"); 152 rs.next(); 153 String productVersion = rs.getString(1); 154 /** 9 = SQL Server 2005, 10 = SQL Server 2008, 11 = SQL Server 2012 / Azure */ 155 int majorVersion = Integer.parseInt(productVersion.split("\\.")[0]); 156 /** 5 = Azure */ 157 int engineEdition = rs.getInt(2); 158 boolean azure = engineEdition == 5; 159 supportsXA = !azure; 160 supportsSequences = majorVersion >= 11 && !azure; 161 } finally { 162 st.close(); 163 } 164 } 165 166 @Override 167 public boolean supportsMultipleFulltextIndexes() { 168 return false; 169 } 170 171 @Override 172 public boolean supportsClustering() { 173 return true; 174 } 175 176 @Override 177 public boolean supportsXA() { 178 return supportsXA; 179 } 180 181 @Override 182 public boolean supportsSoftDelete() { 183 return true; 184 } 185 186 @Override 187 public boolean supportsSequenceId() { 188 return supportsSequences; 189 } 190 191}