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
- Tables
- Procedures
- create_user
- borrow_book
- extend_borrow
- return_book
- Triggers
- ← Back
- SignUp
- LogIn
- Library
- Borrow
- Extend
- Return
SELECT something FROM tabs;
CREATE TABLE books (
book_id INT IDENTITY PRIMARY KEY,
book_title NVARCHAR(50),
book_author NVARCHAR(50),
samples INT
);
GO
CREATE TABLE users (
id INT IDENTITY PRIMARY KEY,
user_id INT UNIQUE NOT NULL,
user_name NVARCHAR(50) NOT NULL,
user_email NVARCHAR(150) NOT NULL,
user_password NVARCHAR(255) NOT NULL,
auth_token VARCHAR(64),
user_status INT DEFAULT 1,
start_date DATE DEFAULT GETDATE(),
end_date DATE DEFAULT '9999-12-31'
);
GO
CREATE TABLE transactions (
id INT IDENTITY PRIMARY KEY,
user_id INT REFERENCES users(user_id),
book_id INT REFERENCES books(book_id),
borrow_date DATE DEFAULT GETDATE(),
return_date DATE DEFAULT (DATEADD(DAY, 14, GETDATE())),
trx_status INT DEFAULT 1,
ext_count INT DEFAULT 0,
start_date DATE DEFAULT GETDATE(),
end_date DATE DEFAULT '9999-12-31'
);
GO
CREATE PROCEDURE create_user
@user_name NVARCHAR(50),
@user_email NVARCHAR(100),
@user_password NVARCHAR(256) /* Hashed in Python */
AS
BEGIN
SET NOCOUNT ON;
DECLARE @user_id INT;
BEGIN TRANSACTION;
BEGIN TRY
IF EXISTS (SELECT 1 FROM users WHERE user_email = @user_email AND end_date = '9999-12-31' AND user_status = 1)
BEGIN
THROW 50001, 'Email is already registered', 1;
END
IF EXISTS (SELECT 1 FROM users WHERE user_name = @user_name AND end_date = '9999-12-31' AND user_status = 1)
BEGIN
THROW 50002, 'Username is already registered', 1;
END
SET @user_id = NEXT VALUE FOR UserId_Seq;
INSERT INTO users (user_id, user_name, user_email, user_password)
VALUES (@user_id, @user_name, @user_email, @user_password);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;
GO
CREATE PROCEDURE borrow_book
@user_id INT,
@book_id INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@stock INT,
@books INT;
BEGIN TRANSACTION;
BEGIN TRY
SELECT @stock = samples
FROM books WITH (UPDLOCK, HOLDLOCK)
WHERE book_id = @book_id;
SET @books = (SELECT COUNT(*) FROM transactions WHERE user_id = @user_id AND trx_status <> 0 AND end_date = '9999-12-31');
IF @books >= 3
BEGIN
RAISERROR ('You can''t borrow more than 3 books.', 16, 1);
RETURN;
END
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')
BEGIN
RAISERROR ('You can''t borrow the same book multiple times.', 16, 1);
RETURN;
END
IF @stock = 0
BEGIN
RAISERROR ('This book is not available in stock right now.', 16, 1);
RETURN;
END
INSERT INTO transactions (user_id, book_id)
VALUES (@user_id, @book_id);
UPDATE books
SET samples = samples - 1
WHERE
book_id = @book_id;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;
GO
CREATE PROCEDURE extend_borrow
@user_id INT,
@book_id INT
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
IF EXISTS (SELECT 1 FROM transactions WHERE book_id = @book_id AND user_id = @user_id AND ext_count = 3 AND trx_status = 3 AND end_date = '9999-12-31')
BEGIN
THROW 50003, 'You can''t extend more than 3 times.', 1;
END
BEGIN
DECLARE
@v_ext_count INT,
@v_borrow_date DATE,
@v_return_date DATE;
SELECT @v_ext_count = ext_count, @v_borrow_date = borrow_date, @v_return_date = return_date
FROM transactions
WHERE
book_id = @book_id
AND user_id = @user_id
AND trx_status <> 0
AND end_date = '9999-12-31';
INSERT INTO transactions (user_id, book_id, borrow_date, return_date, trx_status, ext_count)
VALUES (@user_id, @book_id, @v_borrow_date, DATEADD(DAY, 7, @v_return_date), 3, @v_ext_count + 1);
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
THROW;
END CATCH
END;
GO
CREATE PROCEDURE return_book
@user_id INT,
@book_id INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@v_borrow_date DATE,
@v_ext_count INT;
BEGIN TRANSACTION;
BEGIN TRY
SELECT @v_borrow_date = borrow_date, @v_ext_count = ext_count
FROM transactions
WHERE
trx_status <> 0
AND user_id = @user_id
AND book_id = @book_id
AND end_date = '9999-12-31';
INSERT INTO transactions (user_id, book_id, borrow_date, return_date, trx_status, ext_count, start_date, end_date)
VALUES (@user_id, @book_id, @v_borrow_date, GETDATE(), 0, @v_ext_count, GETDATE(), '9999-12-31');
UPDATE books
SET samples = samples + 1
WHERE
book_id = @book_id;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
END CATCH
END;
GO
CREATE TRIGGER insert_trx_trigg
ON transactions
AFTER INSERT
AS
BEGIN
BEGIN TRANSACTION;
DECLARE
@trx_id INT,
@user_id INT,
@book_id INT;
SELECT @trx_id = id, @user_id = user_id, @book_id = book_id
FROM inserted;
UPDATE transactions
SET end_date = GETDATE()
WHERE
id <> @trx_id
AND end_date = '9999-12-31'
AND trx_status <> 0
AND user_id = @user_id
AND book_id = @book_id;
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
COMMIT TRANSACTION;
END
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)