def test_insert_person_with_duplicate_email(cursor, person_task_dummy):
# Add normal person
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
# Add the same person with different username
query = r"""
SELECT
insert_one_person('{}', '{}', '{}', '{}')
;
""".format('Not_bob_testing_real', person_task_dummy.password,
person_task_dummy.email, person_task_dummy.created_dt)
# Raise IntegrityError as `email` must be UNIQUE
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
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_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_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_task_without_description(cursor, person_task_dummy,
task_dummy):
insert_new_person(cursor, person_task_dummy)
# Add the task
query = r"""
INSERT INTO task
(
description,
category_id,
location,
requester,
start_dt,
end_dt,
price
)
VALUES
(
'{}',
{},
'{}',
'{}',
'{}',
'{}',
{}
)
;
""".format(task_dummy.title, task_dummy.category_id, task_dummy.location,
task_dummy.requester, task_dummy.start_dt, task_dummy.end_dt,
task_dummy.price)
# Raise IntegrityError as `description` cannot be NULL
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_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_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_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_task_with_non_exist_requester(cursor, person_task_dummy,
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)
# Raise IntegrityError as `requester` doesnt exist
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
def test_insert_task_with_invalid_category_id(cursor, person_task_dummy,
task_dummy):
insert_new_person(cursor, person_task_dummy)
# Add the task
query = r"""
SELECT
insert_one_task('{}', '{}', '{}', '{}', '{}', '{}', '{}', {})
;
""".format(task_dummy.title, task_dummy.description, "meow",
task_dummy.location, task_dummy.requester, task_dummy.start_dt,
task_dummy.end_dt, task_dummy.price)
# Raise DataError as `category_id` must be an integer
with pytest.raises(DataError) as e_info:
sql(cursor, query)
def test_insert_task_with_invalid_end_dt(cursor, person_task_dummy,
task_dummy):
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, "123123",
task_dummy.price)
# Raise DataError as `start_dt` is in invalid format
with pytest.raises(DataError) as e_info:
sql(cursor, query)
def test_insert_task_with_price_with_more_than_6_precises(
cursor, person_task_dummy, task_dummy):
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,
9999.999)
# 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_task_with_price_more_than_10000(cursor, person_task_dummy,
task_dummy):
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,
10001)
# Raise DataError as `price` cannot be bigger than 9999.99
with pytest.raises(DataError) as e_info:
sql(cursor, query)
def test_insert_task_with_price_less_than_0(cursor, person_task_dummy,
task_dummy):
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,
-30)
# Raise IntegrityError as `price` must be bigger or equals 0
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
def test_insert_task_with_start_dt_bigger_end_dt(cursor, person_task_dummy,
task_dummy):
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, '2018-03-10 18:43:54.798',
'2018-03-10 17:43:54.798', task_dummy.price)
# Raise IntegrityError as `start_dt` cannot be bigger than `end_dt`
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
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_person_without_email(cursor, person_task_dummy):
query = r"""
INSERT INTO person
(
username,
password,
created_dt
)
VALUES(
'{}',
'{}',
'{}'
)
;
""".format(person_task_dummy.username, person_task_dummy.password,
person_task_dummy.created_dt)
# Raise IntegrityError as `email` cannot be NULL
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
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_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_insert_task_with_non_exist_assignee(cursor, person_task_dummy,
task_dummy):
insert_new_person(cursor, person_task_dummy)
# Add the task
query = r"""
INSERT INTO task
(
title,
description,
category_id,
location,
requester,
start_dt,
end_dt,
price,
assignee
)
VALUES
(
'{}',
'{}',
{},
'{}',
'{}',
'{}',
'{}',
{},
'null_person_testing'
)
;
""".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)
# Raise IntegrityError as `category_id` doesnt exist
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
def test_insert_offer_without_task_id(cursor, task_dummy, offer_dummy,
person_task_dummy, person_offer_dummy):
insert_new_person(cursor, person_offer_dummy)
query = r"""
INSERT INTO offer
(
price,
assignee,
offered_dt
)
VALUES(
{},
'{}',
'{}'
)
;
""".format(offer_dummy.price, offer_dummy.assignee, offer_dummy.offered_dt)
# Raise IntegrityError as `task_id` cannot be NULL
with pytest.raises(IntegrityError) as e_info:
sql(cursor, query)
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_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_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_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_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'
