• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

loresoft / SchemaSaurus / 25392814637

05 May 2026 05:49PM UTC coverage: 87.11% (+0.01%) from 87.098%
25392814637

push

github

pwelter34
Refactor schema readers

1074 of 1424 branches covered (75.42%)

Branch coverage included in aggregate %.

79 of 117 new or added lines in 6 files covered. (67.52%)

3 existing lines in 2 files now uncovered.

5677 of 6326 relevant lines covered (89.74%)

222.98 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

81.82
/src/SchemaSaurus.SqlServer/SqlServerSchemaReader.Tables.cs
1
using Microsoft.Data.SqlClient;
2

3
using SchemaSaurus.Metadata;
4
using SchemaSaurus.Metadata.Builders;
5
using SchemaSaurus.Metadata.Extensions;
6
using SchemaSaurus.Metadata.Provider;
7

8
namespace SchemaSaurus.SqlServer;
9

10
public sealed partial class SqlServerSchemaReader
11
{
12
    /// <inheritdoc />
13
    protected override async Task ReadTablesAsync(
14
        SqlConnection connection,
15
        DatabaseModelBuilder builder,
16
        SchemaReaderOptions options,
17
        CancellationToken cancellationToken)
18
    {
19
        var tableFilter = BuildTableFilter(options);
7✔
20

21
        var tables = await ReadTableDefinitionsAsync(connection, tableFilter, cancellationToken).ConfigureAwait(false);
7✔
22
        if (tables.Count == 0)
7!
23
            return;
×
24

25
        await ReadTableColumnsAsync(connection, tables, tableFilter, cancellationToken).ConfigureAwait(false);
7✔
26
        await ReadKeyConstraintsAsync(connection, tables, cancellationToken).ConfigureAwait(false);
7✔
27
        await ReadTableIndexesAsync(connection, tables, cancellationToken).ConfigureAwait(false);
7✔
28
        await ReadCheckConstraintsAsync(connection, tables, cancellationToken).ConfigureAwait(false);
7✔
29
        await ReadTableForeignKeysAsync(connection, tables, cancellationToken).ConfigureAwait(false);
7✔
30
        await ReadTableTriggersAsync(connection, tables, cancellationToken).ConfigureAwait(false);
7✔
31

32
        // Build the tables and add them to the DatabaseModelBuilder. We do this at the end after reading all related metadata to
33
        // ensure that all properties (including extended properties) are applied to the TableBuilder before it's built.
34
        foreach (var (_, tb) in tables)
400✔
35
            builder.AddTable(tb.Build());
193✔
36
    }
7✔
37

38
    private async Task<Dictionary<int, TableBuilder>> ReadTableDefinitionsAsync(
39
        SqlConnection connection,
40
        string tableFilter,
41
        CancellationToken cancellationToken)
42
    {
43
        var tables = new Dictionary<int, TableBuilder>();
7✔
44
        var sql = $"""
7✔
45
            SELECT
7✔
46
                t.object_id,
7✔
47
                SCHEMA_NAME(t.schema_id)            AS schema_name,
7✔
48
                t.name                              AS table_name,
7✔
49
                t.temporal_type,
7✔
50
                t.is_memory_optimized,
7✔
51
                t.is_filetable,
7✔
52
                SCHEMA_NAME(ht.schema_id)           AS history_schema,
7✔
53
                ht.name                             AS history_name,
7✔
54
                CAST(ep.value AS NVARCHAR(4000))    AS description
7✔
55
            FROM sys.tables t
7✔
56
            LEFT JOIN sys.tables ht ON t.history_table_id = ht.object_id
7✔
57
            LEFT JOIN sys.extended_properties ep
7✔
58
                ON ep.major_id = t.object_id AND ep.minor_id = 0
7✔
59
                AND ep.class = 1 AND ep.name = 'MS_Description'
7✔
60
            WHERE {tableFilter}
7✔
61
              AND t.temporal_type <> 1
7✔
62
            ORDER BY SCHEMA_NAME(t.schema_id), t.name
7✔
63
            """;
7✔
64

65
        using var cmd = connection.CreateCommand();
7✔
66
        cmd.CommandText = sql;
7✔
67

68
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
69

70
        const int objectIdOrdinal = 0;
71
        const int schemaOrdinal = 1;
72
        const int nameOrdinal = 2;
73
        const int temporalOrdinal = 3;
74
        const int memOptOrdinal = 4;
75
        const int fileTableOrdinal = 5;
76
        const int histSchemaOrdinal = 6;
77
        const int histNameOrdinal = 7;
78
        const int descOrdinal = 8;
79

80
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
200✔
81
        {
82
            var objectId = reader.GetInt32(objectIdOrdinal);
193✔
83
            var schema = reader.GetString(schemaOrdinal);
193✔
84
            var name = reader.GetString(nameOrdinal);
193✔
85
            var temporalType = reader.GetByte(temporalOrdinal);
193✔
86
            var isMemOpt = reader.GetBoolean(memOptOrdinal);
193✔
87
            var isFileTable = reader.GetBoolean(fileTableOrdinal);
193✔
88
            var histSchema = reader.GetStringNull(histSchemaOrdinal);
193✔
89
            var histName = reader.GetStringNull(histNameOrdinal);
193✔
90
            var description = reader.GetStringNull(descOrdinal);
193✔
91

92
            SchemaQualifiedName? historyTableName = histSchema is not null && histName is not null
193!
93
                ? new SchemaQualifiedName { Schema = histSchema, Name = histName }
193✔
94
                : null;
193✔
95

96
            var tableOptions = new TableOptions
193✔
97
            {
193✔
98
                IsTemporalTable = temporalType == 2,
193✔
99
                HistoryTableName = historyTableName,
193✔
100
                IsMemoryOptimized = isMemOpt,
193✔
101
                IsFileTable = isFileTable,
193✔
102
            };
193✔
103

104
            var tb = new TableBuilder()
193✔
105
                .WithSchemaQualifiedName(schema, name)
193✔
106
                .WithDescription(description)
193✔
107
                .WithOptions(tableOptions);
193✔
108

109
            // apply extended properties for the table itself (class=1, major_id=object_id, minor_id=0)
110
            ApplyExtendedProperties((1, objectId, 0), tb);
193✔
111

112
            tables[objectId] = tb;
193✔
113
        }
114

115
        return tables;
7✔
116
    }
7✔
117

118
    private async Task ReadTableColumnsAsync(
119
        SqlConnection connection,
120
        Dictionary<int, TableBuilder> tables,
121
        string tableFilter,
122
        CancellationToken cancellationToken)
123
    {
124
        var sql = $"""
7✔
125
            SELECT
7✔
126
                c.object_id,
7✔
127
                c.column_id,
7✔
128
                c.name                              AS column_name,
7✔
129
                st.name                             AS system_type_name,
7✔
130
                TYPE_NAME(c.user_type_id)           AS user_type_name,
7✔
131
                c.max_length,
7✔
132
                c.precision,
7✔
133
                c.scale,
7✔
134
                c.is_nullable,
7✔
135
                c.is_identity,
7✔
136
                c.is_computed,
7✔
137
                CAST(CASE WHEN c.system_type_id = 189 THEN 1 ELSE 0 END AS BIT) AS is_rowversion,
7✔
138
                c.collation_name,
7✔
139
                CAST(ic.seed_value AS BIGINT)       AS identity_seed,
7✔
140
                CAST(ic.increment_value AS BIGINT)  AS identity_increment,
7✔
141
                cc.definition                       AS computed_sql,
7✔
142
                cc.is_persisted,
7✔
143
                dc.definition                       AS default_sql,
7✔
144
                CAST(ep.value AS NVARCHAR(4000))    AS description
7✔
145
            FROM sys.columns c
7✔
146
            INNER JOIN sys.tables t ON c.object_id = t.object_id
7✔
147
            INNER JOIN sys.types st
7✔
148
                ON c.system_type_id = st.system_type_id
7✔
149
                AND st.system_type_id = st.user_type_id
7✔
150
            LEFT JOIN sys.identity_columns ic
7✔
151
                ON c.object_id = ic.object_id AND c.column_id = ic.column_id
7✔
152
            LEFT JOIN sys.computed_columns cc
7✔
153
                ON c.object_id = cc.object_id AND c.column_id = cc.column_id
7✔
154
            LEFT JOIN sys.default_constraints dc
7✔
155
                ON c.default_object_id = dc.object_id
7✔
156
            LEFT JOIN sys.extended_properties ep
7✔
157
                ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
7✔
158
                AND ep.class = 1 AND ep.name = 'MS_Description'
7✔
159
            WHERE {tableFilter}
7✔
160
            ORDER BY c.object_id, c.column_id
7✔
161
            """;
7✔
162

163
        using var cmd = connection.CreateCommand();
7✔
164
        cmd.CommandText = sql;
7✔
165

166
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
167

168
        const int objectIdOrdinal = 0;
169
        const int columnIdOrdinal = 1;
170
        const int colNameOrdinal = 2;
171
        const int sysTypeOrdinal = 3;
172
        const int userTypeOrdinal = 4;
173
        const int maxLenOrdinal = 5;
174
        const int precisionOrdinal = 6;
175
        const int scaleOrdinal = 7;
176
        const int nullableOrdinal = 8;
177
        const int identityOrdinal = 9;
178
        const int computedOrdinal = 10;
179
        const int rowVerOrdinal = 11;
180
        const int collationOrdinal = 12;
181
        const int seedOrdinal = 13;
182
        const int incrOrdinal = 14;
183
        const int compSqlOrdinal = 15;
184
        const int persistedOrdinal = 16;
185
        const int defSqlOrdinal = 17;
186
        const int descOrdinal = 18;
187

188
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
1,571✔
189
        {
190
            var objectId = reader.GetInt32(objectIdOrdinal);
1,564✔
191
            if (!tables.TryGetValue(objectId, out var tableBuilder))
1,564✔
192
                continue;
193

194
            var columnId = reader.GetInt32(columnIdOrdinal);
1,564✔
195
            var columnName = reader.GetString(colNameOrdinal);
1,564✔
196
            var systemTypeName = reader.GetString(sysTypeOrdinal);
1,564✔
197
            var userTypeName = reader.GetStringNull(userTypeOrdinal) ?? systemTypeName;
1,564!
198
            var maxLength = reader.GetInt16(maxLenOrdinal);
1,564✔
199
            var precision = reader.GetByte(precisionOrdinal);
1,564✔
200
            var scale = reader.GetByte(scaleOrdinal);
1,564✔
201
            var isNullable = reader.GetBoolean(nullableOrdinal);
1,564✔
202
            var isIdentity = reader.GetBoolean(identityOrdinal);
1,564✔
203
            var isComputed = reader.GetBoolean(computedOrdinal);
1,564✔
204
            var isRowVersion = reader.GetBoolean(rowVerOrdinal);
1,564✔
205
            var collation = reader.GetStringNull(collationOrdinal);
1,564✔
206
            var identitySeed = reader.GetInt64Null(seedOrdinal);
1,564✔
207
            var identityIncrement = reader.GetInt64Null(incrOrdinal);
1,564✔
208
            var computedSql = reader.GetStringNull(compSqlOrdinal);
1,564✔
209
            var isPersisted = reader.GetBooleanNull(persistedOrdinal) ?? false;
1,564✔
210
            var defaultSql = reader.GetStringNull(defSqlOrdinal);
1,564✔
211
            var description = reader.GetStringNull(descOrdinal);
1,564✔
212

213
            // Map SQL Server system type to DbType and CLR type, and determine Unicode/fixed-length attributes
214
            var (dbType, sqlDbType, systemType, isUnicode, isFixedLength) = SqlServerTypeMapper.MapNativeType(systemTypeName);
1,564✔
215

216
            // Format the native type name with length/precision/scale as appropriate for the type
217
            var nativeTypeName = FormatNativeTypeName(systemTypeName, userTypeName, maxLength, precision, scale);
1,564✔
218

219
            // Normalize max length for character types (e.g. -1 for MAX) and binary types, and set to null for types where it doesn't apply
220
            var maxLengthValue = NormalizeMaxLength(systemTypeName, maxLength);
1,564✔
221

222
            // Only set precision and scale for types where they apply (e.g. decimal, numeric, time, datetime2); set to null for other types
223
            byte? precisionValue = HasPrecision(systemTypeName) ? precision : null;
1,564✔
224

225
            // Scale is applicable for decimal/numeric, and also for time/datetime2 (where it represents fractional seconds precision). For other types, set to null.
226
            var scaleValue = HasScale(systemTypeName) ? (int?)scale : null;
1,564✔
227

228
            tableBuilder.AddColumn(columnBuilder =>
1,564✔
229
            {
1,564✔
230
                columnBuilder
1,564✔
231
                    .WithName(columnName)
1,564✔
232
                    .WithOrdinalPosition(columnId)
1,564✔
233
                    .WithIsNullable(isNullable)
1,564✔
234
                    .WithDefaultValueSql(defaultSql)
1,564✔
235
                    .WithIsIdentity(isIdentity)
1,564✔
236
                    .WithIdentitySeed(identitySeed)
1,564✔
237
                    .WithIdentityIncrement(identityIncrement)
1,564✔
238
                    .WithIsComputed(isComputed)
1,564✔
239
                    .WithComputedColumnSql(computedSql)
1,564✔
240
                    .WithIsStored(isPersisted)
1,564✔
241
                    .WithIsRowVersion(isRowVersion)
1,564✔
242
                    .WithIsConcurrencyToken(isRowVersion)
1,564✔
243
                    .WithCollation(collation)
1,564✔
244
                    .WithDescription(description)
1,564✔
245
                    .WithNativeTypeName(nativeTypeName)
1,564✔
246
                    .WithDbType(dbType)
1,564✔
247
                    .WithSystemType(systemType)
1,564✔
248
                    .WithMaxLength(maxLengthValue)
1,564✔
249
                    .WithPrecision(precisionValue)
1,564✔
250
                    .WithScale(scaleValue)
1,564✔
251
                    .WithIsUnicode(isUnicode)
1,564✔
252
                    .WithIsFixedLength(isFixedLength);
1,564✔
253

1,564✔
254
                // Apply extended properties for the column (class=1, major_id=object_id, minor_id=column_id)
1,564✔
255
                ApplyExtendedProperties((1, objectId, columnId), columnBuilder);
1,564✔
256
                columnBuilder.WithAnnotation(SqlServerAnnotations.SqlDbType, sqlDbType.ToString());
1,564✔
257
            });
1,564✔
258
        }
259
    }
7✔
260

261
    private async Task ReadKeyConstraintsAsync(
262
        SqlConnection connection,
263
        Dictionary<int, TableBuilder> tables,
264
        CancellationToken cancellationToken)
265
    {
266
        // Key constraints (primary keys and unique constraints) are read together since they share underlying index metadata.
267

268
        // Dictionary to accumulate constraint info, keyed by (object_id, constraint_name). Value is (constraint_type, is_clustered, list of columns in order).
269
        var constraints = new Dictionary<(int ObjectId, string Name), (string Type, bool IsClustered, List<ColumnReference> Columns)>();
7✔
270

271
        const string sql = """
272
            SELECT
273
                kc.parent_object_id,
274
                kc.name             AS constraint_name,
275
                kc.type             AS constraint_type,
276
                i.type              AS index_type,
277
                c.name              AS column_name,
278
                ic.is_descending_key
279
            FROM sys.key_constraints kc
280
            INNER JOIN sys.indexes i
281
                ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
282
            INNER JOIN sys.index_columns ic
283
                ON i.object_id = ic.object_id AND i.index_id = ic.index_id
284
            INNER JOIN sys.columns c
285
                ON ic.object_id = c.object_id AND ic.column_id = c.column_id
286
            INNER JOIN sys.tables t ON kc.parent_object_id = t.object_id
287
            WHERE ic.is_included_column = 0
288
              AND t.is_ms_shipped = 0
289
            ORDER BY kc.parent_object_id, kc.name, ic.key_ordinal
290
            """;
291

292
        using var cmd = connection.CreateCommand();
7✔
293
        cmd.CommandText = sql;
7✔
294

295
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
296

297
        const int parentOrdinal = 0;
298
        const int nameOrdinal = 1;
299
        const int typeOrdinal = 2;
300
        const int indexTypeOrdinal = 3;
301
        const int columnNameOrdinal = 4;
302
        const int descendOrdinal = 5;
303

304
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
245✔
305
        {
306
            // Filter rows based on object_id to avoid processing constraints for tables we're not including.
307
            // This is more efficient than filtering in-memory after reading all constraints.
308
            var objectId = reader.GetInt32(parentOrdinal);
238✔
309

310
            if (!tables.ContainsKey(objectId))
238✔
311
                continue;
312

313
            var constraintName = reader.GetString(nameOrdinal);
205✔
314
            var type = reader.GetString(typeOrdinal).Trim();
205✔
315
            var indexType = reader.GetByte(indexTypeOrdinal);
205✔
316
            var columnName = reader.GetString(columnNameOrdinal);
205✔
317
            var sortDirection = reader.GetBoolean(descendOrdinal)
205!
318
                ? SortDirection.Descending
205✔
319
                : SortDirection.Ascending;
205✔
320

321
            var key = (objectId, constraintName);
205✔
322

323
            // If we haven't seen this constraint before, create a new entry in the dictionary with its type and clustering info.
324
            // Otherwise, we'll just add columns to the existing entry.
325
            if (!constraints.TryGetValue(key, out var kc))
205✔
326
            {
327
                kc = (type, indexType == 1, []);
181✔
328

329
                constraints[key] = kc;
181✔
330
            }
331

332
            ColumnReference reference = new()
205✔
333
            {
205✔
334
                ColumnName = columnName,
205✔
335
                SortDirection = sortDirection,
205✔
336
            };
205✔
337
            kc.Columns.Add(reference);
205✔
338
        }
339

340
        // Now that we've read all the constraints and their columns, we can apply them to the corresponding tables.
341
        foreach (var ((objectId, name), (type, isClustered, columns)) in constraints)
376✔
342
        {
343
            var tableBuilder = tables[objectId];
181✔
344
            var columnReferences = columns.ToArray();
181✔
345

346
            // SQL Server represents both primary keys and unique constraints in the sys.key_constraints view,
347
            // distinguished by the 'type' column ('PK' for primary key, 'UQ' for unique constraint).
348
            // We need to check the type to determine whether to call WithPrimaryKey or AddUniqueConstraint on the TableBuilder.
349

350
            if (type == "PK")
181!
351
                tableBuilder.WithPrimaryKey(name, isClustered, columnReferences);
181✔
352
            else
NEW
353
                tableBuilder.AddUniqueConstraint(name, columnReferences);
×
354
        }
355
    }
7✔
356

357
    private async Task ReadTableIndexesAsync(
358
        SqlConnection connection,
359
        Dictionary<int, TableBuilder> tables,
360
        CancellationToken cancellationToken)
361
    {
362
        // Dictionary to accumulate index info, keyed by (object_id, index_id).
363
        var indexes = new Dictionary<(int ObjectId, int IndexId), IndexBuilder>();
7✔
364

365
        const string sql = """
366
            SELECT
367
                i.object_id,
368
                i.index_id,
369
                i.name              AS index_name,
370
                i.is_unique,
371
                i.type              AS index_type,
372
                i.is_disabled,
373
                i.has_filter,
374
                i.filter_definition,
375
                i.fill_factor,
376
                c.name              AS column_name,
377
                ic.is_descending_key,
378
                ic.is_included_column
379
            FROM sys.indexes i
380
            INNER JOIN sys.index_columns ic
381
                ON i.object_id = ic.object_id AND i.index_id = ic.index_id
382
            INNER JOIN sys.columns c
383
                ON ic.object_id = c.object_id AND ic.column_id = c.column_id
384
            INNER JOIN sys.tables t ON i.object_id = t.object_id
385
            WHERE t.is_ms_shipped = 0
386
              AND i.type > 0
387
              AND i.is_primary_key = 0
388
              AND i.is_unique_constraint = 0
389
              AND i.name IS NOT NULL
390
            ORDER BY i.object_id, i.index_id, ic.key_ordinal, ic.index_column_id
391
            """;
392

393
        using var cmd = connection.CreateCommand();
7✔
394
        cmd.CommandText = sql;
7✔
395

396
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
397

398
        const int objectIdOrdinal = 0;
399
        const int indexIdOrdinal = 1;
400
        const int nameOrdinal = 2;
401
        const int uniqueOrdinal = 3;
402
        const int typeOrdinal = 4;
403
        const int disabledOrdinal = 5;
404
        const int filterOrdinal = 6;
405
        const int filterDefOrdinal = 7;
406
        const int fillFactorOrdinal = 8;
407
        const int colNameOrdinal = 9;
408
        const int descendOrdinal = 10;
409
        const int includedOrdinal = 11;
410

411
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
98✔
412
        {
413
            var objectId = reader.GetInt32(objectIdOrdinal);
91✔
414

415
            // Filter rows based on object_id to avoid processing indexes for tables we're not including.
416
            if (!tables.ContainsKey(objectId))
91✔
417
                continue;
418

419
            var indexId = reader.GetInt32(indexIdOrdinal);
78✔
420
            var indexName = reader.GetString(nameOrdinal);
78✔
421
            var isUnique = reader.GetBoolean(uniqueOrdinal);
78✔
422
            var indexType = reader.GetByte(typeOrdinal);
78✔
423
            var isDisabled = reader.GetBoolean(disabledOrdinal);
78✔
424
            var hasFilter = reader.GetBoolean(filterOrdinal);
78✔
425
            var filterExpression = reader.GetStringNull(filterDefOrdinal);
78✔
426
            var fillFactor = reader.GetByte(fillFactorOrdinal);
78✔
427
            var columnName = reader.GetString(colNameOrdinal);
78✔
428
            var isDescending = reader.GetBoolean(descendOrdinal);
78✔
429
            var isIncluded = reader.GetBoolean(includedOrdinal);
78✔
430

431
            var key = (objectId, indexId);
78✔
432

433
            // If we haven't seen this index before, create a new IndexBuilder and add it to the dictionary.
434
            // Otherwise, we'll just add columns to the existing builder.
435
            if (!indexes.TryGetValue(key, out var indexBuilder))
78✔
436
            {
437
                indexBuilder = new IndexBuilder()
60!
438
                    .WithName(indexName)
60✔
439
                    .WithIsUnique(isUnique)
60✔
440
                    .WithIsClustered(indexType == 1)
60✔
441
                    .WithFillFactor(fillFactor == 0 ? null : fillFactor)
60✔
442
                    .WithIsDisabled(isDisabled);
60✔
443

444
                // Apply extended properties for the index (class=7, major_id=object_id, minor_id=index_id)
445
                ApplyExtendedProperties((7, objectId, indexId), indexBuilder);
60✔
446

447
                // If the index has a filter, set the IsFiltered property and the FilterExpression if it's not null.
448
                if (hasFilter)
60!
449
                {
450
                    indexBuilder.WithIsFiltered(true);
×
451
                    if (filterExpression is not null)
×
452
                        indexBuilder.WithFilterExpression(filterExpression);
×
453
                }
454

455
                // SQL Server supports different index types: 1 = clustered, 2 = nonclustered, 3 = XML, 4 = spatial, 5 = columnstore, 6 = columnstore_clustered, 7 = hash.
456
                // We can map these to the IndexType property on the IndexBuilder.
457
                if (indexType is 5 or 6)
60!
458
                    indexBuilder.WithIndexType("COLUMNSTORE");
×
459
                else if (indexType == 7)
60!
460
                    indexBuilder.WithIndexType("HASH");
×
461

462
                indexes[key] = indexBuilder;
60✔
463
            }
464

465
            // Included columns are part of the index but not key columns, so they don't have sort direction.
466
            // We need to call AddIncludedColumn instead of AddColumn for these.
467
            if (isIncluded)
78!
468
            {
NEW
469
                indexBuilder.AddIncludedColumn(columnName);
×
470
            }
471
            else
472
            {
473
                var sortDirection = isDescending ? SortDirection.Descending : SortDirection.Ascending;
78!
474
                indexBuilder.AddColumn(columnName, sortDirection);
78✔
475
            }
476
        }
477

478
        // Now that we've read all the indexes and their columns, we can apply them to the corresponding tables.
479
        foreach (var ((objectId, _), indexBuilder) in indexes)
134✔
480
            tables[objectId].AddIndex(indexBuilder.Build());
60✔
481
    }
7✔
482

483
    private static async Task ReadCheckConstraintsAsync(
484
        SqlConnection connection,
485
        Dictionary<int, TableBuilder> tables,
486
        CancellationToken cancellationToken)
487
    {
488
        const string sql = """
489
            SELECT
490
                cc.parent_object_id,
491
                cc.name         AS constraint_name,
492
                cc.definition
493
            FROM sys.check_constraints cc
494
            INNER JOIN sys.tables t ON cc.parent_object_id = t.object_id
495
            WHERE t.is_ms_shipped = 0
496
            ORDER BY cc.parent_object_id, cc.name
497
            """;
498

499
        using var cmd = connection.CreateCommand();
7✔
500
        cmd.CommandText = sql;
7✔
501

502
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
503

504
        const int parentOrdinal = 0;
505
        const int nameOrdinal = 1;
506
        const int defOrdinal = 2;
507

508
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
7!
509
        {
510
            var objectId = reader.GetInt32(parentOrdinal);
×
511

512
            // Filter rows based on object_id to avoid processing constraints for tables we're not including.
NEW
513
            if (!tables.TryGetValue(objectId, out var tableBuilder))
×
514
                continue;
515

516
            var name = reader.GetString(nameOrdinal);
×
517
            var definition = reader.GetString(defOrdinal);
×
518

NEW
519
            tableBuilder.AddCheckConstraint(name, definition);
×
520
        }
521
    }
7✔
522

523
    private static async Task ReadTableForeignKeysAsync(
524
        SqlConnection connection,
525
        Dictionary<int, TableBuilder> tables,
526
        CancellationToken cancellationToken)
527
    {
528
        // Dictionary to accumulate foreign key info, keyed by (parent_object_id, fk_name).
529
        var foreignKeys = new Dictionary<(int ObjectId, string Name), ForeignKeyBuilder>();
7✔
530

531
        const string sql = """
532
            SELECT
533
                fk.parent_object_id,
534
                fk.name                         AS fk_name,
535
                SCHEMA_NAME(rt.schema_id)       AS principal_schema,
536
                rt.name                         AS principal_table,
537
                fk.delete_referential_action,
538
                fk.update_referential_action,
539
                fk.is_disabled,
540
                pc.name                         AS parent_column,
541
                rc.name                         AS referenced_column
542
            FROM sys.foreign_keys fk
543
            INNER JOIN sys.foreign_key_columns fkc
544
                ON fk.object_id = fkc.constraint_object_id
545
            INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
546
            INNER JOIN sys.tables rt ON fk.referenced_object_id = rt.object_id
547
            INNER JOIN sys.columns pc
548
                ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id
549
            INNER JOIN sys.columns rc
550
                ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
551
            WHERE t.is_ms_shipped = 0
552
            ORDER BY fk.parent_object_id, fk.name, fkc.constraint_column_id
553
            """;
554

555
        using var cmd = connection.CreateCommand();
7✔
556
        cmd.CommandText = sql;
7✔
557

558
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
559

560
        const int parentOrdinal = 0;
561
        const int nameOrdinal = 1;
562
        const int pSchemaOrdinal = 2;
563
        const int pTableOrdinal = 3;
564
        const int deleteOrdinal = 4;
565
        const int updateOrdinal = 5;
566
        const int disabledOrdinal = 6;
567
        const int parentColOrdinal = 7;
568
        const int refColOrdinal = 8;
569

570
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
147✔
571
        {
572
            var objectId = reader.GetInt32(parentOrdinal);
140✔
573

574
            // Filter rows based on parent_object_id to avoid processing foreign keys for tables we're not including.
575
            if (!tables.ContainsKey(objectId))
140✔
576
                continue;
577

578
            var fkName = reader.GetString(nameOrdinal);
120✔
579
            var principalSchema = reader.GetString(pSchemaOrdinal);
120✔
580
            var principalTable = reader.GetString(pTableOrdinal);
120✔
581
            var onDelete = MapReferentialAction(reader.GetByte(deleteOrdinal));
120✔
582
            var onUpdate = MapReferentialAction(reader.GetByte(updateOrdinal));
120✔
583
            var isDisabled = reader.GetBoolean(disabledOrdinal);
120✔
584
            var parentColumn = reader.GetString(parentColOrdinal);
120✔
585
            var referencedColumn = reader.GetString(refColOrdinal);
120✔
586

587
            var key = (objectId, fkName);
120✔
588

589
            // If we haven't seen this foreign key before, create a new ForeignKeyBuilder and add it to the dictionary.
590
            if (!foreignKeys.TryGetValue(key, out var foreignKeyBuilder))
120✔
591
            {
592
                foreignKeyBuilder = new ForeignKeyBuilder()
114✔
593
                    .WithName(fkName)
114✔
594
                    .WithPrincipalTableName(principalSchema, principalTable)
114✔
595
                    .WithOnDelete(onDelete)
114✔
596
                    .WithOnUpdate(onUpdate)
114✔
597
                    .WithIsDisabled(isDisabled);
114✔
598

599
                foreignKeys[key] = foreignKeyBuilder;
114✔
600
            }
601

602
            // Add the column mapping to the ForeignKeyBuilder. Since the query is ordered by constraint_column_id, the columns will be added in the correct order.
603
            foreignKeyBuilder.AddColumnMapping(parentColumn, referencedColumn);
120✔
604
        }
605

606
        // Now that we've read all the foreign keys and their column mappings, we can apply them to the corresponding tables.
607
        foreach (var ((objectId, _), foreignKeyBuilder) in foreignKeys)
242✔
608
            tables[objectId].AddForeignKey(foreignKeyBuilder.Build());
114✔
609
    }
7✔
610

611
    private static async Task ReadTableTriggersAsync(
612
        SqlConnection connection,
613
        Dictionary<int, TableBuilder> tables,
614
        CancellationToken cancellationToken)
615
    {
616
        // Dictionary to accumulate trigger info, keyed by (parent_object_id, trigger_name).
617
        var triggerData = new Dictionary<(int ObjectId, string Name), (bool IsDisabled, bool IsInsteadOf, string? Definition, TriggerEvent Events)>();
7✔
618

619
        const string sql = """
620
            SELECT
621
                tr.parent_id,
622
                tr.name                     AS trigger_name,
623
                tr.is_disabled,
624
                tr.is_instead_of_trigger,
625
                m.definition,
626
                te.type_desc                AS event_type
627
            FROM sys.triggers tr
628
            INNER JOIN sys.trigger_events te ON tr.object_id = te.object_id
629
            LEFT JOIN sys.sql_modules m ON tr.object_id = m.object_id
630
            WHERE tr.parent_class = 1
631
            ORDER BY tr.parent_id, tr.name
632
            """;
633

634
        using var cmd = connection.CreateCommand();
7✔
635
        cmd.CommandText = sql;
7✔
636

637
        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);
7✔
638

639
        const int parentOrdinal = 0;
640
        const int nameOrdinal = 1;
641
        const int disabledOrdinal = 2;
642
        const int insteadOrdinal = 3;
643
        const int definitionOrdinal = 4;
644
        const int eventOrdinal = 5;
645

646
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
7!
647
        {
648
            var parentId = reader.GetInt32(parentOrdinal);
×
649

NEW
650
            if (!tables.ContainsKey(parentId))
×
651
                continue;
652

653
            var name = reader.GetString(nameOrdinal);
×
654
            var isDisabled = reader.GetBoolean(disabledOrdinal);
×
655
            var isInsteadOf = reader.GetBoolean(insteadOrdinal);
×
656
            var definition = reader.GetStringNull(definitionOrdinal);
×
657
            var eventType = reader.GetString(eventOrdinal);
×
658

NEW
659
            var triggerEvent = MapTriggerEvent(eventType);
×
660

UNCOV
661
            var key = (parentId, name);
×
662

663
            // If we haven't seen this trigger before, create a new entry in the dictionary with its disabled/instead_of/definition info.
664
            if (!triggerData.TryGetValue(key, out var td))
×
665
            {
NEW
666
                triggerData[key] = (isDisabled, isInsteadOf, definition, triggerEvent);
×
667
            }
668
            else
669
            {
NEW
670
                td.Events |= triggerEvent;
×
UNCOV
671
                triggerData[key] = td;
×
672
            }
673
        }
674

675
        // Now that we've read all the triggers and their events, we can apply them to the corresponding tables.
676
        foreach (var ((parentId, name), (isDisabled, isInsteadOf, definition, triggerEvents)) in triggerData)
14!
677
        {
678
            // Determine the trigger timing based on the is_instead_of_trigger column.
679
            // If it's an INSTEAD OF trigger, the timing is InsteadOf; otherwise, it's After (SQL Server doesn't have BEFORE triggers).
680
            var timing = isInsteadOf ? TriggerTiming.InsteadOf : TriggerTiming.After;
×
681
            var trigger = new Trigger
×
682
            {
×
683
                Name = name,
×
684
                Timing = timing,
×
685
                Events = triggerEvents,
×
686
                Definition = definition,
×
687
                IsDisabled = isDisabled,
×
688
            };
×
689

690
            tables[parentId].AddTrigger(trigger);
×
691
        }
692
    }
7✔
693

NEW
694
    private static TriggerEvent MapTriggerEvent(string eventType) => eventType switch
×
NEW
695
    {
×
NEW
696
        "INSERT" => TriggerEvent.Insert,
×
NEW
697
        "UPDATE" => TriggerEvent.Update,
×
NEW
698
        "DELETE" => TriggerEvent.Delete,
×
NEW
699
        _ => TriggerEvent.None,
×
NEW
700
    };
×
701
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc