Søren Eggert Lundsteen Olsen
31fa89b385
- A lock timeout or deadlock while detecting (e.g. during an AX table synchronisation) dropped the database from AX monitoring for an hour, so its findings resolved and reopened. The earlier detection is kept and retried within 5 minutes. A cancelled collection is no longer reported as "læsningen fejlede". - A lock timeout kept the earlier batch picture forever; now at most 15 minutes (MeasuredAt), so a job that has started is not reported late from an old picture. - revoke-dynamics-ax-read.sql revoked SELECT on same-named tables in every schema, also a DENY set by a DBA. It now finds the AX schema like the grant script and revokes only granted SELECT there. - SQLI-AX-006 skips blocked sequences (as -005/-008 do); forecasts under a day say "inden for et døgn" instead of "om ca. 0 dage". - Integration tests: the SQL Scripts run as-is in main's format, and the shared connection has the agent's session settings after the AX reads. - docs/dynamics-ax.md: history bounds, lock-timeout window, session settings, /detection-problems, API redaction. 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', 0, CONVERT(date, @late), DATEDIFF(second, CONVERT(date, @late), @late), '19000101', 0),
|
|
(2, 102, 'dat', 'NAT', 2, CONVERT(date, @failed), DATEDIFF(second, CONVERT(date, @failed), @failed), CONVERT(date, @failed), DATEDIFF(second, CONVERT(date, @failed), @failed) + 60),
|
|
(3, 103, 'dat', '', 1, CONVERT(date, @running), DATEDIFF(second, CONVERT(date, @running), @running), '19000101', 0),
|
|
(4, 104, 'dmo', '', 3, 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.");
|
|
}
|
|
}
|