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; 027 028import org.nuxeo.ecm.core.api.NuxeoException; 029import org.nuxeo.runtime.api.Framework; 030 031/** 032 * @author Florent Guillaume 033 */ 034public class DatabaseSQLServer extends DatabaseHelper { 035 036 private boolean supportsXA; 037 038 private boolean supportsSequences; 039 040 private static final String DEF_SERVER = "localhost"; 041 042 private static final String DEF_PORT = "1433"; 043 044 private static final String DEF_DATABASE = "nuxeojunittests"; 045 046 private static final String DEF_USER = "nuxeo"; 047 048 private static final String DEF_PASSWORD = "nuxeo"; 049 050 private static final String CONTRIB_XML = "OSGI-INF/test-repo-repository-mssql-contrib.xml"; 051 052 // true for the Microsoft JDBC driver 053 // false for the jTDS JDBC driver (open source) 054 private static final boolean MSFT = true; 055 056 private static final String DRIVER = MSFT ? "com.microsoft.sqlserver.jdbc.SQLServerDriver" 057 : "net.sourceforge.jtds.jdbc.Driver"; 058 059 private void setProperties() { 060 setProperty(SERVER_PROPERTY, DEF_SERVER); 061 setProperty(PORT_PROPERTY, DEF_PORT); 062 setProperty(DATABASE_PROPERTY, DEF_DATABASE); 063 setProperty(USER_PROPERTY, DEF_USER); 064 setProperty(PASSWORD_PROPERTY, DEF_PASSWORD); 065 // for sql directory tests 066 setProperty(DRIVER_PROPERTY, DRIVER); 067 String url; 068 if (DRIVER.startsWith("com.microsoft")) { 069 url = String.format("jdbc:sqlserver://%s:%s;databaseName=%s;user=%s;password=%s;selectMethod=cursor", 070 Framework.getProperty(SERVER_PROPERTY), Framework.getProperty(PORT_PROPERTY), 071 Framework.getProperty(DATABASE_PROPERTY), Framework.getProperty(USER_PROPERTY), 072 Framework.getProperty(PASSWORD_PROPERTY)); 073 074 } else { 075 url = String.format("jdbc:jtds:sqlserver://%s:%s;databaseName=%s;user=%s;password=%s;useCursors=true", 076 Framework.getProperty(SERVER_PROPERTY), Framework.getProperty(PORT_PROPERTY), 077 Framework.getProperty(DATABASE_PROPERTY), Framework.getProperty(USER_PROPERTY), 078 Framework.getProperty(PASSWORD_PROPERTY)); 079 } 080 setProperty(URL_PROPERTY, url); 081 setProperty(ID_TYPE_PROPERTY, DEF_ID_TYPE); 082 } 083 084 @Override 085 public void setUp() throws SQLException { 086 super.setUp(); 087 try { 088 Class.forName(DRIVER); 089 } catch (ReflectiveOperationException e) { 090 throw new RuntimeException(e); 091 } 092 setProperties(); 093 Connection connection = DriverManager.getConnection(Framework.getProperty(URL_PROPERTY)); 094 try { 095 doOnAllTables(connection, null, null, "DROP TABLE [%s]"); // no CASCADE... 096 checkSupports(connection); 097 // SEQUENCE in SQL Server 2012, but not Azure 098 if (supportsSequences) { 099 Statement st = connection.createStatement(); 100 executeSql(st, "IF EXISTS (SELECT 1 FROM sys.sequences WHERE name = 'hierarchy_seq')" 101 + " DROP SEQUENCE hierarchy_seq"); 102 st.close(); 103 } 104 } finally { 105 connection.close(); 106 } 107 } 108 109 @Override 110 public String getDeploymentContrib() { 111 return CONTRIB_XML; 112 } 113 114 @Override 115 public RepositoryDescriptor getRepositoryDescriptor() { 116 RepositoryDescriptor descriptor = new RepositoryDescriptor(); 117 descriptor.setFulltextAnalyzer("French"); 118 descriptor.setFulltextCatalog("nuxeo"); 119 descriptor.idType = Framework.getProperty(ID_TYPE_PROPERTY); 120 return descriptor; 121 } 122 123 // MS SQL Server has asynchronous indexing of fulltext 124 @Override 125 public void sleepForFulltext() { 126 try { 127 Thread.sleep(10 * 1000); 128 } catch (InterruptedException e) { 129 Thread.currentThread().interrupt(); 130 throw new NuxeoException(e); 131 } 132 } 133 134 protected void checkSupports(Connection connection) throws SQLException { 135 try (Statement st = connection.createStatement(); 136 ResultSet rs = st.executeQuery( 137 "SELECT CONVERT(NVARCHAR(100),SERVERPROPERTY('ProductVersion')), CONVERT(NVARCHAR(100), SERVERPROPERTY('EngineEdition'))")) { 138 rs.next(); 139 String productVersion = rs.getString(1); 140 /** 9 = SQL Server 2005, 10 = SQL Server 2008, 11 = SQL Server 2012 / Azure */ 141 int majorVersion = Integer.parseInt(productVersion.split("\\.")[0]); 142 /** 5 = Azure */ 143 int engineEdition = rs.getInt(2); 144 boolean azure = engineEdition == 5; 145 supportsXA = !azure; 146 supportsSequences = majorVersion >= 11 && !azure; 147 } 148 } 149 150 @Override 151 public boolean supportsMultipleFulltextIndexes() { 152 return false; 153 } 154 155 @Override 156 public boolean supportsClustering() { 157 return true; 158 } 159 160 @Override 161 public boolean supportsXA() { 162 return supportsXA; 163 } 164 165 @Override 166 public boolean supportsSoftDelete() { 167 return true; 168 } 169 170 @Override 171 public boolean supportsSequenceId() { 172 return supportsSequences; 173 } 174 175}