Skip to content

Home

Jump to bottom
Peter Nugent edited this page Aug 12, 2025 · 1 revision

SQLite Toolkit User Guide

A comprehensive guide to using the SQLite Toolkit for BHoM, covering everything from basic operations to advanced scenarios.

Table of Contents

Getting Started

Prerequisites

  • BHoM Framework installed
  • .NET 6.0 or later
  • SQLite database file or ability to create one

Basic Setup

using BH.Adapter.SQLite;
using BH.oM.Adapter.Commands;
using BH.oM.SQLite.Examples;
using BH.oM.SQLite.Requests;

// Create adapter instance
var adapter = new SQLiteAdapter("MyProject.db");

// Your operations here...

// Close connection when finished
adapter.Execute(new Close());

Your First Data Operations

// Create sample data
var readings = new List<SensorReading>
{
    new SensorReading 
    { 
        SensorId = "SENSOR_001", 
        Temperature = 22.5, 
        Humidity = 45.0,
        Timestamp = DateTime.Now,
        IsValid = true,
        StatusCode = 200
    }
};

// Push to database (automatically creates table)
adapter.Push(readings);

// Pull all data back
var allReadings = adapter.Pull();

Understanding the Three-Tier Strategy

The SQLite Toolkit automatically selects the best mapping approach for your data using a sophisticated three-tier strategy:

Tier 1: IRecord Objects (Automatic Mapping)

When it's used: Objects implementing the IRecord interface.

Advantages:

  • Zero configuration required
  • Automatic table creation
  • Optimal performance
  • Full type safety

Example:

public class TemperatureReading : BHoMObject, IRecord
{
    [Description("Unique sensor identifier")]
    public virtual string SensorId { get; set; } = "";

    [Description("Temperature reading in degrees Celsius")]
    public virtual double Temperature { get; set; } = 0.0;

    [Description("Reading timestamp")]
    public virtual DateTime Timestamp { get; set; } = DateTime.Now;

    [Description("Data quality indicator")]
    public virtual bool IsValid { get; set; } = true;
}

// Usage - completely automatic
var readings = new List<TemperatureReading> { /* your data */ };
adapter.Push(readings); // Table created automatically with optimal schema

Tier 2: Custom Mapping (PushConfig)

When it's used: Complex BHoM objects requiring custom property mapping or exclusions.

Advantages:

  • Full control over column naming
  • Property exclusion capabilities
  • Nested property mapping
  • Conflict resolution

Example:

public class StructuralProject : BHoMObject
{
    public override string Name { get; set; } = "";
    public int ProjectId { get; set; }
    public string Description { get; set; } = "";
    public DateTime StartDate { get; set; }
    public double Budget { get; set; }
    public string InternalNotes { get; set; } = ""; // Will be excluded
}

// Custom mapping configuration
var config = new PushConfig
{
    PropertyMappings = new Dictionary<string, string>
    {
        { "Name", "ProjectName" },           // Rename column
        { "ProjectId", "ProjectNumber" },    // Rename column
        { "StartDate", "CommencementDate" }  // Rename column
    },
    ExcludedProperties = new List<string> 
    { 
        "InternalNotes",    // Exclude sensitive data
        "CustomData"        // Exclude complex objects
    }
};

// Usage with custom mapping
adapter.Push(projects, config);

Tier 3: Primitive Fallback (Automatic)

When it's used: Simple objects with only primitive properties that don't implement IRecord.

Advantages:

  • Works with any simple object
  • Automatic primitive property detection
  • No configuration required

Example:

public class SimpleMetric : BHoMObject
{
    public int MetricId { get; set; }
    public string MetricName { get; set; } = "";
    public double Value { get; set; }
    public DateTime RecordedAt { get; set; }
}

// Automatically handled as primitive fallback
adapter.Push(metrics); // Only primitive properties are mapped

Working with IRecord Objects

Creating IRecord Objects

The most efficient way to work with the SQLite Toolkit is to implement the IRecord interface:

public class MaterialProperties : BHoMObject, IRecord
{
    [Description("Material name identifier")]
    public virtual string MaterialName { get; set; } = "";

    [Description("Density in kg/m³")]
    public virtual double Density { get; set; } = 0.0;

    [Description("Young's modulus in Pa")]
    public virtual double YoungModulus { get; set; } = 0.0;

    [Description("Poisson's ratio")]
    public virtual double PoissonRatio { get; set; } = 0.0;

    [Description("Material type classification")]
    public virtual MaterialType Type { get; set; } = MaterialType.Unknown;

