# Library Generator System

A template-based code generator using DBML and Jinja2 to automatically generate Python SQLite code.

## Overview

This project demonstrates the **generator-coding pattern**:

```
DBML Data Model → Parser → Helpers → Jinja2 Template → Generated Python Code
```

The system:
- Defines database schema in **DBML** (Domain-Specific Language)
- Parses DBML using Python helpers
- Renders a **Jinja2 template** to generate Python SQLite code
- Produces a complete, working library management system with sample data

## Project Structure

```
project/
├── data/
│   └── library.dbml                    # ✏️  DBML data model (input)
├── generator/
│   ├── helpers.py                      # Parser & helpers
│   └── generate_library.py             # Generator driver script
├── templates/
│   └── library_schema.py.jinja2       # Jinja2 template
├── generated/
│   └── library_schema.py               # 🤖 Generated code (output, git-ignored)
├── library.py                          # Main program
└── PLAN.md                             # Implementation plan
```

## Quick Start

### 1. Install Dependencies

```bash
pip install jinja2
```

### 2. Generate the Schema Module

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

**Output:**
- Parses `data/library.dbml`
- Applies helpers from `generator/helpers.py`
- Renders `templates/library_schema.py.jinja2`
- Writes `generated/library_schema.py`

### 3. Run the Library System

```bash
python library.py
```

**Output:**
- Creates `library.db` SQLite database
- Inserts sample data:
  - 3 authors (George Orwell, Harper Lee, J.K. Rowling)
  - 5 books
  - 2 borrowers
  - 2 loans (1 active, 1 returned)
- Prints active loans report

## Database Schema

### Authors
```
authors(id, name, birth_year)
```
- George Orwell (born 1903)
- Harper Lee (born 1926)
- J.K. Rowling (born 1965)

### Books
```
books(id, title, isbn, author_id FK)
```
- 5 books with ISBNs and author references

### Borrowers
```
borrowers(id, name, email)
```
- Alice Johnson (alice@example.com)
- Bob Smith (bob@example.com)

### Loans
```
loans(id, book_id FK, borrower_id FK, loan_date, return_date nullable)
```
- Loan 1: 1984 by Alice Johnson (returned 2026-10-20)
- Loan 2: To Kill a Mockingbird by Bob Smith (active, since 2026-10-17)

## Generator Components

### 1. Data Model: `data/library.dbml`

Defines 4 tables with relationships in DBML format:

```dbml
Table authors {
  id integer [primary key]
  name string [not null]
  birth_year integer
}

Table books {
  id integer [primary key]
  title string [not null]
  isbn string
  author_id integer [not null, ref: > authors.id]
}
```

**Key Point:** When you change the schema, modify this file and regenerate.

### 2. Parser & Helpers: `generator/helpers.py`

```python
def parse_dbml(filepath: str) -> Dict[str, Any]
```
Parses DBML file and extracts:
- Table names and fields
- Field types and constraints
- Foreign key relationships

**Helper Functions:**
- `get_python_type(dbml_type)` — Convert DBML types to Python types
- `get_sqlite_type(dbml_type)` — Convert to SQLite types
- `get_sample_data()` — Returns 3 authors, 5 books, 2 borrowers, 2 loans

### 3. Template: `templates/library_schema.py.jinja2`

Jinja2 template that generates:
- `init_database()` function — Creates tables
- `insert_sample_data()` function — Inserts sample data
- `get_active_loans()` function — Queries active loans
- `print_active_loans_report()` function — Displays report
- `print_loan_history_report()` function — Shows all loans

**Template Features:**
- Loop over tables to generate CREATE TABLE statements
- Proper SQL constraint handling
- Parameterized INSERT queries
- JOIN queries for reporting

### 4. Generator Driver: `generator/generate_library.py`

Orchestrates the pipeline:

1. **Parse:** Reads `data/library.dbml`
2. **Prepare:** Gets sample data from helpers
3. **Configure:** Sets up Jinja2 environment with:
   - `trim_blocks=True` — Remove trailing whitespace
   - `lstrip_blocks=True` — Remove leading whitespace
   - `StrictUndefined` — Fail on missing variables
4. **Render:** Fills template with parsed data
5. **Write:** Saves to `generated/library_schema.py`
6. **Format:** Runs `black` formatter (if available)

### 5. Main Program: `library.py`

Uses the generated module:

```python
from generated.library_schema import init_database, insert_sample_data
from generator.helpers import get_sample_data

# Initialize database
conn = init_database("library.db")

# Insert sample data
sample_data = get_sample_data()
insert_sample_data(conn, sample_data)

# Print reports
print_active_loans_report(conn)
```

## Workflow

### To Modify the Schema

