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