Invercargill-Sql/plans/phase-1-orm-core.md

Phase 1: ORM Core Design

Overview

This document describes the design for the core ORM functionality in Invercargill-Sql, leveraging Invercargill.Mapping for entity-to-properties mapping and Invercargill.Expressions for type-safe query filtering.

Design Goals

• Implementation agnostic: Database engines provide translation layers via interfaces
• Leverage Invercargill.Mapping: Use PropertyMapper<T> for entity materialization
• Leverage Invercargill.Expressions: Use expression parsing and visitor pattern for query translation
• Fluent API: Consistent with Invercargill's builder patterns

Architecture Diagram

classDiagram
    direction TB
    
    namespace ORM_Core {
        class OrmSession {
            -Connection _connection
            -Dictionary~Type_EntityMapper~ _mappers
            +register_entity~T~(EntityMapper~T~ mapper) void
            +query~T~() Query~T~
            +insert~T~(T entity) void
            +update~T~(T entity) void
            +delete~T~(T entity) void
        }
        
        class Query~T~ {
            -OrmSession _session
            -Expression? _filter
            -OrderByClause[] _orderings
            -int? _limit
            -int? _offset
            +where(string expression) Query~T~
            +order_by(string expression) Query~T~
            +limit(int count) Query~T~
            +offset(int count) Query~T~
            +materialise() Enumerable~T~
            +materialise_async() async Enumerable~T~
        }
        
        class EntityMapper~T~ {
            +string table_name
            +PropertyMapper~T~ property_mapper
            +ColumnDefinition[] columns
            +IndexDefinition[] indexes
            +string primary_key_column
        }
        
        class EntityMapperBuilder~T~ {
            +table(string name) EntityMapperBuilder~T~
            +column~TProp~(string name, getter, setter) ColumnBuilder~T~
            +composite_index(string name, string[] columns) EntityMapperBuilder~T~
            +composite_unique(string name, string[] columns) EntityMapperBuilder~T~
            +build() EntityMapper~T~
        }
        
        class ColumnBuilder~T~ {
            -EntityMapperBuilder~T~ _parent
            +primary_key() EntityMapperBuilder~T~
            +required() EntityMapperBuilder~T~
            +unique() EntityMapperBuilder~T~
            +index() EntityMapperBuilder~T~
            +default_value(T value) EntityMapperBuilder~T~
            +default_now() EntityMapperBuilder~T~
        }
    }
    
    namespace SQL_Translation {
        class SqlDialect {
            <<interface>>
            +translate_type(ColumnType type) string
            +translate_expression(Expression expr) string
            +build_select(SelectQuery query) string
            +build_insert(InsertQuery query) string
            +build_update(UpdateQuery query) string
            +build_delete(DeleteQuery query) string
        }
        
        class SqliteDialect {
            +translate_type(ColumnType type) string
            +translate_expression(Expression expr) string
            +build_select(SelectQuery query) string
            +build_insert(InsertQuery query) string
            +build_update(UpdateQuery query) string
            +build_delete(DeleteQuery query) string
        }
        
        class ExpressionToSqlVisitor {
            -StringBuilder _sql
            -SqlDialect _dialect
            +visit_binary(BinaryExpression expr) void
            +visit_property(PropertyExpression expr) void
            +visit_literal(LiteralExpression expr) void
            +get_sql() string
        }
    }
    
    namespace Type_System {
        class ColumnType {
            <<enumeration>>
            INT_32
            INT_64
            TEXT
            BOOLEAN
            DECIMAL
            DATETIME
            BINARY
            UUID
        }
        
        class ColumnDefinition {
            +string name
            +ColumnType type
            +bool is_primary_key
            +bool is_required
            +bool is_unique
            +bool has_index
            +Element? default_value
        }
    }
    
    OrmSession --> EntityMapper~T~ : manages
    OrmSession --> Query~T~ : creates
    Query~T~ --> SqlDialect : uses
    EntityMapperBuilder~T~ --> ColumnBuilder~T~ : creates
    ColumnBuilder~T~ --> EntityMapperBuilder~T~ : returns to parent
    SqlDialect <|.. SqliteDialect : implements
    SqliteDialect --> ExpressionToSqlVisitor : uses
    ExpressionToSqlVisitor ..> Expression : visits

