/// <summary>
/// Creates an object that translates the parameters used for external methods to parameters for internal methods.
/// </summary>
public CreateParameterMatrix(TableSchema tableSchema)
{
// Initialize the object.
this.ExternalParameterItems = new SortedList <string, ExternalParameterItem>();
// This will create an interface for the 'Create' method.
foreach (KeyValuePair <string, ColumnSchema> columnPair in tableSchema.Columns)
{
// This column is turned into a simple parameter.
ColumnSchema columnSchema = columnPair.Value;
// If a column requires special processing, it is not handled with the rest of the parameters.
bool isOrdinaryColumn = true;
// The row version is not used in the set of Create parameters.
if (columnSchema.IsRowVersion)
{
isOrdinaryColumn = false;
}
// AutoIncremented columns can only be specified as output parameters.
if (columnSchema.IsAutoIncrement)
{
isOrdinaryColumn = false;
SimpleParameterItem simpleParameterItem = new SimpleParameterItem();
simpleParameterItem.ActualDataType = columnSchema.DataType;
simpleParameterItem.ColumnSchema = columnSchema;
simpleParameterItem.DeclaredDataType = columnSchema.DataType;
simpleParameterItem.Description = String.Format("The generated value for the {0} column.", columnSchema.Name);
simpleParameterItem.FieldDirection = FieldDirection.Out;
simpleParameterItem.Name = CommonConversion.ToCamelCase(columnSchema.Name);
this.ExternalParameterItems.Add(simpleParameterItem.Name, simpleParameterItem);
}
// The only complication for ordinary parameters is whether the data type can accept a default or not.
if (isOrdinaryColumn)
{
SimpleParameterItem simpleParameterItem = new SimpleParameterItem();
bool isOptional = columnSchema.IsNullable || columnSchema.DefaultValue != DBNull.Value;
simpleParameterItem.ActualDataType = columnSchema.DataType;
simpleParameterItem.ColumnSchema = columnSchema;
simpleParameterItem.DeclaredDataType = isOptional ? typeof(Object) : columnSchema.DataType;
simpleParameterItem.Description = String.Format("The {0} value for the {1} column.", isOptional ? "optional" : "required", columnSchema.Name);
simpleParameterItem.FieldDirection = FieldDirection.In;
simpleParameterItem.Name = CommonConversion.ToCamelCase(columnSchema.Name);
this.ExternalParameterItems.Add(simpleParameterItem.Name, simpleParameterItem);
}
}
}
/// <summary>
/// Create a table schema from the XML Schema specification.
/// </summary>
/// <param name="dataModelSchema">The data model to which this table belongs.</param>
/// <param name="xmlSchemaElement">The root of the XmlSchema element that describes the table.</param>
public TableSchema(DataModelSchema dataModelSchema, XmlSchemaElement xmlSchemaElement)
: base(dataModelSchema, xmlSchemaElement)
{
// Initialize the object.
this.dataModelSchema = dataModelSchema;
this.isPersistent = GetPersistentIndicator(xmlSchemaElement);
this.name = xmlSchemaElement.Name;
this.columnList = new SortedList <String, ColumnSchema>();
this.constraintList = new SortedList <String, ConstraintSchema>();
this.childRelationList = new SortedList <String, RelationSchema>();
this.parentRelationList = new SortedList <String, RelationSchema>();
// Every table has a row version column which tracks the history of changes to the row.
ColumnSchema rowVersionSchema = new ColumnSchema(this, "RowVersion", typeof(long), false, true,
DBNull.Value, int.MaxValue);
this.columnList.Add(rowVersionSchema.Name, rowVersionSchema);
// Initialize the columns of the table.
Initialize(xmlSchemaElement);
}
/// <summary>
/// Generate the keys on a table.
/// </summary>
/// <param name="streamWriter">The file to which the DDL is written.</param>
/// <param name="tableSchema">The schema description of the table.</param>
private static void GenerateIndices(StreamWriter streamWriter, TableSchema tableSchema)
{
bool isCommentEmitted = false;
// This will add any additional keys to the table.
foreach (KeyValuePair <string, ConstraintSchema> constraintPair in tableSchema.Constraints)
{
if (constraintPair.Value is UniqueConstraintSchema)
{
UniqueConstraintSchema uniqueConstraintSchema = constraintPair.Value as UniqueConstraintSchema;
if (!uniqueConstraintSchema.IsNullable)
{
continue;
}
if (!isCommentEmitted)
{
isCommentEmitted = true;
// This architecture foregoes the 'query' mechanism for finding records in favor of a more direct record identifier.
// Every table has an implicit key created on the row identifier used to quickly find any given record.
streamWriter.WriteLine(" /* Non-Unique Indices */");
}
streamWriter.WriteLine(" create index \"{0}\"", uniqueConstraintSchema.Name);
streamWriter.WriteLine(" on \"{0}\"", tableSchema.Name);
streamWriter.WriteLine(" (");
ColumnSchema[] keyFields = uniqueConstraintSchema.Columns;
for (int columnIndex = 0; columnIndex < keyFields.Length; columnIndex++)
{
ColumnSchema columnSchema = keyFields[columnIndex];
streamWriter.Write(" \"{0}\"", columnSchema.Name);
streamWriter.WriteLine(columnIndex == keyFields.Length - 1 ? "" : ",");
}
streamWriter.WriteLine(" ) on \"PRIMARY\" ");
streamWriter.WriteLine("");
}
}
}
/// <summary>
/// Converts the system type into an equivalent Sql data type.
/// </summary>
/// <param name="type">Represents a system type declaration.</param>
/// <returns>An equivalent SQL datatype.</returns>
private static string GetSqlDataType(ColumnSchema columnSchema)
{
// This will convert the system datatype into the SQL equivalent.
switch (columnSchema.DataType.ToString())
{
case "System.Object":
case "System.Boolean":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Decimal":
case "System.Single":
case "System.Double":
case "System.DateTime":
case "System.Byte[]":
return(GetSqlDataType(columnSchema.DataType));
case "System.Guid":
return(GetSqlDataType(columnSchema.DataType));
case "System.String":
return(String.Format("\"nvarchar\"({0})", columnSchema.MaximumLength == int.MaxValue ? "max" : Convert.ToString(columnSchema.MaximumLength)));
default:
if (columnSchema.DataType.IsEnum)
{
return(GetSqlDataType(Enum.GetUnderlyingType(columnSchema.DataType)));
}
break;
}
// Failure to convert a data type generates an exception.
throw new Exception(String.Format("The type {0} can't be converted to an SQL data type", columnSchema.DataType));
}
/// <summary>
/// Creates the element that describes a column in a table.
/// </summary>
/// <param name="columnSchema">A description of a column.</param>
/// <returns>An element that can be used in an XML Schema document to describe a column.</returns>
private static XElement CreateColumn(ColumnSchema columnSchema)
{
string dataType = String.Empty;
string xmlType = String.Empty;
object defaultValue = null;
switch (columnSchema.DataType.ToString())
{
case "System.Object":
xmlType = "xs:anyType";
break;
case "System.Int32":
defaultValue = 0;
xmlType = "xs:int";
break;
case "System.Int64":
defaultValue = 0L;
xmlType = "xs:long";
break;
case "System.Decimal":
defaultValue = 0.0M;
xmlType = "xs:decimal";
break;
case "System.Boolean":
defaultValue = false;
xmlType = "xs:boolean";
break;
case "System.String":
defaultValue = String.Empty;
xmlType = "xs:string";
break;
case "System.DateTime":
xmlType = "xs:dateTime";
break;
case "System.Byte[]":
xmlType = "xs:base64Binary";
break;
default:
dataType = columnSchema.DataType.AssemblyQualifiedName;
xmlType = "xs:anyType";
break;
}
// <xs:element name="UserId" msdata:DataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
// msprop:Generator_UserColumnName="UserId" msprop:Generator_ColumnVarNameInTable="columnUserId" msprop:Generator_ColumnPropNameInRow="UserId"
// msprop:Generator_ColumnPropNameInTable="UserIdColumn" type="xs:string" minOccurs="0" />
XElement columnElement = new XElement(
SchemaScrubber.xs + "element",
new XAttribute("name", columnSchema.Name));
// Microsoft uses a custom decoration to describe data types that are not part of the cannon XML Schema datatypes.
if (dataType != string.Empty)
{
columnElement.Add(new XAttribute(SchemaScrubber.msdata + "DataType", dataType));
}
// These are Microsoft added decorations for the schema that describe how a generated DataSet should name the internal and external values.
columnElement.Add(
new XAttribute(SchemaScrubber.msprop + "Generator_UserColumnName", columnSchema.Name),
new XAttribute(SchemaScrubber.msprop + "Generator_ColumnPropNameInRow", columnSchema.Name),
new XAttribute(SchemaScrubber.msprop + "Generator_ColumnVarNameInTable", String.Format("column{0}", columnSchema.Name)),
new XAttribute(SchemaScrubber.msprop + "Generator_ColumnPropNameInTable", String.Format("{0}Column", columnSchema.Name)));
if (columnSchema.MaximumLength == int.MaxValue)
{
columnElement.Add(new XAttribute("type", xmlType));
}
else
{
// <xs:simpleType>
// <xs:restriction base="xs:string">
// <xs:maxLength value="128" />
// </xs:restriction>
// </xs:simpleType>
columnElement.Add(
new XElement(
SchemaScrubber.xs + "simpleType",
new XElement(
SchemaScrubber.xs + "restriction",
new XAttribute("base", xmlType),
new XElement(
SchemaScrubber.xs + "maxLength",
new XAttribute("value", columnSchema.MaximumLength)))));
}
// An optional column is identified with a 'minOccurs=0' attribute.
if (columnSchema.IsNullable)
{
columnElement.Add(new XAttribute("minOccurs", 0));
}
// Provide an explicit default value for all column elements.
if (!columnSchema.IsNullable && defaultValue != null)
{
columnElement.Add(new XAttribute("default", defaultValue));
}
// This describes the column of a table.
return(columnElement);
}
