public void AssureThatPrimaryAndAlternateKeyNamesMatchNamingStandard()
{
AssertCustom.IgnoreOnBuildServer();
const string sql = @"
select a.TableName, a.ConstraintName, 'exec sp_rename ''' + a.ConstraintName + ''', ''' + NewConstraintName + ''', ''OBJECT''' AS QueryToRenameConstraint
from
(
SELECT t.TABLE_NAME as TableName, c.CONSTRAINT_NAME as ConstraintName
, substring(case when c.CONSTRAINT_TYPE = 'unique' then 'AK' else 'PK' end + '_' + c.TABLE_NAME +
(
SELECT '_' + cols.COLUMN_NAME AS [text()]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cols
WHERE cols.CONSTRAINT_NAME = c.CONSTRAINT_NAME
ORDER BY cols.ORDINAL_POSITION
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)'), 1, 128) as NewConstraintName
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on t.TABLE_NAME = c.TABLE_NAME
and c.CONSTRAINT_TYPE in ('primary key', 'unique')
where t.TABLE_TYPE = 'BASE TABLE'
and t.TABLE_NAME not in ('sysdiagrams')
) a
where a.ConstraintName != a.NewConstraintName
order by a.TableName, a.ConstraintName";
var result = ExecAdHocSql(sql);
Approvals.Verify(result.TableToHumanReadableString());
}
public void AssureThatPrimaryKeyColumnMatchNamingStandard()
{
AssertCustom.IgnoreOnBuildServer();
const string sql = @"
select a.TableName, a.PrimaryKeyName, 'exec sp_rename ''' + a.TableName + '.' + a.PrimaryKeyName + ''', ''' + NewPrimaryKeyName + '''' AS QueryToRenameConstraint
from
(
SELECT t.TABLE_NAME as TableName,
substring( (
SELECT cols.COLUMN_NAME AS [text()]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cols
WHERE cols.CONSTRAINT_NAME = c.CONSTRAINT_NAME
ORDER BY cols.ORDINAL_POSITION
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)'), 1, 128) as PrimaryKeyName,
t.TABLE_NAME + 'ID' as NewPrimaryKeyName
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on t.TABLE_NAME = c.TABLE_NAME
and c.CONSTRAINT_TYPE in ('primary key')
where t.TABLE_TYPE = 'BASE TABLE'
and t.TABLE_NAME not in ('sysdiagrams', 'DatabaseMigration')
) a
where a.PrimaryKeyName != a.NewPrimaryKeyName
order by a.TableName, a.PrimaryKeyName";
var result = ExecAdHocSql(sql);
Approvals.Verify(result.TableToHumanReadableString());
}
public void AssureNoMissingForeignKeys()
{
AssertCustom.IgnoreOnBuildServer();
const string sql = @" select *
from
(
select c.TABLE_NAME as TableName, c.COLUMN_NAME as ColumnName
from INFORMATION_SCHEMA.COLUMNS c
join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME
where c.COLUMN_NAME like '%ID'
and t.table_type = 'BASE TABLE'
and c.TABLE_NAME + 'ID' != c.COLUMN_NAME
and c.COLUMN_NAME not like 'pisces%' and c.COLUMN_NAME not like '%OBJECTID'
and t.TABLE_NAME not in ('sysdiagrams', 'spatial_ref_sys', 'geometry_columns')
and c.COLUMN_NAME not like '%guid'
) nonpkcolumns
left join
(
select a.ParentTableName, a.ChildTableName, a.ConstraintName, right(ParentColumns, LEN(ParentColumns) - 1) as ParentColumnName, right(ChildColumns, LEN(childcolumns) - 1) as ChildColumnName
from
(
select rt.name as ParentTableName, pt.name as ChildTableName, fk.name as ConstraintName,
(
SELECT '_' + pc.name AS [text()]
from sys.foreign_key_columns fkcparent
join sys.columns pc on pc.object_id = fkcparent.parent_object_id and pc.column_id = fkcparent.parent_column_id
WHERE fkcparent.constraint_object_id = fk.object_id
ORDER BY fkcparent.constraint_column_id
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)') as ParentColumns,
(
SELECT '_' + rc.name AS [text()]
from sys.foreign_key_columns fkcchild
join sys.columns rc on rc.object_id = fkcchild.referenced_object_id and rc.column_id = fkcchild.referenced_column_id
WHERE fkcchild.constraint_object_id = fk.object_id
ORDER BY fkcchild.constraint_column_id
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)') as ChildColumns
from sys.foreign_keys fk
join sys.tables pt on pt.object_id = fk.parent_object_id
join sys.tables rt on rt.object_id = fk.referenced_object_id
) a
) existingfks on nonpkcolumns.TableName = existingfks.ChildTableName and nonpkcolumns.ColumnName = existingfks.ParentColumnName
where existingfks.ParentTableName is null
order by TableName, ColumnName";
var result = ExecAdHocSql(sql);
Approvals.Verify(result.TableToHumanReadableString());
}
public void TestOrganizationAuditLogging()
{
AssertCustom.IgnoreOnBuildServer();
// Get an arbitrary real-word person to do these actions
var neptuneUser = HttpRequestStorage.DatabaseEntities.People.First();
// Create audit logging
// --------------------
// Make a test object and save it
var dbContext = HttpRequestStorage.DatabaseEntities;
var testOrganization = TestFramework.TestOrganization.Create(dbContext);
HttpRequestStorage.DatabaseEntities.SaveChanges(neptuneUser);
// Check that the audit log mentions this object
System.Diagnostics.Trace.WriteLine(string.Format("Looking for Organization named \"{0}\" in Audit Log database entries.", testOrganization.OrganizationName));
Check.Assert(HttpRequestStorage.DatabaseEntities.AuditLogs.Any(al => al.OriginalValue.Contains(testOrganization.OrganizationName)));
// Change audit logging
// --------------------
// Make changes to the original object
var newOrganizationName = TestFramework.MakeTestName("New Organization Name", Organization.FieldLengths.OrganizationName);
testOrganization.OrganizationName = newOrganizationName;
HttpRequestStorage.DatabaseEntities.SaveChanges(neptuneUser);
// Check that the audit log mentions this NEW name
Check.Assert(HttpRequestStorage.DatabaseEntities.AuditLogs.Any(al => al.NewValue.Contains(newOrganizationName)));
// Delete audit logging
// --------------------
HttpRequestStorage.DatabaseEntities.Organizations.DeleteOrganization(testOrganization);
HttpRequestStorage.DatabaseEntities.SaveChanges(neptuneUser);
// Check that the audit log mentions this Organization name as deleted
Check.Assert(
HttpRequestStorage.DatabaseEntities.AuditLogs.SingleOrDefault(
al => al.TableName == "Organization" && al.AuditLogEventTypeID == AuditLogEventType.Deleted.AuditLogEventTypeID && al.RecordID == testOrganization.OrganizationID) != null,
"Could not find deleted Organization record");
}
public void AssureThatForeignKeyNamesMatchNamingStandard()
{
AssertCustom.IgnoreOnBuildServer();
const string sql = @"
declare @maxNameLength int
set @maxNameLength = 128
if object_id('tempdb.dbo.#IdealConstraintName') is not null drop table #IdealConstraintName
select b.ParentTableName,
b.ChildTableName,
b.ConstraintName,
b.IdealNewConstraintName,
SUBSTRING(b.IdealNewConstraintName, 1, @maxNameLength) as TruncatedConstraintName,
-- We are attempting to work around collisions in names once truncated. This is a decent solution for the present time, but it will
-- break down potentially. We don't expect this to happen, but the real solution should be done in C# anyhow.
ROW_NUMBER() over (partition by SUBSTRING(b.IdealNewConstraintName, 1, @maxNameLength) order by b.IdealNewConstraintName ASC) as TruncatedRank,
COUNT(IdealNewConstraintName) over (partition by SUBSTRING(b.IdealNewConstraintName, 1, @maxNameLength)) as TruncatedRankCount
into #IdealConstraintName
from
(
select a.ParentTableName,
a.ChildTableName,
a.ConstraintName,
'FK_' + a.ChildTableName + '_' + a.ParentTableName + case when a.ParentColumns = a.ChildColumns then a.ParentColumns else a.ParentColumns + a.ChildColumns end as IdealNewConstraintName
from
(
select rt.name as ParentTableName, pt.name as ChildTableName, fk.name as ConstraintName,
(
SELECT '_' + pc.name AS [text()]
from sys.foreign_key_columns fkcparent
join sys.columns pc on pc.object_id = fkcparent.parent_object_id and pc.column_id = fkcparent.parent_column_id
WHERE fkcparent.constraint_object_id = fk.object_id
ORDER BY fkcparent.constraint_column_id
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)') as ParentColumns,
(
SELECT '_' + rc.name AS [text()]
from sys.foreign_key_columns fkcchild
join sys.columns rc on rc.object_id = fkcchild.referenced_object_id and rc.column_id = fkcchild.referenced_column_id
WHERE fkcchild.constraint_object_id = fk.object_id
ORDER BY fkcchild.constraint_column_id
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)') as ChildColumns
from sys.foreign_keys fk
join sys.tables pt on pt.object_id = fk.parent_object_id
join sys.tables rt on rt.object_id = fk.referenced_object_id
) as a
) as b
select db_name() as DatabaseName, c.ChildTableName as TableName, c.ConstraintName as ConstraintName, c.IdealNewConstraintName,
'exec sp_rename ''' + c.ConstraintName + ''', ''' + IdealNewConstraintName + ''', ''OBJECT''' AS QueryToRenameConstraint
from
(
select x.ParentTableName, x.ChildTableName, x.ConstraintName,
case
when x.TruncatedRankCount = 1 then x.TruncatedConstraintName
else substring(x.TruncatedConstraintName, 1, @maxNameLength - datalength(cast(x.TruncatedRank as varchar(25)))) + cast(x.TruncatedRank as varchar(25))
end as IdealNewConstraintName
from #IdealConstraintName x
) as c
where c.ConstraintName != c.IdealNewConstraintName
order by c.ParentTableName, c.ChildTableName, IdealNewConstraintName
";
var result = ExecAdHocSql(sql);
Approvals.Verify(result.TableToHumanReadableString());
}
