### Basic Query Optimization Setup (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/Optimization/OPTIMIZER_GUIDE.md

Provides a C# example for setting up and using the main `QueryOptimizer`. It shows how to instantiate the optimizer with its components and apply it to a `SelectNode`.

```csharp
// Setup components
var costEstimator = new CostEstimator(tableStatistics);
var predicatePushdown = new PredicatePushdown();
var subqueryOptimizer = new SubqueryOptimizer();
var joinReorderer = new JoinReorderer();

// Create optimizer
var optimizer = new QueryOptimizer(
    costEstimator,
    predicatePushdown,
    subqueryOptimizer,
    joinReorderer);

// Optimize parsed AST
var physicalPlan = optimizer.Optimize(selectNode);

// Inspect plan
foreach (var step in physicalPlan.Steps)
{
    Console.WriteLine($"{step.Type}: {step.TableName ?? "N/A"}");
}
```

--------------------------------

### Quick Start: Logging with SharpCoreDB

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Serilog.Sinks/PACKAGE_STATUS.md

Demonstrates how to configure Serilog to use the SharpCoreDB sink for logging application events. This example shows basic setup and logging an information message.

```csharp
using Serilog;
using SharpCoreDB.Serilog.Sinks;

Log.Logger = new LoggerConfiguration()
    .WriteTo.SharpCoreDB("logs.scdb", "password")
    .CreateLogger();

Log.Information("Hello, SharpCoreDB!");
Log.CloseAndFlush();
```

--------------------------------

### Start and Monitor Replication Health (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/distributed/README.md

This example shows how to enable comprehensive monitoring for replication, start the monitoring service, and set up event handlers for critical health degradation alerts.

```csharp
// Enable comprehensive monitoring
var monitor = new ReplicationMonitor(logger);
await monitor.StartAsync();

// Set up alerts for critical metrics
monitor.OnHealthDegraded += (nodeId, issues) =>
{
    logger.LogWarning("Replication health degraded for {Node}: {Issues}", nodeId, issues);
    // Send alerts, trigger failover, etc.
};
```

--------------------------------

### Basic SharpCoreDB Setup in C#

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/scdb/PRODUCTION_GUIDE.md

Demonstrates the basic setup and usage of SharpCoreDB in a C# application. It configures services, initializes the database in SCDB mode, and executes simple SQL commands.

```csharp
using SharpCoreDB;
using SharpCoreDB.Storage;
using Microsoft.Extensions.DependencyInjection;

// Configure services
var services = new ServiceCollection();
services.AddSingleton<ICryptoService, CryptoService>();
var provider = services.BuildServiceProvider();

// Create SCDB database
var options = new DatabaseOptions
{
    StorageMode = StorageMode.SingleFile,  // ✅ Use SCDB format
    PageSize = 4096,                        // 4KB pages (default)
    CreateImmediately = true,
    EnableWalCheckpointing = true,          // ✅ Auto-checkpoint
    WalCheckpointIntervalMs = 30000,        // Checkpoint every 30s
};

using var db = new Database(
    provider,
    "./data/myapp.scdb",  // ✅ Single file!
    "your-master-password",
    isReadOnly: false,
    config: options
);

// Use database
db.ExecuteSQL("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
db.ExecuteSQL("INSERT INTO users VALUES (1, 'Alice')");
```

--------------------------------

### Quick Start Logging with SharpCoreDB Sink

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Serilog.Sinks/PROJECT_SUMMARY.md

This C# code snippet demonstrates the basic setup for using the SharpCoreDB sink with Serilog. It configures the logger to write logs to a SharpCoreDB database file with a specified password. Ensure the SharpCoreDB.Serilog.Sinks NuGet package is installed.

```csharp
Log.Logger = new LoggerConfiguration()
    .WriteTo.SharpCoreDB("logs.scdb", "password")
    .CreateLogger();
```

--------------------------------

### Filtered Sync with SharpCoreDB and Dotmim.Sync

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/proposals/DOTMIM_SYNC_PROVIDER_PROPOSAL.md

Demonstrates how to configure and perform filtered synchronization using the SharpCoreDB provider with Dotmim.Sync. This example shows server-side setup with filters and client-side initialization for syncing only a specific tenant's data.

