### Run Interactive REPL

Source: https://csharpdb.com/docs/tutorials/storage-examples.html

Starts the interactive REPL for the storage study examples. Ensure you have the .NET SDK installed.

```bash
dotnet run --project samples/storage-tutorials/examples/StorageStudyExamples.Repl/StorageStudyExamples.Repl.csproj
```

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

### Run Interactive Storage Examples REPL

Source: https://csharpdb.com/docs/samples.html

Clone the repository and navigate to the examples directory to run the interactive REPL for storage tutorials.

```bash
cd samples/storage-tutorials/examples
dotnet run --project StorageStudyExamples.Repl
```

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

### Getting Started with Collections

Source: https://csharpdb.com/docs/collections.html

Demonstrates how to open a database and get a typed collection for storing C# objects.

```APIDOC
## Getting Started with Collections

Collections serialize C# objects to JSON and store them in B+trees. Each document is keyed by a `string` identifier, and `GetCollectionAsync<T>("name")` opens or creates the collection.

```csharp
public record Customer(
    string Name,
    string Email,
    Address Address,
    string[] Tags
);

public record Address(string City, string State, string Zip);

await using var db = await Database.OpenAsync("myapp.db");
var customers = await db.GetCollectionAsync<Customer>("customers");
```
```

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

### Install Dependencies

Source: https://csharpdb.com/docs/tutorials/native-ffi-javascript.html

Install the necessary npm packages for the Node.js examples. Ensure you have Node.js 18+ and the CSharpDB native library built.

```bash
cd samples/native-ffi/javascript
npm install
```

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

### Run Advanced Standalone Storage Example

Source: https://csharpdb.com/docs/samples.html

Navigate to a specific advanced example directory, such as CSharpDB.GraphDB, and run it directly.

```bash
cd samples/storage-tutorials/examples/CSharpDB.GraphDB
dotnet run
```

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

### REPL Example Mode Commands

Source: https://csharpdb.com/docs/tutorials/storage-examples.html

Common commands available within any loaded example in the REPL.

```text
  Common commands (available in all examples):
    demo                    Run the scripted demo
    sql <query>             Execute raw SQL
    back                    Return to main menu
    help                    Show commands for this example
    clear                   Clear the screen
    quit                    Exit the REPL
```

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

### Run CSharpDB Python Examples

Source: https://csharpdb.com/docs/tutorials/native-ffi-python.html

Navigate to the Python samples directory and execute the example scripts to see CSharpDB in action. These scripts demonstrate various database operations.

```shell
cd samples/native-ffi/python

python example_crud.py
python example_transactions.py
```

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

### Run CSharpDB Examples

Source: https://csharpdb.com/docs/tutorials/native-ffi-javascript.html

Execute the provided Node.js example scripts to test CSharpDB functionality. Alternatively, use the npm scripts defined in package.json.

```bash
node example_crud.mjs
node example_transactions.mjs
```

```bash
npm run crud
npm run transactions
```

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

### FileStorageDevice Usage Examples

Source: https://csharpdb.com/docs/storage-engine.html

Demonstrates common operations using the FileStorageDevice.

```APIDOC
## FileStorageDevice Usage Examples

### Create a New File
```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
Console.WriteLine($"File created. Length: {device.Length}"); // 0
```

### Write and Read Raw Bytes
```csharp
await using var device = new FileStorageDevice("mydb.cdb");
byte[] payload = "Hello, CSharpDB!"u8.ToArray();
await device.WriteAsync(offset: 0, payload);

var buffer = new byte[16];
int bytesRead = await device.ReadAsync(offset: 0, buffer);
Console.WriteLine(System.Text.Encoding.UTF8.GetString(buffer, 0, bytesRead));
```

### Pre-allocate / Extend File Length
```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
const int PageSize = 4096;
await device.SetLengthAsync(PageSize * 8);
Console.WriteLine($"Pre-allocated: {device.Length} bytes"); // 32768
```

### Flush to Disk (fsync)
```csharp
await device.WriteAsync(offset: 0, data);
await device.FlushAsync(); // durable on disk after this returns
```

### Injecting via IStorageDevice (Testability)
```csharp
// Production wiring
IStorageDevice device = new FileStorageDevice("mydb.cdb");
var pager = await Pager.CreateAsync(device, wal, walIndex);

// In a unit test -- swap in your own IStorageDevice implementation
```
```

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

### Setup and Indexing for Performance

Source: https://csharpdb.com/docs/performance.html

Initializes a database, creates a wide table, and defines indexes to optimize common query patterns. Ensure indexes match query predicates for best performance.

```csharp
using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("orders.db"))
    File.Delete("orders.db");

await using var db = await Database.OpenAsync("orders.db");

// Create a table with several columns (simulating a wide row)
await using (await db.ExecuteAsync (@"
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        customer_id INTEGER,
        created_at INTEGER,
        status TEXT,
        total REAL,
        notes TEXT,
        shipping_address TEXT
    )"))
{ } 

// Build indexes that match the query shapes we care about
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at)"))
{ } 
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_orders_status_created ON orders(status, created_at)"))
{ } 

// Seed 1000 rows
await db.BeginTransactionAsync();
for (int i = 1; i <= 1000; i++)
{
    string status = i % 3 == 0 ? "shipped" : i % 3 == 1 ? "pending" : "delivered";
    await using (await db.ExecuteAsync(
        $"INSERT INTO orders VALUES ({i}, {i % 50}, {1700000000 + i * 3600}, '{status}', {i * 1.5}, 'Note {i}', 'Address {i}')"))
    { } 
}
await db.CommitAsync();
```

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

### SQL Query Example

Source: https://csharpdb.com/docs/query-execution-pipeline.html

A sample SQL query used to demonstrate the query execution pipeline.

