public static DatabaseTable CreateTestTable(string tableName)
{
//we only need a schema because MySQL foreign key references do not allow just the foreign key table name- they need the columns too
var schema = new DatabaseSchema(null, null);
var testTable = new DatabaseTable { Name = tableName, Description = "This is a test table" };
schema.Tables.Add(testTable);
testTable.DatabaseSchema = schema; //the migration will discover this and know how to link the self referencing table
var intDataType = new DataType("INT", "System.Int32");
var idColumn = new DatabaseColumn
{
Name = "Id",
DbDataType = "int",
DataType = intDataType,
Nullable = false,
Description = "Primary key",
};
testTable.Columns.Add(idColumn);
var parentColumn = new DatabaseColumn
{
Name = "Parent", //for a self-referencing foreign key
DbDataType = "int",
DataType = intDataType,
Nullable = true,
Description = "Self referencing foreign key",
};
testTable.Columns.Add(parentColumn);
var nameColumn = new DatabaseColumn
{
Name = "NAME",
DbDataType = "VARCHAR",
Length = 10,
DataType = new DataType("VARCHAR", "string"),
Description = "Simple varchar column",
};
testTable.Columns.Add(nameColumn);
var primaryKey = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_" + tableName };
primaryKey.Columns.Add("Id");
testTable.PrimaryKey = primaryKey;
return testTable;
}
/// <summary>
/// Creates sample data from a <see cref="DatabaseSchemaReader.DataSchema.DataType"/>
/// </summary>
/// <param name="dataType">Type of the data.</param>
/// <param name="maxLength">Maximum length of a string</param>
/// <param name="precision">The precision (total digits).</param>
/// <param name="scale">The scale (digits after decimal point).</param>
/// <returns></returns>
public static object CreateData(DataType dataType, int? maxLength, int? precision, int? scale)
{
if (dataType == null)
return null;
if (dataType.IsNumeric)
{
if (precision == scale) return 0.1;
return 1;
}
if (dataType.IsString)
{
var length = maxLength.GetValueOrDefault();
//a weird error in the devart postgresql provider puts length of char fields in precision
if (length < 1 && precision > 1) length = precision.Value;
return GenerateString(length);
}
if (dataType.IsDateTime)
return DateTime.Now;
if (dataType.GetNetType() == typeof(byte[]))
return new byte[] { };
return null;
}
private static DataType InventDataType(string dbType, ICollection<DataType> dataTypeList, SqlType sqlType, int? length)
{
//we need to invent a dataType - we need a .Net typeName
if (IsVariableString(dbType) || IsFixedLengthString(dbType))
{
var dataType = new DataType(dbType, typeof(string).FullName);
dataTypeList.Add(dataType);
return dataType;
}
if (IsBinary(dbType) || IsBlob(dbType, length))
{
var dataType = new DataType(dbType, "System.Byte[]");
dataTypeList.Add(dataType);
return dataType;
}
if (sqlType == SqlType.SqlServer &&
dbType.StartsWith("TIMESTAMP(", StringComparison.OrdinalIgnoreCase))
{
var dataType = new DataType(dbType, "System.Byte[]");
dataType.ProviderDbType = 19;// (int)SqlDbType.Timestamp;
dataTypeList.Add(dataType);
return dataType;
}
if (IsDateTime(dbType))
{
var dataType = new DataType(dbType, typeof(DateTime).FullName);
dataTypeList.Add(dataType);
return dataType;
}
if (IsInteger(dbType))
{
var netType = typeof(int).FullName;
if (dbType.Equals("BIGINT")) netType = typeof(long).FullName;
var dataType = new DataType(dbType, netType);
dataTypeList.Add(dataType);
return dataType;
}
if (HasPrecision(dbType))
{
var dataType = new DataType(dbType, typeof(decimal).FullName);
dataTypeList.Add(dataType);
return dataType;
}
//we've picked off the most common types here
return null;
}
public static List<DataType> DataTypes(DataTable dataTable)
{
List<DataType> list = new List<DataType>();
if (dataTable == null || dataTable.Rows.Count == 0) return list;
var typename = "TypeName";
var datatype = "DataType";
var providerdbtype = "ProviderDbType";
var literalprefix = "LiteralPrefix";
var literalsuffix = "LiteralSuffix";
var createformat = "CreateFormat";
//DB2
if (!dataTable.Columns.Contains(typename)) typename = "provider_type_name";
if (!dataTable.Columns.Contains(datatype)) datatype = "framework_type";
if (!dataTable.Columns.Contains(providerdbtype)) providerdbtype = "provider_type";
if (!dataTable.Columns.Contains(literalprefix)) literalprefix = "literal_prefix";
if (!dataTable.Columns.Contains(literalsuffix)) literalsuffix = "literal_suffix";
if (!dataTable.Columns.Contains(createformat)) createformat = null;
foreach (DataRow row in dataTable.Rows)
{
string typeName = row[typename].ToString();
string netDataType = row[datatype].ToString();
DataType d = new DataType(typeName, netDataType);
var pdt = GetNullableInt(row[providerdbtype]);
d.ProviderDbType = pdt.HasValue ? pdt.Value : -1;
d.LiteralPrefix = row[literalprefix].ToString();
d.LiteralSuffix = row[literalsuffix].ToString();
if (createformat != null)
d.CreateFormat = row[createformat].ToString();
list.Add(d);
}
return list;
}