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