897 lines
40 KiB
C#
897 lines
40 KiB
C#
using Dapper;
|
|
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using Npgsql;
|
|
using PluginBuilder.DataModels;
|
|
using PluginBuilder.JsonConverters;
|
|
using PluginBuilder.Services;
|
|
using PluginBuilder.ViewModels;
|
|
using PluginBuilder.ViewModels.Admin;
|
|
using PluginBuilder.ViewModels.Plugin;
|
|
|
|
namespace PluginBuilder.Util.Extensions;
|
|
|
|
public static class NpgsqlConnectionExtensions
|
|
{
|
|
#region Methods relating to plugin settings
|
|
|
|
public static async Task<PluginSettings?> GetSettings(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
var r = await connection.QueryFirstOrDefaultAsync<string>("SELECT settings FROM plugins WHERE slug=@pluginSlug",
|
|
new { pluginSlug = pluginSlug.ToString() });
|
|
if (r is null)
|
|
return null;
|
|
return JsonConvert.DeserializeObject<PluginSettings>(r, CamelCaseSerializerSettings.Instance);
|
|
}
|
|
|
|
public static async Task<bool> SetPluginSettings(this NpgsqlConnection connection, PluginSlug pluginSlug, PluginSettings? pluginSettings,
|
|
PluginVisibilityEnum? visibility = null)
|
|
{
|
|
var settingsJson = "{}";
|
|
if (pluginSettings != null)
|
|
settingsJson = JsonConvert.SerializeObject(pluginSettings, CamelCaseSerializerSettings.Instance);
|
|
|
|
var sql = "UPDATE plugins SET settings = @settings::JSONB WHERE slug = @pluginSlug";
|
|
if (visibility != null)
|
|
sql = "UPDATE plugins SET settings = @settings::JSONB, visibility = @visibility::plugin_visibility_enum WHERE slug = @pluginSlug";
|
|
|
|
var affectedRows = await connection.ExecuteAsync(sql,
|
|
new { pluginSlug = pluginSlug.ToString(), settings = settingsJson, visibility = visibility?.ToString().ToLowerInvariant() });
|
|
return affectedRows == 1;
|
|
}
|
|
|
|
public static async Task<PluginViewModel?> GetPluginDetails(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
return await connection.QueryFirstOrDefaultAsync<PluginViewModel>(
|
|
"SELECT slug AS \"PluginSlug\", identifier, settings::text AS settings, visibility::text AS visibility FROM plugins WHERE slug=@pluginSlug",
|
|
new { pluginSlug = pluginSlug.ToString() });
|
|
}
|
|
|
|
public static async Task<bool> IsPluginTitleInUse(this NpgsqlConnection conn, string pluginTitle, PluginSlug? currentSlug = null)
|
|
{
|
|
const string sql = """
|
|
SELECT slug
|
|
FROM plugins
|
|
WHERE LOWER(settings->>'pluginTitle') = LOWER(@Title)
|
|
AND (@CurrentSlug IS NULL OR slug <> @CurrentSlug)
|
|
LIMIT 1
|
|
""";
|
|
|
|
var existingSlug = await conn.QueryFirstOrDefaultAsync<string>(sql, new
|
|
{
|
|
Title = pluginTitle.Trim(),
|
|
CurrentSlug = currentSlug?.ToString()
|
|
});
|
|
|
|
return existingSlug is not null;
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region Methods relating to User account settings
|
|
|
|
public static async Task SetAccountDetailSettings(this NpgsqlConnection connection, AccountSettings accountSettings, string userId)
|
|
{
|
|
await connection.ExecuteAsync(
|
|
"UPDATE \"AspNetUsers\" SET \"AccountDetail\" = @settings::JSONB WHERE \"Id\" = @userId",
|
|
new { userId, settings = JsonConvert.SerializeObject(accountSettings, CamelCaseSerializerSettings.Instance) }
|
|
);
|
|
}
|
|
|
|
public static async Task<AccountSettings?> GetAccountDetailSettings(this NpgsqlConnection connection, string userId)
|
|
{
|
|
var accountDetail = await connection.QueryFirstOrDefaultAsync<string>(
|
|
"SELECT \"AccountDetail\" FROM \"AspNetUsers\" WHERE \"Id\" = @userId",
|
|
new { userId }
|
|
);
|
|
if (accountDetail is null)
|
|
return null;
|
|
return JsonConvert.DeserializeObject<AccountSettings>(accountDetail, CamelCaseSerializerSettings.Instance);
|
|
}
|
|
|
|
public static async Task<string?> GetUserIdByNpubAsync(this NpgsqlConnection conn, string npub)
|
|
{
|
|
const string sql = """
|
|
SELECT "Id"
|
|
FROM "AspNetUsers"
|
|
WHERE lower(trim("AccountDetail"->'nostr'->>'npub')) = lower(trim(@npub))
|
|
LIMIT 1;
|
|
""";
|
|
return await conn.ExecuteScalarAsync<string?>(sql, new { npub });
|
|
}
|
|
|
|
public static async Task VerifyGithubAccount(this NpgsqlConnection connection, string userId, string gistUrl)
|
|
{
|
|
await connection.ExecuteAsync(
|
|
"UPDATE \"AspNetUsers\" SET \"GithubGistUrl\" = @gistUrl WHERE \"Id\" = @userId",
|
|
new { userId, gistUrl }
|
|
);
|
|
}
|
|
|
|
public static async Task<bool> IsGithubAccountVerified(this NpgsqlConnection connection, string userId)
|
|
{
|
|
var githubGistUrl = await connection.QuerySingleOrDefaultAsync<string>(
|
|
"SELECT \"GithubGistUrl\" FROM \"AspNetUsers\" WHERE \"Id\" = @userId",
|
|
new { userId }
|
|
);
|
|
return !string.IsNullOrEmpty(githubGistUrl);
|
|
}
|
|
|
|
public static async Task<bool> IsSocialAccountsVerified(this NpgsqlConnection connection, string userId)
|
|
{
|
|
var result = await connection.QuerySingleOrDefaultAsync<(string GithubGistUrl, string AccountDetail)>(
|
|
"SELECT \"GithubGistUrl\", \"AccountDetail\" FROM \"AspNetUsers\" WHERE \"Id\" = @userId AND \"EmailConfirmed\" = true",
|
|
new { userId });
|
|
|
|
if (string.IsNullOrEmpty(result.AccountDetail) || string.IsNullOrEmpty(result.GithubGistUrl))
|
|
return false;
|
|
|
|
var accountSettings = SafeJson.Deserialize<AccountSettings>(result.AccountDetail);
|
|
return !string.IsNullOrWhiteSpace(accountSettings?.Nostr?.Npub);
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region Methods relating to plugin owners/users
|
|
|
|
public static async Task<bool> UserOwnsPlugin(this NpgsqlConnection connection, string userId, PluginSlug pluginSlug)
|
|
{
|
|
return await connection.QuerySingleAsync<bool>("SELECT EXISTS (SELECT * FROM users_plugins WHERE user_id=@userId AND plugin_slug=@pluginSlug);",
|
|
new { pluginSlug = pluginSlug.ToString(), userId });
|
|
}
|
|
|
|
public static async Task<IEnumerable<string>> RetrievePluginUserIds(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
return await connection.QueryAsync<string>("SELECT user_id FROM users_plugins WHERE plugin_slug=@pluginSlug;",
|
|
new { pluginSlug = pluginSlug.ToString() });
|
|
}
|
|
|
|
public static async Task<string?> RetrievePluginPrimaryOwner(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
return await connection.QueryFirstOrDefaultAsync<string>(
|
|
"SELECT user_id FROM users_plugins WHERE plugin_slug=@pluginSlug AND is_primary_owner IS TRUE;",
|
|
new { pluginSlug = pluginSlug.ToString() });
|
|
}
|
|
|
|
public static async Task<bool> AssignPluginPrimaryOwner(this NpgsqlConnection connection, PluginSlug pluginSlug, string userId)
|
|
{
|
|
await using var tx = await connection.BeginTransactionAsync();
|
|
await connection.ExecuteAsync("UPDATE users_plugins SET is_primary_owner = FALSE WHERE plugin_slug = @pluginSlug AND is_primary_owner IS TRUE;",
|
|
new { pluginSlug = pluginSlug.ToString() }, tx);
|
|
|
|
var updated = await connection.ExecuteAsync(@"UPDATE users_plugins SET is_primary_owner = TRUE WHERE plugin_slug = @pluginSlug AND user_id = @userId;",
|
|
new { pluginSlug = pluginSlug.ToString(), userId }, tx);
|
|
|
|
if (updated != 1)
|
|
return false;
|
|
|
|
await tx.CommitAsync();
|
|
return true;
|
|
}
|
|
|
|
public static async Task AddUserPlugin(this NpgsqlConnection connection, PluginSlug pluginSlug, string userId, bool isPrimary = false)
|
|
{
|
|
await connection.ExecuteAsync(
|
|
"INSERT INTO users_plugins (user_id, plugin_slug, is_primary_owner) VALUES (@userId, @pluginSlug, @isPrimary) ON CONFLICT DO NOTHING",
|
|
new { pluginSlug = pluginSlug.ToString(), userId, isPrimary });
|
|
}
|
|
|
|
public static Task<int> RemovePluginOwner(this NpgsqlConnection connection, PluginSlug pluginSlug, string userId)
|
|
{
|
|
return connection.ExecuteAsync("DELETE FROM users_plugins WHERE plugin_slug = @pluginSlug AND user_id = @userId;",
|
|
new { pluginSlug = pluginSlug.ToString(), userId });
|
|
}
|
|
|
|
public static async Task<List<OwnerVm>> GetPluginOwners(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
const string sql = """
|
|
SELECT
|
|
u."Id" AS "UserId",
|
|
up.is_primary_owner AS "IsPrimary",
|
|
u."Email",
|
|
u."AccountDetail",
|
|
u."EmailConfirmed"
|
|
FROM users_plugins up
|
|
JOIN "AspNetUsers" u ON u."Id" = up.user_id
|
|
WHERE up.plugin_slug = @slug
|
|
ORDER BY up.is_primary_owner DESC, COALESCE(u."Email", u."Id");
|
|
""";
|
|
|
|
var owners = await connection.QueryAsync<OwnerVm>(sql, new { slug = pluginSlug.ToString() });
|
|
return owners.ToList();
|
|
}
|
|
|
|
public static async Task<List<(string UserId, bool IsPrimary)>> GetPluginOwnersForUpdate(
|
|
this NpgsqlConnection connection,
|
|
PluginSlug pluginSlug,
|
|
NpgsqlTransaction tx)
|
|
{
|
|
const string sql = """
|
|
SELECT user_id AS "UserId", is_primary_owner AS "IsPrimary"
|
|
FROM users_plugins
|
|
WHERE plugin_slug = @slug
|
|
FOR UPDATE;
|
|
""";
|
|
|
|
var owners = await connection.QueryAsync<(string UserId, bool IsPrimary)>(
|
|
sql,
|
|
new { slug = pluginSlug.ToString() },
|
|
tx);
|
|
|
|
return owners.ToList();
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region Methods relating to plugin and builds
|
|
|
|
public static async Task<bool> NewPlugin(this NpgsqlConnection connection, PluginSlug pluginSlug, string userId)
|
|
{
|
|
var count = await connection.ExecuteAsync(
|
|
"INSERT INTO plugins (slug, added_at) VALUES (@id, @added_at) ON CONFLICT DO NOTHING;",
|
|
new { id = pluginSlug.ToString(), added_at = DateTimeOffset.UtcNow });
|
|
|
|
if (count != 1)
|
|
return false;
|
|
|
|
await connection.AddUserPlugin(pluginSlug, userId, true);
|
|
return true;
|
|
}
|
|
|
|
public static async Task<bool> DeletePlugin(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
var affectedRows = await connection.ExecuteAsync(
|
|
"DELETE FROM plugins WHERE slug = @pluginSlug;",
|
|
new { pluginSlug = pluginSlug.ToString() });
|
|
|
|
return affectedRows == 1;
|
|
}
|
|
|
|
public static async Task UpdateBuild(this NpgsqlConnection connection, FullBuildId fullBuildId, BuildStates newState, JObject? buildInfo,
|
|
PluginManifest? manifestInfo = null, NpgsqlTransaction? tx = null)
|
|
{
|
|
await connection.ExecuteAsync(
|
|
"UPDATE builds " +
|
|
"SET state = @state, " +
|
|
"build_info=COALESCE(build_info || @build_info::JSONB, @build_info::JSONB, build_info), " +
|
|
"manifest_info=COALESCE(@manifest_info::JSONB, manifest_info) " +
|
|
"WHERE plugin_slug=@plugin_slug AND id=@buildId",
|
|
new
|
|
{
|
|
state = newState.ToEventName(),
|
|
build_info = buildInfo?.ToString(),
|
|
manifest_info = manifestInfo?.ToString(),
|
|
plugin_slug = fullBuildId.PluginSlug.ToString(),
|
|
buildId = fullBuildId.BuildId
|
|
}, tx);
|
|
}
|
|
|
|
public static async Task<bool> UpdateVersionReleaseStatus(this NpgsqlConnection connection, PluginSlug pluginSlug, string command, PluginVersion version,
|
|
SignatureProof? signatureProof = null)
|
|
{
|
|
var updated = await connection.ExecuteAsync(
|
|
"UPDATE versions SET pre_release = @preRelease, signatureproof = CASE WHEN @hasSignature THEN @signatureproof::JSONB WHEN @preRelease THEN NULL ELSE signatureproof END " +
|
|
"WHERE plugin_slug = @pluginSlug AND ver = @version",
|
|
new
|
|
{
|
|
signatureproof = signatureProof != null ? JsonConvert.SerializeObject(signatureProof, CamelCaseSerializerSettings.Instance) : null,
|
|
hasSignature = signatureProof != null,
|
|
pluginSlug = pluginSlug.ToString(),
|
|
version = version.VersionParts,
|
|
preRelease = command == "unrelease"
|
|
});
|
|
return updated == 1;
|
|
}
|
|
|
|
public static async Task<PluginSlug[]> GetPluginsByUserId(this NpgsqlConnection connection, string userId)
|
|
{
|
|
return (await connection.QueryAsync<string>(
|
|
"SELECT up.plugin_slug FROM users_plugins up " +
|
|
"JOIN plugins p ON up.plugin_slug=p.slug " +
|
|
"WHERE up.user_id=@userId;", new { userId }))
|
|
.Select(s => PluginSlug.Parse(s)).ToArray();
|
|
}
|
|
|
|
public static async Task<bool> EnsureIdentifierOwnership(this NpgsqlConnection connection, PluginSlug pluginSlug, string identifier)
|
|
{
|
|
var pluginIdentifier =
|
|
await connection.ExecuteScalarAsync<string?>("SELECT identifier FROM plugins WHERE slug=@pluginSlug", new { pluginSlug = pluginSlug.ToString() });
|
|
if (pluginIdentifier is not null)
|
|
return pluginIdentifier == identifier;
|
|
try
|
|
{
|
|
return await connection.ExecuteAsync("UPDATE plugins SET identifier = @identifier WHERE slug=@pluginSlug AND identifier IS NULL",
|
|
new { pluginSlug = pluginSlug.ToString(), identifier }) == 1;
|
|
}
|
|
catch (PostgresException ex) when (ex.SqlState == PostgresErrorCodes.UniqueViolation)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public static async Task<PluginSlug?> ResolvePluginSlug(this DBConnectionFactory connectionFactory, PluginSelector selector)
|
|
{
|
|
if (selector is PluginSelectorBySlug s)
|
|
return s.PluginSlug;
|
|
await using var conn = await connectionFactory.Open();
|
|
return await conn.ResolvePluginSlug(selector);
|
|
}
|
|
|
|
public static async Task<PluginSlug?> ResolvePluginSlug(this NpgsqlConnection connection, PluginSelector selector)
|
|
{
|
|
if (selector is PluginSelectorBySlug s)
|
|
return s.PluginSlug;
|
|
if (selector is PluginSelectorByIdentifier i)
|
|
{
|
|
var slug = await connection.ExecuteScalarAsync<string?>("SELECT slug FROM plugins WHERE identifier=@identifier",
|
|
new { identifier = i.Identifier });
|
|
if (slug is null)
|
|
return null;
|
|
if (PluginSlug.TryParse(slug, out var o))
|
|
return o;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public static Task InsertEvent(this NpgsqlConnection connection, string evtType, JObject data)
|
|
{
|
|
return connection.ExecuteAsync("INSERT INTO evts VALUES (@evtType, @evt::JSONB);", new { evtType, evt = data.ToString() });
|
|
}
|
|
|
|
public static async Task<bool> SetVersionBuild(this NpgsqlConnection connection, FullBuildId fullBuildId, PluginVersion version,
|
|
PluginVersion? minBTCPayVersion, PluginVersion? maxBTCPayVersion, bool preRelease)
|
|
{
|
|
minBTCPayVersion ??= PluginVersion.Zero;
|
|
|
|
return await connection.ExecuteAsync(
|
|
"INSERT INTO versions AS v (plugin_slug, ver, build_id, btcpay_min_ver, btcpay_max_ver, pre_release) " +
|
|
"VALUES (@plugin_slug, @ver, @build_id, @btcpay_min_ver, @btcpay_max_ver, @pre_release) " +
|
|
"ON CONFLICT (plugin_slug, ver) DO UPDATE SET build_id = @build_id, " +
|
|
"btcpay_min_ver = CASE WHEN v.btcpay_min_ver_override_enabled THEN v.btcpay_min_ver ELSE @btcpay_min_ver END, " +
|
|
"btcpay_max_ver = CASE WHEN v.btcpay_max_ver_override_enabled THEN v.btcpay_max_ver ELSE @btcpay_max_ver END, " +
|
|
"pre_release = @pre_release " +
|
|
"WHERE v.pre_release IS TRUE AND (" +
|
|
"v.build_id != @build_id " +
|
|
"OR (NOT v.btcpay_min_ver_override_enabled AND v.btcpay_min_ver != @btcpay_min_ver) " +
|
|
"OR (NOT v.btcpay_max_ver_override_enabled AND v.btcpay_max_ver IS DISTINCT FROM @btcpay_max_ver) " +
|
|
"OR @pre_release IS FALSE);",
|
|
new
|
|
{
|
|
plugin_slug = fullBuildId.PluginSlug.ToString(),
|
|
ver = version.VersionParts,
|
|
build_id = fullBuildId.BuildId,
|
|
btcpay_min_ver = minBTCPayVersion.VersionParts,
|
|
btcpay_max_ver = maxBTCPayVersion?.VersionParts,
|
|
pre_release = preRelease
|
|
}) == 1;
|
|
}
|
|
|
|
public static async Task<long> NewBuild(this NpgsqlConnection connection, PluginSlug pluginSlug, PluginBuildParameters buildParameters,
|
|
FirstBuildEvent? firstBuildEvent = null)
|
|
{
|
|
BuildInfo bi = new()
|
|
{
|
|
BuildConfig = buildParameters.BuildConfig,
|
|
GitRepository = buildParameters.GitRepository,
|
|
GitRef = buildParameters.GitRef,
|
|
PluginDir = buildParameters.PluginDirectory
|
|
};
|
|
var buildId = await connection.ExecuteScalarAsync<long>("" +
|
|
"WITH cte AS " +
|
|
"( " +
|
|
" INSERT INTO builds_ids AS bi VALUES (@plugin_slug, 0)" +
|
|
" ON CONFLICT (plugin_slug) DO UPDATE SET curr_id=bi.curr_id+1 " +
|
|
" RETURNING curr_id " +
|
|
") " +
|
|
"INSERT INTO builds (plugin_slug, id, state, build_info) VALUES (@plugin_slug, (SELECT * FROM cte), @state, @buildInfo::JSONB) RETURNING id;",
|
|
new
|
|
{
|
|
plugin_slug = pluginSlug.ToString(),
|
|
state = BuildStates.Queued.ToEventName(),
|
|
buildInfo = bi.ToString()
|
|
});
|
|
return buildId;
|
|
}
|
|
|
|
public static async Task<long> GetLatestPluginBuildNumber(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
return await connection.ExecuteScalarAsync<long>("SELECT curr_id FROM builds_ids WHERE plugin_slug = @plugin_slug",
|
|
new { plugin_slug = pluginSlug.ToString() });
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region Methods related to getting / setting settings in the DB
|
|
|
|
public static Task<IEnumerable<(string key, string value)>> SettingsGetAllAsync(this NpgsqlConnection connection)
|
|
{
|
|
var query = "SELECT key, value FROM settings";
|
|
return connection.QueryAsync<(string key, string value)>(query);
|
|
}
|
|
|
|
public static Task<string?> SettingsGetAsync(this NpgsqlConnection connection, string key)
|
|
{
|
|
var query = "SELECT value FROM settings WHERE key = @key";
|
|
return connection.QuerySingleOrDefaultAsync<string>(query, new { key });
|
|
}
|
|
|
|
public static Task<int> SettingsSetAsync(this NpgsqlConnection connection, string key, string value)
|
|
{
|
|
var query = """
|
|
INSERT INTO settings(key, value)
|
|
VALUES(@key, @value)
|
|
ON CONFLICT (key) DO UPDATE
|
|
SET value = EXCLUDED.value
|
|
""";
|
|
return connection.ExecuteAsync(query, new { key, value });
|
|
}
|
|
|
|
public static Task<int> SettingsDeleteAsync(this NpgsqlConnection connection, string key)
|
|
{
|
|
var query = """
|
|
DELETE FROM settings
|
|
WHERE key = @key
|
|
""";
|
|
return connection.ExecuteAsync(query, new { key });
|
|
}
|
|
|
|
public static async Task SettingsInitialize(this NpgsqlConnection connection)
|
|
{
|
|
var query = "SELECT key, value FROM settings";
|
|
var result = (await connection.QueryAsync<(string key, string value)>(query)).ToList();
|
|
if (result.All(r => r.key != SettingsKeys.FirstPluginBuildReviewers))
|
|
await connection.ExecuteAsync("INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.FirstPluginBuildReviewers, value = "" });
|
|
|
|
if (result.All(r => r.key != SettingsKeys.VerifiedEmailForPluginPublish))
|
|
await connection.ExecuteAsync("INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.VerifiedEmailForPluginPublish, value = "true" });
|
|
if (result.All(r => r.key != SettingsKeys.VerifiedEmailForLogin))
|
|
await connection.ExecuteAsync("INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.VerifiedEmailForLogin, value = "true" });
|
|
|
|
if (result.All(r => r.key != SettingsKeys.VerifiedGithub))
|
|
await connection.ExecuteAsync("INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.VerifiedGithub, value = "false" });
|
|
|
|
if (result.All(r => r.key != SettingsKeys.VerifiedNostr))
|
|
await connection.ExecuteAsync(
|
|
"INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.VerifiedNostr, value = "false" });
|
|
|
|
if (result.All(r => r.key != SettingsKeys.NostrRelays))
|
|
{
|
|
var json = JsonConvert.SerializeObject(NostrService.DefaultRelays);
|
|
await connection.ExecuteAsync(
|
|
"INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.NostrRelays, value = json });
|
|
}
|
|
|
|
if (result.All(r => r.key != SettingsKeys.RateLimitPermitLimit))
|
|
await connection.ExecuteAsync("INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.RateLimitPermitLimit, value = "30" });
|
|
|
|
if (result.All(r => r.key != SettingsKeys.RateLimitWindowSeconds))
|
|
await connection.ExecuteAsync("INSERT INTO settings (key, value) VALUES (@key, @value)",
|
|
new { key = SettingsKeys.RateLimitWindowSeconds, value = "60" });
|
|
}
|
|
|
|
public static async Task<bool> GetVerifiedEmailForPluginPublishSetting(this NpgsqlConnection connection)
|
|
{
|
|
var settingValue = await connection.SettingsGetAsync(SettingsKeys.VerifiedEmailForPluginPublish);
|
|
return bool.TryParse(settingValue, out var result) && result;
|
|
}
|
|
|
|
public static async Task UpdateVerifiedEmailForPluginPublishSetting(this NpgsqlConnection connection, bool newValue)
|
|
{
|
|
var stringValue = newValue.ToString().ToLowerInvariant();
|
|
await connection.ExecuteAsync("UPDATE settings SET value = @Value WHERE key = @Key",
|
|
new { Value = stringValue, Key = SettingsKeys.VerifiedEmailForPluginPublish });
|
|
}
|
|
|
|
public static async Task<bool> GetVerifiedEmailForLoginSetting(this NpgsqlConnection connection)
|
|
{
|
|
var settingValue = await connection.SettingsGetAsync(SettingsKeys.VerifiedEmailForLogin);
|
|
return bool.TryParse(settingValue, out var result) && result;
|
|
}
|
|
|
|
public static Task<string?> GetFirstPluginBuildReviewersSetting(this NpgsqlConnection connection)
|
|
{
|
|
return connection.SettingsGetAsync(SettingsKeys.FirstPluginBuildReviewers);
|
|
}
|
|
|
|
public static async Task<bool> GetVerifiedGithubSetting(this NpgsqlConnection connection)
|
|
{
|
|
var v = await connection.SettingsGetAsync(SettingsKeys.VerifiedGithub);
|
|
return bool.TryParse(v, out var b) && b;
|
|
}
|
|
|
|
public static async Task<bool> GetVerifiedNostrSetting(this NpgsqlConnection connection)
|
|
{
|
|
var v = await connection.SettingsGetAsync(SettingsKeys.VerifiedNostr);
|
|
return bool.TryParse(v, out var b) && b;
|
|
}
|
|
|
|
public static async Task<string[]> GetNostrRelaysSetting(this NpgsqlConnection connection)
|
|
{
|
|
var raw = await connection.QueryFirstOrDefaultAsync<string>(
|
|
"SELECT value FROM settings WHERE key=@k LIMIT 1",
|
|
new { k = SettingsKeys.NostrRelays });
|
|
|
|
var relays = JsonConvert.DeserializeObject<string[]?>(raw ?? string.Empty);
|
|
return relays is { Length: > 0 } ? relays : NostrService.DefaultRelays.ToArray();
|
|
}
|
|
|
|
public static async Task<int> GetRateLimitPermitLimitSetting(this NpgsqlConnection connection)
|
|
{
|
|
var v = await connection.SettingsGetAsync(SettingsKeys.RateLimitPermitLimit);
|
|
return int.TryParse(v, out var limit) && limit > 0 ? limit : 30;
|
|
}
|
|
|
|
public static async Task<int> GetRateLimitWindowSecondsSetting(this NpgsqlConnection connection)
|
|
{
|
|
var v = await connection.SettingsGetAsync(SettingsKeys.RateLimitWindowSeconds);
|
|
return int.TryParse(v, out var seconds) && seconds > 0 ? seconds : 60;
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region Plugin Reviewers and reviews
|
|
|
|
public static Task UpsertPluginReview(this NpgsqlConnection connection, PluginReviewViewModel model)
|
|
{
|
|
const string sql = """
|
|
INSERT INTO plugin_reviews
|
|
(plugin_slug, rating, body, plugin_version, reviewer_id, created_at, updated_at)
|
|
VALUES
|
|
(@plugin_slug, @rating, NULLIF(@body,''), @plugin_version, @reviewer_id, NOW(), NOW())
|
|
ON CONFLICT (plugin_slug, reviewer_id)
|
|
DO UPDATE SET
|
|
rating = EXCLUDED.rating,
|
|
body = EXCLUDED.body,
|
|
plugin_version = EXCLUDED.plugin_version,
|
|
updated_at = NOW(),
|
|
helpful_voters = CASE
|
|
WHEN (plugin_reviews.rating IS DISTINCT FROM EXCLUDED.rating)
|
|
OR (COALESCE(plugin_reviews.body,'') IS DISTINCT FROM COALESCE(EXCLUDED.body,''))
|
|
THEN '{}'::jsonb
|
|
ELSE plugin_reviews.helpful_voters
|
|
END;
|
|
""";
|
|
|
|
return connection.ExecuteAsync(sql, new
|
|
{
|
|
plugin_slug = model.PluginSlug,
|
|
rating = model.Rating,
|
|
body = model.Body,
|
|
reviewer_id = model.ReviewerId,
|
|
plugin_version = model.PluginVersion
|
|
});
|
|
}
|
|
|
|
public static async Task<bool> DeleteReviewAsync(
|
|
this NpgsqlConnection conn,
|
|
PluginSlug pluginSlug,
|
|
long reviewId,
|
|
string userId,
|
|
bool isAdmin)
|
|
{
|
|
const string sql = """
|
|
DELETE FROM plugin_reviews pr
|
|
USING plugin_reviewers r
|
|
WHERE pr.id = @id
|
|
AND pr.plugin_slug = @slug
|
|
AND (
|
|
@isAdmin
|
|
OR (
|
|
pr.reviewer_id = r.id
|
|
AND r.user_id = @userId
|
|
)
|
|
);
|
|
""";
|
|
|
|
var rows = await conn.ExecuteAsync(sql, new
|
|
{
|
|
id = reviewId,
|
|
slug = pluginSlug.ToString(),
|
|
userId,
|
|
isAdmin
|
|
});
|
|
|
|
return rows > 0;
|
|
}
|
|
|
|
public static Task<bool?> GetReviewHelpfulVoteAsync(
|
|
this NpgsqlConnection conn,
|
|
PluginSlug pluginSlug,
|
|
long reviewId,
|
|
string userId)
|
|
{
|
|
const string sql = """
|
|
SELECT (helpful_voters ->> @userId)::boolean
|
|
FROM plugin_reviews
|
|
WHERE id = @id AND plugin_slug = @slug
|
|
""";
|
|
return conn.ExecuteScalarAsync<bool?>(sql, new
|
|
{
|
|
id = reviewId,
|
|
slug = pluginSlug.ToString(),
|
|
userId
|
|
});
|
|
}
|
|
|
|
public static async Task<bool> RemoveReviewHelpfulVoteAsync(
|
|
this NpgsqlConnection conn,
|
|
PluginSlug pluginSlug,
|
|
long reviewId,
|
|
string userId)
|
|
{
|
|
const string sql = """
|
|
UPDATE plugin_reviews pr
|
|
SET helpful_voters = pr.helpful_voters - @userId
|
|
FROM plugin_reviewers r
|
|
WHERE pr.id = @id
|
|
AND pr.plugin_slug = @slug
|
|
AND pr.reviewer_id = r.id
|
|
AND (r.user_id IS NULL OR r.user_id <> @userId);
|
|
""";
|
|
var rows = await conn.ExecuteAsync(sql, new
|
|
{
|
|
id = reviewId,
|
|
slug = pluginSlug.ToString(),
|
|
userId
|
|
});
|
|
return rows > 0;
|
|
}
|
|
|
|
public static async Task<bool> UpsertReviewHelpfulVoteAsync(
|
|
this NpgsqlConnection conn,
|
|
PluginSlug pluginSlug,
|
|
long reviewId,
|
|
string userId,
|
|
bool isHelpful)
|
|
{
|
|
const string sql = """
|
|
UPDATE plugin_reviews pr
|
|
SET helpful_voters = jsonb_set(
|
|
pr.helpful_voters,
|
|
ARRAY[@userId],
|
|
to_jsonb(@isHelpful),
|
|
true
|
|
)
|
|
FROM plugin_reviewers r
|
|
WHERE pr.id = @id
|
|
AND pr.plugin_slug = @slug
|
|
AND pr.reviewer_id = r.id
|
|
AND (r.user_id IS NULL OR r.user_id <> @userId);
|
|
""";
|
|
var rows = await conn.ExecuteAsync(sql, new
|
|
{
|
|
id = reviewId,
|
|
slug = pluginSlug.ToString(),
|
|
userId,
|
|
isHelpful
|
|
});
|
|
return rows > 0;
|
|
}
|
|
|
|
public static async Task<long> CreateOrUpdatePluginReviewer(this NpgsqlConnection connection, ImportReviewViewModel reviewModel)
|
|
{
|
|
// NOTE: This method intentionally allows matching by EITHER user_id OR profile_url.
|
|
// This is a FEATURE, not a bug - it enables "review migration" where:
|
|
// 1. External reviews are imported first (matched by profile_url, user_id is NULL)
|
|
// 2. When that person registers as a system user, admin can link them (matched by profile_url)
|
|
// 3. The user_id gets populated, claiming ownership of their previously imported reviews
|
|
// This allows users to "claim" their external reviews when they join the platform.
|
|
const string updateSql = """
|
|
UPDATE plugin_reviewers p
|
|
SET
|
|
user_id = COALESCE(p.user_id, @user_id),
|
|
username = @username,
|
|
source = @source,
|
|
profile_url = @profile_url,
|
|
avatar_url = @avatar_url,
|
|
updated_at = NOW()
|
|
WHERE p.id = (
|
|
SELECT id
|
|
FROM plugin_reviewers
|
|
WHERE
|
|
(@user_id IS NOT NULL AND user_id = @user_id)
|
|
OR (@profile_url IS NOT NULL AND profile_url = @profile_url)
|
|
ORDER BY
|
|
CASE
|
|
WHEN @user_id IS NOT NULL AND user_id = @user_id THEN 0
|
|
WHEN profile_url = @profile_url THEN 1
|
|
ELSE 2
|
|
END
|
|
LIMIT 1
|
|
)
|
|
RETURNING id;
|
|
""";
|
|
|
|
var param = new
|
|
{
|
|
user_id = reviewModel.SelectedUserId,
|
|
username = reviewModel.ReviewerName,
|
|
source = reviewModel.LinkExistingUser ? "system" : reviewModel.Source.ToString().ToLower(),
|
|
profile_url = reviewModel.ReviewerProfileUrl,
|
|
avatar_url = reviewModel.ReviewerAvatarUrl
|
|
};
|
|
|
|
var updatedId = await connection.ExecuteScalarAsync<long?>(updateSql, param);
|
|
if (updatedId.HasValue)
|
|
return updatedId.Value;
|
|
|
|
const string insertSql = """
|
|
INSERT INTO plugin_reviewers (user_id, username, source, profile_url, avatar_url, created_at, updated_at)
|
|
VALUES (@user_id, @username, @source, @profile_url, @avatar_url, NOW(), NOW())
|
|
RETURNING id;
|
|
""";
|
|
|
|
return await connection.ExecuteScalarAsync<long>(insertSql, param);
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region Methods relating to plugin listing requests
|
|
|
|
public static async Task<int> CreateListingRequest(this NpgsqlConnection connection, PluginSlug pluginSlug, string releaseNote, string telegramMessage,
|
|
string userReviews, DateTimeOffset? announcementDate)
|
|
{
|
|
const string sql = """
|
|
INSERT INTO plugin_listing_requests (plugin_slug, release_note, telegram_verification_message, user_reviews, announcement_date, status, submitted_at)
|
|
VALUES (@pluginSlug, @releaseNote, @telegramMessage, @userReviews, @announcementDate, 'pending', CURRENT_TIMESTAMP)
|
|
RETURNING id
|
|
""";
|
|
|
|
return await connection.ExecuteScalarAsync<int>(sql, new
|
|
{
|
|
pluginSlug = pluginSlug.ToString(),
|
|
releaseNote,
|
|
telegramMessage,
|
|
userReviews,
|
|
announcementDate
|
|
});
|
|
}
|
|
|
|
public static async Task<PluginListingRequest?> GetListingRequest(this NpgsqlConnection connection, int requestId)
|
|
{
|
|
const string sql = """
|
|
SELECT id AS "Id",
|
|
plugin_slug AS "PluginSlug",
|
|
release_note AS "ReleaseNote",
|
|
telegram_verification_message AS "TelegramVerificationMessage",
|
|
user_reviews AS "UserReviews",
|
|
announcement_date AS "AnnouncementDate",
|
|
status AS "Status",
|
|
submitted_at AS "SubmittedAt",
|
|
reviewed_at AS "ReviewedAt",
|
|
reviewed_by AS "ReviewedBy",
|
|
rejection_reason AS "RejectionReason"
|
|
FROM plugin_listing_requests
|
|
WHERE id = @requestId
|
|
""";
|
|
|
|
return await connection.QueryFirstOrDefaultAsync<PluginListingRequest>(sql, new { requestId });
|
|
}
|
|
|
|
public static async Task<PluginListingRequest?> GetPendingListingRequestForPlugin(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
const string sql = """
|
|
SELECT id AS "Id",
|
|
plugin_slug AS "PluginSlug",
|
|
release_note AS "ReleaseNote",
|
|
telegram_verification_message AS "TelegramVerificationMessage",
|
|
user_reviews AS "UserReviews",
|
|
announcement_date AS "AnnouncementDate",
|
|
status AS "Status",
|
|
submitted_at AS "SubmittedAt",
|
|
reviewed_at AS "ReviewedAt",
|
|
reviewed_by AS "ReviewedBy",
|
|
rejection_reason AS "RejectionReason"
|
|
FROM plugin_listing_requests
|
|
WHERE plugin_slug = @pluginSlug AND status = 'pending'
|
|
ORDER BY submitted_at DESC
|
|
LIMIT 1
|
|
""";
|
|
|
|
return await connection.QueryFirstOrDefaultAsync<PluginListingRequest>(sql, new { pluginSlug = pluginSlug.ToString() });
|
|
}
|
|
|
|
public static async Task<bool> ApproveListingRequest(this NpgsqlConnection connection, int requestId, string reviewedBy)
|
|
{
|
|
const string sql = """
|
|
UPDATE plugin_listing_requests SET status = 'approved', reviewed_at = CURRENT_TIMESTAMP, reviewed_by = @reviewedBy
|
|
WHERE id = @requestId AND status = 'pending'
|
|
""";
|
|
|
|
var affected = await connection.ExecuteAsync(sql, new { requestId, reviewedBy });
|
|
return affected == 1;
|
|
}
|
|
|
|
public static async Task<bool> RejectListingRequest(this NpgsqlConnection connection, int requestId, string reviewedBy, string rejectionReason)
|
|
{
|
|
const string sql = """
|
|
UPDATE plugin_listing_requests
|
|
SET status = 'rejected', reviewed_at = CURRENT_TIMESTAMP, reviewed_by = @reviewedBy, rejection_reason = @rejectionReason
|
|
WHERE id = @requestId AND status = 'pending'
|
|
""";
|
|
|
|
var affected = await connection.ExecuteAsync(sql, new { requestId, reviewedBy, rejectionReason });
|
|
return affected == 1;
|
|
}
|
|
|
|
public static async Task<bool> HasPendingListingRequest(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
const string sql = """
|
|
SELECT EXISTS(SELECT 1 FROM plugin_listing_requests WHERE plugin_slug = @pluginSlug AND status = 'pending')
|
|
""";
|
|
|
|
return await connection.ExecuteScalarAsync<bool>(sql, new { pluginSlug = pluginSlug.ToString() });
|
|
}
|
|
|
|
public static async Task<PluginListingRequest?> GetLatestRejectedListingRequestForPlugin(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
const string sql = """
|
|
SELECT id AS "Id",
|
|
plugin_slug AS "PluginSlug",
|
|
release_note AS "ReleaseNote",
|
|
telegram_verification_message AS "TelegramVerificationMessage",
|
|
user_reviews AS "UserReviews",
|
|
announcement_date AS "AnnouncementDate",
|
|
status AS "Status",
|
|
submitted_at AS "SubmittedAt",
|
|
reviewed_at AS "ReviewedAt",
|
|
reviewed_by AS "ReviewedBy",
|
|
rejection_reason AS "RejectionReason"
|
|
FROM plugin_listing_requests
|
|
WHERE plugin_slug = @pluginSlug AND status = 'rejected'
|
|
ORDER BY submitted_at DESC
|
|
LIMIT 1
|
|
""";
|
|
|
|
return await connection.QueryFirstOrDefaultAsync<PluginListingRequest>(sql, new { pluginSlug = pluginSlug.ToString() });
|
|
}
|
|
|
|
public static async Task<List<PluginListingRequest>> GetAllListingRequestsForPlugin(this NpgsqlConnection connection, PluginSlug pluginSlug)
|
|
{
|
|
const string sql = """
|
|
SELECT id AS "Id",
|
|
plugin_slug AS "PluginSlug",
|
|
release_note AS "ReleaseNote",
|
|
telegram_verification_message AS "TelegramVerificationMessage",
|
|
user_reviews AS "UserReviews",
|
|
announcement_date AS "AnnouncementDate",
|
|
status AS "Status",
|
|
submitted_at AS "SubmittedAt",
|
|
reviewed_at AS "ReviewedAt",
|
|
reviewed_by AS "ReviewedBy",
|
|
rejection_reason AS "RejectionReason"
|
|
FROM plugin_listing_requests
|
|
WHERE plugin_slug = @pluginSlug
|
|
ORDER BY submitted_at DESC
|
|
""";
|
|
|
|
var results = await connection.QueryAsync<PluginListingRequest>(sql, new { pluginSlug = pluginSlug.ToString() });
|
|
return results.ToList();
|
|
}
|
|
|
|
public static async Task<int> GetPendingListingRequestsCount(this NpgsqlConnection connection)
|
|
{
|
|
const string sql = """
|
|
SELECT COUNT(*) FROM plugin_listing_requests WHERE status = 'pending'
|
|
""";
|
|
|
|
return await connection.ExecuteScalarAsync<int>(sql);
|
|
}
|
|
|
|
#endregion
|
|
}
|