Component Details

1. EntityMapper and Builder Pattern

The EntityMapper<T> wraps PropertyMapper<T> and adds ORM-specific metadata (table name, column constraints, indexes).

Key Design Decision: Use sub-builders that return to the parent builder for fluent chaining, similar to PropertyMappingBuilder in Invercargill.Mapping.

// Example usage
var mapper = EntityMapper.build_for<User>(b => b
    .table("users")
    .column<int64>("id", u => u.id, (u, v) => u.id = v)
        .primary_key()
    .column<string>("name", u => u.name, (u, v) => u.name = v)
        .required()
    .column<string>("email", u => u.email, (u, v) => u.email = v)
        .unique()
        .index()
    .column<bool>("is_active", u => u.is_active, (u, v) => u.is_active = v)
    .column<double>("balance", u => u.balance, (u, v) => u.balance = v)
        .default_value(0.0)
    .column<DateTime>("created_at", u => u.created_at, (u, v) => u.created_at = v)
        .default_now()
    .composite_index("idx_name_email", {"name", "email"})
    .composite_unique("uk_name_email", {"name", "email"}));

orm.register_entity(mapper);

2. GType to ColumnType Mapping

Automatic type inference from generic type parameter:

Vala Type	GType	ColumnType	SQLite Storage	PostgreSQL Storage
int	G_TYPE_INT	INT_32	INTEGER	INT
int64	G_TYPE_INT64	INT_64	INTEGER	BIGINT
string	G_TYPE_STRING	TEXT	TEXT	TEXT
bool	G_TYPE_BOOLEAN	BOOLEAN	INTEGER (0/1)	BOOLEAN
double	G_TYPE_DOUBLE	DECIMAL	REAL	DOUBLE PRECISION
float	G_TYPE_FLOAT	DECIMAL	REAL	REAL
DateTime	G_TYPE_DATE_TIME	DATETIME	INTEGER (epoch)	TIMESTAMP
uint8[]	G_TYPE_BYTE_ARRAY	BINARY	BLOB	BYTEA

3. Query API

Queries use Invercargill.Expressions string syntax for filtering. The query object does NOT extend Enumerable<T> - instead it provides materialise() methods that return Enumerable<T>.

// Create query
var query = orm.query<User>();

// Build query with fluent API
var active_users = orm.query<User>()
    .where("u => u.is_active == true")
    .order_by("u => u.created_at")
    .limit(10)
    .materialise();  // Returns Enumerable<User>

// Async materialization
var users_async = yield orm.query<User>()
    .where("u => u.age > 18")
    .materialise_async();

// Complex filter with AND/OR
var results = orm.query<User>()
    .where("u => (u.age > 18 && u.is_active == true) || u.is_admin == true")
    .order_by("u => u.name")
    .materialise();

4. Expression to SQL Translation

The ExpressionToSqlVisitor implements Invercargill.Expressions.ExpressionVisitor to walk expression trees and generate SQL WHERE clauses.

public class ExpressionToSqlVisitor : Object, ExpressionVisitor {
    private StringBuilder _sql;
    private SqlDialect _dialect;
    private EntityMapper _entity_mapper;
    
    public void visit_binary(BinaryExpression expr) {
        _sql.append("(");
        expr.left.accept(this);
        
        switch (expr.op) {
            case BinaryOperator.AND:
                _sql.append(" AND ");
                break;
            case BinaryOperator.OR:
                _sql.append(" OR ");
                break;
            case BinaryOperator.EQUAL:
                _sql.append(" = ");
                break;
            // ... other operators
        }
        
        expr.right.accept(this);
        _sql.append(")");
    }
    
    public void visit_property(PropertyExpression expr) {
        // Translate: u.name -> "name" (column name)
        var column_name = _entity_mapper.get_column_name(expr.property_name);
        _sql.append(column_name);
    }
    
