public SQLStatementWriter WriteCreateFKStatement(JMXForeignKey fk, JMXSchema fromSchema = null)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
//Можно сделать сначала с NOCHECK затем CHECK
// ALTER TABLE[SysCat].[SysSchemas] WITH CHECK ADD CONSTRAINT[FK_SYSSCHEMAS_SYSAREAS]([SysAreaID]) REFERENCES[SysCat].[SysAreas] ([ID])
// ALTER TABLE[SysCat].[SysSchemas] CHECK CONSTRAINT[FK_SYSSCHEMAS_SYSAREAS]
if (fk.CheckOption)
{
// Enable for new added rows
Write($"alter table {fromSchema.DbObjectName} with check add constraint [{fk.KeyName}] foreign key (");
}
else
{
Write($"alter table {fromSchema.DbObjectName} with nocheck add constraint [{fk.KeyName}] foreign key (");
}
Write(string.Join(", ", fk.KeyMembers.Select(m => "[" + m.FieldName + "]").ToArray()));
Write(")");
Write($"references {fk.RefDbObjectName} (");
Write(string.Join(", ", fk.RefKeyMembers.Select(m => "[" + m.FieldName + "]").ToArray()));
Write(")\n");
if (fk.CheckOption)
{
// check existing rows
Write($"alter table {fromSchema.DbObjectName} check constraint [{fk.KeyName}]\n");
}
return(this);
}
public SQLStatementWriter WriteDropParentRelationStatement(JMXForeignKey fk)
{
Write("alter table [{0}].[{1}] drop constraint [{2}]\n".ToFormat(
fk.RefDbObjectName.AreaName,
fk.RefDbObjectName.ObjectName,
fk.KeyName));
return(this);
}
public SQLStatementWriter WriteDropFKStatement(JMXForeignKey fk, JMXSchema fromSchema = null)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
Write("alter table [{0}].[{1}] drop constraint [{2}]\n".ToFormat(
fromSchema.DbObjectName.AreaName,
fromSchema.DbObjectName.ObjectName,
fk.KeyName));
return(this);
}
/// <summary>
/// Remove comment && update
/// </summary>
/// <param name="fk"></param>
/// <returns></returns>
public SQLStatementWriter WriteCreateParentRelationStatement(JMXForeignKey fk)
{
//bool withCheck = (bool)fk["CheckOption"];
//string check = withCheck ? "check" : "nocheck";
//// Enable for new added rows
//Write("alter table [{0}].[{1}] with {2} add constraint [{3}] foreign key (".ToFormat(
// (string)fk["ParentObject"]["AreaName"],
// (string)fk["ParentObject"]["ObjectName"],
// check, (string)fk["KeyName"]));
//Write(string.Join(", ", (fk["KeyMembers"] as JsonArray).Select(m => "[" + (string)m["FieldName"] + "]").ToArray()));
//Write(")\n");
//Write($"references [{fk["RefObject"]["AreaName"]}].[{fk["RefObject"]["ObjectName"]}] (");
//Write(string.Join(", ", (fk["RefKeyMembers"] as JsonArray).Select(m => "[" + (string)m["FieldName"] + "]").ToArray()));
//Write(")\n");
//if (withCheck)
// // check existing rows
// Write($"alter table [{fk["ParentObject"]["AreaName"]}].[{fk["ParentObject"]["ObjectName"]}] " +
// $"check constraint [{(string)fk["KeyName"]}]\n");
return(this);
}