public static void VerifyFile(string approvedFilePath, object receivedData)
{
Approvals.Verify(new ConfigurableTempTextFileWriter(approvedFilePath, "" + receivedData));
}
public void TemplateHasCorrectSymbols()
{
Approvals.Verify(Decompiler.Decompile(afterAssemblyPath, "Costura.AssemblyLoader"));
}
public void TestGrid()
{
string grid = StringUtils.DisplayGrid(4, 4, (x, y) => x == y ? "x" : "_");
Approvals.Verify(grid);
}
public void Should_return_a_build_Info_When_Json_content_is_the_one_of_a_master_build()
{
Approvals.Verify(BuildBuildInfoForFile("AppVeyorResult_master.json"));
}
public async Task Abc()
{
await Task.Delay(1);
Approvals.Verify("abc");
}
public void ReturnMoreComplexQueryBuilder()
{
var validColumns = new ValidColumns(new []
{
"ProductId",
"Name",
"ProductNumber",
"MakeFlag",
"FinishedGoodsFlag",
"Color",
"SafetyStockLevel",
"ReorderPoint",
"StandardCost",
"ProductLine",
});
var query = new Query
{
Fields = new[]
{
"ProductId",
"Name",
"ProductNumber",
"ProductLine",
"ReorderPoint",
"StandardCost",
"MakeFlag",
"Color",
},
Filters = new[]
{
new Filter
{
Property = "Name",
Values = new object[] { "Widget-D", "Enameled Widget" }
},
new Filter
{
Property = "ProductLine",
Values = new object[]
{
"(Blanks)",
"Books",
"Widgets",
"Tools",
"Food",
}
},
new Filter
{
Property = "Color",
Values = new object[] { "#AB558A" }
},
},
Sort = "ProductNumber",
Direction = "asc"
};
var builder = new QueryOrchestrator();
var(sqlQueryGenerator, _) = builder.BuildQuery(query, validColumns, AdventureWorksTableName);
var sql = sqlQueryGenerator;
var actual = PoorMansTSqlFormatterLib.SqlFormattingManager.DefaultFormat(sql);
Approvals.Verify(actual);
}
public void Verify()
{
var verify = Ildasm.Decompile(GetAssemblyPath());
Approvals.Verify(verify);
}
public void VerifyMethod()
{
var verify = Ildasm.Decompile(GetAssemblyPath(), "DummyAssembly.Class1::Method");
Approvals.Verify(verify);
}
public void AssureThatEveryChildTableHasADualForeignKeyWithTenantID()
{
const string sql = @"
if object_id('tempdb.dbo.#SingleColumnKeyConstraints') is not null drop table #SingleColumnKeyConstraints
select tc.table_schema, tc.table_name, tc.constraint_schema, tc.constraint_name, tc.constraint_type
into #SingleColumnKeyConstraints
from information_schema.key_column_usage kcu
join information_schema.table_constraints tc on kcu.constraint_schema = tc.constraint_schema and kcu.constraint_name = tc.constraint_name
group by tc.table_schema, tc.table_name, tc.constraint_schema, tc.constraint_name, tc.constraint_type
having count(*) = 1
if object_id('tempdb.dbo.#fk') is not null drop table #fk;
select kcu.table_schema, kcu.table_name, kcu.column_name, ftc.constraint_schema as foreign_table_schema, ftc.table_name as foreign_table_name, fccu.COLUMN_NAME as foreign_column_name
into #fk
from information_schema.key_column_usage kcu
join #SingleColumnKeyConstraints pk on kcu.constraint_schema = pk.constraint_schema and kcu.constraint_name = pk.constraint_name
join information_schema.referential_constraints rc on kcu.constraint_schema = rc.constraint_schema and kcu.constraint_name = rc.constraint_name
join information_schema.table_constraints ftc on rc.unique_constraint_schema = ftc.CONSTRAINT_SCHEMA and rc.unique_constraint_name = ftc.constraint_name
join information_schema.CONSTRAINT_COLUMN_USAGE fccu on ftc.CONSTRAINT_SCHEMA = fccu.CONSTRAINT_SCHEMA and ftc.constraint_name = fccu.CONSTRAINT_NAME
where pk.CONSTRAINT_TYPE = 'FOREIGN KEY'
if object_id('tempdb.dbo.#tenantIDTables') is not null drop table #tenantIDTables;
select t.table_schema,
t.table_name,
c.column_name,
c.Data_type
into #tenantIDTables
from information_schema.columns c
join information_schema.tables t on c.table_schema = t.table_schema and c.table_name = t.table_name
join #fk on c.table_schema = #fk.table_schema and c.table_name = #fk.table_name and #fk.COLUMN_NAME = 'TenantID'
where t.table_type = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID( QUOTENAME(t.TABLE_SCHEMA) + N'.' + QUOTENAME(t.TABLE_NAME)), 'IsMSShipped') = 0 --Filter out Microsoft tables like sysdiagrams
and t.table_name not in ('geometry_columns', 'spatial_ref_sys', 'sysdiagrams')
order by t.table_schema, t.table_name, c.ordinal_position
select
rts.name as ParentTableSchemaName,
rt.name as ParentTableName,
rc.Name ParentKeyColumnName,
pts.name as ChildTableSchemaName,
pt.Name ChildTableName,
pc.Name ChildKeyColumnName,
pc.is_nullable as ChildKeyColumnIsNullable,
ttdk.KeyName,
concat('alter table ', pts.name, '.', pt.name, ' add constraint FK_', pt.name, '_', rt.name, '_', pc.name + '_TenantID foreign key (', pc.name, ', TenantID) references ', rts.name, '.', rt.name, '(', rc.name, ', TenantID)') as ForeignKeySql
from sys.foreign_keys fk
join sys.tables pt on pt.object_id = fk.parent_object_id
join sys.schemas pts on pt.schema_id = pts.schema_id
join sys.tables rt on rt.object_id = fk.referenced_object_id
join sys.schemas rts on rt.schema_id = rts.schema_id
join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
join sys.columns rc on rc.object_id = fkc.referenced_object_id and rc.column_id = fkc.referenced_column_id
join sys.columns pc on pc.object_id = fkc.parent_object_id and pc.column_id = fkc.parent_column_id--
join
(
-- Only do this for single-column foreign keys
select constraint_object_id
from sys.foreign_key_columns
group by constraint_object_id
having COUNT(*) = 1
) SingleColumnKey on SingleColumnKey.constraint_object_id = fk.object_id
join #tenantIDTables tt on rts.name = tt.TABLE_SCHEMA and rt.name = tt.TABLE_NAME and rc.name = tt.COLUMN_NAME
left join
(
select
rts.name as ParentTableSchemaName,
rt.name as ParentTableName,
rc.Name ParentKeyColumnName,
pts.name as ChildTableSchemaName,
pt.Name ChildTableName,
pc.Name ChildKeyColumnName,
pc.is_nullable as ChildKeyColumnIsNullable,
fk.name as KeyName
from sys.foreign_keys fk
join sys.tables pt on pt.object_id = fk.parent_object_id
join sys.schemas pts on pt.schema_id = pts.schema_id
join sys.tables rt on rt.object_id = fk.referenced_object_id
join sys.schemas rts on rt.schema_id = rts.schema_id
join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
join sys.columns rc on rc.object_id = fkc.referenced_object_id and rc.column_id = fkc.referenced_column_id
join sys.columns pc on pc.object_id = fkc.parent_object_id and pc.column_id = fkc.parent_column_id
join
(
select fkc.constraint_object_id
from sys.foreign_key_columns fkc
join sys.columns rc on rc.object_id = fkc.referenced_object_id and rc.column_id = fkc.referenced_column_id
join
(
select fkc.constraint_object_id
from sys.foreign_key_columns fkc
join sys.columns rc on rc.object_id = fkc.referenced_object_id and rc.column_id = fkc.referenced_column_id
join sys.columns pc on pc.object_id = fkc.parent_object_id and pc.column_id = fkc.parent_column_id
group by constraint_object_id
having COUNT(*) = 2
) doublefks on fkc.constraint_object_id = doublefks.constraint_object_id
where rc.name = 'TenantID'
) fkswithTenantID on fkc.constraint_object_id = fkswithTenantID.constraint_object_id
where rc.name != 'TenantID'
) ttdk on pts.name = ttdk.ChildTableSchemaName and pt.name = ttdk.ChildTableName and pc.name = ttdk.ChildKeyColumnName
where rt.name != 'Tenant' and ttdk.KeyName is null
order by pt.Name, rt.name, rc.name, pc.Name";
Approvals.Verify(ExecAdHocSql(sql).TableToHumanReadableString());
}
public void AssureThatForeignKeyNamesMatchNamingStandard()
{
const string sql = @"
declare @maxNameLength int
set @maxNameLength = 128
if object_id('tempdb.dbo.#IdealConstraintName') is not null drop table #IdealConstraintName
select b.ParentSchemaName,
b.ParentTableName,
b.ChildSchemaName,
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.ParentSchemaName,
a.ParentTableName,
a.ChildSchemaName,
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
rsc.name as ParentSchemaName,
rt.name as ParentTableName,
psc.name as ChildSchemaName,
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
join sys.schemas psc on psc.schema_id = pt.schema_id
join sys.schemas rsc on rsc.schema_id = rt.schema_id
) as a
) as b
select db_name() as DatabaseName,
c.ChildSchemaName as SchemaName,
c.ChildTableName as TableName,
c.ConstraintName as ConstraintName,
c.IdealNewConstraintName,
'exec sp_rename ''' + c.ChildSchemaName + '.' + c.ConstraintName + ''', ''' + IdealNewConstraintName + ''', ''OBJECT''' AS QueryToRenameConstraint
from
(
select x.ParentSchemaName, x.ParentTableName, x.ChildSchemaName, 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.ParentSchemaName, c.ParentTableName, c.ChildSchemaName, c.ChildTableName, IdealNewConstraintName
";
var result = ExecAdHocSql(sql);
Approvals.Verify(result.TableToHumanReadableString());
}
public void AndTheCorrectVersionShouldBeOutput()
{
Approvals.Verify(_result.Output, Scrubbers.GuidScrubber);
}
