Søren Eggert Lundsteen Olsen
8db226d2b6
Cascorp's BATCH table: every STATUS 4 row has an end time and no exception code (finished), STATUS 1 rows have no end time and starts long passed (waiting). So AX 3.0 uses the same order as AX 4.0+: 0 Tilbageholdt, 1 Venter, 2 Udfører, 3 Fejl, 4 Afsluttet. The old assumption (0 Venter ... 4 Tilbageholdt) treated finished jobs as held and waiting ones as running. SQLI-AX-003 skips waiting jobs planned more than BatchAbandonedDays (30) ago: the customer has 25 such rows from 2007-2018 that no batch server serves; they are dead rows, not a live alarm. Adds docs/dynamics-ax/verificer-ax30-batchstatus.sql (read-only) to repeat the check at other AX 3.0 customers. Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
384 lines
25 KiB
C#
384 lines
25 KiB
C#
using System.Diagnostics;
|
|
using System.Text.RegularExpressions;
|
|
using Microsoft.Data.SqlClient;
|
|
using SQLInsight.Collectors.Discovery;
|
|
using SQLInsight.Collectors.DynamicsAx;
|
|
using SQLInsight.Core.Collection;
|
|
using SQLInsight.Core.Configuration;
|
|
using SQLInsight.Core.Data;
|
|
using SQLInsight.Core.DynamicsAx;
|
|
using SQLInsight.Repository;
|
|
using SQLInsight.Rules;
|
|
using SQLInsight.Rules.Engine;
|
|
using SQLInsight.Web;
|
|
|
|
namespace SQLInsight.IntegrationTests;
|
|
|
|
/// <summary>
|
|
/// Fake Dynamics AX databases in the SQL Server 2022 container, built from the schema files: an Axapta 3.0 database
|
|
/// exactly as the customer's (tables owned by bmssa, 32-bit RECID, local date + seconds) and an AX 2012 R2/R3 database
|
|
/// (dbo, PARTITION, model store in <db>_model) from Microsoft's documented schema. Everything runs as the
|
|
/// least-privilege agent login; the optional AX read access is given with the exact GRANT SQLInsight shows.
|
|
/// </summary>
|
|
[Collection(SqlServerTestGroup.Name)]
|
|
[Trait("Category", "Integration")]
|
|
public class DynamicsAxTests(SqlServerFixture sql) : IAsyncLifetime
|
|
{
|
|
private readonly string _ax30 = "AX30_" + Guid.NewGuid().ToString("N")[..6];
|
|
private readonly string _ax2012 = "AX2012_" + Guid.NewGuid().ToString("N")[..6];
|
|
private string Model => _ax2012 + "_model";
|
|
|
|
internal static AxSchemaFile Schema(string name) => AxSchemaFile.Find(Path.Combine(AppContext.BaseDirectory, "DynamicsAx"), name)!;
|
|
|
|
public async Task InitializeAsync()
|
|
{
|
|
await sql.ExecuteAsSaAsync($"CREATE DATABASE [{_ax30}]; CREATE DATABASE [{_ax2012}]; CREATE DATABASE [{Model}];");
|
|
await CreateAsync(_ax30, "bmssa", Schema("schema-ax30"));
|
|
await CreateAsync(_ax2012, "dbo", Schema("schema-ax2012"));
|
|
|
|
// Axapta 3.0 data as the AX kernel stores it: local date (time 00:00) + seconds since midnight in STARTTIME/ENDTIME.
|
|
await sql.ExecuteAsSaAsync($"""
|
|
USE [{_ax30}];
|
|
CREATE TABLE bmssa.CUSTTABLE (ACCOUNTNUM varchar(20) NULL, NAME varchar(60) NULL, DATAAREAID varchar(3) NULL, RECID int NULL);
|
|
INSERT bmssa.CUSTTABLE VALUES ('10000', 'Fortrolig kunde A/S', 'dat', 1);
|
|
INSERT bmssa.DATAAREA (ID, NAME, ISVIRTUAL, ALWAYSNATIVE, RECVERSION, RECID) VALUES ('dat', 'Demo A/S', 0, 0, 1, 1), ('dmo', 'Demo 2', 0, 0, 1, 2);
|
|
INSERT bmssa.SQLDICTIONARY (TABLEID, FIELDID, NAME, SQLNAME, RECID) VALUES (77, 0, 'CustTable', 'CUSTTABLE', 1);
|
|
INSERT bmssa.SYSTEMSEQUENCES (ID, NEXTVAL, MINVAL, MAXVAL, CYCLE, NAME, TABID, DATAAREAID, RECVERSION, RECID)
|
|
VALUES (-1, 1700000000, 1, 2147483647, 0, 'SEQNO', 77, 'dat', 1, 1), (-1, 1000, 1, 2147483647, 0, 'SEQNO', 78, 'dat', 1, 2);
|
|
DECLARE @late datetime, @running datetime, @failed datetime;
|
|
SET @late = DATEADD(hour, -2, GETDATE()); SET @running = DATEADD(minute, -30, GETDATE()); SET @failed = DATEADD(hour, -1, GETDATE());
|
|
INSERT bmssa.BATCH (RECID, CLASSNUM, COMPANY, GROUPID, STATUS, STARTDATE, STARTTIME, ENDDATE, ENDTIME)
|
|
VALUES (1, 101, 'dat', 'NAT', 1, CONVERT(date, @late), DATEDIFF(second, CONVERT(date, @late), @late), '19000101', 0),
|
|
(2, 102, 'dat', 'NAT', 3, CONVERT(date, @failed), DATEDIFF(second, CONVERT(date, @failed), @failed), CONVERT(date, @failed), DATEDIFF(second, CONVERT(date, @failed), @failed) + 60),
|
|
(3, 103, 'dat', '', 2, CONVERT(date, @running), DATEDIFF(second, CONVERT(date, @running), @running), '19000101', 0),
|
|
(4, 104, 'dmo', '', 4, CONVERT(date, @failed), 0, CONVERT(date, @failed), 600);
|
|
INSERT bmssa.NUMBERSEQUENCETABLE (RECID, NUMBERSEQUENCE, TXT, LOWEST, HIGHEST, NEXTREC, FORMAT, BLOCKED, CONTINUOUS, CYCLIC, MANUAL, INUSE,
|
|
LATESTCLEANDATE, LATESTCLEANTIME, CLEANINTERVAL, DATAAREAID)
|
|
VALUES (10, 'Debitor', 'Debitorer', 1, 99999, 97000, 'DEB-#####', 0, 0, 0, 0, 1, '19000101', 0, 0, 'dat'),
|
|
(11, 'Faktura', 'Fakturaer', 1, 99999999, 500, 'FA-####', 0, 0, 0, 0, 1, '19000101', 0, 0, 'dat'),
|
|
(12, 'Gammel', 'Gammel serie', 1, 99999, 50, '#####', 1, 0, 0, 0, 1, '19000101', 0, 0, 'dat'),
|
|
(13, 'Kont', 'Kontinuerlig', 1, 99999, 100, '#####', 0, 1, 0, 0, 1, '19000101', 0, 24, 'dat');
|
|
INSERT bmssa.NUMBERSEQUENCEREFERENCE (NUMBERSEQUENCE, DATATYPEID, DATAAREAID, RECID) VALUES ('Gammel', 1, 'dat', 1);
|
|
""");
|
|
|
|
// AX 2012 R2/R3 data: UTC datetimes, 64-bit RECID, sequences scoped per company, history in BATCHJOBHISTORY.
|
|
await sql.ExecuteAsSaAsync($"""
|
|
USE [{_ax2012}];
|
|
INSERT dbo.DATAAREA (ID, NAME, ISVIRTUAL, ALWAYSNATIVE, RECVERSION, PARTITION, RECID) VALUES ('dk01', 'Danmark', 0, 0, 1, 5637144576, 1);
|
|
INSERT dbo.SQLDICTIONARY (TABLEID, FIELDID, NAME, SQLNAME, RECID) VALUES (77, 0, 'CustTable', 'CUSTTABLE', 1);
|
|
INSERT dbo.SYSTEMSEQUENCES (ID, NEXTVAL, MINVAL, MAXVAL, CYCLE, NAME, TABID, DATAAREAID, RECVERSION, RECID)
|
|
VALUES (-1, 5637150000, 1, 9223372036854775807, 0, 'SEQNO', 77, '', 1, 1);
|
|
INSERT dbo.BATCHJOB (RECID, CAPTION, COMPANY, STATUS, ORIGSTARTDATETIME, STARTDATETIME, ENDDATETIME, PARTITION)
|
|
VALUES (5637144576, 'Lagerafslutning', 'dk01', 1, DATEADD(hour, -2, GETUTCDATE()), '19000101', '19000101', 5637144576),
|
|
(5637144577, 'Webshop-integration', 'dk01', 3, DATEADD(hour, -3, GETUTCDATE()), DATEADD(hour, -3, GETUTCDATE()), DATEADD(minute, -175, GETUTCDATE()), 5637144576);
|
|
INSERT dbo.BATCHJOBHISTORY (RECID, BATCHJOBID, CAPTION, COMPANY, STATUS, STARTDATETIME, ENDDATETIME, PARTITION)
|
|
SELECT 5637145000 + n, 5637144577, 'Webshop-integration', 'dk01', 4, DATEADD(day, -n, GETUTCDATE()), DATEADD(minute, 10, DATEADD(day, -n, GETUTCDATE())), 5637144576
|
|
FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) d;
|
|
INSERT dbo.BATCHJOBHISTORY (RECID, BATCHJOBID, CAPTION, COMPANY, STATUS, STARTDATETIME, ENDDATETIME, PARTITION)
|
|
VALUES (5637145100, 5637144577, 'Webshop-integration', 'dk01', 3, DATEADD(minute, -180, GETUTCDATE()), DATEADD(minute, -175, GETUTCDATE()), 5637144576);
|
|
INSERT dbo.NUMBERSEQUENCESCOPE (DATAAREA, RECVERSION, PARTITION, RECID) VALUES ('dk01', 1, 5637144576, 1001);
|
|
INSERT dbo.NUMBERSEQUENCETABLE (RECID, NUMBERSEQUENCE, TXT, LOWEST, HIGHEST, NEXTREC, FORMAT, BLOCKED, CONTINUOUS, CYCLIC, MANUAL, NUMBERSEQUENCESCOPE,
|
|
LATESTCLEANDATETIME, CLEANINTERVAL, PARTITION)
|
|
VALUES (2001, 'Ordre', 'Salgsordrer', 1, 1000000, 990000, '######', 0, 0, 0, 0, 1001, '19000101', 0, 5637144576);
|
|
INSERT dbo.NUMBERSEQUENCEREFERENCE (NUMBERSEQUENCEID, NUMBERSEQUENCESCOPE, PARTITION, RECID) VALUES (2001, 1001, 5637144576, 1);
|
|
""");
|
|
}
|
|
|
|
public Task DisposeAsync() => sql.ExecuteAsSaAsync(string.Join("\n", new[] { _ax30, _ax2012, Model }.Select(d =>
|
|
$"ALTER DATABASE [{d}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{d}];")));
|
|
|
|
/// <summary>The schema's tables, in the schema the customer has them in.</summary>
|
|
internal async Task CreateAsync(string database, string schema, AxSchemaFile file)
|
|
{
|
|
var create = string.Join("\n", file.CreateTables());
|
|
await sql.ExecuteAsSaAsync($"USE [{database}]; {(schema == "dbo" ? string.Empty : $"EXEC (N'CREATE SCHEMA [{schema}]');")} EXEC (N'{create.Replace("'", "''", StringComparison.Ordinal)}');");
|
|
}
|
|
|
|
private DatabaseSelection Selection => new(monitorNew: false, [_ax30, _ax2012, Model], [], []);
|
|
|
|
private async Task<AxDatabaseSnapshot> DetectAsync(AgentSession session) =>
|
|
await session.CollectAsync<AxDatabaseSnapshot>(new DynamicsAxCollector(new AxDatabaseDetector()), selection: Selection);
|
|
|
|
private Task GrantAsync(AxDatabase database) => sql.ExecuteAsSaAsync(AxReadAccess.GrantSql(database, SqlServerFixture.AgentLogin));
|
|
|
|
/// <summary>A checksum of every AX table: SQLInsight must never change customer data.</summary>
|
|
private async Task<string> ChecksumAsync(string database)
|
|
{
|
|
await using var connection = new SqlConnection(sql.DatabaseConnectionString(database));
|
|
await connection.OpenAsync();
|
|
await using var command = connection.CreateCommand();
|
|
command.CommandText = """
|
|
DECLARE @sql nvarchar(max); SET @sql = N'';
|
|
SELECT @sql = @sql + N'SELECT N''' + s.name + N'.' + t.name + N''' AS t, COUNT(*) AS n, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS c FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' UNION ALL '
|
|
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id;
|
|
SET @sql = LEFT(@sql, LEN(@sql) - 10) + N' ORDER BY t';
|
|
EXEC (@sql);
|
|
""";
|
|
await using var reader = await command.ExecuteReaderAsync();
|
|
var parts = new List<string>();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
parts.Add($"{reader.GetString(0)}:{reader.GetInt32(1)}:{(reader.IsDBNull(2) ? 0 : reader.GetInt32(2))}");
|
|
}
|
|
|
|
return string.Join("|", parts);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Ax30_and_ax2012_are_recognised_by_the_central_detection_without_any_ax_read_access()
|
|
{
|
|
await using var session = await AgentSession.OpenAsync(sql);
|
|
|
|
var snapshot = await DetectAsync(session);
|
|
var discovered = (await DatabaseDiscovery.DiscoverAsync(session.Connection, session.Instance, CancellationToken.None)).ToDictionary(d => d.Name);
|
|
|
|
var ax30 = snapshot.Find(_ax30)!;
|
|
var ax2012 = snapshot.Find(_ax2012)!;
|
|
Assert.Equal((AxVersion.Axapta30, "bmssa", true), (ax30.Version, ax30.Schema, ax30.Verified));
|
|
Assert.Equal((AxVersion.Ax2012R2, "dbo", Model, false), (ax2012.Version, ax2012.Schema, ax2012.ModelDatabase, ax2012.Verified));
|
|
Assert.Null(snapshot.Find(Model));
|
|
Assert.Equal(SqlServerFixture.AgentLogin, snapshot.AgentLogin);
|
|
Assert.DoesNotContain(AxReadAccess.Tables(ax30), ax30.CanRead);
|
|
Assert.Null(ax30.Companies);
|
|
Assert.Equal(("3.0", "2012 R2/R3"), (discovered[_ax30].DynamicsAxVersion, discovered[_ax2012].DynamicsAxVersion));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Without_the_grant_the_checks_say_exactly_which_grant_and_with_it_ax30_is_measured_without_changing_anything()
|
|
{
|
|
var before = await ChecksumAsync(_ax30);
|
|
await using var session = await AgentSession.OpenAsync(sql);
|
|
var detector = new AxDatabaseDetector();
|
|
var batch = await session.CollectAsync<AxBatchSnapshot>(new AxBatchCollector(detector), selection: Selection);
|
|
var reason = batch.Databases.Single(d => d.Database == _ax30).NotMeasurable!;
|
|
|
|
Assert.Contains($"GRANT SELECT ON [bmssa].[BATCH] TO [{SqlServerFixture.AgentLogin}];", reason, StringComparison.Ordinal);
|
|
var grant = Regex.Match(reason, @"Kør som sysadmin eller db_owner: (?<sql>.*)$").Groups["sql"].Value;
|
|
await sql.ExecuteAsSaAsync(grant);
|
|
|
|
// The grant above covered BATCH only; the rest of the version's tables with the full GRANT text.
|
|
var ax = (await DetectAsync(session)).Find(_ax30)!;
|
|
await GrantAsync(ax);
|
|
detector = new AxDatabaseDetector();
|
|
var batchSnapshot = await session.CollectAsync<AxBatchSnapshot>(new AxBatchCollector(detector), selection: Selection);
|
|
var jobs = batchSnapshot.Databases.Single(d => d.Database == _ax30);
|
|
var sequences = await session.CollectAsync<AxSequenceSnapshot>(new AxSequenceCollector(detector), selection: Selection);
|
|
var axSnapshot = await session.CollectAsync<AxDatabaseSnapshot>(new DynamicsAxCollector(detector), selection: Selection);
|
|
|
|
Assert.Null(jobs.NotMeasurable);
|
|
var late = jobs.Jobs.Single(j => j.RecId == 1);
|
|
Assert.Equal(AxBatchStatus.Waiting, late.Status);
|
|
Assert.InRange(late.Late(DateTimeOffset.UtcNow)!.Value.TotalMinutes, 115, 125);
|
|
Assert.Equal(AxBatchStatus.Executing, jobs.Jobs.Single(j => j.RecId == 3).Status);
|
|
Assert.Equal(4, jobs.StatusCounts.Values.Sum());
|
|
var db = sequences.Databases.Single(d => d.Database == _ax30);
|
|
Assert.Null(db.NumberSequencesNotMeasurable);
|
|
Assert.Equal(97, db.NumberSequences.Single(s => s.Code == "Debitor").UsedPercent, 0);
|
|
Assert.True(db.NumberSequences.Single(s => s.Code == "Faktura").FormatTooShort);
|
|
Assert.True(db.NumberSequences.Single(s => s.Code == "Gammel").Referenced);
|
|
var recId = db.RecIds.Single(r => r.TableId == 77);
|
|
Assert.Equal((32, "CustTable"), (recId.Bits, recId.TableName));
|
|
Assert.Equal(["dat", "dmo"], axSnapshot.Find(_ax30)!.Companies!.Select(c => c.Id));
|
|
|
|
var data = new CollectedData();
|
|
data.Set(batchSnapshot);
|
|
data.Set(sequences);
|
|
data.Set(axSnapshot);
|
|
data.Set(new BlockingSnapshot { CapturedAt = DateTimeOffset.UtcNow, Sessions = [] });
|
|
var results = new RuleEngine(BuiltInRules.Create().Where(r => r.Id.StartsWith("SQLI-AX-", StringComparison.Ordinal)))
|
|
.Evaluate(new RuleContext(session.Instance, session.Capabilities, DateTimeOffset.UtcNow, data))
|
|
.Where(r => r.Findings.Count > 0).ToDictionary(r => r.RuleId);
|
|
|
|
Assert.Equal(["SQLI-AX-001", "SQLI-AX-003", "SQLI-AX-005", "SQLI-AX-006", "SQLI-AX-007", "SQLI-AX-008", "SQLI-AX-009"], results.Keys.Order());
|
|
Assert.All(results.Values.SelectMany(r => r.Findings), f => Assert.Equal(_ax30, DynamicsAxFindings.DatabaseOf(f.Subject)));
|
|
Assert.Equal(Core.Findings.Severity.Critical, results["SQLI-AX-009"].Findings.Single().Severity);
|
|
|
|
// Read access is SELECT on the AX system tables only: never the customer's data, and nothing was changed.
|
|
Assert.Equal(0, await AsAgentAsync(_ax30, "SELECT HAS_PERMS_BY_NAME(N'bmssa.CUSTTABLE', N'OBJECT', N'SELECT')"));
|
|
Assert.Equal(0, await AsAgentAsync(_ax30, "SELECT HAS_PERMS_BY_NAME(N'bmssa.NUMBERSEQUENCETABLE', N'OBJECT', N'UPDATE')"));
|
|
Assert.Equal(before, await ChecksumAsync(_ax30));
|
|
}
|
|
|
|
private async Task<int> AsAgentAsync(string database, string query)
|
|
{
|
|
await using var connection = new SqlConnection(new SqlConnectionStringBuilder(sql.AgentConnectionString) { InitialCatalog = database }.ConnectionString);
|
|
await connection.OpenAsync();
|
|
await using var command = connection.CreateCommand();
|
|
command.CommandText = query;
|
|
return Convert.ToInt32(await command.ExecuteScalarAsync(), System.Globalization.CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task The_shipped_grant_script_finds_version_and_schema_and_the_revoke_script_takes_it_back()
|
|
{
|
|
await sql.ExecuteAsSaAsync(AxReadAccess.Script(_ax30, SqlServerFixture.AgentLogin));
|
|
await sql.ExecuteAsSaAsync(AxReadAccess.Script(_ax2012, SqlServerFixture.AgentLogin));
|
|
await using var session = await AgentSession.OpenAsync(sql);
|
|
var granted = await DetectAsync(session);
|
|
|
|
Assert.Empty(AxReadAccess.Missing(granted.Find(_ax30)!));
|
|
Assert.Empty(AxReadAccess.Missing(granted.Find(_ax2012)!));
|
|
Assert.Equal(["BATCH", "DATAAREA", "NUMBERSEQUENCEREFERENCE", "NUMBERSEQUENCETABLE", "SQLDICTIONARY", "SYSTEMSEQUENCES"],
|
|
granted.Find(_ax30)!.Tables.Where(t => t.Readable).Select(t => t.Name).Order());
|
|
Assert.False(granted.Find(_ax2012)!.CanRead("BATCH"));
|
|
Assert.Equal(0, await AsAgentAsync(_ax30, "SELECT HAS_PERMS_BY_NAME(N'bmssa.CUSTTABLE', N'OBJECT', N'SELECT')"));
|
|
|
|
var revoke = await File.ReadAllTextAsync(Path.Combine(AppContext.BaseDirectory, "Scripts", "revoke-dynamics-ax-read.sql"));
|
|
await sql.ExecuteAsSaAsync(revoke.Replace("N'AXDB'", $"N'{_ax30}'", StringComparison.Ordinal)
|
|
.Replace(@"N'DOMÆNE\svc-sqlinsight$'", $"N'{SqlServerFixture.AgentLogin}'", StringComparison.Ordinal));
|
|
var revoked = await session.CollectAsync<AxDatabaseSnapshot>(new DynamicsAxCollector(new AxDatabaseDetector()), selection: Selection);
|
|
Assert.Equal(AxReadAccess.Tables(revoked.Find(_ax30)!).Count, AxReadAccess.Missing(revoked.Find(_ax30)!).Count);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Ax2012_reads_history_by_recid_once_companies_from_the_scope_and_a_64_bit_recid()
|
|
{
|
|
await sql.ExecuteAsSaAsync(AxReadAccess.Script(_ax2012, SqlServerFixture.AgentLogin));
|
|
await using var session = await AgentSession.OpenAsync(sql);
|
|
var detector = new AxDatabaseDetector();
|
|
|
|
var first = await session.CollectAsync<AxBatchSnapshot>(new AxBatchCollector(detector), selection: Selection);
|
|
var second = await session.CollectAsync<AxBatchSnapshot>(new AxBatchCollector(detector), previous: first, selection: Selection);
|
|
var sequences = (await session.CollectAsync<AxSequenceSnapshot>(new AxSequenceCollector(detector), selection: Selection)).Databases.Single(d => d.Database == _ax2012);
|
|
|
|
var db = first.Databases.Single(d => d.Database == _ax2012);
|
|
Assert.Null(db.NotMeasurable);
|
|
Assert.Equal(7, db.NewRuns.Count);
|
|
Assert.Equal(5637145100, db.HistoryWatermark);
|
|
Assert.Empty(second.Databases.Single(d => d.Database == _ax2012).NewRuns);
|
|
var failed = second.Databases.Single(d => d.Database == _ax2012).Jobs.Single(j => j.Caption == "Webshop-integration");
|
|
Assert.Equal((AxBatchStatus.Error, AxBatchStatus.Error, 6), (failed.Status, failed.LastRunStatus, failed.HistoryRuns - 1));
|
|
Assert.Equal(600, failed.MedianSeconds!.Value, 0);
|
|
var order = sequences.NumberSequences.Single();
|
|
Assert.Equal(("dk01", true, "2001"), (order.Company, order.Referenced, order.Key));
|
|
Assert.Equal(64, sequences.RecIds.Single().Bits);
|
|
|
|
// The collectors of an instance share the connection: after the AX reads it has the agent's own settings again.
|
|
await using var check = session.Connection.CreateCommand();
|
|
check.CommandText = "SELECT @@LOCK_TIMEOUT, (SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID)";
|
|
await using var reader = await check.ExecuteReaderAsync();
|
|
Assert.True(await reader.ReadAsync());
|
|
Assert.Equal((5000, 1), (reader.GetInt32(0), (int)reader.GetInt16(1)));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task A_locked_ax_table_is_skipped_within_seconds_and_the_previous_picture_is_kept()
|
|
{
|
|
await sql.ExecuteAsSaAsync(AxReadAccess.Script(_ax30, SqlServerFixture.AgentLogin));
|
|
await using var session = await AgentSession.OpenAsync(sql);
|
|
var detector = new AxDatabaseDetector();
|
|
var before = await session.CollectAsync<AxBatchSnapshot>(new AxBatchCollector(detector), selection: Selection);
|
|
|
|
await using var locker = new SqlConnection(sql.DatabaseConnectionString(_ax30));
|
|
await locker.OpenAsync();
|
|
await using var transaction = (SqlTransaction)await locker.BeginTransactionAsync();
|
|
await using (var command = locker.CreateCommand())
|
|
{
|
|
command.Transaction = transaction;
|
|
command.CommandText = "UPDATE bmssa.BATCH SET DEL_PER = 1 WHERE RECID = 4;"; // a real change, held by an open transaction
|
|
await command.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
var watch = Stopwatch.StartNew();
|
|
var during = await session.CollectAsync<AxBatchSnapshot>(new AxBatchCollector(detector), previous: before, selection: Selection);
|
|
watch.Stop();
|
|
await transaction.RollbackAsync();
|
|
|
|
var db = during.Databases.Single(d => d.Database == _ax30);
|
|
Assert.True(watch.Elapsed < TimeSpan.FromSeconds(10), $"Ventede {watch.Elapsed}.");
|
|
Assert.Null(db.NotMeasurable);
|
|
Assert.Contains(db.Notes, n => n.Contains("låst", StringComparison.Ordinal));
|
|
Assert.Equal(before.Databases.Single(d => d.Database == _ax30).Jobs.Count, db.Jobs.Count);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task The_sql_scripts_run_as_is_against_ax30_and_ax2012()
|
|
{
|
|
await sql.ExecuteAsSaAsync(AxReadAccess.Script(_ax30, SqlServerFixture.AgentLogin));
|
|
var folder = Path.Combine(RepositoryRoot(), "SQL Scripts");
|
|
var ran = 0;
|
|
foreach (var file in Directory.EnumerateFiles(folder, "*.sql", SearchOption.AllDirectories)
|
|
.Where(f => Path.GetFileName(f).Contains("AX", StringComparison.OrdinalIgnoreCase)))
|
|
{
|
|
var text = await File.ReadAllTextAsync(file);
|
|
// Every block with SQL from the code ("-- Kilde:"), as a DBA would run it in SSMS.
|
|
foreach (var batch in Regex.Split(text, @"^\s*GO\s*$", RegexOptions.Multiline).Where(b => b.Contains("-- Kilde: ", StringComparison.Ordinal)))
|
|
{
|
|
var variant = Regex.Match(batch, @"-- Variant: (?<v>[^\r\n]*)").Groups["v"].Value;
|
|
var target = variant.StartsWith("Axapta 3.0", StringComparison.Ordinal) ? _ax30
|
|
: variant.StartsWith("AX 2012", StringComparison.Ordinal) ? _ax2012
|
|
: variant.StartsWith("SQL Server", StringComparison.Ordinal) || variant.StartsWith("Alle versioner", StringComparison.Ordinal) ? _ax30
|
|
: null;
|
|
if (target is null || string.IsNullOrWhiteSpace(variant))
|
|
{
|
|
continue; // AX 4.0 and AX 2009: no fake database of their own here.
|
|
}
|
|
|
|
await sql.ExecuteAsSaAsync(batch.Replace("N'AXDB'", $"N'{target}'", StringComparison.Ordinal).Replace("[AXDB]", $"[{target}]", StringComparison.Ordinal));
|
|
ran++;
|
|
}
|
|
}
|
|
|
|
Assert.True(ran >= 40, $"Kun {ran} varianter kørt.");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task The_repository_keeps_runs_and_one_sample_a_day_and_reads_them_back_for_the_agent()
|
|
{
|
|
var database = "SQLInsightAx_" + Guid.NewGuid().ToString("N")[..8];
|
|
await sql.ExecuteAsSaAsync($"CREATE DATABASE [{database}];");
|
|
try
|
|
{
|
|
var repository = new InsightRepository(sql.DatabaseConnectionString(database));
|
|
await repository.MigrateAsync();
|
|
var now = DateTimeOffset.UtcNow;
|
|
var instanceId = await repository.UpsertInstanceAsync("AX01", new Core.Instances.InstanceInfo
|
|
{
|
|
ServerName = "AX01", Version = Core.Instances.SqlServerVersion.Parse("16.0.4135.4"), Edition = "Standard Edition", EngineEdition = Core.Instances.EngineEdition.Standard,
|
|
}, "SqlClient", false, now);
|
|
var run = new AxBatchRun("101|dat|NAT", "J1@20261001120000", "Klasse 101", "dat", now.UtcDateTime.AddHours(-2), now.UtcDateTime.AddHours(-1), AxBatchStatus.Finished);
|
|
var batch = new AxBatchSnapshot
|
|
{
|
|
CapturedAt = now, Thresholds = new(),
|
|
Databases = [new AxBatchDatabase { Database = "AX30", Version = AxVersion.Axapta30, NewRuns = [run, run], HistoryWatermark = 42 }],
|
|
};
|
|
AxSequenceSnapshot Sequences(long next) => new()
|
|
{
|
|
CapturedAt = now, Thresholds = new(),
|
|
Databases = [new AxSequenceDatabase { Database = "AX30", NumberSequences = [new AxNumberSequence { Key = "dat/Debitor", Code = "Debitor", Next = next }] }],
|
|
};
|
|
|
|
await repository.SaveCollectedAsync(instanceId, DynamicsAxView.BatchKind, batch);
|
|
await repository.SaveCollectedAsync(instanceId, DynamicsAxView.BatchKind, batch);
|
|
await repository.SaveCollectedAsync(instanceId, DynamicsAxView.SequencesKind, Sequences(100));
|
|
await repository.SaveCollectedAsync(instanceId, DynamicsAxView.SequencesKind, Sequences(150));
|
|
|
|
var history = await repository.GetAxBatchHistoryAsync(instanceId, now.AddDays(-90));
|
|
var samples = await repository.GetAxSequenceHistoryAsync(instanceId, now.AddDays(-2));
|
|
var stored = await repository.GetSnapshotAsync<AxBatchSnapshot>(instanceId, DynamicsAxView.BatchKind);
|
|
|
|
Assert.Equal(TimeSpan.FromHours(1), Assert.Single(history.Runs("AX30", "101|dat|NAT")).Duration);
|
|
Assert.Equal(150, Assert.Single(samples[AxSequenceSnapshot.HistoryKey("AX30", 'N', "dat/Debitor")]).Value);
|
|
Assert.Equal(42, stored!.Databases.Single().HistoryWatermark);
|
|
|
|
await repository.RunMaintenanceAsync(new RetentionOptions { EventDays = 1, HourlyDays = 1 }, now.AddDays(3));
|
|
Assert.Equal(0, (await repository.GetAxBatchHistoryAsync(instanceId, now.AddDays(-90))).Count);
|
|
Assert.Empty(await repository.GetAxSequenceHistoryAsync(instanceId, now.AddDays(-2)));
|
|
}
|
|
finally
|
|
{
|
|
await sql.ExecuteAsSaAsync($"ALTER DATABASE [{database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{database}];");
|
|
}
|
|
}
|
|
|
|
internal static string RepositoryRoot()
|
|
{
|
|
var directory = new DirectoryInfo(AppContext.BaseDirectory);
|
|
while (directory is not null && !File.Exists(Path.Combine(directory.FullName, "SQLInsight.slnx")))
|
|
{
|
|
directory = directory.Parent;
|
|
}
|
|
|
|
return directory?.FullName ?? throw new InvalidOperationException("Repository-roden blev ikke fundet.");
|
|
}
|
|
}
|