```csharp
var serverProvider = new SqlSyncProvider(connectionString);
var setup = new SyncSetup("customers", "orders", "products");

// Filter: Only sync tenant_id = 42
setup.Filters.Add("customers", "tenant_id");
setup.Filters.Add("orders", "tenant_id");

// Client-side (SharpCoreDB)
var clientProvider = new SharpCoreDBSyncProvider
{
    ConnectionString = "Path=C:\\data\\local.scdb;Password=secret"
};

var agent = new SyncAgent(clientProvider, serverProvider);
var parameters = new SyncParameters(("tenant_id", 42));
var result = await agent.SynchronizeAsync(setup, parameters);
```

--------------------------------

### QueryOptimizerExtensions Integration Example (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/Optimization/OPTIMIZER_GUIDE.md

Demonstrates how QueryOptimizerExtensions integrates with a QueryCache to retrieve or optimize query plans. It shows the `GetOrAdd` method logic for caching and optimization.

```csharp
// In QueryCache
public void GetOrAdd(string sql, SelectNode query)
{
    // Get parsed AST from cache (existing)
    var parsed = cache.GetParsed(sql);
    
    // Get optimized plan from optimizer cache (new)
    var physical = OptimizerIntegration.GetOrOptimize(
        sql, parsed, costEstimator, ...);
    
    // Execute physical plan
    return ExecutePhysicalPlan(physical);
}
```

--------------------------------

### Initialize SharpCoreDB Database Instance

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Extensions/USAGE_GUIDE.md

Initializes a SharpCoreDB database instance using the DatabaseFactory. This setup is required before performing any database operations. It assumes a service provider is available.

```csharp
using SharpCoreDB;
using SharpCoreDB.Extensions;

var factory = new DatabaseFactory(serviceProvider);
using var db = factory.Create("./myapp.scdb", "YourPassword!");
```

--------------------------------

### Bash/PowerShell Backup Commands

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/migration/MIGRATION_GUIDE.md

These commands show how to back up a directory-based database before performing a migration. Examples are provided for both Linux/Mac (using tar) and Windows (using PowerShell).

```bash
# Linux/Mac
tar -czf mydb_backup.tar.gz mydb/
```

```powershell
# Windows
Compress-Archive -Path mydb\ -DestinationPath mydb_backup.zip
```

--------------------------------

### SQL: Basic String Comparison Examples

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/collation/COLLATION_GUIDE.md

Demonstrates the difference between case-sensitive (binary) and case-insensitive (NOCASE) string comparisons in SQL queries. This highlights how collation affects search results.

```sql
-- Without collation (Binary/case-sensitive):
SELECT * FROM users WHERE name = 'alice';
-- Returns: 0 rows (only matches "alice" exactly)

-- With NOCASE collation:
SELECT * FROM users WHERE name = 'alice';
-- Returns: 1 row (matches "alice", "Alice", "ALICE", etc.)
```

--------------------------------

### Query Examples: JOINs with Collation

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/collation/COLLATION_GUIDE.md

Illustrates performing JOIN operations with collations. It highlights potential warnings due to collation mismatches between joined columns and shows how to explicitly match collations for accurate joins.

```sql
-- JOIN with collation mismatch warning
SELECT u.name, o.user_name
FROM users u
JOIN orders o ON u.name = o.user_name;
-- Warning: Column collations differ (BINARY vs NOCASE)

-- Explicit collation to match
SELECT u.name, o.user_name
FROM users u
JOIN orders o ON u.name COLLATE NOCASE = o.user_name COLLATE NOCASE;
```

--------------------------------

### Running SharpCoreDB Example (Bash)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/Examples/Desktop/SharpCoreDB.Examples.TimeSeries/README.md

Provides the command-line instructions to navigate to the example directory and run the SharpCoreDB time-series metrics collector application using the .NET CLI.

```bash
cd Examples/Desktop/SharpCoreDB.Examples.TimeSeries
dotnet run

```

--------------------------------

### Restore and Build OrchardCore Example Project

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/Examples/Web/Orchardcore/README.md

Restores project dependencies and builds the SharpCoreDB OrchardCore example project. This command assumes the OrchardCore preview NuGet feed has already been added.

```bash
dotnet restore Examples/Web/Orchardcore/SharpCoreDb.Orchardcore/SharpCoreDb.Orchardcore.csproj
dotnet build Examples/Web/Orchardcore/SharpCoreDb.Orchardcore/SharpCoreDb.Orchardcore.csproj
```

--------------------------------

### Quickstart: Initialize and Use SharpCoreDB with Async API

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/README_NUGET.md

