mcs/class/System.Data/Test/ProviderTests/Common/ConnectionManager.cs

   1 // ConnectionManager.cs - Singleton ConnectionManager class to manage
   2 // database connections for test cases.
   3 //
   4 // Authors:
   5 //      Sureshkumar T (tsureshkumar@novell.com)
   6 //
   7 // Copyright Novell Inc., and the individuals listed on the
   8 // ChangeLog entries.
   9 //
  10 //
  11 // Permission is hereby granted, free of charge, to any person
  12 // obtaining a copy of this software and associated documentation
  13 // files (the "Software"), to deal in the Software without
  14 // restriction, including without limitation the rights to use, copy,
  15 // modify, merge, publish, distribute, sublicense, and/or sell copies
  16 // of the Software, and to permit persons to whom the Software is
  17 // furnished to do so, subject to the following conditions:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
  26 // BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
  27 // ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
  28 // CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
  29 // SOFTWARE.
  30
  31 using System;
  32 using System.Collections.Generic;
  33 using System.Data;
  34 using System.Data.Common;
  35 #if !NO_ODBC
  36 using System.Data.Odbc;
  37 #endif
  38 using System.Data.SqlClient;
  39 using System.IO;
  40 using System.Linq;
  41 using System.Text.RegularExpressions;
  42 using NUnit.Framework;
  43
  44 namespace MonoTests.System.Data.Connected
  45 {
  46         public class ConnectionManager
  47         {
  48                 private static ConnectionManager instance;
  49                 private ConnectionHolder<SqlConnection> sql;
  50
  51                 private const string OdbcEnvVar = "SYSTEM_DATA_ODBC_V2";
  52                 private const string SqlEnvVar = "SYSTEM_DATA_MSSQL_V2";
  53
  54                 private ConnectionManager ()
  55                 {
  56                         //Environment.SetEnvironmentVariable(OdbcEnvVar, @"Driver={MySQL ODBC 5.3 Unicode Driver};server=127.0.0.1;uid=sa;pwd=qwerty123;");
  57                         //Environment.SetEnvironmentVariable(SqlEnvVar, @"server=127.0.0.1;database=master;user id=sa;password=qwerty123");
  58
  59                         // Generate a random db name
  60                         DatabaseName = "monotest" + Guid.NewGuid().ToString().Substring(0, 7);
  61
  62                         sql = CreateSqlConfig (SqlEnvVar);
  63                         if (sql != null)
  64                                 CreateMssqlDatabase();
  65
  66 #if !NO_ODBC
  67                         odbc = CreateOdbcConfig (OdbcEnvVar);
  68                         if (odbc != null)
  69                                 CreateMysqlDatabase();
  70 #endif
  71                 }
  72
  73                 static ConnectionHolder<SqlConnection> CreateSqlConfig (string envVarName)
  74                 {
  75                         string connectionString = Environment.GetEnvironmentVariable (envVarName);
  76                         if (string.IsNullOrEmpty (connectionString))
  77                                 return null;
  78
  79                         SqlConnection connection;
  80 #if MOBILE
  81                         connection = new SqlConnection ();
  82 #else
  83                         DbProviderFactory factory = DbProviderFactories.GetFactory ("System.Data.SqlClient");
  84                         connection = (SqlConnection)factory.CreateConnection ();
  85 #endif
  86
  87                         var engine = new EngineConfig {
  88                                 Type = EngineType.SQLServer,
  89                                 ClientVersion = 9,
  90                                 QuoteCharacter = "&quot;",
  91                                 SupportsMicroseconds = true,
  92                                 SupportsUniqueIdentifier = true,
  93                                 SupportsTimestamp = true,
  94                         };
  95
  96                         return new ConnectionHolder<SqlConnection> (engine, connection, connectionString);
  97                 }
  98
  99 #if !NO_ODBC
 100                 static ConnectionHolder<OdbcConnection> CreateOdbcConfig (string envVarName)
 101                 {
 102                         string connectionString = Environment.GetEnvironmentVariable (envVarName);
 103                         if (string.IsNullOrEmpty (connectionString))
 104                                 return null;
 105
 106                         DbProviderFactory factory = DbProviderFactories.GetFactory ("System.Data.Odbc");
 107                         var connection = (OdbcConnection)factory.CreateConnection ();
 108
 109                         var engine = new EngineConfig {
 110                                 Type = EngineType.MySQL,
 111                                 QuoteCharacter = "`",
 112                                 RemovesTrailingSpaces = true,
 113                                 EmptyBinaryAsNull = true,
 114                                 SupportsDate = true,
 115                                 SupportsTime = true
 116                         };
 117
 118                         return new ConnectionHolder<OdbcConnection> (engine, connection, connectionString);
 119                 }
 120 #endif
 121
 122                 private void CreateMssqlDatabase()
 123                 {
 124                         DBHelper.ExecuteNonQuery(sql.Connection, $"CREATE DATABASE [{DatabaseName}]");
 125                         sql.ConnectionString = sql.ConnectionString.Replace(sql.Connection.Database, DatabaseName);
 126                         sql.CloseConnection();
 127
 128                         string query = File.ReadAllText(@"Test/ProviderTests/sql/sqlserver.sql");
 129
 130                         var queries = SplitSqlStatements(query);
 131                         foreach (var subQuery in queries)
 132                         {
 133                                 DBHelper.ExecuteNonQuery(sql.Connection, subQuery);
 134                         }
 135                 }
 136
 137 #if !NO_ODBC
 138                 private void CreateMysqlDatabase()
 139                 {
 140                         DBHelper.ExecuteNonQuery(odbc.Connection, $"CREATE DATABASE {DatabaseName}");
 141                         odbc.Connection.ChangeDatabase(DatabaseName);
 142                         odbc.ConnectionString += $"database={DatabaseName}";
 143
 144                         string query = File.ReadAllText("Test/ProviderTests/sql/MySQL_5.sql");
 145
 146                         var groups = query.Replace("delimiter ", "")
 147                                 .Split(new[] { "//\n" }, StringSplitOptions.RemoveEmptyEntries);
 148
 149                         foreach (var subQuery in groups[0].Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries).Concat(groups.Skip(1)))
 150                         {
 151                                 DBHelper.ExecuteNonQuery(odbc.Connection, subQuery);
 152                         }
 153                 }
 154 #endif
 155
 156                 private void DropMssqlDatabase()
 157                 {
 158                         sql.Connection.ChangeDatabase("master");
 159                         string query = $"ALTER DATABASE [{DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\nDROP DATABASE [{DatabaseName}]";
 160                         DBHelper.ExecuteNonQuery(sql.Connection, query);
 161                 }
 162
 163 #if !NO_ODBC
 164                 private void DropMysqlDatabase()
 165                 {
 166                         string query = $"DROP DATABASE [{DatabaseName}]";
 167                         DBHelper.ExecuteNonQuery(odbc.Connection, query);
 168                 }
 169 #endif
 170
 171                 // Split SQL script by "GO" statements
 172                 private static IEnumerable<string> SplitSqlStatements(string sqlScript)
 173                 {
 174                         var statements = Regex.Split(sqlScript,
 175                                         $@"^[\t ]*GO[\t ]*\d*[\t ]*(?:--.*)?$",
 176                                         RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);
 177                         return statements.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x.Trim(' ', '\r', '\n'));
 178                 }
 179
 180                 public static ConnectionManager Instance => instance ?? (instance = new ConnectionManager());
 181
 182                 public string DatabaseName { get; }
 183
 184 #if !NO_ODBC
 185
 186                 private ConnectionHolder<OdbcConnection> odbc;
 187
 188                 public ConnectionHolder<OdbcConnection> Odbc
 189                 {
 190                         get
 191                         {
 192                                 if (odbc == null)
 193                                         Assert.Ignore($"{OdbcEnvVar} environment variable is not set");
 194                                 return odbc;
 195                         }
 196                 }
 197 #endif
 198
 199                 public ConnectionHolder<SqlConnection> Sql
 200                 {
 201                         get
 202                         {
 203                                 if (sql == null)
 204                                         Assert.Ignore($"{SqlEnvVar} environment variable is not set");
 205                                 return sql;
 206                         }
 207                 }
 208
 209                 public void Close()
 210                 {
 211                         sql?.CloseConnection();
 212 #if !NO_ODBC
 213                         odbc?.CloseConnection();
 214 #endif
 215                 }
 216         }
 217
 218         public class ConnectionHolder<TConnection> where TConnection : DbConnection
 219         {
 220                 private TConnection connection;
 221
 222                 public EngineConfig EngineConfig { get; }
 223
 224                 public TConnection Connection
 225                 {
 226                         get
 227                         {
 228                                 if (!(connection.State == ConnectionState.Closed ||
 229                                         connection.State == ConnectionState.Broken))
 230                                         connection.Close();
 231                                 connection.ConnectionString = ConnectionString;
 232                                 connection.Open();
 233                                 return connection;
 234                         }
 235                 }
 236
 237                 public void CloseConnection()
 238                 {
 239                         if (connection != null && connection.State != ConnectionState.Closed)
 240                                 connection.Close();
 241                 }
 242
 243                 public string ConnectionString { get; set; }
 244
 245                 public ConnectionHolder(EngineConfig engineConfig, TConnection connection, string connectionString)
 246                 {
 247                         EngineConfig = engineConfig;
 248                         this.connection = connection;
 249                         ConnectionString = connectionString;
 250                 }
 251
 252                 public bool IsAzure => ConnectionString.ToLower().Contains("database.windows.net");
 253         }
 254 }