    [Description("Recyclability indicator")]
    public virtual bool IsRecyclable { get; set; } = false;

    [Description("Cost per cubic metre")]
    public virtual decimal CostPerCubicMeter { get; set; } = 0.0m;
}

public enum MaterialType
{
    Unknown = 0,
    Steel = 1,
    Concrete = 2,
    Wood = 3,
    Aluminium = 4
}

Benefits of IRecord Implementation

  1. Automatic Schema Generation: Tables and columns created automatically
  2. Type Safety: Full compile-time type checking
  3. Performance: Optimised SQL generation and execution
  4. Maintenance: Changes to your objects automatically reflect in the database

IRecord Best Practices

public class OptimalSensorReading : BHoMObject, IRecord
{
    // Use virtual properties for BHoM compatibility
    [Description("Sensor device identifier")]
    public virtual string SensorId { get; set; } = "";

    // Include units in descriptions
    [Description("Temperature reading in degrees Celsius")]
    public virtual double Temperature { get; set; } = 0.0;

    // Use descriptive property names
    [Description("Data quality validation result")]
    public virtual bool IsValid { get; set; } = true;

    // Enums are fully supported
    [Description("Sensor operational status")]
    public virtual SensorStatus Status { get; set; } = SensorStatus.Active;

    // DateTime properties work seamlessly
    [Description("Measurement timestamp in UTC")]
    public virtual DateTime Timestamp { get; set; } = DateTime.UtcNow;
}

Custom Property Mapping

Basic Property Mapping

var config = new PushConfig
{
    PropertyMappings = new Dictionary<string, string>
    {
        { "Name", "DisplayName" },
        { "Id", "Identifier" },
        { "Value", "MeasuredValue" }
    }
};

Nested Property Mapping

public class ComplexProject : BHoMObject
{
    public ProjectDetails Details { get; set; } = new ProjectDetails();
    public ProjectStatistics Stats { get; set; } = new ProjectStatistics();
}

var config = new PushConfig
{
    PropertyMappings = new Dictionary<string, string>
    {
        { "Details.Name", "ProjectName" },
        { "Details.Manager", "ProjectManager" },
        { "Stats.ElementCount", "TotalElements" },
        { "Stats.TotalCost", "ProjectBudget" }
    }
};

Property Exclusion

var config = new PushConfig
{
    ExcludedProperties = new List<string>
    {
        "InternalId",           // Internal system field
        "TempData",             // Temporary processing data
        "ComplexCalculations",  // Complex nested objects
        "CachedResults"         // Runtime cache data
    }
};

Advanced Mapping Scenarios

public class EngineeringAnalysis : BHoMObject
{
    public string AnalysisName { get; set; } = "";
    public AnalysisParameters Parameters { get; set; } = new AnalysisParameters();
    public List<AnalysisResult> Results { get; set; } = new List<AnalysisResult>();
    public Dictionary<string, object> Metadata { get; set; } = new Dictionary<string, object>();
}

var advancedConfig = new PushConfig
{
    PropertyMappings = new Dictionary<string, string>
    {
        // Flatten nested objects
        { "Parameters.LoadCase", "LoadCaseName" },
        { "Parameters.Safety", "SafetyFactor" },
        { "Parameters.Method", "AnalysisMethod" },
        
        // Rename for database conventions
        { "AnalysisName", "analysis_name" },
        { "CreatedDate", "created_at" }
    },
    ExcludedProperties = new List<string>
    {
        "Results",      // Store separately due to complexity
        "Metadata",     // Complex dictionary - handle separately
        "TempFiles",    // Temporary analysis files
        "CacheData"     // Runtime cache
    }
};

Filtering and Querying

Equality Filtering

Basic equality filtering for exact matches:

var exactMatch = new EqualityFilterRequest
{
    TableName = "SensorReading",
    ColumnFilters = new List<ColumnFilter>
    {
        new ColumnFilter
        {
            ColumnName = "SensorId",
            Values = new List<object> { "TEMP_001" }
        }
    }
};

var results = adapter.Pull(exactMatch);

Multiple values (IN clause):

var multipleValues = new EqualityFilterRequest
{
    TableName = "MaterialProperties",
    ColumnFilters = new List<ColumnFilter>
    {
        new ColumnFilter
        {
            ColumnName = "Type",
            Values = new List<object> { "Steel", "Concrete", "Aluminium" }
        }
    }
};

Multiple conditions with logical operators:

