amadzarak
291 lines
13 KiB
C#
291 lines
13 KiB
C#
using System.Text.Json;
|
|
using ControlPlane.Core.Models;
|
|
using Microsoft.Extensions.Logging;
|
|
using Npgsql;
|
|
|
|
namespace ControlPlane.Core.Services;
|
|
|
|
/// <summary>
|
|
/// Persists build and release history to opcdb (Postgres).
|
|
/// Replaces the previous JSON-file implementation.
|
|
/// NpgsqlDataSource is singleton and manages the connection pool; this service is safe to register as singleton.
|
|
/// </summary>
|
|
public class BuildHistoryService(NpgsqlDataSource db, ILogger<BuildHistoryService> logger)
|
|
{
|
|
// ── Builds ──────────────────────────────────────────────────────────────
|
|
|
|
public async Task<BuildRecord> CreateBuildAsync(BuildKind kind, string target)
|
|
{
|
|
var record = new BuildRecord { Kind = kind, Target = target };
|
|
|
|
await using var cmd = db.CreateCommand("""
|
|
INSERT INTO build_record (id, kind, target, status, started_at, commit_sha, log)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)
|
|
""");
|
|
cmd.Parameters.AddWithValue(record.Id);
|
|
cmd.Parameters.AddWithValue(record.Kind.ToString());
|
|
cmd.Parameters.AddWithValue(record.Target);
|
|
cmd.Parameters.AddWithValue(record.Status.ToString());
|
|
cmd.Parameters.AddWithValue(record.StartedAt);
|
|
cmd.Parameters.AddWithValue((object?)record.CommitSha ?? DBNull.Value);
|
|
cmd.Parameters.AddWithValue(string.Empty);
|
|
await cmd.ExecuteNonQueryAsync();
|
|
|
|
return record;
|
|
}
|
|
|
|
public async Task CompleteBuildAsync(BuildRecord record, BuildStatus status, string? digest = null)
|
|
{
|
|
record.Status = status;
|
|
record.FinishedAt = DateTimeOffset.UtcNow;
|
|
record.DurationMs = (int)(record.FinishedAt.Value - record.StartedAt).TotalMilliseconds;
|
|
record.ImageDigest = digest;
|
|
|
|
await using var cmd = db.CreateCommand("""
|
|
UPDATE build_record
|
|
SET status = $2, finished_at = $3, duration_ms = $4, image_digest = $5, commit_sha = $6, log = $7
|
|
WHERE id = $1
|
|
""");
|
|
cmd.Parameters.AddWithValue(record.Id);
|
|
cmd.Parameters.AddWithValue(record.Status.ToString());
|
|
cmd.Parameters.AddWithValue(record.FinishedAt!.Value);
|
|
cmd.Parameters.AddWithValue((object?)record.DurationMs ?? DBNull.Value);
|
|
cmd.Parameters.AddWithValue((object?)record.ImageDigest ?? DBNull.Value);
|
|
cmd.Parameters.AddWithValue((object?)record.CommitSha ?? DBNull.Value);
|
|
cmd.Parameters.AddWithValue(string.Join('\n', record.Log));
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
public async Task AppendBuildLogAsync(BuildRecord record, string line)
|
|
{
|
|
record.Log.Add(line);
|
|
// Flush to Postgres every 20 lines — keeps the live log queryable without hammering the DB
|
|
if (record.Log.Count % 20 == 0)
|
|
await FlushLogAsync(record);
|
|
}
|
|
|
|
private async Task FlushLogAsync(BuildRecord record)
|
|
{
|
|
await using var cmd = db.CreateCommand("UPDATE build_record SET log = $2 WHERE id = $1");
|
|
cmd.Parameters.AddWithValue(record.Id);
|
|
cmd.Parameters.AddWithValue(string.Join('\n', record.Log));
|
|
await cmd.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
public async Task<List<BuildRecord>> GetBuildsAsync()
|
|
{
|
|
var result = new List<BuildRecord>();
|
|
|
|
await using var cmd = db.CreateCommand("""
|
|
SELECT id, kind, target, status, started_at, finished_at, duration_ms, image_digest, commit_sha, log
|
|
FROM build_record
|
|
ORDER BY started_at DESC
|
|
LIMIT 100
|
|
""");
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var logText = reader.IsDBNull(9) ? "" : reader.GetString(9);
|
|
result.Add(new BuildRecord
|
|
{
|
|
Id = reader.GetString(0),
|
|
Kind = Enum.Parse<BuildKind>(reader.GetString(1)),
|
|
Target = reader.GetString(2),
|
|
Status = Enum.Parse<BuildStatus>(reader.GetString(3)),
|
|
StartedAt = reader.GetFieldValue<DateTimeOffset>(4),
|
|
FinishedAt = reader.IsDBNull(5) ? null : reader.GetFieldValue<DateTimeOffset>(5),
|
|
DurationMs = reader.IsDBNull(6) ? null : reader.GetInt32(6),
|
|
ImageDigest = reader.IsDBNull(7) ? null : reader.GetString(7),
|
|
CommitSha = reader.IsDBNull(8) ? null : reader.GetString(8),
|
|
Log = logText.Length == 0 ? [] : [.. logText.Split('\n')],
|
|
});
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
// ── Builds by SHA ────────────────────────────────────────────────────────
|
|
|
|
/// <summary>Returns all build records whose <c>commit_sha</c> exactly matches <paramref name="sha"/>.</summary>
|
|
public async Task<List<BuildRecord>> GetBuildsByShaAsync(string sha)
|
|
{
|
|
var result = new List<BuildRecord>();
|
|
|
|
await using var cmd = db.CreateCommand("""
|
|
SELECT id, kind, target, status, started_at, finished_at, duration_ms, image_digest, commit_sha, log
|
|
FROM build_record
|
|
WHERE commit_sha = $1
|
|
ORDER BY started_at DESC
|
|
""");
|
|
cmd.Parameters.AddWithValue(sha);
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var logText = reader.IsDBNull(9) ? "" : reader.GetString(9);
|
|
result.Add(new BuildRecord
|
|
{
|
|
Id = reader.GetString(0),
|
|
Kind = Enum.Parse<BuildKind>(reader.GetString(1)),
|
|
Target = reader.GetString(2),
|
|
Status = Enum.Parse<BuildStatus>(reader.GetString(3)),
|
|
StartedAt = reader.GetFieldValue<DateTimeOffset>(4),
|
|
FinishedAt = reader.IsDBNull(5) ? null : reader.GetFieldValue<DateTimeOffset>(5),
|
|
DurationMs = reader.IsDBNull(6) ? null : reader.GetInt32(6),
|
|
ImageDigest = reader.IsDBNull(7) ? null : reader.GetString(7),
|
|
CommitSha = reader.IsDBNull(8) ? null : reader.GetString(8),
|
|
Log = logText.Length == 0 ? [] : [.. logText.Split('\n')],
|
|
});
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
// ── Releases ────────────────────────────────────────────────────────────
|
|
|
|
public async Task<ReleaseRecord> CreateReleaseAsync(string environment, string imageName, string? commitSha = null)
|
|
{
|
|
var record = new ReleaseRecord { Environment = environment, ImageName = imageName, CommitSha = commitSha };
|
|
|
|
await using var cmd = db.CreateCommand("""
|
|
INSERT INTO release_record (id, environment, image_name, status, started_at, opc_numbers, commit_sha)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)
|
|
""");
|
|
cmd.Parameters.AddWithValue(record.Id);
|
|
cmd.Parameters.AddWithValue(record.Environment);
|
|
cmd.Parameters.AddWithValue(record.ImageName);
|
|
cmd.Parameters.AddWithValue(record.Status.ToString());
|
|
cmd.Parameters.AddWithValue(record.StartedAt);
|
|
cmd.Parameters.Add(new NpgsqlParameter<string[]> { TypedValue = [.. record.OpcNumbers] });
|
|
cmd.Parameters.AddWithValue((object?)record.CommitSha ?? DBNull.Value);
|
|
await cmd.ExecuteNonQueryAsync();
|
|
|
|
return record;
|
|
}
|
|
|
|
public async Task UpdateReleaseAsync(ReleaseRecord record)
|
|
{
|
|
record.FinishedAt = DateTimeOffset.UtcNow;
|
|
|
|
await using var conn = await db.OpenConnectionAsync();
|
|
await using var tx = await conn.BeginTransactionAsync();
|
|
|
|
await using var upd = new NpgsqlCommand("""
|
|
UPDATE release_record SET status = $2, finished_at = $3, opc_numbers = $4, commit_sha = $5 WHERE id = $1
|
|
""", conn, tx);
|
|
upd.Parameters.AddWithValue(record.Id);
|
|
upd.Parameters.AddWithValue(record.Status.ToString());
|
|
upd.Parameters.AddWithValue(record.FinishedAt!.Value);
|
|
upd.Parameters.Add(new NpgsqlParameter<string[]> { TypedValue = [.. record.OpcNumbers] });
|
|
upd.Parameters.AddWithValue((object?)record.CommitSha ?? DBNull.Value);
|
|
await upd.ExecuteNonQueryAsync();
|
|
|
|
// Replace tenant results wholesale on each update
|
|
await using var del = new NpgsqlCommand(
|
|
"DELETE FROM release_tenant_result WHERE release_id = $1", conn, tx);
|
|
del.Parameters.AddWithValue(record.Id);
|
|
await del.ExecuteNonQueryAsync();
|
|
|
|
foreach (var t in record.Tenants)
|
|
{
|
|
await using var ins = new NpgsqlCommand("""
|
|
INSERT INTO release_tenant_result (release_id, subdomain, container_name, success, error)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
""", conn, tx);
|
|
ins.Parameters.AddWithValue(record.Id);
|
|
ins.Parameters.AddWithValue(t.Subdomain);
|
|
ins.Parameters.AddWithValue(t.ContainerName);
|
|
ins.Parameters.AddWithValue(t.Success);
|
|
ins.Parameters.AddWithValue((object?)t.Error ?? DBNull.Value);
|
|
await ins.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
await tx.CommitAsync();
|
|
}
|
|
|
|
public async Task<List<ReleaseRecord>> GetReleasesAsync()
|
|
{
|
|
var ordered = new List<ReleaseRecord>();
|
|
var lookup = new Dictionary<string, ReleaseRecord>();
|
|
|
|
await using var cmd = db.CreateCommand("""
|
|
SELECT id, environment, image_name, status, started_at, finished_at, opc_numbers, commit_sha
|
|
FROM release_record
|
|
ORDER BY started_at DESC
|
|
LIMIT 50
|
|
""");
|
|
await using (var reader = await cmd.ExecuteReaderAsync())
|
|
{
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var r = new ReleaseRecord
|
|
{
|
|
Id = reader.GetString(0),
|
|
Environment = reader.GetString(1),
|
|
ImageName = reader.GetString(2),
|
|
Status = Enum.Parse<ReleaseStatus>(reader.GetString(3)),
|
|
StartedAt = reader.GetFieldValue<DateTimeOffset>(4),
|
|
FinishedAt = reader.IsDBNull(5) ? null : reader.GetFieldValue<DateTimeOffset>(5),
|
|
OpcNumbers = reader.IsDBNull(6) ? [] : [.. reader.GetFieldValue<string[]>(6)],
|
|
CommitSha = reader.IsDBNull(7) ? null : reader.GetString(7),
|
|
};
|
|
ordered.Add(r);
|
|
lookup[r.Id] = r;
|
|
}
|
|
}
|
|
|
|
if (lookup.Count == 0) return [];
|
|
|
|
// Load all tenant results for the fetched release IDs in one query
|
|
await using var cmd2 = db.CreateCommand("""
|
|
SELECT release_id, subdomain, container_name, success, error
|
|
FROM release_tenant_result
|
|
WHERE release_id = ANY($1)
|
|
""");
|
|
cmd2.Parameters.Add(new NpgsqlParameter<string[]> { TypedValue = [.. lookup.Keys] });
|
|
await using var reader2 = await cmd2.ExecuteReaderAsync();
|
|
while (await reader2.ReadAsync())
|
|
{
|
|
if (lookup.TryGetValue(reader2.GetString(0), out var r))
|
|
r.Tenants.Add(new TenantReleaseResult
|
|
{
|
|
Subdomain = reader2.GetString(1),
|
|
ContainerName = reader2.GetString(2),
|
|
Success = reader2.GetBoolean(3),
|
|
Error = reader2.IsDBNull(4) ? null : reader2.GetString(4),
|
|
});
|
|
}
|
|
|
|
return ordered;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Returns the most recent succeeded release for <paramref name="environment"/>, or null if none exists.
|
|
/// Used to calculate the OPC ticket delta between releases (previousSha..currentSha).
|
|
/// </summary>
|
|
public async Task<ReleaseRecord?> GetLastSuccessfulReleaseForEnvAsync(string environment)
|
|
{
|
|
await using var cmd = db.CreateCommand("""
|
|
SELECT id, environment, image_name, status, started_at, finished_at, opc_numbers, commit_sha
|
|
FROM release_record
|
|
WHERE environment = $1 AND status = 'Succeeded'
|
|
ORDER BY started_at DESC
|
|
LIMIT 1
|
|
""");
|
|
cmd.Parameters.AddWithValue(environment);
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
if (!await reader.ReadAsync()) return null;
|
|
return new ReleaseRecord
|
|
{
|
|
Id = reader.GetString(0),
|
|
Environment = reader.GetString(1),
|
|
ImageName = reader.GetString(2),
|
|
Status = Enum.Parse<ReleaseStatus>(reader.GetString(3)),
|
|
StartedAt = reader.GetFieldValue<DateTimeOffset>(4),
|
|
FinishedAt = reader.IsDBNull(5) ? null : reader.GetFieldValue<DateTimeOffset>(5),
|
|
OpcNumbers = reader.IsDBNull(6) ? [] : [.. reader.GetFieldValue<string[]>(6)],
|
|
CommitSha = reader.IsDBNull(7) ? null : reader.GetString(7),
|
|
};
|
|
}
|
|
}
|
|
|