TimeScaleDb Initializer

ApplicationDbContextInitialiser.cs

using System.Reflection;
using VaultApi.Domain.Constants;
using VaultApi.Domain.Entities;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;
using VaultApi.Domain.Common;

namespace VaultApi.Infrastructure.Data;

public static class InitialiserExtensions
{
    public static async Task InitialiseDatabaseAsync(this WebApplication app)
    {
        using var scope = app.Services.CreateScope();
        var initializer = scope.ServiceProvider.GetRequiredService<ApplicationDbContextInitialiser>();
        await initializer.InstallTimeScaleDbAsync();
        await initializer.InitialiseAsync();
        await initializer.EnsureHypertable();
        await initializer.SeedAsync();
    }
}

public class ApplicationDbContextInitialiser
{
    private readonly ILogger<ApplicationDbContextInitialiser> _logger;
    private readonly ApplicationDbContext _context;
    private readonly UserManager<ApplicationUser> _userManager;
    private readonly RoleManager<IdentityRole> _roleManager;

    public ApplicationDbContextInitialiser(ILogger<ApplicationDbContextInitialiser> logger,
        ApplicationDbContext context, UserManager<ApplicationUser> userManager, RoleManager<IdentityRole> roleManager)
    {
        _logger = logger;
        _context = context;
        _userManager = userManager;
        _roleManager = roleManager;
    }

    public async Task InstallTimeScaleDbAsync()
    {
        NpgsqlConnectionStringBuilder builder = new(_context.Database.GetConnectionString());
        string? databaseName = builder.Database; // REMEMBER ORIGINAL DB NAME
        builder.Database = "postgres"; // TEMPORARILY USE POSTGRES DATABASE
        await using (NpgsqlConnection connection = new(builder.ConnectionString))
        {
            await connection.OpenAsync();
            NpgsqlCommand checkExistingCommand = connection.CreateCommand();
            checkExistingCommand.CommandText =
                $@"SELECT datname FROM pg_database WHERE datname = '{databaseName}';";
            bool hasDatabase = false;
            await using (NpgsqlDataReader existing = await checkExistingCommand.ExecuteReaderAsync())
            {
                hasDatabase = existing.HasRows;
            }

            if (!hasDatabase)
            {
                // Create database
                NpgsqlCommand createCommand = connection.CreateCommand();
                createCommand.CommandText = $@"CREATE DATABASE {databaseName}";
                await createCommand.ExecuteNonQueryAsync();
            }

            await connection.CloseAsync();
        }

        builder.Database = databaseName; // CHANGE BACK TO WORKING DATABASE
        await using (NpgsqlConnection connection = new(builder.ConnectionString))
        {
            await connection.OpenAsync();
            // Create database
            NpgsqlCommand createCommand1 = connection.CreateCommand();
            createCommand1.CommandText = @"DO
                    $$BEGIN
                        CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
                    EXCEPTION
                    WHEN unique_violation THEN
                    NULL; --ignore error
                    END;$$; 
                   ";
            await createCommand1.ExecuteNonQueryAsync();
            await connection.CloseAsync();
        }
    }

