/// <summary> /// Create a foreign key constraint on two tables. /// </summary> /// <param name="dataModelSchema">The parent data model schema.</param> /// <param name="xmlSchemaKeyref">The XmlSchema object that describes the foreignn key relation.</param> public RelationSchema(DataModelSchema dataModelSchema, XmlSchemaKeyref xmlSchemaKeyref) { // Initialize the object. this.name = xmlSchemaKeyref.Name; // This will search through each of the tables looking for the parent and child components of the relation. foreach (KeyValuePair <string, TableSchema> keyValuePair in dataModelSchema.Tables) { ConstraintSchema constraintSchema; // This is the parent component of the relation. if (keyValuePair.Value.Constraints.TryGetValue(xmlSchemaKeyref.Refer.Name, out constraintSchema)) { UniqueConstraintSchema uniqueConstraintSchema = constraintSchema as UniqueConstraintSchema; this.parentColumns = uniqueConstraintSchema.Columns; this.parentTable = uniqueConstraintSchema.Table; this.parentKeyConstraint = uniqueConstraintSchema; } // This is the child part of the relation. if (keyValuePair.Value.Constraints.TryGetValue(xmlSchemaKeyref.Name, out constraintSchema)) { ForeignKeyConstraintSchema foreignKeyConstraintSchema = constraintSchema as ForeignKeyConstraintSchema; this.childTable = foreignKeyConstraintSchema.Table; this.childColumns = foreignKeyConstraintSchema.Columns; this.childKeyConstraint = foreignKeyConstraintSchema; } } }
/// <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> /// The foreign constraints can only be evaluated after all the tables, keys and unique constraints have been evaluated. /// </summary> /// <param name="xmlSchema"></param> private void SecondPass(XmlSchemaSet xmlSchemaSet) { // This is the second pass through the schemas. Once the tables, keys and unique constraints have been evaluated, // then the foreign constraints can be constructed and applied to the parent and child tables. foreach (XmlSchemaElement xmlSchemaElement in xmlSchemaSet.GlobalElements.Values) { // Only the Microsoft DataSet element is evaluated for foreign keys. if (ObjectSchema.IsDataSetElement(xmlSchemaElement)) { // This will examine each of the constraints looking for a foreign key description. foreach (XmlSchemaIdentityConstraint xmlSchemaIdentityConstraint in xmlSchemaElement.Constraints) { // Evaluate the foreign keys in the data model. if (xmlSchemaIdentityConstraint is XmlSchemaKeyref) { // This object can be used as a foreign key constraint and, optionally, can be used to describe a // parent/child relationship. XmlSchemaKeyref xmlSchemaKeyref = xmlSchemaIdentityConstraint as XmlSchemaKeyref; // This creates a foreign key. ForeignKeyConstraintSchema foreignKeyConstraintSchema = new ForeignKeyConstraintSchema(this, xmlSchemaIdentityConstraint as XmlSchemaKeyref); // Foreign constraint schemas are always added to the list of constraints on a table. They can also // conditionally become the source for a relationship between two tables. foreignKeyConstraintSchema.Table.Add(foreignKeyConstraintSchema); // Unless specifically instructed to supress the relation, it will be created add added to both the // parent and child tables as well as the data model. XmlAttribute isConstraintOnlyAttribute = ObjectSchema.GetUnhandledAttribute(xmlSchemaIdentityConstraint, QualifiedName.ConstraintOnly); if (isConstraintOnlyAttribute == null || !Convert.ToBoolean(isConstraintOnlyAttribute.Value)) { RelationSchema relationSchema = new RelationSchema(this, xmlSchemaKeyref); relationSchema.ParentTable.ChildRelations.Add(relationSchema.Name, relationSchema); relationSchema.ChildTable.ParentRelations.Add(relationSchema.Name, relationSchema); this.Relations.Add(relationSchema.Name, relationSchema); } } } } } }
/// <summary> /// Creates the element that describes a foreign constraint. /// </summary> /// <param name="foreignConstraintSchema">A description of a foreign constraint.</param> /// <returns>An element that can be used in an XML Schema document to describe a foreign constraint.</returns> private static XElement CreateForeignKey(ForeignKeyConstraintSchema foreignKeyConstraintSchema) { // <xs:keyref name="FK_Entity_AccessControl" refer="EntityKey" msprop:rel_Generator_UserRelationName="FK_Entity_AccessControl" // msprop:rel_Generator_RelationVarName="relationFK_Entity_AccessControl" msprop:rel_Generator_UserChildTable="AccessControl" // msprop:rel_Generator_UserParentTable="Entity" msprop:rel_Generator_ParentPropName="EntityRow" // msprop:rel_Generator_ChildPropName="GetAccessControlRows"> // <xs:selector xpath=".//mstns:AccessControl" /> // <xs:field xpath="mstns:EntityId" /> // </xs:keyref> XElement foreignElement = new XElement( SchemaScrubber.xs + "keyref", new XAttribute("name", foreignKeyConstraintSchema.Name), new XAttribute("refer", foreignKeyConstraintSchema.RelatedTable.GetUniqueConstraint(foreignKeyConstraintSchema.RelatedColumns).Name), new XAttribute(SchemaScrubber.msprop + "rel_Generator_UserRelationName", foreignKeyConstraintSchema.Name), new XAttribute(SchemaScrubber.msprop + "rel_Generator_RelationVarName", String.Format("relation{0}", foreignKeyConstraintSchema.Name)), new XAttribute(SchemaScrubber.msprop + "rel_Generator_UserChildTable", foreignKeyConstraintSchema.Table.Name), new XAttribute(SchemaScrubber.msprop + "rel_Generator_UserParentTable", foreignKeyConstraintSchema.RelatedTable.Name), new XAttribute(SchemaScrubber.msprop + "rel_Generator_ParentPropName", String.Format("{0}Row", foreignKeyConstraintSchema.RelatedTable.Name)), new XAttribute(SchemaScrubber.msprop + "rel_Generator_ChildPropName", String.Format("Get{0}Rows", foreignKeyConstraintSchema.Table.Name))); foreignElement.Add( new XElement( SchemaScrubber.xs + "selector", new XAttribute("xpath", String.Format(".//mstns:{0}", foreignKeyConstraintSchema.Table.Name)))); foreach (ColumnSchema columnSchema in foreignKeyConstraintSchema.Columns) { foreignElement.Add( new XElement( SchemaScrubber.xs + "field", new XAttribute("xpath", String.Format("mstns:{0}", columnSchema.Name)))); } // This describes a foreign constraint on a table. return(foreignElement); }
/// <summary> /// Generates the DDL for 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 GenerateTable(StreamWriter streamWriter, TableSchema tableSchema) { // Generate the prolog for this table. streamWriter.WriteLine("/* The {0} Table */", tableSchema.Name); // Generate a prolog showing the time of creation and the current version of the table for the log file. streamWriter.WriteLine("if not exists (select * from \"VersionControl\" where \"Name\" = '{0}')", tableSchema.Name); streamWriter.WriteLine(" print convert(varchar, getdate(), 120) + 'Z <Undefined>: \"{0}\" doesn''t exist in the catalogs.'", tableSchema.Name); streamWriter.WriteLine("else"); streamWriter.WriteLine("begin"); streamWriter.WriteLine(" declare @revision \"decimal\""); streamWriter.WriteLine(" select @revision = \"Revision\" from \"VersionControl\" where \"Name\" = '{0}'", tableSchema.Name); streamWriter.WriteLine(" print convert(varchar, getdate(), 120) + 'Z Table: \"{0}\", Initial revision: ' + convert(varchar, @revision)", tableSchema.Name); streamWriter.WriteLine("end"); streamWriter.WriteLine(); // The table schema is only run if the version control table indicates that the update is needed. streamWriter.WriteLine("/* This checks the version control table to determine if an update is needed. */"); streamWriter.WriteLine("declare @currentRevision \"decimal\""); streamWriter.WriteLine("declare @requiredRevision \"decimal\""); streamWriter.WriteLine("select @currentRevision = isnull((select \"Revision\" from \"VersionControl\" where \"VersionControl\".\"Name\" = '{0}'), -1.0)", tableSchema.Name); streamWriter.WriteLine("select @requiredRevision = isnull((select \"Revision\" from \"VersionHistory\", \"VersionTag\""); streamWriter.WriteLine(" where \"VersionHistory\".\"Active\" = 1 and \"VersionHistory\".\"Label\" = \"VersionTag\".\"Label\" and \"VersionTag\".\"Name\" = '{0}'), {1})", tableSchema.Name, tableSchema.DataModel.Version); streamWriter.WriteLine("if @currentRevision < {0} and {0} <= @requiredRevision", tableSchema.DataModel.Version); streamWriter.WriteLine("begin"); streamWriter.WriteLine(""); // This transaction will remove the previous version of the table and create the new version. streamWriter.WriteLine(" /* The revision must be completed as a unit. */"); streamWriter.WriteLine(" begin transaction"); streamWriter.WriteLine(""); streamWriter.WriteLine(" /* Remove the object and any dependancies. */"); foreach (KeyValuePair <string, ConstraintSchema> constraintPair in tableSchema.Constraints) { if (constraintPair.Value is ForeignKeyConstraintSchema) { ForeignKeyConstraintSchema foreignKeyConstraintSchema = constraintPair.Value as ForeignKeyConstraintSchema; streamWriter.WriteLine(" if exists (select * from sysobjects where type = 'F' and name = '{0}')", foreignKeyConstraintSchema.Name); streamWriter.WriteLine(" alter table \"{0}\" drop constraint \"{1}\"", foreignKeyConstraintSchema.Table.Name, foreignKeyConstraintSchema.Name); } } streamWriter.WriteLine(" if exists (select * from sysobjects where type = 'U' and name = '{0}')", tableSchema.Name); streamWriter.WriteLine(" drop table \"{0}\"", tableSchema.Name); streamWriter.WriteLine(""); // The table is described here. streamWriter.WriteLine(" /* Create the table. */"); streamWriter.WriteLine(String.Format(" create table \"{0}\" (", tableSchema.Name)); // Generate each of the column descriptions. foreach (ColumnSchema columnSchema in tableSchema.Columns.Values) { streamWriter.WriteLine(String.Format(" \"{0}\" {1} {2},", columnSchema.Name, GetSqlDataType(columnSchema), columnSchema.IsNullable ? "null" : "not null")); } // These columns are always included for house keeping. streamWriter.WriteLine(" \"IsArchived\" \"bit\" not null,"); streamWriter.WriteLine(" \"IsDeleted\" \"bit\" not null,"); // The table is always generated on the primary (default) device. streamWriter.WriteLine(" ) on \"PRIMARY\""); streamWriter.WriteLine(""); // Generate the keys, indices and defaults for this table. GenerateKeys(streamWriter, tableSchema); GenerateIndices(streamWriter, tableSchema); GenerateDefaults(streamWriter, tableSchema); // This will update the version control table to indicate that the table was updated to the new version. streamWriter.WriteLine(" /* Update the versionControl table to reflect the change. */"); streamWriter.WriteLine(" if exists (select * from \"VersionControl\" where \"Name\" = '{0}')", tableSchema.Name); streamWriter.WriteLine(" update \"VersionControl\" set \"Revision\" = {0} where \"Name\" = '{1}'", tableSchema.DataModel.Version, tableSchema.Name); streamWriter.WriteLine(" else"); streamWriter.WriteLine(" insert \"VersionControl\" (\"Name\", \"Revision\") select '{0}', {1}", tableSchema.Name, tableSchema.DataModel.Version); streamWriter.WriteLine(""); // Commit or reject the changes to the table. streamWriter.WriteLine(" /* Commit the changes to the table. */"); streamWriter.WriteLine(" commit transaction"); streamWriter.WriteLine(""); streamWriter.WriteLine("end"); streamWriter.WriteLine("go"); streamWriter.WriteLine(""); // Generate the epilog for a successful update. streamWriter.WriteLine("if @@error = 0"); streamWriter.WriteLine("begin"); streamWriter.WriteLine(" declare @newRevision \"decimal\""); streamWriter.WriteLine(" select @newRevision = \"Revision\" from \"VersionControl\" where \"Name\" = '{0}'", tableSchema.Name); streamWriter.WriteLine(" print convert(varchar, getdate(), 120) + 'Z Table: \"{0}\", Final revision: ' + convert(varchar, @newRevision)", tableSchema.Name); streamWriter.WriteLine("end"); streamWriter.WriteLine("else"); streamWriter.WriteLine("begin"); streamWriter.WriteLine(" declare @oldRevision \"decimal\""); streamWriter.WriteLine(" select @oldRevision = isnull((select \"Revision\" from \"VersionControl\" where \"Name\" = '{0}'), 0.0)", tableSchema.Name); streamWriter.WriteLine(" print convert(varchar, getdate(), 120) + 'Z Table: \"{0}\", Error upgrading from revision: ' + convert(varchar, @oldRevision)", tableSchema.Name); streamWriter.WriteLine("end"); streamWriter.WriteLine("go"); streamWriter.WriteLine(""); }