This C# code demonstrates how to set up SharpCoreDB using dependency injection, create a database instance, define a table with a B-tree index, perform fast inserts, and execute queries using the recommended asynchronous API. It highlights support for large data storage.

```csharp
using Microsoft.Extensions.DependencyInjection;
using SharpCoreDB;

var services = new ServiceCollection();
services.AddSharpCoreDB();
var provider = services.BuildServiceProvider();
var factory = provider.GetRequiredService<DatabaseFactory>();

using var db = factory.Create("./app_db", "StrongPassword!");

// Create table with B-tree index
await db.ExecuteSQLAsync("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
await db.ExecuteSQLAsync("CREATE INDEX idx_age ON users(age) USING BTREE");

// Fast inserts with async API (recommended)
await db.ExecuteSQLAsync("INSERT INTO users VALUES (1, 'Alice', 30)");

// Fast queries with async batch API
var rows = await db.ExecuteQueryAsync("SELECT * FROM users WHERE age > 25");

// Support for large data (>256KB)
var largeData = new byte[10_000_000]; // 10MB
await db.ExecuteSQLAsync("INSERT INTO files VALUES (1, @data)");
```

--------------------------------

### Install SharpCoreDB NuGet Package

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/scdb/PRODUCTION_GUIDE.md

Installs the necessary SharpCoreDB NuGet package for .NET development. Ensure the .NET 10 Runtime or higher is installed.

```bash
# .NET 10 Runtime (required)
dotnet --version  # Should show 10.0 or higher

# NuGet Package
dotnet add package SharpCoreDB --version 2.0.0
```

--------------------------------

### Automated C# Installer for SharpCoreDB Provider

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Data.Provider/REGISTRATION.md

Installs the SharpCoreDB ADO.NET Data Provider using a C# executable. Supports GAC installation and silent mode. Includes uninstallation commands.

```cmd
SharpCoreDB.Installer.exe
SharpCoreDB.Installer.exe /gac
SharpCoreDB.Installer.exe /uninstall /gac
SharpCoreDB.Installer.exe /silent /gac
```

--------------------------------

### Automated PowerShell Installation for SharpCoreDB Provider

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Data.Provider/REGISTRATION.md

Installs the SharpCoreDB ADO.NET Data Provider using PowerShell scripts. Supports GAC installation for SSMS and other tools. Includes verification and uninstallation commands.

```powershell
cd SharpCoreDB.Data.Provider
.Install-SharpCoreDBProvider.ps1
.Install-SharpCoreDBProvider.ps1 -InstallToGAC
.Test-SharpCoreDBProvider.ps1 -CheckGAC
.Uninstall-SharpCoreDBProvider.ps1 -UninstallFromGAC
```

--------------------------------

### Create and Configure C# Project with SharpCoreDB

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/analytics/TUTORIAL.md

This code snippet demonstrates how to create a new console application, navigate into the project directory, and add the necessary NuGet packages for SharpCoreDB and its analytics extension. This sets up the project environment for using the analytics engine.

```bash
dotnet new console -n AnalyticsDemo
cd AnalyticsDemo
dotnet add package SharpCoreDB --version 1.3.5
dotnet add package SharpCoreDB.Analytics --version 1.3.5
```

--------------------------------

### Dependency Injection Setup in C#

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Extensions/README.md

Illustrates a typical dependency injection setup in C# for SharpCoreDB, registering generic repositories, user-specific repositories, and the UserService. It also shows the implementation of a UserRepository.

```csharp
services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
services.AddScoped<IUserRepository, UserRepository>();
services.AddScoped<UserService>();

// In UserRepository
public class UserRepository : Repository<User>, IUserRepository
{
    public UserRepository(IDatabase database) 
        : base(database, "users") { }

    public async Task<User> GetByNameAsync(string name)
    {
        return await QuerySingleAsync(
            "SELECT * FROM users WHERE name = ?",
            [name]
        );
    }
}
```

--------------------------------

### Install SharpCoreDB.Extensions Package

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Extensions/USAGE_GUIDE.md

Installs the SharpCoreDB.Extensions NuGet package. This is a prerequisite for using the extension methods provided by the library. Ensure you are using version 1.0.6 or later.

```bash
dotnet add package SharpCoreDB.Extensions   # v1.0.6+
```

--------------------------------

### SQL: NOCASE Collation Query Examples

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/collation/COLLATION_GUIDE.md

