def test_update_offer_with_open_task(cursor, task_dummy, offer_dummy,
person_task_dummy, person_offer_dummy):
insert_new_person(cursor, person_task_dummy)
insert_new_person(cursor, person_offer_dummy)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
# Add an offer to update later
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy.price, offer_dummy.assignee,
offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Update the offer
query = r"""
SELECT
update_offer_by_assignee_taskid('{}', {}, {}, '{}')
;
""".format(offer_dummy.assignee, task_id, 50, offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Check that the offer's status is changed to 'pending'
query = r"""
SELECT offer.status_offer
FROM offer
WHERE 1=1
AND offer.task_id = {}
AND offer.assignee = '{}'
;
""".format(task_id, offer_dummy.assignee)
status_offer = sql_select(cursor, query)
# Ensure that the status of the edited offer is 'pending'
assert status_offer[0][0] == 'pending'
# Ensure that the status of the task is 'offered'
query = r"""
SELECT task.status_task
FROM task
WHERE 1=1
AND task.id = {}
;
""".format(task_id)
status_task = sql_select(cursor, query)
assert status_task[0][0] == 'offered'
def test_insert_offer_full(cursor, task_dummy, offer_dummy, person_task_dummy,
person_offer_dummy):
insert_new_person(cursor, person_task_dummy)
insert_new_person(cursor, person_offer_dummy)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy.price, offer_dummy.assignee,
offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Ensure that the new offer exists in the table
query = r"""
SELECT offer.id
FROM offer
WHERE 1=1
AND offer.task_id = {}
AND offer.price = {}
AND offer.assignee = '{}'
AND offer.offered_dt = '{}'
AND offer.status_offer = 'pending'
;""".format(task_id, offer_dummy.price, offer_dummy.assignee,
offer_dummy.offered_dt)
data = sql_select(cursor, query)
# Ensure that there is only 1 offer added
assert len(data) == 1
# Ensure the `status_task` is 'offered'
query = r"""
SELECT task.status_task
FROM task
WHERE 1=1
AND id = {}
;
""".format(task_id)
data = sql_select(cursor, query)
# Ensure that the task's status is changed to `offered` after adding an offer
assert data[0][0] == 'offered'
def test_insert_person_full_with_correct_role(cursor, person_task_dummy):
query = r"""
SELECT
insert_one_person('{}', '{}', '{}', '{}')
;
""".format(person_task_dummy.username, person_task_dummy.password,
person_task_dummy.email, person_task_dummy.created_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Ensure that the new person exists in the table
query = r"""
SELECT person.id
FROM person
WHERE 1=1
AND person.username = '******'
AND person.password = '******'
AND person.email = '{}'
AND person.created_dt = '{}'
AND person.role = 'member'
;""".format(person_task_dummy.username, person_task_dummy.password,
person_task_dummy.email, person_task_dummy.created_dt)
data = sql_select(cursor, query)
# Ensure that there is only 1 person added
assert len(data) == 1
def test_no_extra_added_offer_status(cursor):
query = r"""
SELECT
status
FROM offer_status
"""
db_data = sql_select(cursor, query)
csv_data = read_csv("offer_status.csv")
# Ensure that the categories in the CSV file and the Postgres DB are the same
assert set(db_data) == set(csv_data)
def test_delete_task_with_task_id_non_exist(cursor, person_task_dummy,
task_dummy):
# Add the requester
insert_new_person(cursor, person_task_dummy)
# Get the total number of tasks before removing the task
query = r"""
SELECT COUNT(*)
FROM task
;
"""
num_of_tasks_before = sql_select(cursor, query)
try:
num_of_tasks_before = int(num_of_tasks_before[0][0])
except Exception as e:
raise e
# Remove the task
query = r"""
SELECT
delete_one_task_by_task_id({})
;""".format(-1)
try:
row_count = sql_select(cursor, query)
except Exception as e:
raise e
# Get the total number of tasks after removing the task
query = r"""
SELECT COUNT(*)
FROM task
;
"""
num_of_tasks_after = sql_select(cursor, query)
try:
num_of_tasks_after = int(num_of_tasks_after[0][0])
except Exception as e:
raise e
# Ensure that no tasks got removed
assert num_of_tasks_after == num_of_tasks_before
def test_no_extra_added_category(cursor):
query = r"""
SELECT
id,
name
FROM category
"""
db_data = sql_select(cursor, query)
csv_data = read_csv("categories.csv")
# Ensure that the categories in the CSV file and the Postgres DB are the same
assert set(db_data) == set(csv_data)
def test_delete_offer_with_task_id_non_exist(cursor, task_dummy, offer_dummy, person_task_dummy, person_offer_dummy):
insert_new_person(cursor, person_task_dummy)
insert_new_person(cursor, person_offer_dummy)
# Get the total number of offers before removing the offer
query = r"""
SELECT COUNT(*)
FROM offer
;
"""
num_of_offers_before = sql_select(cursor, query)
try:
num_of_offers_before = int(num_of_offers_before[0][0])
except Exception as e:
raise e
# Delete the added offer with an invalid task_id
query = r"""
SELECT
delete_offer_by_assignee_and_task_id('{}', {})
;""".format(offer_dummy.assignee, -1)
try:
sql(cursor, query)
except Exception as e:
raise e
# Get the total number of offers after trying to remove the offer
query = r"""
SELECT COUNT(*)
FROM offer
;
"""
num_of_offers_after = sql_select(cursor, query)
try:
num_of_offers_after = int(num_of_offers_after[0][0])
except Exception as e:
raise e
# Ensure that no offers got deleted
assert num_of_offers_after == num_of_offers_before
def test_insert_task_full(cursor, person_task_dummy, task_dummy):
# Add the requester
insert_new_person(cursor, person_task_dummy)
# Add the task
query = r"""
SELECT
insert_one_task('{}', '{}', {}, '{}', '{}', '{}', '{}', {})
;
""".format(task_dummy.title, task_dummy.description,
task_dummy.category_id, task_dummy.location,
task_dummy.requester, task_dummy.start_dt, task_dummy.end_dt,
task_dummy.price)
try:
sql(cursor, query)
except Exception as e:
raise e
# Ensure that the new task exists in the table
query = r"""
SELECT task.id
FROM task
WHERE 1=1
AND task.title = '{}'
AND task.description = '{}'
AND task.category_id = {}
AND task.location = '{}'
AND task.requester = '{}'
AND task.start_dt = '{}'
AND task.end_dt = '{}'
AND task.price = {}
AND task.status_task = 'open'
AND task.assignee IS NULL
;
""".format(task_dummy.title, task_dummy.description,
task_dummy.category_id, task_dummy.location,
task_dummy.requester, task_dummy.start_dt, task_dummy.end_dt,
task_dummy.price)
data = sql_select(cursor, query)
# Ensure that there is only 1 task got inserted
assert len(data) == 1
def test_insert_offer_with_same_requester_assignee(cursor, task_dummy,
offer_dummy,
person_task_dummy):
insert_new_person(cursor, person_task_dummy)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy.price, person_task_dummy.username,
offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Check that the offer didnt get inserted
query = r"""
SELECT 1
FROM offer
WHERE EXISTS (
SELECT id
FROM offer
WHERE 1=1
AND offer.task_id = {}
AND offer.price = {}
AND offer.assignee = '{}'
AND offer.offered_dt = '{}'
AND offer.status_offer = 'pending'
)
;""".format(task_id, offer_dummy.price, person_task_dummy.username,
offer_dummy.offered_dt)
data = sql_select(cursor, query)
# Ensure that the offer, having the same requester and assignee, doesnt get inserted
assert (1, ) not in data
def test_delete_task_by_task_id(cursor, person_task_dummy, task_dummy):
# Add the requester
insert_new_person(cursor, person_task_dummy)
# Add the task
query = r"""
SELECT
insert_one_task('{}', '{}', {}, '{}', '{}', '{}', '{}', {})
;
""".format(task_dummy.title, task_dummy.description,
task_dummy.category_id, task_dummy.location,
task_dummy.requester, task_dummy.start_dt, task_dummy.end_dt,
task_dummy.price)
try:
sql(cursor, query)
except Exception as e:
raise e
# Get the task_id of the created task
task_id = get_new_task_id(cursor, task_dummy)
# Remove the task
query = r"""
WITH test_query AS (
SELECT
delete_one_task_by_task_id({})
)
SELECT COUNT(*) FROM test_query;
;""".format(task_id)
try:
row_count = sql_select(cursor, query)
except Exception as e:
raise e
# Ensure that only the added task got removed
assert len(row_count) == 1
def test_insert_category(cursor, category_meow):
query = r"""
INSERT INTO category (id, name)
VALUES ({}, '{}')
;
""".format(category_meow.id, category_meow.name)
try:
sql(cursor, query)
except Exception as e:
raise e
# Ensure that the new category exists in the table
query = r"""
SELECT COUNT(*)
FROM category
WHERE 1=1
AND category.name = 'meow'
;"""
data = sql_select(cursor, query)
# Ensure that there is only 1 category added
assert (1, ) in data
def test_update_task_by_id(cursor, person_task_dummy, task_dummy,
new_edited_task_dummy):
# Add the requester
insert_new_person(cursor, person_task_dummy)
# Add the task
query = r"""
SELECT
insert_one_task('{}', '{}', {}, '{}', '{}', '{}', '{}', {})
;
""".format(task_dummy.title, task_dummy.description,
task_dummy.category_id, task_dummy.location,
task_dummy.requester, task_dummy.start_dt, task_dummy.end_dt,
task_dummy.price)
try:
sql(cursor, query)
except Exception as e:
raise e
# Get the task_id of the created task
task_id = get_new_task_id(cursor, task_dummy)
# Update the task's details
query = r"""
WITH test_query AS (
SELECT
update_task_by_id({}, '{}', '{}', {}, '{}', '{}', '{}', {})
)
SELECT COUNT(*) FROM test_query
;
""".format(task_id, new_edited_task_dummy.title,
new_edited_task_dummy.description,
new_edited_task_dummy.category_id,
new_edited_task_dummy.location, new_edited_task_dummy.start_dt,
new_edited_task_dummy.end_dt, new_edited_task_dummy.price)
try:
row_count = sql_select(cursor, query)
except Exception as e:
raise e
# Ensure that only 1 row got edited
assert len(row_count) == 1
# Check that the task has been edited
query = r"""
SELECT COUNT(*)
FROM task
WHERE 1=1
AND task.id = {}
AND task.title = '{}'
AND task.description = '{}'
AND task.category_id = {}
AND task.location = '{}'
AND task.start_dt = '{}'
AND task.end_dt = '{}'
AND task.price = {}
;
""".format(task_id, new_edited_task_dummy.title,
new_edited_task_dummy.description,
new_edited_task_dummy.category_id,
new_edited_task_dummy.location, new_edited_task_dummy.start_dt,
new_edited_task_dummy.end_dt, new_edited_task_dummy.price)
data = sql_select(cursor, query)
# Ensure that there are only 1 task with the unique details
assert (1, ) in data
def test_update_task_upon_reject_one_offer(cursor, task_dummy, offer_dummy, offer_dummy_2, person_task_dummy, person_offer_dummy, person_offer_dummy_2):
insert_new_person(cursor, person_task_dummy)
insert_new_person(cursor, person_offer_dummy)
insert_new_person(cursor, person_offer_dummy_2)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
# Add an offer to reject later
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy.price, offer_dummy.assignee, offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Add another offer
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy_2.price, offer_dummy_2.assignee, offer_dummy_2.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Reject the first offer
offer_id = get_new_offer_id(cursor, task_id, offer_dummy)
query = r"""
WITH test_query AS (
SELECT
update_task_upon_rejecting_offer_by_task_id({}, {})
)
SELECT COUNT(*) FROM test_query
;
""".format(task_id, offer_id)
try:
row_count = sql_select(cursor, query)
except Exception as e:
raise e
# Ensure that there is only 1 offer got updated
assert len(row_count) == 1
# Check that the first offer's status is changed to 'rejected'
query = r"""
SELECT offer.status_offer
FROM offer
WHERE 1=1
AND offer.task_id = {}
AND offer.assignee = '{}'
;
""".format(task_id, offer_dummy.assignee)
status_offer = sql_select(cursor, query)
# Ensure that the status of the edited offer is 'rejected'
assert status_offer[0][0] == 'rejected'
# Check that the second offer's status is still 'pending'
query = r"""
SELECT offer.status_offer
FROM offer
WHERE 1=1
AND offer.task_id = {}
AND offer.assignee = '{}'
;
""".format(task_id, offer_dummy_2.assignee)
status_offer = sql_select(cursor, query)
# Ensure that the status of the edited offer is 'pending'
assert status_offer[0][0] == 'pending'
# Check that the task's status is still 'offered'
query = r"""
SELECT task.status_task
FROM task
WHERE 1=1
AND task.id = {}
;
""".format(task_id)
status_task = sql_select(cursor, query)
# Ensure that the status of the task is 'offered'
assert status_task[0][0] == 'offered'
def test_update_offer_with_accepted_task(cursor, task_dummy, offer_dummy,
person_task_dummy,
person_offer_dummy):
insert_new_person(cursor, person_task_dummy)
insert_new_person(cursor, person_offer_dummy)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
# Add an offer to update later
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy.price, offer_dummy.assignee,
offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Change the `status_task` to 'accepted'
query = r"""
UPDATE task
SET
status_task = 'accepted'
WHERE 1=1
AND id = {}
;
""".format(task_id)
try:
sql(cursor, query)
except Exception as e:
raise e
# Update the offer
query = r"""
WITH test_query AS (
SELECT
update_offer_by_assignee_taskid('{}', {}, {}, '{}')
)
SELECT COUNT(*) FROM test_query
;
""".format(offer_dummy.assignee, task_id, 50, offer_dummy.offered_dt)
try:
row_count = sql_select(cursor, query)
except Exception as e:
raise e
# Ensure that only 1 row got updated
assert len(row_count) == 1
# Check that the task's status is still 'accepted'
query = r"""
SELECT task.status_task
FROM task
WHERE 1=1
AND task.id = {}
;
""".format(task_id)
status_task = sql_select(cursor, query)
# Ensure that the task's status is still 'accepted'
assert status_task[0][0] == 'accepted'
def test_delete_offer_by_assignee_task_id(cursor, task_dummy, offer_dummy, person_task_dummy, person_offer_dummy):
insert_new_person(cursor, person_task_dummy)
insert_new_person(cursor, person_offer_dummy)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
# Add an offer to delete later
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, offer_dummy.price, offer_dummy.assignee, offer_dummy.offered_dt)
try:
sql(cursor, query)
except Exception as e:
raise e
# Get the total number of offers before removing the offer
query = r"""
SELECT COUNT(*)
FROM offer
;
"""
num_of_offers_before = sql_select(cursor, query)
try:
num_of_offers_before = int(num_of_offers_before[0][0])
except Exception as e:
raise e
# Delete the added offer
query = r"""
SELECT
delete_offer_by_assignee_and_task_id('{}', {})
;""".format(offer_dummy.assignee, task_id)
try:
sql(cursor, query)
except Exception as e:
raise e
# Get the total number of offers after removing the offer
query = r"""
SELECT COUNT(*)
FROM offer
;
"""
num_of_offers_after = sql_select(cursor, query)
try:
num_of_offers_after = int(num_of_offers_after[0][0])
except Exception as e:
raise e
# Ensure that only the added offer got removed
assert num_of_offers_after == num_of_offers_before - 1
# Check that the task_status is changed to 'open' if all offers are removed
query = r"""
SELECT task.status_task
FROM task
WHERE 1=1
AND id = {}
;
""".format(task_id)
status_task = sql_select(cursor, query)
# Ensure that the status of task with no offers is 'open'
assert status_task[0][0] == 'open'