| | | 1 | | using Microsoft.EntityFrameworkCore.Migrations; |
| | | 2 | | |
| | | 3 | | #nullable disable |
| | | 4 | | |
| | | 5 | | namespace Elsa.Persistence.EFCore.SqlServer.Migrations.Labels |
| | | 6 | | { |
| | | 7 | | /// <inheritdoc /> |
| | | 8 | | public partial class PerTenantLabelUniqueness : Migration |
| | | 9 | | { |
| | | 10 | | private readonly Elsa.Persistence.EFCore.IElsaDbContextSchema _schema; |
| | | 11 | | |
| | | 12 | | /// <inheritdoc /> |
| | 3 | 13 | | public PerTenantLabelUniqueness(Elsa.Persistence.EFCore.IElsaDbContextSchema schema) |
| | | 14 | | { |
| | 3 | 15 | | _schema = schema; |
| | 3 | 16 | | } |
| | | 17 | | |
| | | 18 | | /// <inheritdoc /> |
| | | 19 | | protected override void Up(MigrationBuilder migrationBuilder) |
| | | 20 | | { |
| | | 21 | | // Default tenant is "" (not null). Stamp leftover nulls so the filtered unique |
| | | 22 | | // index (TenantId IS NOT NULL) covers them. Duplicates are not deleted. |
| | 3 | 23 | | migrationBuilder.Sql($""" |
| | 3 | 24 | | UPDATE [{_schema.Schema}].[Labels] |
| | 3 | 25 | | SET [TenantId] = N'' |
| | 3 | 26 | | WHERE [TenantId] IS NULL; |
| | 3 | 27 | | """); |
| | | 28 | | |
| | | 29 | | // No silent dedupe. List leftover keys and abort; operators must resolve them before upgrading. |
| | 3 | 30 | | migrationBuilder.Sql($""" |
| | 3 | 31 | | IF EXISTS ( |
| | 3 | 32 | | SELECT 1 |
| | 3 | 33 | | FROM [{_schema.Schema}].[Labels] |
| | 3 | 34 | | GROUP BY [TenantId], [NormalizedName] |
| | 3 | 35 | | HAVING COUNT(*) > 1 |
| | 3 | 36 | | ) |
| | 3 | 37 | | BEGIN |
| | 3 | 38 | | DECLARE @DuplicateKeys nvarchar(max); |
| | 3 | 39 | | |
| | 3 | 40 | | SELECT @DuplicateKeys = STRING_AGG( |
| | 3 | 41 | | CAST(CONCAT(N'(', ISNULL([TenantId], N'<null>'), N', ', [NormalizedName], N')') AS nvarchar(max) |
| | 3 | 42 | | N'; ' |
| | 3 | 43 | | ) |
| | 3 | 44 | | FROM ( |
| | 3 | 45 | | SELECT [TenantId], [NormalizedName] |
| | 3 | 46 | | FROM [{_schema.Schema}].[Labels] |
| | 3 | 47 | | GROUP BY [TenantId], [NormalizedName] |
| | 3 | 48 | | HAVING COUNT(*) > 1 |
| | 3 | 49 | | ) AS Duplicates; |
| | 3 | 50 | | |
| | 3 | 51 | | DECLARE @ErrorMessage nvarchar(2048); |
| | 3 | 52 | | SET @ErrorMessage = CONCAT( |
| | 3 | 53 | | N'Cannot create unique index IX_Label_TenantId_NormalizedName because leftover duplicate (Tenant |
| | 3 | 54 | | LEFT(@DuplicateKeys, 1500) |
| | 3 | 55 | | ); |
| | 3 | 56 | | THROW 50001, @ErrorMessage, 1; |
| | 3 | 57 | | END |
| | 3 | 58 | | """); |
| | | 59 | | |
| | | 60 | | // Name/NormalizedName are not truncated; list over-length Ids and abort before ALTER. |
| | 3 | 61 | | migrationBuilder.Sql($""" |
| | 3 | 62 | | IF EXISTS ( |
| | 3 | 63 | | SELECT 1 |
| | 3 | 64 | | FROM [{_schema.Schema}].[Labels] |
| | 3 | 65 | | WHERE LEN([Name]) > 255 OR LEN([NormalizedName]) > 255 |
| | 3 | 66 | | ) |
| | 3 | 67 | | BEGIN |
| | 3 | 68 | | DECLARE @OverLengthIds nvarchar(max); |
| | 3 | 69 | | |
| | 3 | 70 | | SELECT @OverLengthIds = STRING_AGG(CAST([Id] AS nvarchar(max)), N', ') |
| | 3 | 71 | | FROM [{_schema.Schema}].[Labels] |
| | 3 | 72 | | WHERE LEN([Name]) > 255 OR LEN([NormalizedName]) > 255; |
| | 3 | 73 | | |
| | 3 | 74 | | DECLARE @ErrorMessage nvarchar(2048); |
| | 3 | 75 | | SET @ErrorMessage = CONCAT( |
| | 3 | 76 | | N'Cannot alter Labels.Name / Labels.NormalizedName to nvarchar(255) because leftover rows exceed |
| | 3 | 77 | | LEFT(@OverLengthIds, 1500) |
| | 3 | 78 | | ); |
| | 3 | 79 | | THROW 50002, @ErrorMessage, 1; |
| | 3 | 80 | | END |
| | 3 | 81 | | """); |
| | | 82 | | |
| | 3 | 83 | | migrationBuilder.AlterColumn<string>( |
| | 3 | 84 | | name: "TenantId", |
| | 3 | 85 | | schema: _schema.Schema, |
| | 3 | 86 | | table: "Labels", |
| | 3 | 87 | | type: "nvarchar(450)", |
| | 3 | 88 | | nullable: true, |
| | 3 | 89 | | oldClrType: typeof(string), |
| | 3 | 90 | | oldType: "nvarchar(max)", |
| | 3 | 91 | | oldNullable: true); |
| | | 92 | | |
| | 3 | 93 | | migrationBuilder.AlterColumn<string>( |
| | 3 | 94 | | name: "Name", |
| | 3 | 95 | | schema: _schema.Schema, |
| | 3 | 96 | | table: "Labels", |
| | 3 | 97 | | type: "nvarchar(255)", |
| | 3 | 98 | | maxLength: 255, |
| | 3 | 99 | | nullable: false, |
| | 3 | 100 | | oldClrType: typeof(string), |
| | 3 | 101 | | oldType: "nvarchar(max)"); |
| | | 102 | | |
| | 3 | 103 | | migrationBuilder.AlterColumn<string>( |
| | 3 | 104 | | name: "NormalizedName", |
| | 3 | 105 | | schema: _schema.Schema, |
| | 3 | 106 | | table: "Labels", |
| | 3 | 107 | | type: "nvarchar(255)", |
| | 3 | 108 | | maxLength: 255, |
| | 3 | 109 | | nullable: false, |
| | 3 | 110 | | oldClrType: typeof(string), |
| | 3 | 111 | | oldType: "nvarchar(max)"); |
| | | 112 | | |
| | 3 | 113 | | migrationBuilder.CreateIndex( |
| | 3 | 114 | | name: "IX_Label_TenantId_NormalizedName", |
| | 3 | 115 | | schema: _schema.Schema, |
| | 3 | 116 | | table: "Labels", |
| | 3 | 117 | | columns: new[] { "TenantId", "NormalizedName" }, |
| | 3 | 118 | | unique: true, |
| | 3 | 119 | | filter: "[TenantId] IS NOT NULL"); |
| | 3 | 120 | | } |
| | | 121 | | |
| | | 122 | | /// <inheritdoc /> |
| | | 123 | | protected override void Down(MigrationBuilder migrationBuilder) |
| | | 124 | | { |
| | 0 | 125 | | migrationBuilder.DropIndex( |
| | 0 | 126 | | name: "IX_Label_TenantId_NormalizedName", |
| | 0 | 127 | | schema: _schema.Schema, |
| | 0 | 128 | | table: "Labels"); |
| | | 129 | | |
| | 0 | 130 | | migrationBuilder.AlterColumn<string>( |
| | 0 | 131 | | name: "TenantId", |
| | 0 | 132 | | schema: _schema.Schema, |
| | 0 | 133 | | table: "Labels", |
| | 0 | 134 | | type: "nvarchar(max)", |
| | 0 | 135 | | nullable: true, |
| | 0 | 136 | | oldClrType: typeof(string), |
| | 0 | 137 | | oldType: "nvarchar(450)", |
| | 0 | 138 | | oldNullable: true); |
| | | 139 | | |
| | 0 | 140 | | migrationBuilder.AlterColumn<string>( |
| | 0 | 141 | | name: "NormalizedName", |
| | 0 | 142 | | schema: _schema.Schema, |
| | 0 | 143 | | table: "Labels", |
| | 0 | 144 | | type: "nvarchar(max)", |
| | 0 | 145 | | nullable: false, |
| | 0 | 146 | | oldClrType: typeof(string), |
| | 0 | 147 | | oldType: "nvarchar(255)", |
| | 0 | 148 | | oldMaxLength: 255); |
| | | 149 | | |
| | 0 | 150 | | migrationBuilder.AlterColumn<string>( |
| | 0 | 151 | | name: "Name", |
| | 0 | 152 | | schema: _schema.Schema, |
| | 0 | 153 | | table: "Labels", |
| | 0 | 154 | | type: "nvarchar(max)", |
| | 0 | 155 | | nullable: false, |
| | 0 | 156 | | oldClrType: typeof(string), |
| | 0 | 157 | | oldType: "nvarchar(255)", |
| | 0 | 158 | | oldMaxLength: 255); |
| | 0 | 159 | | } |
| | | 160 | | } |
| | | 161 | | } |