122 lines
3.7 KiB
C#
122 lines
3.7 KiB
C#
using Mindrockets.Helpers;
|
|
using Npgsql;
|
|
using System;
|
|
using System.Configuration;
|
|
using System.Threading;
|
|
|
|
namespace UpdateSentencesDate
|
|
{
|
|
internal class Program
|
|
{
|
|
private const int BatchSize = 1000;
|
|
private const int MaxRetries = 3;
|
|
|
|
static int Main(string[] args)
|
|
{
|
|
Mindrockets.Helpers.Logger.Instance.Info($"[{DateTime.UtcNow:u}] Job started");
|
|
|
|
string connectionString = ConfigurationManager
|
|
.ConnectionStrings["PostgreSQL"]?.ConnectionString;
|
|
|
|
if (string.IsNullOrWhiteSpace(connectionString))
|
|
{
|
|
Logger.Instance.Error("Connection string 'PostgreSQL' not found.");
|
|
return 1;
|
|
}
|
|
|
|
int totalUpdated = 0;
|
|
|
|
try
|
|
{
|
|
while (true)
|
|
{
|
|
int rows = ExecuteBatch(connectionString);
|
|
|
|
totalUpdated += rows;
|
|
Logger.Instance.Info($"[{DateTime.UtcNow:u}] Batch updated: {rows}");
|
|
|
|
if (rows < BatchSize)
|
|
break; // no more rows to process
|
|
}
|
|
|
|
Logger.Instance.Info($"[{DateTime.UtcNow:u}] Job completed. Total rows updated: {totalUpdated}");
|
|
return 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Logger.Instance.Error($"[{DateTime.UtcNow:u}] FATAL ERROR");
|
|
Logger.Instance.Error(ex);
|
|
return 1;
|
|
}
|
|
}
|
|
|
|
private static int ExecuteBatch(string connectionString)
|
|
{
|
|
for (int attempt = 1; attempt <= MaxRetries; attempt++)
|
|
{
|
|
try
|
|
{
|
|
using (var conn = new NpgsqlConnection(connectionString))
|
|
{
|
|
conn.Open();
|
|
|
|
using (var tx = conn.BeginTransaction())
|
|
{
|
|
using (var cmd = new NpgsqlCommand(UpdateQuery, conn, tx))
|
|
{
|
|
cmd.CommandTimeout = 60;
|
|
|
|
cmd.Parameters.AddWithValue("@now", DateTime.UtcNow);
|
|
cmd.Parameters.AddWithValue("@batchSize", BatchSize);
|
|
|
|
int affected = cmd.ExecuteNonQuery();
|
|
|
|
tx.Commit();
|
|
return affected;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (PostgresException pgex)
|
|
{
|
|
Logger.Instance.Info($"[{DateTime.UtcNow:u}] PostgreSQL error (attempt {attempt}): {pgex.Message}");
|
|
|
|
if (attempt == MaxRetries)
|
|
throw;
|
|
|
|
Thread.Sleep(2000);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Logger.Instance.Error($"[{DateTime.UtcNow:u}] Error (attempt {attempt}): {ex.Message}");
|
|
|
|
if (attempt == MaxRetries)
|
|
throw;
|
|
|
|
Thread.Sleep(2000);
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
private static readonly string UpdateQuery = @"
|
|
WITH target AS (
|
|
SELECT ""Id""
|
|
FROM dbo.""Translations""
|
|
WHERE ""NeedsReview"" = TRUE
|
|
AND (""TrackerId"" = 3 OR ""TrackerId"" = 6)
|
|
AND ""LastTrackDate"" IS NOT NULL
|
|
AND ""LastTrackDate"" >= (@now - INTERVAL '7 days')
|
|
AND ""LastTrackDate"" < (@now - INTERVAL '1 day')
|
|
ORDER BY ""LastTrackDate"" ASC
|
|
LIMIT @batchSize
|
|
FOR UPDATE SKIP LOCKED
|
|
)
|
|
UPDATE dbo.""Translations"" t
|
|
SET ""LastTrackDate"" = @now
|
|
FROM target
|
|
WHERE t.""Id"" = target.""Id"";
|
|
";
|
|
}
|
|
} |