Provides practical SQL query examples demonstrating the use of NOCASE collation for case-insensitive searches, filtering with LIKE, and sorting results.

```sql
-- Case-insensitive search
SELECT * FROM users WHERE username = 'Alice';
-- Matches: 'alice', 'Alice', 'ALICE', 'aLiCe'

-- Case-insensitive filtering
SELECT * FROM products WHERE category LIKE '%ELECTRONICS%';
-- Matches: 'Electronics', 'electronics', 'ELECTRONICS'

-- Case-insensitive sorting
SELECT * FROM users ORDER BY username;
-- Groups: Alice, alice, ALICE (all together)
```

--------------------------------

### Quick Start: Using SharpCoreDB in C#

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/nuget/README.md

A C# code snippet demonstrating how to set up dependency injection, create a database instance, and perform basic SQL operations using the SharpCoreDB library.

```csharp
using Microsoft.Extensions.DependencyInjection;
using SharpCoreDB;

var services = new ServiceCollection();
services.AddSharpCoreDB();
var provider = services.BuildServiceProvider();
var factory = provider.GetRequiredService<DatabaseFactory>();

// Async API (recommended as of v1.1.1)
using var db = factory.Create("./app_db", "StrongPassword!");
await db.ExecuteSQLAsync("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
await db.ExecuteSQLAsync("INSERT INTO users VALUES (1, 'Alice')");
var rows = await db.ExecuteQueryAsync("SELECT * FROM users");
```

--------------------------------

### SQLite VectorDB Schema Example

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/migration/SQLITE_VECTORS_TO_SHARPCORE.md

Example of creating a table with a vector column (as BLOB) and a virtual table for vector indexing using the sqlite-vec extension. It also shows an example of inserting data and performing a combined FTS5 and vector search.

```sql
-- SQLite with sqlite-vec extension
CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding BLOB  -- vector as blob
);

-- Create vector index
CREATE VIRTUAL TABLE documents_vec USING vec0(
    embedding(1536)  -- 1536-dimensional vectors
);

-- Insert with FTS5 + vector search
INSERT INTO documents VALUES (1, 'Alice in Wonderland', 
    vector32_from_array(...));

-- Search: Combined FTS5 + vector
SELECT id, content FROM documents 
WHERE rowid IN (
    SELECT rowid FROM documents_vec 
    WHERE k = 5 AND threshold = 0.8
);
```

--------------------------------

### SharpCoreDBSyncProvider Usage Example

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/proposals/DOTMIM_SYNC_IMPLEMENTATION_PLAN.md

Demonstrates how to configure and resolve the SharpCoreDBSyncProvider from the service container after registration. This example shows setting connection string and customizing options like auto-tracking and tombstone retention.

```csharp
// Program.cs
services.AddSharpCoreDBSync(
    connectionString: "Path=C:\\data\\local.scdb;Password=secret",
    options: opts => {
        opts.EnableAutoTracking = true;
        opts.TombstoneRetentionDays = 30;
    });

// Later: resolve from container
var provider = serviceProvider.GetRequiredService<SharpCoreDBSyncProvider>();
```

--------------------------------

### Cached Optimization Example (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/Optimization/OPTIMIZER_GUIDE.md

Illustrates the benefit of query caching with the `OptimizerIntegration`. The example shows that the second call to `GetOrOptimize` for the same query retrieves the plan from the cache, avoiding re-optimization.

```csharp
// First execution: optimize
var plan1 = OptimizerIntegration.GetOrOptimize(
    "SELECT * FROM orders WHERE id > 100",
    selectNode, costEstimator, ...);

// Second execution: retrieve from cache
var plan2 = OptimizerIntegration.GetOrOptimize(
    "SELECT * FROM orders WHERE id > 100",
    selectNode, costEstimator, ...);

// plan1 and plan2 are identical, cache hit!
```

--------------------------------

### Initialize SharpCoreDB with Dependency Injection

Source: https://context7.com/mpcoredeveloper/sharpcoredb/llms.txt

Demonstrates how to register SharpCoreDB services, create encrypted file-based or in-memory database instances, and execute initial SQL commands using the DatabaseFactory pattern. Includes options for custom configurations.

