CG.Infrastructure.Authentication.Data 1.1.1

Infrastructure.Authentication.Data

A .NET library that contains database migration scripts and schema definitions for the Infrastructure.Authentication library, specifically designed for ASP.NET Core Identity persistence storage.

Overview

Infrastructure.Authentication.Data is a .NET 9.0 library that provides database infrastructure for the Authentication library. It contains DbUp SQL scripts for creating and maintaining the database schema required for ASP.NET Core Identity functionality, including users, roles, claims, and related tables.

Features

• Database Schema Management: Automated schema creation and management
• ASP.NET Core Identity Support: Complete database structure for Identity framework
• DbUp Integration: Seamless integration with DbUp for database migrations
• Clean Architecture: Separation of database scripts from business logic
• Comprehensive Identity Tables: Users, Roles, Claims, UserRoles, UserLogins, UserTokens

Target Framework

• .NET 9.0

Package Information

• Package ID: CG.Infrastructure.Authentication.Data
• Version: 1.1.0
• Authors: Matthew Evans
• Company: Matthew Evans
• Product: CG.Infrastructure.Authentication.Data
• Description: Infra Authentication Data library that contains DbUp SQL scripts and functionality to create the necessary tables for AspNetCore.Identity persistance storage

Project Structure

Infrastructure.Authentication.Data/
├── Scripts/
│   ├── Schemas/
│   │   └── EnsureSchema.sql                    # Schema creation script
│   └── Migrations/
│       ├── 000001 - AlterDatabase.sql         # Database configuration
│       ├── 000002 - CreateTable - AspNetRoles.sql
│       ├── 000003 - CreateTable - AspNetRoleClaims.sql
│       ├── 000004 - CreateTable - AspNetUsers.sql
│       ├── 000005 - CreateTable - AspNetUserClaims.sql
│       ├── 000006 - CreateTable - AspNetUserLogins.sql
│       ├── 000007 - CreateTable - AspNetUserRoles.sql
│       ├── 000008 - CreateTable - AspNetUserTokens.sql
│       └── 000009 - CreateIndexes.sql         # Performance indexes

Note: Query constants are now defined in the main Infrastructure.Authentication project under Data/Repositories/Queries/ for better organization and maintainability.

Database Schema

Core Identity Tables

AspNetUsers Table

CREATE TABLE [dbo].[AspNetUsers]
(
    [Id] [nvarchar](450) NOT NULL,
    [UserName] [nvarchar](256) NULL,
    [NormalizedUserName] [nvarchar](256) NULL,
    [Email] [nvarchar](256) NULL,
    [NormalizedEmail] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [ConcurrencyStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEnd] [datetimeoffset](7) NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
)

AspNetRoles Table

CREATE TABLE [dbo].[AspNetRoles]
(
    [Id] [nvarchar](450) NOT NULL,
    [Name] [nvarchar](256) NULL,
    [NormalizedName] [nvarchar](256) NULL,
    [ConcurrencyStamp] [nvarchar](max) NULL,
    CONSTRAINT [PK_AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
)

AspNetUserRoles Table

CREATE TABLE [dbo].[AspNetUserRoles]
(
    [UserId] [nvarchar](450) NOT NULL,
    [RoleId] [nvarchar](450) NOT NULL,
    CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC)
)

AspNetUserClaims Table

CREATE TABLE [dbo].[AspNetUserClaims]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [nvarchar](450) NOT NULL,
    [ClaimType] [nvarchar](max) NULL,
    [ClaimValue] [nvarchar](max) NULL,
    CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC)
)

AspNetRoleClaims Table

CREATE TABLE [dbo].[AspNetRoleClaims]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RoleId] [nvarchar](450) NOT NULL,
    [ClaimType] [nvarchar](max) NULL,
    [ClaimValue] [nvarchar](max) NULL,
    CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED ([Id] ASC)
)

AspNetUserLogins Table

CREATE TABLE [dbo].[AspNetUserLogins]
(
    [LoginProvider] [nvarchar](450) NOT NULL,
    [ProviderKey] [nvarchar](450) NOT NULL,
    [ProviderDisplayName] [nvarchar](max) NULL,
    [UserId] [nvarchar](450) NOT NULL,
    CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC)
)

AspNetUserTokens Table

CREATE TABLE [dbo].[AspNetUserTokens]
(
    [UserId] [nvarchar](450) NOT NULL,
    [LoginProvider] [nvarchar](450) NOT NULL,
    [Name] [nvarchar](450) NOT NULL,
    [Value] [nvarchar](max) NULL,
    CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [Name] ASC)
)

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 Identity.DbUp

The scripts are typically executed through the Identity.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. AlterDatabase.sql: Configures database settings and collation
3. CreateTable scripts: Creates all Identity tables in dependency order
4. CreateIndexes.sql: Creates performance indexes

Note: This project now uses query constants instead of stored procedures for better maintainability and version control.

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
5. Query Constants: SQL queries are defined as C# constants for better maintainability
6. Performance Indexes: Includes strategic indexes for common query patterns
7. Direct SQL Execution: Queries are executed directly using CommandType.Text for flexibility

Migration Process

Using DbUp

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

var result = upgrader.PerformUpgrade();

Manual Execution

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

ASP.NET Core Identity Configuration

To use this with ASP.NET Core Identity, configure the DbContext:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

services.AddDefaultIdentity<ApplicationUser>()
    .AddRoles<ApplicationRole>()
    .AddEntityFrameworkStores<ApplicationDbContext>();

Query Constants Usage

The project now uses C# query constants instead of stored procedures. Here's how to use them:

// Example: Using the query constants with IDataAccess
await dataAccess.SaveData(AspNetIdentityQueries.Users_Insert, user, connectionName, CommandType.Text);
await dataAccess.LoadData<ApplicationUser>(AspNetIdentityQueries.Users_GetById, new { Id = userId }, connectionName, CommandType.Text);

Available Query Categories:

• User Queries: Insert, Update, Delete, Get operations for users
• User Claims Queries: User claim management operations
• User Logins Queries: External login provider operations
• User Roles Queries: User-role relationship operations
• User Tokens Queries: User token management operations
• Role Queries: Role CRUD operations
• Role Claims Queries: Role claim management operations
• Utility Queries: Existence checks, counts, and specialized queries

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.