# Library Generator System — Implementation Plan

## Overview

Build a template-based code generator that:
- **Input**: DBML (Database Markup Language) data model
- **Processing**: Parse DBML → Apply helpers → Render Jinja2 template
- **Output**: Python SQLite code (schema creation, sample data insertion)
- **Deliverable**: Working `library.py` program demonstrating active/returned loans

---

## Step 1: Data Model — DBML Schema

**File**: `data/library.dbml`

Define 4 tables with relationships:

```
Table authors
- id (primary key)
- name (string)
- birth_year (integer)

Table books
- id (primary key)
- title (string)
- isbn (string)
- author_id (foreign key → authors.id)

Table borrowers
- id (primary key)
- name (string)
- email (string)

Table loans
- id (primary key)
- book_id (foreign key → books.id)
- borrower_id (foreign key → borrowers.id)
- loan_date (date)
- return_date (date, nullable)
```

**Responsibility**: Spec-compliant DBML describing the interface boundary (persistent data).

---

## Step 2: Parser & Helpers

**File**: `generator/helpers.py`

Implement:

1. **DBML Parser**: Load `library.dbml` using Python (using a DBML library or custom parser)
   - Parse table definitions
   - Extract fields (name, type, constraints)
   - Extract relationships (foreign keys)
   - Produce in-memory dictionary/object representation

2. **Helper Functions** (reshape for template consumption):
   - `parse_dbml(filepath)` → dict with tables, fields, relationships
   - `get_python_type(dbml_type)` → convert DBML types to Python types (e.g. `INT` → `int`, `STRING` → `str`)
   - `get_sqlite_type(dbml_type)` → convert DBML types to SQLite types (e.g. `INT` → `INTEGER`, `STRING` → `TEXT`)
   - `get_sample_data()` → return 3 authors, 5 books, 2 borrowers, 2 loans (mixed returned/active)

**Responsibility**: Data transformation only; keep templates simple.

---

## Step 3: Template — Jinja2

**File**: `templates/library_schema.py.jinja2`

Generate Python module with:

1. **Imports**: `sqlite3`, `datetime`

2. **Database initialization function**:
   - Create `library.db` SQLite file
   - Loop over tables, execute `CREATE TABLE` statements
   - Return connection object

3. **Schema constants** (if helpful):
   - Table names, field names

4. **Sample data insertion function**:
   - Accept (connection, data dict)
   - Execute `INSERT` statements for authors, books, borrowers, loans
   - Use parameterized queries

5. **Report function** (optional):
   - Query active loans (return_date IS NULL)
   - Join books, borrowers
   - Return formatted output

**Template structure**:
```jinja2
import sqlite3
from datetime import datetime

def init_database(db_path="library.db"):
    """Create database and schema."""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    {%- for table in tables %}
    # CREATE {{ table.name }}
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS {{ table.name }} (
        {%- for field in table.fields %}
            {{ field.name }} {{ field.sql_type }}
            {%- if field.constraints %} {{ field.constraints }}{% endif %},
        {%- endfor %}
        )
    """)
    {%- endfor %}
    
    conn.commit()
    return conn

def insert_sample_data(conn, data):
    """Insert sample data."""
    # ... INSERT statements
    pass

def get_active_loans(conn):
    """Report active loans."""
    # ... SELECT query
    pass
```

**Responsibility**: Template reads like target Python code; Jinja2 markup is thin.

---

## Step 4: Generator Script

**File**: `generator/generate_library.py`

Driver script:

1. Parse command-line args (optional: input DBML path, output path)
2. Call `parse_dbml()` from helpers
3. Retrieve sample data via `get_sample_data()`
4. Load Jinja2 environment (`autoescape=False`, `trim_blocks=True`, `lstrip_blocks=True`)
5. Render `library_schema.py.jinja2` with parsed data
6. Write output to `generated/library_schema.py`
7. Run `black` (if available) to format

```bash
python generator/generate_library.py
```

**Output**: `generated/library_schema.py` (never edit by hand)

---

## Step 5: Main Program

**File**: `library.py`

Executable script:

1. Import from `generated/library_schema.py`
2. Call `init_database()` → creates `library.db`
3. Call `insert_sample_data(conn, data)` → populates tables
4. Call `get_active_loans(conn)` → prints report
5. Close connection

Example output:
```
Active Loans Report
===================
Book: "1984" | Borrower: "Alice Johnson" | Loan Date: 2025-10-01
Book: "To Kill a Mockingbird" | Borrower: "Bob Smith" | Loan Date: 2025-10-15
```

---

## Directory Structure

```
project/
├── data/
│   └── library.dbml                    # DBML data model
├── templates/
│   └── library_schema.py.jinja2       # Jinja2 template
├── generator/
│   ├── helpers.py                      # Parsers and helpers
│   └── generate_library.py             # Generator driver script
├── generated/
│   └── library_schema.py               # OUTPUT (git-ignored)
├── library.py                          # Main program
└── .gitignore                          # Ignore generated/
```

---

## Step 6: Testing Strategy

| Test | Method |
|------|--------|
| **Golden file** | Run generator, commit output snapshot, verify no regressions |
| **Compilation** | Import `generated/library_schema.py` without errors |
| **Round-trip** | Execute `library.py`, query `library.db`, verify sample data |

---

## Dependencies

- `python3` (3.8+)
- `jinja2` — template engine
- `dbml` or custom DBML parser
- `sqlite3` — built-in
- `black` (optional) — code formatter

Install:
```bash
pip install jinja2 dbml
```

---

## Key Rules Applied

| Rule | How |
|------|-----|
| **Never edit generated output** | Output goes to `generated/`, separate from hand-written `library.py` |
| **Interface in data model** | New table/field → modify `library.dbml`, regenerate |
| **Data reshaping in helpers** | Sample data, type conversions → `helpers.py` |
| **Templates read like code** | `library_schema.py.jinja2` looks like Python with thin Jinja2 layer |
| **One concern per template** | Single template generates complete schema module |
| **Control whitespace** | Use `{%-`, `-%}`, `trim_blocks`, `lstrip_blocks` |

---

## Execution Flow

```
1. Data Model (library.dbml)
        ↓
2. Parser (parse_dbml) + Helpers (type conversion, sample data)
        ↓
3. Template Engine (Jinja2)
        ↓
4. Generated Output (library_schema.py)
        ↓
5. Main Program (library.py imports and uses generated code)
```

---

## Success Criteria

✅ DBML file parses correctly  
✅ Generator produces valid Python code without errors  
✅ `library.py` runs and creates `library.db`  
✅ Database contains 3 authors, 5 books, 2 borrowers, 2 loans  
✅ Active loans report displays correctly (1 active, 1 returned)  
✅ No hand-edited generated code  
✅ Template reads like Python; Jinja2 is minimal