```csharp
using Microsoft.Extensions.DependencyInjection;
using SharpCoreDB;

// Register services
var services = new ServiceCollection();
services.AddSharpCoreDB();
var provider = services.BuildServiceProvider();

// Get database factory
var factory = provider.GetRequiredService<DatabaseFactory>();

// Create encrypted file-based database
using var db = factory.Create(
    path: "./myapp.db",
    password: "YourStrongPassword123!"
);

// Or create in-memory database for testing
using var testDb = factory.CreateInMemory("TestDB");

// Create with custom options
var options = new DatabaseOptions
{
    StorageMode = StorageMode.PageBased,
    PageSize = 4096,
    EnableWal = true,
    CacheSize = 256
};
using var customDb = factory.Create("./data/mydb", "password", options);

// Execute SQL and persist data
db.ExecuteSQL("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
db.ExecuteSQL("INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')");
db.Flush();      // Flush WAL to disk
db.ForceSave();  // Force checkpoint
```

--------------------------------

### UNICODE Collation Query Examples

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/collation/COLLATION_GUIDE.md

Provides SQL query examples demonstrating the use of UNICODE collation for international name matching, diacritic-insensitive searches using LIKE, and multi-language sorting.

```sql
-- International name matching
SELECT * FROM contacts WHERE name = 'José';
-- Matches: 'jose', 'José', 'JOSÉ', 'Jose'

-- Diacritic-insensitive search
SELECT * FROM products WHERE description LIKE '%cafe%' COLLATE UNICODE;
-- Matches: 'cafe', 'café', 'Café', 'CAFÉ'

-- Multi-language sorting
SELECT * FROM directory ORDER BY name COLLATE UNICODE;
-- Proper ordering for: José, João, Jürgen, Jean
```

--------------------------------

### Bidirectional Traversal Example (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/graphrag/PHASE5_1_FEATURE_GUIDE.md

Example demonstrating the Bidirectional graph traversal strategy. This approach is useful for finding connections between nodes by exploring from both the start and end points simultaneously.

```csharp
var results = await context.Users
    .GraphTraverse(userId, "FriendId", 5)
    .WithStrategy(GraphTraversalStrategy.Bidirectional)
    .ToListAsync();
```

--------------------------------

### Development Setup and Commands for SharpCoreDB

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/CONTRIBUTING.md

This snippet provides essential bash commands for setting up the SharpCoreDB development environment. It includes cloning the repository, configuring remotes, restoring dependencies, building the solution, and running tests and benchmarks.

```bash
# Clone your fork
git clone https://github.com/YOUR-USERNAME/SharpCoreDB.git
cd SharpCoreDB

# Add upstream remote
git remote add upstream https://github.com/MPCoreDeveloper/SharpCoreDB.git

# Restore dependencies
dotnet restore

# Build the solution
dotnet build

# Run tests
dotnet test

# Run benchmarks (optional)
cd tests/SharpCoreDB.Benchmarks
dotnet run -c Release
```

--------------------------------

### Query Examples: GROUP BY with Collation

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/collation/COLLATION_GUIDE.md

Shows how to group data using collations. Examples include grouping case-insensitively to combine variations of a username and grouping international names to count occurrences accurately.

```sql
-- Group case-insensitively
SELECT username, COUNT(*) 
FROM login_attempts 
GROUP BY username COLLATE NOCASE;
-- Result: Groups 'alice', 'Alice', 'ALICE' together

-- Group by international names
SELECT name, COUNT(*)
FROM customers
GROUP BY name COLLATE UNICODE;
```

--------------------------------

### Install SharpCoreDB Sync Provider and Dependencies

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/sync/README.md

Installs the SharpCoreDB.Provider.Sync package and the core Dotmim.Sync package using the .NET CLI. Also installs a specific database provider package.

```bash
# Add the sync provider
dotnet add package SharpCoreDB.Provider.Sync --version 1.0.0

# Add Dotmim.Sync core
dotnet add package Dotmim.Sync.Core --version 1.3.0

# Add provider for your target database
dotnet add package Dotmim.Sync.SqlServer --version 1.3.0
# OR
dotnet add package Dotmim.Sync.PostgreSql --version 1.3.0
# OR
dotnet add package Dotmim.Sync.MySql --version 1.3.0
```

--------------------------------

### Quick Start: Configure and Use Distributed Database in C#

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.Distributed/NuGet.README.md

Demonstrates how to configure sharding, create a distributed table, and insert data using SharpCoreDB.Distributed. It requires the SharpCoreDB.Distributed library and .NET 10.0+.