```sql
SELECT c.name, SUM(o.amount) AS total
                FROM customers c
                LEFT JOIN orders o ON o.cust_id = c.id
                WHERE c.country = 'USA'
                GROUP BY c.id, c.name
                ORDER BY total DESC
                LIMIT 10;
                
```

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

### REPL Main Menu Commands

Source: https://csharpdb.com/docs/tutorials/storage-examples.html

Commands available in the main menu of the REPL for managing examples.

```text
  Main menu:
    list                    List all available examples
    load <name>             Load an example
    help                    Show help
    clear                   Clear the screen
    quit                    Exit the REPL
```

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

### Virtual File System Commands

Source: https://csharpdb.com/docs/tutorials/storage-examples.html

Commands for interacting with the virtual file system example, which stores data in a single .cdb file.

```text
Command | Usage | Description  
---|---
`tree`| `tree`| Show the full directory tree  
`ls`| `ls [path]`| List contents of a directory  
`cd`| `cd <path>`| Change current directory  
`pwd`| `pwd`| Print current directory path  
`cat`| `cat <filename>`| Print file contents  
`mkdir`| `mkdir <name>`| Create a folder  
`touch`| `touch <name> [content...]`| Create a file  
`ln`| `ln <name> <target-path>`| Create a shortcut  
`rm`| `rm <name>`| Remove a file or folder (recursive)  
`mv`| `mv <name> <new-name>`| Rename an entry  
`info`| `info <name>`| Show entry details (type, size, created)  
`stats`| `stats`| Drive statistics (counts by type, total size)  
```

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

### Create Database Schema and Indexes

Source: https://csharpdb.com/docs/performance.html

Sets up the database schema and creates indexes on join keys and selective filter columns to optimize query performance. Ensure the database file is deleted to start fresh.

```csharp
using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("reporting.db"))
    File.Delete("reporting.db");

await using var db = await Database.OpenAsync("reporting.db");

// Schema
await using (await db.ExecuteAsync (@"
    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY, name TEXT, region TEXT)"))
{ }
await using (await db.ExecuteAsync (@"
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, created_at INTEGER)"))
{ }
await using (await db.ExecuteAsync (@"
    CREATE TABLE IF NOT EXISTS order_items (
        id INTEGER PRIMARY KEY, order_id INTEGER, product TEXT, quantity INTEGER, price REAL)"))
{ }

// Indexes on join keys and selective filter columns
await using (await db.ExecuteAsync (
    "CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)"))
{ }
await using (await db.ExecuteAsync (
    "CREATE INDEX IF NOT EXISTS idx_items_order ON order_items(order_id)"))
{ }
await using (await db.ExecuteAsync (
    "CREATE INDEX IF NOT EXISTS idx_customers_region ON customers(region)"))
{ }
```

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

### Run Storage Inspector (Human-Readable)

Source: https://csharpdb.com/docs/storage-inspector.html

Use this command to get a human-readable summary of the database state.

```bash
csharpdb inspect mydata.db
```

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

### Initialize Collection API

Source: https://csharpdb.com/docs/collections.html

Opens a database and gets a typed collection. Ensure the database file exists or will be created.

```csharp
public record Customer(
    string Name,
    string Email,
    Address Address,
    string[] Tags
);

public record Address(string City, string State, string Zip);

await using var db = await Database.OpenAsync("myapp.db");
var customers = await db.GetCollectionAsync<Customer>("customers");
```

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

### Create a New Database with Pager

Source: https://csharpdb.com/docs/storage-engine-reference.html

Initializes a new database file, including the file header page. Use when starting a new database from scratch.

```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
                var walIndex = new WalIndex();
                await using var wal = new WriteAheadLog("mydb.cdb", walIndex);
                await wal.OpenAsync(currentDbPageCount: 0);
                
                var pager = await Pager.CreateAsync(device, wal, walIndex);
                await pager.InitializeNewDatabaseAsync(); // writes file header (page 0)
                
                Console.WriteLine($"Pages: {pager.PageCount}"); // 1 (the file header page)
                

```

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

### General REPL Commands

Source: https://csharpdb.com/docs/cli.html

Basic commands for interacting with the REPL, including getting help and exiting.

```bash
.help
```

```bash
.quit / .exit
```

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

### Begin Transaction in REPL

Source: https://csharpdb.com/docs/cli.html

Starts a new transaction in the C# DB REPL. Subsequent commands will be part of this transaction until committed or rolled back.

```sql
.begin
```

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

### Run CSharpDB CLI Interactive REPL

Source: https://csharpdb.com/docs/cli.html

Starts the CSharpDB CLI in interactive mode. Opens a specified database file or defaults to 'csharpdb.db' in the current directory.

```bash
csharpdb mydata.db
```

```bash
csharpdb
```

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

### Update Package Usage

Source: https://csharpdb.com/docs/migrations.html

Use the `dotnet add package` command to install the `CSharpDB.Primitives` package, replacing the old `CSharpDB.Core` package.

```bash
# Before
dotnet add package CSharpDB.Core

# After
dotnet add package CSharpDB.Primitives
```

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

### Install CSharpDB Package

Source: https://csharpdb.com/docs/getting-started.html

Add the main CSharpDB package to your .NET project using the dotnet CLI. This package includes core components like the storage engine, SQL engine, Collection API, and ADO.NET provider.

```bash
dotnet add package CSharpDB
```

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

### Build ProductCacheService with CSharpDB

Source: https://csharpdb.com/docs/samples.html

Defines a typed service for an in-memory database, enabling efficient data access. Includes methods for getting products by SKU, category, category statistics, and bulk cache refresh. Requires CSharpDB.Engine and ILogger.

```csharp
using CSharpDB.Engine;

public sealed class ProductCacheService
{
    private readonly Database _db;
    private readonly ILogger<ProductCacheService> _logger;

    public ProductCacheService(Database db, ILogger<ProductCacheService> logger)
    {
        _db = db;
        _logger = logger;
    }

    /// <summary>Look up a product by SKU using the index.</summary>
    public async Task<CachedProduct?> GetBySkuAsync(string sku)
    {
        var result = await _db.ExecuteAsync(
            $"SELECT Id, Sku, Name, Price, Category FROM ProductCache WHERE Sku = '{sku}'");

        var rows = await result.ToListAsync();
        if (rows.Count == 0) return null;

        var r = rows[0];
        return new CachedProduct(
            r[0].AsInteger, r[1].AsText, r[2].AsText,
            r[3].AsReal, r[4].AsText);
    }

    /// <summary>Query products by category — uses the category index.</summary>
    public async Task<List<CachedProduct>> GetByCategoryAsync(string category)
    {
        var result = await _db.ExecuteAsync(
            $"SELECT Id, Sku, Name, Price, Category FROM ProductCache WHERE Category = '{category}' ORDER BY Name");

        var products = new List<CachedProduct>();
        await foreach (var r in result.GetRowsAsync())
        {
            products.Add(new(
                r[0].AsInteger, r[1].AsText, r[2].AsText,
                r[3].AsReal, r[4].AsText));
        }
        return products;
    }

    /// <summary>Aggregate cached data — avg price per category.</summary>
    public async Task<List<(string Category, double AvgPrice, long Count)>>
        GetCategoryStatsAsync()
    {
        var result = await _db.ExecuteAsync(@" 
            SELECT Category, AVG(Price), COUNT(*)
            FROM ProductCache
            GROUP BY Category
            ORDER BY COUNT(*) DESC");

        var stats = new List<(string, double, long)>();
        await foreach (var r in result.GetRowsAsync())
            stats.Add((r[0].AsText, r[1].AsReal, r[2].AsInteger));
        return stats;
    }

    /// <summary>Bulk-refresh the cache from an external source.</summary>
    public async Task RefreshAsync(IReadOnlyList<CachedProduct> products)
    {
        _logger.LogInformation("Refreshing cache with {Count} products", products.Count);

        // Atomic swap — readers see old data until commit
        await _db.ExecuteAsync("DELETE FROM ProductCache");

        var now = DateTime.UtcNow.ToString("o");
        foreach (var p in products)
        {
            await _db.ExecuteAsync(
                $"INSERT INTO ProductCache VALUES ({p.Id}, '{p.Sku}', '{p.Name}', {p.Price}, '{p.Category}', '{now}')");
        }

        _logger.LogInformation("Cache refresh complete");
    }
}

public record CachedProduct(
    long Id, string Sku, string Name,
    double Price, string Category);

```

```csharp
builder.Services.AddSingleton<ProductCacheService>();

```

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

### Run MCP Server with Different Targets

Source: https://csharpdb.com/docs/mcp-server.html

Demonstrates how to run the MCP server with default, database path, and explicit endpoint configurations.

```bash
# Default direct client target (ConnectionStrings:CSharpDB or csharpdb.db)
dotnet run --project src/CSharpDB.Mcp
```

```bash
# Direct client target via database path
dotnet run --project src/CSharpDB.Mcp -- --database mydata.db
```

```bash
# Explicit endpoint selection
dotnet run --project src/CSharpDB.Mcp -- --endpoint http://localhost:61818 --transport http
```

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

### Run All Micro Benchmarks (BenchmarkDotNet)

Source: https://csharpdb.com/docs/benchmarks.html

Execute all micro benchmarks using BenchmarkDotNet. Ensure you are in the correct directory before running.

```bash
cd tests/CSharpDB.Benchmarks
dotnet run -c Release -- --filter "*"
```

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

### Create Tables and Query Data with SQL

Source: https://csharpdb.com/docs/tutorials/storage.html

Use `ExecuteAsync` to create 'folders' and 'files' tables if they don't exist, and then query file names and sizes. Ensure the database connection is properly managed with `await using`.

```csharp
await using var db = await Database.OpenAsync("storage.cdb");

await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS folders (
        id          INTEGER PRIMARY KEY,
        path        TEXT NOT NULL,
        name        TEXT NOT NULL,
        created_at  TEXT NOT NULL
    )");

await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS files (
        id           INTEGER PRIMARY KEY,
        folder_path  TEXT NOT NULL,
        name         TEXT NOT NULL,
        content      TEXT NOT NULL,
        content_type TEXT NOT NULL,
        size_bytes   INTEGER NOT NULL,
        created_at   TEXT NOT NULL,
        updated_at   TEXT NOT NULL
    )");

await using var result = await db.ExecuteAsync(
    "SELECT name, size_bytes FROM files WHERE folder_path = '/documents'");
while (await result.MoveNextAsync())
    Console.WriteLine($"{result.Current[0]}  ({result.Current[1]} bytes)");
```

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

### Get Table Row Count

Source: https://csharpdb.com/docs/rest-api.html

Retrieves the total number of rows in a specified table.

```APIDOC
## GET /api/tables/{name}/count

### Description
Get the row count for a table.

### Method
GET

### Endpoint
/api/tables/{name}/count

### Parameters
#### Path Parameters
- **name** (string) - Required - The name of the table.

### Response
#### Success Response (200)
- **count** (integer) - The total number of rows in the table.

### Response Example
```json
{ "count": 42 }
```
```

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

### IExample Interface Definition

Source: https://csharpdb.com/docs/tutorials/storage-examples.html

Defines the basic contract for storage examples, including asynchronous disposal.

```csharp
public interface IExample : IAsyncDisposable
{
    string Name { get; }
    string CommandName { get; }
    string Description { get; }
    Task InitializeAsync(string workingDirectory);
    Task RunDemoAsync(TextWriter output);
}
```

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

### Create a New Database File

Source: https://csharpdb.com/docs/storage-engine-reference.html

Demonstrates creating a new database file using `FileStorageDevice`. The file will be created, and if it already exists, the operation will fail.

