TODO App

The first mock application - a simple task management app that established SIMPLE_SQL's core patterns and fluent API design.

Domain Overview

The TODO app implements classic task management:

• Tasks - Items with title, description, status, due date
• Categories - Organize tasks into groups
• Filtering - By status, category, due date
• Ordering - By priority, due date, name

Schema

categories (id, name, color)
tasks (id, title, description, category_id, status, priority, due_date, created_at)

Patterns Established

Fluent Query Builder

The TODO app drove the design of SIMPLE_SQL's chainable query API:

-- Clean, readable query construction
result := db.select_from ("tasks")
    .columns ("id, title, status, due_date")
    .where ("status = ?", "pending")
    .order_by ("due_date ASC, priority DESC")
    .execute

Basic CRUD Operations

-- Create
db.insert_into ("tasks")
    .value ("title", "Buy groceries")
    .value ("category_id", 1)
    .value ("priority", 2)
    .execute

-- Read
task := db.select_from ("tasks").where ("id = ?", task_id).execute.first

-- Update
db.update ("tasks")
    .set ("status", "completed")
    .where ("id = ?", task_id)
    .execute

-- Delete
db.delete_from ("tasks").where ("id = ?", task_id).execute

Transaction Usage

-- Batch insert with transaction
db.begin_transaction
db.insert_into ("tasks").value ("title", "Task 1").execute
db.insert_into ("tasks").value ("title", "Task 2").execute
db.insert_into ("tasks").value ("title", "Task 3").execute
db.commit

Lessons Learned

Example: Task Manager Class

class TASK_MANAGER

feature -- Queries

    pending_tasks: SIMPLE_SQL_RESULT
        do
            Result := db.select_from ("tasks")
                .where ("status = 'pending'")
                .order_by ("due_date")
                .execute
        end

    tasks_due_today: SIMPLE_SQL_RESULT
        do
            Result := db.select_from ("tasks")
                .where ("due_date = date('now') AND status != 'completed'")
                .order_by ("priority DESC")
                .execute
        end

    tasks_in_category (a_category_id: INTEGER): SIMPLE_SQL_RESULT
        do
            Result := db.select_from ("tasks")
                .where ("category_id = ?", a_category_id)
                .order_by ("title")
                .execute
        end

feature -- Commands

    add_task (a_title: STRING; a_category: INTEGER; a_due: detachable DATE)
        local
            builder: SIMPLE_SQL_INSERT_BUILDER
        do
            builder := db.insert_into ("tasks")
                .value ("title", a_title)
                .value ("category_id", a_category)
                .value ("status", "pending")

            if attached a_due as due then
                builder.value ("due_date", due.formatted_out ("yyyy-mm-dd"))
            end

            builder.execute
        end

    complete_task (a_id: INTEGER)
        do
            db.update ("tasks")
                .set ("status", "completed")
                .where ("id = ?", a_id)
                .execute
        end

end

Source Code

• TODO_APP - Main application class
• TODO_REPOSITORY - Task CRUD operations
• TODO_ITEM - Task entity