Select an item from the menu.
Hi, I’m Daniel, and as you can probably tell from this portfolio page, I’m not primarily a front-end person. In a professional setting, this means that while my profile might not be the most appealing you'll come across, if we look past the usual checkboxes and assumptions, you’ll find someone who’s not just capable but also genuine, reliable, and ready to prove his worth.
🚀 Work in Progress
Check out my Library project
This project was all about learning how to integrate a database into a web application. Having a data analysis background,
I was comfortable querying data, but leveling up to writing procedures that generate them was a whole new challenge.
On top of that, I made the switch from Oracle to T-SQL, which added an extra layer of learning.
Takeaways:
✅ Leveled up from querying data to structuring and managing it.
✅ Learned how Python and SQL work together in a web environment.
Next Improvements:
🔹 Add functionality for "Forgot Password" obj.
🔹 Adding new features while refining database architecture.
- ← Back
- ERD
- Tables
- status_dictionary
- books
- users
- transactions
- transactions_audit
- Procedures
- create_user
- borrow_book
- extend_borrow
- return_book
- ← Back
- SignUp
- LogIn
- Library
- Borrow
- Extend
- Return
SELECT something FROM tabs;
CREATE TABLE status_dictionary (
status_type NVARCHAR(50),
status_code INT,
status_description NVARCHAR(100),
CONSTRAINT PK_Status PRIMARY KEY (status_type, status_code)
);
GO
INSERT INTO status_dictionary
VALUES
('user_status', 0, 'Closed'),
('user_status', 1, 'Active'),
('trx_status', 0, 'Return'),
('trx_status', 1, 'Borrow'),
('trx_status', 3, 'Extend'),
('trx_status', 5, 'Overdue'),
('audit_status', 1, 'Success'),
('audit_status', 0, 'Failure'),
('book_status', 1, 'Active'),
('book_status', 0, 'Inactive');
GO
CREATE TABLE books (
book_id INT IDENTITY PRIMARY KEY,
book_title NVARCHAR(150),
book_author NVARCHAR(50),
samples INT,
book_status INT DEFAULT 1,
created_at DATETIME2 DEFAULT GETDATE(),
updated_at DATETIME2 DEFAULT GETDATE()
);
GO
CREATE TABLE users (
user_id INT IDENTITY PRIMARY KEY,
user_name NVARCHAR(50) NOT NULL UNIQUE,
user_email NVARCHAR(150) NOT NULL UNIQUE,
user_password NVARCHAR(255) NOT NULL,
user_status BIT DEFAULT 1, -- 1 (Active) | 0 (Closed)
start_date DATETIME2 DEFAULT GETDATE(),
end_date DATETIME2 DEFAULT '9999-12-31'
);
GO
CREATE TABLE transactions (
trx_id INT IDENTITY PRIMARY KEY,
user_id INT REFERENCES users(user_id),
book_id INT REFERENCES books(book_id),
borrow_date DATETIME2 DEFAULT GETDATE(),
return_date DATETIME2 DEFAULT (DATEADD(DAY, 14, GETDATE())),
trx_status INT DEFAULT 1,
ext_count INT DEFAULT 0,
start_date DATETIME2 DEFAULT GETDATE(),
end_date DATETIME2 DEFAULT '9999-12-31',
updated_at DATETIME2 DEFAULT GETDATE(),
CONSTRAINT CHK_TrxStatus CHECK (trx_status IN (0, 1, 3, 5)) -- Return | Borrow | Extend | Overdue
);
GO
-- Filtered Unique Index for Active Transactions - enforces correctness
CREATE UNIQUE INDEX ux_active_trx_per_user_book
ON transactions(user_id, book_id)
WHERE trx_status = 1;
GO
-- Indexing
CREATE NONCLUSTERED INDEX ix_user_id ON transactions(user_id);
CREATE NONCLUSTERED INDEX ix_book_id ON transactions(book_id);
CREATE TABLE transactions_audit (
audit_id INT IDENTITY PRIMARY KEY,
trx_id INT NULL,
user_id INT,
book_id INT,
borrow_date DATETIME2 DEFAULT NULL,
return_date DATETIME2 DEFAULT NULL,
trx_status INT,
ext_count INT DEFAULT NULL,
start_date DATETIME2 DEFAULT GETDATE(),
end_date DATETIME2 DEFAULT '9999-12-31',
audit_action NVARCHAR(50) DEFAULT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
audit_status BIT DEFAULT 1, -- 1 = success | 0 = fail
audit_status_desc NVARCHAR(255) DEFAULT NULL,
audit_timestamp DATETIME2 DEFAULT GETDATE()
);
CREATE NONCLUSTERED INDEX ix_trx_id ON transactions_audit(trx_id);
CREATE PROCEDURE create_user
@user_name NVARCHAR(50),
@user_email NVARCHAR(150),
@user_password NVARCHAR(256) /* Hashed in Python */
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM users WHERE user_email = @user_email AND end_date = '9999-12-31' AND user_status = 1)
THROW 50001, 'Email is already registered', 1;
IF EXISTS (SELECT 1 FROM users WHERE user_name = @user_name AND end_date = '9999-12-31' AND user_status = 1)
THROW 50002, 'Username is already registered', 1;
INSERT INTO users (user_name, user_email, user_password)
VALUES (@user_name, @user_email, @user_password);
COMMIT ;
END TRY
BEGIN CATCH
ROLLBACK;
THROW;
END CATCH;
END;
GO
/*
There are some other validations in Python:
if not user_name or len(user_name) < 3:
errors.append("Username must be at least 3 characters long.")
if not email or not re.match(r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$", email):
errors.append("Invalid email format.")
if not raw_password or len(raw_password) < 5:
errors.append("Password must be at least 5 characters long.")
*/
CREATE PROCEDURE borrow_book
@user_id INT,
@book_id INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@stock INT,
@active_borrow_count INT;
BEGIN TRY
BEGIN TRANSACTION;
SELECT @stock = samples
FROM books WITH (UPDLOCK, HOLDLOCK)
WHERE book_id = @book_id;
SET @active_borrow_count = (SELECT COUNT(*) FROM transactions WHERE user_id = @user_id AND trx_status <> 0 AND end_date = '9999-12-31');
IF @active_borrow_count >= 3
THROW 50003, 'You can''t borrow more than 3 books.', 1;
IF EXISTS (SELECT 1 FROM transactions WHERE book_id = @book_id AND user_id = @user_id AND trx_status <> 0 AND end_date = '9999-12-31')
THROW 50004, 'You can''t borrow the same book multiple times.', 1;
IF @stock = 0
THROW 50005, 'This book is not available in stock right now.', 1;
INSERT INTO transactions (user_id, book_id)
OUTPUT inserted.trx_id, inserted.user_id, inserted.book_id, inserted.borrow_date, inserted.return_date, inserted.trx_status, inserted.ext_count, inserted.start_date, inserted.end_date, 'INSERT', 1, 'Borrow'
INTO transactions_audit (trx_id, user_id, book_id, borrow_date, return_date, trx_status, ext_count, start_date, end_date, audit_action, audit_status, audit_status_desc)
VALUES (@user_id, @book_id);
UPDATE books
SET samples = samples - 1
WHERE
book_id = @book_id;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
INSERT INTO transactions_audit (user_id, book_id, trx_status, audit_action, audit_status, audit_status_desc)
VALUES (@user_id, @book_id, 1, 'INSERT', 0, ERROR_MESSAGE());
THROW;
END CATCH
END;
CREATE PROCEDURE extend_borrow
@user_id INT,
@book_id INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@v_trx_id INT,
@v_max_extend DATETIME2,
@v_return_date DATETIME2,
@v_trx_status INT;
BEGIN TRY
BEGIN TRANSACTION;
SELECT
@v_trx_id = trx_id,
@v_max_extend = DATEADD(day, 35, borrow_date),
@v_trx_status = trx_status,
@v_return_date = DATEADD(day, 7, return_date)
FROM transactions WITH (UPDLOCK, HOLDLOCK)
WHERE
user_id = @user_id
AND book_id = @book_id
AND trx_status <> 0
AND ext_count < 3
AND end_date = '9999-12-31';
IF @v_trx_id IS NULL
THROW 50006, 'You can''t extend more than 3 times.', 1;
IF @v_max_extend < DATEADD(day, 7, GETDATE())
THROW 50007, 'You have exceeded the maximum allowable extension period.', 1;
UPDATE transactions_audit
SET end_date = GETDATE()
WHERE
trx_id = @v_trx_id
AND end_date = '9999-12-31';
UPDATE transactions
SET
trx_status = 3,
ext_count = ext_count + 1,
return_date = CASE
WHEN @v_trx_status = 5 THEN DATEADD(day, 7, GETDATE())
ELSE @v_return_date
END,
updated_at = GETDATE()
OUTPUT @v_trx_id, inserted.user_id, inserted.book_id, inserted.borrow_date, inserted.return_date, inserted.trx_status, inserted.ext_count, inserted.end_date, 'UPDATE', 'Extend'
INTO transactions_audit (trx_id, user_id, book_id, borrow_date, return_date, trx_status, ext_count, end_date, audit_action, audit_status_desc)
WHERE
trx_id = @v_trx_id;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
INSERT INTO transactions_audit (user_id, book_id, trx_status, audit_action, audit_status, audit_status_desc)
VALUES (@user_id, @book_id, 3, 'UPDATE', 0, ERROR_MESSAGE());
THROW;
END CATCH
END;
GO
CREATE PROCEDURE return_book
@user_id INT,
@book_id INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@v_trx_id INT,
@v_borrow_date DATETIME2,
@v_ext_count INT;
BEGIN TRY
BEGIN TRANSACTION;
SELECT @v_trx_id = trx_id, @v_borrow_date = borrow_date, @v_ext_count = ext_count
FROM transactions
WHERE
user_id = @user_id
AND book_id = @book_id
AND trx_status <> 0
AND end_date = '9999-12-31';
UPDATE transactions
SET end_date = GETDATE(), updated_at = GETDATE()
OUTPUT @v_trx_id, inserted.user_id, inserted.book_id, inserted.borrow_date, inserted.return_date, inserted.trx_status, inserted.ext_count, inserted.end_date, 'UPDATE', 'Close active row'
INTO transactions_audit (trx_id, user_id, book_id, borrow_date, return_date, trx_status, ext_count, end_date, audit_action, audit_status_desc)
WHERE
trx_id = @v_trx_id;
INSERT INTO transactions (user_id, book_id, borrow_date, return_date, trx_status, ext_count, start_date)
OUTPUT inserted.trx_id, inserted.user_id, inserted.book_id, inserted.borrow_date, inserted.return_date, inserted.trx_status, inserted.ext_count, inserted.start_date, inserted.end_date, 'INSERT', 1, 'Return'
INTO transactions_audit (trx_id, user_id, book_id, borrow_date, return_date, trx_status, ext_count, start_date, end_date, audit_action, audit_status, audit_status_desc)
VALUES (@user_id, @book_id, @v_borrow_date, GETDATE(), 0, @v_ext_count, GETDATE());
UPDATE books
SET samples = samples + 1
WHERE
book_id = @book_id;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
INSERT INTO transactions_audit (user_id, book_id, trx_status, audit_action, audit_status, audit_status_desc)
VALUES (@user_id, @book_id, 0, 'INSERT', 0, ERROR_MESSAGE());
THROW;
END CATCH
END;
GO
print('Select a view from the menu.')
def sign_up(request):
if request.method == 'POST':
try:
data = json.loads(request.body)
user_name = data.get('username', '').strip()
email = data.get('email', '').strip()
raw_password = data.get('password1', '').strip()
errors=[]
if not user_name or len(user_name) < 3:
errors.append("Username must be at least 3 characters long.")
if not email or not re.match(r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$", email):
errors.append("Invalid email format.")
if not raw_password or len(raw_password) < 5:
errors.append("Password must be at least 5 characters long.")
if errors:
return JsonResponse({'success': False, 'errors': errors}, status=400)
password = make_password(raw_password)
with connection.cursor() as cursor:
try:
cursor.execute("EXEC create_user @user_name=%s, @user_email=%s, @user_password=%s",
[user_name, email, password])
except DatabaseError as e:
error_message = str(e)
if "Email is already registered" in error_message:
return JsonResponse({'success': False, 'errors': ["Email is already registered."]}, status=400)
if "Username is already registered" in error_message:
return JsonResponse({'success': False, 'errors': ["Username is already registered."]}, status=400)
return JsonResponse({'success': True, 'message': 'User created successfully'})
except json.JSONDecodeError:
return JsonResponse({'success': False, 'errors': ["Invalid JSON format."]}, status=400)
except Exception as e:
return JsonResponse({'success': False, 'errors': ["Something went wrong."]}, status=500)
return JsonResponse({'succes':False, 'errors': ['Invalid request']}, status=400)
def log_in(request):
if request.method == 'POST':
try:
data = json.loads(request.body)
user_name = data.get('username', '').strip()
password = data.get('password', '').strip()
errors = []
if not user_name:
errors.append("Username is required.")
if not password:
errors.append("Password is required.")
if errors:
return JsonResponse({'success': False, 'errors': errors}, status=400)
with connection.cursor() as cursor:
cursor.execute("SELECT user_id, user_password FROM Users WHERE CAST(end_date as DATE) = CAST('9999-12-31' as DATE) AND user_name = %s", [user_name])
user = cursor.fetchone() # Fetch single row
if user is None:
return JsonResponse({'success': False, 'errors': ["Invalid username or password."]}, status=400)
user_id = user[0]
stored_hashed_password = user[1] # Extract password from query result
if not check_password(password, stored_hashed_password):
return JsonResponse({'success': False, 'errors': ["Invalid username or password."]}, status=400)
request.session['user_id'] = user_id
request.session['username'] = user_name
return JsonResponse({'success': True, 'redirect_url': '/library/app/'})
except Exception as e:
return JsonResponse({'success': False, 'errors': ["Something went wrong."]}, status=500)
return JsonResponse({'success': False, 'errors': ['Invalid request']}, status=400)
def library_view(request):
user_id = request.session.get('user_id')
user_name = request.session.get('username')
if not user_id:
return JsonResponse({'success': False, 'message': 'User not logged in'}, status=401)
# Get borrowed books
with connection.cursor() as cursor:
cursor.execute("SELECT a.book_id, b.book_title, b.book_author, a.borrow_date, a.return_date "
"FROM transactions a "
"LEFT JOIN books b ON a.book_id=b.book_id "
"WHERE a.trx_status <> 0 AND CAST(a.end_date AS DATE) = CAST('9999-12-31' AS DATE) AND a.user_id = %s", [user_id])
books = cursor.fetchall()
borrowed_books = []
for book in books:
book_data = {
'book_id': book[0],
'book_img': re.sub(r'[^a-zA-Z0-9]', '', book[1]).lower(),
'book_title': book[1],
'book_author': book[2],
'borrow_date': book[3],
'return_date': book[4]
}
borrowed_books.append(book_data)
with connection.cursor() as cursor:
cursor.execute("SELECT book_id, book_title, book_author, samples FROM books")
lib_books = cursor.fetchall()
stock = []
for book in lib_books:
book_data = {
'book_id': book[0],
'book_img': re.sub(r'[^a-zA-Z0-9]', '', book[1]).lower(),
'book_title': book[1],
'book_author': book[2],
'samples': book[3]
}
stock.append(book_data)
random.shuffle(stock)
return render(request, 'app.html', {
'username': user_name,
'borrowed_books': borrowed_books,
'stock': stock
})
def borrow_book(request):
if request.method == 'POST':
try:
data = json.loads(request.body)
book_id = data.get('book_id')
user_id = request.session.get('user_id')
if not user_id or not book_id:
return JsonResponse({'success': False, 'message': 'Invalid request'}, status=400)
with connection.cursor() as cursor:
try:
cursor.execute("EXEC borrow_book @user_id=%s, @book_id=%s", [user_id, book_id])
cursor.execute("SELECT a.book_id, b.book_title, b.book_author, a.borrow_date, a.return_date "
"FROM transactions a "
"LEFT JOIN books b ON a.book_id=b.book_id "
"WHERE a.trx_status <> 0 AND CAST(end_date as DATE) = CAST('9999-12-31' as DATE) AND a.user_id = %s", [user_id])
borrowed_books = [
{
'book_id': row[0],
'book_img': re.sub(r'[^a-zA-Z0-9]', '', row[1]).lower(),
'book_title': row[1],
'book_author': row[2],
'borrow_date': row[3],
'return_date': row[4]
}
for row in cursor.fetchall()
]
except DatabaseError as e:
error_message = str(e)
if "multiple times" in error_message:
return JsonResponse({'success': False, 'errors': ["You can't borrow the same book multiple times."]}, status=400)
elif "not available" in error_message:
return JsonResponse({'success': False, 'errors': ["This book is not available in stock right now."]}, status=400)
elif "3 books" in error_message:
return JsonResponse({'success': False, 'errors': ["You can't borrow more than 3 books."]}, status=400)
else:
return JsonResponse({'success': False, 'errors': [error_message]}, status=400)
return JsonResponse({'success': True, 'message': "You borrowed a new book. Enjoy reading!", 'borrowed_books':borrowed_books, 'book_id':book_id})
except:
return JsonResponse({'success': False, 'message': 'Return Error'}, status=400)
def extend_book(request):
if request.method == 'POST':
try:
data = json.loads(request.body)
book_id = data.get('book_id')
user_id = request.session.get('user_id')
if not user_id or not book_id:
return JsonResponse({'success': False, 'message': 'Invalid request'}, status=400)
with connection.cursor() as cursor:
try:
cursor.execute("EXEC extend_borrow @user_id=%s, @book_id=%s", [user_id, book_id])
cursor.execute("SELECT return_date FROM transactions WHERE trx_status <> 0 AND CAST(end_date as DATE) = CAST('9999-12-31' as DATE) AND user_id=%s AND book_id=%s", [user_id, book_id])
new_return_date = cursor.fetchone()[0]
except DatabaseError as e:
error_message = str(e)
if "You can't extend more than 3 times." in error_message:
return JsonResponse({'success': False, 'errors': ["You can't extend more than 3 times."]}, status=400)
return JsonResponse({'success': False, 'errors': [error_message]}, status=400)
return JsonResponse({'success': True, 'message': "You've extended the period by 7 more days.", 'new_return_date': new_return_date})
except:
return JsonResponse({'success': False, 'message': 'Extend Error'}, status=400)
def return_book(request):
if request.method == 'POST':
try:
data = json.loads(request.body)
book_id = data.get('book_id')
user_id = request.session.get('user_id')
if not user_id or not book_id:
return JsonResponse({'success': False, 'message': 'Invalid request'}, status=400)
with connection.cursor() as cursor:
try:
cursor.execute("EXEC return_book @user_id=%s, @book_id=%s", [user_id, book_id])
cursor.execute("SELECT a.book_id, b.book_title, b.book_author, a.borrow_date, a.return_date "
"FROM transactions a "
"LEFT JOIN books b ON a.book_id=b.book_id "
"WHERE a.trx_status <> 0 AND CAST(end_date as DATE) = CAST('9999-12-31' as DATE) AND a.user_id = %s", [user_id])
borrowed_books = [
{
'book_id': row[0],
'book_img': re.sub(r'[^a-zA-Z0-9]', '', row[1]).lower(),
'book_title': row[1],
'book_author': row[2],
'borrow_date': row[3],
'return_date': row[4]
}
for row in cursor.fetchall()
]
except DatabaseError as e:
error_message = str(e)
return JsonResponse({'success': False, 'errors': [error_message]}, status=400)
return JsonResponse({'success': True, 'message': "The book was returned.", 'borrowed_books':borrowed_books, 'book_id':book_id})
except:
return JsonResponse({'success': False, 'message': 'Return Error'}, status=400)