def test_insert_offer_with_price_less_than_10000(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"""
INSERT INTO offer
(
task_id,
price,
assignee,
offered_dt,
status_offer
)
VALUES(
{},
{},
'{}',
'{}',
'Nexist'
)
;
""".format(task_id, 10002, offer_dummy.assignee, offer_dummy.offered_dt)
# Raise IntegrityError as `price` cannot be bigger than 9999.99
with pytest.raises(DataError) as e_info:
sql(cursor, query)
def test_insert_offer_with_price_more_than_6_precises(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"""
INSERT INTO offer
(
task_id,
price,
assignee,
offered_dt,
status_offer
)
VALUES(
{},
{},
'{}',
'{}',
'Nexist'
)
;
""".format(task_id, 9999.999, offer_dummy.assignee, offer_dummy.offered_dt)
# Raise DataError as `price` can only have less or equals than 6 precises
with pytest.raises(DataError) as e_info:
sql(cursor, query)
def test_insert_offer_with_duplicate_task_id_assignee(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
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 with same task_id and assignee
query = r"""
SELECT
insert_one_offer({}, {}, '{}', '{}')
;
""".format(task_id, 120.23, offer_dummy.assignee,
"2018-01-10 07:43:54.798")
# Raise IntegrityError as pair `task_id` and `assignee` must be UNIQUE
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
def test_insert_offer_with_status_offer_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)
insert_new_task(cursor, task_dummy)
task_id = get_new_task_id(cursor, task_dummy)
query = r"""
INSERT INTO offer
(
task_id,
price,
assignee,
offered_dt,
status_offer
)
VALUES(
{},
{},
'{}',
'{}',
'Nexist'
)
;
""".format(task_id, offer_dummy.price, offer_dummy.assignee,
offer_dummy.offered_dt)
# Raise IntegrityError as `status_offer` doesnt exist
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
def test_insert_offer_without_offered_dt(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"""
INSERT INTO offer
(
task_id,
assignee,
price
)
VALUES(
{},
'{}',
{}
)
;
""".format(task_id, offer_dummy.assignee, offer_dummy.price)
# Raise IntegrityError as `offered_dt` cannot be NULL
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
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_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_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'