/// <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 GenerateKeys(StreamWriter streamWriter, TableSchema tableSchema)
{
// 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(" /* Unique Constraints */");
streamWriter.WriteLine(" alter table \"{0}\" with nocheck add ", tableSchema.Name);
// Put all the non-nullable unique keys into a list.
List <UniqueConstraintSchema> uniqueKeys = new List <UniqueConstraintSchema>();
foreach (ConstraintSchema constraintSchema in tableSchema.Constraints.Values)
{
if (constraintSchema is UniqueConstraintSchema && !constraintSchema.IsNullable)
{
uniqueKeys.Add(constraintSchema as UniqueConstraintSchema);
}
}
// This will add any additional keys to the table.
for (int keyIndex = 0; keyIndex < uniqueKeys.Count; keyIndex++)
{
UniqueConstraintSchema uniqueSchema = uniqueKeys[keyIndex];
if (uniqueSchema.IsNullable)
{
continue;
}
if (uniqueSchema.IsPrimaryKey)
{
streamWriter.WriteLine(" constraint \"{0}\" primary key clustered", uniqueSchema.Name);
}
else
{
streamWriter.WriteLine(" constraint \"{0}\" unique", uniqueSchema.Name);
}
streamWriter.WriteLine(" (");
ColumnSchema[] keyFields = uniqueSchema.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.Write(" ) on \"PRIMARY\" ");
// End with a comment if there are more foreign constraints comming.
streamWriter.WriteLine(keyIndex < uniqueKeys.Count - 1 ? "," : "");
}
streamWriter.WriteLine("");
// Roll the transaction back if the indices can't be created.
streamWriter.WriteLine("");
}
/// <summary>
/// Creates an array of values from a unique constraint that can be used for finding records.
/// </summary>
/// <param name="uniqueConstraintSchema">A description of a unique constraint.</param>
/// <returns>An array of expressions that can be used as a key for finding records in a table.</returns>
public CodeExpression[] CreateKey(UniqueConstraintSchema uniqueConstraintSchema)
{
// This will cycle through all the foreign and simple parameters looking for any columns that match up to the child
// columns of the constraint. When found, they are placed in the array in the proper order to match up against the
// given unique constraint.
List <CodeExpression> keys = new List <CodeExpression>();
foreach (ColumnSchema uniqueColumn in uniqueConstraintSchema.Columns)
{
foreach (KeyValuePair <string, ExternalParameterItem> parameterPair in this.ExternalParameterItems)
{
// This correlates the unique constraint columns with the variables that have been created in the method to
// hold the key values from the foreign tables. These variables exist outside of the conditional logic that
// finds the parent row, so if the parent key is null, these values will also be null. However, since they're
// still declared, they can be used to construct keys, which is useful when they're optional values.
if (parameterPair.Value is ForeignKeyConstraintParameterItem)
{
ForeignKeyConstraintParameterItem foreignKeyConstraintParameterItem = parameterPair.Value as ForeignKeyConstraintParameterItem;
ForeignKeyConstraintSchema foreignKeyConstraintSchema = foreignKeyConstraintParameterItem.ForeignKeyConstraintSchema;
for (int columnIndex = 0; columnIndex < foreignKeyConstraintSchema.Columns.Length; columnIndex++)
{
if (uniqueColumn == foreignKeyConstraintSchema.Columns[columnIndex])
{
foreach (ForeignKeyVariableItem foreignKeyVariableItem in foreignKeyConstraintParameterItem.ForeignKeyVariables)
{
if (foreignKeyConstraintSchema.Columns[columnIndex] == foreignKeyVariableItem.ColumnSchema)
{
keys.Add(foreignKeyVariableItem.Expression);
}
}
}
}
}
// This will match the columns described in the simple parameters to the columns in the unique constraint.
if (parameterPair.Value is SimpleParameterItem)
{
SimpleParameterItem simpleParameterItem = parameterPair.Value as SimpleParameterItem;
if (uniqueColumn == simpleParameterItem.ColumnSchema)
{
keys.Add(new CodeVariableReferenceExpression(CommonConversion.ToCamelCase(simpleParameterItem.ColumnSchema.Name)));
}
}
}
}
// This array can be used as a key to find the record in a table.
return(keys.ToArray());
}
/// <summary>
/// Creates a collection of tables found in the schema.
/// </summary>
/// <param name="xmlSchema">The schema that describes the data model.</param>
/// <returns>A list of TableSchema objects that describe the tables found in the data model schema.</returns>
private void FirstPass(XmlSchemaSet xmlSchemaSet)
{
// Scan through the schema set looking for table elements. These can either be defined at the root element for a standard
// schema or they can be found as choices of a special element describing a 'DataSet' for the Microsoft version of a
// schema.
foreach (XmlSchemaElement xmlSchemaElement in xmlSchemaSet.GlobalElements.Values)
{
// If the element read from the schema is the Microsoft DataSet element, then the tables are described as choices
// associated with an implicit complex type on that element.
if (ObjectSchema.IsDataSetElement(xmlSchemaElement))
{
// The tables are described as an choices of an implicit (nested) complex type.
if (xmlSchemaElement.SchemaType is XmlSchemaComplexType)
{
// The complex type describes the table.
XmlSchemaComplexType xmlSchemaComplexType = xmlSchemaElement.SchemaType as XmlSchemaComplexType;
// The data model is described as a set of one or more choices of tables.
if (xmlSchemaComplexType.Particle is XmlSchemaChoice)
{
// The data model is described as a set of choices. Each choice represents a table.
XmlSchemaChoice xmlSchemaChoice = xmlSchemaComplexType.Particle as XmlSchemaChoice;
// Create a table for each of the choices described in the complex type.
foreach (XmlSchemaObject choiceObject in xmlSchemaChoice.Items)
{
if (choiceObject is XmlSchemaElement)
{
XmlSchemaElement tableElement = choiceObject as XmlSchemaElement;
this.tableList.Add(tableElement.Name, new TableSchema(this, tableElement));
}
}
}
}
// The constraints describe the columns that are unique for a table.
foreach (XmlSchemaIdentityConstraint xmlSchemaIdentityConstraint in xmlSchemaElement.Constraints)
{
// This describes the columns that must be unique within a table.
if (xmlSchemaIdentityConstraint is XmlSchemaUnique)
{
XmlSchemaUnique xmlSchemaUnique = xmlSchemaIdentityConstraint as XmlSchemaUnique;
UniqueConstraintSchema uniqueConstraintSchema = new UniqueConstraintSchema(this, xmlSchemaUnique);
uniqueConstraintSchema.Table.Add(uniqueConstraintSchema);
}
}
}
}
}
/// <summary>
/// Adds a constraint to the table.
/// </summary>
/// <param name="constraintSchema">The constraint to be added.</param>
public void Add(ConstraintSchema constraintSchema)
{
// This member provides quick access to the primary unique constraint.
if (constraintSchema is UniqueConstraintSchema)
{
UniqueConstraintSchema uniqueSchema = constraintSchema as UniqueConstraintSchema;
if (uniqueSchema.IsPrimaryKey)
{
this.primaryKey = uniqueSchema;
}
}
// All constraints on the table are place in this collection. When the CodeDOM is created, this table will be
// examined and an equivalent constraint will be placed on the target table.
this.constraintList.Add(constraintSchema.Name, constraintSchema);
}
/// <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("");
}
}
}