```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
                Console.WriteLine($"File created. Length: {device.Length}"); // 0
```

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

### SQL Expression-Level Collation

Source: https://csharpdb.com/docs/sql-reference.html

Example of applying a collation (NOCASE_AI) to an expression in a SELECT statement for ordering.

```sql
-- Expression-level collation
                SELECT * FROM products ORDER BY name COLLATE NOCASE_AI;
```

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

### SQL Column-Level Collation

Source: https://csharpdb.com/docs/sql-reference.html

Example of defining a column with a specific collation (NOCASE) during table creation.

```sql
-- Column-level collation
                CREATE TABLE products (
                    name TEXT COLLATE NOCASE
                );
```

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

### SQL Special Expressions

Source: https://csharpdb.com/docs/sql-reference.html

Shows examples of special expressions like BETWEEN, IN, NOT IN, LIKE, and NULL checks.

```sql
WHERE age BETWEEN 18 AND 65
```

```sql
WHERE status IN ('active', 'pending')
```

```sql
WHERE id NOT IN (1, 2, 3)
```

```sql
WHERE name LIKE 'J%'
```

```sql
WHERE code LIKE '100\%%' ESCAPE '\'
```

```sql
WHERE email IS NULL
```

```sql
WHERE email IS NOT NULL
```

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

### Stored Procedure Creation and Execution

Source: https://csharpdb.com/docs/sql.html

Demonstrates how to create a stored procedure with parameters and how to execute it using the C# client.

```csharp
// Create a procedure
await client.CreateProcedureAsync(new ProcedureDefinition
{
    Name = "GetEmployeesByDept",
    BodySql = "SELECT * FROM Employees WHERE DeptId = @deptId",
    Parameters = [
        new ProcedureParameterDefinition { Name = "deptId", Type = DbType.Integer }
    ]
});

// Execute
var result = await client.ExecuteProcedureAsync(
    "GetEmployeesByDept",
    new Dictionary<string, object?> { ["deptId"] = 10 });
```

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

### Create a New Database with Pager

Source: https://csharpdb.com/docs/storage-engine.html

Initializes a new database file, WAL, and Pager instance. Writes the initial file header.

```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
var walIndex = new WalIndex();
await using var wal = new WriteAheadLog("mydb.cdb", walIndex);
await wal.OpenAsync(currentDbPageCount: 0);

var pager = await Pager.CreateAsync(device, wal, walIndex);
await pager.InitializeNewDatabaseAsync(); // writes file header (page 0)
```

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

### Bootstrap Storage Collections

Source: https://csharpdb.com/docs/tutorials/storage.html

Opens a CSharpDB database and gets collections for 'FolderEntry' and 'FileEntry' types.

```csharp
await using var db = await Database.OpenAsync("storage.cdb");
var folders = await db.GetCollectionAsync<FolderEntry>("folders");
var files   = await db.GetCollectionAsync<FileEntry>("files");
```

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

### Create and Initialize FileStorageDevice

Source: https://csharpdb.com/docs/storage-engine.html

Demonstrates creating a new database file using FileStorageDevice and checking its initial length. The `createNew: true` option ensures that the operation fails if the file already exists.

```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
Console.WriteLine($"File created. Length: {device.Length}"); // 0
```

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

### Get Table Row Count

Source: https://csharpdb.com/docs/rest-api.html

Returns the total number of rows present in a given table.

```json
{ "count": 42 }
```

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

### Run CSV Bulk Import Sample

Source: https://csharpdb.com/docs/tutorials/csv-bulk-import.html

Execute the sample project to perform a CSV bulk import. Optional flags can override default paths and batch size.

```bash
dotnet run --project samples/csv-bulk-import/CsvBulkImportSample.csproj
```

```bash
dotnet run --project samples/csv-bulk-import/CsvBulkImportSample.csproj -- \
  --csv-path samples/csv-bulk-import/events.csv \
  --database-path artifacts/samples/csv-import-demo.db \
  --batch-size 1000
```

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

### Run CSV Bulk Import Sample

Source: https://csharpdb.com/docs/samples.html

Execute the CSV bulk import sample project using the dotnet CLI. This sample demonstrates best practices for ingesting relational data via the public engine API, including transaction batching and post-load index creation.

```bash
dotnet run --project samples/csv-bulk-import/CsvBulkImportSample.csproj
```

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

### CRUD Operations

Source: https://csharpdb.com/docs/collections.html

Provides examples for inserting, retrieving, updating, deleting, and scanning documents within a collection.

```APIDOC
## CRUD Operations

```csharp
// Put (insert or update)
await customers.PutAsync("customer:alice", new Customer(
    "Alice", "alice@example.com",
    new("Portland", "OR", "97201"),
    ["premium", "early-adopter"]
));

// Get by key
var alice = await customers.GetAsync("customer:alice");

// Delete
await customers.DeleteAsync("customer:alice");

// Scan all documents
await foreach (var entry in customers.ScanAsync())
    Console.WriteLine($"{entry.Key}: {entry.Value.Name}");
```
```

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

### Open or Create Database

Source: https://csharpdb.com/docs/getting-started-reference.html

Opens an existing database file or creates a new one if it does not exist. Uses `await using` to ensure proper closure.

```csharp
await using var db = await Database.OpenAsync("myapp.db");
                
```

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

### Get Table Schema

Source: https://csharpdb.com/docs/rest-api.html

Retrieves the full schema definition for a specified table, including column details.

