public void GenerateProcedure_Customized_ForUnrootedWorld()
{
// Note it doesn't really make sense to use root copy on the unrooted world. Compare to the corresponding deep copy test.
string procedure = RootCopyGenerator.GenerateProcedure(
catalog: UnrootedWorld.Catalog,
rootTable: UnrootedWorld.NationsTable,
procedureName: "RootCopyNation",
primaryKeyOutputParameterName: "insertedID");
Assert.AreEqual(
@"CREATE PROCEDURE [dbo].[RootCopyNation]
@id INT,
@insertedID INT = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN;
DECLARE @NationIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
DECLARE @ProvinceIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
DECLARE @ResidentIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
MERGE INTO [dbo].[Nations] AS Target
USING (
SELECT *
FROM [dbo].[Nations]
WHERE [ID] = @id
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[Name],
[FoundedDate])
VALUES (
Source.[Name],
Source.[FoundedDate])
OUTPUT Source.[ID], Inserted.[ID]
INTO @NationIDPairs;
SET @insertedID = SCOPE_IDENTITY();
MERGE INTO [dbo].[Provinces] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Provinces] copy
JOIN @NationIDPairs j0
ON copy.[NationID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[NationID],
[Name],
[Motto],
[LeaderResidentID])
VALUES (
j0InsertedID,
Source.[Name],
Source.[Motto],
Source.[LeaderResidentID])
OUTPUT Source.[ID], Inserted.[ID]
INTO @ProvinceIDPairs;
MERGE INTO [dbo].[Residents] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID,
j1.InsertedID j1InsertedID
FROM [dbo].[Residents] copy
JOIN @ProvinceIDPairs j0
ON copy.[ProvinceID] = j0.ExistingID
JOIN @NationIDPairs j1
ON copy.[NationalityNationID] = j1.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[ProvinceID],
[NationalityNationID],
[Name],
[SpouseResidentID],
[FavoriteProvinceID])
VALUES (
j0InsertedID,
j1InsertedID,
Source.[Name],
Source.[SpouseResidentID],
Source.[FavoriteProvinceID])
OUTPUT Source.[ID], Inserted.[ID]
INTO @ResidentIDPairs;
UPDATE copy
SET
copy.[LeaderResidentID] = j0.InsertedID
FROM [dbo].[Provinces] copy
JOIN @ResidentIDPairs j0
ON copy.[LeaderResidentID] = j0.ExistingID
WHERE copy.[ID] IN (SELECT InsertedID FROM @ProvinceIDPairs);
UPDATE copy
SET
copy.[SpouseResidentID] = COALESCE(j0.InsertedID, copy.[SpouseResidentID]),
copy.[FavoriteProvinceID] = COALESCE(j1.InsertedID, copy.[FavoriteProvinceID])
FROM [dbo].[Residents] copy
LEFT JOIN @ResidentIDPairs j0
ON copy.[SpouseResidentID] = j0.ExistingID
LEFT JOIN @ProvinceIDPairs j1
ON copy.[FavoriteProvinceID] = j1.ExistingID
WHERE copy.[ID] IN (SELECT InsertedID FROM @ResidentIDPairs);
COMMIT TRAN;
END;", procedure);
}
public void GenerateProcedure_Scoped_ForRootedWorld()
{
// Nations is the top-level root, but we can also treat provinces as a root, leaving the nations table unaffected.
string procedure = RootCopyGenerator.GenerateProcedure(
catalog: RootedWorld.Catalog,
rootTable: RootedWorld.ProvincesTable);
Assert.AreEqual(
@"CREATE PROCEDURE [dbo].[CopyProvince]
@id INT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN;
DECLARE @ProvinceIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
MERGE INTO [dbo].[Provinces] AS Target
USING (
SELECT *
FROM [dbo].[Provinces]
WHERE [ID] = @id
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[NationID],
[Name],
[Motto])
VALUES (
Source.[NationID],
Source.[Name],
Source.[Motto])
OUTPUT Source.[ID], Inserted.[ID]
INTO @ProvinceIDPairs;
MERGE INTO [dbo].[Residents] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Residents] copy
JOIN @ProvinceIDPairs j0
ON copy.[ProvinceID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[ProvinceID],
[Name])
VALUES (
j0InsertedID,
Source.[Name]);
COMMIT TRAN;
END;", procedure);
}
public void GenerateProcedureBody_Customized_ForRootedWorld()
{
var updateParameterNames = new Dictionary <Column, string>
{
{ RootedWorld.Catalog.FindColumn("Provinces", "Motto"), "toMotto" },
{ RootedWorld.Catalog.FindColumn("Provinces", "Name"), "@toName" },
};
string procedure = RootCopyGenerator.GenerateProcedureBody(
catalog: RootedWorld.Catalog,
rootTable: RootedWorld.NationsTable,
primaryKeyParameterName: "existingNationID",
primaryKeyOutputParameterName: "insertedNationID",
updateParameterNames: updateParameterNames);
Assert.AreEqual(@"
DECLARE @NationIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
DECLARE @ProvinceIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
MERGE INTO [dbo].[Nations] AS Target
USING (
SELECT *
FROM [dbo].[Nations]
WHERE [ID] = @existingNationID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[Name],
[FoundedDate])
VALUES (
Source.[Name],
Source.[FoundedDate])
OUTPUT Source.[ID], Inserted.[ID]
INTO @NationIDPairs;
SET @insertedNationID = SCOPE_IDENTITY();
MERGE INTO [dbo].[Provinces] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Provinces] copy
JOIN @NationIDPairs j0
ON copy.[NationID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[NationID],
[Name],
[Motto])
VALUES (
j0InsertedID,
@toName,
@toMotto)
OUTPUT Source.[ID], Inserted.[ID]
INTO @ProvinceIDPairs;
MERGE INTO [dbo].[Residents] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Residents] copy
JOIN @ProvinceIDPairs j0
ON copy.[ProvinceID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[ProvinceID],
[Name])
VALUES (
j0InsertedID,
Source.[Name]);
", procedure);
}
public void GenerateProcedure_Default_ForRootedWorld()
{
string procedure = RootCopyGenerator.GenerateProcedure(
catalog: RootedWorld.Catalog,
rootTable: RootedWorld.NationsTable);
Assert.AreEqual(
@"CREATE PROCEDURE [dbo].[CopyNation]
@id INT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN;
DECLARE @NationIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
DECLARE @ProvinceIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
MERGE INTO [dbo].[Nations] AS Target
USING (
SELECT *
FROM [dbo].[Nations]
WHERE [ID] = @id
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[Name],
[FoundedDate])
VALUES (
Source.[Name],
Source.[FoundedDate])
OUTPUT Source.[ID], Inserted.[ID]
INTO @NationIDPairs;
MERGE INTO [dbo].[Provinces] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Provinces] copy
JOIN @NationIDPairs j0
ON copy.[NationID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[NationID],
[Name],
[Motto])
VALUES (
j0InsertedID,
Source.[Name],
Source.[Motto])
OUTPUT Source.[ID], Inserted.[ID]
INTO @ProvinceIDPairs;
MERGE INTO [dbo].[Residents] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Residents] copy
JOIN @ProvinceIDPairs j0
ON copy.[ProvinceID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[ProvinceID],
[Name])
VALUES (
j0InsertedID,
Source.[Name]);
COMMIT TRAN;
END;", procedure);
}
