CG.Infrastructure.Logging.Data 1.1.1

Infrastructure.Logging.Data

A .NET library that contains database migration scripts and schema definitions for the Infrastructure.Logging library, specifically designed for Serilog database logging with LogEvents.

Overview

Infrastructure.Logging.Data is a .NET 9.0 library that provides database infrastructure for the Logging library. It contains DbUp SQL scripts for creating and maintaining the database schema required for Serilog database logging functionality.

Features

• Database Schema Management: Automated schema creation and management
• Serilog Database Support: Database structure for LogEvents table
• DbUp Integration: Seamless integration with DbUp for database migrations
• Clean Architecture: Separation of database scripts from business logic

Target Framework

• .NET 9.0

Package Information

• Package ID: CG.Infrastructure.Logging.Data
• Version: 1.1.0
• Authors: Matthew Evans
• Company: Matthew Evans
• Product: CG.Infrastructure.Logging.Data
• Description: Infra Logging Data library that contains DbUp SQL scripts and functionality to create the necessary tables for Serilog logging

Project Structure

Infrastructure.Logging.Data/
├── Scripts/
│   ├── Schemas/
│   │   └── EnsureSchema.sql          # Schema creation script
│   └── Migrations/
│       └── 000002 - CreateTable - LogEvents.sql  # LogEvents table creation

Database Schema

LogEvents Table

The main table for Serilog database logging:

CREATE TABLE [dbo].[LogEvents]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](max) NULL,
    [Severity] [nvarchar](max) NULL,
    [TimeStamp] [datetimeoffset](7) NULL,
    [Exception] [nvarchar](max) NULL,
    [LogEvent] [nvarchar](max) NULL,
    [TraceId] [nvarchar](max) NULL,
    CONSTRAINT [PK_LogEvents] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Columns:

• Id: Auto-incrementing primary key for the log entry
• Message: The log message text
• Severity: Log level (Information, Warning, Error, etc.)
• TimeStamp: When the log entry was created (with timezone support)
• Exception: Exception details if applicable
• LogEvent: Structured log event data (JSON)
• TraceId: Correlation ID for request tracing

Usage

Database Migration

This library is designed to be used with DbUp for database migrations. The scripts are embedded as resources and can be executed using the DbUp framework.

Integration with Logging.DbUp

The scripts are typically executed through the Logging.DbUp project, which provides a command-line interface for database migrations.

Script Execution Order

1. EnsureSchema.sql: Creates the database schema if it doesn't exist
2. CreateTable - LogEvents.sql: Creates the LogEvents table

Dependencies

This library has no external dependencies and is designed to be lightweight, containing only the necessary SQL scripts for database setup.

Best Practices

1. Version Control: All database scripts are version-controlled
2. Idempotency: Scripts are designed to be run multiple times safely
3. Schema Variables: Uses DbUp variables for schema names
4. Clean Separation: Database scripts are separate from business logic

Migration Process

Using DbUp

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(typeof(Program).Assembly)
    .WithVariable("schemaname", "Logging")
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

Manual Execution

Scripts can also be executed manually in SQL Server Management Studio or any other SQL client.

Serilog Configuration

To use this with Serilog, configure the MSSqlServer sink:

Log.Logger = new LoggerConfiguration()
    .WriteTo.MSSqlServer(
        connectionString: "your_connection_string",
        sinkOptions: new MSSqlServerSinkOptions
        {
            TableName = "LogEvents",
            SchemaName = "Logging",
            AutoCreateSqlTable = false // Let DbUp handle table creation
        })
    .CreateLogger();

Contributing

This project is part of the CG Infrastructure Libraries. For contributions, please refer to the main infrastructure repository guidelines.

License

See the LICENSE file in the root directory for licensing information.

Support

For issues and questions related to this library, please contact the development team or create an issue in the project repository.