var complexFilter = new EqualityFilterRequest
{
    TableName = "SensorReading",
    ColumnFilters = new List<ColumnFilter>
    {
        new ColumnFilter
        {
            ColumnName = "IsValid",
            Values = new List<object> { true }
        },
        new ColumnFilter
        {
            ColumnName = "StatusCode",
            Values = new List<object> { 200, 201 }
        }
    },
    Logic = LogicalOperator.And,  // AND conditions together
    MaxResults = 100              // Limit results
};

Range Filtering

Numeric ranges:

var temperatureRange = new RangeFilterRequest
{
    TableName = "SensorReading",
    ColumnRanges = new Dictionary<string, GeneralDomain>
    {
        { "Temperature", new GeneralDomain(20.0, 30.0) },
        { "Humidity", new GeneralDomain(40.0, 60.0) }
    },
    InclusiveBounds = true,  // 20 <= Temperature <= 30
    MaxResults = 500
};

Date ranges:

var dateRange = new RangeFilterRequest
{
    TableName = "SensorReading",
    ColumnRanges = new Dictionary<string, GeneralDomain>
    {
        { "Timestamp", new GeneralDomain(
            DateTime.Today.AddDays(-7),  // Last 7 days
            DateTime.Now
        )}
    }
};

Custom SQL Queries

Simple aggregation:

var averageTemp = new CustomSqlRequest
{
    SqlQuery = "SELECT AVG(Temperature) as AverageTemperature FROM SensorReading WHERE IsValid = @valid",
    Parameters = new Dictionary<string, object>
    {
        { "@valid", true }
    }
};

Complex analytical queries:

var complexAnalysis = new CustomSqlRequest
{
    SqlQuery = @"
        SELECT 
            SensorId,
            DATE(Timestamp) as ReadingDate,
            AVG(Temperature) as AvgTemp,
            MIN(Temperature) as MinTemp,
            MAX(Temperature) as MaxTemp,
            COUNT(*) as ReadingCount,
            STDDEV(Temperature) as TempVariance
        FROM SensorReading 
        WHERE 
            Timestamp BETWEEN @startDate AND @endDate
            AND IsValid = @valid
            AND StatusCode = @statusCode
        GROUP BY SensorId, DATE(Timestamp)
        HAVING COUNT(*) >= @minReadings
        ORDER BY ReadingDate DESC, AvgTemp DESC
        LIMIT @maxResults",
    Parameters = new Dictionary<string, object>
    {
        { "@startDate", DateTime.Today.AddDays(-30) },
        { "@endDate", DateTime.Now },
        { "@valid", true },
        { "@statusCode", 200 },
        { "@minReadings", 10 },
        { "@maxResults", 100 }
    }
};

Join queries across tables:

var joinQuery = new CustomSqlRequest
{
    SqlQuery = @"
        SELECT 
            s.SensorId,
            s.Temperature,
            s.Timestamp,
            m.MaterialName,
            m.Density
        FROM SensorReading s
        INNER JOIN MaterialProperties m ON s.MaterialId = m.Id
        WHERE 
            s.Temperature > @minTemp
            AND m.Type = @materialType
        ORDER BY s.Timestamp DESC",
    Parameters = new Dictionary<string, object>
    {
        { "@minTemp", 25.0 },
        { "@materialType", "Steel" }
    }
};

Schema Management

Automatic Schema Introspection

Get information about all tables:

var allTables = new SchemaRequest
{
    TableNames = new List<string>(),  // Empty = all tables
    IncludeColumns = true,
    IncludeIndexes = true,
    IncludeForeignKeys = true
};

var schema = adapter.Pull(allTables);

Get specific table information:

var specificTable = new SchemaRequest
{
    TableNames = new List<string> { "SensorReading", "MaterialProperties" },
    IncludeColumns = true,
    IncludeIndexes = false,
    IncludeForeignKeys = false
};

Table Operations

Query table with advanced options:

var tableQuery = new TableRequest
{
    Name = "SensorReading",
    Columns = new List<string> 
    { 
        "SensorId", 
        "Temperature", 
        "Humidity", 
        "Timestamp" 
    },
    WhereConditions = new List<string> 
    { 
        "Temperature > 20",
        "IsValid = 1",
        "StatusCode = 200"
    },
    OrderBy = new List<string> { "Timestamp DESC", "Temperature ASC" },
    Limit = 100,
    Offset = 0,
    Distinct = false
};

var results = adapter.Pull(tableQuery);

Pagination example:

// Get first page
var page1 = new TableRequest
{
    Name = "SensorReading",
    OrderBy = new List<string> { "Timestamp DESC" },
    Limit = 50,
    Offset = 0
};

