4 files changed, 142 insertions, 1 deletions
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
new file mode 100644
index 0000000..d5edd13
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
@@ -0,0 +1,115 @@
+using System.Data;
+using Dapper;
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Options;
+
+namespace CrupestApi.Commons.Crud;
+
+public class CrudService<TEntity>
+{
+    protected readonly IOptionsSnapshot<CrupestApiConfig> _crupestApiOptions;
+    protected readonly ILogger<CrudService<TEntity>> _logger;
+
+    public CrudService(IOptionsSnapshot<CrupestApiConfig> crupestApiOptions, ILogger<CrudService<TEntity>> logger)
+    {
+        _crupestApiOptions = crupestApiOptions;
+        _logger = logger;
+    }
+
+    public virtual string GetTableName()
+    {
+        return typeof(TEntity).Name;
+    }
+
+    public virtual string GetDbConnectionString()
+    {
+        var fileName = Path.Combine(_crupestApiOptions.Value.DataDir, "crupest-api.db");
+
+        return new SqliteConnectionStringBuilder()
+        {
+            DataSource = fileName,
+            Mode = SqliteOpenMode.ReadWriteCreate
+        }.ToString();
+    }
+
+
+    public async Task<SqliteConnection> CreateDbConnection()
+    {
+        var connection = new SqliteConnection(GetDbConnectionString());
+        await connection.OpenAsync();
+        return connection;
+    }
+
+    public virtual async Task<bool> CheckDatabaseExist(SqliteConnection connection)
+    {
+        var tableName = GetTableName();
+        var count = (await connection.QueryAsync<int>(
+            @"SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND tbl_name = @TableName;",
+            new { TableName = tableName })).Single();
+        if (count == 0)
+        {
+            return false;
+        }
+        else if (count > 1)
+        {
+            throw new DatabaseInternalException($"More than 1 table has name {tableName}. What happened?");
+        }
+        else
+        {
+            return true;
+        }
+    }
+
+    public string GetDatabaseTypeName(Type type)
+    {
+        if (type == typeof(int))
+        {
+            return "INTEGER";
+        }
+        else if (type == typeof(double))
+        {
+            return "REAL";
+        }
+        else if (type == typeof(bool))
+        {
+            return "BOOLEAN";
+        }
+        else
+        {
+            throw new DatabaseInternalException($"Type {type} is not supported.");
+        }
+    }
+
+    public string GetCreateTableColumnSql()
+    {
+        var properties = typeof(TEntity).GetProperties();
+        var sql = string.Join(", ", properties.Select(p => $"{p.Name} {GetDatabaseTypeName(p.PropertyType)}"));
+        return sql;
+    }
+
+    public virtual async Task DoInitializeDatabase(SqliteConnection connection)
+    {
+        await using var transaction = await connection.BeginTransactionAsync();
+        var tableName = GetTableName();
+        var columnSql = GetCreateTableColumnSql();
+        var sql = $@"
+CREATE TABLE {tableName}(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    {columnSql}
+);
+        ";
+        await connection.ExecuteAsync(sql, transaction: transaction);
+        await transaction.CommitAsync();
+    }
+
+    public virtual async Task<SqliteConnection> EnsureDatabase()
+    {
+        var connection = await CreateDbConnection();
+        var exist = await CheckDatabaseExist(connection);
+        if (!exist)
+        {
+            await DoInitializeDatabase(connection);
+        }
+        return connection;
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs
new file mode 100644
index 0000000..aa25a23
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudWebApplicationExtensions.cs
@@ -0,0 +1,9 @@
+namespace CrupestApi.Commons.Crud;
+
+public static class CrudWebApplicationExtensions
+{
+    public static IApplicationBuilder UseCrud(this IApplicationBuilder app, string path)
+    {
+        return app;
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DatabaseInternalException.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DatabaseInternalException.cs
new file mode 100644
index 0000000..77b3c66
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DatabaseInternalException.cs
@@ -0,0 +1,12 @@
+namespace CrupestApi.Commons.Crud;
+
+[System.Serializable]
+public class DatabaseInternalException : System.Exception
+{
+    public DatabaseInternalException() { }
+    public DatabaseInternalException(string message) : base(message) { }
+    public DatabaseInternalException(string message, System.Exception inner) : base(message, inner) { }
+    protected DatabaseInternalException(
+        System.Runtime.Serialization.SerializationInfo info,
+        System.Runtime.Serialization.StreamingContext context) : base(info, context) { }
+}
+\ No newline at end of file
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj b/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj
index ae8ba71..8e291fa 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/CrupestApi.Commons.csproj
@@ -8,4 +8,9 @@
     <SelfContained>false</SelfContained>
   </PropertyGroup>
 
-</Project>
+  <ItemGroup>
+    <PackageReference Include="Dapper" Version="2.0.123" />
+    <PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.0" />
+  </ItemGroup>
+
+</Project>
+\ No newline at end of file