exercises

"""
15 - Database: Exercises
Practice SQLite database operations.
"""

print("=" * 60)
print("DATABASE EXERCISES")
print("=" * 60)

import sqlite3
import os
import tempfile
from contextlib import contextmanager

# =============================================================================
# EXERCISE 1: Create Database Schema
# Create a database with tables for a library system:
# - books (id, title, author, isbn, available)
# - members (id, name, email, join_date)
# - loans (id, book_id, member_id, loan_date, return_date)
# =============================================================================
print("\n--- Exercise 1: Library Schema ---")

# Your code here:


# =============================================================================
# EXERCISE 2: CRUD Operations
# Implement functions: add_book, get_book, update_book, delete_book
# =============================================================================
print("\n--- Exercise 2: Book CRUD ---")

# Your code here:


# =============================================================================
# EXERCISE 3: Parameterized Queries
# Create a search function that safely searches books by title or author.
# =============================================================================
print("\n--- Exercise 3: Safe Search ---")

# Your code here:


# =============================================================================
# EXERCISE 4: Row Factory
# Fetch all books and return as list of dictionaries.
# =============================================================================
print("\n--- Exercise 4: Dict Results ---")

# Your code here:


# =============================================================================
# EXERCISE 5: Transactions
# Implement a loan_book function that:
# 1. Checks if book is available
# 2. Creates loan record
# 3. Marks book as unavailable
# All in one transaction.
# =============================================================================
print("\n--- Exercise 5: Transaction ---")

# Your code here:


# =============================================================================
# EXERCISE 6: Join Query
# Get all loans with book title and member name.
# =============================================================================
print("\n--- Exercise 6: Join Query ---")

# Your code here:


# =============================================================================
# EXERCISE 7: Aggregate Query
# Get statistics: total books, available books, books per author.
# =============================================================================
print("\n--- Exercise 7: Statistics ---")

# Your code here:


# =============================================================================
# EXERCISE 8: Database Class
# Create a reusable Database class with helper methods.
# =============================================================================
print("\n--- Exercise 8: Database Class ---")

# Your code here:


# =============================================================================
# SOLUTIONS
# =============================================================================
print("\n\n" + "=" * 60)
print("SOLUTIONS")
print("=" * 60)

# Create temporary database for solutions
DB_PATH = os.path.join(tempfile.gettempdir(), 'library.sqlite')

# Clean up
if os.path.exists(DB_PATH):
    os.remove(DB_PATH)

# SOLUTION 1
print("\n--- Solution 1: Library Schema ---")

def create_library_schema(db_path):
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS books (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                author TEXT NOT NULL,
                isbn TEXT UNIQUE,
                available INTEGER DEFAULT 1
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS loans (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                book_id INTEGER,
                member_id INTEGER,
                loan_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                return_date TIMESTAMP,
                FOREIGN KEY (book_id) REFERENCES books (id),
                FOREIGN KEY (member_id) REFERENCES members (id)
            )
        ''')
        
        conn.commit()

create_library_schema(DB_PATH)
print("Schema created!")

# SOLUTION 2
print("\n--- Solution 2: Book CRUD ---")

def add_book(db_path, title, author, isbn):
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO books (title, author, isbn) 
            VALUES (?, ?, ?)
        ''', (title, author, isbn))
        return cursor.lastrowid

def get_book(db_path, book_id):
    with sqlite3.connect(db_path) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM books WHERE id = ?', (book_id,))
        row = cursor.fetchone()
        return dict(row) if row else None

def update_book(db_path, book_id, **kwargs):
    if not kwargs:
        return 0
    
    with sqlite3.connect(db_path) as conn:
        set_clause = ', '.join(f"{k} = ?" for k in kwargs.keys())
        values = list(kwargs.values()) + [book_id]
        
        cursor = conn.cursor()
        cursor.execute(f'''
            UPDATE books SET {set_clause} WHERE id = ?
        ''', values)
        return cursor.rowcount

def delete_book(db_path, book_id):
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('DELETE FROM books WHERE id = ?', (book_id,))
        return cursor.rowcount

# Test CRUD
book_id = add_book(DB_PATH, "Python 101", "John Doe", "978-1234567890")
print(f"Added book: {get_book(DB_PATH, book_id)}")

update_book(DB_PATH, book_id, title="Python 102", author="John Doe Jr.")
print(f"Updated book: {get_book(DB_PATH, book_id)}")

# Add more books for later tests
add_book(DB_PATH, "Clean Code", "Robert Martin", "978-0132350884")
add_book(DB_PATH, "Design Patterns", "Gang of Four", "978-0201633610")

# SOLUTION 3
print("\n--- Solution 3: Safe Search ---")

