public void TestGenerateScriptDropConstraint()
{
var p = new MSSQLProvider(GetTestDB(), null);
var sv = p.GetSourceView();
var model = new WXMLModel();
var smc = new SourceToModelConnector(sv, model);
smc.ApplySourceViewToModel(false, relation1to1.Hierarchy, true, true, false);
Assert.AreEqual(28, model.GetActiveEntities().Count());
Assert.AreEqual(32, model.GetSourceFragments().Count());
EntityPropertyDefinition prop = model.GetActiveEntities().SelectMany(item => item.GetProperties().OfType <EntityPropertyDefinition>()).First();
SourceConstraint c = new SourceConstraint(SourceConstraint.UniqueConstraintTypeName, "xxx");
c.SourceFields.AddRange(prop.SourceFields.Cast <SourceFieldDefinition>());
prop.SourceFragment.Constraints.Add(c);
var msc = new ModelToSourceConnector(p.GetSourceView(), model);
var tbl = msc.SourceView.GetSourceFragments().Single(item => item.Selector == prop.SourceFragment.Selector &&
item.Name == prop.SourceFragment.Name);
tbl.Constraints.Add(new SourceConstraint(SourceConstraint.UniqueConstraintTypeName, "xxx"));
string script = msc.GenerateSourceScript(p, false);
Assert.IsFalse(string.IsNullOrEmpty(script), script);
Assert.AreEqual(1, new Regex("DROP CONSTRAINT").Matches(script).Count);
}
private SourceFragmentDefinition GetMasterTable(SourceFragmentDefinition sf,
out List <SourceFragmentRefDefinition.Condition> conditions)
{
SourceConstraint fk = SourceView.GetSourceFields(sf)
.Where(item => item.IsPK)
.SelectMany(item => item.Constraints)
.Single(item => item.ConstraintType == SourceConstraint.ForeignKeyConstraintTypeName);
SourceFragmentDefinition m = null;
conditions = new List <SourceFragmentRefDefinition.Condition>();
foreach (SourceReferences rel in SourceView.GetFKRelations(fk))
{
if (m == null)
{
m = rel.PKField.SourceFragment;
}
conditions.Add(new SourceFragmentRefDefinition.Condition(
rel.PKField.SourceFieldExpression, rel.FKField.SourceFieldExpression
));
}
return(m);
}
private static bool CreateFKDefinition(IEnumerable <ScalarPropertyDefinition> fpk,
SelfRelationTarget rt, SourceFragmentDefinition relSF, StringBuilder script, ISourceProvider provider,
out FKDefinition f)
{
f = new FKDefinition()
{
cols = rt.FieldName,
refCols = fpk.Select(item => item.SourceFieldExpression).ToArray(),
refTbl = fpk.First().SourceFragment
};
if (relSF != null)
{
SourceConstraint fk = relSF.Constraints.SingleOrDefault(item =>
item.ConstraintType == SourceConstraint.ForeignKeyConstraintTypeName &&
rt.FieldName.All(p => item.SourceFields.Any(pkf => pkf.SourceFieldExpression == p))
);
if (fk != null)
{
//if (!rt.FieldName.All(item => fk.SourceFields.Any(pkf => pkf.SourceFieldExpression == item)))
// provider.GenerateDropConstraintScript(relSF, fk.ConstraintName, script);
//else
return(false);
//f.constraintName = fk.ConstraintName;
}
}
return(true);
}
public void TestO2MSimilarRelations()
{
SourceView sv = new SourceView();
SourceFragmentDefinition sf1 = new SourceFragmentDefinition("tbl1", "tbl1", "dbo");
SourceFragmentDefinition sf2 = new SourceFragmentDefinition("tbl2", "tbl2", "dbo");
SourceFieldDefinition pkField = new SourceFieldDefinition(sf1, "id", "int")
{
IsNullable = false
};
sv.SourceFields.Add(pkField);
sv.SourceFields.Add(new SourceFieldDefinition(sf2, "id", "int")
{
IsNullable = false
});
sv.SourceFields.Add(new SourceFieldDefinition(sf2, "prop1_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf2, "prop2_id", "int"));
SourceConstraint pk = new SourceConstraint(SourceConstraint.PrimaryKeyConstraintTypeName, "pk1");
pk.SourceFields.Add(pkField);
SourceConstraint pk2 = new SourceConstraint(SourceConstraint.PrimaryKeyConstraintTypeName, "pk2");
pk2.SourceFields.Add(sv.GetSourceFields(sf2).Single(item => item.SourceFieldExpression == "id"));
SourceConstraint fk1 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "fk1");
fk1.SourceFields.Add(sv.GetSourceFields(sf2).Single(item => item.SourceFieldExpression == "prop1_id"));
SourceConstraint fk2 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "fk2");
fk2.SourceFields.Add(sv.GetSourceFields(sf2).Single(item => item.SourceFieldExpression == "prop2_id"));
sf1.Constraints.Add(pk);
sf2.Constraints.Add(pk2);
sf2.Constraints.Add(fk1);
sf2.Constraints.Add(fk2);
sv.References.Add(new SourceReferences(pk, fk1, pkField,
sv.GetSourceFields(sf2).Single(item => item.SourceFieldExpression == "prop1_id")
));
sv.References.Add(new SourceReferences(pk, fk2, pkField,
sv.GetSourceFields(sf2).Single(item => item.SourceFieldExpression == "prop2_id")
));
WXMLModel model = new WXMLModel();
SourceToModelConnector c = new SourceToModelConnector(sv, model);
c.ApplySourceViewToModel();
}
protected EntityPropertyDefinition AppendFK(EntityDefinition e, SourceFragmentDefinition sf,
SourceConstraint fk, relation1to1 rb, out bool created,
bool transforRawNamesToReadableForm, bool capitalizeNames, bool caseSensitive)
{
created = false;
var rels = SourceView.GetFKRelations(fk);
SourceFragmentDefinition m = rels.First().PKField.SourceFragment;
EntityDefinition re = e;
if (sf != m)
{
re = GetEntity(m, rb, transforRawNamesToReadableForm, capitalizeNames, caseSensitive);
}
string rid = "t" + re.Name;
TypeDefinition td = Model.GetType(rid, false);
if (td == null)
{
td = new TypeDefinition(rid, re);
Model.AddType(td);
}
string propAlias = td.Entity.Name;
if (rels.Count() == 1)
{
propAlias = Trim(GetName(rels.First().FKField.SourceFieldExpression), true);
}
if (capitalizeNames)
{
propAlias = Capitalize(propAlias);
}
string propName = propAlias;
EntityPropertyDefinition ep = null;
//try
//{
ep = (EntityPropertyDefinition)e.OwnProperties
.SingleOrDefault(item => item.Identifier == propAlias);
if (ep == null)
{
ep = e.OwnProperties.OfType <EntityPropertyDefinition>().SingleOrDefault(item => fk.SourceFields.All(sf2 =>
item.SourceFields.Any(sff => sf2.SourceFieldExpression.Trim('[', ']') == sff.SourceFieldExpression.Trim('[', ']'))
));
}
if (ep == null)
{
var one2one = fk.SourceFields.All(sss => sss.IsPK);
if (one2one && e.BaseEntity == re)
{
foreach (var sfd_ in fk.SourceFields)
{
bool x;
var xx = AppendColumn(e, sfd_, out x, transforRawNamesToReadableForm, capitalizeNames, caseSensitive);
RaiseOnPropertyCreated(xx, x);
}
return(null);
}
int cnt = e.OwnProperties.Count(p => p.Name == propName);
if (cnt > 0)
{
propName = propName + cnt;
propAlias = propAlias + cnt;
}
SourceFragmentDefinition sfd = GetSourceFragment(sf);
ep = new EntityPropertyDefinition(propName, propAlias,
Field2DbRelations.None, "Auto generated from constraint " + fk.ConstraintName,
AccessLevel.Private, AccessLevel.Public, td, sfd, e);
e.AddProperty(ep);
created = true;
foreach (SourceReferences rel in rels)
{
SourceFieldDefinition fld = SourceView.GetSourceFields(sf).Single(item => item.SourceFieldExpression == rel.FKField.SourceFieldExpression);
ScalarPropertyDefinition pk = re.GetPkProperties().SingleOrDefault(item => item.SourceFieldExpression == rel.PKField.SourceFieldExpression);
if (pk == null)
{
if (rel.PKConstraint.ConstraintType != SourceConstraint.UniqueConstraintTypeName)
{
throw new WXMLException(string.Format("Cannot find pk for constraint {0}", rel.PKConstraint.ConstraintName));
}
pk = re.GetProperties().OfType <ScalarPropertyDefinition>().Single(item => item.SourceFieldExpression == rel.PKField.SourceFieldExpression);
}
ep.AddSourceField(pk.PropertyAlias,
fld.SourceFieldExpression, null, fld.SourceType, fld.SourceTypeSize, fld.IsNullable, fld.DefaultValue
);
}
}
else
{
if (ep.Description == "Auto generated from constraint " + fk.ConstraintName)
{
ep.PropertyType = td;
}
}
foreach (SourceFieldDefinition pkField in SourceView.GetFKRelations(fk)
.Select(item => item.FKField)
.Where(item => item.IsPK))
{
string pkPropAlias = GetName(pkField.SourceFieldExpression);
if (!SourceView.GetSourceFields(pkField.SourceFragment).Any(item => GetName(item.SourceFieldExpression).Equals(Trim(pkPropAlias, transforRawNamesToReadableForm), StringComparison.InvariantCultureIgnoreCase)))
{
pkPropAlias = Trim(pkPropAlias, transforRawNamesToReadableForm);
}
//string pkPropAlias = Trim(GetName(pkField.SourceFieldExpression), transforRawNamesToReadableForm);
if (capitalizeNames)
{
pkPropAlias = Capitalize(pkPropAlias);
}
string pkPropName = pkPropAlias;
PropertyDefinition pe = e.OwnProperties
.SingleOrDefault(pd => pd.Identifier == pkPropAlias);
Field2DbRelations attrs = pkField.GetAttributes();
TypeDefinition pkType = GetClrType(pkField.SourceType, pkField.IsNullable, ref attrs);
bool pkCreated = pe == null;
if (pkCreated)
{
int cnt = e.OwnProperties.Count(p => p.Name == pkPropName);
if (cnt > 0)
{
pkPropName = pkPropName + cnt;
//pkPropAlias = pkPropAlias + cnt;
}
pe = new ScalarPropertyDefinition(e, pkPropName, pkPropAlias, attrs,
"Auto generated from column " + pkField.SourceFieldExpression,
pkType, pkField, AccessLevel.Private, AccessLevel.Public);
e.AddProperty(pe);
}
else
{
l1:
if (pe is ScalarPropertyDefinition)
{
pe.Attributes |= attrs;
pe.PropertyType = pkType;
((ScalarPropertyDefinition)pe).SourceField = pkField;
}
else
{
int cnt = e.OwnProperties.Count(p => p.Identifier == pkPropAlias);
if (cnt > 0)
{
if (e.OwnProperties.Any(item => item.Identifier == GetName(pkField.SourceFieldExpression)))
{
pkPropAlias = pkPropAlias + cnt;
}
else
{
pkPropAlias = GetName(pkField.SourceFieldExpression);
}
}
pkPropName = pkPropAlias;
pe = e.OwnProperties.SingleOrDefault(item => item.PropertyAlias == pkPropAlias);
if (pe != null)
{
if (pe is EntityPropertyDefinition)
{
throw new WXMLParserException(string.Format("Property {0} expected to be of type ScalarPropertyDefinition", pe.Identifier));
}
goto l1;
}
pe = new ScalarPropertyDefinition(e, pkPropName, pkPropAlias, attrs,
"Auto generated from column " + pkField.SourceFieldExpression,
pkType, pkField, AccessLevel.Private, AccessLevel.Public);
e.AddProperty(pe);
}
}
RaiseOnPropertyCreated(pe, pkCreated);
}
//}
//catch
//{
// int i = 10;
//}
return(ep);
}
public static SourceFieldDefinition Create(SourceView db, DbDataReader reader, bool escapeTableNames, bool escapeColumnNames, bool skipSchema)
{
SourceFieldDefinition c = new SourceFieldDefinition();
string table = reader.GetString(reader.GetOrdinal("table_name"));
string schema = null;
if (!skipSchema && !reader.IsDBNull(reader.GetOrdinal("table_schema")))
{
schema = reader.GetString(reader.GetOrdinal("table_schema"));
if (escapeTableNames)
{
if (!(table.StartsWith("[") || table.EndsWith("]")))
{
table = "[" + table + "]";
}
if (!(schema.StartsWith("[") || schema.EndsWith("]")))
{
schema = "[" + schema + "]";
}
}
}
c.SourceFragment = db.GetOrCreateSourceFragment(schema, table);
c._column = reader.GetString(reader.GetOrdinal("column_name"));
if (escapeColumnNames && !c._column.StartsWith("[") && !c._column.EndsWith("]"))
{
c._column = "[" + c._column + "]";
}
if (!db.GetSourceFields(c.SourceFragment).Any(item => item.SourceFieldExpression == c._column))
{
string yn = reader.GetString(reader.GetOrdinal("is_nullable"));
if (yn == "YES")
{
c.IsNullable = true;
}
else
{
c.IsNullable = false;
}
c.SourceType = reader.GetString(reader.GetOrdinal("data_type"));
try
{
c.IsAutoIncrement = Convert.ToBoolean(reader.GetInt32(reader.GetOrdinal("identity")));
}
catch
{ }
int dfo = reader.GetOrdinal("column_default");
if (!reader.IsDBNull(dfo))
{
c._defaultValue = reader.GetString(dfo);
}
if (!new[] { "ntext", "text", "image" }.Any(item => item == c.SourceType.ToLower()))
{
int sc = reader.GetOrdinal("character_maximum_length");
if (!reader.IsDBNull(sc))
{
c.SourceTypeSize = reader.GetInt32(sc);
}
}
db.SourceFields.Add(c);
}
else
{
c = db.GetSourceFields(c.SourceFragment).Single(item => item.SourceFieldExpression == c._column);
}
try
{
int ct = reader.GetOrdinal("constraint_type");
int cn = reader.GetOrdinal("constraint_name");
if (!reader.IsDBNull(ct))
{
SourceConstraint cns = c.SourceFragment.Constraints
.SingleOrDefault(item => item.ConstraintName == reader.GetString(cn));
if (cns == null)
{
cns = new SourceConstraint(reader.GetString(ct), reader.GetString(cn));
c.SourceFragment.Constraints.Add(cns);
}
cns.SourceFields.Add(c);
}
}
catch { }
return(c);
}
public static SourceView CreateComplexSourceView()
{
SourceView sv = new SourceView();
SourceFragmentDefinition sf1 = new SourceFragmentDefinition("tbl1", "tbl1", "dbo");
SourceFragmentDefinition sf2 = new SourceFragmentDefinition("tbl2", "tbl2", "dbo");
SourceFragmentDefinition sf3 = new SourceFragmentDefinition("tbl3", "tbl3", "dbo");
SourceFragmentDefinition sf4 = new SourceFragmentDefinition("tbl4", "tbl4", "dbo");
SourceFragmentDefinition sf5 = new SourceFragmentDefinition("tbl5", "tbl5", "dbo");
SourceFragmentDefinition sf6 = new SourceFragmentDefinition("tbl6", "tbl6", "dbo");
SourceFieldDefinition pkField = new SourceFieldDefinition(sf1, "id", "int")
{
IsNullable = false
};
sv.SourceFields.Add(pkField);
SourceFieldDefinition pkField2 = new SourceFieldDefinition(sf2, "id", "int")
{
IsNullable = false
};
sv.SourceFields.Add(pkField2);
sv.SourceFields.Add(new SourceFieldDefinition(sf3, "prop1_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf3, "prop2_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf4, "prop1_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf4, "prop2_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf5, "prop1_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf5, "prop2_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf6, "prop1_id", "int"));
sv.SourceFields.Add(new SourceFieldDefinition(sf6, "prop2_id", "int"));
SourceConstraint pk = new SourceConstraint(SourceConstraint.PrimaryKeyConstraintTypeName, "pk1");
pk.SourceFields.Add(pkField);
sf1.Constraints.Add(pk);
SourceConstraint pk2 = new SourceConstraint(SourceConstraint.PrimaryKeyConstraintTypeName, "pk2");
pk2.SourceFields.Add(pkField2);
sf2.Constraints.Add(pk2);
{
SourceConstraint fk1 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl3_fk1");
fk1.SourceFields.Add(sv.GetSourceFields(sf3).Single(item => item.SourceFieldExpression == "prop1_id"));
SourceConstraint fk2 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl3_fk2");
fk2.SourceFields.Add(sv.GetSourceFields(sf3).Single(item => item.SourceFieldExpression == "prop2_id"));
sf3.Constraints.Add(fk1);
sf3.Constraints.Add(fk2);
sv.References.Add(new SourceReferences(pk, fk1, pkField,
sv.GetSourceFields(sf3).Single(item => item.SourceFieldExpression == "prop1_id")
));
sv.References.Add(new SourceReferences(pk2, fk2, pkField2,
sv.GetSourceFields(sf3).Single(item => item.SourceFieldExpression == "prop2_id")
));
}
{
SourceConstraint fk1 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl4_fk1");
fk1.SourceFields.Add(sv.GetSourceFields(sf4).Single(item => item.SourceFieldExpression == "prop1_id"));
SourceConstraint fk2 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl4_fk2");
fk2.SourceFields.Add(sv.GetSourceFields(sf4).Single(item => item.SourceFieldExpression == "prop2_id"));
sf4.Constraints.Add(fk1);
sf4.Constraints.Add(fk2);
sv.References.Add(new SourceReferences(pk, fk1, pkField,
sv.GetSourceFields(sf4).Single(item => item.SourceFieldExpression == "prop1_id")
));
sv.References.Add(new SourceReferences(pk2, fk2, pkField2,
sv.GetSourceFields(sf4).Single(item => item.SourceFieldExpression == "prop2_id")
));
}
{
SourceConstraint fk1 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl5_fk1");
fk1.SourceFields.Add(sv.GetSourceFields(sf5).Single(item => item.SourceFieldExpression == "prop1_id"));
SourceConstraint fk2 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl5_fk2");
fk2.SourceFields.Add(sv.GetSourceFields(sf5).Single(item => item.SourceFieldExpression == "prop2_id"));
sf5.Constraints.Add(fk1);
sf5.Constraints.Add(fk2);
sv.References.Add(new SourceReferences(pk, fk1, pkField,
sv.GetSourceFields(sf5).Single(item => item.SourceFieldExpression == "prop1_id")
));
sv.References.Add(new SourceReferences(pk, fk2, pkField,
sv.GetSourceFields(sf5).Single(item => item.SourceFieldExpression == "prop2_id")
));
}
{
SourceConstraint fk1 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl6_fk1");
fk1.SourceFields.Add(sv.GetSourceFields(sf6).Single(item => item.SourceFieldExpression == "prop1_id"));
SourceConstraint fk2 = new SourceConstraint(SourceConstraint.ForeignKeyConstraintTypeName, "tbl6_fk2");
fk2.SourceFields.Add(sv.GetSourceFields(sf6).Single(item => item.SourceFieldExpression == "prop2_id"));
sf6.Constraints.Add(fk1);
sf6.Constraints.Add(fk2);
sv.References.Add(new SourceReferences(pk, fk1, pkField,
sv.GetSourceFields(sf6).Single(item => item.SourceFieldExpression == "prop1_id")
));
sv.References.Add(new SourceReferences(pk, fk2, pkField,
sv.GetSourceFields(sf6).Single(item => item.SourceFieldExpression == "prop2_id")
));
}
return(sv);
}
private void CreatePrimaryKeys(StringBuilder script, ISourceProvider provider)
{
var pks = Model.GetActiveEntities().SelectMany(item => item.GetPkProperties());
var rels = Model.GetActiveRelations().Where(item => item.Constraint != RelationConstraint.None);
if (pks.Count() == 0 && rels.Count() == 0)
{
return;
}
bool hdr = false;
foreach (SourceFragmentDefinition s in pks.Select(item => item.SourceFragment).Distinct())
{
SourceFragmentDefinition sf = s;
var targetSF = SourceView.GetSourceFragments().SingleOrDefault(item =>
item.Name == sf.Name && item.Selector == sf.Selector);
var tablePKs = pks.Where(item => item.SourceFragment == sf);
//string constraintName = null;
const bool isPK = true;
if (targetSF != null)
{
SourceConstraint pk = targetSF.Constraints.SingleOrDefault(item => item.ConstraintType == SourceConstraint.PrimaryKeyConstraintTypeName);
//if (pk == null)
//{
// isPK = false;
// pk = targetSF.Constraints.SingleOrDefault(item => item.ConstraintType == SourceConstraint.UniqueConstraintTypeName);
//}
if (pk != null)
{
if (tablePKs.All(item => pk.SourceFields.Any(pkf => pkf.SourceFieldExpression == item.SourceFieldExpression)))
{
continue;
}
}
//else
// isPK = true;
}
//if (string.IsNullOrEmpty(constraintName))
//{
// constraintName = "PK_" + sf.Name.Trim(']', '[');
//}
if (!hdr)
{
script.AppendLine("--Creating primary keys");
hdr = true;
}
provider.GenerateCreatePKScript(tablePKs.Select(item => new PropDefinition {
Field = item.SourceField, Attr = item.Attributes, PropType = item.PropertyType
}),
"PK_" + sf.Name.Trim(']', '['), script, isPK, true);
}
foreach (RelationDefinitionBase rel in rels)
{
var targetSF = SourceView.GetSourceFragments().SingleOrDefault(item =>
item.Name == rel.SourceFragment.Name && item.Selector == rel.SourceFragment.Selector);
bool isPK = rel.Constraint == RelationConstraint.PrimaryKey;
if (targetSF != null)
{
if (isPK)
{
SourceConstraint pk = targetSF.Constraints.SingleOrDefault(item =>
item.ConstraintType == SourceConstraint.PrimaryKeyConstraintTypeName);
if (pk != null)
{
if (rel.Left.FieldName.Union(rel.Right.FieldName).All(item => pk.SourceFields.Any(pkf => pkf.SourceFieldExpression == item)))
{
continue;
}
}
}
else
{
if (targetSF.Constraints.Any(item =>
item.ConstraintType == SourceConstraint.UniqueConstraintTypeName &&
rel.Left.FieldName.Union(rel.Right.FieldName).All(fld => item.SourceFields.Any(pkf => pkf.SourceFieldExpression == fld))
))
{
continue;
}
}
}
if (!hdr)
{
script.AppendLine("--Creating primary keys");
hdr = true;
}
provider.GenerateCreatePKScript(rel.Left.FieldName.Union(rel.Right.FieldName)
.Select(item => new PropDefinition {
Field = new SourceFieldDefinition(rel.SourceFragment, item)
})
, isPK?"PK_":"UQ_" + rel.SourceFragment.Name.Trim(']', '['), script, isPK, true);
}
}
