/// <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);
}