```APIDOC
## GET /api/tables/{name}/schema

### Description
Get the full schema for a table.

### Method
GET

### Endpoint
/api/tables/{name}/schema

### Parameters
#### Path Parameters
- **name** (string) - Required - The name of the table.

### Response
#### Success Response (200)
- **tableName** (string) - The name of the table.
- **columns** (array) - An array of column definitions.
  - **name** (string) - The name of the column.
  - **type** (string) - The data type of the column.
  - **nullable** (boolean) - Indicates if the column can contain null values.
  - **isPrimaryKey** (boolean) - Indicates if the column is part of the primary key.
  - **isIdentity** (boolean) - Indicates if the column is an identity column.

### Response Example
```json
{
  "tableName": "users",
  "columns": [
    { "name": "id", "type": "Integer", "nullable": false, "isPrimaryKey": true, "isIdentity": true },
    { "name": "name", "type": "Text", "nullable": false, "isPrimaryKey": false, "isIdentity": false },
    { "name": "age", "type": "Integer", "nullable": true, "isPrimaryKey": false, "isIdentity": false }
  ]
}
```
```

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

### Use Custom Catalog Store

Source: https://csharpdb.com/docs/tutorials/storage-extensibility.html

Replace the default catalog store with a custom implementation.

```csharp
builder.UseCatalogStore(new MyCatalogStore());
```

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

### Create and Access DbValue Instances

Source: https://csharpdb.com/docs/core-concepts.html

Demonstrates creating DbValue instances from different fundamental types and accessing their values type-safely. Also shows how to compare DbValue instances.

```csharp
var id    = DbValue.FromInteger(42);
var name  = DbValue.FromText("Alice");
var score = DbValue.FromReal(98.5);
var empty = DbValue.Null;

// Type-safe access
long idVal     = id.AsInteger;    // 42
string nameVal = name.AsText;     // "Alice"
bool isNull    = empty.IsNull;    // true

// Comparison
int cmp = DbValue.Compare(id, DbValue.FromInteger(43)); // -1
```

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

### Get Table Schema

Source: https://csharpdb.com/docs/rest-api.html

Fetches the complete schema definition for a specified table, including column details.

```json
{
  "tableName": "users",
  "columns": [
    { "name": "id", "type": "Integer", "nullable": false, "isPrimaryKey": true, "isIdentity": true },
    { "name": "name", "type": "Text", "nullable": false, "isPrimaryKey": false, "isIdentity": false },
    { "name": "age", "type": "Integer", "nullable": true, "isPrimaryKey": false, "isIdentity": false }
  ]
}
```

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

### Get Single Row by Primary Key

Source: https://csharpdb.com/docs/rest-api.html

Retrieves a single row from a table using its primary key value.

```APIDOC
## GET /api/tables/{name}/rows/{pkValue}

### Description
Get a single row by primary key.

### Method
GET

### Endpoint
/api/tables/{name}/rows/{pkValue}

### Parameters
#### Path Parameters
- **name** (string) - Required - The name of the table.
- **pkValue** (any) - Required - The value of the primary key for the row.

#### Query Parameters
- **pkColumn** (string) - Optional - The name of the primary key column (default: "id").

### Response
#### Success Response (200)
- **(object)** - An object representing the row with column names as keys.

### Response Example
```json
{ "id": 1, "name": "Alice", "age": 30 }
```
```

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

### Get Database Info

Source: https://csharpdb.com/docs/rest-api.html

Retrieves a summary of the database, including counts of tables, indexes, views, triggers, and procedures.

```APIDOC
## GET /api/info

### Description
Returns a summary of the database.

### Method
GET

### Endpoint
/api/info

### Response
#### Success Response (200)
- **dataSource** (string) - The data source name.
- **tableCount** (integer) - The number of tables.
- **indexCount** (integer) - The number of indexes.
- **viewCount** (integer) - The number of views.
- **triggerCount** (integer) - The number of triggers.
- **procedureCount** (integer) - The number of procedures.

### Response Example
```json
{
  "dataSource": "csharpdb.db",
  "tableCount": 3,
  "indexCount": 2,
  "viewCount": 1,
  "triggerCount": 1,
  "procedureCount": 2
}
```
```

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

### Create and Query Tables in CSharpDB

Source: https://csharpdb.com/docs/storage-engine-reference.html

Demonstrates how to create a new table with specified columns and query table metadata, including its root page and B-tree structure. Also shows how to list all available table names.

```csharp
var schema = new TableSchema
                {
                    TableName = "users",
                    Columns = new[]
                    {
                        new ColumnDefinition { Name = "id", Type = DbType.Integer, IsPrimaryKey = true },
                        new ColumnDefinition { Name = "name", Type = DbType.Text },
                        new ColumnDefinition { Name = "age", Type = DbType.Integer },
                    }
                };
                await catalog.CreateTableAsync(schema);
                
                // Query table metadata
                TableSchema? users = catalog.GetTable("users");
                uint rootPage = catalog.GetTableRootPage("users");
                BTree tableTree = catalog.GetTableTree("users");
                
                // List all tables
                IReadOnlyCollection<string> tableNames = catalog.GetTableNames();
                
                // For snapshot readers
                BTree snapshotTree = catalog.GetTableTree("users", snapshotPager);
```

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

### Get Database Info

Source: https://csharpdb.com/docs/rest-api.html

Retrieve a summary of the database, including counts of tables, indexes, views, triggers, and procedures.

```http
GET /api/info
```

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

### Run Sample SQL File with CSharpDB CLI

Source: https://csharpdb.com/docs/faq.html

Use the CSharpDB CLI to execute SQL scripts against a database file. Ensure the CLI project is built and accessible.

```bash
dotnet run --project src/CSharpDB.Cli -- mydata.db
```

```csharp
csdb> .read samples/ecommerce-store/schema.sql
```

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

### Import and Run ETL Pipeline

Source: https://csharpdb.com/docs/cli.html

Demonstrates importing an ETL pipeline package and then executing it. Assumes the pipeline definition is in `pipeline.json`.

```bash
csharpdb -- etl import mydata.db pipeline.json
```

```bash
csharpdb -- etl run mydata.db pipeline.json
```

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

### Run Storage Inspector (JSON Output)

Source: https://csharpdb.com/docs/storage-inspector.html