```csharp
using SharpCoreDB.Distributed;

// Configure sharding
var shardConfig = new ShardConfiguration
{
    ShardCount = 4,
    ShardKey = new HashShardKey("UserId"),
    ReplicasPerShard = 2
};

var distributedDb = new DistributedDatabase(shardConfig);

// Create distributed table
await distributedDb.ExecuteAsync(@"
    CREATE TABLE Users (
        UserId INT PRIMARY KEY,
        Name TEXT,
        Email TEXT
    ) SHARDED BY UserId
");

// Data is automatically distributed across shards
await distributedDb.ExecuteAsync(
    "INSERT INTO Users VALUES (1, 'Alice', 'alice@example.com')"
);
```

--------------------------------

### Query Examples: ORDER BY with Collation

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/collation/COLLATION_GUIDE.md

Demonstrates sorting data using different collations. Examples include case-insensitive sorting (NOCASE), sorting with accents grouped (UNICODE), and reverse case-insensitive sorting.

```sql
-- Sort case-insensitively
SELECT * FROM users ORDER BY username COLLATE NOCASE;

-- Sort with accents grouped
SELECT * FROM products ORDER BY name COLLATE UNICODE;

-- Reverse case-insensitive sort
SELECT * FROM categories ORDER BY name COLLATE NOCASE DESC;
```

--------------------------------

### Quick Start: Using SharpCoreDB Query Plan Cache

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/QUERY_PLAN_CACHE.md

Demonstrates the basic usage of SharpCoreDB's query plan caching. It shows how the first execution incurs a small overhead for plan creation and caching, while subsequent executions with identical SQL and parameters reuse the cached plan for a significant speedup. It also shows how to retrieve cache statistics.

```csharp
using var db = new Database(services, "./mydb", "password");

// First execution: Plan created and cached (~100 µs overhead)
db.ExecuteSQL("INSERT INTO users (name) VALUES (?)", 
    new Dictionary<string, object?> { ["@p0"] = "Alice" });

// Second execution: Plan reused from cache (~0.5 µs overhead)
db.ExecuteSQL("INSERT INTO users (name) VALUES (?)", 
    new Dictionary<string, object?> { ["@p0"] = "Bob" });

// Cache statistics
var (hits, misses, hitRate, count) = db.GetPlanCacheStats();
// hits: 1, misses: 1, hitRate: 0.5 (50%), count: 1
```

--------------------------------

### JoinReorderer Usage Example (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/Optimization/OPTIMIZER_GUIDE.md

Shows how to use the JoinReorderer in C# to optimize the order of join operations. The example suggests reordering joins based on table size to minimize intermediate results.

```csharp
var reorderer = new JoinReorderer();
logicalPlan = reorderer.ReorderJoins(logicalPlan, costEstimator);

// BEFORE: orders ⋈ customers ⋈ products
// AFTER:  customers ⋈ orders ⋈ products (customers likely smallest)
```

--------------------------------

### SQL Scalar Subquery Example

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/Execution/SUBQUERY_INTEGRATION_GUIDE.md

Illustrates the usage of scalar subqueries within the SELECT list to retrieve aggregated values for each row. This example shows calculating the average salary and the difference from the average for each employee.

```sql
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) as avg_salary,
    salary - (SELECT AVG(salary) FROM employees) as diff
FROM employees;
```

--------------------------------

### DI vs. Manual Instantiation for SharpCoreDBSyncProvider

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/proposals/ADD_IN_PATTERN_SUMMARY.md

Illustrates the shift from manually instantiating the SharpCoreDBSyncProvider to using dependency injection for a more streamlined and maintainable approach.

```csharp
// Instead of manual instantiation:
// var provider = new SharpCoreDBSyncProvider { ConnectionString = "..." };

// Use DI:
services.AddSharpCoreDBSync("...");
var provider = serviceProvider.GetRequiredService<SharpCoreDBSyncProvider>();
```

--------------------------------

### Dependency Injection Setup

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB.EntityFrameworkCore/README.md

Shows how to configure SharpCoreDB and related services (like repositories and services) using dependency injection in a .NET application.

```APIDOC
## Dependency Injection Setup

### Description
Configuring SharpCoreDB and custom services using the built-in dependency injection container.

### Code Example
```csharp
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSharpCoreDB("Data Source=./app.db;Password=secure!");
});

services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
services.AddScoped<UserService>();
```
```

--------------------------------

### Install SharpCoreDB .NET Packages

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/Vectors/VECTOR_MIGRATION_GUIDE.md

