From f9aa02ec1a4c24e80a206857d4f68198bb027bb4 Mon Sep 17 00:00:00 2001 From: crupest 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 + dropped/template/crupest-api-config.json.template | 10 + dropped/template/docker-compose.yaml.template | 24 + dropped/template/nginx/timeline.conf.template | 21 + dropped/template/v2ray-client-config.json.template | 46 ++ 58 files changed, 3902 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 create mode 100644 dropped/template/crupest-api-config.json.template create mode 100644 dropped/template/docker-compose.yaml.template create mode 100644 dropped/template/nginx/timeline.conf.template create mode 100644 dropped/template/v2ray-client-config.json.template (limited to 'dropped') 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(); + builder.Services.AddCrud(); + builder.WebHost.UseTestServer(); + _app = builder.Build(); + _app.UseCrudCore(); + _app.MapCrud("/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(); + 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>(); + 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(); + 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>(); + 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(); + 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(); + 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(); + 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>(); + 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 _crudService; + + public CrudServiceTest() + { + var columnTypeProvider = new ColumnTypeProvider(); + var tableInfoFactory = new TableInfoFactory(columnTypeProvider, NullLoggerFactory.Instance); + var dbConnectionFactory = new SqliteMemoryConnectionFactory(); + + _crudService = new CrudService( + 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(() => _crudService.GetByKey("KeyNotExist")); + Assert.Throws(() => _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 SymbolTokens = new List() { '(', ')', ';' }; + + public static List SqlExtractWords(string? sql, bool toLower = true) + { + var result = new List(); + + 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 { "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(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 @@ + + + + net7.0 + enable + enable + + false + + + + + + + + runtime; build; native; contentfiles; analyzers; buildtransitive + all + + + runtime; build; native; contentfiles; analyzers; buildtransitive + all + + + + + + + + \ 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().BindConfiguration("CrupestApi"); + services.PostConfigure(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 _logger; + + /// + /// Initialize a column without corresponding property. + /// + public ColumnInfo(TableInfo table, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider, ILoggerFactory loggerFactory) + { + _logger = loggerFactory.CreateLogger(); + 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); + } + + /// + /// Initialize a column with corresponding property. + /// + public ColumnInfo(TableInfo table, PropertyInfo propertyInfo, IColumnTypeProvider typeProvider, ILoggerFactory loggerFactory) + { + _logger = loggerFactory.CreateLogger(); + _logger.LogInformation("Create column with property {}.", propertyInfo.Name); + + Table = table; + PropertyInfo = propertyInfo; + ColumnType = typeProvider.Get(propertyInfo.PropertyType); + + var columnAttribute = propertyInfo.GetCustomAttribute(); + 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); + /// + /// 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. + /// + /// + /// + public bool IsSpecifiedAsKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.ActAsKey) is true; + public ColumnIndexType Index => Metadata.GetValueOrDefault(ColumnMetadataKeys.Index) ?? ColumnIndexType.None; + + /// + /// Whether the column value can be generated, which means the column has a default value or a default value generator or is AUTOINCREMENT. + /// + public bool CanBeGenerated => DefaultValue is not null || DefaultValueGeneratorMethod is not null || IsAutoIncrement; + + /// + /// The real column name. Maybe set in metadata or just the property name. + /// + /// + 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); + + /// + /// This will add hooks for string type column to coerce null to ""(empty string) when get or set. No effect on non-string type. + /// + public const string DefaultEmptyForString = nameof(ColumnAttribute.DefaultEmptyForString); + + /// + /// This indicates that you take care of generate this column value when create entity. User calling the api can not specify the value. + /// + public const string OnlyGenerated = nameof(ColumnAttribute.OnlyGenerated); + + /// + /// The default value generator method name in entity type. Default to null, aka, search for ColumnNameDefaultValueGenerator. + /// Generator has signature static void DefaultValueGenerator(ColumnInfo column) + /// + public const string DefaultValueGenerator = nameof(ColumnAttribute.DefaultValueGenerator); + + /// + /// The validator method name in entity type. Default to null, aka, the default validator. + /// Validator has signature static void Validator(ColumnInfo column, object value) + /// Value param is never null. If you want to mean NULL, it should be a . + /// + public const string Validator = nameof(ColumnAttribute.Validator); + + /// + /// The column can only be set when inserted, can't be changed in update. + /// + /// + public const string NoUpdate = nameof(ColumnAttribute.NoUpdate); + + /// + /// This column acts as key when get one entity for http get method in path. + /// + public const string ActAsKey = nameof(ColumnAttribute.ActAsKey); + + /// + /// The default value used for the column. + /// + 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(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; + + /// + public bool DefaultEmptyForString { get; init; } + + /// + public bool OnlyGenerated { get; init; } + + /// + public string? DefaultValueGenerator { get; init; } + + /// + public string? Validator { get; init; } + + /// + public bool NoUpdate { get; init; } + + /// + 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 _own = new Dictionary(); + private IList _children = new List(); + + 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(); + public static IColumnTypeInfo IntColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo ShortColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo SByteColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo LongColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo FloatColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo DoubleColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo StringColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + public static IColumnTypeInfo BytesColumnTypeInfo { get; } = new SimpleColumnTypeInfo(); + 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 GetAll(); + IColumnTypeInfo Get(Type clrType); + + IList GetAllCustom() + { + return GetAll().Where(t => !t.IsSimple).ToList(); + } +} + +public class SimpleColumnTypeInfo : IColumnTypeInfo +{ + public Type ClrType => typeof(T); + public Type DatabaseClrType => typeof(T); +} + +public class DateTimeColumnTypeInfo : IColumnTypeInfo +{ + private JsonConverter _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 +{ + 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 _typeMap = new Dictionary(); + + 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 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 : 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> _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>(); + } + + 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 GetAll() + { + EnsureDatabase(); + var result = _table.Select(_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(_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(); + services.TryAddSingleton(); + services.TryAddSingleton(); + services.TryAddSingleton(); + services.AddSecrets(); + return services; + } + + public static IServiceCollection AddCrud(this IServiceCollection services) where TEntity : class where TCrudService : CrudService + { + AddCrudCore(services); + + services.TryAddScoped, TCrudService>(); + services.TryAddScoped>(); + + return services; + } + + public static IServiceCollection AddCrud(this IServiceCollection services) where TEntity : class + { + return services.AddCrud>(); + } + +} 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(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>(); + var entityJsonHelper = context.RequestServices.GetRequiredService>(); + 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>(); + var entityJsonHelper = context.RequestServices.GetRequiredService>(); + 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>(); + var entityJsonHelper = context.RequestServices.GetRequiredService>(); + 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>(); + var entityJsonHelper = context.RequestServices.GetRequiredService>(); + 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>(); + 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 _apiConfigMonitor; + + public SqliteConnectionFactory(IOptionsMonitor 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 _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; + +/// +/// This will always represent null value in database. +/// +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; + +/// +/// Contains all you need to do with json. +/// +public class EntityJsonHelper where TEntity : class +{ + private readonly TableInfo _table; + private readonly IOptionsMonitor _jsonSerializerOptions; + + public EntityJsonHelper(ITableInfoFactory tableInfoFactory, IOptionsMonitor jsonSerializerOptions) + { + _table = tableInfoFactory.Get(typeof(TEntity)); + _jsonSerializerOptions = jsonSerializerOptions; + } + + public Dictionary ConvertEntityToDictionary(TEntity entity, bool includeNonColumnProperties = false) + { + var result = new Dictionary(); + + 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 ConvertJsonObjectToDictionary(JsonElement jsonElement) + { + var result = new Dictionary(); + + 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(); + + Dictionary 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(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 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(); + 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(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 GetSubclauses() + { + return Enumerable.Empty(); + } + + IEnumerable GetRelatedColumns() + { + var subclauses = GetSubclauses(); + var result = new List(); + 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 +{ + /// + /// Null means use default value. Use . + /// + 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 Items { get; } + string GenerateColumnListSql(string? dbProviderId = null); + (string sql, ParamList parameters) GenerateValueListSql(string? dbProviderId = null); +} + +public class InsertClause : IInsertClause +{ + public List Items { get; } = new List(); + + 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 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; } + + /// + /// 0 if not primary key. 1-based index if in primary key. + /// + public int PrimaryKey { get; set; } +} + +public class Table +{ + public Table(string name) + { + Name = name; + } + + public string Name { get; set; } + public List Columns { get; set; } = new List(); +} + +public interface IDatabaseMigrator +{ + Table? GetTable(IDbConnection dbConnection, string tableName); + Table ConvertTableInfoToTable(TableInfo tableInfo); + string GenerateCreateTableColumnSqlSegment(TableColumn column); + string GenerateCreateTableSql(string tableName, IEnumerable 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( + "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($"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 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() : 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($"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($"SELECT * FROM {tempTableName}").Cast>().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() : new HashSet(databaseTable.Columns.Select(c => c.Name)); + var wantedTableColumns = new HashSet(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 Items { get; } + // Contains "ORDER BY" keyword! + string GenerateSql(string? dbProviderId = null); +} + +public class OrderByClause : IOrderByClause +{ + public List Items { get; } = new List(); + + public OrderByClause(params OrderByItem[] items) + { + Items.AddRange(items); + } + + public static OrderByClause Create(params OrderByItem[] items) + { + return new OrderByClause(items); + } + + public List 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; + +/// +/// 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. +/// +/// +/// If value is DbNullValue, it will be treated as null. +/// +public record ParamInfo(string Name, object? Value, string? ColumnName = null); + +public class ParamList : List +{ + 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(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; + +/// +/// Contains all you need to manipulate a table. +/// +public class TableInfo +{ + private readonly IColumnTypeProvider _columnTypeProvider; + private readonly Lazy> _lazyColumnNameList; + private readonly ILoggerFactory _loggerFactory; + private readonly ILogger _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(); + + _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(); + + bool hasId = false; + ColumnInfo? primaryKeyColumn = null; + ColumnInfo? keyColumn = null; + + List 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>(() => 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 Columns { get; } + public IReadOnlyList PropertyColumns => Columns.Where(c => c.PropertyInfo is not null).ToList(); + public ColumnInfo PrimaryKeyColumn { get; } + /// + /// Maybe not the primary key. But acts as primary key. + /// + /// + public ColumnInfo KeyColumn { get; } + public IReadOnlyList ColumnProperties => PropertyColumns.Select(c => c.PropertyInfo!).ToList(); + public IReadOnlyList NonColumnProperties { get; } + public IReadOnlyList ColumnNameList => _lazyColumnNameList.Value; + + protected bool CheckPropertyIsColumn(PropertyInfo property) + { + var columnAttribute = property.GetCustomAttribute(); + 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 sqlNameSet = new HashSet(); + + 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); + } + } + } + + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + 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); + } + + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + 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); + } + + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + 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); + } + + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + 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; + } + + /// + /// ConvertParameters. Select. Call hooks. + /// + public virtual List 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(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(sql, ConvertParameters(parameters)); + return result; + } + + public virtual TResult MapDynamicTo(dynamic d) + { + var dict = (IDictionary)d; + + var result = Activator.CreateInstance(); + 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; + } + + /// + /// Select and call hooks. + /// + public virtual List Select(IDbConnection dbConnection, string? what = null, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) + { + List queryResult = SelectDynamic(dbConnection, what, where, orderBy, skip, limit).ToList(); + + return queryResult.Select(MapDynamicTo).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; + } + + /// + /// Insert a entity and call hooks. + /// + /// The key of insert entity. + 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; + } + + /// + /// Upgrade a entity and call hooks. + /// + /// The key of insert entity. + 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 _cache = new Dictionary(); + private readonly IColumnTypeProvider _columnTypeProvider; + private readonly ILoggerFactory _loggerFactory; + private readonly ILogger _logger; + + public TableInfoFactory(IColumnTypeProvider columnTypeProvider, ILoggerFactory loggerFactory) + { + _columnTypeProvider = columnTypeProvider; + _loggerFactory = loggerFactory; + _logger = loggerFactory.CreateLogger(); + } + + // 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 Items { get; } + (string sql, ParamList parameters) GenerateSql(); +} + +public class UpdateClause : IUpdateClause +{ + public List Items { get; } = new List(); + + public UpdateClause(IEnumerable 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 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; + +/// +/// This exception means the exception is caused by user and can be safely shown to user. +/// +[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 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 GetRelatedColumns() + { + return new List { 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 @@ + + + + net7.0 + library + enable + enable + false + + + + + + + + \ 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(); + services.Configure(config => + { + config.AllowTrailingCommas = true; + config.PropertyNameCaseInsensitive = true; + config.PropertyNamingPolicy = JsonNamingPolicy.CamelCase; + }); + + return services; + } + + public static async Task ReadJsonAsync(this HttpRequest request) + { + var jsonOptions = request.HttpContext.RequestServices.GetRequiredService>(); + using var stream = request.Body; + var body = await JsonSerializer.DeserializeAsync(stream, jsonOptions.Value); + return body!; + } + + public static async Task WriteJsonAsync(this HttpResponse response, T bodyObject, int statusCode = 200, HttpResponseAction? beforeWriteBody = null, CancellationToken cancellationToken = default) + { + var jsonOptions = response.HttpContext.RequestServices.GetRequiredService>(); + byte[] json = JsonSerializer.SerializeToUtf8Bytes(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(this HttpContext context, T bodyObject, int statusCode = 200, HttpResponseAction? beforeWriteBody = null, CancellationToken cancellationToken = default) + { + return context.Response.WriteJsonAsync(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(); + 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 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, ISecretService +{ + private readonly ILogger _logger; + + public SecretService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory) + : base(tableInfoFactory, dbConnectionFactory, migrator, loggerFactory) + { + _logger = loggerFactory.CreateLogger(); + } + + 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 GetPermissions(string secret) + { + var list = _table.Select(_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(); + 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 @@ + + + + + + + + + + + + + net7.0 + library + enable + enable + false + + + 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 @@ + + + + + + + + + + + + + net7.0 + library + enable + enable + false + + + 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(); + return services; + } + + public static WebApplication MapSecrets(this WebApplication webApplication, string path = "/api/secrets") + { + webApplication.MapCrud(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 @@ + + + + + + + + net7.0 + library + enable + enable + false + + + 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 _options; + private readonly ILogger _logger; + + public TodosService(IOptionsSnapshot options, ILogger 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> 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(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(); + + 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().BindConfiguration("CrupestApi:Todos"); + services.PostConfigure(config => + { + if (config.Count == 0) + { + config.Count = 20; + } + }); + services.TryAddScoped(); + 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(); + + 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 @@ + + + + + + + + + + + net7.0 + enable + enable + false + + + \ 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"] diff --git a/dropped/template/crupest-api-config.json.template b/dropped/template/crupest-api-config.json.template new file mode 100644 index 0000000..65a7944 --- /dev/null +++ b/dropped/template/crupest-api-config.json.template @@ -0,0 +1,10 @@ +{ + "CrupestApi": { + "Todos": { + "Username": "$CRUPEST_GITHUB_USERNAME", + "ProjectNumber": "$CRUPEST_GITHUB_PROJECT_NUMBER", + "Token": "$CRUPEST_GITHUB_TOKEN", + "Count": "$CRUPEST_GITHUB_TODO_COUNT" + } + } +} diff --git a/dropped/template/docker-compose.yaml.template b/dropped/template/docker-compose.yaml.template new file mode 100644 index 0000000..73ff4e8 --- /dev/null +++ b/dropped/template/docker-compose.yaml.template @@ -0,0 +1,24 @@ +services: + + timeline: + image: crupest/timeline:latest + pull_policy: always + container_name: timeline + restart: on-failure:3 + environment: + - ASPNETCORE_FORWARDEDHEADERS_ENABLED=true + - TIMELINE_DisableAutoBackup=true + volumes: + - ./data/timeline:/root/timeline + + crupest-api: + pull_policy: build + build: + context: ./docker/crupest-api + dockerfile: Dockerfile + pull: true + tags: + - "crupest/crupest-api:latest" + container_name: crupest-api + volumes: + - "./crupest-api-config.json:/crupest-api-config.json:ro" diff --git a/dropped/template/nginx/timeline.conf.template b/dropped/template/nginx/timeline.conf.template new file mode 100644 index 0000000..551e0ae --- /dev/null +++ b/dropped/template/nginx/timeline.conf.template @@ -0,0 +1,21 @@ +server { + listen 443 ssl http2; + listen [::]:443 ssl http2; + server_name timeline.${CRUPEST_DOMAIN}; + + location / { + include common/reverse-proxy; + proxy_pass http://timeline:5000/; + } + + client_max_body_size 5G; +} + +server { + listen 80; + listen [::]:80; + server_name timeline.${CRUPEST_DOMAIN}; + + include common/https-redirect; + include common/acme-challenge; +} diff --git a/dropped/template/v2ray-client-config.json.template b/dropped/template/v2ray-client-config.json.template new file mode 100644 index 0000000..0c99c6d --- /dev/null +++ b/dropped/template/v2ray-client-config.json.template @@ -0,0 +1,46 @@ +{ + "inbounds": [ + { + "port": 1080, + "listen": "127.0.0.1", + "protocol": "socks", + "sniffing": { + "enabled": true, + "destOverride": [ + "http", + "tls" + ] + }, + "settings": { + "auth": "noauth", + "udp": false + } + } + ], + "outbounds": [ + { + "protocol": "vmess", + "settings": { + "vnext": [ + { + "address": "$CRUPEST_DOMAIN", + "port": 443, + "users": [ + { + "id": "$CRUPEST_V2RAY_TOKEN", + "alterId": 0 + } + ] + } + ] + }, + "streamSettings": { + "network": "ws", + "security": "tls", + "wsSettings": { + "path": "/_$CRUPEST_V2RAY_PATH" + } + } + } + ] +} \ No newline at end of file -- cgit v1.2.3