    public async Task InitialiseAsync()
    {
        try
        {
            await _context.Database.MigrateAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "An error occurred while initialising the database.");
            throw;
        }
    }

    public async Task EnsureHypertable()
    {
        var model = _context.Model;

        foreach (var entityType in model.GetEntityTypes())
        {
            var tableName = entityType.GetTableName();
            var schema = entityType.GetSchema() ?? "public";
            var timeColumnProperty = entityType.GetProperties()
                .FirstOrDefault(p => Attribute.IsDefined(p.PropertyInfo, typeof(HyperTableColumnAttribute)));

            if (timeColumnProperty != null)
            {
                var timeColumnName = timeColumnProperty.Name;
                var sql = $"SELECT create_hypertable('{schema}.{tableName}', '{timeColumnName}', if_not_exists => TRUE);";

                await _context.Database.ExecuteSqlRawAsync(sql);
            }
        }
    }

    public async Task SeedAsync()
    {
        try
        {
            await TrySeedAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "An error occurred while seeding the database.");
            throw;
        }
    }

    public async Task TrySeedAsync()
    {
        IdentityRole administratorRole = new("Administrator");

        if (_roleManager.Roles.All(r => r.Name != administratorRole.Name))
        {
            await _roleManager.CreateAsync(administratorRole);
        }

        IdentityRole moderatorRole = new("Moderator");

        if (_roleManager.Roles.All(r => r.Name != moderatorRole.Name))
        {
            await _roleManager.CreateAsync(moderatorRole);
        }

        IdentityRole userRole = new("User");

        if (_roleManager.Roles.All(r => r.Name != userRole.Name))
        {
            await _roleManager.CreateAsync(userRole);
        }

        IdentityRole apiRole = new("Api");

        if (_roleManager.Roles.All(r => r.Name != apiRole.Name))
        {
            await _roleManager.CreateAsync(apiRole);
        }

        if (!await _context.ProvisionUsers.AnyAsync())
        {
            await _context.ProvisionUsers.AddAsync(new ProvisionUser
            {
                Email = "utharn.b@rmutsb.ac.th",
                Name = "อุทาน บูรณศักดิ์ศรี",
                Role = "Administrator",
                MainGroup = "ผู้ดูแลระบบ",
                SubGroup = "ผู้ดูแลระบบ",
            });
        }

        foreach (string api in Apis.AllApis)
        {
            if (!await _context.ApiModules.AnyAsync(a => a.Name == api))
            {
                await _context.ApiModules.AddAsync(new ApiModule { Name = api });
            }
        }

        await _context.SaveChangesAsync();
        await UpdateSequences(_context);
    }

    private static async Task UpdateSequences(ApplicationDbContext context)
    {
        NpgsqlConnectionStringBuilder builder = new(context.Database.GetConnectionString());
        await using NpgsqlConnection connection = new(builder.ConnectionString);
        await connection.OpenAsync();
        NpgsqlCommand checkExistingCommand = connection.CreateCommand();
        checkExistingCommand.CommandText = @"SELECT sequencename from pg_sequences;";
        List<string> allSequences = new();
        await using NpgsqlDataReader existing = await checkExistingCommand.ExecuteReaderAsync();
        while (await existing.ReadAsync())
        {
            allSequences.Add(existing.GetString(0));
        }

        await existing.CloseAsync();

        foreach (string sequence in allSequences)
        {
            try
            {
                string[] names = sequence.Split("_");
                string columnName = names[^2];
                string tableName = string.Join('_', names[..^2]);
                NpgsqlCommand createCommand = connection.CreateCommand();
                createCommand.CommandText = $@"do $$
                    declare maxid int;
                    begin
                        select COALESCE(max(""{columnName}""), 1) from ""{tableName}"" into maxid;
                    execute 'alter SEQUENCE ""{sequence}"" START  with ' || maxid;
                    end;
                    $$ language plpgsql;
                    ";
                await createCommand.ExecuteNonQueryAsync();
            }
            catch (Exception)
            {
                // ignored
            }
        }
    }
}

HyperTableColumnAttribute.cs

namespace VaultApi.Domain.Common;

[AttributeUsage(AttributeTargets.Property)]
public class HyperTableColumnAttribute : Attribute
{
}

VaultDataConfiguration.cs

public class VaultDataConfiguration : IEntityTypeConfiguration<VaultData>
{
    public void Configure(EntityTypeBuilder<VaultData> builder)
    {
        builder.ToTable("vaultdata");
        builder.HasKey(b => new { b.Created, b.Id});
        builder.IsHypertable(b => b.Created);
    }
}


public class VaultData
{
    public Guid Id { get; set; } = default!;
    [HyperTableColumn()]
    public DateTime Created { get; set; }
    public int RemainingTimes { get; set; }
}