SQL Engine

Your Code → WitDbCommand → WitSqlEngine → Storage

Your Code → WitSqlEngine → Storage

using OutWit.Database.Core.Builder;
using OutWit.Database.Engine;

// Create database with desired configuration
var database = new WitDatabaseBuilder()
    .WithFilePath("myapp.witdb")
    .WithBTree()
    .WithTransactions()
    .Build();

// Create the SQL engine
var engine = new WitSqlEngine(database, ownsStore: true);

var database = WitDatabase.CreateInMemory();
var engine = new WitSqlEngine(database, ownsStore: true);

// Execute returns a WitSqlResult for any SQL
using var result = engine.Execute("SELECT * FROM Users");

// Iterate through rows
while (result.Read())
{
    var id = result.CurrentRow["Id"].AsInt64();
    var name = result.CurrentRow["Name"].AsString();
    Console.WriteLine($"{id}: {name}");
}

// Get all rows as a list
List<WitSqlRow> users = engine.Query("SELECT * FROM Users WHERE IsActive = 1");

// Get first row or null
WitSqlRow? user = engine.QueryFirstOrDefault("SELECT * FROM Users WHERE Id = 1");

// Get a single scalar value
WitSqlValue count = engine.ExecuteScalar("SELECT COUNT(*) FROM Users");
Console.WriteLine($"User count: {count.AsInt64()}");

// Execute non-query (INSERT, UPDATE, DELETE) and get rows affected
int affected = engine.ExecuteNonQuery("UPDATE Users SET LastLogin = NOW() WHERE Id = 1");

var parameters = new Dictionary<string, object?>
{
    ["@name"] = "Alice",
    ["@email"] = "alice@example.com"
};

var user = engine.QueryFirstOrDefault(
    "SELECT * FROM Users WHERE Name = @name AND Email = @email",
    parameters);

// All equivalent
engine.Execute("SELECT * FROM Users WHERE Id = @id", new Dictionary<string, object?> { ["id"] = 1 });
engine.Execute("SELECT * FROM Users WHERE Id = :id", new Dictionary<string, object?> { ["id"] = 1 });
engine.Execute("SELECT * FROM Users WHERE Id = $id", new Dictionary<string, object?> { ["id"] = 1 });

// With timeout
var timeout = TimeSpan.FromSeconds(30);
using var result = engine.Execute(
    "SELECT * FROM LargeTable WHERE ComplexCondition(Column)",
    parameters: null,
    timeout: timeout);

// With CancellationToken
var cts = new CancellationTokenSource(TimeSpan.FromSeconds(10));
using var result = engine.Execute(
    "SELECT * FROM LargeTable",
    parameters: null,
    timeout: null,
    cancellationToken: cts.Token);

// Practical example: cancellable report generation
public async Task<List<ReportRow>> GenerateReportAsync(CancellationToken cancellationToken)
{
    var results = new List<ReportRow>();
    
    using var result = engine.Execute(
        "SELECT * FROM Orders WHERE Date > @start",
        new Dictionary<string, object?> { ["start"] = DateTime.Today.AddMonths(-12) },
        timeout: TimeSpan.FromMinutes(5),
        cancellationToken: cancellationToken);
    
    while (result.Read())
    {
        cancellationToken.ThrowIfCancellationRequested();
        results.Add(MapToReportRow(result.CurrentRow));
    }
    
    return results;
}