def search_books(db_path, search_term):
    with sqlite3.connect(db_path) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # Use parameterized LIKE query
        pattern = f"%{search_term}%"
        cursor.execute('''
            SELECT * FROM books 
            WHERE title LIKE ? OR author LIKE ?
        ''', (pattern, pattern))
        
        return [dict(row) for row in cursor.fetchall()]

results = search_books(DB_PATH, "Python")
print(f"Search 'Python': {results}")

results = search_books(DB_PATH, "Robert")
print(f"Search 'Robert': {results}")

# SOLUTION 4
print("\n--- Solution 4: Dict Results ---")

def get_all_books(db_path):
    with sqlite3.connect(db_path) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM books')
        return [dict(row) for row in cursor.fetchall()]

books = get_all_books(DB_PATH)
print("All books:")
for book in books:
    print(f"  {book}")

# SOLUTION 5
print("\n--- Solution 5: Transaction ---")

def add_member(db_path, name, email):
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO members (name, email) VALUES (?, ?)
        ''', (name, email))
        return cursor.lastrowid

def loan_book(db_path, book_id, member_id):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # Check availability
        cursor.execute('SELECT available FROM books WHERE id = ?', (book_id,))
        row = cursor.fetchone()
        
        if not row:
            raise ValueError("Book not found")
        if not row[0]:
            raise ValueError("Book not available")
        
        # Create loan
        cursor.execute('''
            INSERT INTO loans (book_id, member_id) VALUES (?, ?)
        ''', (book_id, member_id))
        
        # Mark unavailable
        cursor.execute('''
            UPDATE books SET available = 0 WHERE id = ?
        ''', (book_id,))
        
        conn.commit()
        return True
        
    except Exception as e:
        conn.rollback()
        print(f"Loan failed: {e}")
        return False
    finally:
        conn.close()

# Add member and loan book
member_id = add_member(DB_PATH, "Alice", "alice@example.com")
result = loan_book(DB_PATH, 1, member_id)
print(f"Loan result: {result}")

# Try loaning same book again
result = loan_book(DB_PATH, 1, member_id)
print(f"Second loan attempt: {result}")

# SOLUTION 6
print("\n--- Solution 6: Join Query ---")

def get_loans_with_details(db_path):
    with sqlite3.connect(db_path) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT 
                loans.id as loan_id,
                books.title as book_title,
                members.name as member_name,
                loans.loan_date,
                loans.return_date
            FROM loans
            INNER JOIN books ON loans.book_id = books.id
            INNER JOIN members ON loans.member_id = members.id
        ''')
        
        return [dict(row) for row in cursor.fetchall()]

loans = get_loans_with_details(DB_PATH)
print("Loans:")
for loan in loans:
    print(f"  {loan['member_name']} borrowed '{loan['book_title']}' on {loan['loan_date']}")

# SOLUTION 7
print("\n--- Solution 7: Statistics ---")

def get_library_stats(db_path):
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        
        stats = {}
        
        # Total books
        cursor.execute('SELECT COUNT(*) FROM books')
        stats['total_books'] = cursor.fetchone()[0]
        
        # Available books
        cursor.execute('SELECT COUNT(*) FROM books WHERE available = 1')
        stats['available_books'] = cursor.fetchone()[0]
        
        # Books per author
        cursor.execute('''
            SELECT author, COUNT(*) as count 
            FROM books 
            GROUP BY author
        ''')
        stats['books_per_author'] = {row[0]: row[1] for row in cursor.fetchall()}
        
        return stats

stats = get_library_stats(DB_PATH)
print(f"Library statistics: {stats}")

# SOLUTION 8
print("\n--- Solution 8: Database Class ---")

class LibraryDB:
    def __init__(self, db_path):
        self.db_path = db_path
    
    @contextmanager
    def get_connection(self):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()
    
    def query(self, sql, params=()):
        with self.get_connection() as conn:
            return [dict(row) for row in conn.execute(sql, params)]
    
    def execute(self, sql, params=()):
        with self.get_connection() as conn:
            cursor = conn.execute(sql, params)
            return cursor.lastrowid
    
    def get_books(self, available_only=False):
        sql = 'SELECT * FROM books'
        if available_only:
            sql += ' WHERE available = 1'
        return self.query(sql)
    
    def get_book_by_id(self, book_id):
        result = self.query('SELECT * FROM books WHERE id = ?', (book_id,))
        return result[0] if result else None

# Use class
library = LibraryDB(DB_PATH)

print("All books via class:")
for book in library.get_books():
    status = "Available" if book['available'] else "On loan"
    print(f"  {book['title']} - {status}")

print("\nAvailable books only:")
for book in library.get_books(available_only=True):
    print(f"  {book['title']}")

# Cleanup
os.remove(DB_PATH)
print("\n(Cleaned up temporary database)")

print("\n" + "=" * 60)
print("END OF EXERCISES")
print("=" * 60)