Use this command to get machine-readable JSON output for the database state.

```bash
csharpdb inspect mydata.db --json
```

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

### Open Multiple Database Volumes

Source: https://csharpdb.com/docs/storage-engine-reference.html

Demonstrates how to manage multiple database files, each mapped to a specific folder, using a dictionary to cache `Database` instances. This approach is useful for large datasets requiring per-folder backup, different checkpoint intervals, or parallel writes to disjoint folders.

```csharp
                var volumes = new Dictionary<string, Database>(StringComparer.Ordinal);
                
                async ValueTask<Database> GetVolumeAsync(string folderName)
                {
                    if (!volumes.TryGetValue(folderName, out var db))
                    {
                        db = await Database.OpenAsync($"{folderName}.cdb");
                        volumes[folderName] = db;
                    }
                    return db;
                }
                
                // Write to the "documents" volume
                var docsDb   = await GetVolumeAsync("documents");
                var docsFiles = await docsDb.GetCollectionAsync<FileEntry>("files");
                await docsFiles.PutAsync("readme.txt", new FileEntry(
                    Name:        "readme.txt",
                    FolderPath:  "/",
                    Content:     "Welcome to the documents volume.",
                    ContentType: "text/plain",
                    SizeBytes:   32,
                    CreatedAt:   DateTime.UtcNow,
                    UpdatedAt:   DateTime.UtcNow));
                
                // Write to the "images" volume
                var imagesDb    = await GetVolumeAsync("images");
                var imagesFiles = await imagesDb.GetCollectionAsync<FileEntry>("files");
                await imagesFiles.PutAsync("logo.svg", new FileEntry(
                    Name:        "logo.svg",
                    FolderPath:  "/",
                    Content:     "<svg>...</svg>",
                    ContentType: "image/svg+xml",
                    SizeBytes:   14,
                    CreatedAt:   DateTime.UtcNow,
                    UpdatedAt:   DateTime.UtcNow));
                
                // Dispose all volumes on shutdown
                foreach (var (_, volume) in volumes)
                    await volume.DisposeAsync();
                

```

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

### Range Queries

Source: https://csharpdb.com/docs/collections.html

Illustrates how to perform range queries on indexed properties for ordered data, using a `Total` property as an example.

```APIDOC
## Range Queries

Query indexed properties with range bounds for ordered data.

```csharp
public record Order(string Customer, double Total, string Date);

var orders = await db.GetCollectionAsync<Order>("orders");
await orders.EnsureIndexAsync(o => o.Total);

// Find orders with total between 100 and 500
await foreach (var match in orders.FindByPathRangeAsync(
    o => o.Total, 100.0, 500.0))
{
    Console.WriteLine(match.Key);
}
```
```

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

### Create Products Table

Source: https://csharpdb.com/docs/getting-started-reference.html

Creates a 'products' table with specified columns and data types. Use `IF NOT EXISTS` to prevent errors if the table already exists.

```csharp
await db.ExecuteAsync(@"
                    CREATE TABLE products (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        price REAL,
                        category TEXT
                    )
                ");
                
```

```csharp
await db.ExecuteAsync("CREATE TABLE IF NOT EXISTS products (id INTEGER, name TEXT)");
                
```

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

### Create a New File

Source: https://csharpdb.com/docs/storage-engine-reference.html

Demonstrates how to create a new, empty database file using FileStorageDevice.

```APIDOC
## Create a New File

### Description
Creates a new database file. This operation will fail if the specified file already exists.

### Code Example
```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
Console.WriteLine($"File created. Length: {device.Length}"); // Output: File created. Length: 0
```
```

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

### Tokenization Examples

Source: https://csharpdb.com/docs/full-text-search.html

Illustrates how text is tokenized based on word boundaries (Unicode letters, digits, apostrophes) after normalization and lowercasing.

```text
// "Quick brown fox" → ["quick", "brown", "fox"]
// "Runner's high"   → ["runner's", "high"]
// "CSharpDB v2.4"   → ["csharpdb", "v2", "4"]
```

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

### List Views API Response

Source: https://csharpdb.com/docs/rest-api.html

The response to a GET /api/views request is a JSON array containing the names of all available views.

```json
["order_summary", "product_catalog"]
```

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

### Create and Manage Schema Catalog

Source: https://csharpdb.com/docs/storage-engine.html

Demonstrates creating a schema catalog, defining and creating tables, querying table metadata, and creating/querying indexes, views, and triggers.

```csharp
var catalog = await SchemaCatalog.CreateAsync(pager);

// Create a table
var schema = new TableSchema
{
    TableName = "users",
    Columns = new []
    {
        new ColumnDefinition { Name = "id", Type = DbType.Integer, IsPrimaryKey = true },
        new ColumnDefinition { Name = "name", Type = DbType.Text },
        new ColumnDefinition { Name = "age", Type = DbType.Integer },
    }
};
await catalog.CreateTableAsync(schema);

// Query table metadata
TableSchema? users = catalog.GetTable("users");
uint rootPage = catalog.GetTableRootPage("users");
BTree tableTree = catalog.GetTableTree("users");

// Create and query indexes
var indexSchema = new IndexSchema
{
    IndexName = "idx_users_name",
    TableName = "users",
    Columns = new [] { "name" },
    IsUnique = false,
};
await catalog.CreateIndexAsync(indexSchema);
IIndexStore indexStore = catalog.GetIndexStore("idx_users_name");

// Views and triggers
await catalog.CreateViewAsync("active_users", "SELECT * FROM users WHERE age > 18");
string? viewSql = catalog.GetViewSql("active_users");
```

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

### Get ETL Pipeline Rejected Rows

Source: https://csharpdb.com/docs/cli.html

Retrieves rejected rows from a specific ETL pipeline run. Supports JSON output.

```bash
etl rejects <dbfile> <runId> [--json]
```

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

### Create Database Backup in REPL