// Get second page
var page2 = new TableRequest
{
    Name = "SensorReading",
    OrderBy = new List<string> { "Timestamp DESC" },
    Limit = 50,
    Offset = 50
};

Error Handling

Basic Error Handling

try
{
    adapter.Push(sensorData);
}
catch (Exception ex)
{
    Console.WriteLine($"Error pushing data: {ex.Message}");
    // Handle error appropriately
}

Validating Operations

The toolkit provides extensive validation and will use BHoM's error recording system:

// Check for errors after operations
var queryResult = adapter.Pull(customSqlRequest);
var result = queryResult.FirstOrDefault() as QueryResult;

if (result != null && !result.IsSuccess)
{
    Console.WriteLine($"Query failed: {result.ErrorMessage}");
    // Handle failed query
}

Common Error Scenarios

  1. Invalid SQL Syntax:
var invalidQuery = new CustomSqlRequest
{
    SqlQuery = "SELCT * FROM NonExistentTable",  // Typo in SELECT
    Parameters = new Dictionary<string, object>()
};

// Will return QueryResult with IsSuccess = false and descriptive ErrorMessage
  1. Missing Parameters:
var missingParamQuery = new CustomSqlRequest
{
    SqlQuery = "SELECT * FROM SensorReading WHERE Temperature > @minTemp",
    Parameters = new Dictionary<string, object>()  // Missing @minTemp parameter
};
  1. Type Mismatches:
var typeMismatch = new CustomSqlRequest
{
    SqlQuery = "SELECT * FROM SensorReading WHERE Temperature > @minTemp",
    Parameters = new Dictionary<string, object>
    {
        { "@minTemp", "not_a_number" }  // String instead of number
    }
};

Performance Optimisation

Batch Operations

Process large datasets efficiently:

// Process large datasets in batches
var largeMeasurementSet = GenerateLargeMeasurementSet(10000);

// Split into batches of 1000
var batches = largeMeasurementSet
    .Select((x, i) => new { Index = i, Value = x })
    .GroupBy(x => x.Index / 1000)
    .Select(x => x.Select(v => v.Value).ToList())
    .ToList();

foreach (var batch in batches)
{
    adapter.Push(batch);
    // Optional: progress reporting
    Console.WriteLine($"Processed batch {batches.IndexOf(batch) + 1} of {batches.Count}");
}

Connection Management

// Efficient connection usage
using (var adapter = new SQLiteAdapter("LargeDataset.db"))
{
    adapter.Execute(new Open() { FileName = "LargeDataset.db" });
    
    // Perform multiple operations with same connection
    adapter.Push(batch1);
    adapter.Push(batch2);
    adapter.Push(batch3);
    
    var results = adapter.Pull(complexQuery);
    
    // Connection automatically closed
}

Query Optimisation

Use indexed columns for better performance:

// Good: Filter on likely indexed columns
var optimisedQuery = new CustomSqlRequest
{
    SqlQuery = @"
        SELECT * FROM SensorReading 
        WHERE SensorId = @sensorId          -- Likely indexed
        AND Timestamp >= @startDate         -- Likely indexed
        ORDER BY Timestamp DESC             -- Use index for sorting
        LIMIT @maxResults",
    Parameters = new Dictionary<string, object>
    {
        { "@sensorId", "TEMP_001" },
        { "@startDate", DateTime.Today },
        { "@maxResults", 1000 }
    }
};

Best Practices

Security Best Practices

  1. Always Use Parameterised Queries:
// Good - parameterised
var safeQuery = new CustomSqlRequest
{
    SqlQuery = "SELECT * FROM SensorReading WHERE SensorId = @sensorId",
    Parameters = new Dictionary<string, object> { { "@sensorId", userInput } }
};

// NEVER - SQL injection risk
var unsafeQuery = new CustomSqlRequest
{
    SqlQuery = $"SELECT * FROM SensorReading WHERE SensorId = '{userInput}'"
};
  1. Validate Input Data:
public bool ValidateSensorReading(SensorReading reading)
{
    if (string.IsNullOrWhiteSpace(reading.SensorId))
        return false;
        
    if (reading.Temperature < -273.15 || reading.Temperature > 1000)
        return false;
        
    if (reading.Humidity < 0 || reading.Humidity > 100)
        return false;
        
    return true;
}

Data Modeling Best Practices

  1. Use Appropriate Data Types:
public class WellDesignedReading : BHoMObject, IRecord
{
    public virtual string SensorId { get; set; } = "";           // String for IDs
    public virtual double Temperature { get; set; } = 0.0;       // Double for measurements
    public virtual DateTime Timestamp { get; set; } = DateTime.UtcNow;  // DateTime for timestamps
    public virtual bool IsValid { get; set; } = true;           // Boolean for flags
    public virtual int StatusCode { get; set; } = 200;          // Integer for codes
    public virtual decimal Cost { get; set; } = 0.0m;           // Decimal for money
}
  1. Design for Querying:
public class QueryOptimisedReading : BHoMObject, IRecord
{
    // Primary identifier - will be queried frequently
    public virtual string SensorId { get; set; } = "";
    
    // Timestamp - will be used for time-based queries
    public virtual DateTime Timestamp { get; set; } = DateTime.UtcNow;
    
    // Status flags - will be used for filtering
    public virtual bool IsValid { get; set; } = true;
    public virtual ReadingStatus Status { get; set; } = ReadingStatus.Normal;
    
    // Measurement data
    public virtual double Temperature { get; set; } = 0.0;
    public virtual double Humidity { get; set; } = 0.0;
}

Troubleshooting

Common Issues and Solutions

Issue: "Table does not exist"

Cause: Attempting to query a table that hasn't been created yet. Solution: Ensure you've pushed data of that type first, or the table exists.

// Ensure table exists by pushing data first
adapter.Push(new List<SensorReading> { new SensorReading() });

// Now queries will work
var results = adapter.Pull(new EqualityFilterRequest { TableName = "SensorReading" });

Issue: "Column not found"

Cause: Property name doesn't match expected column name. Solution: Check your property mappings or use correct property names.

// Check what columns actually exist
var schema = adapter.Pull(new SchemaRequest 
{ 
    TableNames = new List<string> { "SensorReading" },
    IncludeColumns = true 
});

Issue: "SQL injection detected"

Cause: Using string concatenation instead of parameters. Solution: Always use parameterised queries.

// Wrong
var query = $"SELECT * FROM SensorReading WHERE SensorId = '{sensorId}'";

// Correct
var request = new CustomSqlRequest
{
    SqlQuery = "SELECT * FROM SensorReading WHERE SensorId = @sensorId",
    Parameters = new Dictionary<string, object> { { "@sensorId", sensorId } }
};

Issue: Poor performance with large datasets

Cause: Not using batch operations or appropriate filtering. Solution: Implement batch processing and use indexed columns for filtering.

// Use pagination for large result sets
var pagedResults = new TableRequest
{
    Name = "SensorReading",
    OrderBy = new List<string> { "Timestamp DESC" },
    Limit = 1000,  // Reasonable page size
    Offset = 0
};

Debugging Tips

  1. Enable Detailed Error Messages:
var result = adapter.Pull(customQuery).FirstOrDefault() as QueryResult;
if (result != null && !result.IsSuccess)
{
    Console.WriteLine($"Query failed: {result.ErrorMessage}");
    Console.WriteLine($"SQL: {customQuery.SqlQuery}");
    Console.WriteLine($"Parameters: {string.Join(", ", customQuery.Parameters)}");
}
  1. Validate Your Objects:
// Check if your object implements IRecord
var isIRecord = typeof(IRecord).IsAssignableFrom(typeof(YourObjectType));
Console.WriteLine($"Implements IRecord: {isIRecord}");

// Check property types
foreach (var prop in typeof(YourObjectType).GetProperties())
{
    Console.WriteLine($"{prop.Name}: {prop.PropertyType}");
}
  1. Test with Simple Cases First:
// Start with minimal test case
var simpleTest = new List<SensorReading>
{
    new SensorReading { SensorId = "TEST", Temperature = 20.0 }
};

adapter.Push(simpleTest);
var retrieved = adapter.Pull();

Console.WriteLine($"Pushed 1, retrieved {retrieved.Count()}");

Getting Help

If you encounter issues not covered in this guide:

  1. Check the test suite: The comprehensive test suite in SQLite_Toolkit.Tests/ provides working examples of all functionality
  2. Review error messages: The toolkit provides detailed error messages through BHoM's error recording system
  3. Consult BHoM documentation: https://bhom.xyz/documentation/
  4. Open an issue: Report bugs or request features on the project's GitHub repository

This user guide covers the essential aspects of working with the SQLite Toolkit. The toolkit is designed to be intuitive and follows BHoM conventions, making it easy to integrate into your existing BHoM workflows.

Clone this wiki locally