Welcome to the interactive lecture session for INFO 2602 week 2.

In this codelab you will reinforce the content learned this week in both the Lab and Lecture sessions. Namely:

• Modeling based on a scenario
• Mapping relationships between entities
• Developing software to achieve functionality

In today's scenario, we'd see how we can use the knowledge we gained this week to build out a simple application. We'd be building a sample library rental application.

We would be using the same workspace from the Week 2 Lab as a base for this session.

Who are the main users that would interact with an app like this?

What are some of the features of the application you think would be good for a library rental application?

Potential Stakeholders

These are all of the entities that would interact with an application like this

1. Customers
2. Librarians
3. Application Administrator

Models

Users

Book

Student

Rental

Functionality (Listing from in class)

1. Should be able to enter books in the system
2. Should be able to get a report on the list of books that has been rented
3. Should be able to get a report on the list of books that are available
4. Should be able to rent a book to a student

Below contains the in-class code for the models.py file

from sqlmodel import Field, SQLModel
from typing import Optional
from pwdlib import PasswordHash
from datetime import datetime

password_hash = PasswordHash.recommended()

class User(SQLModel, table=True):
    id: Optional[int] =  Field(default=None, primary_key=True)
    fname:str
    lname:str
    email:str = Field(index=True, unique=True)
    password:str

class Book(SQLModel, table=True):
    isbn: str = Field(primary_key=True)
    name: str
    is_rented: bool = False

class Student(SQLModel, table=True):
    id: int = Field(primary_key=True)
    fname:str
    lname:str
    email:str = Field(index=True, unique=True)

class Rental(SQLModel, table=True):
    id: int|None = Field(primary_key=True)
    student_id: int = Field(foreign_key="student.id")
    book_id: str = Field(foreign_key="book.isbn")
    date_borrowed: datetime
    date_returned: Optional[datetime]

Below contains the code for the cli.py file

import typer
from app.database import create_db_and_tables, get_session, drop_all
from app.models import User, Book, Student, Rental
from fastapi import Depends
from sqlmodel import select
from sqlalchemy.exc import IntegrityError
from datetime import datetime

cli = typer.Typer()

@cli.command()
def initialize():
    with get_session() as db: # Get a connection to the database
        drop_all() # delete all tables
        create_db_and_tables() #recreate all tables

        # Populate with sample books and students
        db.add_all([
            Book(
                isbn=f"a1b2{i}", 
                name=f"Book {i}"
            ) for i in range(10)
        ])
        db.add_all([
            Student(
                id=816123000+i, 
                fname=f"Student {i}", 
                lname=f"Smith{i}", 
                email=f"student{i}@mail.com"
            ) for i in range(10)
        ])
        db.commit()
        print("Database Initialized")

@cli.command()
def create_book(isbn:str, name:str, ):
    with get_session() as db:
        book = Book(isbn=isbn, name=name)
        db.add(book)
        db.commit()
        print("Book created successfully")

@cli.command()
def list_rented_books():
    with get_session() as db:
        all_rented_books = db.exec(select(Book).where(Book.is_rented == True)).all()
        print(all_rented_books)

@cli.command()
def list_unrented_books():
    with get_session() as db:
        all_unrented_books = db.exec(select(Book).where(Book.is_rented == False)).all()
        print(all_unrented_books)

@cli.command()
def rent_book(isbn:str, student_id:int):
    with get_session() as db:
        book = db.exec(select(Book).where(Book.isbn == isbn, Book.is_rented==False)).one_or_none()
        if not book:
            print("Invalid book. Or book is rented")
            return
        
        student = db.exec(select(Student).where(Student.id == student_id)).one_or_none()
        if not student:
            print("Invalid student")
            return

        book.is_rented = True
        rental_date = datetime.now()

        rental_rec = Rental(student_id = student_id, book_id=book.isbn, date_borrowed=rental_date)
        db.add_all([rental_rec, book])
        db.commit()
        print("book checked out successfully")

if __name__ == "__main__":
    cli()