Source: https://csharpdb.com/docs/cli.html

Creates a backup of the current database file in the C# DB REPL. Includes an option to write a manifest file.

```sql
.backup mydata.backup.db --with-manifest
```

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

### Folder and File Storage Operations

Source: https://csharpdb.com/docs/storage-engine.html

Illustrates how to use the `Database` and `Collection<T>` API to create and manage a folder and file storage system.

```csharp
await using var db = await Database.OpenAsync("storage.cdb");
var folders = await db.GetCollectionAsync<FolderEntry>("folders");
var files   = await db.GetCollectionAsync<FileEntry>("files");

// Create a folder
await folders.PutAsync("/documents", new FolderEntry(
    Name: "documents", Path: "/documents",
    CreatedAt: DateTime.UtcNow));

// Create a file
await files.PutAsync("/documents/readme.txt", new FileEntry(
    Name: "readme.txt", FolderPath: "/documents",
    Content: "Hello world", ContentType: "text/plain",
    SizeBytes: 11, CreatedAt: DateTime.UtcNow, UpdatedAt: DateTime.UtcNow));

// Read a file
FileEntry? file = await files.GetAsync("/documents/readme.txt");

// List files in a folder
await foreach (var kvp in files.FindAsync(f => f.FolderPath == "/documents"))
    Console.WriteLine(kvp.Value.Name);
```

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

### Daemon-Backed ADO.NET Connection

Source: https://csharpdb.com/docs/getting-started-reference.html

Connect to a long-lived CSharpDB.Daemon process using a specific connection string. This example shows how to query the number of products.

```csharp
using CSharpDB.Data;
                
                await using var conn = new CSharpDbConnection(
                    "Transport=Grpc;Endpoint=http://localhost:5820");
                await conn.OpenAsync();
                
                using var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT COUNT(*) FROM products";
                var count = (long)(await cmd.ExecuteScalarAsync() ?? 0L);
```

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

### Configure Checkpoint Policy

Source: https://csharpdb.com/docs/storage-engine-reference.html

Sets up a checkpoint policy that triggers after 500 frames or 5 minutes, whichever comes first. The checkpoint execution mode is set to background, with a maximum of 64 pages per step.

```csharp
var options = new PagerOptions
                {
                    CheckpointPolicy = new AnyCheckpointPolicy(
                        new FrameCountCheckpointPolicy(threshold: 500),
                        new TimeIntervalCheckpointPolicy(TimeSpan.FromMinutes(5))
                    ),
                    AutoCheckpointExecutionMode = AutoCheckpointExecutionMode.Background,
                    AutoCheckpointMaxPagesPerStep = 64
                };
                
                var pager = await Pager.CreateAsync(device, wal, walIndex, options);
                // Auto-checkpoint triggers after 500 frames OR 5 minutes, whichever comes first.
                // In background mode, the checkpoint runs after commit in smaller slices.
```

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

### Get ETL Pipeline Run Package

Source: https://csharpdb.com/docs/cli.html

Retrieves the package definition used for a specific ETL pipeline run. Supports JSON output.

```bash
etl run-package <dbfile> <runId> [--json]
```

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

### Configure Database with Storage Engine Options

Source: https://csharpdb.com/docs/tutorials/storage-extensibility.html

Open a database file with custom storage engine options, configuring pager and caching B-tree indexes.

```csharp
using CSharpDB.Engine;
using CSharpDB.Storage.Checkpointing;
using CSharpDB.Storage.Paging;

var options = new DatabaseOptions()
    .ConfigureStorageEngine(builder =>
    {
        builder.UsePagerOptions(new PagerOptions
        {
            MaxCachedPages = 1000,
            CheckpointPolicy = new FrameCountCheckpointPolicy(500),
        });

        builder.UseCachingBTreeIndexes(findCacheCapacity: 2048);
    });

await using var db = await Database.OpenAsync("mydb.cdb", options);
```

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

### Add CSharpDB EF Core Package

Source: https://csharpdb.com/docs/entity-framework-core.html

Install the necessary NuGet packages for CSharpDB Entity Framework Core integration and design-time commands.

```bash
dotnet add package CSharpDB.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
```

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

### Register CSharpDB Hosted Service and Singleton

Source: https://csharpdb.com/docs/samples.html

This code sets up a hosted service to manage the database lifecycle and registers the database instance as a singleton. It loads data from a snapshot if available or creates a new in-memory database, defines the schema, and creates indexes. The database is then exposed for dependency injection.

```csharp
using CSharpDB.Engine;

public sealed class CacheDbHostedService : IHostedLifecycleService, IAsyncDisposable
{
    private readonly ILogger<CacheDbHostedService> _logger;
    private readonly string? _snapshotPath;
    private Database? _db;

    public Database Db => _db
        ?? throw new InvalidOperationException("Cache not initialized");

    public CacheDbHostedService(
        ILogger<CacheDbHostedService> logger,
        IConfiguration config)
    {
        _logger = logger;
        _snapshotPath = config["CacheDb:SnapshotPath"];
    }

    public async Task StartingAsync(CancellationToken ct)
    {
        // Load from snapshot if available, otherwise create fresh
        if (_snapshotPath is not null && File.Exists(_snapshotPath))
        {
            _logger.LogInformation("Loading cache from {Path}", _snapshotPath);
            _db = await Database.LoadIntoMemoryAsync(_snapshotPath, ct);
        }
        else
        {
            _db = await Database.OpenInMemoryAsync(ct);
        }

        // Create cache tables
        await _db.ExecuteAsync(@" 
            CREATE TABLE IF NOT EXISTS ProductCache (
                Id       INTEGER PRIMARY KEY,
                Sku      TEXT NOT NULL,
                Name     TEXT NOT NULL,
                Price    REAL,
                Category TEXT,
                CachedAt TEXT NOT NULL
            )");

        await _db.ExecuteAsync(
            "CREATE INDEX IF NOT EXISTS idx_product_sku ON ProductCache (Sku)");
        await _db.ExecuteAsync(
            "CREATE INDEX IF NOT EXISTS idx_product_cat ON ProductCache (Category)");

        _logger.LogInformation("Cache database ready");
    }

    public Task StartAsync(CancellationToken ct) => Task.CompletedTask;
    public Task StartedAsync(CancellationToken ct) => Task.CompletedTask;
    public Task StoppingAsync(CancellationToken ct) => Task.CompletedTask;
    public Task StopAsync(CancellationToken ct) => Task.CompletedTask;

    public async Task StoppedAsync(CancellationToken ct)
    {
        // Save snapshot on graceful shutdown
        if (_db is not null && _snapshotPath is not null)
        {
            _logger.LogInformation("Saving cache snapshot to {Path}", _snapshotPath);
            await _db.SaveToFileAsync(_snapshotPath, ct);
        }
    }

    public async ValueTask DisposeAsync()
    {
        if (_db is not null)
            await _db.DisposeAsync();
    }
}

```

