001/* 002 * Copyright (c) 2006-2011 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * All rights reserved. This program and the accompanying materials 005 * are made available under the terms of the Eclipse Public License v1.0 006 * which accompanies this distribution, and is available at 007 * http://www.eclipse.org/legal/epl-v10.html 008 * 009 * Contributors: 010 * Florent Guillaume 011 */ 012 013package org.nuxeo.ecm.core.storage.sql; 014 015import java.sql.Connection; 016import java.sql.DriverManager; 017import java.sql.ResultSet; 018import java.sql.SQLException; 019import java.sql.Statement; 020import java.util.HashMap; 021import java.util.Map; 022 023import org.nuxeo.runtime.api.Framework; 024 025/** 026 * @author Florent Guillaume 027 */ 028public class DatabaseSQLServer extends DatabaseHelper { 029 030 public static DatabaseHelper INSTANCE = new DatabaseSQLServer(); 031 032 private boolean supportsXA; 033 034 private boolean supportsSequences; 035 036 private static final String DEF_SERVER = "localhost"; 037 038 private static final String DEF_PORT = "1433"; 039 040 private static final String DEF_DATABASE = "nuxeojunittests"; 041 042 private static final String DEF_USER = "nuxeo"; 043 044 private static final String DEF_PASSWORD = "nuxeo"; 045 046 private static final String CONTRIB_XML = "OSGI-INF/test-repo-repository-mssql-contrib.xml"; 047 048 // true for the Microsoft JDBC driver 049 // false for the jTDS JDBC driver (open source) 050 private static final boolean MSFT = false; 051 052 private static final String DRIVER = MSFT ? "com.microsoft.sqlserver.jdbc.SQLServerDriver" 053 : "net.sourceforge.jtds.jdbc.Driver"; 054 055 private static final String XA_DATASOURCE = MSFT ? "com.microsoft.sqlserver.jdbc.SQLServerXADataSource" 056 : "net.sourceforge.jtds.jdbcx.JtdsDataSource"; 057 058 private void setProperties() { 059 Framework.getProperties().setProperty(REPOSITORY_PROPERTY, repositoryName); 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 setProperty(XA_DATASOURCE_PROPERTY, XA_DATASOURCE); 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", 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", 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 Exception { 087 super.setUp(); 088 Class.forName(DRIVER); 089 setProperties(); 090 Connection connection = DriverManager.getConnection(Framework.getProperty(URL_PROPERTY)); 091 try { 092 doOnAllTables(connection, null, null, "DROP TABLE [%s]"); // no CASCADE... 093 checkSupports(connection); 094 // SEQUENCE in SQL Server 2012, but not Azure 095 if (supportsSequences) { 096 Statement st = connection.createStatement(); 097 executeSql(st, "IF EXISTS (SELECT 1 FROM sys.sequences WHERE name = 'hierarchy_seq')" 098 + " DROP SEQUENCE hierarchy_seq"); 099 st.close(); 100 } 101 } finally { 102 connection.close(); 103 } 104 } 105 106 @Override 107 public String getDeploymentContrib() { 108 return CONTRIB_XML; 109 } 110 111 @Override 112 public RepositoryDescriptor getRepositoryDescriptor() { 113 RepositoryDescriptor descriptor = new RepositoryDescriptor(); 114 descriptor.xaDataSourceName = XA_DATASOURCE; 115 Map<String, String> properties = new HashMap<String, String>(); 116 properties.put("ServerName", Framework.getProperty(SERVER_PROPERTY)); 117 properties.put("PortNumber", Framework.getProperty(PORT_PROPERTY)); 118 properties.put("DatabaseName", Framework.getProperty(DATABASE_PROPERTY)); 119 properties.put("User", Framework.getProperty(USER_PROPERTY)); 120 properties.put("Password", Framework.getProperty(PASSWORD_PROPERTY)); 121 properties.put("UseCursors", "true"); 122 descriptor.properties = properties; 123 descriptor.fulltextAnalyzer = "French"; 124 descriptor.fulltextCatalog = "nuxeo"; 125 descriptor.idType = Framework.getProperty(ID_TYPE_PROPERTY); 126 return descriptor; 127 } 128 129 // MS SQL Server has asynchronous indexing of fulltext 130 @Override 131 public void sleepForFulltext() { 132 try { 133 Thread.sleep(10 * 1000); 134 } catch (InterruptedException e) { 135 } 136 } 137 138 protected void checkSupports(Connection connection) throws SQLException { 139 Statement st = connection.createStatement(); 140 try { 141 ResultSet rs = st.executeQuery("SELECT SERVERPROPERTY('ProductVersion'), CONVERT(NVARCHAR(100), SERVERPROPERTY('EngineEdition'))"); 142 rs.next(); 143 String productVersion = rs.getString(1); 144 /** 9 = SQL Server 2005, 10 = SQL Server 2008, 11 = SQL Server 2012 / Azure */ 145 int majorVersion = Integer.parseInt(productVersion.split("\\.")[0]); 146 /** 5 = Azure */ 147 int engineEdition = rs.getInt(2); 148 boolean azure = engineEdition == 5; 149 supportsXA = !azure; 150 supportsSequences = majorVersion >= 11 && !azure; 151 } finally { 152 st.close(); 153 } 154 } 155 156 @Override 157 public boolean supportsMultipleFulltextIndexes() { 158 return false; 159 } 160 161 @Override 162 public boolean supportsClustering() { 163 return true; 164 } 165 166 @Override 167 public boolean supportsXA() { 168 return supportsXA; 169 } 170 171 @Override 172 public boolean supportsSoftDelete() { 173 return true; 174 } 175 176 @Override 177 public boolean supportsSequenceId() { 178 return supportsSequences; 179 } 180 181}