From f9aa02ec1a4c24e80a206857d4f68198bb027bb4 Mon Sep 17 00:00:00 2001
From: crupest <crupest@outlook.com>
Date: Mon, 11 Nov 2024 01:12:29 +0800
Subject: HALF WORK: 2024.12.19
Re-organize file structure.
---
.../docker/crupest-api/CrupestApi/.dockerignore | 2 +
dropped/docker/crupest-api/CrupestApi/.gitignore | 4 +
.../Crud/ColumnTypeInfoTest.cs | 39 ++
.../Crud/CrudIntegratedTest.cs | 200 +++++++
.../Crud/CrudServiceTest.cs | 77 +++
.../Crud/SqlCompareHelper.cs | 85 +++
.../CrupestApi.Commons.Tests/Crud/TableInfoTest.cs | 35 ++
.../CrupestApi.Commons.Tests/Crud/TestEntity.cs | 23 +
.../CrupestApi.Commons.Tests.csproj | 29 +
.../CrupestApi/CrupestApi.Commons.Tests/Usings.cs | 1 +
.../CrupestApi/CrupestApi.Commons/Config.cs | 23 +
.../CrupestApi.Commons/Crud/ColumnInfo.cs | 236 ++++++++
.../CrupestApi.Commons/Crud/ColumnMetadata.cs | 188 ++++++
.../CrupestApi.Commons/Crud/ColumnTypeInfo.cs | 218 +++++++
.../CrupestApi.Commons/Crud/CrudService.cs | 132 +++++
.../Crud/CrudServiceCollectionExtensions.cs | 34 ++
.../Crud/CrudWebApplicationExtensions.cs | 101 ++++
.../CrupestApi.Commons/Crud/DbConnectionFactory.cs | 75 +++
.../CrupestApi.Commons/Crud/DbNullValue.cs | 9 +
.../CrupestApi.Commons/Crud/EntityJsonHelper.cs | 206 +++++++
.../CrupestApi/CrupestApi.Commons/Crud/IClause.cs | 24 +
.../CrupestApi.Commons/Crud/InsertClause.cs | 77 +++
.../Crud/Migrations/DatabaseMigrator.cs | 44 ++
.../Crud/Migrations/SqliteDatabaseMigrator.cs | 175 ++++++
.../CrupestApi.Commons/Crud/OrderByClause.cs | 50 ++
.../CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs | 73 +++
.../CrupestApi/CrupestApi.Commons/Crud/README.md | 47 ++
.../CrupestApi.Commons/Crud/TableInfo.cs | 628 +++++++++++++++++++++
.../CrupestApi.Commons/Crud/UpdateClause.cs | 77 +++
.../CrupestApi.Commons/Crud/UserException.cs | 15 +
.../CrupestApi.Commons/Crud/WhereClause.cs | 182 ++++++
.../CrupestApi.Commons/CrupestApi.Commons.csproj | 16 +
.../CrupestApi.Commons/EntityNotExistException.cs | 8 +
.../CrupestApi.Commons/HttpContextExtensions.cs | 113 ++++
.../CrupestApi.Commons/Secrets/ISecretService.cs | 8 +
.../CrupestApi.Commons/Secrets/SecretInfo.cs | 48 ++
.../CrupestApi.Commons/Secrets/SecretService.cs | 48 ++
.../Secrets/SecretServiceCollectionExtensions.cs | 12 +
.../CrupestApi.Commons/Secrets/SecretsConstants.cs | 6 +
.../CrupestApi.Files/CrupestApi.Files.csproj | 20 +
.../CrupestApi/CrupestApi.Files/FilesService.cs | 6 +
.../CrupestApi.Secrets/CrupestApi.Secrets.csproj | 20 +
.../CrupestApi.Secrets/SecretsExtensions.cs | 19 +
.../CrupestApi.Todos/CrupestApi.Todos.csproj | 15 +
.../CrupestApi.Todos/TodosConfiguration.cs | 14 +
.../CrupestApi/CrupestApi.Todos/TodosService.cs | 163 ++++++
.../TodosServiceCollectionExtensions.cs | 21 +
.../TodosWebApplicationExtensions.cs | 32 ++
.../docker/crupest-api/CrupestApi/CrupestApi.sln | 46 ++
.../CrupestApi/CrupestApi/CrupestApi.csproj | 17 +
.../crupest-api/CrupestApi/CrupestApi/Program.cs | 24 +
.../CrupestApi/Properties/launchSettings.json | 15 +
.../CrupestApi/CrupestApi/appsettings.json | 8 +
dropped/docker/crupest-api/Dockerfile | 13 +
54 files changed, 3801 insertions(+)
create mode 100644 dropped/docker/crupest-api/CrupestApi/.dockerignore
create mode 100644 dropped/docker/crupest-api/CrupestApi/.gitignore
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/ColumnTypeInfoTest.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudIntegratedTest.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/SqlCompareHelper.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TableInfoTest.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TestEntity.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/CrupestApi.Commons.Tests.csproj
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Usings.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Config.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/EntityJsonHelper.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UserException.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/EntityNotExistException.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/HttpContextExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/ISecretService.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretInfo.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretServiceCollectionExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretsConstants.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/CrupestApi.Files.csproj
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/FilesService.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/CrupestApi.Secrets.csproj
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/CrupestApi.Todos.csproj
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosConfiguration.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosService.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosServiceCollectionExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosWebApplicationExtensions.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi.sln
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi/CrupestApi.csproj
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi/Program.cs
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi/Properties/launchSettings.json
create mode 100644 dropped/docker/crupest-api/CrupestApi/CrupestApi/appsettings.json
create mode 100644 dropped/docker/crupest-api/Dockerfile
(limited to 'dropped/docker')
diff --git a/dropped/docker/crupest-api/CrupestApi/.dockerignore b/dropped/docker/crupest-api/CrupestApi/.dockerignore
new file mode 100644
index 0000000..f1c182d
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/.dockerignore
@@ -0,0 +1,2 @@
+*/obj
+*/bin
diff --git a/dropped/docker/crupest-api/CrupestApi/.gitignore b/dropped/docker/crupest-api/CrupestApi/.gitignore
new file mode 100644
index 0000000..371ea59
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/.gitignore
@@ -0,0 +1,4 @@
+.vs
+obj
+bin
+dev-config.json
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/ColumnTypeInfoTest.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/ColumnTypeInfoTest.cs
new file mode 100644
index 0000000..b9ec03e
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/ColumnTypeInfoTest.cs
@@ -0,0 +1,39 @@
+using System.Data;
+
+namespace CrupestApi.Commons.Crud.Tests;
+
+public class ColumnTypeInfoTest
+{
+ private ColumnTypeProvider _provider = new ColumnTypeProvider();
+
+ [Theory]
+ [InlineData(typeof(int), DbType.Int32, 123)]
+ [InlineData(typeof(long), DbType.Int64, 456)]
+ [InlineData(typeof(sbyte), DbType.SByte, 789)]
+ [InlineData(typeof(short), DbType.Int16, 101)]
+ [InlineData(typeof(float), DbType.Single, 1.0f)]
+ [InlineData(typeof(double), DbType.Double, 1.0)]
+ [InlineData(typeof(string), DbType.String, "Hello world!")]
+ [InlineData(typeof(byte[]), DbType.Binary, new byte[] { 1, 2, 3 })]
+ public void BasicColumnTypeTest(Type type, DbType dbType, object? value)
+ {
+ var typeInfo = _provider.Get(type);
+ Assert.True(typeInfo.IsSimple);
+ Assert.Equal(dbType, typeInfo.DbType);
+ Assert.Equal(value, typeInfo.ConvertFromDatabase(value));
+ Assert.Equal(value, typeInfo.ConvertToDatabase(value));
+ }
+
+ [Fact]
+ public void DateTimeColumnTypeTest()
+ {
+ var dateTimeColumnTypeInfo = _provider.Get(typeof(DateTime));
+ Assert.Equal(typeof(DateTime), dateTimeColumnTypeInfo.ClrType);
+ Assert.Equal(typeof(string), dateTimeColumnTypeInfo.DatabaseClrType);
+
+ var dateTime = new DateTime(2000, 1, 1, 0, 0, 0, DateTimeKind.Utc);
+ var dateTimeString = "2000-01-01T00:00:00Z";
+ Assert.Equal(dateTimeString, dateTimeColumnTypeInfo.ConvertToDatabase(dateTime));
+ Assert.Equal(dateTime, dateTimeColumnTypeInfo.ConvertFromDatabase(dateTimeString));
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudIntegratedTest.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudIntegratedTest.cs
new file mode 100644
index 0000000..bd07c70
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudIntegratedTest.cs
@@ -0,0 +1,200 @@
+using System.Net;
+using System.Net.Http.Headers;
+using CrupestApi.Commons.Secrets;
+using Microsoft.AspNetCore.TestHost;
+
+namespace CrupestApi.Commons.Crud.Tests;
+
+public class CrudIntegratedTest : IAsyncLifetime
+{
+ private readonly WebApplication _app;
+ private HttpClient _httpClient = default!;
+ private HttpClient _authorizedHttpClient = default!;
+ private string _token = default!;
+
+ public CrudIntegratedTest()
+ {
+ var builder = WebApplication.CreateBuilder();
+ builder.Logging.ClearProviders();
+ builder.Services.AddSingleton<IDbConnectionFactory, SqliteMemoryConnectionFactory>();
+ builder.Services.AddCrud<TestEntity>();
+ builder.WebHost.UseTestServer();
+ _app = builder.Build();
+ _app.UseCrudCore();
+ _app.MapCrud<TestEntity>("/test", "test-perm");
+ }
+
+ public async Task InitializeAsync()
+ {
+ await _app.StartAsync();
+ _httpClient = _app.GetTestClient();
+
+ using (var scope = _app.Services.CreateScope())
+ {
+ var secretService = (SecretService)scope.ServiceProvider.GetRequiredService<ISecretService>();
+ var key = secretService.Create(new SecretInfo
+ {
+ Key = "test-perm"
+ });
+ _token = secretService.GetByKey(key).Secret;
+ }
+
+ _authorizedHttpClient = _app.GetTestClient();
+ _authorizedHttpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", _token);
+ }
+
+ public async Task DisposeAsync()
+ {
+ await _app.StopAsync();
+ }
+
+
+ [Fact]
+ public async Task EmptyTest()
+ {
+ using var response = await _authorizedHttpClient.GetAsync("/test");
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<List<TestEntity>>();
+ Assert.NotNull(body);
+ Assert.Empty(body);
+ }
+
+ [Fact]
+ public async Task CrudTest()
+ {
+ {
+ using var response = await _authorizedHttpClient.PostAsJsonAsync("/test", new TestEntity
+ {
+ Name = "test",
+ Age = 22
+ });
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<TestEntity>();
+ Assert.NotNull(body);
+ Assert.Equal("test", body.Name);
+ Assert.Equal(22, body.Age);
+ Assert.Null(body.Height);
+ Assert.NotEmpty(body.Secret);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.GetAsync("/test");
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<List<TestEntity>>();
+ Assert.NotNull(body);
+ var entity = Assert.Single(body);
+ Assert.Equal("test", entity.Name);
+ Assert.Equal(22, entity.Age);
+ Assert.Null(entity.Height);
+ Assert.NotEmpty(entity.Secret);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.GetAsync("/test/test");
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<TestEntity>();
+ Assert.NotNull(body);
+ Assert.Equal("test", body.Name);
+ Assert.Equal(22, body.Age);
+ Assert.Null(body.Height);
+ Assert.NotEmpty(body.Secret);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.PatchAsJsonAsync("/test/test", new TestEntity
+ {
+ Name = "test-2",
+ Age = 23,
+ Height = 188.0f
+ });
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<TestEntity>();
+ Assert.NotNull(body);
+ Assert.Equal("test-2", body.Name);
+ Assert.Equal(23, body.Age);
+ Assert.Equal(188.0f, body.Height);
+ Assert.NotEmpty(body.Secret);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.GetAsync("/test/test-2");
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<TestEntity>();
+ Assert.NotNull(body);
+ Assert.Equal("test-2", body.Name);
+ Assert.Equal(23, body.Age);
+ Assert.Equal(188.0f, body.Height);
+ Assert.NotEmpty(body.Secret);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.DeleteAsync("/test/test-2");
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.GetAsync("/test");
+ Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+ var body = await response.Content.ReadFromJsonAsync<List<TestEntity>>();
+ Assert.NotNull(body);
+ Assert.Empty(body);
+ }
+ }
+
+ [Fact]
+ public async Task UnauthorizedTest()
+ {
+ {
+ using var response = await _httpClient.GetAsync("/test");
+ Assert.Equal(HttpStatusCode.Unauthorized, response.StatusCode);
+ }
+
+ {
+ using var response = await _httpClient.GetAsync("/test/test");
+ Assert.Equal(HttpStatusCode.Unauthorized, response.StatusCode);
+ }
+
+ {
+ using var response = await _httpClient.PostAsJsonAsync("/test", new TestEntity
+ {
+ Name = "test",
+ Age = 22
+ });
+ Assert.Equal(HttpStatusCode.Unauthorized, response.StatusCode);
+ }
+
+ {
+ using var response = await _httpClient.PatchAsJsonAsync("/test/test", new TestEntity
+ {
+ Name = "test-2",
+ Age = 23,
+ Height = 188.0f
+ });
+ Assert.Equal(HttpStatusCode.Unauthorized, response.StatusCode);
+ }
+
+ {
+ using var response = await _httpClient.DeleteAsync("/test/test");
+ Assert.Equal(HttpStatusCode.Unauthorized, response.StatusCode);
+ }
+ }
+
+ [Fact]
+ public async Task NotFoundTest()
+ {
+ {
+ using var response = await _authorizedHttpClient.GetAsync("/test/test");
+ Assert.Equal(HttpStatusCode.NotFound, response.StatusCode);
+ }
+
+ {
+ using var response = await _authorizedHttpClient.PatchAsJsonAsync("/test/test", new TestEntity
+ {
+ Name = "test-2",
+ Age = 23,
+ Height = 188.0f
+ });
+ Assert.Equal(HttpStatusCode.NotFound, response.StatusCode);
+ }
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs
new file mode 100644
index 0000000..ad0d34c
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs
@@ -0,0 +1,77 @@
+using CrupestApi.Commons.Crud.Migrations;
+using Microsoft.Extensions.Logging.Abstractions;
+
+namespace CrupestApi.Commons.Crud.Tests;
+
+public class CrudServiceTest
+{
+ private readonly SqliteMemoryConnectionFactory _memoryConnectionFactory = new SqliteMemoryConnectionFactory();
+
+ private readonly CrudService<TestEntity> _crudService;
+
+ public CrudServiceTest()
+ {
+ var columnTypeProvider = new ColumnTypeProvider();
+ var tableInfoFactory = new TableInfoFactory(columnTypeProvider, NullLoggerFactory.Instance);
+ var dbConnectionFactory = new SqliteMemoryConnectionFactory();
+
+ _crudService = new CrudService<TestEntity>(
+ tableInfoFactory, dbConnectionFactory, new SqliteDatabaseMigrator(), NullLoggerFactory.Instance);
+ }
+
+ [Fact]
+ public void CrudTest()
+ {
+ var key = _crudService.Create(new TestEntity()
+ {
+ Name = "crupest",
+ Age = 18,
+ });
+
+ Assert.Equal("crupest", key);
+
+ var entity = _crudService.GetByKey(key);
+ Assert.Equal("crupest", entity.Name);
+ Assert.Equal(18, entity.Age);
+ Assert.Null(entity.Height);
+ Assert.NotEmpty(entity.Secret);
+
+ var list = _crudService.GetAll();
+ entity = Assert.Single(list);
+ Assert.Equal("crupest", entity.Name);
+ Assert.Equal(18, entity.Age);
+ Assert.Null(entity.Height);
+ Assert.NotEmpty(entity.Secret);
+
+ var count = _crudService.GetCount();
+ Assert.Equal(1, count);
+
+ _crudService.UpdateByKey(key, new TestEntity()
+ {
+ Name = "crupest2.0",
+ Age = 22,
+ Height = 180,
+ });
+
+ entity = _crudService.GetByKey("crupest2.0");
+ Assert.Equal("crupest2.0", entity.Name);
+ Assert.Equal(22, entity.Age);
+ Assert.Equal(180, entity.Height);
+ Assert.NotEmpty(entity.Secret);
+
+ _crudService.DeleteByKey("crupest2.0");
+
+ count = _crudService.GetCount();
+ Assert.Equal(0, count);
+ }
+
+ [Fact]
+ public void EntityNotExistTest()
+ {
+ Assert.Throws<EntityNotExistException>(() => _crudService.GetByKey("KeyNotExist"));
+ Assert.Throws<EntityNotExistException>(() => _crudService.UpdateByKey("KeyNotExist", new TestEntity
+ {
+ Name = "crupest"
+ }));
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/SqlCompareHelper.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/SqlCompareHelper.cs
new file mode 100644
index 0000000..72b6218
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/SqlCompareHelper.cs
@@ -0,0 +1,85 @@
+using System.Text;
+
+namespace CrupestApi.Commons.Crud.Tests;
+
+public class SqlCompareHelper
+{
+ private static List<char> SymbolTokens = new List<char>() { '(', ')', ';' };
+
+ public static List<string> SqlExtractWords(string? sql, bool toLower = true)
+ {
+ var result = new List<string>();
+
+ if (string.IsNullOrEmpty(sql))
+ {
+ return result;
+ }
+
+ var current = 0;
+
+ StringBuilder? wordBuilder = null;
+
+ while (current < sql.Length)
+ {
+ if (char.IsWhiteSpace(sql[current]))
+ {
+ if (wordBuilder is not null)
+ {
+ result.Add(wordBuilder.ToString());
+ wordBuilder = null;
+ }
+ }
+ else if (SymbolTokens.Contains(sql[current]))
+ {
+ if (wordBuilder is not null)
+ {
+ result.Add(wordBuilder.ToString());
+ wordBuilder = null;
+ }
+ result.Add(sql[current].ToString());
+ }
+ else
+ {
+ if (wordBuilder is not null)
+ {
+ wordBuilder.Append(sql[current]);
+ }
+ else
+ {
+ wordBuilder = new StringBuilder();
+ wordBuilder.Append(sql[current]);
+ }
+ }
+ current++;
+ }
+
+ if (wordBuilder is not null)
+ {
+ result.Add(wordBuilder.ToString());
+ }
+
+ if (toLower)
+ {
+ for (int i = 0; i < result.Count; i++)
+ {
+ result[i] = result[i].ToLower();
+ }
+ }
+
+ return result;
+ }
+
+ public static bool SqlEqual(string left, string right)
+ {
+ return SqlExtractWords(left) == SqlExtractWords(right);
+ }
+
+ [Fact]
+ public void TestSqlExtractWords()
+ {
+ var sql = "SELECT * FROM TableName WHERE (id = @abcd);";
+ var words = SqlExtractWords(sql);
+
+ Assert.Equal(new List<string> { "select", "*", "from", "tablename", "where", "(", "id", "=", "@abcd", ")", ";" }, words);
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TableInfoTest.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TableInfoTest.cs
new file mode 100644
index 0000000..b0aa702
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TableInfoTest.cs
@@ -0,0 +1,35 @@
+using Microsoft.Extensions.Logging.Abstractions;
+
+namespace CrupestApi.Commons.Crud.Tests;
+
+public class TableInfoTest
+{
+ private static TableInfoFactory TableInfoFactory = new TableInfoFactory(new ColumnTypeProvider(), NullLoggerFactory.Instance);
+
+ private TableInfo _tableInfo;
+
+ public TableInfoTest()
+ {
+ _tableInfo = TableInfoFactory.Get(typeof(TestEntity));
+ }
+
+ [Fact]
+ public void TestColumnCount()
+ {
+ Assert.Equal(5, _tableInfo.Columns.Count);
+ Assert.Equal(4, _tableInfo.PropertyColumns.Count);
+ Assert.Equal(4, _tableInfo.ColumnProperties.Count);
+ Assert.Equal(1, _tableInfo.NonColumnProperties.Count);
+ }
+
+ [Fact]
+ public void GenerateSelectSqlTest()
+ {
+ var (sql, parameters) = _tableInfo.GenerateSelectSql(null, WhereClause.Create().Eq("Name", "Hello"));
+ var parameterName = parameters.First().Name;
+
+ // TODO: Is there a way to auto detect parameters?
+ SqlCompareHelper.SqlEqual($"SELECT * FROM TestEntity WHERE (Name = @{parameterName})", sql);
+ Assert.Equal("Hello", parameters.Get<string>(parameterName));
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TestEntity.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TestEntity.cs
new file mode 100644
index 0000000..c15334c
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/TestEntity.cs
@@ -0,0 +1,23 @@
+namespace CrupestApi.Commons.Crud.Tests;
+
+public class TestEntity
+{
+ [Column(ActAsKey = true, NotNull = true)]
+ public string Name { get; set; } = default!;
+
+ [Column(NotNull = true)]
+ public int Age { get; set; }
+
+ [Column]
+ public float? Height { get; set; }
+
+ [Column(OnlyGenerated = true, NotNull = true, NoUpdate = true)]
+ public string Secret { get; set; } = default!;
+
+ public static string SecretDefaultValueGenerator()
+ {
+ return "secret";
+ }
+
+ public string NonColumn { get; set; } = "Not A Column";
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/CrupestApi.Commons.Tests.csproj b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/CrupestApi.Commons.Tests.csproj
new file mode 100644
index 0000000..0360ee1
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/CrupestApi.Commons.Tests.csproj
@@ -0,0 +1,29 @@
+<Project Sdk="Microsoft.NET.Sdk.Web">
+
+ <PropertyGroup>
+ <TargetFramework>net7.0</TargetFramework>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <Nullable>enable</Nullable>
+
+ <IsPackable>false</IsPackable>
+ </PropertyGroup>
+
+ <ItemGroup>
+ <PackageReference Include="Microsoft.AspNetCore.TestHost" Version="7.0.1" />
+ <PackageReference Include="Microsoft.NET.Test.Sdk" Version="17.4.0" />
+ <PackageReference Include="xunit" Version="2.4.2" />
+ <PackageReference Include="xunit.runner.visualstudio" Version="2.4.5">
+ <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
+ <PrivateAssets>all</PrivateAssets>
+ </PackageReference>
+ <PackageReference Include="coverlet.collector" Version="3.2.0">
+ <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
+ <PrivateAssets>all</PrivateAssets>
+ </PackageReference>
+ </ItemGroup>
+
+ <ItemGroup>
+ <ProjectReference Include="..\CrupestApi.Commons\CrupestApi.Commons.csproj" />
+ </ItemGroup>
+
+</Project>
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Usings.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Usings.cs
new file mode 100644
index 0000000..8c927eb
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Usings.cs
@@ -0,0 +1 @@
+global using Xunit;
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Config.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Config.cs
new file mode 100644
index 0000000..0ca3547
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Config.cs
@@ -0,0 +1,23 @@
+namespace CrupestApi.Commons;
+
+public class CrupestApiConfig
+{
+ public string DataDir { get; set; } = string.Empty;
+}
+
+public static class CrupestApiConfigExtensions
+{
+ public static IServiceCollection AddCrupestApiConfig(this IServiceCollection services)
+ {
+ services.AddOptions<CrupestApiConfig>().BindConfiguration("CrupestApi");
+ services.PostConfigure<CrupestApiConfig>(config =>
+ {
+ if (config.DataDir is null || config.DataDir.Length == 0)
+ {
+ config.DataDir = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile), "crupest-api");
+ }
+ });
+
+ return services;
+ }
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
new file mode 100644
index 0000000..e8d3c2e
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
@@ -0,0 +1,236 @@
+using System.Diagnostics;
+using System.Reflection;
+using System.Text;
+
+namespace CrupestApi.Commons.Crud;
+
+public class ColumnInfo
+{
+ private readonly AggregateColumnMetadata _metadata = new AggregateColumnMetadata();
+ private ILogger<ColumnInfo> _logger;
+
+ /// <summary>
+ /// Initialize a column without corresponding property.
+ /// </summary>
+ public ColumnInfo(TableInfo table, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider, ILoggerFactory loggerFactory)
+ {
+ _logger = loggerFactory.CreateLogger<ColumnInfo>();
+ if (metadata is null)
+ throw new ArgumentException("You must specify metadata for non-property column.");
+ if (metadata.TryGetValue(ColumnMetadataKeys.ColumnName, out var columnName))
+ _logger.LogInformation("Create column without property.", columnName);
+ else
+ throw new ArgumentException("You must specify name in metadata for non-property column.");
+
+ Table = table;
+ _metadata.Add(metadata);
+ ColumnType = typeProvider.Get(clrType);
+ }
+
+ /// <summary>
+ /// Initialize a column with corresponding property.
+ /// </summary>
+ public ColumnInfo(TableInfo table, PropertyInfo propertyInfo, IColumnTypeProvider typeProvider, ILoggerFactory loggerFactory)
+ {
+ _logger = loggerFactory.CreateLogger<ColumnInfo>();
+ _logger.LogInformation("Create column with property {}.", propertyInfo.Name);
+
+ Table = table;
+ PropertyInfo = propertyInfo;
+ ColumnType = typeProvider.Get(propertyInfo.PropertyType);
+
+ var columnAttribute = propertyInfo.GetCustomAttribute<ColumnAttribute>();
+ if (columnAttribute is not null)
+ {
+ _metadata.Add(columnAttribute);
+ }
+ }
+
+ public TableInfo Table { get; }
+
+ public Type EntityType => Table.EntityType;
+
+ // If null, there is no corresponding property.
+ public PropertyInfo? PropertyInfo { get; } = null;
+
+ public IColumnMetadata Metadata => _metadata;
+
+ public IColumnTypeInfo ColumnType { get; }
+
+ public bool IsPrimaryKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsPrimaryKey) is true;
+ public bool IsAutoIncrement => IsPrimaryKey;
+ public bool IsNotNull => IsPrimaryKey || Metadata.GetValueOrDefault(ColumnMetadataKeys.NotNull) is true;
+ public bool IsOnlyGenerated => Metadata.GetValueOrDefault(ColumnMetadataKeys.OnlyGenerated) is true;
+ public bool IsNoUpdate => Metadata.GetValueOrDefault(ColumnMetadataKeys.NoUpdate) is true;
+ public object? DefaultValue => Metadata.GetValueOrDefault(ColumnMetadataKeys.DefaultValue);
+ /// <summary>
+ /// This only returns metadata value. It doesn't not fall back to primary column. If you want to get the real key column, go to table info.
+ /// </summary>
+ /// <seealso cref="ColumnMetadataKeys.ActAsKey"/>
+ /// <seealso cref="TableInfo.KeyColumn"/>
+ public bool IsSpecifiedAsKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.ActAsKey) is true;
+ public ColumnIndexType Index => Metadata.GetValueOrDefault<ColumnIndexType?>(ColumnMetadataKeys.Index) ?? ColumnIndexType.None;
+
+ /// <summary>
+ /// Whether the column value can be generated, which means the column has a default value or a default value generator or is AUTOINCREMENT.
+ /// </summary>
+ public bool CanBeGenerated => DefaultValue is not null || DefaultValueGeneratorMethod is not null || IsAutoIncrement;
+
+ /// <summary>
+ /// The real column name. Maybe set in metadata or just the property name.
+ /// </summary>
+ /// <value></value>
+ public string ColumnName
+ {
+ get
+ {
+ object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.ColumnName);
+ Debug.Assert(value is null || value is string);
+ return ((string?)value ?? PropertyInfo?.Name) ?? throw new Exception("Failed to get column name.");
+ }
+ }
+
+ public MethodInfo? DefaultValueGeneratorMethod
+ {
+ get
+ {
+ object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.DefaultValueGenerator);
+ Debug.Assert(value is null || value is string);
+ MethodInfo? result;
+ if (value is null)
+ {
+ string methodName = ColumnName + "DefaultValueGenerator";
+ result = Table.EntityType.GetMethod(methodName, BindingFlags.Public | BindingFlags.Static);
+ }
+ else
+ {
+ string methodName = (string)value;
+ result = Table.EntityType.GetMethod(methodName, BindingFlags.Static) ?? throw new Exception("The default value generator does not exist.");
+ }
+
+ return result;
+ }
+ }
+
+ public MethodInfo? ValidatorMethod
+ {
+ get
+ {
+ object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.DefaultValueGenerator);
+ Debug.Assert(value is null || value is string);
+ MethodInfo? result;
+ if (value is null)
+ {
+ string methodName = ColumnName + "Validator";
+ result = Table.EntityType.GetMethod(methodName, BindingFlags.Static);
+ }
+ else
+ {
+ string methodName = (string)value;
+ result = Table.EntityType.GetMethod(methodName, BindingFlags.Static) ?? throw new Exception("The validator does not exist.");
+ }
+
+ return result;
+ }
+ }
+
+ public void InvokeValidator(object? value)
+ {
+ var method = this.ValidatorMethod;
+ if (method is null)
+ {
+ _logger.LogInformation("Try to invoke validator for column {} but it does not exist.", ColumnName);
+ return;
+ }
+ var parameters = method.GetParameters();
+ if (parameters.Length == 0)
+ {
+ throw new Exception("The validator method must have at least one parameter.");
+ }
+ else if (parameters.Length == 1)
+ {
+ method.Invoke(null, new object?[] { value });
+ }
+ else if (parameters.Length == 2)
+ {
+ if (parameters[0].ParameterType == typeof(ColumnInfo))
+ method.Invoke(null, new object?[] { this, value });
+ else if (parameters[1].ParameterType == typeof(ColumnInfo))
+ method.Invoke(null, new object?[] { value, this });
+ else
+ throw new Exception("The validator method must have a parameter of type ColumnInfo if it has 2 parameters.");
+ }
+ else
+ {
+ throw new Exception("The validator method can only have 1 or 2 parameters.");
+ }
+ }
+
+ public object? InvokeDefaultValueGenerator()
+ {
+ var method = this.DefaultValueGeneratorMethod;
+ if (method is null)
+ {
+ _logger.LogInformation("Try to invoke default value generator for column {} but it does not exist.", ColumnName);
+ return null;
+ }
+ var parameters = method.GetParameters();
+ if (parameters.Length == 0)
+ {
+ return method.Invoke(null, new object?[0]);
+ }
+ else if (parameters.Length == 1)
+ {
+ if (parameters[0].ParameterType != typeof(ColumnInfo))
+ throw new Exception("The default value generator method can only have a parameter of type ColumnInfo.");
+ return method.Invoke(null, new object?[] { this });
+ }
+ else
+ {
+ throw new Exception("The default value generator method can only have 0 or 1 parameter.");
+ }
+ }
+
+ public object? GenerateDefaultValue()
+ {
+ if (DefaultValueGeneratorMethod is not null)
+ {
+ return InvokeDefaultValueGenerator();
+ }
+
+ if (Metadata.TryGetValue(ColumnMetadataKeys.DefaultValue, out object? value))
+ {
+ return value;
+ }
+ else
+ {
+ return null;
+ }
+ }
+
+ public string GenerateCreateTableColumnString(string? dbProviderId = null)
+ {
+ StringBuilder result = new StringBuilder();
+ result.Append(ColumnName);
+ result.Append(' ');
+ result.Append(ColumnType.GetSqlTypeString(dbProviderId));
+ if (IsPrimaryKey)
+ {
+ result.Append(' ');
+ result.Append("PRIMARY KEY");
+ }
+ else if (IsNotNull)
+ {
+ result.Append(' ');
+ result.Append("NOT NULL");
+ }
+
+ if (IsAutoIncrement)
+ {
+ result.Append(' ');
+ result.Append("AUTOINCREMENT");
+ }
+
+ return result.ToString();
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
new file mode 100644
index 0000000..7247ff1
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
@@ -0,0 +1,188 @@
+namespace CrupestApi.Commons.Crud;
+
+public static class ColumnMetadataKeys
+{
+ public const string ColumnName = nameof(ColumnAttribute.ColumnName);
+ public const string NotNull = nameof(ColumnAttribute.NotNull);
+ public const string IsPrimaryKey = nameof(ColumnAttribute.IsPrimaryKey);
+ public const string Index = nameof(ColumnAttribute.Index);
+
+ /// <summary>
+ /// This will add hooks for string type column to coerce null to ""(empty string) when get or set. No effect on non-string type.
+ /// </summary>
+ public const string DefaultEmptyForString = nameof(ColumnAttribute.DefaultEmptyForString);
+
+ /// <summary>
+ /// This indicates that you take care of generate this column value when create entity. User calling the api can not specify the value.
+ /// </summary>
+ public const string OnlyGenerated = nameof(ColumnAttribute.OnlyGenerated);
+
+ /// <summary>
+ /// The default value generator method name in entity type. Default to null, aka, search for ColumnNameDefaultValueGenerator.
+ /// Generator has signature <code>static void DefaultValueGenerator(ColumnInfo column)</code>
+ /// </summary>
+ public const string DefaultValueGenerator = nameof(ColumnAttribute.DefaultValueGenerator);
+
+ /// <summary>
+ /// The validator method name in entity type. Default to null, aka, the default validator.
+ /// Validator has signature <code>static void Validator(ColumnInfo column, object value)</code>
+ /// Value param is never null. If you want to mean NULL, it should be a <see cref="DbNullValue"/>.
+ /// </summary>
+ public const string Validator = nameof(ColumnAttribute.Validator);
+
+ /// <summary>
+ /// The column can only be set when inserted, can't be changed in update.
+ /// </summary>
+ /// <returns></returns>
+ public const string NoUpdate = nameof(ColumnAttribute.NoUpdate);
+
+ /// <summary>
+ /// This column acts as key when get one entity for http get method in path.
+ /// </summary>
+ public const string ActAsKey = nameof(ColumnAttribute.ActAsKey);
+
+ /// <summary>
+ /// The default value used for the column.
+ /// </summary>
+ public const string DefaultValue = nameof(ColumnAttribute.DefaultValue);
+}
+
+public interface IColumnMetadata
+{
+ bool TryGetValue(string key, out object? value);
+
+ object? GetValueOrDefault(string key)
+ {
+ if (TryGetValue(key, out var value))
+ {
+ return value;
+ }
+ else
+ {
+ return null;
+ }
+ }
+
+ T? GetValueOrDefault<T>(string key)
+ {
+ return (T?)GetValueOrDefault(key);
+ }
+
+ object? this[string key]
+ {
+ get
+ {
+ if (TryGetValue(key, out var value))
+ {
+ return value;
+ }
+ else
+ {
+ throw new KeyNotFoundException("Key not found.");
+ }
+ }
+ }
+}
+
+public enum ColumnIndexType
+{
+ None,
+ Unique,
+ NonUnique
+}
+
+[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
+public class ColumnAttribute : Attribute, IColumnMetadata
+{
+ // if null, use the property name.
+ public string? ColumnName { get; init; }
+
+ // default false.
+ public bool NotNull { get; init; }
+
+ // default false
+ public bool IsPrimaryKey { get; init; }
+
+ // default None
+ public ColumnIndexType Index { get; init; } = ColumnIndexType.None;
+
+ /// <seealso cref="ColumnMetadataKeys.DefaultEmptyForString"/>
+ public bool DefaultEmptyForString { get; init; }
+
+ /// <seealso cref="ColumnMetadataKeys.OnlyGenerated"/>
+ public bool OnlyGenerated { get; init; }
+
+ /// <seealso cref="ColumnMetadataKeys.DefaultValueGenerator"/>
+ public string? DefaultValueGenerator { get; init; }
+
+ /// <seealso cref="ColumnMetadataKeys.Validator"/>
+ public string? Validator { get; init; }
+
+ /// <seealso cref="ColumnMetadataKeys.NoUpdate"/>
+ public bool NoUpdate { get; init; }
+
+ /// <seealso cref="ColumnMetadataKeys.ActAsKey"/>
+ public bool ActAsKey { get; init; }
+
+ public object? DefaultValue { get; init; }
+
+ public bool TryGetValue(string key, out object? value)
+ {
+ var property = GetType().GetProperty(key);
+ if (property is null)
+ {
+ value = null;
+ return false;
+ }
+ value = property.GetValue(this);
+ return true;
+ }
+}
+
+public class AggregateColumnMetadata : IColumnMetadata
+{
+ private IDictionary<string, object?> _own = new Dictionary<string, object?>();
+ private IList<IColumnMetadata> _children = new List<IColumnMetadata>();
+
+ public void Add(string key, object? value)
+ {
+ _own[key] = value;
+ }
+
+ public void Remove(string key)
+ {
+ _own.Remove(key);
+ }
+
+ public void Add(IColumnMetadata child)
+ {
+ _children.Add(child);
+ }
+
+ public void Remove(IColumnMetadata child)
+ {
+ _children.Remove(child);
+ }
+
+ public bool TryGetValue(string key, out object? value)
+ {
+ if (_own.ContainsKey(key))
+ {
+ value = _own[key];
+ return true;
+ }
+
+ bool found = false;
+ value = null;
+ foreach (var child in _children)
+ {
+ if (child.TryGetValue(key, out var tempValue))
+ {
+ value = tempValue;
+ found = true;
+ }
+ }
+
+ return found;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
new file mode 100644
index 0000000..19eff52
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
@@ -0,0 +1,218 @@
+using System.Data;
+using System.Diagnostics;
+using System.Globalization;
+using System.Text.Json;
+using System.Text.Json.Serialization;
+
+namespace CrupestApi.Commons.Crud;
+
+public interface IColumnTypeInfo
+{
+ public static IColumnTypeInfo BoolColumnTypeInfo { get; } = new SimpleColumnTypeInfo<bool>();
+ public static IColumnTypeInfo IntColumnTypeInfo { get; } = new SimpleColumnTypeInfo<int>();
+ public static IColumnTypeInfo ShortColumnTypeInfo { get; } = new SimpleColumnTypeInfo<short>();
+ public static IColumnTypeInfo SByteColumnTypeInfo { get; } = new SimpleColumnTypeInfo<sbyte>();
+ public static IColumnTypeInfo LongColumnTypeInfo { get; } = new SimpleColumnTypeInfo<long>();
+ public static IColumnTypeInfo FloatColumnTypeInfo { get; } = new SimpleColumnTypeInfo<float>();
+ public static IColumnTypeInfo DoubleColumnTypeInfo { get; } = new SimpleColumnTypeInfo<double>();
+ public static IColumnTypeInfo StringColumnTypeInfo { get; } = new SimpleColumnTypeInfo<string>();
+ public static IColumnTypeInfo BytesColumnTypeInfo { get; } = new SimpleColumnTypeInfo<byte[]>();
+ public static IColumnTypeInfo DateTimeColumnTypeInfo { get; } = new DateTimeColumnTypeInfo();
+
+ Type ClrType { get; }
+ Type DatabaseClrType { get; }
+ bool IsSimple { get { return ClrType == DatabaseClrType; } }
+ DbType DbType
+ {
+ get
+ {
+ if (DatabaseClrType == typeof(bool))
+ {
+ return DbType.Boolean;
+ }
+ else if (DatabaseClrType == typeof(int))
+ {
+ return DbType.Int32;
+ }
+ else if (DatabaseClrType == typeof(long))
+ {
+ return DbType.Int64;
+ }
+ else if (DatabaseClrType == typeof(short))
+ {
+ return DbType.Int16;
+ }
+ else if (DatabaseClrType == typeof(sbyte))
+ {
+ return DbType.SByte;
+ }
+ else if (DatabaseClrType == typeof(double))
+ {
+ return DbType.Double;
+ }
+ else if (DatabaseClrType == typeof(float))
+ {
+ return DbType.Single;
+ }
+ else if (DatabaseClrType == typeof(string))
+ {
+ return DbType.String;
+ }
+ else if (DatabaseClrType == typeof(byte[]))
+ {
+ return DbType.Binary;
+ }
+ else
+ {
+ throw new Exception("Can't deduce DbType.");
+ }
+ }
+ }
+
+ string GetSqlTypeString(string? dbProviderId = null)
+ {
+ // Default implementation for SQLite
+ return DbType switch
+ {
+ DbType.String => "TEXT",
+ DbType.Boolean or DbType.Int16 or DbType.Int32 or DbType.Int64 => "INTEGER",
+ DbType.Single or DbType.Double => "REAL",
+ DbType.Binary => "BLOB",
+ _ => throw new Exception($"Unsupported DbType: {DbType}"),
+ };
+ }
+
+ JsonConverter? JsonConverter { get { return null; } }
+
+ // You must override this method if ClrType != DatabaseClrType
+ object? ConvertFromDatabase(object? databaseValue)
+ {
+ Debug.Assert(IsSimple);
+ return databaseValue;
+ }
+
+ // You must override this method if ClrType != DatabaseClrType
+ object? ConvertToDatabase(object? value)
+ {
+ Debug.Assert(IsSimple);
+ return value;
+ }
+}
+
+public interface IColumnTypeProvider
+{
+ IReadOnlyList<IColumnTypeInfo> GetAll();
+ IColumnTypeInfo Get(Type clrType);
+
+ IList<IColumnTypeInfo> GetAllCustom()
+ {
+ return GetAll().Where(t => !t.IsSimple).ToList();
+ }
+}
+
+public class SimpleColumnTypeInfo<T> : IColumnTypeInfo
+{
+ public Type ClrType => typeof(T);
+ public Type DatabaseClrType => typeof(T);
+}
+
+public class DateTimeColumnTypeInfo : IColumnTypeInfo
+{
+ private JsonConverter<DateTime> _jsonConverter;
+
+ public DateTimeColumnTypeInfo()
+ {
+ _jsonConverter = new DateTimeJsonConverter(this);
+ }
+
+ public Type ClrType => typeof(DateTime);
+ public Type DatabaseClrType => typeof(string);
+
+ public JsonConverter JsonConverter => _jsonConverter;
+
+ public object? ConvertToDatabase(object? value)
+ {
+ if (value is null) return null;
+ Debug.Assert(value is DateTime);
+ return ((DateTime)value).ToUniversalTime().ToString("s") + "Z";
+ }
+
+ public object? ConvertFromDatabase(object? databaseValue)
+ {
+ if (databaseValue is null) return null;
+ Debug.Assert(databaseValue is string);
+ var databaseString = (string)databaseValue;
+ var dateTimeStyles = DateTimeStyles.None;
+ if (databaseString.Length > 0 && databaseString[^1] == 'Z')
+ {
+ databaseString = databaseString.Substring(0, databaseString.Length - 1);
+ dateTimeStyles = DateTimeStyles.AssumeUniversal & DateTimeStyles.AdjustToUniversal;
+ }
+ return DateTime.ParseExact(databaseString, "s", null, dateTimeStyles);
+ }
+}
+
+public class DateTimeJsonConverter : JsonConverter<DateTime>
+{
+ private readonly DateTimeColumnTypeInfo _typeInfo;
+
+ public DateTimeJsonConverter(DateTimeColumnTypeInfo typeInfo)
+ {
+ _typeInfo = typeInfo;
+ }
+
+ public override DateTime Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
+ {
+ var databaseValue = reader.GetString();
+ return (DateTime)_typeInfo.ConvertFromDatabase(databaseValue)!;
+ }
+
+ public override void Write(Utf8JsonWriter writer, DateTime value, JsonSerializerOptions options)
+ {
+ var databaseValue = _typeInfo.ConvertToDatabase(value);
+ writer.WriteStringValue((string)databaseValue!);
+ }
+}
+
+public class ColumnTypeProvider : IColumnTypeProvider
+{
+ private Dictionary<Type, IColumnTypeInfo> _typeMap = new Dictionary<Type, IColumnTypeInfo>();
+
+ public ColumnTypeProvider()
+ {
+ _typeMap.Add(IColumnTypeInfo.BoolColumnTypeInfo.ClrType, IColumnTypeInfo.BoolColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.IntColumnTypeInfo.ClrType, IColumnTypeInfo.IntColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.ShortColumnTypeInfo.ClrType, IColumnTypeInfo.ShortColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.SByteColumnTypeInfo.ClrType, IColumnTypeInfo.SByteColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.LongColumnTypeInfo.ClrType, IColumnTypeInfo.LongColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.FloatColumnTypeInfo.ClrType, IColumnTypeInfo.FloatColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.DoubleColumnTypeInfo.ClrType, IColumnTypeInfo.DoubleColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.StringColumnTypeInfo.ClrType, IColumnTypeInfo.StringColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.BytesColumnTypeInfo.ClrType, IColumnTypeInfo.BytesColumnTypeInfo);
+ _typeMap.Add(IColumnTypeInfo.DateTimeColumnTypeInfo.ClrType, IColumnTypeInfo.DateTimeColumnTypeInfo);
+ }
+
+ public IReadOnlyList<IColumnTypeInfo> GetAll()
+ {
+ return _typeMap.Values.ToList();
+ }
+
+ // This is thread-safe.
+ public IColumnTypeInfo Get(Type clrType)
+ {
+ if (_typeMap.TryGetValue(clrType, out var typeInfo))
+ {
+ return typeInfo;
+ }
+ else
+ {
+ if (clrType.IsGenericType && clrType.GetGenericTypeDefinition() == typeof(Nullable<>))
+ {
+ clrType = clrType.GetGenericArguments()[0];
+ return Get(clrType);
+ }
+
+ throw new Exception($"Unsupported type: {clrType}");
+ }
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
new file mode 100644
index 0000000..1e881d3
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
@@ -0,0 +1,132 @@
+using System.Data;
+using CrupestApi.Commons.Crud.Migrations;
+
+namespace CrupestApi.Commons.Crud;
+
+[Flags]
+public enum UpdateBehavior
+{
+ None = 0,
+ SaveNull = 1
+}
+
+public class CrudService<TEntity> : IDisposable where TEntity : class
+{
+ protected readonly TableInfo _table;
+ protected readonly string? _connectionName;
+ protected readonly IDbConnection _dbConnection;
+ private readonly bool _shouldDisposeConnection;
+ private IDatabaseMigrator _migrator;
+ private readonly ILogger<CrudService<TEntity>> _logger;
+
+ public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory)
+ {
+ _connectionName = GetConnectionName();
+ _table = tableInfoFactory.Get(typeof(TEntity));
+ _dbConnection = dbConnectionFactory.Get(_connectionName);
+ _shouldDisposeConnection = dbConnectionFactory.ShouldDisposeConnection;
+ _migrator = migrator;
+ _logger = loggerFactory.CreateLogger<CrudService<TEntity>>();
+ }
+
+ protected virtual void EnsureDatabase()
+ {
+ if (_migrator.NeedMigrate(_dbConnection, _table))
+ {
+ _logger.LogInformation($"Entity {_table.TableName} needs migration.");
+ _migrator.AutoMigrate(_dbConnection, _table);
+ }
+ }
+
+ protected virtual string GetConnectionName()
+ {
+ return typeof(TEntity).Name;
+ }
+
+ protected virtual void AfterMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+ {
+
+ }
+
+ public void Dispose()
+ {
+ if (_shouldDisposeConnection)
+ _dbConnection.Dispose();
+ }
+
+ public List<TEntity> GetAll()
+ {
+ EnsureDatabase();
+ var result = _table.Select<TEntity>(_dbConnection, null);
+ return result;
+ }
+
+ public int GetCount()
+ {
+ EnsureDatabase();
+ var result = _table.SelectCount(_dbConnection);
+ return result;
+ }
+
+ public TEntity GetByKey(object key)
+ {
+ EnsureDatabase();
+ var result = _table.Select<TEntity>(_dbConnection, null, WhereClause.Create().Eq(_table.KeyColumn.ColumnName, key)).SingleOrDefault();
+ if (result is null)
+ {
+ throw new EntityNotExistException($"Required entity for key {key} not found.");
+ }
+ return result;
+ }
+
+ public IInsertClause ConvertEntityToInsertClauses(TEntity entity)
+ {
+ var result = new InsertClause();
+ foreach (var column in _table.PropertyColumns)
+ {
+ var value = column.PropertyInfo!.GetValue(entity);
+ result.Add(column.ColumnName, value);
+ }
+ return result;
+ }
+
+ public object Create(TEntity entity)
+ {
+ EnsureDatabase();
+ var insertClause = ConvertEntityToInsertClauses(entity);
+ _table.Insert(_dbConnection, insertClause, out var key);
+ return key;
+ }
+
+ public IUpdateClause ConvertEntityToUpdateClauses(TEntity entity, UpdateBehavior behavior)
+ {
+ var result = UpdateClause.Create();
+ var saveNull = behavior.HasFlag(UpdateBehavior.SaveNull);
+ foreach (var column in _table.PropertyColumns)
+ {
+ var value = column.PropertyInfo!.GetValue(entity);
+ if (!saveNull && value is null) continue;
+ result.Add(column.ColumnName, value);
+ }
+ return result;
+ }
+
+ // Return new key.
+ public object UpdateByKey(object key, TEntity entity, UpdateBehavior behavior = UpdateBehavior.None)
+ {
+ EnsureDatabase();
+ var affectedCount = _table.Update(_dbConnection, WhereClause.Create().Eq(_table.KeyColumn.ColumnName, key),
+ ConvertEntityToUpdateClauses(entity, behavior), out var newKey);
+ if (affectedCount == 0)
+ {
+ throw new EntityNotExistException($"Required entity for key {key} not found.");
+ }
+ return newKey ?? key;
+ }
+
+ public bool DeleteByKey(object key)
+ {
+ EnsureDatabase();
+ return _table.Delete(_dbConnection, WhereClause.Create().Eq(_table.KeyColumn.ColumnName, key)) == 1;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
new file mode 100644
index 0000000..a7e5193
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
@@ -0,0 +1,34 @@
+using CrupestApi.Commons.Crud.Migrations;
+using CrupestApi.Commons.Secrets;
+using Microsoft.Extensions.DependencyInjection.Extensions;
+
+namespace CrupestApi.Commons.Crud;
+
+public static class CrudServiceCollectionExtensions
+{
+ public static IServiceCollection AddCrudCore(this IServiceCollection services)
+ {
+ services.TryAddSingleton<IDbConnectionFactory, SqliteConnectionFactory>();
+ services.TryAddSingleton<IColumnTypeProvider, ColumnTypeProvider>();
+ services.TryAddSingleton<ITableInfoFactory, TableInfoFactory>();
+ services.TryAddSingleton<IDatabaseMigrator, SqliteDatabaseMigrator>();
+ services.AddSecrets();
+ return services;
+ }
+
+ public static IServiceCollection AddCrud<TEntity, TCrudService>(this IServiceCollection services) where TEntity : class where TCrudService : CrudService<TEntity>
+ {
+ AddCrudCore(services);
+
+ services.TryAddScoped<CrudService<TEntity>, TCrudService>();
+ services.TryAddScoped<EntityJsonHelper<TEntity>>();
+
+ return services;
+ }
+
+ public static IServiceCollection AddCrud<TEntity>(this IServiceCollection services) where TEntity : class
+ {
+ return services.AddCrud<TEntity, CrudService<TEntity>>();
+ }
+
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs
new file mode 100644
index 0000000..8942979
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs
@@ -0,0 +1,101 @@
+namespace CrupestApi.Commons.Crud;
+
+public static class CrudWebApplicationExtensions
+{
+ public static WebApplication UseCrudCore(this WebApplication app)
+ {
+ app.Use(async (context, next) =>
+ {
+ try
+ {
+ await next();
+ }
+ catch (EntityNotExistException)
+ {
+ await context.ResponseMessageAsync("Requested entity does not exist.", StatusCodes.Status404NotFound);
+ }
+ catch (UserException e)
+ {
+ await context.ResponseMessageAsync(e.Message);
+ }
+ });
+
+ return app;
+ }
+
+ public static WebApplication MapCrud<TEntity>(this WebApplication app, string path, string? permission) where TEntity : class
+ {
+ app.MapGet(path, async (context) =>
+ {
+ if (!context.RequirePermission(permission)) return;
+ var crudService = context.RequestServices.GetRequiredService<CrudService<TEntity>>();
+ var entityJsonHelper = context.RequestServices.GetRequiredService<EntityJsonHelper<TEntity>>();
+ var allEntities = crudService.GetAll();
+ await context.ResponseJsonAsync(allEntities.Select(e => entityJsonHelper.ConvertEntityToDictionary(e)));
+ });
+
+ app.MapGet(path + "/{key}", async (context) =>
+ {
+ if (!context.RequirePermission(permission)) return;
+ var crudService = context.RequestServices.GetRequiredService<CrudService<TEntity>>();
+ var entityJsonHelper = context.RequestServices.GetRequiredService<EntityJsonHelper<TEntity>>();
+ var key = context.Request.RouteValues["key"]?.ToString();
+ if (key == null)
+ {
+ await context.ResponseMessageAsync("Please specify a key in path.");
+ return;
+ }
+
+ var entity = crudService.GetByKey(key);
+ await context.ResponseJsonAsync(entityJsonHelper.ConvertEntityToDictionary(entity));
+ });
+
+ app.MapPost(path, async (context) =>
+ {
+ if (!context.RequirePermission(permission)) return;
+ var crudService = context.RequestServices.GetRequiredService<CrudService<TEntity>>();
+ var entityJsonHelper = context.RequestServices.GetRequiredService<EntityJsonHelper<TEntity>>();
+ var jsonDocument = await context.Request.ReadJsonAsync();
+ var key = crudService.Create(entityJsonHelper.ConvertJsonToEntityForInsert(jsonDocument.RootElement));
+ await context.ResponseJsonAsync(entityJsonHelper.ConvertEntityToDictionary(crudService.GetByKey(key)));
+ });
+
+ app.MapPatch(path + "/{key}", async (context) =>
+ {
+ if (!context.RequirePermission(permission)) return;
+ var key = context.Request.RouteValues["key"]?.ToString();
+ var crudService = context.RequestServices.GetRequiredService<CrudService<TEntity>>();
+ var entityJsonHelper = context.RequestServices.GetRequiredService<EntityJsonHelper<TEntity>>();
+ if (key == null)
+ {
+ await context.ResponseMessageAsync("Please specify a key in path.");
+ return;
+ }
+
+ var jsonDocument = await context.Request.ReadJsonAsync();
+ var entity = entityJsonHelper.ConvertJsonToEntityForUpdate(jsonDocument.RootElement, out var updateBehavior);
+ var newKey = crudService.UpdateByKey(key, entity, updateBehavior);
+ await context.ResponseJsonAsync(entityJsonHelper.ConvertEntityToDictionary(crudService.GetByKey(newKey)));
+ });
+
+ app.MapDelete(path + "/{key}", async (context) =>
+ {
+ if (!context.RequirePermission(permission)) return;
+ var crudService = context.RequestServices.GetRequiredService<CrudService<TEntity>>();
+ var key = context.Request.RouteValues["key"]?.ToString();
+ if (key == null)
+ {
+ await context.ResponseMessageAsync("Please specify a key in path.");
+ return;
+ }
+
+ var deleted = crudService.DeleteByKey(key);
+ if (deleted)
+ await context.ResponseMessageAsync("Deleted.", StatusCodes.Status200OK);
+ else
+ await context.ResponseMessageAsync("Not exist.", StatusCodes.Status200OK);
+ });
+
+ return app;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
new file mode 100644
index 0000000..701622c
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
@@ -0,0 +1,75 @@
+using System.Data;
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Options;
+
+namespace CrupestApi.Commons.Crud;
+
+public interface IDbConnectionFactory
+{
+ IDbConnection Get(string? name = null);
+ bool ShouldDisposeConnection { get; }
+}
+
+public class SqliteConnectionFactory : IDbConnectionFactory
+{
+ private readonly IOptionsMonitor<CrupestApiConfig> _apiConfigMonitor;
+
+ public SqliteConnectionFactory(IOptionsMonitor<CrupestApiConfig> apiConfigMonitor)
+ {
+ _apiConfigMonitor = apiConfigMonitor;
+ }
+
+ public IDbConnection Get(string? name = null)
+ {
+ var connectionString = new SqliteConnectionStringBuilder()
+ {
+ DataSource = Path.Combine(_apiConfigMonitor.CurrentValue.DataDir, $"{name ?? "crupest-api"}.db"),
+ Mode = SqliteOpenMode.ReadWriteCreate
+ }.ToString();
+
+ var connection = new SqliteConnection(connectionString);
+ connection.Open();
+ return connection;
+ }
+
+ public bool ShouldDisposeConnection => true;
+}
+
+public class SqliteMemoryConnectionFactory : IDbConnectionFactory, IDisposable
+{
+ private readonly Dictionary<string, IDbConnection> _connections = new();
+
+ public IDbConnection Get(string? name = null)
+ {
+ name = name ?? "crupest-api";
+
+ if (_connections.TryGetValue(name, out var connection))
+ {
+ return connection;
+ }
+ else
+ {
+ var connectionString = new SqliteConnectionStringBuilder()
+ {
+ DataSource = ":memory:",
+ Mode = SqliteOpenMode.ReadWriteCreate
+ }.ToString();
+
+ connection = new SqliteConnection(connectionString);
+ _connections.Add(name, connection);
+ connection.Open();
+ return connection;
+ }
+ }
+
+ public bool ShouldDisposeConnection => false;
+
+
+ public void Dispose()
+ {
+ foreach (var connection in _connections.Values)
+ {
+ connection.Dispose();
+ }
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs
new file mode 100644
index 0000000..5dc5a61
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs
@@ -0,0 +1,9 @@
+namespace CrupestApi.Commons.Crud;
+
+/// <summary>
+/// This will always represent null value in database.
+/// </summary>
+public class DbNullValue
+{
+ public static DbNullValue Instance { get; } = new DbNullValue();
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/EntityJsonHelper.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/EntityJsonHelper.cs
new file mode 100644
index 0000000..cf3f178
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/EntityJsonHelper.cs
@@ -0,0 +1,206 @@
+using System.Globalization;
+using System.Text.Json;
+using Microsoft.Extensions.Options;
+
+namespace CrupestApi.Commons.Crud;
+
+/// <summary>
+/// Contains all you need to do with json.
+/// </summary>
+public class EntityJsonHelper<TEntity> where TEntity : class
+{
+ private readonly TableInfo _table;
+ private readonly IOptionsMonitor<JsonSerializerOptions> _jsonSerializerOptions;
+
+ public EntityJsonHelper(ITableInfoFactory tableInfoFactory, IOptionsMonitor<JsonSerializerOptions> jsonSerializerOptions)
+ {
+ _table = tableInfoFactory.Get(typeof(TEntity));
+ _jsonSerializerOptions = jsonSerializerOptions;
+ }
+
+ public Dictionary<string, object?> ConvertEntityToDictionary(TEntity entity, bool includeNonColumnProperties = false)
+ {
+ var result = new Dictionary<string, object?>();
+
+ foreach (var column in _table.PropertyColumns)
+ {
+ var value = column.PropertyInfo!.GetValue(entity);
+ var realValue = column.ColumnType.ConvertToDatabase(value);
+ result[column.ColumnName] = realValue;
+ }
+
+ if (includeNonColumnProperties)
+ {
+ foreach (var propertyInfo in _table.NonColumnProperties)
+ {
+ var value = propertyInfo.GetValue(entity);
+ result[propertyInfo.Name] = value;
+ }
+ }
+
+ return result;
+ }
+
+ public string ConvertEntityToJson(TEntity entity, bool includeNonColumnProperties = false)
+ {
+ var dictionary = ConvertEntityToDictionary(entity, includeNonColumnProperties);
+ return JsonSerializer.Serialize(dictionary, _jsonSerializerOptions.CurrentValue);
+ }
+
+ private object? ConvertJsonValue(JsonElement? optionalJsonElement, Type type, string propertyName)
+ {
+ if (optionalJsonElement is null)
+ {
+ return null;
+ }
+
+ var jsonElement = optionalJsonElement.Value;
+
+ if (jsonElement.ValueKind is JsonValueKind.Null or JsonValueKind.Undefined)
+ {
+ return null;
+ }
+
+ if (jsonElement.ValueKind is JsonValueKind.String)
+ {
+ if (type != typeof(string))
+ {
+ throw new UserException($"Property {propertyName} must be a string.");
+ }
+ return jsonElement.GetString()!;
+ }
+
+ if (jsonElement.ValueKind is JsonValueKind.True or JsonValueKind.False)
+ {
+ if (type != typeof(bool))
+ {
+ throw new UserException($"Property {propertyName} must be a boolean.");
+ }
+ return jsonElement.GetBoolean();
+ }
+
+ if (jsonElement.ValueKind is JsonValueKind.Number)
+ {
+ try
+ {
+ return Convert.ChangeType(jsonElement.GetRawText(), type, CultureInfo.InvariantCulture);
+ }
+ catch (Exception)
+ {
+ throw new UserException($"Property {propertyName} must be a valid number.");
+ }
+ }
+
+ throw new UserException($"Property {propertyName} is of wrong type.");
+ }
+
+ public Dictionary<string, JsonElement> ConvertJsonObjectToDictionary(JsonElement jsonElement)
+ {
+ var result = new Dictionary<string, JsonElement>();
+
+ foreach (var property in jsonElement.EnumerateObject())
+ {
+ result[property.Name.ToLower()] = property.Value;
+ }
+
+ return result;
+ }
+
+ public TEntity ConvertJsonToEntityForInsert(JsonElement jsonElement)
+ {
+ if (jsonElement.ValueKind is not JsonValueKind.Object)
+ throw new ArgumentException("The jsonElement must be an object.");
+
+ var result = Activator.CreateInstance<TEntity>();
+
+ Dictionary<string, JsonElement> jsonProperties = ConvertJsonObjectToDictionary(jsonElement);
+
+ foreach (var column in _table.PropertyColumns)
+ {
+ var jsonPropertyValue = jsonProperties.GetValueOrDefault(column.ColumnName.ToLower());
+ var value = ConvertJsonValue(jsonPropertyValue, column.ColumnType.DatabaseClrType, column.ColumnName);
+ if (column.IsOnlyGenerated && value is not null)
+ {
+ throw new UserException($"Property {column.ColumnName} is auto generated, you cannot set it.");
+ }
+ if (!column.CanBeGenerated && value is null && column.IsNotNull)
+ {
+ throw new UserException($"Property {column.ColumnName} can NOT be generated, you must set it.");
+ }
+ var realValue = column.ColumnType.ConvertFromDatabase(value);
+ column.PropertyInfo!.SetValue(result, realValue);
+ }
+
+ return result;
+ }
+
+ public TEntity ConvertJsonToEntityForInsert(string json)
+ {
+ var jsonElement = JsonSerializer.Deserialize<JsonElement>(json, _jsonSerializerOptions.CurrentValue);
+ return ConvertJsonToEntityForInsert(jsonElement!);
+ }
+
+ public TEntity ConvertJsonToEntityForUpdate(JsonElement jsonElement, out UpdateBehavior updateBehavior)
+ {
+ if (jsonElement.ValueKind is not JsonValueKind.Object)
+ throw new UserException("The jsonElement must be an object.");
+
+ updateBehavior = UpdateBehavior.None;
+
+ Dictionary<string, JsonElement> jsonProperties = ConvertJsonObjectToDictionary(jsonElement);
+
+ bool saveNull = false;
+ if (jsonProperties.TryGetValue("$saveNull".ToLower(), out var saveNullValue))
+ {
+ if (saveNullValue.ValueKind is JsonValueKind.True)
+ {
+ updateBehavior |= UpdateBehavior.SaveNull;
+ saveNull = true;
+ }
+ else if (saveNullValue.ValueKind is JsonValueKind.False)
+ {
+
+ }
+ else
+ {
+ throw new UserException("The $saveNull must be a boolean.");
+ }
+ }
+
+ var result = Activator.CreateInstance<TEntity>();
+ foreach (var column in _table.PropertyColumns)
+ {
+ if (jsonProperties.TryGetValue(column.ColumnName.ToLower(), out var jsonPropertyValue))
+ {
+ if (jsonPropertyValue.ValueKind is JsonValueKind.Null or JsonValueKind.Undefined)
+ {
+ if ((column.IsOnlyGenerated || column.IsNoUpdate) && saveNull)
+ {
+ throw new UserException($"Property {column.ColumnName} is auto generated or not updatable, you cannot set it.");
+ }
+
+ column.PropertyInfo!.SetValue(result, null);
+ }
+ else
+ {
+ if (column.IsOnlyGenerated || column.IsNoUpdate)
+ {
+ throw new UserException($"Property {column.ColumnName} is auto generated or not updatable, you cannot set it.");
+ }
+
+ var value = ConvertJsonValue(jsonPropertyValue, column.ColumnType.DatabaseClrType, column.ColumnName);
+ var realValue = column.ColumnType.ConvertFromDatabase(value);
+ column.PropertyInfo!.SetValue(result, realValue);
+ }
+ }
+ }
+
+ return result;
+ }
+
+ public TEntity ConvertJsonToEntityForUpdate(string json, out UpdateBehavior updateBehavior)
+ {
+ var jsonElement = JsonSerializer.Deserialize<JsonElement>(json, _jsonSerializerOptions.CurrentValue);
+ return ConvertJsonToEntityForUpdate(jsonElement!, out updateBehavior);
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs
new file mode 100644
index 0000000..964a669
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs
@@ -0,0 +1,24 @@
+using Dapper;
+
+namespace CrupestApi.Commons.Crud;
+
+public interface IClause
+{
+ IEnumerable<IClause> GetSubclauses()
+ {
+ return Enumerable.Empty<IClause>();
+ }
+
+ IEnumerable<string> GetRelatedColumns()
+ {
+ var subclauses = GetSubclauses();
+ var result = new List<string>();
+ foreach (var subclause in subclauses)
+ {
+ var columns = subclause.GetRelatedColumns();
+ if (columns is not null)
+ result.AddRange(columns);
+ }
+ return result;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs
new file mode 100644
index 0000000..a880e66
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs
@@ -0,0 +1,77 @@
+using System.Text;
+
+namespace CrupestApi.Commons.Crud;
+
+public class InsertItem
+{
+ /// <summary>
+ /// Null means use default value. Use <see cref="DbNullValue"/>.
+ /// </summary>
+ public InsertItem(string columnName, object? value)
+ {
+ ColumnName = columnName;
+ Value = value;
+ }
+
+ public string ColumnName { get; set; }
+ public object? Value { get; set; }
+}
+
+public interface IInsertClause : IClause
+{
+ List<InsertItem> Items { get; }
+ string GenerateColumnListSql(string? dbProviderId = null);
+ (string sql, ParamList parameters) GenerateValueListSql(string? dbProviderId = null);
+}
+
+public class InsertClause : IInsertClause
+{
+ public List<InsertItem> Items { get; } = new List<InsertItem>();
+
+ public InsertClause(params InsertItem[] items)
+ {
+ Items.AddRange(items);
+ }
+
+ public InsertClause Add(params InsertItem[] items)
+ {
+ Items.AddRange(items);
+ return this;
+ }
+
+ public InsertClause Add(string column, object? value)
+ {
+ return Add(new InsertItem(column, value));
+ }
+
+ public static InsertClause Create(params InsertItem[] items)
+ {
+ return new InsertClause(items);
+ }
+
+ public List<string> GetRelatedColumns()
+ {
+ return Items.Select(i => i.ColumnName).ToList();
+ }
+
+ public string GenerateColumnListSql(string? dbProviderId = null)
+ {
+ return string.Join(", ", Items.Select(i => i.ColumnName));
+ }
+
+ public (string sql, ParamList parameters) GenerateValueListSql(string? dbProviderId = null)
+ {
+ var parameters = new ParamList();
+ var sb = new StringBuilder();
+ for (var i = 0; i < Items.Count; i++)
+ {
+ var item = Items[i];
+ var parameterName = parameters.AddRandomNameParameter(item.Value, item.ColumnName);
+ sb.Append($"@{parameterName}");
+ if (i != Items.Count - 1)
+ sb.Append(", ");
+ }
+
+ return (sb.ToString(), parameters);
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
new file mode 100644
index 0000000..f1ae616
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
@@ -0,0 +1,44 @@
+using System.Data;
+
+namespace CrupestApi.Commons.Crud.Migrations;
+
+public class TableColumn
+{
+ public TableColumn(string name, string type, bool notNull, int primaryKey)
+ {
+ Name = name;
+ Type = type;
+ NotNull = notNull;
+ PrimaryKey = primaryKey;
+ }
+
+ public string Name { get; set; }
+ public string Type { get; set; }
+ public bool NotNull { get; set; }
+
+ /// <summary>
+ /// 0 if not primary key. 1-based index if in primary key.
+ /// </summary>
+ public int PrimaryKey { get; set; }
+}
+
+public class Table
+{
+ public Table(string name)
+ {
+ Name = name;
+ }
+
+ public string Name { get; set; }
+ public List<TableColumn> Columns { get; set; } = new List<TableColumn>();
+}
+
+public interface IDatabaseMigrator
+{
+ Table? GetTable(IDbConnection dbConnection, string tableName);
+ Table ConvertTableInfoToTable(TableInfo tableInfo);
+ string GenerateCreateTableColumnSqlSegment(TableColumn column);
+ string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns);
+ bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo);
+ void AutoMigrate(IDbConnection dbConnection, TableInfo tableInfo);
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
new file mode 100644
index 0000000..33310d6
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
@@ -0,0 +1,175 @@
+using System.Data;
+using System.Text;
+using System.Text.Json;
+using System.Text.RegularExpressions;
+using Dapper;
+
+namespace CrupestApi.Commons.Crud.Migrations;
+
+public class SqliteDatabaseMigrator : IDatabaseMigrator
+{
+ private void CheckTableName(string name)
+ {
+ if (Regex.Match(name, @"^[_0-9a-zA-Z]+$").Success is false)
+ {
+ throw new ArgumentException("Fxxk, what have you passed as table name.");
+ }
+ }
+
+ public Table? GetTable(IDbConnection dbConnection, string tableName)
+ {
+ var count = dbConnection.QuerySingle<int>(
+ "SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND name = @TableName;",
+ new { TableName = tableName });
+ if (count == 0)
+ {
+ return null;
+ }
+ else if (count > 1)
+ {
+ throw new Exception($"More than 1 table has name {tableName}. What happened?");
+ }
+ else
+ {
+ var table = new Table(tableName);
+ var queryColumns = dbConnection.Query<dynamic>($"PRAGMA table_info({tableName})");
+
+ foreach (var column in queryColumns)
+ {
+ var columnName = (string)column.name;
+ var columnType = (string)column.type;
+ var isNullable = Convert.ToBoolean(column.notnull);
+ var primaryKey = Convert.ToInt32(column.pk);
+
+ table.Columns.Add(new TableColumn(columnName, columnType, isNullable, primaryKey));
+ }
+
+ return table;
+ }
+ }
+
+ public Table ConvertTableInfoToTable(TableInfo tableInfo)
+ {
+ var table = new Table(tableInfo.TableName);
+
+ foreach (var columnInfo in tableInfo.Columns)
+ {
+ table.Columns.Add(new TableColumn(columnInfo.ColumnName, columnInfo.ColumnType.GetSqlTypeString(),
+ columnInfo.IsNotNull, columnInfo.IsPrimaryKey ? 1 : 0));
+ }
+
+ return table;
+ }
+
+ public string GenerateCreateTableColumnSqlSegment(TableColumn column)
+ {
+ StringBuilder result = new StringBuilder();
+ result.Append(column.Name);
+ result.Append(' ');
+ result.Append(column.Type);
+ if (column.PrimaryKey is not 0)
+ {
+ result.Append(" PRIMARY KEY AUTOINCREMENT");
+ }
+ else if (column.NotNull)
+ {
+ result.Append(" NOT NULL");
+ }
+
+ return result.ToString();
+ }
+
+ public string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns)
+ {
+ CheckTableName(tableName);
+
+ var sql = $@"
+CREATE TABLE {tableName} (
+ {string.Join(",\n ", columns.Select(GenerateCreateTableColumnSqlSegment))}
+);
+ ".Trim();
+
+ return sql;
+
+ }
+
+ public void AutoMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+ {
+ var tableName = tableInfo.TableName;
+ var databaseTable = GetTable(dbConnection, tableName);
+ var wantedTable = ConvertTableInfoToTable(tableInfo);
+ var databaseTableColumnNames = databaseTable is null ? new List<string>() : databaseTable.Columns.Select(column => column.Name).ToList();
+ var wantedTableColumnNames = wantedTable.Columns.Select(column => column.Name).ToList();
+
+ var notChangeColumns = wantedTableColumnNames.Where(column => databaseTableColumnNames.Contains(column)).ToList();
+ var addColumns = wantedTableColumnNames.Where(column => !databaseTableColumnNames.Contains(column)).ToList();
+
+ if (databaseTable is not null && dbConnection.QuerySingle<int>($"SELECT count(*) FROM {tableName}") > 0)
+ {
+ foreach (var columnName in addColumns)
+ {
+ var columnInfo = tableInfo.GetColumn(columnName);
+ if (!columnInfo.CanBeGenerated)
+ {
+ throw new Exception($"Column {columnName} cannot be generated. So we can't auto-migrate.");
+ }
+ }
+ }
+
+ // We are sqlite, so it's a little bit difficult.
+ using var transaction = dbConnection.BeginTransaction();
+
+ if (databaseTable is not null)
+ {
+ var tempTableName = tableInfo.TableName + "_temp";
+ dbConnection.Execute($"ALTER TABLE {tableName} RENAME TO {tempTableName}", new { TableName = tableName, tempTableName });
+
+ var createTableSql = GenerateCreateTableSql(tableName, wantedTable.Columns);
+ dbConnection.Execute(createTableSql);
+
+ // Copy old data to new table.
+ var originalRows = dbConnection.Query<dynamic>($"SELECT * FROM {tempTableName}").Cast<IDictionary<string, object?>>().ToList();
+ foreach (var originalRow in originalRows)
+ {
+ var parameters = new DynamicParameters();
+
+ foreach (var columnName in notChangeColumns)
+ {
+ parameters.Add(columnName, originalRow[columnName]);
+ }
+
+ foreach (var columnName in addColumns)
+ {
+ parameters.Add(columnName, tableInfo.GetColumn(columnName).GenerateDefaultValue());
+ }
+
+ string columnSql = string.Join(", ", wantedTableColumnNames);
+ string valuesSql = string.Join(", ", wantedTableColumnNames.Select(c => "@" + c));
+
+ string sql = $"INSERT INTO {tableName} ({columnSql}) VALUES {valuesSql})";
+ dbConnection.Execute(sql, parameters);
+ }
+
+ // Finally drop old table
+ dbConnection.Execute($"DROP TABLE {tempTableName}");
+ }
+ else
+ {
+ var createTableSql = GenerateCreateTableSql(tableName, wantedTable.Columns);
+ dbConnection.Execute(createTableSql);
+ }
+
+ // Commit transaction.
+ transaction.Commit();
+ }
+
+ public bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+ {
+ var tableName = tableInfo.TableName;
+ var databaseTable = GetTable(dbConnection, tableName);
+ var wantedTable = ConvertTableInfoToTable(tableInfo);
+ var databaseTableColumns = databaseTable is null ? new HashSet<string>() : new HashSet<string>(databaseTable.Columns.Select(c => c.Name));
+ var wantedTableColumns = new HashSet<string>(wantedTable.Columns.Select(c => c.Name));
+ return !databaseTableColumns.SetEquals(wantedTableColumns);
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
new file mode 100644
index 0000000..734d044
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
@@ -0,0 +1,50 @@
+namespace CrupestApi.Commons.Crud;
+
+public class OrderByItem
+{
+ public OrderByItem(string columnName, bool isAscending)
+ {
+ ColumnName = columnName;
+ IsAscending = isAscending;
+ }
+
+ public string ColumnName { get; }
+ public bool IsAscending { get; }
+
+ public string GenerateSql()
+ {
+ return $"{ColumnName} {(IsAscending ? "ASC" : "DESC")}";
+ }
+}
+
+public interface IOrderByClause : IClause
+{
+ List<OrderByItem> Items { get; }
+ // Contains "ORDER BY" keyword!
+ string GenerateSql(string? dbProviderId = null);
+}
+
+public class OrderByClause : IOrderByClause
+{
+ public List<OrderByItem> Items { get; } = new List<OrderByItem>();
+
+ public OrderByClause(params OrderByItem[] items)
+ {
+ Items.AddRange(items);
+ }
+
+ public static OrderByClause Create(params OrderByItem[] items)
+ {
+ return new OrderByClause(items);
+ }
+
+ public List<string> GetRelatedColumns()
+ {
+ return Items.Select(x => x.ColumnName).ToList();
+ }
+
+ public string GenerateSql(string? dbProviderId = null)
+ {
+ return "ORDER BY " + string.Join(", ", Items.Select(i => i.GenerateSql()));
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs
new file mode 100644
index 0000000..37d77ca
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs
@@ -0,0 +1,73 @@
+using System.Data;
+using System.Diagnostics;
+
+namespace CrupestApi.Commons.Crud;
+
+/// <summary>
+/// <see cref="ColumnName"/> is an optional column name related to the param. You may use it to do some column related things. Like use a more accurate conversion.
+/// </summary>
+/// <remarks>
+/// If value is DbNullValue, it will be treated as null.
+/// </remarks>
+public record ParamInfo(string Name, object? Value, string? ColumnName = null);
+
+public class ParamList : List<ParamInfo>
+{
+ private static Random random = new Random();
+ private const string chars = "abcdefghijklmnopqrstuvwxyz";
+ public static string GenerateRandomKey(int length)
+ {
+ lock (random)
+ {
+ var result = new string(Enumerable.Repeat(chars, length)
+ .Select(s => s[random.Next(s.Length)]).ToArray());
+ return result;
+ }
+ }
+
+ public string GenerateRandomParameterName()
+ {
+ var parameterName = GenerateRandomKey(10);
+ int retryTimes = 1;
+ while (ContainsKey(parameterName))
+ {
+ retryTimes++;
+ Debug.Assert(retryTimes <= 100);
+ parameterName = GenerateRandomKey(10);
+ }
+ return parameterName;
+ }
+
+
+ public bool ContainsKey(string name)
+ {
+ return this.SingleOrDefault(p => p.Name.Equals(name, StringComparison.OrdinalIgnoreCase)) is not null;
+ }
+
+ public T? Get<T>(string key)
+ {
+ return (T?)this.SingleOrDefault(p => p.Name.Equals(key, StringComparison.OrdinalIgnoreCase))?.Value;
+ }
+
+ public object? this[string key]
+ {
+ get
+ {
+ return this.SingleOrDefault(p => p.Name.Equals(key, StringComparison.OrdinalIgnoreCase)) ?? throw new KeyNotFoundException("Key not found.");
+ }
+ }
+
+ public void Add(string name, object? value, string? columnName = null)
+ {
+ Add(new ParamInfo(name, value, columnName));
+ }
+
+ // Return the random name.
+ public string AddRandomNameParameter(object? value, string? columnName = null)
+ {
+ var parameterName = GenerateRandomParameterName();
+ var param = new ParamInfo(parameterName, value, columnName);
+ Add(param);
+ return parameterName;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md
new file mode 100644
index 0000000..b008ea7
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md
@@ -0,0 +1,47 @@
+# CRUD Technic Notes
+
+## Overview
+
+The ultimate CRUD scaffold finally comes.
+
+## Database Pipeline
+
+### Select
+
+1. Create select `what`, where clause, order clause, `Offset` and `Limit`.
+2. Check clauses' related columns are valid.
+3. Generate sql string and param list.
+4. Convert param list to `Dapper` dynamic params with proper type conversion in `IColumnTypeInfo`.
+5. Execute sql and get `dynamic`s.
+6. (Optional) Convert `dynamic`s to `TEntity`s.
+
+### Insert
+
+1. Create insert clause.
+2. Check clauses' related columns are valid.
+3. Create a real empty insert clause.
+4. For each column:
+ 1. If insert item exists and value is not null but the column `IsGenerated` is true, throw exception.
+ 2. If insert item does not exist or value is `null`, use default value generator to generate value. However, `DbNullValue` always means use `NULL` for that column.
+ 3. If value is `null` and the column `IsAutoIncrement` is true, skip to next column.
+ 4. Coerce null to `DbNullValue`.
+ 5. Run validator to validate the value.
+ 6. If value is `DbNullValue`, `IsNotNull` is true, throw exception.
+ 7. Add column and value to real insert clause.
+5. Generate sql string and param list.
+6. Convert param list to `Dapper` dynamic params with proper type conversion in `IColumnTypeInfo`.
+7. Execute sql and return `KeyColumn` value.
+
+### Update
+
+1. Create update clause, where clause.
+2. Check clauses' related columns are valid. Then generate sql string and param list.
+3. Create a real empty update clause.
+4. For each column:
+ 1. If update item exists and value is not null but the column `IsNoUpdate` is true, throw exception.
+ 2. Invoke validator to validate the value.
+ 3. If `IsNotNull` is true and value is `DbNullValue`, throw exception.
+ 4. Add column and value to real update clause.
+5. Generate sql string and param list.
+6. Convert param list to `Dapper` dynamic params with proper type conversion in `IColumnTypeInfo`.
+7. Execute sql and return count of affected rows.
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
new file mode 100644
index 0000000..4a7ea95
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
@@ -0,0 +1,628 @@
+using System.Data;
+using System.Diagnostics;
+using System.Reflection;
+using System.Text;
+using Dapper;
+
+namespace CrupestApi.Commons.Crud;
+
+/// <summary>
+/// Contains all you need to manipulate a table.
+/// </summary>
+public class TableInfo
+{
+ private readonly IColumnTypeProvider _columnTypeProvider;
+ private readonly Lazy<List<string>> _lazyColumnNameList;
+ private readonly ILoggerFactory _loggerFactory;
+ private readonly ILogger<TableInfo> _logger;
+
+ public TableInfo(Type entityType, IColumnTypeProvider columnTypeProvider, ILoggerFactory loggerFactory)
+ : this(entityType.Name, entityType, columnTypeProvider, loggerFactory)
+ {
+ }
+
+ public TableInfo(string tableName, Type entityType, IColumnTypeProvider columnTypeProvider, ILoggerFactory loggerFactory)
+ {
+ _loggerFactory = loggerFactory;
+ _logger = loggerFactory.CreateLogger<TableInfo>();
+
+ _logger.LogInformation("Create TableInfo for entity type '{}'.", entityType.Name);
+
+ _columnTypeProvider = columnTypeProvider;
+
+ TableName = tableName;
+ EntityType = entityType;
+
+
+ var properties = entityType.GetProperties();
+ _logger.LogInformation("Find following properties: {}", string.Join(", ", properties.Select(p => p.Name)));
+
+ var columnInfos = new List<ColumnInfo>();
+
+ bool hasId = false;
+ ColumnInfo? primaryKeyColumn = null;
+ ColumnInfo? keyColumn = null;
+
+ List<PropertyInfo> nonColumnProperties = new();
+
+ foreach (var property in properties)
+ {
+ _logger.LogInformation("Check property '{}'.", property.Name);
+ if (CheckPropertyIsColumn(property))
+ {
+ _logger.LogInformation("{} is a column, create ColumnInfo for it.", property.Name);
+ var columnInfo = new ColumnInfo(this, property, _columnTypeProvider, _loggerFactory);
+ columnInfos.Add(columnInfo);
+ if (columnInfo.IsPrimaryKey)
+ {
+ _logger.LogInformation("Column {} is a primary key.", property.Name);
+ primaryKeyColumn = columnInfo;
+ }
+ if (columnInfo.ColumnName.Equals("id", StringComparison.OrdinalIgnoreCase))
+ {
+ _logger.LogInformation("Column {} has name id.", property.Name);
+ hasId = true;
+ }
+ if (columnInfo.IsSpecifiedAsKey)
+ {
+ if (keyColumn is not null)
+ {
+ throw new Exception("Already exists a key column.");
+ }
+ _logger.LogInformation("Column {} is specified as key.", property.Name);
+ keyColumn = columnInfo;
+ }
+ }
+ else
+ {
+ _logger.LogInformation("{} is not a column.", property.Name);
+ nonColumnProperties.Add(property);
+ }
+ }
+
+ if (primaryKeyColumn is null)
+ {
+ if (hasId) throw new Exception("A column named id already exists but is not primary key.");
+ _logger.LogInformation("No primary key column found, create one automatically.");
+ primaryKeyColumn = CreateAutoIdColumn();
+ columnInfos.Add(primaryKeyColumn);
+ }
+
+ if (keyColumn is null)
+ {
+ _logger.LogInformation("No key column is specified, will use primary key.");
+ keyColumn = primaryKeyColumn;
+ }
+
+ Columns = columnInfos;
+ PrimaryKeyColumn = primaryKeyColumn;
+ KeyColumn = keyColumn;
+ NonColumnProperties = nonColumnProperties;
+
+ _logger.LogInformation("Check table validity.");
+ CheckValidity();
+
+ _logger.LogInformation("TableInfo succeeded to create.");
+
+ _lazyColumnNameList = new Lazy<List<string>>(() => Columns.Select(c => c.ColumnName).ToList());
+ }
+
+ private ColumnInfo CreateAutoIdColumn()
+ {
+ return new ColumnInfo(this,
+ new ColumnAttribute
+ {
+ ColumnName = "Id",
+ NotNull = true,
+ IsPrimaryKey = true,
+ },
+ typeof(long), _columnTypeProvider, _loggerFactory);
+ }
+
+ public Type EntityType { get; }
+ public string TableName { get; }
+ public IReadOnlyList<ColumnInfo> Columns { get; }
+ public IReadOnlyList<ColumnInfo> PropertyColumns => Columns.Where(c => c.PropertyInfo is not null).ToList();
+ public ColumnInfo PrimaryKeyColumn { get; }
+ /// <summary>
+ /// Maybe not the primary key. But acts as primary key.
+ /// </summary>
+ /// <seealso cref="ColumnMetadataKeys.ActAsKey"/>
+ public ColumnInfo KeyColumn { get; }
+ public IReadOnlyList<PropertyInfo> ColumnProperties => PropertyColumns.Select(c => c.PropertyInfo!).ToList();
+ public IReadOnlyList<PropertyInfo> NonColumnProperties { get; }
+ public IReadOnlyList<string> ColumnNameList => _lazyColumnNameList.Value;
+
+ protected bool CheckPropertyIsColumn(PropertyInfo property)
+ {
+ var columnAttribute = property.GetCustomAttribute<ColumnAttribute>();
+ if (columnAttribute is null) return false;
+ return true;
+ }
+
+ public ColumnInfo GetColumn(string columnName)
+ {
+ foreach (var column in Columns)
+ {
+ if (column.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
+ {
+ return column;
+ }
+ }
+ throw new KeyNotFoundException("No such column with given name.");
+ }
+
+ public void CheckGeneratedColumnHasGenerator()
+ {
+ foreach (var column in Columns)
+ {
+ if (column.IsOnlyGenerated && column.DefaultValueGeneratorMethod is null)
+ {
+ throw new Exception($"Column '{column.ColumnName}' is generated but has no generator.");
+ }
+ }
+ }
+
+ public void CheckValidity()
+ {
+ // Check if there is only one primary key.
+ bool hasPrimaryKey = false;
+ bool hasKey = false;
+ foreach (var column in Columns)
+ {
+ if (column.IsPrimaryKey)
+ {
+ if (hasPrimaryKey) throw new Exception("More than one columns are primary key.");
+ hasPrimaryKey = true;
+ }
+
+ if (column.IsSpecifiedAsKey)
+ {
+ if (hasKey) throw new Exception("More than one columns are specified as key column.");
+ }
+ }
+
+ if (!hasPrimaryKey) throw new Exception("No column is primary key.");
+
+ // Check two columns have the same sql name.
+ HashSet<string> sqlNameSet = new HashSet<string>();
+
+ foreach (var column in Columns)
+ {
+ if (sqlNameSet.Contains(column.ColumnName))
+ throw new Exception($"Two columns have the same sql name '{column.ColumnName}'.");
+ sqlNameSet.Add(column.ColumnName);
+ }
+
+ CheckGeneratedColumnHasGenerator();
+ }
+
+ public string GenerateCreateIndexSql(string? dbProviderId = null)
+ {
+ var sb = new StringBuilder();
+
+ foreach (var column in Columns)
+ {
+ if (column.Index == ColumnIndexType.None) continue;
+
+ sb.Append($"CREATE {(column.Index == ColumnIndexType.Unique ? "UNIQUE" : "")} INDEX {TableName}_{column.ColumnName}_index ON {TableName} ({column.ColumnName});\n");
+ }
+
+ return sb.ToString();
+ }
+
+ public string GenerateCreateTableSql(bool createIndex = true, string? dbProviderId = null)
+ {
+ var tableName = TableName;
+ var columnSql = string.Join(",\n", Columns.Select(c => c.GenerateCreateTableColumnString(dbProviderId)));
+
+ var sql = $@"
+CREATE TABLE {tableName}(
+ {columnSql}
+);
+ ";
+
+ if (createIndex)
+ {
+ sql += GenerateCreateIndexSql(dbProviderId);
+ }
+
+ return sql;
+ }
+
+ public void CheckColumnName(string columnName)
+ {
+ if (!ColumnNameList.Contains(columnName))
+ {
+ throw new ArgumentException($"Column {columnName} is not in the table.");
+ }
+ }
+
+ public void CheckRelatedColumns(IClause? clause)
+ {
+ if (clause is not null)
+ {
+ var relatedColumns = clause.GetRelatedColumns();
+ foreach (var column in relatedColumns)
+ {
+ CheckColumnName(column);
+ }
+ }
+ }
+
+ /// <summary>
+ /// If you call this manually, it's your duty to call hooks.
+ /// </summary>
+ /// <seealso cref="SelectDynamic"/>
+ public (string sql, ParamList parameters) GenerateSelectSql(string? selectWhat, IWhereClause? whereClause, IOrderByClause? orderByClause = null, int? skip = null, int? limit = null, string? dbProviderId = null)
+ {
+ CheckRelatedColumns(whereClause);
+ CheckRelatedColumns(orderByClause);
+
+ var parameters = new ParamList();
+
+ StringBuilder result = new StringBuilder()
+ .Append($"SELECT {selectWhat ?? "*"} FROM ")
+ .Append(TableName);
+
+ if (whereClause is not null)
+ {
+ result.Append(" WHERE ");
+ var (whereSql, whereParameters) = whereClause.GenerateSql(dbProviderId);
+ parameters.AddRange(whereParameters);
+ result.Append(whereSql);
+ }
+
+ if (orderByClause is not null)
+ {
+ result.Append(' ');
+ var orderBySql = orderByClause.GenerateSql(dbProviderId);
+ result.Append(orderBySql);
+ }
+
+ if (limit is not null)
+ {
+ result.Append(" LIMIT @Limit");
+ parameters.Add("Limit", limit.Value);
+ }
+
+ if (skip is not null)
+ {
+ result.Append(" OFFSET @Skip");
+ parameters.Add("Skip", skip.Value);
+ }
+
+ result.Append(';');
+
+ return (result.ToString(), parameters);
+ }
+
+ /// <summary>
+ /// If you call this manually, it's your duty to call hooks.
+ /// </summary>
+ /// <seealso cref="Insert"/>
+ public (string sql, ParamList parameters) GenerateInsertSql(IInsertClause insertClause, string? dbProviderId = null)
+ {
+ CheckRelatedColumns(insertClause);
+
+ var parameters = new ParamList();
+
+ var result = new StringBuilder()
+ .Append("INSERT INTO ")
+ .Append(TableName)
+ .Append(" (")
+ .Append(insertClause.GenerateColumnListSql(dbProviderId))
+ .Append(") VALUES (");
+
+ var (valueSql, valueParameters) = insertClause.GenerateValueListSql(dbProviderId);
+ result.Append(valueSql).Append(");");
+
+ parameters.AddRange(valueParameters);
+
+ return (result.ToString(), parameters);
+ }
+
+ /// <summary>
+ /// If you call this manually, it's your duty to call hooks.
+ /// </summary>
+ /// <seealso cref="Update"/>
+ public (string sql, ParamList parameters) GenerateUpdateSql(IWhereClause? whereClause, IUpdateClause updateClause)
+ {
+ CheckRelatedColumns(whereClause);
+ CheckRelatedColumns(updateClause);
+
+ var parameters = new ParamList();
+
+ StringBuilder sb = new StringBuilder("UPDATE ");
+ sb.Append(TableName);
+ sb.Append(" SET ");
+ var (updateSql, updateParameters) = updateClause.GenerateSql();
+ sb.Append(updateSql);
+ parameters.AddRange(updateParameters);
+ if (whereClause is not null)
+ {
+ sb.Append(" WHERE ");
+ var (whereSql, whereParameters) = whereClause.GenerateSql();
+ sb.Append(whereSql);
+ parameters.AddRange(whereParameters);
+ }
+ sb.Append(';');
+
+ return (sb.ToString(), parameters);
+ }
+
+ /// <summary>
+ /// If you call this manually, it's your duty to call hooks.
+ /// </summary>
+ /// <seealso cref="Delete"/>
+ public (string sql, ParamList parameters) GenerateDeleteSql(IWhereClause? whereClause)
+ {
+ CheckRelatedColumns(whereClause);
+
+ var parameters = new ParamList();
+
+ StringBuilder sb = new StringBuilder("DELETE FROM ");
+ sb.Append(TableName);
+ if (whereClause is not null)
+ {
+ sb.Append(" WHERE ");
+ var (whereSql, whereParameters) = whereClause.GenerateSql();
+ parameters.AddRange(whereParameters);
+ sb.Append(whereSql);
+ }
+ sb.Append(';');
+
+ return (sb.ToString(), parameters);
+ }
+
+ private DynamicParameters ConvertParameters(ParamList parameters)
+ {
+ var result = new DynamicParameters();
+ foreach (var param in parameters)
+ {
+ if (param.Value is null || param.Value is DbNullValue)
+ {
+ result.Add(param.Name, null);
+ continue;
+ }
+
+ var columnName = param.ColumnName;
+ IColumnTypeInfo typeInfo;
+ if (columnName is not null)
+ {
+ typeInfo = GetColumn(columnName).ColumnType;
+ }
+ else
+ {
+ typeInfo = _columnTypeProvider.Get(param.Value.GetType());
+ }
+
+ result.Add(param.Name, typeInfo.ConvertToDatabase(param.Value), typeInfo.DbType);
+ }
+ return result;
+ }
+
+ /// <summary>
+ /// ConvertParameters. Select. Call hooks.
+ /// </summary>
+ public virtual List<dynamic> SelectDynamic(IDbConnection dbConnection, string? what = null, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null)
+ {
+ var (sql, parameters) = GenerateSelectSql(what, where, orderBy, skip, limit);
+ var queryResult = dbConnection.Query<dynamic>(sql, ConvertParameters(parameters));
+ return queryResult.ToList();
+ }
+
+ public virtual int SelectCount(IDbConnection dbConnection, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null)
+ {
+ var (sql, parameters) = GenerateSelectSql("COUNT(*)", where, orderBy, skip, limit);
+ var result = dbConnection.QuerySingle<int>(sql, ConvertParameters(parameters));
+ return result;
+ }
+
+ public virtual TResult MapDynamicTo<TResult>(dynamic d)
+ {
+ var dict = (IDictionary<string, object?>)d;
+
+ var result = Activator.CreateInstance<TResult>();
+ Type resultType = typeof(TResult);
+
+ foreach (var column in Columns)
+ {
+ var resultProperty = resultType.GetProperty(column.ColumnName);
+ if (dict.ContainsKey(column.ColumnName) && resultProperty is not null)
+ {
+ if (dict[column.ColumnName] is null)
+ {
+ resultProperty.SetValue(result, null);
+ continue;
+ }
+ object? value = Convert.ChangeType(dict[column.ColumnName], column.ColumnType.DatabaseClrType);
+ value = column.ColumnType.ConvertFromDatabase(value);
+ resultProperty.SetValue(result, value);
+ }
+ }
+
+ return result;
+ }
+
+ /// <summary>
+ /// Select and call hooks.
+ /// </summary>
+ public virtual List<TResult> Select<TResult>(IDbConnection dbConnection, string? what = null, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null)
+ {
+ List<dynamic> queryResult = SelectDynamic(dbConnection, what, where, orderBy, skip, limit).ToList();
+
+ return queryResult.Select(MapDynamicTo<TResult>).ToList();
+ }
+
+ public IInsertClause ConvertEntityToInsertClause(object entity)
+ {
+ Debug.Assert(EntityType.IsInstanceOfType(entity));
+ var result = new InsertClause();
+ foreach (var column in PropertyColumns)
+ {
+ var value = column.PropertyInfo!.GetValue(entity);
+ result.Add(column.ColumnName, value);
+ }
+ return result;
+ }
+
+ /// <summary>
+ /// Insert a entity and call hooks.
+ /// </summary>
+ /// <returns>The key of insert entity.</returns>
+ public int Insert(IDbConnection dbConnection, IInsertClause insert, out object key)
+ {
+ object? finalKey = null;
+
+ var realInsert = InsertClause.Create();
+
+ foreach (var column in Columns)
+ {
+ InsertItem? item = insert.Items.SingleOrDefault(i => i.ColumnName == column.ColumnName);
+
+ var value = item?.Value;
+
+ if (column.IsOnlyGenerated && value is not null)
+ {
+ throw new Exception($"The column '{column.ColumnName}' is auto generated. You can't specify it explicitly.");
+ }
+
+ if (value is null)
+ {
+ value = column.GenerateDefaultValue();
+ }
+
+ if (value is null && column.IsAutoIncrement)
+ {
+ continue;
+ }
+
+ if (value is null)
+ {
+ value = DbNullValue.Instance;
+ }
+
+ column.InvokeValidator(value);
+
+ InsertItem realInsertItem;
+
+ if (value is DbNullValue)
+ {
+ if (column.IsNotNull)
+ {
+ throw new Exception($"Column '{column.ColumnName}' is not nullable. Please specify a non-null value.");
+ }
+
+ realInsertItem = new InsertItem(column.ColumnName, null);
+ }
+ else
+ {
+ realInsertItem = new InsertItem(column.ColumnName, value);
+ }
+
+ realInsert.Add(realInsertItem);
+
+ if (realInsertItem.ColumnName == KeyColumn.ColumnName)
+ {
+ finalKey = realInsertItem.Value;
+ }
+ }
+
+ if (finalKey is null) throw new Exception("No key???");
+ key = finalKey;
+
+ var (sql, parameters) = GenerateInsertSql(realInsert);
+
+ var affectedRowCount = dbConnection.Execute(sql, ConvertParameters(parameters));
+
+ if (affectedRowCount != 1)
+ throw new Exception("Failed to insert.");
+
+ return affectedRowCount;
+ }
+
+ /// <summary>
+ /// Upgrade a entity and call hooks.
+ /// </summary>
+ /// <returns>The key of insert entity.</returns>
+ public virtual int Update(IDbConnection dbConnection, IWhereClause? where, IUpdateClause update, out object? newKey)
+ {
+ newKey = null;
+
+ var realUpdate = UpdateClause.Create();
+
+ foreach (var column in Columns)
+ {
+ UpdateItem? item = update.Items.FirstOrDefault(i => i.ColumnName == column.ColumnName);
+ object? value = item?.Value;
+
+ if (value is not null)
+ {
+ if (column.IsNoUpdate)
+ {
+ throw new Exception($"The column '{column.ColumnName}' can't be update.");
+ }
+
+ column.InvokeValidator(value);
+
+ realUpdate.Add(column.ColumnName, value);
+
+ if (column.ColumnName == KeyColumn.ColumnName)
+ {
+ newKey = value;
+ }
+ }
+ }
+
+ var (sql, parameters) = GenerateUpdateSql(where, realUpdate);
+ return dbConnection.Execute(sql, ConvertParameters(parameters));
+ }
+
+ public virtual int Delete(IDbConnection dbConnection, IWhereClause? where)
+ {
+ var (sql, parameters) = GenerateDeleteSql(where);
+ return dbConnection.Execute(sql, ConvertParameters(parameters));
+ }
+}
+
+public interface ITableInfoFactory
+{
+ TableInfo Get(Type type);
+}
+
+public class TableInfoFactory : ITableInfoFactory
+{
+ private readonly Dictionary<Type, TableInfo> _cache = new Dictionary<Type, TableInfo>();
+ private readonly IColumnTypeProvider _columnTypeProvider;
+ private readonly ILoggerFactory _loggerFactory;
+ private readonly ILogger<TableInfoFactory> _logger;
+
+ public TableInfoFactory(IColumnTypeProvider columnTypeProvider, ILoggerFactory loggerFactory)
+ {
+ _columnTypeProvider = columnTypeProvider;
+ _loggerFactory = loggerFactory;
+ _logger = loggerFactory.CreateLogger<TableInfoFactory>();
+ }
+
+ // This is thread-safe.
+ public TableInfo Get(Type type)
+ {
+ lock (_cache)
+ {
+ if (_cache.TryGetValue(type, out var tableInfo))
+ {
+ _logger.LogDebug("Table info of type '{}' is cached, return it.", type.Name);
+ return tableInfo;
+ }
+ else
+ {
+ _logger.LogDebug("Table info for type '{}' is not in cache, create it.", type.Name);
+ tableInfo = new TableInfo(type, _columnTypeProvider, _loggerFactory);
+ _logger.LogDebug("Table info for type '{}' is created, add it to cache.", type.Name);
+ _cache.Add(type, tableInfo);
+ return tableInfo;
+ }
+ }
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
new file mode 100644
index 0000000..de5c6c3
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
@@ -0,0 +1,77 @@
+using System.Text;
+
+namespace CrupestApi.Commons.Crud;
+
+public class UpdateItem
+{
+ public UpdateItem(string columnName, object? value)
+ {
+ ColumnName = columnName;
+ Value = value;
+ }
+
+ public string ColumnName { get; set; }
+ public object? Value { get; set; }
+}
+
+public interface IUpdateClause : IClause
+{
+ List<UpdateItem> Items { get; }
+ (string sql, ParamList parameters) GenerateSql();
+}
+
+public class UpdateClause : IUpdateClause
+{
+ public List<UpdateItem> Items { get; } = new List<UpdateItem>();
+
+ public UpdateClause(IEnumerable<UpdateItem> items)
+ {
+ Items.AddRange(items);
+ }
+
+ public UpdateClause(params UpdateItem[] items)
+ {
+ Items.AddRange(items);
+ }
+
+ public UpdateClause Add(params UpdateItem[] items)
+ {
+ Items.AddRange(items);
+ return this;
+ }
+
+ public UpdateClause Add(string column, object? value)
+ {
+ return Add(new UpdateItem(column, value));
+ }
+
+ public static UpdateClause Create(params UpdateItem[] items)
+ {
+ return new UpdateClause(items);
+ }
+
+ public List<string> GetRelatedColumns()
+ {
+ return Items.Select(i => i.ColumnName).ToList();
+ }
+
+ public (string sql, ParamList parameters) GenerateSql()
+ {
+ var parameters = new ParamList();
+
+ StringBuilder result = new StringBuilder();
+
+ foreach (var item in Items)
+ {
+ if (result.Length > 0)
+ {
+ result.Append(", ");
+ }
+
+ var parameterName = parameters.AddRandomNameParameter(item.Value, item.ColumnName);
+ result.Append($"{item.ColumnName} = @{parameterName}");
+ }
+
+ return (result.ToString(), parameters);
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UserException.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UserException.cs
new file mode 100644
index 0000000..1a10b97
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UserException.cs
@@ -0,0 +1,15 @@
+namespace CrupestApi.Commons.Crud;
+
+/// <summary>
+/// This exception means the exception is caused by user and can be safely shown to user.
+/// </summary>
+[System.Serializable]
+public class UserException : Exception
+{
+ public UserException() { }
+ public UserException(string message) : base(message) { }
+ public UserException(string message, System.Exception inner) : base(message, inner) { }
+ protected UserException(
+ System.Runtime.Serialization.SerializationInfo info,
+ System.Runtime.Serialization.StreamingContext context) : base(info, context) { }
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
new file mode 100644
index 0000000..de69f2f
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
@@ -0,0 +1,182 @@
+using System.Text;
+
+namespace CrupestApi.Commons.Crud;
+
+public interface IWhereClause : IClause
+{
+ // Does not contain "WHERE" keyword!
+ (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null);
+}
+
+public class CompositeWhereClause : IWhereClause
+{
+ public CompositeWhereClause(string concatOp, bool parenthesesSubclause, params IWhereClause[] subclauses)
+ {
+ ConcatOp = concatOp;
+ ParenthesesSubclause = parenthesesSubclause;
+ Subclauses = subclauses.ToList();
+ }
+
+ public string ConcatOp { get; }
+ public bool ParenthesesSubclause { get; }
+ public List<IWhereClause> Subclauses { get; }
+
+ public CompositeWhereClause Eq(string column, object? value)
+ {
+ Subclauses.Add(SimpleCompareWhereClause.Eq(column, value));
+ return this;
+ }
+
+ public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null)
+ {
+ var parameters = new ParamList();
+ var sql = new StringBuilder();
+ var subclauses = GetSubclauses();
+ if (subclauses is null) return ("", new());
+ var first = true;
+ foreach (var subclause in Subclauses)
+ {
+ var (subSql, subParameters) = subclause.GenerateSql(dbProviderId);
+ if (subSql is null) continue;
+ if (first)
+ {
+ first = false;
+ }
+ else
+ {
+ sql.Append($" {ConcatOp} ");
+ }
+ if (ParenthesesSubclause)
+ {
+ sql.Append("(");
+ }
+ sql.Append(subSql);
+ if (ParenthesesSubclause)
+ {
+ sql.Append(")");
+ }
+ parameters.AddRange(subParameters);
+ }
+ return (sql.ToString(), parameters);
+ }
+
+ public object GetSubclauses()
+ {
+ return Subclauses;
+ }
+}
+
+public class AndWhereClause : CompositeWhereClause
+{
+ public AndWhereClause(params IWhereClause[] clauses)
+ : this(true, clauses)
+ {
+
+ }
+
+ public AndWhereClause(bool parenthesesSubclause, params IWhereClause[] clauses)
+ : base("AND", parenthesesSubclause, clauses)
+ {
+
+ }
+
+ public static AndWhereClause Create(params IWhereClause[] clauses)
+ {
+ return new AndWhereClause(clauses);
+ }
+}
+
+public class OrWhereClause : CompositeWhereClause
+{
+ public OrWhereClause(params IWhereClause[] clauses)
+ : this(true, clauses)
+ {
+
+ }
+
+ public OrWhereClause(bool parenthesesSubclause, params IWhereClause[] clauses)
+ : base("OR", parenthesesSubclause, clauses)
+ {
+
+ }
+
+ public static OrWhereClause Create(params IWhereClause[] clauses)
+ {
+ return new OrWhereClause(clauses);
+ }
+}
+
+// It's simple because it only compare column and value but not expressions.
+public class SimpleCompareWhereClause : IWhereClause
+{
+ public string Column { get; }
+ public string Operator { get; }
+ public object? Value { get; }
+
+ public List<string> GetRelatedColumns()
+ {
+ return new List<string> { Column };
+ }
+
+ // It's user's responsibility to keep column safe, with proper escape.
+ public SimpleCompareWhereClause(string column, string op, object? value)
+ {
+ Column = column;
+ Operator = op;
+ Value = value;
+ }
+
+ public static SimpleCompareWhereClause Create(string column, string op, object? value)
+ {
+ return new SimpleCompareWhereClause(column, op, value);
+ }
+
+ public static SimpleCompareWhereClause Eq(string column, object? value)
+ {
+ return new SimpleCompareWhereClause(column, "=", value);
+ }
+
+ public static SimpleCompareWhereClause Neq(string column, object? value)
+ {
+ return new SimpleCompareWhereClause(column, "<>", value);
+ }
+
+ public static SimpleCompareWhereClause Gt(string column, object? value)
+ {
+ return new SimpleCompareWhereClause(column, ">", value);
+ }
+
+ public static SimpleCompareWhereClause Gte(string column, object? value)
+ {
+ return new SimpleCompareWhereClause(column, ">=", value);
+ }
+
+ public static SimpleCompareWhereClause Lt(string column, object? value)
+ {
+ return new SimpleCompareWhereClause(column, "<", value);
+ }
+
+ public static SimpleCompareWhereClause Lte(string column, object? value)
+ {
+ return new SimpleCompareWhereClause(column, "<=", value);
+ }
+
+ public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null)
+ {
+ var parameters = new ParamList();
+ var parameterName = parameters.AddRandomNameParameter(Value, Column);
+ return ($"{Column} {Operator} @{parameterName}", parameters);
+ }
+}
+
+public class WhereClause : AndWhereClause
+{
+ public WhereClause()
+ {
+ }
+
+ public void Add(IWhereClause subclause)
+ {
+ Subclauses.Add(subclause);
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj
new file mode 100644
index 0000000..8e291fa
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj
@@ -0,0 +1,16 @@
+<Project Sdk="Microsoft.NET.Sdk.Web">
+
+ <PropertyGroup>
+ <TargetFramework>net7.0</TargetFramework>
+ <TargetType>library</TargetType>
+ <Nullable>enable</Nullable>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <SelfContained>false</SelfContained>
+ </PropertyGroup>
+
+ <ItemGroup>
+ <PackageReference Include="Dapper" Version="2.0.123" />
+ <PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.0" />
+ </ItemGroup>
+
+</Project>
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/EntityNotExistException.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/EntityNotExistException.cs
new file mode 100644
index 0000000..0e1f4f4
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/EntityNotExistException.cs
@@ -0,0 +1,8 @@
+namespace CrupestApi.Commons;
+
+public class EntityNotExistException : Exception
+{
+ public EntityNotExistException() { }
+ public EntityNotExistException(string message) : base(message) { }
+ public EntityNotExistException(string message, Exception inner) : base(message, inner) { }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/HttpContextExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/HttpContextExtensions.cs
new file mode 100644
index 0000000..a0b2d89
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/HttpContextExtensions.cs
@@ -0,0 +1,113 @@
+using System.Text.Json;
+using CrupestApi.Commons.Secrets;
+using Microsoft.Extensions.Options;
+
+namespace CrupestApi.Commons;
+
+public delegate void HttpResponseAction(HttpResponse response);
+
+public class MessageBody
+{
+ public MessageBody(string message)
+ {
+ Message = message;
+ }
+
+ public string Message { get; set; }
+}
+
+public static class CrupestApiJsonExtensions
+{
+ public static IServiceCollection AddJsonOptions(this IServiceCollection services)
+ {
+ services.AddOptions<JsonSerializerOptions>();
+ services.Configure<JsonSerializerOptions>(config =>
+ {
+ config.AllowTrailingCommas = true;
+ config.PropertyNameCaseInsensitive = true;
+ config.PropertyNamingPolicy = JsonNamingPolicy.CamelCase;
+ });
+
+ return services;
+ }
+
+ public static async Task<JsonDocument> ReadJsonAsync(this HttpRequest request)
+ {
+ var jsonOptions = request.HttpContext.RequestServices.GetRequiredService<IOptionsSnapshot<JsonSerializerOptions>>();
+ using var stream = request.Body;
+ var body = await JsonSerializer.DeserializeAsync<JsonDocument>(stream, jsonOptions.Value);
+ return body!;
+ }
+
+ public static async Task WriteJsonAsync<T>(this HttpResponse response, T bodyObject, int statusCode = 200, HttpResponseAction? beforeWriteBody = null, CancellationToken cancellationToken = default)
+ {
+ var jsonOptions = response.HttpContext.RequestServices.GetRequiredService<IOptionsSnapshot<JsonSerializerOptions>>();
+ byte[] json = JsonSerializer.SerializeToUtf8Bytes<T>(bodyObject, jsonOptions.Value);
+
+ var byteCount = json.Length;
+
+ response.StatusCode = statusCode;
+ response.Headers.ContentType = "application/json; charset=utf-8";
+ response.Headers.ContentLength = byteCount;
+
+ if (beforeWriteBody is not null)
+ {
+ beforeWriteBody(response);
+ }
+
+ await response.Body.WriteAsync(json, cancellationToken);
+ }
+
+ public static async Task WriteMessageAsync(this HttpResponse response, string message, int statusCode = 400, HttpResponseAction? beforeWriteBody = null, CancellationToken cancellationToken = default)
+ {
+ await response.WriteJsonAsync(new MessageBody(message), statusCode: statusCode, beforeWriteBody, cancellationToken);
+ }
+
+ public static Task ResponseJsonAsync<T>(this HttpContext context, T bodyObject, int statusCode = 200, HttpResponseAction? beforeWriteBody = null, CancellationToken cancellationToken = default)
+ {
+ return context.Response.WriteJsonAsync<T>(bodyObject, statusCode, beforeWriteBody, cancellationToken);
+ }
+
+ public static Task ResponseMessageAsync(this HttpContext context, string message, int statusCode = 400, HttpResponseAction? beforeWriteBody = null, CancellationToken cancellationToken = default)
+ {
+ return context.Response.WriteMessageAsync(message, statusCode, beforeWriteBody, cancellationToken);
+ }
+
+ public static string? GetToken(this HttpRequest request)
+ {
+ var token = request.Headers["Authorization"].ToString();
+ if (token.StartsWith("Bearer "))
+ {
+ token = token.Substring("Bearer ".Length);
+ return token;
+ }
+
+ if (request.Query.TryGetValue("token", out var tokenValues))
+ {
+ return tokenValues.Last();
+ }
+
+ return null;
+ }
+
+ public static bool RequirePermission(this HttpContext context, string? permission)
+ {
+ if (permission is null) return true;
+
+ var token = context.Request.GetToken();
+ if (token is null)
+ {
+ context.ResponseMessageAsync("Unauthorized", 401);
+ return false;
+ }
+
+ var secretService = context.RequestServices.GetRequiredService<ISecretService>();
+ var permissions = secretService.GetPermissions(token);
+ if (!permissions.Contains(permission))
+ {
+ context.ResponseMessageAsync("Forbidden", 403);
+ return false;
+ }
+ return true;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/ISecretService.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/ISecretService.cs
new file mode 100644
index 0000000..83025f8
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/ISecretService.cs
@@ -0,0 +1,8 @@
+namespace CrupestApi.Commons.Secrets;
+
+public interface ISecretService
+{
+ void CreateTestSecret(string key, string secret);
+
+ List<string> GetPermissions(string secret);
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretInfo.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretInfo.cs
new file mode 100644
index 0000000..c3a4de0
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretInfo.cs
@@ -0,0 +1,48 @@
+using System.Security.Cryptography;
+using System.Text;
+using CrupestApi.Commons.Crud;
+
+namespace CrupestApi.Commons.Secrets;
+
+public class SecretInfo
+{
+ [Column(NotNull = true)]
+ public string Key { get; set; } = default!;
+ [Column(NotNull = true, NoUpdate = true, ActAsKey = true)]
+ public string Secret { get; set; } = default!;
+ [Column(DefaultEmptyForString = true)]
+ public string Description { get; set; } = default!;
+ [Column(NotNull = false)]
+ public DateTime? ExpireTime { get; set; }
+ [Column(NotNull = true, DefaultValue = false)]
+ public bool Revoked { get; set; }
+ [Column(NotNull = true)]
+ public DateTime CreateTime { get; set; }
+
+ private static RandomNumberGenerator RandomNumberGenerator = RandomNumberGenerator.Create();
+
+ private static string GenerateRandomKey(int length)
+ {
+ const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
+ var result = new StringBuilder(length);
+ lock (RandomNumberGenerator)
+ {
+ for (int i = 0; i < length; i++)
+ {
+ result.Append(chars[RandomNumberGenerator.GetInt32(chars.Length)]);
+ }
+ }
+ return result.ToString();
+ }
+
+
+ public static string SecretDefaultValueGenerator()
+ {
+ return GenerateRandomKey(16);
+ }
+
+ public static DateTime CreateTimeDefaultValueGenerator()
+ {
+ return DateTime.UtcNow;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs
new file mode 100644
index 0000000..c693d8d
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs
@@ -0,0 +1,48 @@
+using System.Data;
+using CrupestApi.Commons.Crud;
+using CrupestApi.Commons.Crud.Migrations;
+
+namespace CrupestApi.Commons.Secrets;
+
+public class SecretService : CrudService<SecretInfo>, ISecretService
+{
+ private readonly ILogger<SecretService> _logger;
+
+ public SecretService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory)
+ : base(tableInfoFactory, dbConnectionFactory, migrator, loggerFactory)
+ {
+ _logger = loggerFactory.CreateLogger<SecretService>();
+ }
+
+ protected override void AfterMigrate(IDbConnection connection, TableInfo table)
+ {
+ if (table.SelectCount(connection) == 0)
+ {
+ _logger.LogInformation("No secrets found, insert default secrets.");
+ using var transaction = connection.BeginTransaction();
+ var insertClause = InsertClause.Create()
+ .Add(nameof(SecretInfo.Key), SecretsConstants.SecretManagementKey)
+ .Add(nameof(SecretInfo.Secret), "crupest")
+ .Add(nameof(SecretInfo.Description), "This is the init key. Please revoke it immediately after creating a new one.");
+ _table.Insert(connection, insertClause, out var _);
+ transaction.Commit();
+ }
+ }
+
+ public void CreateTestSecret(string key, string secret)
+ {
+ var connection = _dbConnection;
+ var insertClause = InsertClause.Create()
+ .Add(nameof(SecretInfo.Key), key)
+ .Add(nameof(SecretInfo.Secret), secret)
+ .Add(nameof(SecretInfo.Description), "Test secret.");
+ _table.Insert(connection, insertClause, out var _);
+ }
+
+ public List<string> GetPermissions(string secret)
+ {
+ var list = _table.Select<SecretInfo>(_dbConnection,
+ where: WhereClause.Create().Eq(nameof(SecretInfo.Secret), secret));
+ return list.Select(x => x.Key).ToList();
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretServiceCollectionExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretServiceCollectionExtensions.cs
new file mode 100644
index 0000000..a9c0e5f
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretServiceCollectionExtensions.cs
@@ -0,0 +1,12 @@
+using Microsoft.Extensions.DependencyInjection.Extensions;
+
+namespace CrupestApi.Commons.Secrets;
+
+public static class SecretServiceCollectionExtensions
+{
+ public static IServiceCollection AddSecrets(this IServiceCollection services)
+ {
+ services.TryAddScoped<ISecretService, SecretService>();
+ return services;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretsConstants.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretsConstants.cs
new file mode 100644
index 0000000..207cc45
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretsConstants.cs
@@ -0,0 +1,6 @@
+namespace CrupestApi.Commons.Secrets;
+
+public static class SecretsConstants
+{
+ public const string SecretManagementKey = "crupest.secrets.management";
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/CrupestApi.Files.csproj b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/CrupestApi.Files.csproj
new file mode 100644
index 0000000..2221809
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/CrupestApi.Files.csproj
@@ -0,0 +1,20 @@
+<Project Sdk="Microsoft.NET.Sdk.Web">
+
+ <ItemGroup>
+ <ProjectReference Include="..\CrupestApi.Commons\CrupestApi.Commons.csproj" />
+ </ItemGroup>
+
+ <ItemGroup>
+ <PackageReference Include="Dapper" Version="2.0.123" />
+ <PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.0" />
+ </ItemGroup>
+
+ <PropertyGroup>
+ <TargetFramework>net7.0</TargetFramework>
+ <TargetType>library</TargetType>
+ <Nullable>enable</Nullable>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <SelfContained>false</SelfContained>
+ </PropertyGroup>
+
+</Project>
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/FilesService.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/FilesService.cs
new file mode 100644
index 0000000..c851a92
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Files/FilesService.cs
@@ -0,0 +1,6 @@
+namespace CrupestApi.Files;
+
+public class FilesService
+{
+
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/CrupestApi.Secrets.csproj b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/CrupestApi.Secrets.csproj
new file mode 100644
index 0000000..70c83f3
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/CrupestApi.Secrets.csproj
@@ -0,0 +1,20 @@
+<Project Sdk="Microsoft.NET.Sdk.Web">
+
+ <ItemGroup>
+ <ProjectReference Include="..\CrupestApi.Commons\CrupestApi.Commons.csproj" />
+ </ItemGroup>
+
+ <ItemGroup>
+ <PackageReference Include="Dapper" Version="2.0.123" />
+ <PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.0" />
+ </ItemGroup>
+
+ <PropertyGroup>
+ <TargetFramework>net7.0</TargetFramework>
+ <TargetType>library</TargetType>
+ <Nullable>enable</Nullable>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <SelfContained>false</SelfContained>
+ </PropertyGroup>
+
+</Project>
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsExtensions.cs
new file mode 100644
index 0000000..e09887b
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsExtensions.cs
@@ -0,0 +1,19 @@
+using CrupestApi.Commons.Secrets;
+using CrupestApi.Commons.Crud;
+
+namespace CrupestApi.Secrets;
+
+public static class SecretsExtensions
+{
+ public static IServiceCollection AddSecrets(this IServiceCollection services)
+ {
+ services.AddCrud<SecretInfo, SecretService>();
+ return services;
+ }
+
+ public static WebApplication MapSecrets(this WebApplication webApplication, string path = "/api/secrets")
+ {
+ webApplication.MapCrud<SecretInfo>(path, SecretsConstants.SecretManagementKey);
+ return webApplication;
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/CrupestApi.Todos.csproj b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/CrupestApi.Todos.csproj
new file mode 100644
index 0000000..86460e3
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/CrupestApi.Todos.csproj
@@ -0,0 +1,15 @@
+<Project Sdk="Microsoft.NET.Sdk.Web">
+
+ <ItemGroup>
+ <ProjectReference Include="..\CrupestApi.Commons\CrupestApi.Commons.csproj" />
+ </ItemGroup>
+
+ <PropertyGroup>
+ <TargetFramework>net7.0</TargetFramework>
+ <TargetType>library</TargetType>
+ <Nullable>enable</Nullable>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <SelfContained>false</SelfContained>
+ </PropertyGroup>
+
+</Project>
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosConfiguration.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosConfiguration.cs
new file mode 100644
index 0000000..e8160d2
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosConfiguration.cs
@@ -0,0 +1,14 @@
+using System.ComponentModel.DataAnnotations;
+
+namespace CrupestApi.Todos;
+
+public class TodosConfiguration
+{
+ [Required]
+ public string Username { get; set; } = default!;
+ [Required]
+ public int ProjectNumber { get; set; } = default!;
+ [Required]
+ public string Token { get; set; } = default!;
+ public int Count { get; set; }
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosService.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosService.cs
new file mode 100644
index 0000000..5839086
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosService.cs
@@ -0,0 +1,163 @@
+using System.Net.Http.Headers;
+using System.Net.Mime;
+using System.Text;
+using System.Text.Json;
+using Microsoft.Extensions.Options;
+
+namespace CrupestApi.Todos;
+
+public class TodosItem
+{
+ public string Status { get; set; } = default!;
+ public string Title { get; set; } = default!;
+ public bool Closed { get; set; }
+ public string Color { get; set; } = default!;
+}
+
+public class TodosService
+{
+ private readonly IOptionsSnapshot<TodosConfiguration> _options;
+ private readonly ILogger<TodosService> _logger;
+
+ public TodosService(IOptionsSnapshot<TodosConfiguration> options, ILogger<TodosService> logger)
+ {
+ _options = options;
+ _logger = logger;
+ }
+
+ private static string CreateGraphQLQuery(TodosConfiguration todoConfiguration)
+ {
+ return $$"""
+{
+ user(login: "{{todoConfiguration.Username}}") {
+ projectV2(number: {{todoConfiguration.ProjectNumber}}) {
+ items(last: {{todoConfiguration.Count}}) {
+ nodes {
+ fieldValueByName(name: "Status") {
+ ... on ProjectV2ItemFieldSingleSelectValue {
+ name
+ }
+ }
+ content {
+ __typename
+ ... on Issue {
+ title
+ closed
+ }
+ ... on PullRequest {
+ title
+ closed
+ }
+ ... on DraftIssue {
+ title
+ }
+ }
+ }
+ }
+ }
+ }
+}
+""";
+ }
+
+
+ public async Task<List<TodosItem>> GetTodosAsync()
+ {
+ var todoOptions = _options.Value;
+ if (todoOptions is null)
+ {
+ throw new Exception("Fail to get todos configuration.");
+ }
+
+ _logger.LogInformation("Username: {}; ProjectNumber: {}; Count: {}", todoOptions.Username, todoOptions.ProjectNumber, todoOptions.Count);
+ _logger.LogInformation("Getting todos from GitHub GraphQL API...");
+
+ using var httpClient = new HttpClient();
+
+ using var requestContent = new StringContent(JsonSerializer.Serialize(new
+ {
+ query = CreateGraphQLQuery(todoOptions)
+ }));
+ requestContent.Headers.ContentType = new MediaTypeHeaderValue(MediaTypeNames.Application.Json, Encoding.UTF8.WebName);
+
+ using var request = new HttpRequestMessage(HttpMethod.Post, "https://api.github.com/graphql");
+ request.Content = requestContent;
+ request.Headers.Authorization = new AuthenticationHeaderValue("Bearer", todoOptions.Token);
+ request.Headers.TryAddWithoutValidation("User-Agent", todoOptions.Username);
+
+ using var response = await httpClient.SendAsync(request);
+ var responseBody = await response.Content.ReadAsStringAsync();
+
+ _logger.LogInformation("GitHub server returned status code: {}", response.StatusCode);
+ _logger.LogInformation("GitHub server returned body: {}", responseBody);
+
+ if (response.IsSuccessStatusCode)
+ {
+ using var responseJson = JsonSerializer.Deserialize<JsonDocument>(responseBody);
+ if (responseJson is null)
+ {
+ throw new Exception("Fail to deserialize response body.");
+ }
+
+ var nodes = responseJson.RootElement.GetProperty("data").GetProperty("user").GetProperty("projectV2").GetProperty("items").GetProperty("nodes").EnumerateArray();
+
+ var result = new List<TodosItem>();
+
+ foreach (var node in nodes)
+ {
+ var content = node.GetProperty("content");
+ var title = content.GetProperty("title").GetString();
+ if (title is null)
+ {
+ throw new Exception("Fail to get title.");
+ }
+
+ bool done = false;
+
+ var statusField = node.GetProperty("fieldValueByName");
+ if (statusField.ValueKind != JsonValueKind.Null) // if there is a "Status" field
+ {
+ var statusName = statusField.GetProperty("name").GetString();
+ if (statusName is null)
+ {
+ throw new Exception("Fail to get status.");
+ }
+
+ // if name is "Done", then it is closed, otherwise we check if the issue is closed
+ if (statusName.Equals("Done", StringComparison.OrdinalIgnoreCase))
+ {
+ done = true;
+ }
+ }
+
+ JsonElement closedElement;
+ // if item has a "closed" field, then it is a pull request or an issue, and we check if it is closed
+ if (content.TryGetProperty("closed", out closedElement) && closedElement.GetBoolean())
+ {
+ done = true;
+ }
+
+ // If item "Status" field is "Done' or item is a pull request or issue and it is closed, then it is done.
+ // Otherwise it is not closed. Like:
+ // 1. it is a draft issue with no "Status" field or "Status" field is not "Done"
+ // 2. it is a pull request or issue with no "Status" field or "Status" field is not "Done" and it is not closed
+
+ result.Add(new TodosItem
+ {
+ Title = title,
+ Status = done ? "Done" : "Todo",
+ Closed = done,
+ Color = done ? "green" : "blue"
+ });
+ }
+
+ return result;
+ }
+ else
+ {
+ const string message = "Fail to get todos from GitHub.";
+ _logger.LogError(message);
+ throw new Exception(message);
+ }
+ }
+}
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosServiceCollectionExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosServiceCollectionExtensions.cs
new file mode 100644
index 0000000..a49d55d
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosServiceCollectionExtensions.cs
@@ -0,0 +1,21 @@
+using Microsoft.Extensions.DependencyInjection.Extensions;
+
+namespace CrupestApi.Todos;
+
+public static class TodosServiceCollectionExtensions
+{
+ public static IServiceCollection AddTodos(this IServiceCollection services)
+ {
+ services.AddOptions<TodosConfiguration>().BindConfiguration("CrupestApi:Todos");
+ services.PostConfigure<TodosConfiguration>(config =>
+ {
+ if (config.Count == 0)
+ {
+ config.Count = 20;
+ }
+ });
+ services.TryAddScoped<TodosService>();
+ return services;
+ }
+}
+
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosWebApplicationExtensions.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosWebApplicationExtensions.cs
new file mode 100644
index 0000000..0ff05a0
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Todos/TodosWebApplicationExtensions.cs
@@ -0,0 +1,32 @@
+using CrupestApi.Commons;
+
+namespace CrupestApi.Todos;
+
+public static class TodosWebApplicationExtensions
+{
+ public static WebApplication MapTodos(this WebApplication app, string path)
+ {
+ if (app is null)
+ {
+ throw new ArgumentNullException(nameof(app));
+ }
+
+ app.MapGet(path, async (context) =>
+ {
+ var todosService = context.RequestServices.GetRequiredService<TodosService>();
+
+ try
+ {
+ var todos = await todosService.GetTodosAsync();
+ await context.Response.WriteJsonAsync(todos);
+
+ }
+ catch (Exception e)
+ {
+ await context.Response.WriteMessageAsync(e.Message, statusCode: StatusCodes.Status503ServiceUnavailable);
+ }
+ });
+
+ return app;
+ }
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.sln b/dropped/docker/crupest-api/CrupestApi/CrupestApi.sln
new file mode 100644
index 0000000..ebfd960
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.sln
@@ -0,0 +1,46 @@
+
+Microsoft Visual Studio Solution File, Format Version 12.00
+# Visual Studio Version 17
+VisualStudioVersion = 17.0.31903.59
+MinimumVisualStudioVersion = 10.0.40219.1
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "CrupestApi", "CrupestApi\CrupestApi.csproj", "{E30916BB-08F9-45F0-BC1A-69B66AE79913}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "CrupestApi.Todos", "CrupestApi.Todos\CrupestApi.Todos.csproj", "{BF9F5F71-AE65-4896-8E6F-FE0D4AD0E7D1}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "CrupestApi.Secrets", "CrupestApi.Secrets\CrupestApi.Secrets.csproj", "{9A7CC9F9-70CB-408A-ADFC-5119C0BDB236}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "CrupestApi.Commons", "CrupestApi.Commons\CrupestApi.Commons.csproj", "{38083CCA-E56C-4D24-BAB6-EEC30E0F478F}"
+EndProject
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "CrupestApi.Commons.Tests", "CrupestApi.Commons.Tests\CrupestApi.Commons.Tests.csproj", "{0D0304BF-6A18-444C-BAF4-6ABFF98A0F77}"
+EndProject
+Global
+ GlobalSection(SolutionConfigurationPlatforms) = preSolution
+ Debug|Any CPU = Debug|Any CPU
+ Release|Any CPU = Release|Any CPU
+ EndGlobalSection
+ GlobalSection(SolutionProperties) = preSolution
+ HideSolutionNode = FALSE
+ EndGlobalSection
+ GlobalSection(ProjectConfigurationPlatforms) = postSolution
+ {E30916BB-08F9-45F0-BC1A-69B66AE79913}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {E30916BB-08F9-45F0-BC1A-69B66AE79913}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {E30916BB-08F9-45F0-BC1A-69B66AE79913}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {E30916BB-08F9-45F0-BC1A-69B66AE79913}.Release|Any CPU.Build.0 = Release|Any CPU
+ {BF9F5F71-AE65-4896-8E6F-FE0D4AD0E7D1}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {BF9F5F71-AE65-4896-8E6F-FE0D4AD0E7D1}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {BF9F5F71-AE65-4896-8E6F-FE0D4AD0E7D1}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {BF9F5F71-AE65-4896-8E6F-FE0D4AD0E7D1}.Release|Any CPU.Build.0 = Release|Any CPU
+ {9A7CC9F9-70CB-408A-ADFC-5119C0BDB236}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {9A7CC9F9-70CB-408A-ADFC-5119C0BDB236}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {9A7CC9F9-70CB-408A-ADFC-5119C0BDB236}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {9A7CC9F9-70CB-408A-ADFC-5119C0BDB236}.Release|Any CPU.Build.0 = Release|Any CPU
+ {38083CCA-E56C-4D24-BAB6-EEC30E0F478F}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {38083CCA-E56C-4D24-BAB6-EEC30E0F478F}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {38083CCA-E56C-4D24-BAB6-EEC30E0F478F}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {38083CCA-E56C-4D24-BAB6-EEC30E0F478F}.Release|Any CPU.Build.0 = Release|Any CPU
+ {0D0304BF-6A18-444C-BAF4-6ABFF98A0F77}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {0D0304BF-6A18-444C-BAF4-6ABFF98A0F77}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {0D0304BF-6A18-444C-BAF4-6ABFF98A0F77}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {0D0304BF-6A18-444C-BAF4-6ABFF98A0F77}.Release|Any CPU.Build.0 = Release|Any CPU
+ EndGlobalSection
+EndGlobal
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi/CrupestApi.csproj b/dropped/docker/crupest-api/CrupestApi/CrupestApi/CrupestApi.csproj
new file mode 100644
index 0000000..5954f00
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi/CrupestApi.csproj
@@ -0,0 +1,17 @@
+<Project Sdk="Microsoft.NET.Sdk.Web">
+
+ <ItemGroup>
+ <ProjectReference Include="..\CrupestApi.Todos\CrupestApi.Todos.csproj" />
+ <ProjectReference Include="..\CrupestApi.Files\CrupestApi.Files.csproj" />
+ <ProjectReference Include="..\CrupestApi.Commons\CrupestApi.Commons.csproj" />
+ <ProjectReference Include="..\CrupestApi.Secrets\CrupestApi.Secrets.csproj" />
+ </ItemGroup>
+
+ <PropertyGroup>
+ <TargetFramework>net7.0</TargetFramework>
+ <Nullable>enable</Nullable>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <SelfContained>false</SelfContained>
+ </PropertyGroup>
+
+</Project>
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi/Program.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi/Program.cs
new file mode 100644
index 0000000..46648d9
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi/Program.cs
@@ -0,0 +1,24 @@
+using CrupestApi.Commons;
+using CrupestApi.Commons.Crud;
+using CrupestApi.Secrets;
+using CrupestApi.Todos;
+
+var builder = WebApplication.CreateBuilder(args);
+
+string configFilePath = Environment.GetEnvironmentVariable("CRUPEST_API_CONFIG_FILE") ?? "/crupest-api-config.json";
+builder.Configuration.AddJsonFile(configFilePath, optional: false, reloadOnChange: true);
+
+builder.Services.AddJsonOptions();
+builder.Services.AddCrupestApiConfig();
+
+builder.Services.AddTodos();
+builder.Services.AddSecrets();
+
+var app = builder.Build();
+
+app.UseCrudCore();
+app.MapTodos("/api/todos");
+// TODO: It's not safe now!
+// app.MapSecrets("/api/secrets");
+
+app.Run();
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi/Properties/launchSettings.json b/dropped/docker/crupest-api/CrupestApi/CrupestApi/Properties/launchSettings.json
new file mode 100644
index 0000000..a4a5cbf
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi/Properties/launchSettings.json
@@ -0,0 +1,15 @@
+{
+ "$schema": "https://json.schemastore.org/launchsettings.json",
+ "profiles": {
+ "dev": {
+ "commandName": "Project",
+ "dotnetRunMessages": true,
+ "applicationUrl": "http://localhost:5188",
+ "workingDirectory": ".",
+ "environmentVariables": {
+ "ASPNETCORE_ENVIRONMENT": "Development",
+ "CRUPEST_API_CONFIG_FILE": "dev-config.json"
+ }
+ }
+ }
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi/appsettings.json b/dropped/docker/crupest-api/CrupestApi/CrupestApi/appsettings.json
new file mode 100644
index 0000000..53753bd
--- /dev/null
+++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi/appsettings.json
@@ -0,0 +1,8 @@
+{
+ "Logging": {
+ "LogLevel": {
+ "Default": "Information"
+ }
+ },
+ "AllowedHosts": "*"
+}
\ No newline at end of file
diff --git a/dropped/docker/crupest-api/Dockerfile b/dropped/docker/crupest-api/Dockerfile
new file mode 100644
index 0000000..feb7522
--- /dev/null
+++ b/dropped/docker/crupest-api/Dockerfile
@@ -0,0 +1,13 @@
+FROM mcr.microsoft.com/dotnet/sdk:7.0-alpine AS build
+COPY CrupestApi /CrupestApi
+WORKDIR /CrupestApi
+RUN dotnet publish CrupestApi/CrupestApi.csproj --configuration Release --output ./publish -r linux-x64
+
+FROM mcr.microsoft.com/dotnet/aspnet:7.0-alpine
+ENV ASPNETCORE_URLS=http://0.0.0.0:5000
+ENV ASPNETCORE_FORWARDEDHEADERS_ENABLED=true
+COPY --from=build /CrupestApi/publish /CrupestApi
+WORKDIR /CrupestApi
+VOLUME [ "/crupest-api-config.json" ]
+EXPOSE 5000
+ENTRYPOINT ["dotnet", "CrupestApi.dll"]
--
cgit v1.2.3