    public void visit_literal(LiteralExpression expr) {
        // Generate parameter placeholder
        _sql.append("?");
        // Store value for parameter binding
    }
}

5. SqlDialect Interface

Database-specific SQL generation is abstracted behind SqlDialect:

public interface SqlDialect : Object {
    // Type translation
    public abstract string translate_type(ColumnType type);
    
    // Expression translation
    public abstract string translate_expression(Expression expr, EntityMapper mapper);
    
    // CRUD SQL generation
    public abstract string build_select(SelectQuery query);
    public abstract string build_insert(InsertQuery query);
    public abstract string build_update(UpdateQuery query);
    public abstract string build_delete(DeleteQuery query);
}

6. CRUD Operations

// Insert
var user = new User() {
    name = "John Doe",
    email = "john@example.com",
    is_active = true
};
orm.insert(user);
print("Inserted with ID: %lld\n", user.id);  // ID populated after insert

// Update
user.name = "Jane Doe";
orm.update(user);

// Delete
orm.delete(user);

// Batch operations
var users = new Series<User>();
// ... add users
orm.insert_all(users);

File Structure

src/
├── orm/
│   ├── orm-session.vala              # Main ORM entry point
│   ├── entity-mapper.vala            # EntityMapper class
│   ├── entity-mapper-builder.vala    # Fluent builder for EntityMapper
│   ├── column-builder.vala           # Sub-builder for column constraints
│   ├── query.vala                    # Query class
│   ├── column-type.vala              # ColumnType enum
│   ├── column-definition.vala        # Column metadata
│   └── index-definition.vala         # Index metadata
├── dialects/
│   ├── sql-dialect.vala              # Interface for SQL dialects
│   └── sqlite-dialect.vala           # SQLite implementation
└── expressions/
    └── expression-to-sql-visitor.vala # Expression tree to SQL translator

Dependencies

• Invercargill.Mapping - PropertyMapper<T>, Mapper<TNative, TElement>
• Invercargill.Expressions - Expression, ExpressionParser, ExpressionVisitor
• Invercargill.DataStructures - Dictionary, Vector, Series
• Invercargill - Enumerable, Properties, Element

Implementation Checklist

• Create ColumnType enum with GType mapping
• Create ColumnDefinition and IndexDefinition classes
• Implement ColumnBuilder with fluent return to parent
• Implement EntityMapperBuilder with generic type parameter
• Implement EntityMapper wrapping PropertyMapper
• Create SqlDialect interface
• Implement SqliteDialect with type translation
• Implement ExpressionToSqlVisitor for WHERE clause generation
• Create Query class with where(), order_by(), limit(), offset()
• Implement Query.materialise() using expression translation
• Implement OrmSession with entity registration
• Implement CRUD operations (insert, update, delete)
• Add async variants for all operations
• Write unit tests for entity mapping
• Write unit tests for query translation
• Write integration tests with SQLite

Usage Example

using InvercargillSql;
using InvercargillSql.Orm;

void main() {
    try {
        // Create connection
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        
        // Create ORM session
        var orm = new OrmSession(conn);
        
        // Register entities
        orm.register_entity(EntityMapper.build_for<User>(b => b
            .table("users")
            .column<int64>("id", u => u.id, (u, v) => u.id = v)
                .primary_key()
            .column<string>("name", u => u.name, (u, v) => u.name = v)
                .required()
            .column<string>("email", u => u.email, (u, v) => u.email = v)
                .unique()
            .column<DateTime>("created_at", u => u.created_at, (u, v) => u.created_at = v)
                .default_now()));
        
        // Insert
        var user = new User() { name = "John", email = "john@example.com" };
        orm.insert(user);
        
        // Query
        var active = orm.query<User>()
            .where("u => u.name == 'John'")
            .materialise();
        
        foreach (var u in active) {
            print("User: %s\n", u.name);
        }
        
        // Update
        user.name = "Jane";
        orm.update(user);
        
        // Delete
        orm.delete(user);
        
        conn.close();
    } catch (Error e) {
        stderr.printf("Error: %s\n", e.message);
    }
}