Installs the necessary SharpCoreDB packages for .NET projects. This command-line instruction adds the core SharpCoreDB library and its vector search component to your project dependencies.

```bash
dotnet add package SharpCoreDB --version 1.2.0
dotnet add package SharpCoreDB.VectorSearch
```

--------------------------------

### SharpCoreDB Performance Metrics Example in C#

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/serialization/SERIALIZATION_AND_STORAGE_GUIDE.md

Provides an example of how to retrieve and interpret performance metrics from SharpCoreDB's block registry. It shows sample output and explains how to derive insights into the efficiency of write operations.

```csharp
// Monitor performance
var metrics = blockRegistry.GetMetrics();
// (TotalFlushes, BatchedFlushes, BlocksWritten, DirtyCount)
// Example output: (10, 10, 1000, 0)
// Interpretation: 1000 blocks written in 10 batched flushes = 100 blocks/flush

```

--------------------------------

### Utilize Diagnostic Tools for Sync with C#

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/sync/README.md

Shows how to use diagnostic tools in C# to get synchronization summaries, inspect database schemas, and validate the sync setup. It includes fetching validation errors if the setup is invalid.

```csharp
// Get sync summary
var summary = await agent.GetSummaryAsync();
Console.WriteLine($"Last sync: {summary.LastSync}");
Console.WriteLine($"Total changes: {summary.TotalChanges}");

// Check database schema
var schema = await agent.GetSchemaAsync();
foreach (var table in schema.Tables)
{
    Console.WriteLine($"Table: {table.TableName}, Columns: {table.Columns.Count}");
}

// Validate sync setup
var isValid = await agent.ValidateSetupAsync();
if (!isValid)
{
    var errors = await agent.GetValidationErrorsAsync();
    foreach (var error in errors)
    {
        Console.WriteLine($"Validation error: {error.Message}");
    }
}
```

--------------------------------

### SQL HAVING with Subquery Example

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/src/SharpCoreDB/Execution/SUBQUERY_INTEGRATION_GUIDE.md

Shows how to use a subquery within the HAVING clause to filter groups based on an aggregated condition that involves comparing with a global aggregate. This example selects departments where the average salary is greater than the overall average salary.

```sql
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
```

--------------------------------

### C# Sync Provider Usage Example

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/proposals/QUICK_REFERENCE.md

Demonstrates how to set up and use the SharpCoreDBSyncProvider for bidirectional data synchronization. It covers client and server provider configuration, defining sync scopes, applying filters for multi-tenancy, and initiating the synchronization process with specific parameters.

```csharp
// Setup
var clientProvider = serviceProvider.GetRequiredService<SharpCoreDBSyncProvider>();
var serverProvider = new SqlSyncProvider(serverConnectionString);

// Define sync scope
var setup = new SyncSetup("customers", "orders", "products");

// Setup filters for multi-tenant
setup.Filters.Add("customers", "tenant_id");
setup.Filters.Add("orders", "tenant_id");

// Create agent and sync
var agent = new SyncAgent(clientProvider, serverProvider);
var parameters = new SyncParameters(("tenant_id", 42)); // Sync only tenant 42
var result = await agent.SynchronizeAsync(setup, parameters);

// Result includes:
// - Changes applied
// - Conflicts resolved
// - Duration/stats
```

--------------------------------

### C# Example: Manual vs. Server-Side Aggregation

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/graphrag/PHASE9_KICKOFF.md

Demonstrates the difference between manual, in-memory aggregation and efficient server-side aggregation using SharpCoreDB. The 'Before' example loads all data into memory, which is inefficient for large datasets. The 'After' example leverages Phase 9 features to perform aggregation directly on the database.

```csharp
// Users had to do this manually:
var orders = await db.Orders.ToListAsync();
var groupedByCustomer = orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new {
        CustomerId = g.Key,
        Total = g.Sum(o => o.Amount),
        Count = g.Count()
    })
    .ToList();
// Problem: Loads ALL data into memory! ❌
```

```csharp
// Phase 9 pushes aggregation to database:
var stats = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new {
        CustomerId = g.Key,
        Total = g.Sum(o => o.Amount),
        Count = g.Count()
    })
    .ToListAsync();
// Benefits: Only aggregates returned, memory efficient ✅
```

--------------------------------

### GRAPH_TRAVERSE SQL Function Example (SQL)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/graphrag/GRAPHRAG_PROPOSAL_ANALYSIS.md

