/// <summary> /// Executes the given script in a test context. /// </summary> /// <param name="setupScript">the script. usually CreateOrleansTables_xxxx.sql</param> /// <param name="dataBaseName">the target database to be populated</param> /// <returns></returns> private async Task ExecuteSetupScript(string setupScript, string dataBaseName) { var splitScripts = ConvertToExecutableBatches(setupScript, dataBaseName); foreach (var script in splitScripts) { var res1 = await storage.ExecuteAsync(script); } }
/// <summary> /// Uses <see cref="IRelationalStorage"/> with <see cref="DbExtensions.ReflectionSelector{TResult}(System.Data.IDataRecord)"/>. /// </summary> /// <param name="storage">The storage to use.</param> /// <param name="query">Executes a given statement. Especially intended to use with <em>INSERT</em>, <em>UPDATE</em>, <em>DELETE</em> or <em>DDL</em> queries.</param> /// <param name="parameters">Adds parameters to the query. Parameter names must match those defined in the query.</param> /// <returns>Affected rows count.</returns> /// <example>This uses reflection to provide parameters to an execute /// query that reads only affected rows count if available. /// <code> /// //Here reflection (<seealso cref="DbExtensions.ReflectionParameterProvider{T}(IDbCommand, T, IReadOnlyDictionary{string, string})"/>) /// is used to match parameter names as well as to read back the results (<seealso cref="DbExtensions.ReflectionSelector{TResult}(IDataRecord)"/>). /// var query = "IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tname) CREATE TABLE Test(Id INT PRIMARY KEY IDENTITY(1, 1) NOT NULL);" /// await db.ExecuteAsync(query, new { tname = "test_table" }); /// </code> /// </example> public static async Task <int> ExecuteAsync(this IRelationalStorage storage, string query, object parameters) { return(await storage.ExecuteAsync(query, command => { if (parameters != null) { command.ReflectionParameterProvider(parameters); } }).ConfigureAwait(continueOnCapturedContext: false)); }
/// <summary> /// Uses <see cref="IRelationalStorage"/> with <see cref="DbExtensions.ReflectionSelector{TResult}(System.Data.IDataRecord)"/>. /// </summary> /// <param name="storage">The storage to use.</param> /// <param name="query">Executes a given statement. Especially intended to use with <em>INSERT</em>, <em>UPDATE</em>, <em>DELETE</em> or <em>DDL</em> queries.</param> /// <param name="parameters">Adds parameters to the query. Parameter names must match those defined in the query.</param> /// <param name="cancellationToken">The cancellation token. Defaults to <see cref="CancellationToken.None"/>.</param> /// <returns>Affected rows count.</returns> /// <example>This uses reflection to provide parameters to an execute /// query that reads only affected rows count if available. /// <code> /// //Here reflection (<seealso cref="DbExtensions.ReflectionParameterProvider{T}(IDbCommand, T, IReadOnlyDictionary{string, string})"/>) /// is used to match parameter names as well as to read back the results (<seealso cref="DbExtensions.ReflectionSelector{TResult}(IDataRecord)"/>). /// var query = "IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tname) CREATE TABLE Test(Id INT PRIMARY KEY IDENTITY(1, 1) NOT NULL);" /// await db.ExecuteAsync(query, new { tname = "test_table" }); /// </code> /// </example> public static Task <int> ExecuteAsync(this IRelationalStorage storage, string query, object parameters, CancellationToken cancellationToken = default(CancellationToken)) { return(storage.ExecuteAsync(query, command => { if (parameters != null) { command.ReflectionParameterProvider(parameters); } }, cancellationToken)); }
public static void ClassInitialize(TestContext context) { Console.WriteLine("TestContext.DeploymentDirectory={0}", context.DeploymentDirectory); Console.WriteLine("TestContext="); Console.WriteLine(DumpTestContext(context)); Console.WriteLine("Initializing relational databases..."); relationalStorage = RelationalStorageUtilities.CreateDefaultSqlServerStorageInstance(); Console.WriteLine("Dropping and recreating database '{0}' with connectionstring '{1}'", testDatabaseName, relationalStorage.ConnectionString); if (relationalStorage.ExistsDatabaseAsync(testDatabaseName).Result) { relationalStorage.DropDatabaseAsync(testDatabaseName).Wait(); } relationalStorage.CreateDatabaseAsync(testDatabaseName).Wait(); //The old storage instance has the previous connection string, time have a new handle with a new connection string... relationalStorage = relationalStorage.CreateNewStorageInstance(testDatabaseName); Console.WriteLine("Creating database tables..."); var creationScripts = RelationalStorageUtilities.RemoveBatchSeparators(File.ReadAllText("CreateOrleansTables_SqlServer.sql")); foreach (var creationScript in creationScripts) { var res = relationalStorage.ExecuteAsync(creationScript).Result; } //Currently there's only one database under test, SQL Server. So this as the other //setup is hardcoded here: putting the database in simple recovery mode. //This removes the use of recovery log in case of database crashes, which //improves performance to some degree, depending on usage. For non-performance testing only. var simpleModeRes = relationalStorage.ExecuteAsync(string.Format("ALTER DATABASE [{0}] SET RECOVERY SIMPLE;", testDatabaseName)).Result; Console.WriteLine("Initializing relational databases done."); siloOptions.DataConnectionString = relationalStorage.ConnectionString; }
public static void ClassInitialize(TestContext testContext) { TraceLogger.Initialize(new NodeConfiguration()); TraceLogger.AddTraceLevelOverride("SQLMembershipTableTests", Logger.Severity.Verbose3); // Set shorter init timeout for these tests OrleansSiloInstanceManager.initTimeout = TimeSpan.FromSeconds(20); Console.WriteLine("Initializing relational databases..."); relationalStorage = RelationalStorageUtilities.CreateDefaultSqlServerStorageInstance(); Console.WriteLine("Dropping and recreating database '{0}' with connectionstring '{1}'", testDatabaseName, relationalStorage.ConnectionString); if (relationalStorage.ExistsDatabaseAsync(testDatabaseName).Result) { relationalStorage.DropDatabaseAsync(testDatabaseName).Wait(); } relationalStorage.CreateDatabaseAsync(testDatabaseName).Wait(); //The old storage instance has the previous connection string, time have a new handle with a new connection string... relationalStorage = relationalStorage.CreateNewStorageInstance(testDatabaseName); Console.WriteLine("Creating database tables..."); var creationScripts = RelationalStorageUtilities.RemoveBatchSeparators(File.ReadAllText("CreateOrleansTables_SqlServer.sql")); foreach (var creationScript in creationScripts) { var res = relationalStorage.ExecuteAsync(creationScript).Result; } //Currently there's only one database under test, SQL Server. So this as the other //setup is hardcoded here: putting the database in simple recovery mode. //This removes the use of recovery log in case of database crashes, which //improves performance to some degree, depending on usage. For non-performance testing only. var simpleModeRes = relationalStorage.ExecuteAsync(string.Format("ALTER DATABASE [{0}] SET RECOVERY SIMPLE;", testDatabaseName)).Result; Console.WriteLine("Initializing relational databases done."); }
public static void ClassInitialize(TestContext testContext) { TraceLogger.Initialize(new NodeConfiguration()); TraceLogger.AddTraceLevelOverride("SQLMembershipTableTests", Logger.Severity.Verbose3); // Set shorter init timeout for these tests OrleansSiloInstanceManager.initTimeout = TimeSpan.FromSeconds(20); Console.WriteLine("Initializing relational databases..."); relationalStorage = RelationalStorageUtilities.CreateDefaultSqlServerStorageInstance(); Console.WriteLine("Dropping and recreating database '{0}' with connectionstring '{1}'", testDatabaseName, relationalStorage.ConnectionString); if(relationalStorage.ExistsDatabaseAsync(testDatabaseName).Result) { relationalStorage.DropDatabaseAsync(testDatabaseName).Wait(); } relationalStorage.CreateDatabaseAsync(testDatabaseName).Wait(); //The old storage instance has the previous connection string, time have a new handle with a new connection string... relationalStorage = relationalStorage.CreateNewStorageInstance(testDatabaseName); Console.WriteLine("Creating database tables..."); var creationScripts = RelationalStorageUtilities.RemoveBatchSeparators(File.ReadAllText("CreateOrleansTables_SqlServer.sql")); foreach(var creationScript in creationScripts) { var res = relationalStorage.ExecuteAsync(creationScript).Result; } //Currently there's only one database under test, SQL Server. So this as the other //setup is hardcoded here: putting the database in simple recovery mode. //This removes the use of recovery log in case of database crashes, which //improves performance to some degree, depending on usage. For non-performance testing only. var simpleModeRes = relationalStorage.ExecuteAsync(string.Format("ALTER DATABASE [{0}] SET RECOVERY SIMPLE;", testDatabaseName)).Result; Console.WriteLine("Initializing relational databases done."); }
/// <summary> /// Executes a multi-record insert query clause with <em>SELECT UNION ALL</em>. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="storage">The storage to use.</param> /// <param name="tableName">The table name to against which to execute the query.</param> /// <param name="parameters">The parameters to insert.</param> /// <param name="nameMap">If provided, maps property names from <typeparamref name="T"/> to ones provided in the map.</param> /// <param name="onlyOnceColumns">If given, SQL parameter values for the given <typeparamref name="T"/> property types are generated only once. Effective only when <paramref name="useSqlParams"/> is <em>TRUE</em>.</param> /// <param name="useSqlParams"><em>TRUE</em> if the query should be in parameterized form. <em>FALSE</em> otherwise.</param> /// <returns>The rows affected.</returns> public static Task <int> ExecuteMultipleInsertIntoAsync <T>(this IRelationalStorage storage, string tableName, IEnumerable <T> parameters, IReadOnlyDictionary <string, string> nameMap = null, IEnumerable <string> onlyOnceColumns = null, bool useSqlParams = true) { if (string.IsNullOrWhiteSpace(tableName)) { throw new ArgumentException("The name must be a legal SQL table name", "tableName"); } if (parameters == null) { throw new ArgumentNullException("parameters"); } var storageConsts = DbConstantsStore.GetDbConstants(storage.InvariantName); var startEscapeIndicator = storageConsts.StartEscapeIndicator; var endEscapeIndicator = storageConsts.EndEscapeIndicator; //SqlParameters map is needed in case the query needs to be parameterized in order to avoid two //reflection passes as first a query needs to be constructed and after that when a database //command object has been created, parameters need to be provided to them. var sqlParameters = new Dictionary <string, object>(); const string insertIntoValuesTemplate = "INSERT INTO {0} ({1}) SELECT {2};"; var columns = string.Empty; var values = new List <string>(); if (parameters.Any()) { //Type and property information are the same for all of the objects. //The following assumes the property names will be retrieved in the same //order as is the index iteration done. var onlyOnceRow = new List <string>(); var properties = parameters.First().GetType().GetProperties(); columns = string.Join(",", nameMap == null ? properties.Select(pn => string.Format("{0}{1}{2}", startEscapeIndicator, pn.Name, endEscapeIndicator)) : properties.Select(pn => string.Format("{0}{1}{2}", startEscapeIndicator, (nameMap.ContainsKey(pn.Name) ? nameMap[pn.Name] : pn.Name), endEscapeIndicator))); if (onlyOnceColumns != null && onlyOnceColumns.Any()) { var onlyOnceProperties = properties.Where(pn => onlyOnceColumns.Contains(pn.Name)).Select(pn => pn).ToArray(); var onlyOnceData = parameters.First(); for (int i = 0; i < onlyOnceProperties.Length; ++i) { var currentProperty = onlyOnceProperties[i]; var parameterValue = currentProperty.GetValue(onlyOnceData, null); if (useSqlParams) { var parameterName = string.Format("@{0}", (nameMap.ContainsKey(onlyOnceProperties[i].Name) ? nameMap[onlyOnceProperties[i].Name] : onlyOnceProperties[i].Name)); onlyOnceRow.Add(parameterName); sqlParameters.Add(parameterName, parameterValue); } else { onlyOnceRow.Add(string.Format(sqlFormatProvider, "{0}", parameterValue)); } } } var dataRows = new List <string>(); var multiProperties = onlyOnceColumns == null ? properties : properties.Where(pn => !onlyOnceColumns.Contains(pn.Name)).Select(pn => pn).ToArray(); int parameterCount = 0; foreach (var row in parameters) { for (int i = 0; i < multiProperties.Length; ++i) { var currentProperty = multiProperties[i]; var parameterValue = currentProperty.GetValue(row, null); if (useSqlParams) { var parameterName = string.Format(indexedParameterTemplate, parameterCount); dataRows.Add(parameterName); sqlParameters.Add(parameterName, parameterValue); ++parameterCount; } else { dataRows.Add(string.Format(sqlFormatProvider, "{0}", parameterValue)); } } values.Add(string.Format("{0}", string.Join(",", onlyOnceRow.Concat(dataRows)))); dataRows.Clear(); } } var query = string.Format(insertIntoValuesTemplate, tableName, columns, string.Join(storageConsts.UnionAllSelectTemplate, values)); return(storage.ExecuteAsync(query, command => { if (useSqlParams) { foreach (var sp in sqlParameters) { var p = command.CreateParameter(); p.ParameterName = sp.Key; p.Value = sp.Value ?? DBNull.Value; p.Direction = ParameterDirection.Input; command.Parameters.Add(p); } } })); }
private Task ExecuteAsync(string query, Func <IDbCommand, DbStoredQueries.Columns> parameterProvider) { return(storage.ExecuteAsync(query, command => parameterProvider(command))); }
public static void ClassInitialize(TestContext context) { Console.WriteLine("TestContext.DeploymentDirectory={0}", context.DeploymentDirectory); Console.WriteLine("TestContext="); Console.WriteLine(DumpTestContext(context)); Console.WriteLine("Initializing relational databases..."); relationalStorage = RelationalStorageUtilities.CreateDefaultSqlServerStorageInstance(); Console.WriteLine("Dropping and recreating database '{0}' with connectionstring '{1}'", testDatabaseName, relationalStorage.ConnectionString); if(relationalStorage.ExistsDatabaseAsync(testDatabaseName).Result) { relationalStorage.DropDatabaseAsync(testDatabaseName).Wait(); } relationalStorage.CreateDatabaseAsync(testDatabaseName).Wait(); //The old storage instance has the previous connection string, time have a new handle with a new connection string... relationalStorage = relationalStorage.CreateNewStorageInstance(testDatabaseName); Console.WriteLine("Creating database tables..."); var creationScripts = RelationalStorageUtilities.RemoveBatchSeparators(File.ReadAllText("CreateOrleansTables_SqlServer.sql")); foreach(var creationScript in creationScripts) { var res = relationalStorage.ExecuteAsync(creationScript).Result; } //Currently there's only one database under test, SQL Server. So this as the other //setup is hardcoded here: putting the database in simple recovery mode. //This removes the use of recovery log in case of database crashes, which //improves performance to some degree, depending on usage. For non-performance testing only. var simpleModeRes = relationalStorage.ExecuteAsync(string.Format("ALTER DATABASE [{0}] SET RECOVERY SIMPLE;", testDatabaseName)).Result; Console.WriteLine("Initializing relational databases done."); siloOptions.DataConnectionString = relationalStorage.ConnectionString; }
/// <summary> /// Executes a multi-record insert query clause with <em>SELECT UNION ALL</em>. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="storage">The storage to use.</param> /// <param name="tableName">The table name to against which to execute the query.</param> /// <param name="parameters">The parameters to insert.</param> /// <param name="useSqlParams"><em>TRUE</em> if the query should be in parameterized form. <em>FALSE</em> otherwise.</param> /// <returns>The rows affected.</returns> public static Task <int> ExecuteMultipleInsertIntoAsync <T>(this IRelationalStorage storage, string tableName, IEnumerable <T> parameters, bool useSqlParams = false) { if (string.IsNullOrWhiteSpace(tableName)) { throw new ArgumentException("The name must be a legal SQL table name", "tableName"); } if (parameters == null) { throw new ArgumentNullException("parameters"); } //SqlParameters map is needed in case the query needs to be parameterized in order to avoid two //reflection passes as first a query needs to be constructed and after that when a database //command object has been created, parameters need to be provided to them. var sqlParameters = new Dictionary <string, object>(); const string insertIntoValuesTemplate = "INSERT INTO {0} ({1}) SELECT {2};"; string columns = string.Empty; var values = new List <string>(); var materializedParameters = parameters.ToList(); if (materializedParameters.Any()) { //Type and property information are the same for all of the objects. //The following assumes the property names will be retrieved in the same //order as is the index iteration done. var type = materializedParameters.First().GetType(); var properties = type.GetProperties(); var startEscapeIndicator = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.StartEscapeIndicatorKey); var endEscapeIndicator = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.StartEscapeIndicatorKey); columns = string.Join(",", properties.Select(f => string.Format("{0}{1}{2}", startEscapeIndicator, f.Name, endEscapeIndicator))); int parameterCount = 0; //This datarows will be used multiple times. It is all right as all the rows //are of the same length, so all the values will always be replaced. var dataRows = new string[properties.Length]; foreach (var row in materializedParameters) { for (int i = 0; i < properties.Length; ++i) { if (useSqlParams) { var parameterName = string.Format(string.Format(indexedParameterTemplate, parameterCount)); dataRows[i] = parameterName; sqlParameters.Add(parameterName, properties[i].GetValue(row, null)); ++parameterCount; } else { dataRows[i] = string.Format(sqlFormatProvider, "{0}", properties[i].GetValue(row, null)); } } values.Add(string.Format("{0}", string.Join(",", dataRows))); } } //If this is an Oracle database, every UNION ALL SELECT needs to have "FROM DUAL" appended. if (storage.InvariantName == WellKnownRelationalInvariants.OracleDatabase) { //Counting starts from 1 as the first SELECT should not select from dual. for (int i = 1; i < values.Count; ++i) { values[i] = string.Concat(values[i], " FROM DUAL"); } } var query = string.Format(insertIntoValuesTemplate, tableName, columns, string.Join(" UNION ALL SELECT ", values)); return(storage.ExecuteAsync(query, command => { if (useSqlParams) { foreach (var sqlParameter in sqlParameters) { var p = command.CreateParameter(); p.ParameterName = sqlParameter.Key; p.Value = sqlParameter.Value ?? DBNull.Value; p.Direction = ParameterDirection.Input; command.Parameters.Add(p); } } })); }
/// <summary> /// Deletes all data from Orleans database tables. /// </summary> /// <param name="storage">The storage to use.</param> /// <returns>The call will be succesful if the DDL query is successful. Otherwise an exception will be thrown.</returns> public static async Task DeleteAllDataAsync(this IRelationalStorage storage) { var deleteAllTemplate = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.DeleteAllDataKey); await storage.ExecuteAsync(deleteAllTemplate, command => { }).ConfigureAwait(continueOnCapturedContext: false); }
/// <summary> /// Drops a database with a given name. /// </summary> /// <param name="storage">The storage to use.</param> /// <param name="databaseName">The name of the database to drop.</param> /// <returns>The call will be succesful if the DDL query is successful. Otherwise an exception will be thrown.</returns> public static async Task DropDatabaseAsync(this IRelationalStorage storage, string databaseName) { var dropTemplate = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.DropDatabaseKey); await storage.ExecuteAsync(string.Format(dropTemplate, databaseName), command => { }).ConfigureAwait(continueOnCapturedContext: false); }
/// <summary> /// Deletes all data from Orleans database tables. /// </summary> /// <param name="storage">The storage to use.</param> /// <returns>The call will be succesful if the DDL query is successful. Otherwise an exception will be thrown.</returns> public static async Task DeleteAllDataAsync(this IRelationalStorage storage, string query) { await storage.ExecuteAsync(query, command => { }).ConfigureAwait(continueOnCapturedContext: false); }
/// <summary> /// Drops a database with a given name. /// </summary> /// <param name="storage">The storage to use.</param> /// <param name="databaseName">The name of the database to drop.</param> /// <returns>The call will be succesful if the DDL query is successful. Otherwise an exception will be thrown.</returns> public static async Task DropDatabaseAsync(this IRelationalStorage storage, string query, string databaseName) { await storage.ExecuteAsync(string.Format(query, databaseName), command => { }).ConfigureAwait(continueOnCapturedContext: false); }