169 lines
8.0 KiB
C#
169 lines
8.0 KiB
C#
namespace Omika.Diagnostics.Tests;
|
|
|
|
/// <summary>
|
|
/// Contrat du schéma PostgreSQL (§15-§17, §28) : vérifie que le fichier SQL déclare
|
|
/// les tables, la clé unique <c>event_id</c> (déduplication) et la table des métriques.
|
|
/// </summary>
|
|
public class DiagnosticSchemaContractTests
|
|
{
|
|
[Fact]
|
|
public void Schema_DeclaresEventTable_WithUniqueEventId()
|
|
{
|
|
string sql = ReadSchema();
|
|
|
|
Assert.Contains("CREATE TABLE IF NOT EXISTS diagnostic_event", sql);
|
|
Assert.Contains("event_id", sql);
|
|
Assert.Contains("UNIQUE", sql);
|
|
Assert.Contains("ux_diagnostic_event_event_id", sql); // ON CONFLICT (event_id) DO NOTHING
|
|
}
|
|
|
|
[Fact]
|
|
public void Schema_DeclaresMetricTable_AndRequiredIndexes()
|
|
{
|
|
string sql = ReadSchema();
|
|
|
|
Assert.Contains("CREATE TABLE IF NOT EXISTS telemetry_metric_hourly", sql);
|
|
Assert.Contains("metric_name", sql);
|
|
Assert.Contains("ux_telemetry_metric_hourly", sql);
|
|
}
|
|
|
|
[Fact]
|
|
public void ActorKeyMigration_IsIdempotent_AndNonDestructive()
|
|
{
|
|
string sql = ReadMigration("002_actor_key.sql");
|
|
|
|
Assert.Contains("ALTER TABLE diagnostic_event", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS actor_key", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS actor_key_version", sql);
|
|
Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_actor_key_timestamp", sql);
|
|
Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
|
|
[Fact]
|
|
public void ClientContextMigration_IsIdempotent_AndDeclaresNullableColumns()
|
|
{
|
|
string sql = ReadMigration("005_diagnostics_client_context.sql");
|
|
|
|
Assert.Contains("ALTER TABLE diagnostic_event", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS client_code", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS server_instance", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS environment", sql);
|
|
Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_client_code_timestamp", sql);
|
|
Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("UPDATE", sql, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
|
|
[Fact]
|
|
public void DeviceLicenseMigration_IsIdempotent_AndDeclaresNullableColumns()
|
|
{
|
|
string sql = ReadMigration("006_diagnostics_device_license.sql");
|
|
|
|
Assert.Contains("ALTER TABLE diagnostic_event", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS license_number", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS device_key", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS device_key_version", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS device_manufacturer", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS device_model", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS operating_system", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS operating_system_version", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS app_build", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS screen_width", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS screen_height", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS logical_width", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS logical_height", sql);
|
|
Assert.Contains("ADD COLUMN IF NOT EXISTS display_scale", sql);
|
|
Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_device_key_timestamp", sql);
|
|
Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_license_number_timestamp", sql);
|
|
// Types : dimensions écran entières, display_scale réel.
|
|
Assert.Contains("screen_width INTEGER", sql);
|
|
Assert.Contains("display_scale DOUBLE PRECISION", sql);
|
|
// Non destructif : aucune suppression/réécriture.
|
|
Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("UPDATE", sql, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
|
|
[Fact]
|
|
public void TasksMigration_IsIdempotent_AndNonDestructive()
|
|
{
|
|
string sql = ReadMigration("012_diagnostics_tasks.sql");
|
|
|
|
Assert.Contains("CREATE TABLE IF NOT EXISTS diagnostics_tasks", sql);
|
|
Assert.Contains("group_url_identifier", sql);
|
|
Assert.Contains("ux_diagnostics_tasks_group_url_identifier", sql);
|
|
Assert.Contains("GRANT SELECT ON TABLE diagnostics_tasks TO omika_diagnostics_web", sql);
|
|
Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
|
|
[Fact]
|
|
public void RetentionUntreatedMigration_IsIdempotent_AndColumnScoped()
|
|
{
|
|
string sql = ReadMigration("015_diagnostics_retention_untreated.sql");
|
|
|
|
// Moindre privilège : SELECT UNIQUEMENT sur group_url_identifier + status (le
|
|
// rapprochement « traité/non traité »), jamais sur title/description/cause ni
|
|
// les usernames. Aucun droit d'écriture accordé au worker.
|
|
Assert.Contains("GRANT SELECT (group_url_identifier, status)", sql);
|
|
Assert.Contains("ON TABLE diagnostics_tasks TO omika_diagnostics_worker", sql);
|
|
Assert.DoesNotContain("GRANT INSERT", sql);
|
|
Assert.DoesNotContain("GRANT UPDATE", sql);
|
|
Assert.DoesNotContain("GRANT DELETE", sql);
|
|
Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
|
|
[Fact]
|
|
public void EventDeleteMigration_GrantsDeleteToWebOnly_AndIsNonDestructive()
|
|
{
|
|
string sql = ReadMigration("016_diagnostics_event_delete.sql");
|
|
|
|
// Moindre privilège : DELETE sur diagnostic_event accordé au SEUL rôle Web
|
|
// (suppression d'un groupe d'erreurs depuis /Erreurs). Aucun GRANT accordé au
|
|
// Worker ni à l'API (le Worker a déjà son propre périmètre via la migration 004).
|
|
Assert.Contains("GRANT DELETE ON TABLE diagnostic_event TO omika_diagnostics_web", sql);
|
|
Assert.DoesNotContain("TO omika_diagnostics_worker", sql);
|
|
Assert.DoesNotContain("TO omika_diagnostics_api", sql);
|
|
Assert.DoesNotContain("GRANT INSERT", sql);
|
|
Assert.DoesNotContain("GRANT UPDATE", sql);
|
|
Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
|
|
private static string ReadSchema()
|
|
{
|
|
string root = FindRepoRoot();
|
|
return File.ReadAllText(Path.Combine(root, "infra", "postgresql", "001_diagnostics_schema.sql"));
|
|
}
|
|
|
|
private static string ReadMigration(string fileName)
|
|
{
|
|
string root = FindRepoRoot();
|
|
return File.ReadAllText(Path.Combine(root, "infra", "postgresql", fileName));
|
|
}
|
|
|
|
private static string FindRepoRoot()
|
|
{
|
|
DirectoryInfo? dir = new(AppContext.BaseDirectory);
|
|
while (dir is not null)
|
|
{
|
|
if (File.Exists(Path.Combine(dir.FullName, "Omika.slnx")))
|
|
{
|
|
return dir.FullName;
|
|
}
|
|
|
|
dir = dir.Parent;
|
|
}
|
|
|
|
throw new InvalidOperationException("Racine du dépôt introuvable (Omika.slnx absent).");
|
|
}
|
|
}
|