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;
}
}
private void AddColumnMap(ColumnRelationship relationship)
{
this.AddColumnMap(((Column)relationship.ParentColumnRef.Object).Name, ((Column)relationship.ChildColumnRef.Object).Name);
}
public static void LoadRelations(nHydrateGeneratorProject project, bool assumeInheritance, string connectionString)
{
var root = (ModelRoot)project.RootController.Object;
var dsRelationship = DatabaseHelper.ExecuteDataset(connectionString, GetSqlForRelationships());
foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
{
var constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
var parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
var childTableName = (string)rowRelationship["FK_TABLE_NAME"];
var parentTable = root.Database.Tables[parentTableName];
var childTable = root.Database.Tables[childTableName];
if ((parentTable != null) && (childTable != null))
{
Relation relation = null;
var isAdd = false;
if (!root.Database.Relations.Contains(constraintName))
{
var roleName = string.Empty;
relation = root.Database.Relations.Add();
relation.ParentTableRef = parentTable.CreateRef();
relation.ChildTableRef = childTable.CreateRef();
relation.ConstraintName = constraintName;
var search = ("_" + childTable.DatabaseName + "_" + parentTable.DatabaseName).ToLower();
roleName = constraintName.ToLower().Replace(search, string.Empty);
if (roleName.Length >= 3) roleName = roleName.Remove(0, 3);
var v = roleName.ToLower();
if (v != "fk") relation.RoleName = v;
isAdd = true;
}
else
{
relation = root.Database.Relations.GetByName(constraintName);
}
//add the column relationship to the relation
var columnRelationship = new ColumnRelationship(relation.Root);
var parentColumnName = (string)rowRelationship["UQ_COLUMN_NAME"];
var childColumnName = (string)rowRelationship["FK_COLUMN_NAME"];
var parentColumns = parentTable.GetColumns().ToList();
var childColumns = childTable.GetColumns().ToList();
if (parentColumns.Count(x => x.Name == parentColumnName) == 1 && (childColumns.Count(x => x.Name == childColumnName) == 1))
{
var parentColumn = parentTable.Columns[parentColumnName].Object as Column;
var childColumn = childTable.Columns[childColumnName].Object as Column;
columnRelationship.ParentColumnRef = parentColumn.CreateRef();
columnRelationship.ChildColumnRef = childColumn.CreateRef();
relation.ColumnRelationships.Add(columnRelationship);
//ONLY ADD THIS RELATION IF ALL WENT WELL
if (isAdd)
parentTable.Relationships.Add(relation.CreateRef());
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
} //Not Contains constraint
}
//Map parent tables if there is 1-1 relation and PK match
//Make sure we have choosen to assume inheritance
if (assumeInheritance)
{
foreach (Relation relation in root.Database.Relations)
{
if (relation.IsOneToOne)
{
var parentTable = (Table)relation.ParentTableRef.Object;
var childTable = (Table)relation.ChildTableRef.Object;
if (parentTable.PrimaryKeyColumns.Count == childTable.PrimaryKeyColumns.Count)
{
var pkMatch = true;
foreach (var k in parentTable.PrimaryKeyColumns)
{
pkMatch |= (childTable.PrimaryKeyColumns.Count(x => x.Name == k.Name) == 1);
}
if (pkMatch && childTable.CanInherit(parentTable))
{
childTable.ParentTable = parentTable;
}
}
}
}
}
//Check for associative tables
foreach (Relation relation in root.Database.Relations)
{
var parentTable = (Table)relation.ParentTableRef.Object;
var childTable = (Table)relation.ChildTableRef.Object;
//If there are 2 PK in the child table and that is all the columns
//and it is a base table
if ((childTable.PrimaryKeyColumns.Count == 2) &&
(childTable.Columns.Count == 2) &&
childTable.ParentTable == null)
{
//If child table has 2 relations comming in
var allRelations = childTable.GetRelationsWhereChild();
if (allRelations.Count() == 2)
{
//Relation relation2 = allRelations.FirstOrDefault(x => x != relation);
//Table parentTable2 = (Table)relation2.ParentTableRef.Object;
childTable.AssociativeTable = true;
}
}
}
}
private void cmdOK_Click(object sender, System.EventArgs e)
{
if (cboChildTable.Text == "")
{
MessageBox.Show("You must specify a foreign table.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else if (this.lvwColumns.Items.Count == 0)
{
MessageBox.Show("You must specify at least one set of key mappings.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//Save
var root = (ModelRoot)this.Relation.Root;
this.Relation.ParentTableRef = root.Database.Tables[lblPrimaryTable.Text].CreateRef();
this.Relation.ChildTableRef = root.Database.Tables[cboChildTable.Text].CreateRef();
this.Relation.Enforce = chkEnforce.Checked;
this.Relation.ColumnRelationships.Clear();
foreach (ListViewItem item in this.lvwColumns.Items)
{
var relationship = new ColumnRelationship(this.Relation.Root);
relationship.ParentColumnRef = ((Column)root.Database.Tables[lblPrimaryTable.Text].Columns[item.SubItems[0].Text].Object).CreateRef();
relationship.ChildColumnRef = ((Column)root.Database.Tables[cboChildTable.Text].Columns[item.SubItems[1].Text].Object).CreateRef();
this.Relation.ColumnRelationships.Add(relationship);
}
this.Relation.RoleName = txtRole.Text;
//if ((!string.IsNullOrEmpty(cboParentTable.Text)) && (!string.IsNullOrEmpty(cboChildTable.Text)))
// this.Relation.RoleName = cboParentTable.Text + "_" + cboChildTable.Text;
//else
// this.Relation.RoleName = string.Empty;
this.DialogResult = DialogResult.OK;
this.Close();
}
private static void UpdateRelations(
nHydrate.Generator.Models.Database newDatabase,
nHydrate.Generator.Models.Database oldDatabase)
{
foreach (Relation relation in newDatabase.Relations)
{
var pTable = oldDatabase.Tables[relation.ParentTable.Name];
var cTable = oldDatabase.Tables[relation.ChildTable.Name];
if (pTable != null && cTable != null)
{
var newRelation = oldDatabase.Relations.Add();
newRelation.RoleName = relation.RoleName;
newRelation.ParentTableRef = pTable.CreateRef();
newRelation.ChildTableRef = cTable.CreateRef();
foreach (ColumnRelationship cr in relation.ColumnRelationships)
{
var pColumn = pTable.GetColumns().FirstOrDefault(x => x.Name == cr.ParentColumn.Name);
var cColumn = cTable.GetColumns().FirstOrDefault(x => x.Name == cr.ChildColumn.Name);
if (pColumn != null && cColumn != null)
{
var newCR = new ColumnRelationship(oldDatabase.Root);
newCR.ParentColumnRef = pColumn.CreateRef();
newCR.ChildColumnRef = cColumn.CreateRef();
newRelation.ColumnRelationships.Add(newCR);
}
}
pTable.Relationships.Add(newRelation.CreateRef());
}
}
}
