review.tizen.org Git - platform/upstream/dotnet/runtime.git/blob

   1 // Licensed to the .NET Foundation under one or more agreements.
   2 // The .NET Foundation licenses this file to you under the MIT license.
   3 // See the LICENSE file in the project root for more information.
   4
   5 using System.Threading;
   6 using Xunit;
   7
   8 namespace System.Data.SqlClient.ManualTesting.Tests
   9 {
  10     public class SqlNotificationTest : IDisposable
  11     {
  12         // Misc constants
  13         private const int CALLBACK_TIMEOUT = 5000; // milliseconds
  14
  15         // Database schema
  16         private readonly string _tableName   = $"dbo.[SQLDEP_{Guid.NewGuid().ToString()}]";
  17         private readonly string _queueName   = $"SQLDEP_{Guid.NewGuid().ToString()}";
  18         private readonly string _serviceName = $"SQLDEP_{Guid.NewGuid().ToString()}";
  19         private readonly string _schemaQueue;
  20
  21         // Connection information used by all tests
  22         private readonly string _startConnectionString;
  23         private readonly string _execConnectionString;
  24
  25         public SqlNotificationTest()
  26         {
  27             _startConnectionString = DataTestUtility.TcpConnStr;
  28             _execConnectionString = DataTestUtility.TcpConnStr;
  29
  30             _schemaQueue = $"[{_queueName}]";
  31
  32             Setup();
  33         }
  34
  35         public void Dispose()
  36         {
  37             Cleanup();
  38         }
  39
  40         #region StartStop_Tests
  41
  42         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
  43         public void Test_DoubleStart_SameConnStr()
  44         {
  45             Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener.");
  46
  47             Assert.False(SqlDependency.Start(_startConnectionString), "Expected failure when trying to start listener.");
  48
  49             Assert.False(SqlDependency.Stop(_startConnectionString), "Expected failure when trying to completely stop listener.");
  50
  51             Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener.");
  52         }
  53
  54         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
  55         public void Test_DoubleStart_DifferentConnStr()
  56         {
  57             SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(_startConnectionString);
  58
  59             // just change something that doesn't impact the dependency dispatcher
  60             if (cb.ShouldSerialize("connect timeout"))
  61                 cb.ConnectTimeout = cb.ConnectTimeout + 1;
  62             else
  63                 cb.ConnectTimeout = 50;
  64
  65             Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener.");
  66
  67             try
  68             {
  69                 DataTestUtility.AssertThrowsWrapper<InvalidOperationException>(() => SqlDependency.Start(cb.ToString()));
  70             }
  71             finally
  72             {
  73                 Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener.");
  74
  75                 Assert.False(SqlDependency.Stop(cb.ToString()), "Expected failure when trying to completely stop listener.");
  76             }
  77         }
  78
  79         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
  80         public void Test_Start_DifferentDB()
  81         {
  82             SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(_startConnectionString)
  83             {
  84                 InitialCatalog = "tempdb"
  85             };
  86             string altDatabaseConnectionString = cb.ToString();
  87
  88             Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener.");
  89
  90             Assert.True(SqlDependency.Start(altDatabaseConnectionString), "Failed to start listener.");
  91
  92             Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener.");
  93
  94             Assert.True(SqlDependency.Stop(altDatabaseConnectionString), "Failed to stop listener.");
  95         }
  96         #endregion
  97
  98         #region SqlDependency_Tests
  99
 100         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
 101         public void Test_SingleDependency_NoStart()
 102         {
 103             using (SqlConnection conn = new SqlConnection(_execConnectionString))
 104             using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn))
 105             {
 106                 conn.Open();
 107
 108                 SqlDependency dep = new SqlDependency(cmd);
 109                 dep.OnChange += delegate (object o, SqlNotificationEventArgs args)
 110                 {
 111                     Console.WriteLine("4 Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
 112                 };
 113
 114                 DataTestUtility.AssertThrowsWrapper<InvalidOperationException>(() => cmd.ExecuteReader());
 115             }
 116         }
 117
 118         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
 119         public void Test_SingleDependency_Stopped()
 120         {
 121             SqlDependency.Start(_startConnectionString);
 122             SqlDependency.Stop(_startConnectionString);
 123
 124             using (SqlConnection conn = new SqlConnection(_execConnectionString))
 125             using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn))
 126             {
 127                 conn.Open();
 128
 129                 SqlDependency dep = new SqlDependency(cmd);
 130                 dep.OnChange += delegate (object o, SqlNotificationEventArgs args)
 131                 {
 132                     // Delegate won't be called, since notifications were stoppped
 133                     Console.WriteLine("5 Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
 134                 };
 135
 136                 DataTestUtility.AssertThrowsWrapper<InvalidOperationException>(() => cmd.ExecuteReader());
 137             }
 138         }
 139
 140         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
 141         public void Test_SingleDependency_AllDefaults_SqlAuth()
 142         {
 143             Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener.");
 144
 145             try
 146             {
 147                 // create a new event every time to avoid mixing notification callbacks
 148                 ManualResetEvent notificationReceived = new ManualResetEvent(false);
 149                 ManualResetEvent updateCompleted = new ManualResetEvent(false);
 150
 151                 using (SqlConnection conn = new SqlConnection(_execConnectionString))
 152                 using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn))
 153                 {
 154                     conn.Open();
 155
 156                     SqlDependency dep = new SqlDependency(cmd);
 157                     dep.OnChange += delegate (object o, SqlNotificationEventArgs arg)
 158                     {
 159                         Assert.True(updateCompleted.WaitOne(CALLBACK_TIMEOUT, false), "Received notification, but update did not complete.");
 160
 161                         DataTestUtility.AssertEqualsWithDescription(SqlNotificationType.Change, arg.Type, "Unexpected Type value.");
 162                         DataTestUtility.AssertEqualsWithDescription(SqlNotificationInfo.Update, arg.Info, "Unexpected Info value.");
 163                         DataTestUtility.AssertEqualsWithDescription(SqlNotificationSource.Data, arg.Source, "Unexpected Source value.");
 164
 165                         notificationReceived.Set();
 166                     };
 167
 168                     cmd.ExecuteReader();
 169                 }
 170
 171                 int count = RunSQL("UPDATE " + _tableName + " SET c=" + Environment.TickCount);
 172                 DataTestUtility.AssertEqualsWithDescription(1, count, "Unexpected count value.");
 173
 174                 updateCompleted.Set();
 175
 176                 Assert.True(notificationReceived.WaitOne(CALLBACK_TIMEOUT, false), "Notification not received within the timeout period");
 177             }
 178             finally
 179             {
 180                 Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener.");
 181             }
 182         }
 183
 184         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
 185         public void Test_SingleDependency_CustomQueue_SqlAuth()
 186         {
 187             Assert.True(SqlDependency.Start(_startConnectionString, _queueName), "Failed to start listener.");
 188
 189             try
 190             {
 191                 // create a new event every time to avoid mixing notification callbacks
 192                 ManualResetEvent notificationReceived = new ManualResetEvent(false);
 193                 ManualResetEvent updateCompleted = new ManualResetEvent(false);
 194
 195                 using (SqlConnection conn = new SqlConnection(_execConnectionString))
 196                 using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn))
 197                 {
 198                     conn.Open();
 199
 200                     SqlDependency dep = new SqlDependency(cmd, "service=" + _serviceName + ";local database=msdb", 0);
 201                     dep.OnChange += delegate (object o, SqlNotificationEventArgs args)
 202                     {
 203                         Assert.True(updateCompleted.WaitOne(CALLBACK_TIMEOUT, false), "Received notification, but update did not complete.");
 204
 205                         Console.WriteLine("7 Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
 206                         notificationReceived.Set();
 207                     };
 208
 209                     cmd.ExecuteReader();
 210                 }
 211
 212                 int count = RunSQL("UPDATE " + _tableName + " SET c=" + Environment.TickCount);
 213                 DataTestUtility.AssertEqualsWithDescription(1, count, "Unexpected count value.");
 214
 215                 updateCompleted.Set();
 216
 217                 Assert.False(notificationReceived.WaitOne(CALLBACK_TIMEOUT, false), "Notification should not be received.");
 218             }
 219             finally
 220             {
 221                 Assert.True(SqlDependency.Stop(_startConnectionString, _queueName), "Failed to stop listener.");
 222             }
 223         }
 224
 225         /// <summary>
 226         /// SqlDependecy premature timeout
 227         /// </summary>
 228         [ConditionalFact(typeof(DataTestUtility), nameof(DataTestUtility.AreConnStringsSetup))]
 229         public void Test_SingleDependency_Timeout()
 230         {
 231             Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener.");
 232
 233             try
 234             {
 235                 // with resolution of 15 seconds, SqlDependency should fire timeout notification only after 45 seconds, leave 5 seconds gap from both sides.
 236                 const int SqlDependencyTimerResolution = 15; // seconds
 237                 const int testTimeSeconds = SqlDependencyTimerResolution * 3 - 5;
 238                 const int minTimeoutEventInterval = testTimeSeconds - 1;
 239                 const int maxTimeoutEventInterval = testTimeSeconds + SqlDependencyTimerResolution + 1;
 240
 241                 // create a new event every time to avoid mixing notification callbacks
 242                 ManualResetEvent notificationReceived = new ManualResetEvent(false);
 243                 DateTime startUtcTime;
 244
 245                 using (SqlConnection conn = new SqlConnection(_execConnectionString))
 246                 using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn))
 247                 {
 248                     conn.Open();
 249
 250                     // create SqlDependency with timeout
 251                     SqlDependency dep = new SqlDependency(cmd, null, testTimeSeconds);
 252                     dep.OnChange += delegate (object o, SqlNotificationEventArgs arg)
 253                     {
 254                         // notification of Timeout can arrive either from server or from client timer. Handle both situations here:
 255                         SqlNotificationInfo info = arg.Info;
 256                         if (info == SqlNotificationInfo.Unknown)
 257                         {
 258                             // server timed out before the client, replace it with Error to produce consistent output for trun
 259                             info = SqlNotificationInfo.Error;
 260                         }
 261
 262                         DataTestUtility.AssertEqualsWithDescription(SqlNotificationType.Change, arg.Type, "Unexpected Type value.");
 263                         DataTestUtility.AssertEqualsWithDescription(SqlNotificationInfo.Error, arg.Info, "Unexpected Info value.");
 264                         DataTestUtility.AssertEqualsWithDescription(SqlNotificationSource.Timeout, arg.Source, "Unexpected Source value.");
 265                         notificationReceived.Set();
 266                     };
 267
 268                     cmd.ExecuteReader();
 269                     startUtcTime = DateTime.UtcNow;
 270                 }
 271
 272                 Assert.True(
 273                     notificationReceived.WaitOne(TimeSpan.FromSeconds(maxTimeoutEventInterval), false),
 274                     string.Format("Notification not received within the maximum timeout period of {0} seconds", maxTimeoutEventInterval));
 275
 276                 // notification received in time, check that it is not too early
 277                 TimeSpan notificationTime = DateTime.UtcNow - startUtcTime;
 278                 Assert.True(
 279                     notificationTime >= TimeSpan.FromSeconds(minTimeoutEventInterval),
 280                     string.Format(
 281                         "Notification was not expected before {0} seconds: received after {1} seconds",
 282                         minTimeoutEventInterval, notificationTime.TotalSeconds));
 283             }
 284             finally
 285             {
 286                 Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener.");
 287             }
 288         }
 289
 290         #endregion
 291
 292         #region Utility_Methods
 293         private static string[] CreateSqlSetupStatements(string tableName, string queueName, string serviceName)
 294         {
 295             return new string[] {
 296                 string.Format("CREATE TABLE {0}(a INT NOT NULL, b NVARCHAR(10), c INT NOT NULL)", tableName),
 297                 string.Format("INSERT INTO {0} (a, b, c) VALUES (1, 'foo', 0)", tableName),
 298                 string.Format("CREATE QUEUE {0}", queueName),
 299                 string.Format("CREATE SERVICE [{0}] ON QUEUE {1} ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])", serviceName, queueName)
 300             };
 301         }
 302
 303         private static string[] CreateSqlCleanupStatements(string tableName, string queueName, string serviceName)
 304         {
 305             return new string[] {
 306                 string.Format("DROP TABLE {0}", tableName),
 307                 string.Format("DROP SERVICE [{0}]", serviceName),
 308                 string.Format("DROP QUEUE {0}", queueName)
 309             };
 310         }
 311
 312         private void Setup()
 313         {
 314             RunSQL(CreateSqlSetupStatements(_tableName, _schemaQueue, _serviceName));
 315         }
 316
 317         private void Cleanup()
 318         {
 319             RunSQL(CreateSqlCleanupStatements(_tableName, _schemaQueue, _serviceName));
 320         }
 321
 322         private int RunSQL(params string[] stmts)
 323         {
 324             int count = -1;
 325             using (SqlConnection conn = new SqlConnection(_execConnectionString))
 326             {
 327                 conn.Open();
 328
 329                 SqlCommand cmd = conn.CreateCommand();
 330
 331                 foreach (string stmt in stmts)
 332                 {
 333                     cmd.CommandText = stmt;
 334                     int tmp = cmd.ExecuteNonQuery();
 335                     count = ((0 <= tmp) ? ((0 <= count) ? count + tmp : tmp) : count);
 336                 }
 337             }
 338             return count;
 339         }
 340
 341         #endregion
 342     }
 343 }