/// <summary>
/// Static helper to package up the relevant info into a MutliShardTestCaseColumn object.
/// </summary>
/// <param name="sqlTypeName">The SQL Server database engine data type sqlTypeName.</param>
/// <param name="sqlFieldDeclarationText">The text to use to create this column in SQL Server.</param>
/// <param name="dbType">The SqlDbType for this column.</param>
/// <param name="length">The length of the column (usefulf for char/binary/etc.</param>
/// <param name="listToAddTo">The list to add the newly created column into.</param>
private static void AddColumnToList(string sqlTypeName, string sqlFieldDeclarationText, SqlDbType dbType, int length, List <MutliShardTestCaseColumn> listToAddTo)
{
string colName = GenerateTestColumnName(sqlTypeName);
MutliShardTestCaseColumn toAdd = new MutliShardTestCaseColumn(sqlTypeName, sqlFieldDeclarationText, dbType, length, colName);
listToAddTo.Add(toAdd);
}
/// <summary>
/// Helper to generate random field data for a record in the test table.
/// </summary>
/// <param name="numCommands">The number of records to generate random data for.</param>
/// <param name="dbName">The name of the database to put in the dbName column.</param>
/// <returns>Array filled with the commands to execute to insert the data.</returns>
private static string[] GetInsertValuesCommands(int numCommands, string dbName)
{
string[] commandsToReturn = new string[numCommands];
StringBuilder insertCommand = new StringBuilder();
IReadOnlyList <MutliShardTestCaseColumn> fieldInfo = MutliShardTestCaseColumn.DefinedColumns;
for (int i = 0; i < numCommands; i++)
{
insertCommand.Clear();
// Set up the stem, which includes putting the dbName in the dbNameColumn.
//
insertCommand.AppendFormat("INSERT INTO {0} ({1}", s_testTableName, s_dbNameField);
// Now put in all the column names in the order we will do them.
//
for (int j = 0; j < fieldInfo.Count; j++)
{
MutliShardTestCaseColumn curField = fieldInfo[j];
// Special case: if we hit the rowversion field let's skip it - it gets updated automatically.
//
if (!IsTimestampField(curField.DbType))
{
insertCommand.AppendFormat(", {0}", curField.TestColumnName);
}
}
// Close the field list
//
insertCommand.Append(") ");
// Now put in the VALUES stem
//
insertCommand.AppendFormat("VALUES ('{0}'", dbName);
// Now put in the individual field values
//
for (int j = 0; j < fieldInfo.Count; j++)
{
MutliShardTestCaseColumn curField = fieldInfo[j];
// Special case: if we hit the rowversion field let's skip it - it gets updated automatically.
//
if (!IsTimestampField(curField.DbType))
{
string valueToPutIn = GetTestFieldValue(curField);
insertCommand.AppendFormat(", {0}", valueToPutIn);
}
}
// Finally, close the values list, terminate the statement, and add it to the array.
//
insertCommand.Append(");");
commandsToReturn[i] = insertCommand.ToString();
}
return(commandsToReturn);
}
/// <summary>
/// Helper to produce a random double drawn from the decimal domain.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to generate and cast the value.</returns>
private static string GetRandomSqlDecimalValue(MutliShardTestCaseColumn column)
{
// .NET Decimal has less range than SQL decimal, so we need to drop down to the
// .NET range to test these consistently.
//
double theValue = s_random.NextDouble() * Decimal.ToDouble(Decimal.MaxValue);
return(string.Format("CAST({0} AS {1})", theValue, column.ColumnTypeDeclaration));
}
/// <summary>
/// Helper that constructs the sql script to create our test table.
/// </summary>
/// <returns>
/// T-SQL to create our test table.
/// </returns>
private static string GetTestTableCreateCommand()
{
StringBuilder createCommand = new StringBuilder();
// Set up the stem of the statement
//
createCommand.AppendFormat("CREATE TABLE {0} ({1} nvarchar(50)", s_testTableName, s_dbNameField);
IReadOnlyList <MutliShardTestCaseColumn> fieldInfo = MutliShardTestCaseColumn.DefinedColumns;
for (int i = 0; i < fieldInfo.Count; i++)
{
MutliShardTestCaseColumn curField = fieldInfo[i];
createCommand.AppendFormat(", {0} {1}", curField.TestColumnName, curField.ColumnTypeDeclaration);
}
createCommand.Append(");");
return(createCommand.ToString());
}
/// <summary>
/// Helper that produces tsql to cast a random int as a particular data type drawn from the SmallInt range.
/// </summary>
/// <param name="column">The column that will determine the data type we wish to insert into.</param>
/// <returns>The tsql fragment to generate the desired value.</returns>
private static string GetRandomSqlSmallIntValue(MutliShardTestCaseColumn column)
{
int theValue = s_random.Next(Int16.MinValue, Int16.MaxValue);
return GetSpecificIntCastAsArg(theValue, column);
}
/// <summary>
/// Helper that produces tsql to cast a random int as a particular data type.
/// </summary>
/// <param name="column">The column that will determine the data type we wish to insert into.</param>
/// <returns>The tsql fragment to generate the desired value.</returns>
private static string GetRandomIntCastAsArg(MutliShardTestCaseColumn column)
{
int theValue = s_random.Next();
return GetSpecificIntCastAsArg(theValue, column);
}
/// <summary>
/// Helper to generate a tsql fragement that will produce a random value of the given type to insert into the test database.
/// </summary>
/// <param name="dataTypeInfo">The datatype of the desired value.</param>
/// <returns>The tsql fragment that will generate a random value of the desired type.</returns>
private static string GetTestFieldValue(MutliShardTestCaseColumn dataTypeInfo)
{
SqlDbType dbType = dataTypeInfo.DbType;
int? length = dataTypeInfo.FieldLength;
switch (dbType)
{
case SqlDbType.BigInt:
// SQL Types: bigint
return GetRandomIntCastAsArg(dataTypeInfo);
case SqlDbType.Binary:
// SQL Types: image
return GetRandomSqlBinaryValue(length.Value);
case SqlDbType.Bit:
// SQL Types: bit
return GetRandomSqlBitValue();
case SqlDbType.Char:
// SQL Types: char[(n)]
return GetRandomSqlCharValue(length.Value);
case SqlDbType.Date:
// SQL Types: date
return GetRandomSqlDateValue();
case SqlDbType.DateTime:
// SQL Types: datetime, smalldatetime
return GetRandomSqlDatetimeCastAsArg(dataTypeInfo);
case SqlDbType.DateTime2:
// SQL Types: datetime2
return GetRandomSqlDatetimeCastAsArg(dataTypeInfo);
case SqlDbType.DateTimeOffset:
// SQL Types: datetimeoffset
return GetRandomSqlDatetimeoffsetValue();
case SqlDbType.Decimal:
// SQL Types: decimal, numeric
// These are the same.
return GetRandomSqlDecimalValue(dataTypeInfo);
case SqlDbType.Float:
// SQL Types: float
return GetRandomSqlFloatValue(dataTypeInfo);
case SqlDbType.Image:
// SQL Types: image
return GetRandomSqlBinaryValue(dataTypeInfo.FieldLength);
case SqlDbType.Int:
// SQL Types: int
return GetRandomSqlIntValue();
case SqlDbType.Money:
// SQL Types: money
return GetRandomSqlMoneyValue(dataTypeInfo);
case SqlDbType.NChar:
// SQL Types: nchar[(n)]
return GetRandomSqlNCharValue(length.Value);
case SqlDbType.NText:
// SQL Types: ntext
return GetRandomSqlNCharValue(length.Value);
case SqlDbType.NVarChar:
// SQL Types: nvarchar[(n)]
return GetRandomSqlNCharValue(length.Value);
case SqlDbType.Real:
// SQL Types: real
return GetRandomSqlRealValue(dataTypeInfo);
case SqlDbType.SmallDateTime:
// SQL Types: smalldatetime
return GetRandomSqlDatetimeCastAsArg(dataTypeInfo);
case SqlDbType.SmallInt:
// SQL Types: smallint
return GetRandomSqlSmallIntValue(dataTypeInfo);
case SqlDbType.SmallMoney:
// SQL Types: smallmoney
return GetRandomSqlSmallMoneyValue(dataTypeInfo);
case SqlDbType.Text:
// SQL Types: text
return GetRandomSqlCharValue(length.Value);
case SqlDbType.Time:
// SQL Types: time
return GetRandomSqlDatetimeCastAsArg(dataTypeInfo);
case SqlDbType.Timestamp:
// SQL Types: rowversion, timestamp
//exclding it should happen automatically. should not be here. throw.
throw new ArgumentException(SqlDbType.Timestamp.ToString());
case SqlDbType.TinyInt:
// SQL Types: tinyint
return GetRandomSqlTinyIntValue();
case SqlDbType.UniqueIdentifier:
// SQL Types: uniqueidentifier
return GetRandomSqlUniqueIdentifierValue();
case SqlDbType.VarBinary:
// SQL Types: binary[(n)], varbinary[(n)]
return GetRandomSqlBinaryValue(length.Value);
case SqlDbType.VarChar:
// SQL Types: varchar[(n)]
return GetRandomSqlCharValue(length.Value);
default:
throw new ArgumentException(dbType.ToString());
}
}
/// <summary>
//// Helper to cast a particular double as a particular type.
/// </summary>
/// <param name="theValue">The value to cast.</param>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to cast the value.</returns>
private static string GetSpecificDoubleCastAsArg(double theValue, MutliShardTestCaseColumn column)
{
return(string.Format("CAST({0} AS {1})", theValue, column.SqlServerDatabaseEngineType));
}
/// <summary>
/// Helper to produce a random double drawn from the real (sqlsingle) domain.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to generate and cast the value.</returns>
private static string GetRandomSqlRealValue(MutliShardTestCaseColumn column)
{
double theValue = s_random.NextDouble() * SqlSingle.MaxValue.Value;
return GetSpecificDoubleCastAsArg(theValue, column);
}
/// <summary>
/// Helper to produce a random double cast as a particular type.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to generate the desired value cast as the desired type.</returns>
private static string GetRandomDoubleCastAsArg(MutliShardTestCaseColumn column)
{
double randomDouble = s_random.NextDouble() * double.MaxValue;
return GetSpecificDoubleCastAsArg(randomDouble, column);
}
/// <summary>
/// Helper that produces a random double within the smallmoney domain and casts it to the desired column type.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>The tsql to generate the casted value.</returns>
private static string GetRandomSqlSmallMoneyValue(MutliShardTestCaseColumn column)
{
double randomSmallMoneyValue = s_random.NextDouble() * (214748.3647);
return(GetSpecificDoubleCastAsArg(randomSmallMoneyValue, column));
}
/// <summary>
/// Helper that produces a random sqldatetime value cast as a particular type.
/// </summary>
/// <param name="column">The column whoe type the value should be cast to.</param>
/// <returns>The tsql to generate the casted value.</returns>
private static string GetRandomSqlDatetimeCastAsArg(MutliShardTestCaseColumn column)
{
return(string.Format("CAST(SYSDATETIME() AS {0})", column.SqlServerDatabaseEngineType));
}
/// <summary>
/// Helper that produces tsql to cast a random int as a particular data type drawn from the SmallInt range.
/// </summary>
/// <param name="column">The column that will determine the data type we wish to insert into.</param>
/// <returns>The tsql fragment to generate the desired value.</returns>
private static string GetRandomSqlSmallIntValue(MutliShardTestCaseColumn column)
{
int theValue = s_random.Next(Int16.MinValue, Int16.MaxValue);
return(GetSpecificIntCastAsArg(theValue, column));
}
/// <summary>
/// Helper that produces tsql to cast a random int as a particular data type.
/// </summary>
/// <param name="column">The column that will determine the data type we wish to insert into.</param>
/// <returns>The tsql fragment to generate the desired value.</returns>
private static string GetRandomIntCastAsArg(MutliShardTestCaseColumn column)
{
int theValue = s_random.Next();
return(GetSpecificIntCastAsArg(theValue, column));
}
/// <summary>
/// Helper to generate a tsql fragement that will produce a random value of the given type to insert into the test database.
/// </summary>
/// <param name="dataTypeInfo">The datatype of the desired value.</param>
/// <returns>The tsql fragment that will generate a random value of the desired type.</returns>
private static string GetTestFieldValue(MutliShardTestCaseColumn dataTypeInfo)
{
SqlDbType dbType = dataTypeInfo.DbType;
int? length = dataTypeInfo.FieldLength;
switch (dbType)
{
case SqlDbType.BigInt:
// SQL Types: bigint
return(GetRandomIntCastAsArg(dataTypeInfo));
case SqlDbType.Binary:
// SQL Types: image
return(GetRandomSqlBinaryValue(length.Value));
case SqlDbType.Bit:
// SQL Types: bit
return(GetRandomSqlBitValue());
case SqlDbType.Char:
// SQL Types: char[(n)]
return(GetRandomSqlCharValue(length.Value));
case SqlDbType.Date:
// SQL Types: date
return(GetRandomSqlDateValue());
case SqlDbType.DateTime:
// SQL Types: datetime, smalldatetime
return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo));
case SqlDbType.DateTime2:
// SQL Types: datetime2
return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo));
case SqlDbType.DateTimeOffset:
// SQL Types: datetimeoffset
return(GetRandomSqlDatetimeoffsetValue());
case SqlDbType.Decimal:
// SQL Types: decimal, numeric
// These are the same.
return(GetRandomSqlDecimalValue(dataTypeInfo));
case SqlDbType.Float:
// SQL Types: float
return(GetRandomSqlFloatValue(dataTypeInfo));
case SqlDbType.Image:
// SQL Types: image
return(GetRandomSqlBinaryValue(dataTypeInfo.FieldLength));
case SqlDbType.Int:
// SQL Types: int
return(GetRandomSqlIntValue());
case SqlDbType.Money:
// SQL Types: money
return(GetRandomSqlMoneyValue(dataTypeInfo));
case SqlDbType.NChar:
// SQL Types: nchar[(n)]
return(GetRandomSqlNCharValue(length.Value));
case SqlDbType.NText:
// SQL Types: ntext
return(GetRandomSqlNCharValue(length.Value));
case SqlDbType.NVarChar:
// SQL Types: nvarchar[(n)]
return(GetRandomSqlNCharValue(length.Value));
case SqlDbType.Real:
// SQL Types: real
return(GetRandomSqlRealValue(dataTypeInfo));
case SqlDbType.SmallDateTime:
// SQL Types: smalldatetime
return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo));
case SqlDbType.SmallInt:
// SQL Types: smallint
return(GetRandomSqlSmallIntValue(dataTypeInfo));
case SqlDbType.SmallMoney:
// SQL Types: smallmoney
return(GetRandomSqlSmallMoneyValue(dataTypeInfo));
case SqlDbType.Text:
// SQL Types: text
return(GetRandomSqlCharValue(length.Value));
case SqlDbType.Time:
// SQL Types: time
return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo));
case SqlDbType.Timestamp:
// SQL Types: rowversion, timestamp
//exclding it should happen automatically. should not be here. throw.
throw new ArgumentException(SqlDbType.Timestamp.ToString());
case SqlDbType.TinyInt:
// SQL Types: tinyint
return(GetRandomSqlTinyIntValue());
case SqlDbType.UniqueIdentifier:
// SQL Types: uniqueidentifier
return(GetRandomSqlUniqueIdentifierValue());
case SqlDbType.VarBinary:
// SQL Types: binary[(n)], varbinary[(n)]
return(GetRandomSqlBinaryValue(length.Value));
case SqlDbType.VarChar:
// SQL Types: varchar[(n)]
return(GetRandomSqlCharValue(length.Value));
default:
throw new ArgumentException(dbType.ToString());
}
}
/// <summary>
/// Helper that produces a random sqldatetime value cast as a particular type.
/// </summary>
/// <param name="column">The column whoe type the value should be cast to.</param>
/// <returns>The tsql to generate the casted value.</returns>
private static string GetRandomSqlDatetimeCastAsArg(MutliShardTestCaseColumn column)
{
return string.Format("CAST(SYSDATETIME() AS {0})", column.SqlServerDatabaseEngineType);
}
/// <summary>
/// Helper that produces a random double within the smallmoney domain and casts it to the desired column type.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>The tsql to generate the casted value.</returns>
private static string GetRandomSqlSmallMoneyValue(MutliShardTestCaseColumn column)
{
double randomSmallMoneyValue = s_random.NextDouble() * (214748.3647);
return GetSpecificDoubleCastAsArg(randomSmallMoneyValue, column);
}
/// <summary>
/// Helper to produce a random double cast as a particular type.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to generate the desired value cast as the desired type.</returns>
private static string GetRandomDoubleCastAsArg(MutliShardTestCaseColumn column)
{
double randomDouble = s_random.NextDouble() * double.MaxValue;
return(GetSpecificDoubleCastAsArg(randomDouble, column));
}
/// <summary>
/// Helper to produce a random double drawn from the decimal domain.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to generate and cast the value.</returns>
private static string GetRandomSqlDecimalValue(MutliShardTestCaseColumn column)
{
// .NET Decimal has less range than SQL decimal, so we need to drop down to the
// .NET range to test these consistently.
//
double theValue = s_random.NextDouble() * Decimal.ToDouble(Decimal.MaxValue);
return string.Format("CAST({0} AS {1})", theValue, column.ColumnTypeDeclaration);
}
/// <summary>
/// Helper to produce a random double drawn from the real (sqlsingle) domain.
/// </summary>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to generate and cast the value.</returns>
private static string GetRandomSqlRealValue(MutliShardTestCaseColumn column)
{
double theValue = s_random.NextDouble() * SqlSingle.MaxValue.Value;
return(GetSpecificDoubleCastAsArg(theValue, column));
}
/// <summary>
//// Helper to cast a particular double as a particular type.
/// </summary>
/// <param name="theValue">The value to cast.</param>
/// <param name="column">The column whose type the value should be cast to.</param>
/// <returns>Tsql to cast the value.</returns>
private static string GetSpecificDoubleCastAsArg(double theValue, MutliShardTestCaseColumn column)
{
return string.Format("CAST({0} AS {1})", theValue, column.SqlServerDatabaseEngineType);
}
/// <summary>
/// Static helper to package up the relevant info into a MutliShardTestCaseColumn object.
/// </summary>
/// <param name="sqlTypeName">The SQL Server database engine data type sqlTypeName.</param>
/// <param name="sqlFieldDeclarationText">The text to use to create this column in SQL Server.</param>
/// <param name="dbType">The SqlDbType for this column.</param>
/// <param name="length">The length of the column (usefulf for char/binary/etc.</param>
/// <param name="listToAddTo">The list to add the newly created column into.</param>
private static void AddColumnToList(string sqlTypeName, string sqlFieldDeclarationText, SqlDbType dbType, int length, List<MutliShardTestCaseColumn> listToAddTo)
{
string colName = GenerateTestColumnName(sqlTypeName);
MutliShardTestCaseColumn toAdd = new MutliShardTestCaseColumn(sqlTypeName, sqlFieldDeclarationText, dbType, length, colName);
listToAddTo.Add(toAdd);
}