1. Edit `data/library.dbml` (add/remove tables or fields)
2. Run `python generator/generate_library.py` to regenerate
3. Run `python library.py` to verify

**Key Rule:** Never edit `generated/library_schema.py` — it will be overwritten on regeneration.

### To Add More Sample Data

Edit `get_sample_data()` in `generator/helpers.py`:

```python
def get_sample_data() -> Dict[str, List[Dict[str, Any]]]:
    authors = [
        {'id': 1, 'name': 'New Author', 'birth_year': 2000},
        ...
    ]
    ...
```

Then regenerate and run.

### To Customize the Generated Code

Edit the Jinja2 template `templates/library_schema.py.jinja2`:

```jinja2
{%- for table in tables %}
    cursor.execute("CREATE TABLE {{ table.name }} ...")
{%- endfor %}
```

Then regenerate to apply changes.

## Generator-Coding Rules Applied

| Rule | Implementation |
|------|----------------|
| **Never edit generated output** | `generated/library_schema.py` is ignored in `.gitignore` |
| **Interface changes in data model** | New table/field → modify `data/library.dbml` |
| **Data reshaping in helpers** | Type conversions, sample data in `generator/helpers.py` |
| **Templates read like target code** | `library_schema.py.jinja2` is mostly Python with thin Jinja2 layer |
| **One concern per template** | Single template generates complete schema module |
| **Control whitespace explicitly** | Uses `trim_blocks`, `lstrip_blocks`, `{%-`, `-%}` |

## Testing

Run the generator and main program:

```bash
# Generate code
python generator/generate_library.py

# Verify output is valid Python
python -m py_compile generated/library_schema.py

# Run main program
python library.py

# Verify database contents
sqlite3 library.db "SELECT COUNT(*) FROM books;"
```

## Example Output

```
======================================================================
                      LIBRARY MANAGEMENT SYSTEM                       
======================================================================

Step 1: Initializing database...
  ✓ Database created: library.db

Step 2: Preparing sample data...
  ✓ Loaded 3 authors
  ✓ Loaded 5 books
  ✓ Loaded 2 borrowers
  ✓ Loaded 2 loans

Step 3: Inserting sample data...
  ✓ Sample data inserted successfully

Step 4: Generating reports...

======================================================================
                         ACTIVE LOANS REPORT                          
======================================================================

Loan ID: 2
  Book: To Kill a Mockingbird
  Borrower: Bob Smith
  Loan Date: 2026-10-17

======================================================================
```

## Files Reference

| File | Type | Purpose | Editable? |
|------|------|---------|-----------|
| `data/library.dbml` | Input | Database schema in DBML | ✅ Yes |
| `generator/helpers.py` | Code | Parser & helpers | ✅ Yes |
| `templates/library_schema.py.jinja2` | Template | Output template | ✅ Yes |
| `generator/generate_library.py` | Script | Generator driver | ✅ Yes |
| `generated/library_schema.py` | Output | Generated code | ❌ No |
| `library.py` | Program | Main application | ✅ Yes |

## Extending the System

### Add a New Table

1. Edit `data/library.dbml`:
   ```dbml
   Table reviews {
     id integer [primary key]
     book_id integer [ref: > books.id]
     rating integer [not null]
   }
   ```

2. Regenerate:
   ```bash
   python generator/generate_library.py
   ```

3. The template automatically generates CREATE TABLE for new tables.

### Add Helper Functions

Edit `generator/helpers.py` and add functions like:
```python
def get_books_by_author(conn, author_id):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM books WHERE author_id = ?", (author_id,))
    return cursor.fetchall()
```

Then import and use in `library.py`.

### Generate for a Different Database

Fork the template and helpers for PostgreSQL, MySQL, etc.:
- Create `templates/library_schema_postgres.sql.jinja2`
- Update `generator/generate_library.py` to support multiple targets

## Dependencies

- **Python 3.8+**
- **Jinja2** — Template engine
- **sqlite3** — Built-in, no installation needed
- **black** (optional) — Code formatter

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

## Summary

This project demonstrates:

✅ **DBML as data model** — Clear, maintainable schema definition  
✅ **Jinja2 templating** — Flexible code generation  
✅ **Separation of concerns** — Hand-written vs. generated code  
✅ **Pipeline architecture** — Parse → Helper → Template → Output  
✅ **Never edit generated output** — Source of truth is the data model  
✅ **Sample data included** — Ready-to-run demo  
✅ **Type conversion helpers** — Automatic DBML → SQLite/Python mapping  

Use this pattern for:
- Database schema generation (SQLAlchemy models, migrations, etc.)
- API client/server code generation
- Register map generation for hardware
- Configuration file generation
- Any repetitive, specification-driven code