db.py

"""
This file is for initializing the database.
This file also provides the connection to the database.
The class for database schema is also defined here.
"""

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash
import os
import datetime

# initialize the serve 
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# initialize the database connection
db = SQLAlchemy(app)

"""
Definition for the table `user`, including the basic information for accounts
"""
class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password = db.Column(db.String(256), unique=False, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username


"""
Definition for the table `book`, including the basic information for books.
"""
class Books(db.Model):
    ISBN = db.Column(db.String(80), unique=True,
                     primary_key=True, nullable=False)
    bookname = db.Column(db.String(80), nullable=False)
    author = db.Column(db.String(80), nullable=False)
    public_date = db.Column(db.DateTime)
    description = db.Column(db.String(256), nullable=False)
    # picture url saves the file name of the book image
    picture_url = db.Column(db.String(256), nullable=False)
    trade_price = db.Column(db.Integer(), nullable=False)
    retail_price = db.Column(db.Integer(), nullable=False)
    quantity = db.Column(db.Integer(), nullable=False)

    def __repr__(self):
        return '<Book %r>' % self.bookname

"""
Definition of the table `item`. Item is the term in shopping cart.
"""
class Item(db.Model):
    item_id = db.Column(db.Integer(), primary_key=True,
                        unique=True, nullable=False)
    user_id = db.Column(db.Integer(),  nullable=False)
    ISBN = db.Column(db.String(80), nullable=False)
    quantity = db.Column(db.Integer(), nullable=False)
    bookname = db.Column(db.String(80), nullable=False)
    picture_url = db.Column(db.String(256), nullable=False)
    trade_price = db.Column(db.Integer(), nullable=False)

    def __repr__(self):
        return '<Item %r>' % self.item_id


def init_db():
    """
    Running this function would delete the original database and insert basic information.
    The inserted information includes three users and ten books.
    """
    if os.path.isfile("./database.db"):
        os.remove("./database.db")

    db.create_all()
    # add the test users
    admin = Users(username='admin',
                  password=generate_password_hash('p455w0rd'))
    customer1 = Users(username='customer1',
                      password=generate_password_hash('p455w0rd'))
    customer2 = Users(username='customer2',
                      password=generate_password_hash('p455w0rd'))
    book1 = Books(ISBN="9780593490594", bookname="Silverview", author="John le Carré", public_date=datetime.datetime(2021, 10, 12), description="......", picture_url="1.jfif", trade_price=28, retail_price=14, quantity=20)
    book2 = Books(ISBN="9781250145260", bookname="The Madness of Crowds (Chief Inspector Gamache Series #17)", author="Louise Penny", public_date=datetime.datetime(2021, 8, 24), description="......", picture_url="2.jfif", trade_price=28.99, retail_price=14.49, quantity=20)
    book3 = Books(ISBN="9780593490532", bookname = "The Man Who Died Twice (B&N Exclusive Edition) (Thursday Murder Club Series #2)", author="Richard Osman", public_date=datetime.datetime(2021, 9, 28), description="......", picture_url="3.jfif", trade_price=26, retail_price=19.99, quantity=20)
    book4 = Books(ISBN="9781250265586", bookname = "The Guilt Trip", author="Sandie Jones", public_date=datetime.datetime(2021, 8, 3), description="......", picture_url="4.jfif", trade_price=27.99, retail_price=19.99, quantity=20)
    book5 = Books(ISBN="9780063204669", bookname = "False Witness (Signed B&N Exclusive)", author="Karin Slaughter", public_date=datetime.datetime(2021, 7, 20), description="......", picture_url="5.jfif", trade_price=28.99, retail_price=19.99, quantity=20)
    book6 = Books(ISBN="9781250304452", bookname = "The Maidens", author="Alex Michaelides", public_date=datetime.datetime(2021, 6, 15), description="......", picture_url="6.jfif", trade_price=22.49, retail_price=19.99, quantity=20)
    book7 = Books(ISBN="9781250204479", bookname = "The Heron's Cry (Detective Matthew Venn Novel #2)", author="Ann Cleeves", public_date=datetime.datetime(2021, 9, 7), description="......", picture_url="7.jfif", trade_price=22.49, retail_price=19.99, quantity=20)
    book8 = Books(ISBN="9780385546577", bookname = "While Justice Sleeps", author="Stacey Abrams", public_date=datetime.datetime(2021, 5, 11), description="......", picture_url="8.jfif", trade_price=24.99, retail_price=19.99, quantity=20)
    book9 = Books(ISBN="9781631498619", bookname = "Mrs. March", author="Virginia Feito", public_date=datetime.datetime(2021, 8, 10), description="......", picture_url="9.jfif", trade_price=20.49, retail_price=19.99, quantity=20)
    book10 = Books(ISBN="9781641292498", bookname = "Clark and Division", author="Naomi Hirahara", public_date=datetime.datetime(2021, 8, 3), description="......", picture_url="10.jfif", trade_price=23.49, retail_price=19.99, quantity=20)
    db.session.add(admin)
    db.session.add(customer1)
    db.session.add(customer2)
    db.session.add(book1)
    db.session.add(book2)
    db.session.add(book3)
    db.session.add(book4)
    db.session.add(book5)
    db.session.add(book6)
    db.session.add(book7)
    db.session.add(book8)
    db.session.add(book9)
    db.session.add(book10)
    db.session.commit()


if __name__ == "__main__":
    init_db()