public ColumnRelationshipCollectionEditorForm(Relation relation)
: this()
{
_relation = relation;
var root = relation.Root as ModelRoot;
Table[] parentList = { };
Table[] childList = { };
cboParentField.Enabled = false;
cboChildField.Enabled = false;
this.EnableButtons();
chkEnforce.Checked = relation.Enforce;
if (relation.ParentTableRef != null)
parentList = root.Database.Tables.GetById(relation.ParentTableRef.Ref);
if (relation.ChildTableRef != null)
childList = root.Database.Tables.GetById(relation.ChildTableRef.Ref);
foreach (Table table in root.Database.Tables)
{
cboChildTable.Items.Add(table);
}
if (parentList.Length > 0) lblPrimaryTable.Text = parentList[0].Name;
if (childList.Length > 0) cboChildTable.SelectedItem = childList[0];
foreach (ColumnRelationship relationship in this.Relation.ColumnRelationships)
{
this.AddColumnMap(relationship);
}
txtRole.Text = relation.RoleName;
this.LoadFields(lblPrimaryTable.Text, cboParentField);
}
public void Populate(Relation relation)
{
lblTable.Text = relation.ParentTable.Name + " -> " + relation.ChildTable.Name;
lblRole.Text = (string.IsNullOrEmpty(relation.RoleName) ? "---" : relation.RoleName);
var text = string.Empty;
foreach (ColumnRelationship r in relation.ColumnRelationships)
{
text += r.ParentColumn.Name + " = " + r.ChildColumn.Name + "\r\n";
}
lblField.Text = text;
}
private void AddItem(Relation relation)
{
var li = new ListViewItem();
li.Tag = relation;
li.Text = relation.ParentTable.Name;
if (relation.ChildTable == null) li.SubItems.Add(string.Empty);
else li.SubItems.Add(relation.ChildTable.Name);
li.SubItems.Add(relation.RoleName);
lvwMembers.Items.Add(li);
}
public RelationshipChecker(Relation relationship)
{
this.Relationship = relationship;
this.TableList = new List<Table>();
}
public virtual void Initialize(IModelObject model)
{
try
{
_model = (ModelRoot)model;
var hasMetaData = false;
foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
{
if (table.CreateMetaData)
hasMetaData = true;
}
Table projectItemDataType = null;
if (hasMetaData)
{
#region Create the PROPERTY_ITEM_DATA_TYPE table
projectItemDataType = _model.Database.Tables.Add("PROPERTY_ITEM_DATA_TYPE");
projectItemDataType.IsMetaDataMaster = true;
Column column = null;
column = _model.Database.Columns.Add("property_item_data_type_id");
column.ParentTableRef = projectItemDataType.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.PrimaryKey = true;
column.Identity = IdentityTypeConstants.None;
column.AllowNull = false;
projectItemDataType.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("name");
column.ParentTableRef = projectItemDataType.CreateRef();
column.DataType = System.Data.SqlDbType.VarChar;
column.Length = 50;
column.AllowNull = false;
projectItemDataType.Columns.Add(column.CreateRef());
#endregion
}
foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
{
if (table.CreateMetaData)
{
Column column = null;
#region Create the PROPERTY_ITEM_DEFINE table
var projectItemDefineTable = _model.Database.Tables.Add(table.DatabaseName + "_PROPERTY_ITEM_DEFINE");
projectItemDefineTable.IsMetaDataDefinition = true;
column = _model.Database.Columns.Add("property_item_define_id");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.PrimaryKey = true;
column.Identity = IdentityTypeConstants.Database;
column.AllowNull = false;
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("name");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.VarChar;
column.Length = 50;
column.AllowNull = false;
column.UIVisible = true;
column.SortOrder = 0;
column.FriendlyName = "Name";
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("property_item_data_type");
column.EnumType = "PropertyBagDataTypeConstants";
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.UIVisible = true;
column.SortOrder = 1;
column.FriendlyName = "Data type";
column.AllowNull = false;
projectItemDefineTable.Columns.Add(column.CreateRef());
//RELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATION
//Add a relation to from the datatype table to this one
var relation2 = _model.Database.Relations.Add();
relation2.ParentTableRef = projectItemDataType.CreateRef();
relation2.ChildTableRef = projectItemDefineTable.CreateRef();
var relationship = new ColumnRelationship((INHydrateModelObject)relation2.Root);
relationship.ParentColumnRef = ((Column)projectItemDataType.Columns[0].Object).CreateRef();
relationship.ChildColumnRef = column.CreateRef();
relation2.ColumnRelationships.Add(relationship);
projectItemDataType.Relationships.Add(relation2.CreateRef());
//RELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATIONRELATION
column = _model.Database.Columns.Add("group");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.VarChar;
column.Length = 50;
column.AllowNull = true;
column.UIVisible = true;
column.SortOrder = 2;
column.FriendlyName = "Group";
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("sort_index");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.AllowNull = false;
column.UIVisible = true;
column.SortOrder = 3;
column.FriendlyName = "Sort order";
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("minimum_value");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.VarChar;
column.Length = 50;
column.UIVisible = true;
column.SortOrder = 4;
column.FriendlyName = "Minimum value";
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("maximum_value");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.VarChar;
column.Length = 50;
column.UIVisible = true;
column.SortOrder = 5;
column.FriendlyName = "Maximum value";
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("max_length");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.UIVisible = true;
column.SortOrder = 6;
column.FriendlyName = "Maximum Length";
projectItemDefineTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("is_required");
column.ParentTableRef = projectItemDefineTable.CreateRef();
column.DataType = System.Data.SqlDbType.Bit;
column.AllowNull = false;
column.UIVisible = true;
column.SortOrder = 7;
column.FriendlyName = "Required";
projectItemDefineTable.Columns.Add(column.CreateRef());
#endregion
#region Create the PROPERTY_ITEM table
var projectItemValueTable = _model.Database.Tables.Add(table.DatabaseName + "_PROPERTY_ITEM");
projectItemValueTable.IsMetaData = true;
column = _model.Database.Columns.Add("property_item_id");
column.ParentTableRef = projectItemValueTable.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.PrimaryKey = true;
column.AllowNull = false;
column.Identity = IdentityTypeConstants.Database;
projectItemValueTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("property_item_define_id");
column.ParentTableRef = projectItemValueTable.CreateRef();
column.DataType = System.Data.SqlDbType.Int;
column.AllowNull = false;
projectItemValueTable.Columns.Add(column.CreateRef());
column = _model.Database.Columns.Add("item_value");
column.ParentTableRef = projectItemValueTable.CreateRef();
column.DataType = System.Data.SqlDbType.VarChar;
column.Length = 1024;
column.AllowNull = false;
projectItemValueTable.Columns.Add(column.CreateRef());
//Create all primary keys
foreach (var pkColumn in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
column = _model.Database.Columns.Add(pkColumn.DatabaseName);
column.ParentTableRef = projectItemValueTable.CreateRef();
column.DataType = pkColumn.DataType;
column.Length = pkColumn.Length;
column.AllowNull = false;
projectItemValueTable.Columns.Add(column.CreateRef());
}
//Add relationship between Definition and Value table
if (true)
{
var relation = new Relation(this._model);
relation.ParentTableRef = projectItemDefineTable.CreateRef();
relation.ChildTableRef = projectItemValueTable.CreateRef();
var colRel = new ColumnRelationship(_model);
colRel.ParentColumnRef = projectItemDefineTable.Columns["property_item_define_id"];
colRel.ChildColumnRef = projectItemValueTable.Columns["property_item_define_id"];
relation.ColumnRelationships.Add(colRel);
relation.RoleName = string.Empty;
_model.Database.Relations.Add(relation);
projectItemDefineTable.Relationships.Add(relation.CreateRef());
}
//Add relationship between Value table and primary table
if (true)
{
var relation = new Relation(this._model);
relation.ParentTableRef = table.CreateRef();
relation.ChildTableRef = projectItemValueTable.CreateRef();
foreach (var pkColumn in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
var colRel = new ColumnRelationship(_model);
colRel.ParentColumnRef = table.Columns[pkColumn.DatabaseName];
colRel.ChildColumnRef = projectItemValueTable.Columns[pkColumn.DatabaseName];
relation.RoleName = string.Empty;
relation.ColumnRelationships.Add(colRel);
table.Relationships.Add(relation.CreateRef());
}
_model.Database.Relations.Add(relation);
}
#endregion
}
}
//_model = (ModelRoot)model;
}
catch (Exception ex)
{
throw;
}
}
public static string CreateFkName(Relation relation)
{
var childTable = relation.ChildTable;
var parentTable = relation.ParentTable;
var model = relation.Root as ModelRoot;
var indexName = "FK_" + relation.DatabaseRoleName + "_" + Globals.GetTableDatabaseName(model, childTable) + "_" + Globals.GetTableDatabaseName(model, parentTable);
var sb = new StringBuilder();
foreach (var c in indexName)
{
if (ValidationHelper.ValidCodeChars.Contains(c)) sb.Append(c);
else sb.Append("_");
}
return sb.ToString();
}
private static string AppendParentTableColumns(Relation relation, Table table)
{
try
{
//Sort the columns by PK/Unique first and then by name
var crList = relation.ColumnRelationships.ToList();
if (crList.Count == 0) return string.Empty;
//Loop through the ordered columns of the parent table's primary key index
//var columnList = crList.OrderBy(x => x.ParentColumn.Name).Select(cr => cr.ParentColumn).ToList();
var columnList = crList.Select(cr => cr.ParentColumn).ToList();
return string.Join(",", columnList.Select(x => " [" + x.Name + "]\r\n"));
}
catch (Exception ex)
{
throw;
}
}
private static string GetFieldNames(Relation relation)
{
var retval = new StringBuilder();
for (var kk = 0; kk < relation.ColumnRelationships.Count; kk++)
{
var columnRelationship = relation.ColumnRelationships[kk];
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
var parentTable = parentColumn.ParentTable;
var childTable = childColumn.ParentTable;
retval.Append("[" + parentTable.DatabaseName + "].[" + parentColumn.DatabaseName + "] -> ");
retval.Append("[" + childTable.DatabaseName + "].[" + childColumn.DatabaseName + "]");
if (kk < relation.ColumnRelationships.Count - 1)
{
retval.Append(", ");
}
}
return retval.ToString();
}
public static string GetSqlAddFK(Relation relation)
{
var indexName = nHydrate.Core.SQLGeneration.SQLEmit.CreateFkName(relation);
indexName = indexName.ToUpper();
var childTable = relation.ChildTable;
var parentTable = relation.ParentTable;
var sb = new StringBuilder();
if (childTable.Generated && parentTable.Generated &&
(parentTable.TypedTable != TypedTableConstants.EnumOnly) &&
(childTable.TypedTable != TypedTableConstants.EnumOnly))
{
sb.AppendLine("--FOREIGN KEY RELATIONSHIP [" + parentTable.DatabaseName + "] -> [" + childTable.DatabaseName + "] (" + GetFieldNames(relation) + ")");
sb.AppendLine("if not exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'F')");
sb.AppendLine("ALTER TABLE [" + childTable.GetSQLSchema() + "].[" + childTable.DatabaseName + "] ADD ");
sb.AppendLine("CONSTRAINT [" + indexName + "] FOREIGN KEY ");
sb.AppendLine("(");
sb.Append(AppendChildTableColumns(relation));
sb.AppendLine(") REFERENCES [" + parentTable.GetSQLSchema() + "].[" +
parentTable.DatabaseName + "] (");
sb.Append(AppendParentTableColumns(relation, childTable));
sb.AppendLine(")");
}
return sb.ToString();
}
public static string GetSqlRemoveFK(Relation relation)
{
var indexName = nHydrate.Core.SQLGeneration.SQLEmit.CreateFkName(relation).ToUpper();
var targetTable = relation.ChildTable;
var sb = new StringBuilder();
sb.AppendLine("--REMOVE FOREIGN KEY");
sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'F')");
sb.AppendLine("ALTER TABLE [" + targetTable.GetSQLSchema() + "].[" + targetTable.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]");
return sb.ToString();
}
//[Browsable(true)]
//[Category("Data")]
//[Description("The date that this object was created.")]
//[ReadOnlyAttribute(true)]
//public DateTime CreatedDate
//{
// get { return _createdDate; }
//}
#endregion
#region Methods
protected internal bool RelationRoleExists(string roleName, Relation skipItem)
{
foreach (Relation relation in this.Relations)
{
//If this is not the skip item and the role matches then return true
if ((relation != skipItem) && (StringHelper.Match(relation.RoleName, roleName, true)))
{
return true;
}
}
return false;
}
protected string RelationKeyParameterList(Relation relation)
{
var output = new StringBuilder();
try
{
foreach (ColumnRelationship columnRelationship in relation.ColumnRelationships)
{
var column = (Column)columnRelationship.ParentColumnRef.Object;
output.Append(column.GetCodeType() + " ");
output.Append(column.CamelName);
output.Append(", ");
}
if (output.Length > 2)
{
output.Remove(output.Length - 2, 2);
}
}
catch (Exception ex)
{
throw new Exception(_currentTable.DatabaseName + ": cannot get key as parameter list", ex);
}
return output.ToString();
}
private void BuildNonPrimaryRelations(Relation relation)
{
var childTable = relation.ChildTableRef.Object as Table;
var objectName = _currentTable.PascalName + childTable.PascalName + relation.RoleName + "RelationKey";
sb.AppendLine(" #region " + objectName);
sb.AppendLine();
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// A relation key object for a '" + _currentTable.PascalName + "' => '" + childTable.PascalName + "' relationship.");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" [Serializable()]");
sb.AppendLine(" [System.CodeDom.Compiler.GeneratedCode(\"nHydrateModelGenerator\", \"" + _model.ModelToolVersion + "\")]");
sb.AppendLine(" public class " + objectName + " : nHydrate.EFCore.DataAccess.IPrimaryKey");
sb.AppendLine(" {");
foreach (var columnRelationship in relation.ColumnRelationships.ToList())
{
var column = columnRelationship.ParentColumnRef.Object as Column;
sb.AppendLine(" private " + column.GetCodeType() + " m" + column.PascalName + ";");
}
sb.AppendLine();
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// The constructor for this object which takes the fields that comprise the key for the '" + _currentTable.PascalName + "' table.");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" public " + objectName + "(" + RelationKeyParameterList(relation) + ")");
sb.AppendLine(" {");
foreach (var columnRelationship in relation.ColumnRelationships.ToList())
{
var column = columnRelationship.ParentColumnRef.Object as Column;
sb.AppendLine(" m" + column.PascalName + " = " + column.CamelName + ";");
}
sb.AppendLine(" }");
sb.AppendLine();
foreach (var columnRelationship in relation.ColumnRelationships.ToList())
{
var column = columnRelationship.ParentColumnRef.Object as Column;
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// A key for the '" + _currentTable.PascalName + "' table.");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" public " + column.GetCodeType() + " " + column.PascalName);
sb.AppendLine(" {");
sb.AppendLine(" get { return m" + column.PascalName + "; }");
sb.AppendLine(" }");
sb.AppendLine();
}
//Equals (Only works for non-composite keys)
if (_currentTable.PrimaryKeyColumns.Count > 0)
{
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// Returns a value indicating whether the current object is equal to a specified object.");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" public override bool Equals(object obj)");
sb.AppendLine(" {");
sb.AppendLine(" if (obj == null) return false;");
sb.AppendLine(" if (obj.GetType() == this.GetType())");
sb.AppendLine(" {");
var index = 0;
sb.AppendLine(" var retval = true;");
foreach (var columnRelationship in relation.ColumnRelationships.ToList())
{
var column = columnRelationship.ParentColumnRef.Object as Column;
sb.AppendLine(" retval &= (this." + column.PascalName + " == ((" + this.GetLocalNamespace() + ".Entity." + objectName + ")obj)." + column.PascalName + ");");
index++;
}
sb.AppendLine(" return retval;");
sb.AppendLine(" }");
sb.AppendLine(" return false;");
sb.AppendLine();
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// Serves as a hash function for this particular type.");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" public override int GetHashCode()");
sb.AppendLine(" {");
sb.AppendLine(" return base.GetHashCode();");
sb.AppendLine(" }");
sb.AppendLine();
}
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" #endregion");
sb.AppendLine();
}