| | | 1 | | namespace ZhEaIsNsAaBn.DbSeeder.SqlServer; |
| | | 2 | | |
| | | 3 | | /// <summary> |
| | | 4 | | /// T-SQL text for the seed provider. Identifiers come <b>only</b> from EF / database metadata and are always bracket-qu |
| | | 5 | | /// (<c>QUOTENAME</c> rules: <c>]</c> doubled); every value is a parameter. |
| | | 6 | | /// </summary> |
| | | 7 | | internal static class SqlServerSeedSql |
| | | 8 | | { |
| | | 9 | | public const string HistoryTable = "__SeedMigrationsHistory"; |
| | | 10 | | public const string AuditTable = "__SeedMigrationsAudit"; |
| | | 11 | | public const string LockResource = "__SeedMigrations"; |
| | | 12 | | |
| | | 13 | | public const string CreateHistory = $""" |
| | | 14 | | IF OBJECT_ID( N'[{HistoryTable}]', N'U' ) IS NULL |
| | | 15 | | CREATE TABLE [{HistoryTable}] ( |
| | | 16 | | [MigrationId] nvarchar(150) NOT NULL, |
| | | 17 | | [ContextKey] nvarchar(300) NOT NULL, |
| | | 18 | | [Checksum] char(64) NOT NULL, |
| | | 19 | | [AppliedOn] datetime2 NOT NULL, |
| | | 20 | | [ProductVersion] nvarchar(32) NOT NULL, |
| | | 21 | | CONSTRAINT [PK_{HistoryTable}] PRIMARY KEY ( [MigrationId], [ContextKey] ) |
| | | 22 | | ); |
| | | 23 | | """; |
| | | 24 | | |
| | | 25 | | public const string CreateAudit = $""" |
| | | 26 | | IF OBJECT_ID( N'[{AuditTable}]', N'U' ) IS NULL |
| | | 27 | | CREATE TABLE [{AuditTable}] ( |
| | | 28 | | [Id] bigint IDENTITY NOT NULL CONSTRAINT [PK_{AuditTable}] PRIMARY |
| | | 29 | | [MigrationId] nvarchar(150) NOT NULL, |
| | | 30 | | [ContextKey] nvarchar(300) NOT NULL, |
| | | 31 | | [Operation] nvarchar(20) NOT NULL, |
| | | 32 | | [StartedOn] datetime2 NOT NULL, |
| | | 33 | | [FinishedOn] datetime2 NOT NULL, |
| | | 34 | | [Succeeded] bit NOT NULL, |
| | | 35 | | [ErrorCode] nvarchar(100) NULL, |
| | | 36 | | [RowsInserted] int NOT NULL, |
| | | 37 | | [RowsUpdated] int NOT NULL, |
| | | 38 | | [RowsDeleted] int NOT NULL, |
| | | 39 | | [DbLogin] nvarchar(256) NOT NULL, |
| | | 40 | | [OsUser] nvarchar(256) NULL, |
| | | 41 | | [MachineName] nvarchar(256) NULL, |
| | | 42 | | [ToolVersion] nvarchar(32) NOT NULL, |
| | | 43 | | [Details] nvarchar(max) NULL |
| | | 44 | | ); |
| | | 45 | | """; |
| | | 46 | | |
| | | 47 | | public const string AcquireLock = $""" |
| | | 48 | | DECLARE @Result int; |
| | | 49 | | EXEC @Result = sp_getapplock @Resource = N'{LockResource}', @LockMode = 'Exclusiv |
| | | 50 | | SELECT @Result; |
| | | 51 | | """; |
| | | 52 | | |
| | | 53 | | public const string ReleaseLock = $"EXEC sp_releaseapplock @Resource = N'{LockResource}', @LockOwner = 'Session';"; |
| | | 54 | | |
| | | 55 | | public const string HistoryExists = $"SELECT CASE WHEN OBJECT_ID( N'[{HistoryTable}]', N'U' ) IS NULL THEN 0 ELSE 1 |
| | | 56 | | |
| | | 57 | | public const string ReadHistory = $"SELECT [MigrationId], [ContextKey], [Checksum], [AppliedOn], [ProductVersion] FR |
| | | 58 | | |
| | | 59 | | public const string InsertHistory = $""" |
| | | 60 | | INSERT INTO [{HistoryTable}] ( [MigrationId], [ContextKey], [Checksum], [Applie |
| | | 61 | | VALUES ( @MigrationId, @ContextKey, @Checksum, @AppliedOn, @ProductVersion ); |
| | | 62 | | """; |
| | | 63 | | |
| | | 64 | | public const string DeleteHistory = $"DELETE FROM [{HistoryTable}] WHERE [MigrationId] = @MigrationId AND [ContextKe |
| | | 65 | | |
| | | 66 | | public const string InsertAudit = $""" |
| | | 67 | | INSERT INTO [{AuditTable}] ( [MigrationId], [ContextKey], [Operation], [StartedOn |
| | | 68 | | [RowsInserted], [RowsUpdated], [RowsDeleted], [DbLog |
| | | 69 | | VALUES ( @MigrationId, @ContextKey, @Operation, @StartedOn, @FinishedOn, @Succeed |
| | | 70 | | @RowsInserted, @RowsUpdated, @RowsDeleted, SUSER_SNAME(), @OsUser, @Mach |
| | | 71 | | """; |
| | | 72 | | |
| | | 73 | | /// Per target column: collation (text columns only) and whether it is an identity — from the database, the source o |
| | | 74 | | public const string TableColumns = "SELECT [name], [collation_name], [is_identity] FROM sys.columns WHERE [object_id |
| | | 75 | | |
| | | 76 | | /// Foreign keys pointing at a table, from the database (the source of truth, not the EF model). |
| | | 77 | | public const string ReferencingKeys = """ |
| | | 78 | | SELECT fk.[object_id], OBJECT_SCHEMA_NAME( fk.[parent_object_id] ), OBJECT_NAM |
| | | 79 | | FROM sys.foreign_keys AS fk |
| | | 80 | | INNER JOIN sys.foreign_key_columns AS fkc ON fkc.[constraint_object_id] = fk.[ |
| | | 81 | | INNER JOIN sys.columns AS pc ON pc.[object_id] = fkc.[parent_object_id] AND pc |
| | | 82 | | INNER JOIN sys.columns AS rc ON rc.[object_id] = fkc.[referenced_object_id] AN |
| | | 83 | | WHERE fk.[referenced_object_id] = OBJECT_ID( @Table ) |
| | | 84 | | ORDER BY fk.[object_id], fkc.[constraint_column_id]; |
| | | 85 | | """; |
| | | 86 | | |
| | | 87 | | |
| | | 88 | | /// Primary-key columns of a table, in key order (the force tree tracks rows by them). |
| | | 89 | | public const string PrimaryKeyColumns = """ |
| | | 90 | | SELECT c.[name] |
| | | 91 | | FROM sys.indexes AS i |
| | | 92 | | INNER JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic. |
| | | 93 | | INNER JOIN sys.columns AS c ON c.[object_id] = ic.[object_id] AND c.[column_ |
| | | 94 | | WHERE i.[object_id] = OBJECT_ID( @Table ) AND i.[is_primary_key] = 1 |
| | | 95 | | ORDER BY ic.[key_ordinal]; |
| | | 96 | | """; |
| | | 97 | | |
| | | 98 | | public const string TableName = "SELECT OBJECT_SCHEMA_NAME( OBJECT_ID( @Table ) ), OBJECT_NAME( OBJECT_ID( @Table ) |
| | | 99 | | |
| | | 100 | | |
| | | 101 | | /// A Unicode SQL string literal: <c>N'…'</c> with <c>'</c> doubled. |
| | 114 | 102 | | public static string Text( string value ) => "N'" + value.Replace( "'", "''", StringComparison.Ordinal ) + "'"; |
| | | 103 | | |
| | | 104 | | /// <c>[name]</c>, with <c>]</c> doubled (what <c>QUOTENAME</c> does). |
| | 32914 | 105 | | public static string Quote( string identifier ) => "[" + identifier.Replace( "]", "]]", StringComparison.Ordinal ) + |
| | | 106 | | |
| | | 107 | | /// <c>[schema].[table]</c>, or <c>[table]</c> for the default schema. |
| | 3472 | 108 | | public static string Table( SeedTable table ) => table.Schema is null ? Quote( table.Name ) : $"{Quote( table.Schema |
| | | 109 | | |
| | | 110 | | /// <c>CREATE TABLE #seed_n</c> from model metadata (never <c>SELECT … INTO</c>, which would copy <c>IDENTITY</c>). |
| | | 111 | | /// text columns get the target column's own collation, so comparisons never hit a collation conflict with tempdb. |
| | | 112 | | public static string CreateTemp( string tempTable, IEnumerable<SeedColumn> columns, IReadOnlyDictionary<string, stri |
| | 1619 | 113 | | => $"CREATE TABLE {tempTable} ( {string.Join( ", ", columns.Select( c => $"{Quote( c.ColumnName )} {c.StoreType} |
| | | 114 | | |
| | | 115 | | /// <c>t.[a] = s.[a] AND …</c> |
| | | 116 | | public static string Join( IEnumerable<SeedColumn> columns, string left, string right ) |
| | 1622 | 117 | | => string.Join( " AND ", columns.Select( c => $"{left}.{Quote( c.ColumnName )} = {right}.{Quote( c.ColumnName )} |
| | | 118 | | |
| | | 119 | | /// The upsert batch (§7): null-safe <c>UPDATE … WHERE EXISTS ( s EXCEPT t )</c>, then <c>INSERT … WHERE NOT EXISTS< |
| | | 120 | | /// <c>IDENTITY_INSERT</c> is switched off again even when the insert fails (it is a session setting, not rolled bac |
| | | 121 | | /// Update / insert values are the parameters <c>@u{i}</c> / <c>@i{i}</c> unless <c>updateValue</c> / <c>insertValue |
| | | 122 | | /// (a script passes literals); <c>guardIdentity</c> (a script) switches <c>IDENTITY_INSERT</c> only when the target |
| | | 123 | | /// the identity columns. |
| | | 124 | | public static string Upsert( |
| | | 125 | | SeedTableWrite write, |
| | | 126 | | string tempTable, |
| | | 127 | | IReadOnlySet<string> identityColumns, |
| | | 128 | | Func<int, string>? updateValue = null, |
| | | 129 | | Func<int, string>? insertValue = null, |
| | | 130 | | bool guardIdentity = false |
| | | 131 | | ) |
| | | 132 | | { |
| | 1167 | 133 | | var target = Table( write.Table ); |
| | 1167 | 134 | | var match = write.Table.MatchColumns; |
| | 1167 | 135 | | var on = Join( match, "t", "s" ); |
| | 1167 | 136 | | var keys = match.Select( c => c.ColumnName ).ToHashSet( StringComparer.Ordinal ); |
| | 1167 | 137 | | var compare = write.Columns.Where( c => !keys.Contains( c.ColumnName ) ).ToList(); |
| | 1167 | 138 | | var set = compare.Where( c => !identityColumns.Contains( c.ColumnName ) ).ToList(); |
| | 1167 | 139 | | var sql = new StringBuilder(); |
| | | 140 | | |
| | | 141 | | |
| | 1167 | 142 | | if ( set.Count > 0 ) |
| | | 143 | | { |
| | 1167 | 144 | | var assignments = set.Select( c => $"t.{Quote( c.ColumnName )} = s.{Quote( c.ColumnName )}" ) |
| | 1167 | 145 | | .Concat( write.UpdateValues.Select( ( v, i ) => $"t.{Quote( v.Column.ColumnName )} = {( |
| | | 146 | | |
| | 1167 | 147 | | sql.AppendLine( CultureInfo.InvariantCulture, $"UPDATE t SET {string.Join( ", ", assignments )}" ) |
| | 1167 | 148 | | .AppendLine( CultureInfo.InvariantCulture, $"FROM {target} AS t INNER JOIN {tempTable} AS s ON {on}" ) |
| | 1167 | 149 | | .AppendLine( CultureInfo.InvariantCulture, $"WHERE EXISTS ( SELECT {Columns( compare, "s" )} EXCEPT SELEC |
| | 1167 | 150 | | .AppendLine( "SET @Updated = @@ROWCOUNT;" ); |
| | | 151 | | } |
| | 0 | 152 | | else { sql.AppendLine( "SET @Updated = 0;" ); } |
| | | 153 | | |
| | | 154 | | |
| | 1167 | 155 | | var insertColumns = write.Columns.Select( c => Quote( c.ColumnName ) ).Concat( write.InsertValues.Select( v => Q |
| | 1167 | 156 | | var insertValues = write.Columns.Select( c => $"s.{Quote( c.ColumnName )}" ).Concat( write.InsertValues.Select( |
| | 1167 | 157 | | var insert = $""" |
| | 1167 | 158 | | INSERT INTO {target} ( {string.Join( ", ", insertColumns )} ) |
| | 1167 | 159 | | SELECT {string.Join( ", ", insertValues )} FROM {tempTable} AS s |
| | 1167 | 160 | | WHERE NOT EXISTS ( SELECT 1 FROM {target} AS t WHERE {on} ); |
| | 1167 | 161 | | SET @Inserted = @@ROWCOUNT; |
| | 1167 | 162 | | """; |
| | | 163 | | |
| | 1167 | 164 | | var identity = write.Columns.Where( c => identityColumns.Contains( c.ColumnName ) ).ToList(); |
| | | 165 | | |
| | 1167 | 166 | | if ( identity.Count > 0 ) |
| | | 167 | | { |
| | 236 | 168 | | var guard = guardIdentity |
| | 236 | 169 | | ? $"IF EXISTS ( SELECT 1 FROM sys.identity_columns WHERE [object_id] = OBJECT_ID( {Text( tar |
| | 236 | 170 | | : string.Empty; |
| | | 171 | | |
| | 236 | 172 | | sql.AppendLine( CultureInfo.InvariantCulture, $"{guard}SET IDENTITY_INSERT {target} ON;" ) |
| | 236 | 173 | | .AppendLine( "BEGIN TRY" ) |
| | 236 | 174 | | .AppendLine( insert ) |
| | 236 | 175 | | .AppendLine( "END TRY" ) |
| | 236 | 176 | | .AppendLine( CultureInfo.InvariantCulture, $"BEGIN CATCH {guard}SET IDENTITY_INSERT {target} OFF; THROW; |
| | 236 | 177 | | .AppendLine( CultureInfo.InvariantCulture, $"{guard}SET IDENTITY_INSERT {target} OFF;" ); |
| | | 178 | | } |
| | 931 | 179 | | else { sql.AppendLine( insert ); } |
| | | 180 | | |
| | | 181 | | |
| | 1167 | 182 | | return sql.AppendLine( CultureInfo.InvariantCulture, $"DROP TABLE {tempTable};" ).ToString(); |
| | | 183 | | } |
| | | 184 | | |
| | | 185 | | public static string Delete( SeedTable table, string tempTable ) |
| | 99 | 186 | | => $""" |
| | 99 | 187 | | DELETE t FROM {Table( table )} AS t INNER JOIN {tempTable} AS s ON {Join( table.MatchColumns, "t", "s" )}; |
| | 99 | 188 | | SET @Count = @@ROWCOUNT; |
| | 99 | 189 | | DROP TABLE {tempTable}; |
| | 99 | 190 | | """; |
| | | 191 | | |
| | | 192 | | /// Soft delete (<paramref name="flagged"/> = <see langword="false"/>: only rows not flagged yet, a deleted row is n |
| | | 193 | | /// restore (<see langword="true"/>: only rows flagged). |
| | | 194 | | public static string SetFlagged( SeedTable table, string tempTable, IReadOnlyList<SeedColumnValue> values, bool flag |
| | 230 | 195 | | => $""" |
| | 230 | 196 | | UPDATE t SET {string.Join( ", ", values.Select( ( v, i ) => $"t.{Quote( v.Column.ColumnName )} = {( value is |
| | 230 | 197 | | FROM {Table( table )} AS t INNER JOIN {tempTable} AS s ON {Join( table.MatchColumns, "t", "s" )} |
| | 230 | 198 | | WHERE t.{Quote( table.SoftDeleteFlag!.ColumnName )} = {( flagged ? 1 : 0 )}; |
| | 230 | 199 | | SET @Count = @@ROWCOUNT; |
| | 230 | 200 | | DROP TABLE {tempTable}; |
| | 230 | 201 | | """; |
| | | 202 | | |
| | | 203 | | /// Rows of <paramref name="referencing"/> that point (through <paramref name="pairs"/>) at the seed rows in the tem |
| | | 204 | | /// self-reference, rows that are themselves being deleted don't count. |
| | | 205 | | public static string CountReferences( SeedTable table, string tempTable, string referencing, IReadOnlyList<(string C |
| | | 206 | | { |
| | 87 | 207 | | var through = string.Join( " AND ", pairs.Select( p => $"r.{Quote( p.Column )} = t.{Quote( p.Referenced )}" ) ); |
| | 87 | 208 | | var sql = $"SELECT COUNT_BIG( * ) FROM {referencing} AS r INNER JOIN {Table( table )} AS t ON {through} INNE |
| | | 209 | | |
| | 87 | 210 | | return isSelf ? $"{sql} WHERE NOT EXISTS ( SELECT 1 FROM {tempTable} AS x WHERE {Join( table.MatchColumns, "r", |
| | | 211 | | } |
| | | 212 | | |
| | | 213 | | |
| | 2334 | 214 | | private static string Columns( IEnumerable<SeedColumn> columns, string alias ) => string.Join( ", ", columns.Select( |
| | | 215 | | |
| | | 216 | | /// <c>sys.columns.collation_name</c> is set for text columns only. A collation can't be bracket-quoted, so it must |
| | | 217 | | private static string Collation( SeedColumn column, IReadOnlyDictionary<string, string> collations ) |
| | | 218 | | { |
| | 6308 | 219 | | if ( !collations.TryGetValue( column.ColumnName, out var collation ) ) { return string.Empty; } |
| | | 220 | | |
| | 1946 | 221 | | return collation.All( c => char.IsAsciiLetterOrDigit( c ) || c == '_' ) |
| | 1946 | 222 | | ? $" COLLATE {collation}" |
| | 1946 | 223 | | : throw new InvalidOperationException( $"Unexpected collation name '{collation}' on column {column.Co |
| | | 224 | | } |
| | | 225 | | } |