```csharp
var builder = WebApplication.CreateBuilder(args);

// Register the cache database as a hosted service + singleton
builder.Services.AddSingleton<CacheDbHostedService>();
builder.Services.AddHostedService(
    sp => sp.GetRequiredService<CacheDbHostedService>());

// Expose the Database instance for injection
builder.Services.AddSingleton(
    sp => sp.GetRequiredService<CacheDbHostedService>().Db);

var app = builder.Build();


```

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

### Create and Query a View

Source: https://csharpdb.com/docs/getting-started-reference.html

Defines a reusable named query (view) that can be queried like a table. This example creates a view for expensive products and then queries it.

```csharp
await db.ExecuteAsync (@"
                    CREATE VIEW expensive_products AS
                    SELECT name, price FROM products WHERE price > 20.0");
                
                // Query the view like a table
                await using var result = await db.ExecuteAsync("SELECT * FROM expensive_products");
                

```

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

### InitializeNewDatabaseAsync

Source: https://csharpdb.com/docs/storage-engine-reference.html

Initializes a new database by writing the file header (page 0). This should be called after creating a Pager for a new database.

```APIDOC
## InitializeNewDatabaseAsync

### Description
Initializes a new database by writing the file header to page 0. This method should be called after `Pager.CreateAsync` when creating a new database file.

### Method
`await pager.InitializeNewDatabaseAsync();`
```

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

### Injecting IStorageDevice for Testability

Source: https://csharpdb.com/docs/storage-engine-reference.html

Demonstrates how to use IStorageDevice for production and provides an in-memory implementation for unit testing.

```csharp
// Production wiring
                IStorageDevice device = new FileStorageDevice("mydb.cdb");
                var pager = await Pager.CreateAsync(device, wal, walIndex);
                
                // In a unit test -- swap in your own IStorageDevice implementation
                public sealed class MemoryStorageDevice : IStorageDevice
                {
                    private byte[] _data = [];
                    public long Length => _data.Length;
                
                    public ValueTask<int> ReadAsync(long offset, Memory<byte> buffer, CancellationToken ct = default)
                    {
                        int available = (int)Math.Max(0, _data.Length - offset);
                        int toCopy = Math.Min(buffer.Length, available);
                        _data.AsMemory((int)offset, toCopy).CopyTo(buffer);
                        buffer[toCopy..].Span.Clear();
                        return ValueTask.FromResult(toCopy);
                    }
                
                    public ValueTask WriteAsync(long offset, ReadOnlyMemory<byte> buffer, CancellationToken ct = default)
                    {
                        long needed = offset + buffer.Length;
                        if (needed > _data.Length)
                            Array.Resize(ref _data, (int)needed);
                        buffer.CopyTo(_data.AsMemory((int)offset));
                        return ValueTask.CompletedTask;
                    }
                
                    public ValueTask FlushAsync(CancellationToken ct = default) => ValueTask.CompletedTask;
                
                    public ValueTask SetLengthAsync(long length, CancellationToken ct = default)
                    {
                        Array.Resize(ref _data, (int)length);
                        return ValueTask.CompletedTask;
                    }
                
                    public ValueTask DisposeAsync() => ValueTask.CompletedTask;
                    public void Dispose() { }
                }
                
```

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

### Get View Definition API Response

Source: https://csharpdb.com/docs/rest-api.html

This JSON object represents the definition of a specific view, including its name and the SQL query used to create it.

```json
{
  "viewName": "order_summary",
  "selectSql": "SELECT o.id, c.name, o.total FROM orders o INNER JOIN customers c ON o.customer_id = c.id"
}
```

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

### Get Single Row by Primary Key

Source: https://csharpdb.com/docs/rest-api.html

Fetches a specific row from a table using its primary key value. The primary key column can be specified.

```json
{ "id": 1, "name": "Alice", "age": 30 }
```

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

### Pre-allocate File Size with SetLengthAsync

Source: https://csharpdb.com/docs/storage-engine.html

Demonstrates pre-allocating a specific size for the database file using `SetLengthAsync`. This can be useful for reserving space or setting up initial file structures.

```csharp
await using var device = new FileStorageDevice("mydb.cdb", createNew: true);
const int PageSize = 4096;
await device.SetLengthAsync(PageSize * 8);
Console.WriteLine($"Pre-allocated: {device.Length} bytes"); // 32768
```

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

### Get ETL Pipeline Run Status

Source: https://csharpdb.com/docs/cli.html

Retrieves the status of a specific ETL pipeline run using its run ID. Supports JSON output.

```bash
etl status <dbfile> <runId> [--json]
```

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

### Show Table Schema in REPL

Source: https://csharpdb.com/docs/cli.html

Displays the `CREATE TABLE` statement for a specified table in the C# DB REPL.

```sql
.schema users
```