mirror of
https://github.com/hanzoai/jube-fork.git
synced 2026-10-30 18:33:33 +00:00
richard-churchman
a6bbd81271
* Added `Cache` flag to `EntityAnalysisRequestXPath`, updating POCOs, DTOs, and synchronisation mappings. Included migrations and updated pages and JS for new fields. * Updated the Completions Endpoint to only return values if Cache is enabled in the EntityAnalysisModelRequestXPath for EntityAnalysisModelAbstractionRule configuration. * Updated the parser to ignore fields if the parse processing type is Abstraction Rule, so to ensure that an Abstraction Rule does not compile anywhere, in the controller or syncronisation, if it trys to use a key that exists but is marked as Cache. * Created migrations for the cache flag and added indexes to cache key columns. * Implemented a new serialisation pattern for message pack payloads to reduce byte storage of names, replacing them with a `CacheIndexId` serialised as a single byte. * Implemented indexing logic for `CacheIndexId`, which increments within a transaction per `EntityAnalysisModel`. Created `CacheIndexId` values for legacy data. * Created migrations for legacy Redis cache payload data to adopt the new `CacheIndexId` format. Added a unique composite index to enforce sequencing and eliminate conflict risks. * Implemented a new LRU cache warming strategy that populates the LRU cache based on journal usage. During warming, cache entries are populated in order of most recently used journals, exiting when byte limits are reached. This replaces a full cursor scan of payload data, reducing container startup time and mitigating Redis herd-stampede risks. Each journal recall writes to a sorted set (`LruJournals`) that maintains an ordered activity list for cache population. On payload entry removal, the corresponding journal is checked and the `LruJournal` key is removed if no further data exists. Added a `SortedSetLruJournalRemove` enum for task tracking. Implemented an `IsFull` method on the local LRU cache to exit warming once full. * Updated TTL Counters to process `double` values instead of `int` to support summing of integer and float payload types, making Volume and Velocity rules available to TTL Counters and long-term state. Updated POCOs, DTOs, and synchronisation mappings, applied logic in invocation processes, and included migrations. Updated pages and JS for new fields. * Updated TTL Counters to specify date resolution for TTL Counter entries, reducing memory footprint by allowing aggregation at broader date roundings (e.g. for long-term state, a daily entry expiry is sufficient). Updated VB.NET code wrappers for the new generic. * Updated TTL Counters to support an `EnableSum` flag, which uses a `TtlCounterDataValue` field to specify the value to sum. Implemented a dropdown in the page and JS that returns only integer and float data types for selection. * Updated the efficient no-boxing dictionary to instantiate on the basis of generic type, enabling use with both `int` (message pack) and `string` (core processing and legacy payload migrations). This touched a large number of files and is the primary reason for the size of this commit. Updated the dictionary to support addition of internal values via the interface for serialisation and mapping. Updated JSON serialisers to support the generic. * Implemented invocation mapping logic for insert and recall between the indexed Redis payload and the string-keyed payload required for processing. * Bumped the message pack envelope version flag to version 2 to reflect the new payload formats (noting that all data has been migrated and version 1 no longer exists in Redis). * Updated documentation to cover `LruJournals` in the Redis section, the `Cache` field on the `EntityAnalysisModelsRequestXPath` page, and the `ResolutionInterval` and `TtlCounterDataValue` fields on the `EntityAnalysisTtlCounters` page.
366 lines
15 KiB
C#
366 lines
15 KiB
C#
/* Copyright (C) 2022-present Jube Holdings Limited.
|
|
*
|
|
* This file is part of Jube™ software.
|
|
*
|
|
* Jube™ is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License
|
|
* as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
|
|
* Jube™ is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty
|
|
* of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.
|
|
|
|
* You should have received a copy of the GNU Affero General Public License along with Jube™. If not,
|
|
* see <https://www.gnu.org/licenses/>.
|
|
*/
|
|
|
|
namespace Jube.Data.Reporting
|
|
{
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Dynamic;
|
|
using System.Linq;
|
|
using System.Threading;
|
|
using System.Threading.Tasks;
|
|
using Dictionary;
|
|
using Extension;
|
|
using Newtonsoft.Json;
|
|
using Npgsql;
|
|
|
|
public class Postgres(string connectionString)
|
|
{
|
|
public async Task<Dictionary<string, string>> IntrospectAsync(string sql, Dictionary<string, object> parameters, CancellationToken token = default)
|
|
{
|
|
var connection = new NpgsqlConnection(connectionString);
|
|
var values = new Dictionary<string, string>();
|
|
try
|
|
{
|
|
await connection.OpenAsync(token).ConfigureAwait(false);
|
|
|
|
var tableName = "Temp_" + Guid.NewGuid().ToString("N");
|
|
|
|
var wrapSql = $"select * into TEMPORARY TABLE {tableName} from (select * from ({sql}) b LIMIT 0) c";
|
|
var commandTempTable = new NpgsqlCommand(wrapSql);
|
|
commandTempTable.Connection = connection;
|
|
|
|
foreach (var (key, value) in parameters.Where(parameter => sql.Contains("@" + parameter.Key)))
|
|
{
|
|
commandTempTable.Parameters.AddWithValue(key, value);
|
|
}
|
|
|
|
await commandTempTable.ExecuteNonQueryAsync(token).ConfigureAwait(false);
|
|
|
|
var introspectionSql = "SELECT attname, format_type(atttypid, atttypmod) AS type" +
|
|
" FROM pg_attribute" +
|
|
$" WHERE attrelid = '{tableName}'::regclass" +
|
|
" AND attnum > 0 " +
|
|
" AND NOT attisdropped " +
|
|
" ORDER BY attnum";
|
|
|
|
|
|
var command = new NpgsqlCommand(introspectionSql);
|
|
command.Connection = connection;
|
|
|
|
var reader = await command.ExecuteReaderAsync(token).ConfigureAwait(false);
|
|
while (await reader.ReadAsync(token).ConfigureAwait(false))
|
|
{
|
|
values.Add(reader.GetValue(0).AsString(), reader.GetValue(1).AsString());
|
|
}
|
|
|
|
await reader.CloseAsync().ConfigureAwait(false);
|
|
await reader.DisposeAsync().ConfigureAwait(false);
|
|
await command.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
catch
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
return values;
|
|
}
|
|
|
|
public async Task PrepareAsync(string sql, List<object> parameters, CancellationToken token = default)
|
|
{
|
|
var connection = new NpgsqlConnection(connectionString);
|
|
try
|
|
{
|
|
await connection.OpenAsync(token).ConfigureAwait(false);
|
|
|
|
var command = new NpgsqlCommand(sql);
|
|
command.Connection = connection;
|
|
|
|
for (var i = 0; i < parameters.Count; i++)
|
|
{
|
|
command.Parameters.AddWithValue("@" + (i + 1), parameters[i]);
|
|
}
|
|
|
|
await command.PrepareAsync(token).ConfigureAwait(false);
|
|
}
|
|
catch
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
}
|
|
|
|
public async Task<List<string>> ExecuteReturnOnlyJsonFromArchiveSampleAsync(int entityAnalysisModelId,
|
|
string filterSql,
|
|
string filterTokens,
|
|
int limit, bool mockData, CancellationToken token = default)
|
|
{
|
|
var value = new List<string>();
|
|
var connection = new NpgsqlConnection(connectionString);
|
|
try
|
|
{
|
|
await connection.OpenAsync(token).ConfigureAwait(false);
|
|
|
|
var tokens = JsonConvert.DeserializeObject<List<object>>(filterTokens);
|
|
tokens.Add(entityAnalysisModelId);
|
|
tokens.Add(limit);
|
|
|
|
var tableName = mockData ? "MockArchive" : "Archive";
|
|
|
|
var sql =
|
|
$"select \"Json\" from \"{tableName}\" where \"EntityAnalysisModelId\" = (@{tokens.Count - 1})"
|
|
+ " and " + filterSql
|
|
+ $" order by \"EntityAnalysisModelInstanceEntryGuid\" limit (@{limit})";
|
|
|
|
var command = new NpgsqlCommand(sql);
|
|
command.Connection = connection;
|
|
|
|
for (var i = 0; i < tokens.Count; i++)
|
|
{
|
|
command.Parameters.AddWithValue("@" + (i + 1), tokens[i]);
|
|
}
|
|
|
|
await command.PrepareAsync(token).ConfigureAwait(false);
|
|
|
|
var reader = await command.ExecuteReaderAsync(token).ConfigureAwait(false);
|
|
|
|
while (await reader.ReadAsync(token).ConfigureAwait(false))
|
|
{
|
|
value.Add(reader.GetValue(0).AsString());
|
|
}
|
|
|
|
await reader.CloseAsync().ConfigureAwait(false);
|
|
await reader.DisposeAsync().ConfigureAwait(false);
|
|
await command.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
catch
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
return value;
|
|
}
|
|
|
|
public async Task<List<DictionaryNoBoxing<string>>> ExecuteReturnPayloadFromArchiveWithSkipLimitAsync(
|
|
string sql,
|
|
DateTime adjustedStartDate,
|
|
int skip,
|
|
int limit,
|
|
CancellationToken token = default)
|
|
{
|
|
var value = new List<DictionaryNoBoxing<string>>();
|
|
var connection = new NpgsqlConnection(connectionString);
|
|
try
|
|
{
|
|
await connection.OpenAsync(token).ConfigureAwait(false);
|
|
|
|
var command = new NpgsqlCommand(sql);
|
|
command.Connection = connection;
|
|
command.Parameters.AddWithValue("adjustedStartDate", adjustedStartDate);
|
|
command.Parameters.AddWithValue("limit", limit);
|
|
command.Parameters.AddWithValue("skip", skip);
|
|
|
|
await command.PrepareAsync(token).ConfigureAwait(false);
|
|
|
|
var reader = await command.ExecuteReaderAsync(token).ConfigureAwait(false);
|
|
while (await reader.ReadAsync(token).ConfigureAwait(false))
|
|
{
|
|
var dictionaryNoBoxing = new DictionaryNoBoxing<string>();
|
|
for (var index = 0; index < reader.FieldCount; index++)
|
|
{
|
|
if (!dictionaryNoBoxing.ContainsKey(reader.GetName(index)))
|
|
{
|
|
var clrType = reader.GetFieldType(index);
|
|
|
|
if (await reader.IsDBNullAsync(index, token))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (clrType == typeof(int))
|
|
{
|
|
dictionaryNoBoxing.TryAdd(reader.GetName(index), reader.GetValue(index).AsInt());
|
|
}
|
|
else if (clrType == typeof(decimal) || clrType == typeof(float) || clrType == typeof(double))
|
|
{
|
|
dictionaryNoBoxing.TryAdd(reader.GetName(index), reader.GetValue(index).AsDouble());
|
|
}
|
|
else if (clrType == typeof(bool))
|
|
{
|
|
dictionaryNoBoxing.TryAdd(reader.GetName(index), reader.GetBoolean(index));
|
|
}
|
|
else if (clrType == typeof(string))
|
|
{
|
|
dictionaryNoBoxing.TryAdd(reader.GetName(index), reader.GetValue(index).AsString());
|
|
}
|
|
else if (clrType == typeof(DateTime))
|
|
{
|
|
dictionaryNoBoxing.TryAdd(reader.GetName(index), reader.GetValue(index).AsDateTime());
|
|
}
|
|
else if (clrType == typeof(Guid))
|
|
{
|
|
dictionaryNoBoxing.TryAdd(reader.GetName(index), reader.GetValue(index).AsGuid());
|
|
}
|
|
}
|
|
}
|
|
|
|
value.Add(dictionaryNoBoxing);
|
|
}
|
|
|
|
await reader.CloseAsync().ConfigureAwait(false);
|
|
await reader.DisposeAsync().ConfigureAwait(false);
|
|
await command.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
catch
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
return value;
|
|
}
|
|
|
|
public async Task<List<IDictionary<string, object>>> ExecuteByNamedParametersAsync(string sql,
|
|
Dictionary<string, object> parameters, CancellationToken token = default)
|
|
{
|
|
var value = new List<IDictionary<string, object>>();
|
|
var connection = new NpgsqlConnection(connectionString);
|
|
try
|
|
{
|
|
await connection.OpenAsync(token).ConfigureAwait(false);
|
|
|
|
var command = new NpgsqlCommand(sql);
|
|
command.Connection = connection;
|
|
|
|
foreach (var (key, o) in parameters)
|
|
{
|
|
command.Parameters.AddWithValue("@" + key, o);
|
|
}
|
|
|
|
await command.PrepareAsync(token).ConfigureAwait(false);
|
|
|
|
var reader = await command.ExecuteReaderAsync(token).ConfigureAwait(false);
|
|
while (await reader.ReadAsync(token).ConfigureAwait(false))
|
|
{
|
|
IDictionary<string, object> eo = new ExpandoObject();
|
|
for (var index = 0; index < reader.FieldCount; index++)
|
|
{
|
|
if (!eo.ContainsKey(reader.GetName(index)))
|
|
{
|
|
eo.Add(reader.GetName(index), await reader.IsDBNullAsync(index, token) ? null : reader.GetValue(index));
|
|
}
|
|
}
|
|
|
|
value.Add(eo);
|
|
}
|
|
|
|
await reader.CloseAsync().ConfigureAwait(false);
|
|
await reader.DisposeAsync().ConfigureAwait(false);
|
|
await command.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
catch
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
return value;
|
|
}
|
|
|
|
public async Task<List<IDictionary<string, object>>> ExecuteByOrderedParametersAsync(string sql,
|
|
List<object> parameters, CancellationToken token = default)
|
|
{
|
|
var value = new List<IDictionary<string, object>>();
|
|
var connection = new NpgsqlConnection(connectionString);
|
|
try
|
|
{
|
|
await connection.OpenAsync(token).ConfigureAwait(false);
|
|
|
|
var command = new NpgsqlCommand(sql);
|
|
command.Connection = connection;
|
|
|
|
for (var i = 0; i < parameters.Count; i++)
|
|
{
|
|
command.Parameters.AddWithValue("@" + (i + 1), parameters[i]);
|
|
}
|
|
|
|
await command.PrepareAsync(token).ConfigureAwait(false);
|
|
|
|
var reader = await command.ExecuteReaderAsync(token).ConfigureAwait(false);
|
|
while (await reader.ReadAsync(token).ConfigureAwait(false))
|
|
{
|
|
IDictionary<string, object> eo = new ExpandoObject();
|
|
for (var index = 0; index < reader.FieldCount; index++)
|
|
{
|
|
if (!eo.ContainsKey(reader.GetName(index)))
|
|
{
|
|
eo.Add(reader.GetName(index), await reader.IsDBNullAsync(index, token) ? null : reader.GetValue(index));
|
|
}
|
|
}
|
|
|
|
value.Add(eo);
|
|
}
|
|
|
|
await reader.CloseAsync().ConfigureAwait(false);
|
|
await reader.DisposeAsync().ConfigureAwait(false);
|
|
await command.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
catch
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
await connection.CloseAsync().ConfigureAwait(false);
|
|
await connection.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
return value;
|
|
}
|
|
}
|
|
}
|