engine.Execute(@"
    CREATE TABLE Users (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Name VARCHAR(100) NOT NULL,
        Email VARCHAR(255) UNIQUE,
        CreatedAt DATETIME DEFAULT NOW(),
        IsActive BOOLEAN DEFAULT true
    )
");

engine.Execute(@"
    CREATE INDEX IX_Users_Email ON Users(Email)
");

using var result = engine.Execute("UPDATE Users SET IsActive = 0 WHERE LastLogin < @date",
    new Dictionary<string, object?> { ["date"] = DateTime.UtcNow.AddYears(-1) });

// Number of rows affected by INSERT/UPDATE/DELETE
Console.WriteLine($"Rows affected: {result.RowsAffected}");

// Column information
Console.WriteLine($"Columns: {result.Columns.Count}");
foreach (var col in result.Columns)
{
    Console.WriteLine($"  {col.Name}: {col.Type}");
}

// For SELECT with RETURNING or INSERT...RETURNING
Console.WriteLine($"Has rows: {result.HasRows}");

using var result = engine.Execute("SELECT Id, Name, Email FROM Users");

while (result.Read())
{
    // Access by column name
    var name = result.CurrentRow["Name"].AsString();
    
    // Access by index
    var id = result.CurrentRow[0].AsInt64();
    
    // The CurrentRow property returns a WitSqlRow
    WitSqlRow row = result.CurrentRow;
}

using var result = engine.Execute("SELECT * FROM Users");
List<WitSqlRow> allRows = result.ReadAll();

WitSqlRow row = result.CurrentRow;

// Column access by name or index
WitSqlValue idValue = row["Id"];
WitSqlValue nameValue = row[1];

// Type conversion methods
long id = row["Id"].AsInt64();
string name = row["Name"].AsString();
decimal? price = row["Price"].AsDecimalOrNull();
DateTime created = row["CreatedAt"].AsDateTime();
bool isActive = row["IsActive"].AsBoolean();

// Check for NULL
if (row["MiddleName"].IsNull)
{
    Console.WriteLine("No middle name");
}

// Get column names
IReadOnlyList<string> columns = row.ColumnNames;

using var stmt = engine.Prepare("SELECT * FROM Users WHERE Department = @dept");

// Set parameters
stmt.SetParameter("dept", "Engineering");

// Execute
using var result = stmt.Execute();
while (result.Read())
{
    Console.WriteLine(result.CurrentRow["Name"].AsString());
}

using var stmt = engine.Prepare("SELECT * FROM Products WHERE Category = @cat AND Price < @maxPrice");

// First execution
stmt.SetParameter("cat", "Electronics");
stmt.SetParameter("maxPrice", 500.00m);
using var electronics = stmt.Execute();
// Process electronics...

// Clear and reuse
stmt.ClearParameters();
stmt.SetParameter("cat", "Clothing");
stmt.SetParameter("maxPrice", 100.00m);
using var clothing = stmt.Execute();
// Process clothing...

using var stmt = engine.Prepare("INSERT INTO Users (Name, Email, Age) VALUES (@name, @email, @age)");

using var result = stmt
    .SetParameter("name", "Alice")
    .SetParameter("email", "alice@example.com")
    .SetParameter("age", 30)
    .Execute();

stmt.SetParameters(new Dictionary<string, object?>
{
    ["name"] = "Bob",
    ["email"] = "bob@example.com",
    ["age"] = 25
});
using var result = stmt.Execute();

using var stmt = engine.Prepare("SELECT * FROM LargeTable WHERE Category = @cat");
stmt.SetParameter("cat", "Reports");

// With CancellationToken
var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
using var result = stmt.Execute(cts.Token);

// Process with cancellation checks
while (result.Read())
{
    cts.Token.ThrowIfCancellationRequested();
    ProcessRow(result.CurrentRow);
}

using var stmt = engine.Prepare("INSERT INTO Users (Name, Email) VALUES (@name, @email)");

var users = new[]
{
    new Dictionary<string, object?> { ["name"] = "Alice", ["email"] = "alice@test.com" },
    new Dictionary<string, object?> { ["name"] = "Bob", ["email"] = "bob@test.com" },
    new Dictionary<string, object?> { ["name"] = "Charlie", ["email"] = "charlie@test.com" },
};

int rowsAffected = stmt.ExecuteBatch(users);
Console.WriteLine($"Inserted {rowsAffected} users");

var cts = new CancellationTokenSource();

// Cancel after 1 minute
cts.CancelAfter(TimeSpan.FromMinutes(1));

try
{
    int affected = stmt.ExecuteBatch(millionsOfRows, cts.Token);
    Console.WriteLine($"Inserted {affected} rows");
}
catch (OperationCanceledException)
{
    Console.WriteLine("Batch operation was cancelled");
}

public record UserDto(string Name, string Email, int Age);

using var stmt = engine.Prepare("INSERT INTO Users (Name, Email, Age) VALUES (@Name, @Email, @Age)");

var users = new[]
{
    new UserDto("Alice", "alice@test.com", 30),
    new UserDto("Bob", "bob@test.com", 25),
    new UserDto("Charlie", "charlie@test.com", 35),
};

int rowsAffected = stmt.ExecuteBatch(users);  // Properties mapped to parameters

var columns = new[] { "Name", "Email", "Age" };
var rows = new List<object?[]>
{
    new object?[] { "Alice", "alice@test.com", 25 },
    new object?[] { "Bob", "bob@test.com", 30 },
    new object?[] { "Charlie", "charlie@test.com", 35 },
};

int inserted = engine.BulkInsert("Users", columns, rows);
Console.WriteLine($"Inserted {inserted} rows");

var rows = new[]
{
    new Dictionary<string, object?> { ["Name"] = "Alice", ["Email"] = "alice@test.com" },
    new Dictionary<string, object?> { ["Name"] = "Bob", ["Email"] = "bob@test.com" },
};

int inserted = engine.BulkInsert("Users", rows);

var cts = new CancellationTokenSource(TimeSpan.FromMinutes(10));

try
{
    int inserted = engine.BulkInsert("Events", columns, millionsOfRows, cts.Token);
    Console.WriteLine($"Inserted {inserted} rows");
}
catch (OperationCanceledException)
{
    Console.WriteLine("Bulk insert was cancelled");
    // Partial data may have been inserted if not in a transaction
}

engine.Execute("BEGIN TRANSACTION");
try
{
    engine.BulkInsert("Users", columns, rows);
    engine.Execute("COMMIT");
}
catch
{
    engine.Execute("ROLLBACK");
    throw;
}

engine.Execute("BEGIN TRANSACTION");

try
{
    engine.Execute("UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1");
    engine.Execute("UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2");
    
    engine.Execute("COMMIT");
}
catch
{
    engine.Execute("ROLLBACK");
    throw;
}

engine.Execute("BEGIN TRANSACTION");
engine.Execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

// Your queries run with serializable isolation
// ...

engine.Execute("COMMIT");

engine.Execute("BEGIN TRANSACTION");

engine.Execute("INSERT INTO Orders (CustomerId, Total) VALUES (1, 100)");
engine.Execute("SAVEPOINT order_created");

try
{
    engine.Execute("INSERT INTO OrderItems (OrderId, ProductId) VALUES (LAST_INSERT_ID(), 999)");
    // This might fail if product 999 doesn't exist
}
catch
{
    engine.Execute("ROLLBACK TO order_created");
    // Order still exists, but without items
}

engine.Execute("COMMIT");

using var transaction = database.BeginTransaction();

try
{
    // Operations within transaction context...
    engine.Execute("INSERT INTO Logs (Message) VALUES ('Test')");
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

using var result = engine.Execute("EXPLAIN SELECT * FROM Users WHERE Email = 'alice@test.com'");

while (result.Read())
{
    Console.WriteLine(result.CurrentRow["Plan"].AsString());
}

Scan: Users
  Filter: Email = 'alice@test.com'
  Estimated rows: 1

// Without index
engine.Execute("EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123");
// Output: Scan: Orders, Filter: CustomerId = 123
//         Estimated rows: 10000

// Create index
engine.Execute("CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId)");

// With index
engine.Execute("EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123");
// Output: IndexSeek: IX_Orders_CustomerId
//         Estimated rows: 15

var sql = @"
    EXPLAIN 
    SELECT o.Id, c.Name, o.Total
    FROM Orders o
    JOIN Customers c ON o.CustomerId = c.Id
    WHERE o.OrderDate > '2024-01-01'
    ORDER BY o.Total DESC
    LIMIT 10";

using var result = engine.Execute(sql);
while (result.Read())
{
    Console.WriteLine(result.CurrentRow["Plan"].AsString());
}

Limit: 10
  Sort: Total DESC
    Join: INNER
      Left: IndexRangeScan: IX_Orders_OrderDate (> '2024-01-01')
      Right: IndexSeek: PK_Customers

// Helper method to analyze query performance
public void AnalyzeQuery(string sql)
{
    using var result = engine.Execute($"EXPLAIN {sql}");
    
    Console.WriteLine("Execution Plan:");
    while (result.Read())
    {
        var plan = result.CurrentRow["Plan"].AsString();
        
        // Highlight potential issues
        if (plan.Contains("Scan:") && !plan.Contains("Estimated rows: 0"))
        {
            Console.ForegroundColor = ConsoleColor.Yellow;
            Console.WriteLine($"  ⚠ {plan}");
            Console.ResetColor();
        }
        else
        {
            Console.WriteLine($"  {plan}");
        }
    }
}

// Get table metadata
DefinitionTable? table = engine.GetTable("Users");

if (table != null)
{
    Console.WriteLine($"Table: {table.Name}");
    Console.WriteLine($"Columns: {table.Columns.Count}");
    
    foreach (var column in table.Columns)
    {
        Console.WriteLine($"  {column.Name}: {column.Type}");
        Console.WriteLine($"    Nullable: {column.IsNullable}");
        Console.WriteLine($"    Primary Key: {column.IsPrimaryKey}");
        Console.WriteLine($"    Default: {column.DefaultValue ?? "none"}");
    }
}

// O(1) operation - uses cached metadata
long rowCount = engine.GetTableRowCount("Users");
Console.WriteLine($"Users table has {rowCount} rows");

// Compare with SQL COUNT (scans table)
// This is slower for large tables
var countResult = engine.ExecuteScalar("SELECT COUNT(*) FROM Users");

// Get a specific index
DefinitionIndex? index = engine.GetIndex("IX_Users_Email");

if (index != null)
{
    Console.WriteLine($"Index: {index.Name}");
    Console.WriteLine($"Table: {index.TableName}");
    Console.WriteLine($"Unique: {index.IsUnique}");
    Console.WriteLine($"Columns: {string.Join(", ", index.Columns)}");
}

// Get all indexes for a table
foreach (var idx in engine.GetTableIndexes("Orders"))
{
    Console.WriteLine($"  {idx.Name} on ({string.Join(", ", idx.Columns)})");
}

// Using INFORMATION_SCHEMA (SQL standard)
var tables = engine.Query(@"
    SELECT TABLE_NAME, TABLE_TYPE 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'public'");

foreach (var row in tables)
{
    Console.WriteLine($"{row["TABLE_NAME"].AsString()} ({row["TABLE_TYPE"].AsString()})");
}

var columns = engine.Query(@"
    SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Users'
    ORDER BY ORDINAL_POSITION");

foreach (var col in columns)
{
    var nullable = col["IS_NULLABLE"].AsString() == "YES" ? "NULL" : "NOT NULL";
    Console.WriteLine($"  {col["COLUMN_NAME"].AsString()}: {col["DATA_TYPE"].AsString()} {nullable}");
}

// Create a table scan iterator
using var iterator = engine.CreateTableScan("Users");

while (iterator.MoveNext())
{
    WitSqlRow row = iterator.Current;
    Console.WriteLine($"User: {row["Name"].AsString()}");
}

// Exact match on indexed column
// Equivalent to: SELECT * FROM Users WHERE Email = 'alice@test.com'
var keyValues = new WitSqlValue[] { WitSqlValue.FromText("alice@test.com") };

using var iterator = engine.CreateIndexSeek("Users", "IX_Users_Email", keyValues);

while (iterator.MoveNext())
{
    var row = iterator.Current;
    Console.WriteLine($"Found: {row["Name"].AsString()}");
}

// Range scan: Orders where Total between 100 and 500
// Equivalent to: SELECT * FROM Orders WHERE Total >= 100 AND Total <= 500

var startKey = WitSqlValue.FromDecimal(100m);
var endKey = WitSqlValue.FromDecimal(500m);

using var iterator = engine.CreateIndexRangeScan(
    tableName: "Orders",
    indexName: "IX_Orders_Total",
    startKey: startKey,
    startInclusive: true,   // >= 100
    endKey: endKey,
    endInclusive: true);    // <= 500

while (iterator.MoveNext())
{
    var order = iterator.Current;
    Console.WriteLine($"Order {order["Id"].AsInt64()}: ${order["Total"].AsDecimal()}");
}

// If you know the row ID (e.g., from a previous query or index lookup)
long rowId = 12345;

WitSqlRow? row = engine.GetRowById("Users", rowId);

if (row != null)
{
    Console.WriteLine($"User: {row["Name"].AsString()}");
}

public IEnumerable<WitSqlRow> GetOrdersInRange(decimal minTotal, decimal maxTotal, Func<WitSqlRow, bool> additionalFilter)
{
    using var iterator = engine.CreateIndexRangeScan(
        "Orders",
        "IX_Orders_Total",
        WitSqlValue.FromDecimal(minTotal), true,
        WitSqlValue.FromDecimal(maxTotal), true);
    
    while (iterator.MoveNext())
    {
        if (additionalFilter(iterator.Current))
        {
            yield return iterator.Current;
        }
    }
}

// Usage
var orders = GetOrdersInRange(100m, 500m, 
    row => row["Status"].AsString() == "Completed");

// First call: parses SQL, caches plan
engine.Query("SELECT * FROM Users WHERE IsActive = 1");

// Second call: reuses cached plan (faster)
engine.Query("SELECT * FROM Users WHERE IsActive = 1");

// BAD: Different SQL strings, no cache benefit
engine.Query("SELECT * FROM Users WHERE Id = 1");
engine.Query("SELECT * FROM Users WHERE Id = 2");
engine.Query("SELECT * FROM Users WHERE Id = 3");

// GOOD: Same SQL string, parameters vary, cache hit every time
engine.Query("SELECT * FROM Users WHERE Id = @id", new Dictionary<string, object?> { ["id"] = 1 });
engine.Query("SELECT * FROM Users WHERE Id = @id", new Dictionary<string, object?> { ["id"] = 2 });
engine.Query("SELECT * FROM Users WHERE Id = @id", new Dictionary<string, object?> { ["id"] = 3 });

try
{
    engine.Execute("SELECT * FROM NonExistentTable");
}
catch (WitSqlException ex)
{
    Console.WriteLine($"SQL Error: {ex.Message}");
    // Output: SQL Error: Table 'NonExistentTable' does not exist
}

try
{
    engine.Execute("INSERT INTO Users (Email) VALUES (@email)", 
        new Dictionary<string, object?> { ["email"] = "duplicate@test.com" });
}
catch (WitSqlException ex) when (ex.Message.Contains("UNIQUE constraint"))
{
    Console.WriteLine("Email already exists");
}
catch (WitSqlException ex) when (ex.Message.Contains("NOT NULL constraint"))
{
    Console.WriteLine("Required field is missing");
}
catch (WitSqlException ex)
{
    Console.WriteLine($"Database error: {ex.Message}");
}

try
{
    using var result = engine.Execute(
        "SELECT * FROM VeryLargeTable WHERE ComplexCondition(Column)",
        parameters: null,
        timeout: TimeSpan.FromSeconds(5));
    
    // Process results...
}
catch (TimeoutException)
{
    Console.WriteLine("Query took too long and was aborted");
}

using var result = engine.Execute("INSERT INTO Users (Email) VALUES ('duplicate@test.com')");

if (result.RowsAffected == 0)
{
    // Insert might have failed due to constraint (with ON CONFLICT DO NOTHING)
    Console.WriteLine("No rows inserted");
}

// Option 1: using statement
using var engine = new WitSqlEngine(database, ownsStore: true);
// Engine disposed at end of scope

// Option 2: Manual disposal
var engine = new WitSqlEngine(database, ownsStore: true);
try
{
    // Use engine...
}
finally
{
    engine.Dispose();
}

// GOOD: using statement
using var result = engine.Execute("SELECT * FROM Users");
while (result.Read()) { /* ... */ }

// GOOD: Manual disposal
var result = engine.Execute("SELECT * FROM Users");
try
{
    while (result.Read()) { /* ... */ }
}
finally
{
    result.Dispose();
}

// Convenience methods that return collections handle disposal internally
List<WitSqlRow> rows = engine.Query("SELECT * FROM Users");  // No using needed

using var stmt = engine.Prepare("SELECT * FROM Users WHERE Id = @id");
// Statement disposed at end of scope

using var iterator = engine.CreateTableScan("Users");
while (iterator.MoveNext())
{
    // Process rows...
}
// Iterator disposed, resources released

// Shared database, engine per thread
var database = new WitDatabaseBuilder()
    .WithFilePath("shared.witdb")
    .WithBTree()
    .WithTransactions()
    .Build();

Parallel.For(0, 10, i =>
{
    // Each thread gets its own engine
    using var engine = new WitSqlEngine(database, ownsStore: false);
    
    engine.Execute("INSERT INTO Logs (Message) VALUES (@msg)",
        new Dictionary<string, object?> { ["msg"] = $"Thread {i}" });
});

database.Dispose();  // Dispose database after all engines

services.AddSingleton<WitDatabase>(sp =>
{
    return new WitDatabaseBuilder()
        .WithFilePath("app.witdb")
        .WithBTree()
        .WithTransactions()
        .Build();
});

services.AddScoped<WitSqlEngine>(sp =>
{
    var database = sp.GetRequiredService<WitDatabase>();
    return new WitSqlEngine(database, ownsStore: false);
});

public class ThreadSafeEngine : IDisposable
{
    private readonly WitSqlEngine _engine;
    private readonly Lock _lock = new();
    
    public ThreadSafeEngine(WitDatabase database)
    {
        _engine = new WitSqlEngine(database, ownsStore: false);
    }
    
    public List<WitSqlRow> Query(string sql, IDictionary<string, object?>? parameters = null)
    {
        lock (_lock)
        {
            return _engine.Query(sql, parameters);
        }
    }
    
    public int ExecuteNonQuery(string sql, IDictionary<string, object?>? parameters = null)
    {
        lock (_lock)
        {
            return _engine.ExecuteNonQuery(sql, parameters);
        }
    }
    
    public void Dispose() => _engine.Dispose();
}

// File-based
var db = new WitDatabaseBuilder().WithFilePath("app.witdb").WithBTree().WithTransactions().Build();
var engine = new WitSqlEngine(db, ownsStore: true);

// In-memory
var db = WitDatabase.CreateInMemory();
var engine = new WitSqlEngine(db, ownsStore: true);

BEGIN TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT name
ROLLBACK TO name
RELEASE SAVEPOINT name
SET TRANSACTION ISOLATION LEVEL level

row["Column"].AsInt64()          // long
row["Column"].AsInt64OrNull()    // long?
row["Column"].AsDouble()         // double
row["Column"].AsDecimal()        // decimal
row["Column"].AsString()         // string
row["Column"].AsStringOrNull()   // string?
row["Column"].AsBoolean()        // bool
row["Column"].AsDateTime()       // DateTime
row["Column"].AsGuid()           // Guid
row["Column"].AsBytes()          // byte[]
row["Column"].IsNull             // bool

EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123