SharpCoreDB.Provider.YesSql 1.0.6

SharpCoreDB.Provider.YesSql

YesSql Provider for SharpCoreDB - Enables OrchardCore CMS and other YesSql-based applications to use SharpCoreDB as the underlying database.

📋 Table of Contents

• Overview
• Architecture
• Installation
• Quick Start
• Configuration
• How It Works
• Performance
• Compatibility
• Troubleshooting
• Advanced Topics
• Contributing

Overview

What is YesSql?

YesSql is a document database for .NET that provides:

• LINQ query support for documents
• Automatic change tracking
• Document sessions (like Entity Framework)
• Multi-tenancy support (table prefixes)
• Used by OrchardCore CMS

What does this provider do?

This provider enables YesSql to use SharpCoreDB instead of SQLite/SQL Server/PostgreSQL, giving you:

✅ Native encryption (AES-256-GCM) for all data
✅ Better performance for analytics (345x faster than LiteDB via SIMD)
✅ Smaller memory footprint (6.2x less than LiteDB)
✅ Pure .NET implementation (no native dependencies)
✅ OrchardCore compatibility out-of-the-box

Architecture

High-Level Overview

OrchardCore CMS / YesSql Application
          ↓
   YesSql.Core (Document ORM)
          ↓
   YesSql.Provider.Sqlite (SQL Dialect)
          ↓
   SharpCoreDB.Provider.YesSql (This package)
          ↓ ADO.NET Provider
   SharpCoreDB.Data.Provider (DbConnection, DbCommand)
          ↓
   SharpCoreDB Core Engine
          ↓
   Encrypted .scdb file

Key Components

Why Sqlite Dialect?

YesSql generates SQL using a dialect abstraction. We use SqliteDialect because SharpCoreDB is Sqlite-compatible:

Installation

NuGet Package

dotnet add package SharpCoreDB.Provider.YesSql

From Source

git clone https://github.com/MPCoreDeveloper/SharpCoreDB.git
cd SharpCoreDB
dotnet build src/SharpCoreDB.Provider.YesSql

Quick Start

Basic Usage

using Microsoft.Extensions.DependencyInjection;
using SharpCoreDB.Provider.YesSql;

var services = new ServiceCollection();

// Add YesSql with SharpCoreDB
services.AddYesSqlWithSharpCoreDB(
    connectionString: "Data Source=./orchardcore.scdb;Password=StrongPassword123",
    tablePrefix: "oc_",
    isolationLevel: IsolationLevel.ReadCommitted);

var serviceProvider = services.BuildServiceProvider();
var store = serviceProvider.GetRequiredService<IStore>();

// Use YesSql normally
using var session = store.CreateSession();

// Save a document
var user = new User { Name = "Alice", Email = "alice@example.com" };
session.Save(user);
await session.SaveChangesAsync();

// Query documents
var users = await session.Query<User>().Where(u => u.Name == "Alice").ListAsync();

OrchardCore Integration

// In your OrchardCore startup
public void ConfigureServices(IServiceCollection services)
{
    services.AddOrchardCore()
        .AddSetupFeatures("OrchardCore.AutoSetup")
        .ConfigureServices((tenant, services) =>
        {
            // Use SharpCoreDB instead of SQLite
            services.AddYesSqlWithSharpCoreDB(
                connectionString: $"Data Source=./App_Data/Sites/{tenant}/yessql.scdb;Password={GetPassword()}",
                tablePrefix: tenant + "_");
        });
}

Configuration

Connection String Format

Data Source=<path>;Password=<password>[;Option=value]

Parameters:

• Data Source - Path to .scdb file (required)
• Password - Encryption password (required)

Example:

Data Source=./App_Data/orchardcore.scdb;Password=MySecurePassword123

Configuration Options

services.AddYesSqlWithSharpCoreDB(
    connectionString: "Data Source=./db.scdb;Password=pwd",
    tablePrefix: "oc_",                      // Multi-tenancy support
    isolationLevel: IsolationLevel.ReadCommitted  // Transaction isolation
);

How It Works

1. SQL Generation (YesSql → Sqlite Dialect)

YesSql uses SqliteDialect to generate SQL:

// YesSql generates:
CREATE TABLE IF NOT EXISTS "oc_Document" (
    "Id" INTEGER PRIMARY KEY,
    "Type" TEXT,
    "Content" TEXT
)

// SharpCoreDB executes:
CreateTable("oc_Document", columns: [...])

2. ADO.NET Provider (YesSql → SharpCoreDB)

The provider implements standard ADO.NET interfaces:

// YesSql calls:
var connection = connectionFactory.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM oc_Document WHERE Type = @p0";
command.Parameters.Add("p0", "User");
var reader = command.ExecuteReader();

3. SQLite System Table Interceptie

Problem: YesSql queries sqlite_master to discover tables:

SELECT name FROM sqlite_master WHERE type='table'

Solution: SharpCoreDB intercepts these queries and redirects to native metadata API:

// In SharpCoreDBCommand.ExecuteDbDataReader():
if (commandTextUpper.Contains("SQLITE_MASTER"))
{
    return ExecuteSystemTableQuery(commandTextUpper, behavior);
}

// ExecuteSystemTableQuery() uses native API:
if (db is IMetadataProvider metadata)
{
    var tables = metadata.GetTables(); // ✅ Native SharpCoreDB API
    
    // Convert to SQLite format for YesSql
    var results = tables.Select(t => new Dictionary<string, object>
    {
        ["name"] = t.Name,
        ["type"] = "TABLE"
    });
    
    return new SharpCoreDBDataReader(results, behavior);
}

Supported system queries:

• SELECT name FROM sqlite_master WHERE type='table'
• PRAGMA table_info('tablename')
• SELECT * FROM pragma_table_info('tablename')

4. last_insert_rowid() Support

YesSql expects SELECT last_insert_rowid() after INSERT to get the new row ID:

// YesSql executes:
INSERT INTO oc_Document (Type, Content) VALUES (@p0, @p1);
SELECT last_insert_rowid();

// SharpCoreDB handles via:
public long GetLastInsertRowId()
{
    return _lastInsertRowId; // Thread-safe AsyncLocal<long>
}

Thread Safety:
Each thread has its own last_insert_rowid value via AsyncLocal<long>, ensuring correct behavior in concurrent scenarios.

Performance

vs SQLite (Direct)

vs LiteDB (Alternative)

Encryption Overhead

Surprising Result: Hardware-accelerated AES-NI makes encryption actually faster than unencrypted mode!

Compatibility

YesSql Features

OrchardCore Compatibility

SQL Features

Troubleshooting

Common Issues

1. "Table sqlite_master does not exist"

Cause: SharpCoreDB doesn't have a real sqlite_master table.

Solution: ✅ Already handled! The ADO.NET provider intercepts these queries automatically.

Verification:

var connection = new SharpCoreDBConnection("Data Source=test.scdb;Password=pwd");
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT name FROM sqlite_master WHERE type='table'";
var reader = command.ExecuteReader(); // ✅ Works!