public SqlRandomTable(SqlRandomTableColumn[] columns, int? primaryKeyColumnIndex = null, int estimatedRowCount = 0)
{
if (columns == null || columns.Length == 0)
throw new ArgumentException("non-empty type array is required");
if (estimatedRowCount < 0)
throw new ArgumentOutOfRangeException("non-negative row count is required, use 0 for default");
if (primaryKeyColumnIndex.HasValue && (primaryKeyColumnIndex.Value < 0 || primaryKeyColumnIndex.Value >= columns.Length))
throw new ArgumentOutOfRangeException("primaryColumnIndex");
PrimaryKeyColumnIndex = primaryKeyColumnIndex;
_columns = (SqlRandomTableColumn[])columns.Clone();
_columnNames = new string[columns.Length];
if (estimatedRowCount == 0)
_rows = new List<object[]>();
else
_rows = new List<object[]>(estimatedRowCount);
Columns = new List<SqlRandomTableColumn>(_columns).AsReadOnly();
ColumnNames = new List<string>(_columnNames).AsReadOnly();
bool hasSparse = false;
double totalNonSparse = 0;
foreach (var c in _columns)
{
if (c.IsSparse)
{
hasSparse = true;
}
else
{
totalNonSparse += c.GetInRowSize(null); // for non-sparse columns size does not depend on the value
}
}
HasSparseColumns = hasSparse;
NonSparseValuesTotalSize = totalNonSparse;
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
string sizeSuffix;
if (!columnInfo.StorageSize.HasValue)
{
sizeSuffix = DefaultSize.ToString();
}
else
{
int size = columnInfo.StorageSize.Value;
if (size > MaxDefinedSize)
{
sizeSuffix = "max";
}
else
{
Debug.Assert(size > 0, "wrong size");
sizeSuffix = size.ToString();
}
}
return string.Format("{0}({1})", TypePrefix, sizeSuffix);
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return "uniqueidentifier";
}
protected override object ReadInternal(DbDataReader reader, int ordinal, SqlRandomTableColumn columnInfo, Type asType)
{
return ReadByteArray(reader, ordinal, asType);
}
protected override object ReadInternal(DbDataReader reader, int ordinal, SqlRandomTableColumn columnInfo, Type asType)
{
ValidateReadType(typeof(double), asType);
if (reader.IsDBNull(ordinal))
return DBNull.Value;
return reader.GetDouble(ordinal);
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return LargeVarDataRowUsage;
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
int precision = columnInfo.Precision.HasValue ? columnInfo.Precision.Value : _defaultPrecision;
if (precision < 1 || precision > 38)
{
throw new ArgumentOutOfRangeException("wrong precision");
}
if (precision < 10)
{
return 5;
}
else if (precision < 20)
{
return 9;
}
else if (precision < 28)
{
return 13;
}
else
{
return 17;
}
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return string.Format("{0}({1})", TypePrefix, GetCharSize(columnInfo));
}
public override bool CanCompareValues(SqlRandomTableColumn columnInfo)
{
return((columnInfo.Options & SqlRandomColumnOptions.ColumnSet) == 0);
}
protected override object ReadInternal(DbDataReader reader, int ordinal, SqlRandomTableColumn columnInfo, Type asType)
{
return(reader.GetValue(ordinal));
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return("sql_variant");
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return("smalldatetime");
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
// this method does not use Guid.NewGuid since it is not based on the given rand object
return(rand.NextUniqueIdentifier());
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return("uniqueidentifier");
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return rand.NextByteArray(0, columnInfo.StorageSize);
}
private int GetCharSize(SqlRandomTableColumn columnInfo)
{
ValidateColumnInfo(columnInfo);
int charSize = columnInfo.StorageSize.HasValue ? columnInfo.StorageSize.Value / 2 : DefaultCharSize;
if (charSize < 1 || charSize > MaxCharSize)
throw new NotSupportedException("wrong size");
return charSize;
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return(rand.NextByteArray(0, columnInfo.StorageSize));
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return 0.125; // 8 bits => 1 byte
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
int size = columnInfo.StorageSize.HasValue ? columnInfo.StorageSize.Value : DefaultCharSize;
return(rand.NextAnsiArray(0, size));
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return TypeTSqlName;
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return(GetCharSize(columnInfo) * 2); // nchar is not stored in row
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
int precision = columnInfo.Precision.HasValue ? columnInfo.Precision.Value : MaxFloatPrecision;
if (precision != RealPrecision && precision != MaxFloatPrecision)
throw new ArgumentException("wrong precision");
int mantissaBits = (precision <= RealPrecision) ? 24 : 53;
return string.Format("{0}({1})", TypePrefix, mantissaBits);
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return(string.Format("{0}({1})", TypePrefix, GetCharSize(columnInfo)));
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return "rowversion";
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
int storageSize = GetCharSize(columnInfo) * 2;
return(rand.NextUcs2Array(0, storageSize));
}
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
throw new InvalidOperationException("should not be used for timestamp - use CanCompareValues before calling this method");
}
protected override object ReadInternal(DbDataReader reader, int ordinal, SqlRandomTableColumn columnInfo, Type asType)
{
return(ReadCharData(reader, ordinal, asType));
}
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
throw new InvalidOperationException("should not be used for timestamp - use CanCompareValues before calling this method");
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return(0.125); // 8 bits => 1 byte
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return rand.NextIntInclusive();
}
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
return(CompareByteArray(expected, actual, allowIncomplete: false));
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
int size = columnInfo.StorageSize.HasValue ? columnInfo.StorageSize.Value : DefaultCharSize;
return rand.NextAnsiArray(0, size);
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return((decimal)Math.Round(rand.NextDouble()));
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return GetCharSize(columnInfo) * 2; // nchar is not stored in row
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return(LargeVarDataRowUsage);
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
int storageSize = GetCharSize(columnInfo) * 2;
return rand.NextUcs2Array(0, storageSize);
}
protected override string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo)
{
return(TSqlTypeName);
}
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
return CompareByteArray(expected, actual, allowIncomplete: false);
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return(rand.NextReal());
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return (decimal)Math.Round(rand.NextDouble());
}
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
return(CompareValues <double>(expected, actual));
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return rand.NextSmallMoney();
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return(8);
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
// float
if (!columnInfo.Precision.HasValue)
{
return 8;
}
else
{
int precision = columnInfo.Precision.Value;
if (precision != RealPrecision && precision != MaxFloatPrecision)
throw new ArgumentException("wrong precision");
return (precision <= RealPrecision) ? 4 : 8;
}
}
/// <summary>
/// Creates random list of columns from the given source collection. The rules are:
/// * table cannot contain more than 1024 non-sparse columns
/// * total row size of non-sparse columns should not exceed 8060 or 8018 (with sparse)
/// * column set column must be added if number of columns in total exceeds 1024
/// </summary>
public static SqlRandomTableColumn[] CreateRandTypes(SqlRandomizer rand, SqlRandomTypeInfoCollection sourceCollection, int maxColumnsCount, bool createIdColumn)
{
var retColumns = new List <SqlRandomTableColumn>(maxColumnsCount);
bool hasTimestamp = false;
double totalRowSize = 0;
int totalRegularColumns = 0;
bool hasColumnSet = false;
bool hasSparseColumns = false;
int maxRowSize = MaxBytesPerRow; // set to MaxBytesPerRowWithSparse when sparse column is first added
int i = 0;
if (createIdColumn)
{
SqlRandomTypeInfo keyType = sourceCollection[SqlDbType.Int];
SqlRandomTableColumn keyColumn = keyType.CreateDefaultColumn(SqlRandomColumnOptions.None);
retColumns.Add(keyColumn);
totalRowSize += keyType.GetInRowSize(keyColumn, null);
i++;
totalRegularColumns++;
}
for (; i < maxColumnsCount; i++)
{
// select column options (sparse/column-set)
bool isSparse; // must be set in the if/else flow below
bool isColumnSet = false;
if (totalRegularColumns >= MaxNonSparseColumns)
{
// reached the limit for regular columns
if (!hasColumnSet)
{
// no column-set yet, stop unconditionally
// this can happen if large char/binary value brought the row size total to a limit leaving no space for column-set
break;
}
// there is a column set, enforce sparse from this point
isSparse = true;
}
else if (i == (MaxNonSparseColumns - 1) && hasSparseColumns && !hasColumnSet)
{
// we almost reached the limit of regular & sparse columns with, but no column set added
// to increase chances for >1024 columns, enforce column set now
isColumnSet = true;
isSparse = false;
}
else if (totalRowSize > MaxBytesPerRowWithSparse)
{
Debug.Assert(totalRowSize <= MaxBytesPerRow, "size over the max limit");
Debug.Assert(!hasSparseColumns, "should not have sparse columns after MaxBytesPerRowWithSparse (check maxRowSize)");
// cannot insert sparse from this point
isSparse = false;
isColumnSet = false;
}
else
{
// check how close we are to the limit of the row size
int sparseProbability;
if (totalRowSize < 100)
{
sparseProbability = 2;
}
else if (totalRowSize < MaxBytesPerRowWithSparse / 2)
{
sparseProbability = 10;
}
else if (totalRowSize < (MaxBytesPerRowWithSparse - s_columnSetSafetyRange))
{
sparseProbability = 50;
}
else
{
// close to the row size limit, special case
if (!hasColumnSet)
{
// if we have not added column set column yet
// column-set is a regular column and its size counts towards row size, so time to add it
isColumnSet = true;
sparseProbability = -1; // not used
}
else
{
sparseProbability = 90;
}
}
if (!isColumnSet)
{
isSparse = (rand.Next(100) < sparseProbability);
if (!isSparse && !hasColumnSet)
{
// if decided to add regular column, give it a (low) chance to inject a column set at any position
isColumnSet = rand.Next(100) < 1;
}
}
else
{
isSparse = false;
}
}
// select the type
SqlRandomTypeInfo ti;
SqlRandomColumnOptions options = SqlRandomColumnOptions.None;
if (isSparse)
{
Debug.Assert(!isColumnSet, "should not have both sparse and column set flags set");
ti = sourceCollection.NextSparse(rand);
Debug.Assert(ti.CanBeSparseColumn, "NextSparse must return only types that can be sparse");
options |= SqlRandomColumnOptions.Sparse;
}
else if (isColumnSet)
{
Debug.Assert(!hasColumnSet, "there is already a column set, we should not set isColumnSet again above");
ti = sourceCollection[SqlDbType.Xml];
options |= SqlRandomColumnOptions.ColumnSet;
}
else
{
// regular column
ti = sourceCollection.Next(rand);
if (ti.Type == SqlDbType.Timestamp)
{
// while table can contain single timestamp column only, there is no way to insert values into it.
// thus, do not allow this
if (hasTimestamp || maxColumnsCount == 1)
{
ti = sourceCollection[SqlDbType.Int];
}
else
{
// table cannot have two timestamp columns
hasTimestamp = true;
}
}
}
SqlRandomTableColumn col = ti.CreateRandomColumn(rand, options);
if (!isSparse)
{
double rowSize = ti.GetInRowSize(col, DBNull.Value);
int overhead = GetRowOverhead(retColumns.Count + 1); // +1 for this column
if (totalRowSize + rowSize + overhead > maxRowSize)
{
// cannot use this column
// note that if this column is a column set column
continue;
}
totalRowSize += rowSize;
totalRegularColumns++;
}
// else - sparse columns are not counted towards row size when table is created (they are when inserting new row with non-null value in the sparse column)...
retColumns.Add(col);
// after adding the column, update the state
if (isColumnSet)
{
hasColumnSet = true;
}
if (isSparse)
{
hasSparseColumns = true;
maxRowSize = MaxBytesPerRowWithSparse; // reduce the max row size
}
}
return(retColumns.ToArray());
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
int precision = columnInfo.Precision.HasValue ? columnInfo.Precision.Value : MaxFloatPrecision;
if (precision <= RealPrecision)
return rand.NextDouble(float.MinValue, float.MaxValue, precision);
else
return rand.NextDouble(double.MinValue, double.MaxValue, precision);
}
protected abstract double GetInRowSizeInternal(SqlRandomTableColumn columnInfo);
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
return CompareValues<Double>(expected, actual);
}
/// <summary>
/// gets TSQL definition of the column
/// </summary>
public string GetTSqlTypeDefinition(SqlRandomTableColumn columnInfo)
{
ValidateColumnInfo(columnInfo);
return(GetTSqlTypeDefinitionInternal(columnInfo));
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
return rand.NextRowVersion();
}
protected abstract string GetTSqlTypeDefinitionInternal(SqlRandomTableColumn columnInfo);
public override bool CanCompareValues(SqlRandomTableColumn columnInfo)
{
// completely ignore TIMESTAMP value comparison
return false;
}
/// <summary>
/// creates random, but valud value for the type, based on the given column definition
/// </summary>
public object CreateRandomValue(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
ValidateColumnInfo(columnInfo);
return(CreateRandomValueInternal(rand, columnInfo));
}
protected override double GetInRowSizeInternal(SqlRandomTableColumn columnInfo)
{
return 16;
}
protected override bool CompareValuesInternal(SqlRandomTableColumn columnInfo, object expected, object actual)
{
if ((columnInfo.Options & SqlRandomColumnOptions.ColumnSet) != 0)
{
throw new InvalidOperationException("should not be used for ColumnSet columns - use CanCompareValues before calling this method");
}
return CompareCharArray(expected, actual, allowIncomplete: false);
}
protected override object CreateRandomValueInternal(SqlRandomizer rand, SqlRandomTableColumn columnInfo)
{
// this method does not use Guid.NewGuid since it is not based on the given rand object
return rand.NextUniqueIdentifier();
}
public override bool CanCompareValues(SqlRandomTableColumn columnInfo)
{
// completely ignore TIMESTAMP value comparison
return(false);
}