private static DataIndexDbDefinition CreateIndexDefinition(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
{
var tableName = prefix + table.Name;
var indexColumns = new[] { relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName };
var filter = relationship.ExcludeRemovedItems ? "([IsRemoved]=(0))" : string.Empty;
var index = new DataIndexDbDefinition(GetForeignKeyIndexName(tableName, indexColumns), tableName);
index.IndexFields.AddRange(indexColumns.Select(columnName => new DataIndexFieldDbDefinition(columnName, true)));
index.FilterDefinition = filter;
return index;
}
/// <summary>
/// Gets the add foreign key SQL.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="relationship">The relationship.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected override string GetAddForeignKeySql(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
{
return string.Format(
@"
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'{4}{1}')
BEGIN
ALTER TABLE [{4}{0}] WITH CHECK ADD CONSTRAINT [{4}{1}] FOREIGN KEY([{2}])
REFERENCES [{3}] ([Id])
ALTER TABLE [{4}{0}] CHECK CONSTRAINT [{4}{1}]
END
{5}
",
table.Name,
relationship.Name,
relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName,
relationship.FKTableName,
prefix,
GenerateCreateIndexIfNotExists(CreateIndexDefinition(table, relationship, prefix)));
}
/// <summary>Gets the audit trigger script for multi cr table.</summary>
/// <param name="table">The table.</param>
/// <param name="fieldDefinition">The field definition.</param>
/// <param name="primaryTable">The primary table.</param>
/// <param name="foreignTable">The foreign table.</param>
/// <returns></returns>
private string GetAuditTriggerScriptForMultiCRTable(ITableDefinition table, TableFieldDefinition fieldDefinition, TableRelationshipDefinition primaryTable, TableRelationshipDefinition foreignTable)
{
if (fieldDefinition.ReferenceDisplayFields.Any(x =>
x.SystemName == fieldDefinition.ReferenceDisplayField
&& (x.ColumnType == ColumnTypes.MultiReference || x.IsReverseRef)))
return string.Empty;
if (string.IsNullOrEmpty(fieldDefinition.ReferenceColumnTable) || string.IsNullOrEmpty(fieldDefinition.ReferencedColumnName))
return string.Empty;
var result = string.Format(@"
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]'))
drop trigger [dbo].[{0}_Audit];
GO
CREATE TRIGGER [dbo].[{0}_Audit]
ON [dbo].[{0}]
AFTER INSERT,DELETE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED )
RETURN
", table.Name);
if (fieldDefinition.ReferenceColumnTable.Equals(fieldDefinition.ReferencedProcessName))
{
result += string.Format(@"
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'D'
,p.Id
,'{1}'
,'Link removed: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX))
,NULL
,p.LastModifiedOn
,p.LastModifiedBy
FROM DELETED d
INNER JOIN dbo.{0} p ON p.Id = d.[{4}]
LEFT OUTER JOIN [{2}] si ON si.Id = d.[{5}]
RETURN
END
IF NOT EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'I'
,p.[Id]
,'{1}'
,NULL
,'Link added: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX))
,p.LastModifiedOn
,p.LastModifiedBy
FROM INSERTED i
INNER JOIN dbo.{0} p ON p.Id = i.[{4}]
LEFT OUTER JOIN [{2}] si ON si.Id = i.[{5}]
RETURN
END
END
GO
", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferenceDisplayField, primaryTable.JoinFieldName, foreignTable.JoinFieldName);
}
else
{
result += string.Format(@"
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'D'
,d.{0}Id
,'{1}'
,'Link removed: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX))
,NULL
,t.LastModifiedOn
,t.LastModifiedBy
FROM DELETED d
LEFT OUTER JOIN dbo.{2} p ON p.Id = d.[{5}]
INNER JOIN [{4}] spi ON spi.Id = p.ParentId
INNER JOIN dbo.[{0}] t on t.Id = d.{0}Id
RETURN
END
IF NOT EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'I'
,i.{0}Id
,'{1}'
,NULL
,'Link added: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX))
,t.LastModifiedOn
,t.LastModifiedBy
FROM INSERTED i
LEFT OUTER JOIN dbo.{2} p ON p.Id = i.[{5}]
INNER JOIN [{4}] spi ON spi.Id = p.ParentId
INNER JOIN dbo.[{0}] t on t.Id = i.{0}Id
RETURN
END
END
GO
", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), foreignTable.FKTableName, fieldDefinition.ReferenceDisplayField, fieldDefinition.ReferenceColumnTable, foreignTable.JoinFieldName);
}
return result;
}
/// <summary>Gets the audit trigger script for a many to many table.</summary>
/// <param name="tableName"></param>
/// <param name="primaryTable"></param>
/// <param name="foreignTable"></param>
/// <returns>System.String</returns>
private string GetAuditTriggerScriptForM2MTable(string tableName, TableRelationshipDefinition primaryTable, TableRelationshipDefinition foreignTable)
{
var result = string.Format(@"
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]'))
drop trigger [dbo].[{0}_Audit];
GO
CREATE TRIGGER [dbo].[{0}_Audit]
ON [dbo].[{0}]
AFTER INSERT,DELETE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED )
RETURN
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{1}]
SELECT
'D'
,d.[{2}]
,'{3}'
,'{4} Id=' + CAST(r.Id AS VARCHAR(10))
,NULL
,p.LastModifiedOn
,p.LastModifiedBy
FROM DELETED d
INNER JOIN dbo.{1} p ON p.Id = d.[{2}]
INNER JOIN dbo.{4} r ON r.Id = d.[{5}]
RETURN
END
IF NOT EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{1}]
SELECT
'I'
,i.[{2}]
,'{3}'
,NULL
,'{4} Id=' + CAST(r.Id AS VARCHAR(10))
,p.LastModifiedOn
,p.LastModifiedBy
FROM INSERTED i
INNER JOIN dbo.{1} p ON p.Id = i.[{2}]
INNER JOIN dbo.{4} r ON r.Id = i.[{5}]
RETURN
END
END
GO
"
, tableName, primaryTable.FKTableName, primaryTable.JoinFieldName, primaryTable.FKName, foreignTable.FKTableName, foreignTable.JoinFieldName);
return result;
}
public void PropertyName_JuskFKName_WhenFKNameHasNoIdSuffix()
{
var d = new TableRelationshipDefinition { FKName = "fk" };
Assert.AreEqual("fk", d.PropertyName);
}
public void PropertyName_IsKFNameWithoutIdSuffix()
{
var d = new TableRelationshipDefinition { FKName = "fkId" };
Assert.AreEqual("fk", d.PropertyName);
}
/// <summary>
/// Gets the add foreign key SQL.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="relationship">The relationship.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected override string GetAddForeignKeySql(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
{
return
string.Format(
@"ALTER TABLE ""{4}{0}"" ADD CONSTRAINT ""{4}{1}"" FOREIGN KEY(""{2}"") REFERENCES ""{3}"" (""Id"");;",
OracleNamesTranslator.Translate(table.Name),
OracleNamesTranslator.Translate(relationship.Name),
relationship.IsManyToMany ? OracleNamesTranslator.Translate(relationship.JoinFieldName) : OracleNamesTranslator.Translate(relationship.FKName),
OracleNamesTranslator.Translate(relationship.FKTableName),
OracleNamesTranslator.TranslatePrefix(prefix));
}
protected override string GetAddForeignKeySql(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
{
reportMethodCalled();
return string.Empty;
}
/// <summary> /// Gets the add foreign key SQL. /// </summary> /// <param name="table">The table.</param> /// <param name="relationship">The relationship.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected abstract string GetAddForeignKeySql(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null);