An example of using the GRAPH_TRAVERSE SQL function to perform a Breadth-First Search (BFS) traversal. It demonstrates how to find nodes within a specified depth from a starting node, using a given relationship column.

```sql
-- BFS traversal: find all nodes within 2 hops of node_id=5
SELECT * FROM documents 
WHERE doc_id IN (
    GRAPH_TRAVERSE(
        table => 'documents',
        start_node => 5,
        relationship_column => 'references',
        max_depth => 2,
        strategy => 'BFS'
    )
);
```

--------------------------------

### Metrics Query Examples

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/Examples/Desktop/SharpCoreDB.Examples.TimeSeries/README.md

Demonstrates how to query metrics using the MetricsAnalytics component. It shows methods for retrieving average metric values over a time window, the peak metric with its timestamp, and detecting anomalies based on a threshold.

```csharp
// Get average metric over time window
GetAverageMetric("CPU", TimeSpan.FromHours(1))
    → Returns: 28.43% (average CPU usage last hour)

// Get peak metric with timestamp
GetPeakMetric("Memory", TimeSpan.FromHours(24))
    → Returns: (512.45 MB, 2025-01-29T14:32:15Z)

// Detect metrics exceeding threshold
DetectAnomalies("CPU", threshold: 80, TimeSpan.FromHours(6))
    → Returns: List of times when CPU > 80%

```

--------------------------------

### C# SharpCoreDB Vector Search Example

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/Vectors/VECTOR_MIGRATION_GUIDE.md

Illustrates a vector search query using SharpCoreDB's native vector engine. This example performs an equivalent search to the SQLite version but demonstrates the significant performance improvement achieved with SharpCoreDB.

```csharp
var stopwatch = Stopwatch.StartNew();
var results = await db.ExecuteQueryAsync(@"
    SELECT id, content FROM documents
    WHERE vec_distance('cosine', embedding, @query) > 0.8
    ORDER BY vec_distance DESC
    LIMIT 10"
);
stopwatch.Stop();
Console.WriteLine($"Search: {stopwatch.ElapsedMilliseconds}ms");  // 2ms
```

--------------------------------

### Build SharpCoreDB Provider Sync Project (Bash)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/proposals/PHASE1_DELIVERY.md

Command to build the SharpCoreDB.Provider.Sync project using the .NET CLI. This is a prerequisite for testing and packaging.

```bash
dotnet build src/SharpCoreDB.Provider.Sync/SharpCoreDB.Provider.Sync.csproj
```

--------------------------------

### Enable Replication for Migration (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/distributed/README.md

This code demonstrates enabling replication capabilities for a distributed setup during a migration process, initializing the MultiMasterReplicationManager and starting its asynchronous operations.

```csharp
// Add replication capabilities
var replicationManager = new MultiMasterReplicationManager(shardManager, conflictResolver);
await replicationManager.StartAsync();
```

--------------------------------

### Cache Eviction Example (LRU)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/QUERY_PLAN_CACHE.md

Provides a conceptual example of the Least Recently Used (LRU) cache eviction strategy. When the cache reaches its capacity, the oldest entry is removed to make space for a new one, maintaining a set of recently accessed query plans.

```text
Capacity: 3 entries
Current: [Entry1, Entry2, Entry3]

New INSERT → Cache full
Evict: Entry3 (oldest)
Result: [Entry4, Entry1, Entry2]
```

--------------------------------

### SharpCoreDBSyncProvider Implementation (C#)

Source: https://github.com/mpcoredeveloper/sharpcoredb/blob/master/docs/proposals/DOTMIM_SYNC_PROVIDER_PROPOSAL.md

Demonstrates the C# implementation of the SharpCoreDBSyncProvider class, which extends Dotmim.Sync's CoreProvider. This class is responsible for establishing connections to SharpCoreDB and is configured with a connection string.

```csharp
/// <summary>
/// Dotmim.Sync provider for SharpCoreDB encrypted database engine.
/// Enables bidirectional synchronization between SharpCoreDB and any Dotmim.Sync-supported database.
/// </summary>
public sealed class SharpCoreDBSyncProvider : CoreProvider
{
    public required string ConnectionString { get; init; }

    // CoreProvider overrides for SharpCoreDB-specific implementations
    public override DbConnection CreateConnection() => new SharpCoreDBConnection(ConnectionString);
    public override string GetDatabaseName() => /* extract from connection string */;
    // ... additional overrides
}
```