def test_adapt_db_expected_list(self):
"""Test for adapting dictionaries in initial_db and expected_result to unitary lists """
collection = Collection(name_md='Colección', position=8, description_md='Colección de pruebas', author=None)
collection.save()
# Problems with dictionaries or already with [dict]
problems = [
SelectProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=dict()),
DMLProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=dict()),
FunctionProblem(title_html="titulo", text_html="enunciado", initial_db=dict(),
collection=collection, author=None, expected_result=dict()),
ProcProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=dict()),
TriggerProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=dict()),
SelectProblem(title_html="titulo", text_html="enunciado", initial_db=[dict()], collection=collection,
author=None, expected_result=[dict()]), # already has the right representation
]
for prob in problems:
prob.save()
adapt_db_result_to_list()
for prob in Problem.objects.all().select_subclasses():
self.assertIs(type(prob.initial_db), list)
self.assertIs(type(prob.initial_db[0]), dict)
self.assertIs(type(prob.expected_result), list)
self.assertIs(type(prob.expected_result[0]), dict)
for prob in Problem.objects.all():
prob.delete()
# Problems with wrong types in initial_db or expected_result
wrong_problems = [
SelectProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=3),
DMLProblem(title_html="titulo", text_html="enunciado", initial_db=6, collection=collection,
author=None, expected_result=dict()),
FunctionProblem(title_html="titulo", text_html="enunciado", initial_db=[],
collection=collection, author=None, expected_result=dict()),
ProcProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=[3]),
TriggerProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=[]),
TriggerProblem(title_html="titulo", text_html="enunciado", initial_db=dict(), collection=collection,
author=None, expected_result=[dict(), dict(), False]),
]
# Tries every wrong program individually (removing it after checking the exception)
for prob in wrong_problems:
prob.save()
with self.assertRaises(TypeError):
adapt_db_result_to_list()
prob.delete()
def test_with_wrong_zips(self):
"""Test to check that ZIP files with wrong hints.md file raise ValidationError"""
curr_path = os.path.dirname(__file__)
zip_select_description_path = os.path.join(curr_path, self.ZIP_FOLDER, self.SELECT_HINTS_WRONG_DESCRIPTION)
zip_select_description2_path = os.path.join(curr_path, self.ZIP_FOLDER, self.SELECT_HINTS_WRONG_DESCRIPTION2)
zip_select_sub_path = os.path.join(curr_path, self.ZIP_FOLDER, self.SELECT_HINTS_WRONG_SUBMITS)
zip_select_sub2_path = os.path.join(curr_path, self.ZIP_FOLDER, self.SELECT_HINTS_WRONG_SUBMITS2)
select_description = SelectProblem(zipfile=zip_select_description_path)
select_description2 = SelectProblem(zipfile=zip_select_description2_path)
select_sub = SelectProblem(zipfile=zip_select_sub_path)
select_sub2 = SelectProblem(zipfile=zip_select_sub2_path)
for problem in [select_description, select_sub, select_sub2, select_description2]:
self.assertRaises(ValidationError, problem.clean)
def test_individual_zip(self):
"""Load problem data from valid ZIP"""
curr_path = os.path.dirname(__file__)
zip_select_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.SELECT_OK)
zip_dml_path = os.path.join(curr_path, self.ZIP_FOLDER, self.DML_OK)
zip_function_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.FUNCTION_OK)
zip_proc_path = os.path.join(curr_path, self.ZIP_FOLDER, self.PROC_OK)
zip_trigger_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.TRIGGER_OK)
zip_discriminant_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.DISCRIMINANT_OK)
select_problem = SelectProblem(zipfile=zip_select_path)
dml_problem = DMLProblem(zipfile=zip_dml_path)
function_problem = FunctionProblem(zipfile=zip_function_path)
proc_problem = ProcProblem(zipfile=zip_proc_path)
trigger_problem = TriggerProblem(zipfile=zip_trigger_path)
discriminant_problem = DiscriminantProblem(
zipfile=zip_discriminant_path)
for problem in [
select_problem, dml_problem, function_problem, proc_problem,
trigger_problem, discriminant_problem
]:
self.assertFalse(problem.text_md)
problem.clean()
self.assertTrue(problem.text_md)
self.assertTrue('.html' in problem.template())
self.assertTrue(str(problem))
self.assertEqual(problem.language, 'es')
def test_load_broken_zip(self):
"""Open corrupt ZIP files"""
curr_path = os.path.dirname(__file__)
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, self.CORRUPT_ZIP)
collection = Collection(name_md='Colección',
position=8,
description_md='Colección de pruebas',
author=None)
collection.clean()
collection.save()
# Loading a broken ZIP file with several problems
collection.zipfile = zip_path
self.assertRaises(ValidationError, collection.clean)
# Loading a broken ZIP file a select problem
zip_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.CORRUPT_SELECT_ZIP)
problem = SelectProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
# Loading a correct ZIP file that contains a broken ZIP
zip_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.CORRUPT_ZIP_INSIDE)
collection.zipfile = zip_path
self.assertRaises(ValidationError, collection.clean)
def test_show_problems(self):
"""Shows a problem of each type"""
curr_path = os.path.dirname(__file__)
zip_select_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER, ParseTest.SELECT_OK)
zip_dml_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER, ParseTest.DML_OK)
zip_function_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER, ParseTest.FUNCTION_OK)
zip_proc_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER, ParseTest.PROC_OK)
zip_trigger_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER, ParseTest.TRIGGER_OK)
collection = create_collection('Colleccion de prueba XYZ')
user = create_user('5555', 'pepe')
select_problem = SelectProblem(zipfile=zip_select_path, collection=collection, author=user)
dml_problem = DMLProblem(zipfile=zip_dml_path, collection=collection, author=user)
function_problem = FunctionProblem(zipfile=zip_function_path, collection=collection, author=user)
proc_problem = ProcProblem(zipfile=zip_proc_path, collection=collection, author=user)
trigger_problem = TriggerProblem(zipfile=zip_trigger_path, collection=collection, author=user)
client = Client()
client.login(username='******', password='******')
for problem in [select_problem, dml_problem, function_problem, proc_problem, trigger_problem]:
problem.clean()
problem.save()
problem_url = reverse('judge:problem', args=[problem.pk])
response = client.get(problem_url, follow=True)
self.assertEqual(response.status_code, 200)
self.assertIn(problem.title_html, response.content.decode('utf-8'))
def test_zip_errors(self):
"""ValidationError when loading ZIP in all problem types"""
curr_path = os.path.dirname(__file__)
# Select problems
for filename in [
self.SELECT_MISSING_FILES, self.SELECT_EMPTY_TITLE,
self.SELECT_TEXT_DECODE
]:
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, filename)
problem = SelectProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
# DML problems
for filename in [
self.DML_MISSING_FILES, self.DML_BAD_NUM_STMT,
self.DML_TEXT_DECODE
]:
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, filename)
problem = DMLProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
# Function problems
for filename in [
self.FUNCTION_MISSING_FILES, self.FUNCTION_EMPTY_TITLE,
self.FUNCTION_TEXT_DECODE
]:
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, filename)
problem = FunctionProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
# Procedure problems
for filename in [
self.PROC_MISSING_FILES, self.PROC_EMPTY_TITLE,
self.PROC_TEXT_DECODE
]:
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, filename)
problem = ProcProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
# Trigger problems
for filename in [
self.TRIGGER_MISSING_FILES, self.TRIGGER_EMPTY_TITLE,
self.TRIGGER_TEXT_DECODE, self.TRIGGER_BAD_INSERT
]:
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, filename)
problem = TriggerProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
# Discriminant problems
for filename in [
self.DISCRIMINANT_MISSING_FILES, self.DISCRIMINANT_BAD_STMT,
self.DISCRIMINANT_TEXT_DECODE
]:
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, filename)
problem = DiscriminantProblem(zipfile=zip_path)
self.assertRaises(ValidationError, problem.clean)
def test_download(self):
"""Download the script of a problem (CREATE + INSERT)"""
curr_path = os.path.dirname(__file__)
zip_select_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER,
ParseTest.SELECT_OK)
zip_dml_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER,
ParseTest.DML_OK)
zip_function_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER,
ParseTest.FUNCTION_OK)
zip_proc_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER,
ParseTest.PROC_OK)
zip_trigger_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER,
ParseTest.TRIGGER_OK)
collection = create_collection('Colleccion de prueba AAA')
user = create_user('54522', 'antonio')
select_problem = SelectProblem(zipfile=zip_select_path,
collection=collection,
author=user)
dml_problem = DMLProblem(zipfile=zip_dml_path,
collection=collection,
author=user)
function_problem = FunctionProblem(zipfile=zip_function_path,
collection=collection,
author=user)
proc_problem = ProcProblem(zipfile=zip_proc_path,
collection=collection,
author=user)
trigger_problem = TriggerProblem(zipfile=zip_trigger_path,
collection=collection,
author=user)
client = Client()
client.login(username='******', password='******')
for problem in [
select_problem, dml_problem, function_problem, proc_problem,
trigger_problem
]:
problem.clean()
problem.save()
url = reverse('judge:create_insert', args=[problem.pk])
response = client.get(url, follow=True)
script = problem.create_sql + '\n\n' + problem.insert_sql
self.assertEqual(
response.get('Content-Disposition'),
"attachment; filename=create_insert.sql",
)
self.assertEqual(response.get('Content-Type'), "application/sql")
self.assertEqual(response.content.decode('UTF-8'), script)
self.assertEqual(response.status_code, 200)
def test_validate_wrong_problem(self):
""" Test to validate with DES a wrong SelectProblem """
problem = SelectProblem(create_sql='CREATE TABLE t(age int)',
insert_sql='',
solution='SELECT * FROM table')
problem2 = SelectProblem(create_sql='CREATE TABLE t(age int)',
insert_sql='',
solution='SELECT * FRO t')
with self.assertRaises(ValidationError) as ctx:
problem.validate_des()
self.assertIn("Unknown table or view 'table'", ctx.exception.message)
with self.assertRaises(ValidationError) as ctx:
problem2.validate_des()
self.assertIn("Expected FROM clause", ctx.exception.message)
def test_table_with_date(self):
"""Check that DATEs are correctly stored and retrived from the DB, and comparing them to a new obtained
value works as expected"""
collection = Collection()
collection.save()
create = 'CREATE TABLE test (day DATE);'
insert = "INSERT INTO test VALUES (TO_DATE('2003/07/09', 'yyyy/mm/dd'))"
solution = 'SELECT * FROM test'
problem = SelectProblem(title_md='Dates', text_md='Example with dates',
create_sql=create, insert_sql=insert, collection=collection,
solution=solution)
problem.clean()
problem.save()
oracle = OracleExecutor.get()
veredict, _ = problem.judge(solution, oracle)
self.assertEqual(veredict, VeredictCode.AC)
veredict, _ = problem.judge("SELECT TO_DATE('2003/07/09', 'yyyy/mm/dd') AS day FROM dual", oracle)
self.assertEqual(veredict, VeredictCode.AC)
def create_select_problem(collection, name='Ejemplo'):
""" Creates and stores a Select Problem """
create = 'CREATE TABLE test (n NUMBER);'
insert = "INSERT INTO test VALUES (901)"
solution = 'SELECT * FROM test'
problem = SelectProblem(title_md=name, text_md='texto largo',
create_sql=create, insert_sql=insert, collection=collection,
solution=solution)
problem.clean()
problem.save()
return problem
def test_num_statements(self):
"""Incorrect number of statements must raise a ValidationError"""
collection = Collection()
collection.save()
create = 'CREATE TABLE arg (n NUMBER);'
insert = "INSERT INTO arg VALUES (88)"
solution = 'SELECT * FROM arg'
problem = SelectProblem(
title_md='Test',
text_md='Simple Table',
create_sql=create,
insert_sql=insert,
collection=collection,
min_stmt=5,
max_stmt=2, # Impossible
solution=solution)
self.assertRaises(ValidationError, problem.clean)
def test_no_type(self):
"""Loading problem details form a ZIP with a JSON file without type field"""
curr_path = os.path.dirname(__file__)
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, self.NO_TYPE)
select_problem = SelectProblem(zipfile=zip_path)
dml_problem = DMLProblem(zipfile=zip_path)
function_problem = FunctionProblem(zipfile=zip_path)
proc_problem = ProcProblem(zipfile=zip_path)
trigger_problem = TriggerProblem(zipfile=zip_path)
discriminant_problem = DiscriminantProblem(zipfile=zip_path)
for problem in [
select_problem, dml_problem, function_problem, proc_problem,
trigger_problem, discriminant_problem
]:
self.assertRaises(ValidationError, problem.clean)
def test_zip_other_type(self):
"""Loading a problem data from a ZIP of a different type must raise a ValidationError"""
curr_path = os.path.dirname(__file__)
zip_select_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.SELECT_OK)
zip_dml_path = os.path.join(curr_path, self.ZIP_FOLDER, self.DML_OK)
zip_function_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.FUNCTION_OK)
zip_proc_path = os.path.join(curr_path, self.ZIP_FOLDER, self.PROC_OK)
zip_trigger_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.TRIGGER_OK)
select_problem = SelectProblem(zipfile=zip_dml_path)
dml_problem = DMLProblem(zipfile=zip_function_path)
function_problem = FunctionProblem(zipfile=zip_proc_path)
proc_problem = ProcProblem(zipfile=zip_trigger_path)
trigger_problem = TriggerProblem(zipfile=zip_select_path)
discriminant_problem = TriggerProblem(zipfile=zip_select_path)
for problem in [
select_problem, dml_problem, function_problem, proc_problem,
trigger_problem, discriminant_problem
]:
self.assertRaises(ValidationError, problem.clean)
def test_bad_json(self):
""" Errors when decoding JSON or missing type """
curr_path = os.path.dirname(__file__)
zip_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.JSON_DECODE_ERROR)
problem = SelectProblem(zipfile=zip_path)
with self.assertRaises(ValidationError) as ctxt:
problem.clean()
self.assertIn('Error when opening problem.json: Expecting value',
str(ctxt.exception))
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, self.NO_TYPE)
self.assertIsNone(get_problem_type_from_zip(zip_path))
zip_path = os.path.join(curr_path, self.ZIP_FOLDER, self.WRONG_TYPE)
problem = SelectProblem(zipfile=zip_path)
with self.assertRaises(ZipFileParsingException) as ctxt:
get_problem_type_from_zip(zip_path)
self.assertIn('Problem type is not defined in', str(ctxt.exception))
def test_des_timeout_problem(self):
""" DES require too much time to process SQL query, so it is aborted and returns no messages """
problem = SelectProblem(create_sql=self.__CREATE_TIMEOUT,
solution='SELECT * FROM Persona')
msgs = problem.get_des_messages_solution(self.__QUERY_TIMEOUT)
self.assertEqual(msgs, [])
def test_problem_collection_stats(self):
"""Methods that compute statistics in collections and problems"""
collection = Collection(name_md='ABC', description_md='blablabla')
collection.clean()
collection.save()
self.assertTrue('ABC' in str(collection))
user_model = django.contrib.auth.get_user_model()
create = 'CREATE TABLE mytable (dd DATE);'
insert = "INSERT INTO mytable VALUES (TO_DATE('2020/01/31', 'yyyy/mm/dd'))"
solution = 'SELECT * FROM mytable'
problem1 = SelectProblem(title_md='Dates',
text_md='Example with dates',
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
problem2 = SelectProblem(title_md='Dates',
text_md='Example with dates',
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
problem3 = SelectProblem(title_md='Dates',
text_md='Example with dates',
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
user1 = user_model.objects.create_user(username='******',
email='*****@*****.**',
password='******')
user2 = user_model.objects.create_user(username='******',
email='*****@*****.**',
password='******')
problem1.clean()
problem1.save()
problem2.clean()
problem2.save()
problem3.clean()
problem3.save()
user1.save()
user2.save()
sub1 = Submission(code='nada',
veredict_code=VeredictCode.WA,
user=user1,
problem=problem1)
sub2 = Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1)
sub3 = Submission(code='nada',
veredict_code=VeredictCode.TLE,
user=user1,
problem=problem1)
sub4 = Submission(code='nada',
veredict_code=VeredictCode.RE,
user=user1,
problem=problem1)
sub5 = Submission(code='nada',
veredict_code=VeredictCode.VE,
user=user1,
problem=problem1)
sub6 = Submission(code='nada',
veredict_code=VeredictCode.IE,
user=user1,
problem=problem1)
self.assertTrue('WA' in str(sub1))
self.assertTrue('AC' in str(sub2))
for sub in [sub1, sub2, sub3, sub4, sub5, sub6]:
sub.save()
# Problem solved
self.assertTrue(problem1.solved_by_user(user1))
self.assertFalse(problem1.solved_by_user(user2))
self.assertFalse(problem2.solved_by_user(user1))
self.assertFalse(problem2.solved_by_user(user2))
# Number of submissions
self.assertEqual(problem1.num_submissions_by_user(user1), 6)
self.assertEqual(problem1.num_submissions_by_user(user2), 0)
self.assertEqual(problem2.num_submissions_by_user(user1), 0)
self.assertEqual(problem2.num_submissions_by_user(user2), 0)
# Problems in collection
self.assertEqual(collection.problems().count(), 3)
self.assertEqual(collection.num_problems(), 3)
# Numbers of problems solved by a user
self.assertEqual(collection.num_solved_by_user(user1), 1)
self.assertEqual(collection.num_solved_by_user(user2), 0)
def test_podium(self):
"""Test the correct performance of the podium"""
collection = Collection(name_md='ABC', description_md='blablabla')
collection.clean()
collection.save()
self.assertIn('ABC', str(collection))
user_model = django.contrib.auth.get_user_model()
create = 'CREATE TABLE mytable (dd DATE);'
insert = "INSERT INTO mytable VALUES (TO_DATE('2020/01/31', 'yyyy/mm/dd'))"
solution = 'SELECT * FROM mytable'
problem1 = SelectProblem(title_md='Dates',
text_md='Example with dates',
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
problem2 = SelectProblem(title_md='Dates',
text_md='Example with dates',
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
user1 = user_model.objects.create_user(username='******',
email='*****@*****.**',
password='******')
user2 = user_model.objects.create_user(username='******',
email='*****@*****.**',
password='******')
user3 = user_model.objects.create_user(username='******',
email='*****@*****.**',
password='******')
user4 = user_model.objects.create_user(username='******',
email='*****@*****.**',
password='******')
problem1.clean()
problem1.save()
problem2.clean()
problem2.save()
user1.save()
user2.save()
user3.save()
self.assertIsNone(problem1.solved_first())
self.assertIsNone(problem1.solved_second())
self.assertIsNone(problem1.solved_third())
sub1 = Submission(code='nada',
veredict_code=VeredictCode.WA,
user=user1,
problem=problem1)
sub2 = Submission(code='nada',
veredict_code=VeredictCode.IE,
user=user1,
problem=problem1)
sub3 = Submission(code='nada',
veredict_code=VeredictCode.TLE,
user=user1,
problem=problem1)
sub4 = Submission(code='nada',
veredict_code=VeredictCode.RE,
user=user1,
problem=problem1)
sub5 = Submission(code='nada',
veredict_code=VeredictCode.VE,
user=user1,
problem=problem1)
for sub in [sub1, sub2, sub3, sub4, sub5]:
sub.save()
self.assertIsNone(problem1.solved_first())
self.assertIsNone(problem1.solved_second())
self.assertIsNone(problem1.solved_third())
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1).save()
self.assertEqual(problem1.solved_first(), user1)
self.assertIsNone(problem1.solved_second())
self.assertIsNone(problem1.solved_third())
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1).save()
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1).save()
self.assertEqual(problem1.solved_first(), user1)
self.assertIsNone(problem1.solved_second())
self.assertIsNone(problem1.solved_third())
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user2,
problem=problem1).save()
self.assertEqual(problem1.solved_first(), user1)
self.assertEqual(problem1.solved_second(), user2)
self.assertIsNone(problem1.solved_third())
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1).save()
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user3,
problem=problem1).save()
self.assertEqual(problem1.solved_first(), user1)
self.assertEqual(problem1.solved_second(), user2)
self.assertEqual(problem1.solved_third(), user3)
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1).save()
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user1,
problem=problem1).save()
Submission(code='nada',
veredict_code=VeredictCode.AC,
user=user4,
problem=problem1).save()
self.assertEqual(problem1.solved_first(), user1)
self.assertEqual(problem1.solved_second(), user2)
self.assertEqual(problem1.solved_third(), user3)
self.assertIsNone(problem2.solved_first())
self.assertIsNone(problem2.solved_second())
self.assertIsNone(problem2.solved_third())
def test_judge_multiple_db(self):
"""Test multiple db select problems"""
curr_path = os.path.dirname(__file__)
zip_select_multiple_db_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.SELECT_MULTIPLE_DB_OK)
select_multiple_db_problem = SelectProblem(
zipfile=zip_select_multiple_db_path)
select_multiple_db_problem.clean()
self.assertEqual(len(select_multiple_db_problem.insert_sql_list()), 3)
collection = Collection(name_md='ABC', description_md='blablabla')
collection.clean()
collection.save()
self.assertTrue('ABC' in str(collection))
create = '''CREATE TABLE Club(
CIF CHAR(9) PRIMARY KEY, -- No puede ser NULL
Nombre VARCHAR2(40) NOT NULL,
Sede VARCHAR2(30) NOT NULL,
Num_Socios NUMBER(10,0) NOT NULL,
CONSTRAINT NumSociosPositivos CHECK (Num_Socios >= 0)
);
CREATE TABLE Persona(
NIF CHAR(9) PRIMARY KEY,
Nombre VARCHAR2(20) NOT NULL
);'''
insert = '''INSERT INTO Club VALUES ('11111111X', 'Madrid', 'A', 70000);
-- @new data base@
INSERT INTO Club VALUES ('11111111X', 'Madrid', 'A', 70000);
INSERT INTO Club VALUES ('11111112X', 'Futbol Club Barcelona', 'A', 80000);
INSERT INTO Club VALUES ('11111113X', 'Paris Saint-Germain Football Club', 'C', 1000);
INSERT INTO Persona VALUES ('00000001X', 'Peter Johnoson');
-- @new data base@
INSERT INTO Club VALUES ('11111111X', 'Madrid', 'A', 70000);
INSERT INTO Club VALUES ('11111112X', 'Madrid', 'B', 80000);
INSERT INTO Club VALUES ('11111114X', 'Futbol Club Barcelona', 'B', 80000);
INSERT INTO Club VALUES ('11111115X', 'Paris Saint-Germain Football Club', 'C', 1000);
INSERT INTO Persona VALUES ('00000001X', 'Peter Johnoson');'''
solution = "SELECT Sede, Nombre FROM Club WHERE CIF = '11111111X' and Nombre ='Madrid';"
oracle = OracleExecutor.get()
problem = SelectProblem(title_md='Test Multiple db Select',
text_md='bla',
create_sql=create,
insert_sql=insert,
collection=collection,
author=None,
check_order=False,
solution=solution)
problem.clean()
problem.save()
self.assertEqual(problem.judge(solution, oracle)[0], VeredictCode.AC)
self.assertEqual(
problem.judge(
"SELECT Sede, Nombre FROM Club WHERE Nombre ='Madrid';",
oracle)[0], VeredictCode.WA)
self.assertEqual(
problem.judge("SELECT Sede, Nombre FROM Club;", oracle)[0],
VeredictCode.WA)
html = problem.judge(
"SELECT Sede, Nombre FROM Club WHERE CIF = '11111111X' and Nombre ='Madrid';",
oracle)[1]
soup = BeautifulSoup(html, 'html.parser')
# Dont show db if code is correct
self.assertIsNone(soup.find(id="bd"))
html = problem.judge(
"SELECT Sede, Nombre FROM Club WHERE CIF = '11111117X';",
oracle)[1]
soup = BeautifulSoup(html, 'html.parser')
# Dont show db if code is wrong in the first db
self.assertIsNone(soup.find(id="bd"))
html = problem.judge("SELECT Sede, Nombre FROM Club;", oracle)[1]
soup = BeautifulSoup(html, 'html.parser')
# Show second db if code is correct in the first db but not in the second db
self.assertEqual(
soup.find(id="bd").find('p').find('strong').string,
"Base de datos utilizada para la ejecución de tu código SQL:")
self.assertEqual(
soup.find(id="bd").find_all('thead')[0].find_all('th')[0].string,
"CIF")
self.assertEqual(
soup.find(id="bd").find_all('thead')[0].find_all('th')[1].string,
"NOMBRE")
self.assertEqual(len(soup.find(id="bd").find_all('thead')), 2)
self.assertEqual(
soup.find(id="bd").find_all('thead')[1].find_all('th')[0].string,
"NIF")
self.assertEqual(
soup.find(id="bd").find_all('tbody')[0].find_all('tr')[1].find_all(
'td')[0].string, "11111112X")
self.assertEqual(
soup.find(id="bd").find_all('tbody')[0].find_all('tr')[1].find_all(
'td')[2].string, "A")
self.assertEqual(
len(soup.find(id="bd").find_all('tbody')[0].find_all('tr')), 3)
html = problem.judge(
"SELECT Sede, Nombre FROM Club WHERE Nombre ='Madrid';", oracle)[1]
soup = BeautifulSoup(html, 'html.parser')
# Show third db if code is correct in the first and second dbs but not in the third db
self.assertEqual(
soup.find(id="bd").find('p').find('strong').string,
"Base de datos utilizada para la ejecución de tu código SQL:")
self.assertEqual(
soup.find(id="bd").find_all('thead')[0].find_all('th')[0].string,
"CIF")
self.assertEqual(
soup.find(id="bd").find_all('thead')[0].find_all('th')[1].string,
"NOMBRE")
self.assertEqual(
soup.find(id="bd").find_all('tbody')[0].find_all('tr')[1].find_all(
'td')[0].string, "11111112X")
self.assertEqual(
soup.find(id="bd").find_all('tbody')[0].find_all('tr')[1].find_all(
'td')[2].string, "B")
self.assertEqual(
len(soup.find(id="bd").find_all('tbody')[0].find_all('tr')), 4)
def test_long_hint(self):
"""Test to check if hints.md is loaded correctly to a SelectProblem.
It checks both hints with one line and hints with several lines"""
curr_path = os.path.dirname(__file__)
zip_select_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.SELECT_HINTS)
zip_select_pro_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.SELECT_HINTS_PRO)
collection = create_collection('Coleccion 1')
select = SelectProblem(zipfile=zip_select_path, collection=collection)
select_pro = SelectProblem(zipfile=zip_select_pro_path,
collection=collection)
hints_expected1 = (3, 'descripcion pista 1')
hints_expected2 = (5, 'descripcion pista 2')
hints_expected3 = (10, 'descripcion pista 3')
text_md = """Ten en **cuenta** que:
* debes seleccionar las tablas
* debes elegir cuidadosamente las columnas"""
hits_expected_pro = (5, text_md)
# Check hints loaded for SelectProblem
select.clean()
select.save()
hints = Hint.objects.filter(problem=select).order_by('num_submit')
self.assertEqual(hints.count(), 3)
self.assertEqual(hints_expected1[0], hints[0].num_submit)
self.assertEqual(hints_expected1[1], hints[0].text_md)
self.assertEqual(hints_expected2[0], hints[1].num_submit)
self.assertEqual(hints_expected2[1], hints[1].text_md)
self.assertEqual(hints_expected3[0], hints[2].num_submit)
self.assertEqual(hints_expected3[1], hints[2].text_md)
# Check hints loaded for SelectProblem pro
select_pro.clean()
select_pro.save()
hints = Hint.objects.filter(problem=select_pro).order_by('num_submit')
self.assertEqual(hints.count(), 3)
self.assertEqual(hints_expected1[0], hints[0].num_submit)
self.assertEqual(hints_expected1[1], hints[0].text_md)
self.assertEqual(hits_expected_pro[0], hints[1].num_submit)
self.assertEqual(hits_expected_pro[1], hints[1].text_md)
self.assertEqual(hints_expected3[0], hints[2].num_submit)
self.assertEqual(hints_expected3[1], hints[2].text_md)
def test_validate_correct_problem(self):
""" Test to validate with DES a correct SelectProblem """
problem = SelectProblem(create_sql='CREATE TABLE t(age int)',
insert_sql='',
solution='SELECT * FROM t')
self.assertIsNone(problem.validate_des())
def test_solved_n_position(self):
""" Test that solved_n_position does not count staff or inactive users """
staff_user = get_user_model().objects.create_user('teacher',
password='******',
is_staff=True,
is_active=True)
inactive_user = get_user_model().objects.create_user('inactive',
password='******',
is_staff=False,
is_active=False)
user = get_user_model().objects.create_user('normal',
password='******',
is_staff=False,
is_active=True)
collection = Collection(name_md='ABC', description_md='blablabla')
collection.clean()
collection.save()
create = 'CREATE TABLE tabla (xx NUMBER);'
solution = 'SELECT * FROM tabla'
problem = SelectProblem(title_md='Example',
text_md='Enunciado',
create_sql=create,
insert_sql="",
collection=collection,
solution=solution)
problem.clean()
problem.save()
sub1 = Submission(code='nada',
verdict_code=VerdictCode.AC,
user=staff_user,
problem=problem)
sub2 = Submission(code='nada',
verdict_code=VerdictCode.AC,
user=inactive_user,
problem=problem)
sub3 = Submission(code='nada',
verdict_code=VerdictCode.AC,
user=user,
problem=problem)
for sub in (sub1, sub2, sub3):
sub.clean()
sub.save()
# First non-staff active user to solve the problem is 'user'
self.assertEqual(problem.solved_first(), user)
self.assertIsNone(problem.solved_second())
self.assertIsNone(problem.solved_third())
# Non-active or staff users are not counted in solved_position
self.assertIsNone(problem.solved_position(staff_user))
self.assertIsNone(problem.solved_position(inactive_user))
self.assertEqual(problem.solved_position(user), 1)
def test_collection_languages(self):
"""Test to check languages in collection list"""
client = Client()
collection = create_collection('Collection')
create_user('5555', 'pepe')
client.login(username='******', password='******')
create = 'CREATE TABLE mytable (dd DATE);'
insert = "INSERT INTO mytable VALUES (TO_DATE('2020/01/31', 'yyyy/mm/dd'))"
solution = 'SELECT * FROM mytable'
problem1 = SelectProblem(title_md='Dates',
text_md='Example with dates',
language="es",
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
problem2 = SelectProblem(title_md='Dates',
text_md='Example with dates',
language="es",
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
problem1.clean()
problem1.save()
problem2.clean()
problem2.save()
self.assertIn("es", collection.languages())
self.assertNotIn("en", collection.languages())
collections_url = reverse('judge:collections')
html = client.get(collections_url, follow=True).content.decode('utf-8')
soup = BeautifulSoup(html, 'html.parser')
self.assertEqual(
soup.find_all("div", {"class": "flags"})[0].find_all(
"span", {"flag-icon"}), [])
problem3 = SelectProblem(title_md='Dates',
text_md='Example with dates',
language="en",
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution)
problem3.clean()
problem3.save()
self.assertIn("es", collection.languages())
self.assertIn("en", collection.languages())
collections_url = reverse('judge:collections')
html = client.get(collections_url, follow=True).content.decode('utf-8')
soup = BeautifulSoup(html, 'html.parser')
self.assertEqual(
len(
soup.find_all("div", {"class": "flags"})[0].find_all(
"span", {"flag-icon"})), 2)
flags = soup.find_all("div", {"class": "flags"})[0].find_all(
"span", {"flag-icon"})[0]['class']
flags.extend(
soup.find_all("div", {"class": "flags"})[0].find_all(
"span", {"flag-icon"})[1]['class'])
self.assertIn("flag-icon-us", flags)
self.assertIn("flag-icon-es", flags)
def test_select(self):
"""Tests for SelectProblem.judge()"""
collection = Collection()
collection.save()
create = '''CREATE TABLE "Nombre Club" (
CIF CHAR(9) PRIMARY KEY, -- No puede ser NULL
Nombre VARCHAR2(40) NOT NULL UNIQUE,
Sede VARCHAR2(30) NOT NULL,
Num_Socios NUMBER(10,0) NOT NULL,
CONSTRAINT NumSociosPositivos CHECK (Num_Socios >= 0)
);'''
insert = '''INSERT INTO "Nombre Club" VALUES ('11111111X', 'Real Madrid CF', 'Concha Espina', 70000);
INSERT INTO "Nombre Club" VALUES ('11111112X', 'Futbol Club Barcelona', 'Aristides Maillol', 80000);
INSERT INTO "Nombre Club" VALUES ('11111113X', 'PSG', 'Rue du Commandant Guilbaud', 1000);'''
solution = 'SELECT * FROM "Nombre Club";'
oracle = OracleExecutor.get()
problem = SelectProblem(title_md='Test Select',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
author=None,
check_order=False,
solution=solution)
problem.clean() # Needed to compute extra HTML fields and solutions
problem.save()
# Time-limit
tle = SELECT_TLE
too_many_rows = f"select * from dual connect by level <= {int(os.environ['ORACLE_MAX_ROWS'])+1};"
too_many_cols = f"select {','.join(['1']*(int(os.environ['ORACLE_MAX_COLS'])+1))} from dual;"
self.assert_executor_exception(lambda: problem.judge(tle, oracle),
OracleStatusCode.TLE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge(too_many_rows, oracle),
OracleStatusCode.TLE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge(too_many_cols, oracle),
OracleStatusCode.TLE_USER_CODE)
# Validation error (only one statement supported)
self.assert_executor_exception(lambda: problem.judge('', oracle),
OracleStatusCode.NUMBER_STATEMENTS)
self.assert_executor_exception(
lambda: problem.judge(
'SELECT * FROM "Nombre Club"; SELECT * '
'FROM "Nombre Club"', oracle),
OracleStatusCode.NUMBER_STATEMENTS)
# Runtime error
self.assert_executor_exception(
lambda: problem.judge('SELECT * from "Nombre ClubE"', oracle),
OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge('SELECT * from Club', oracle),
OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge('SELECT * FROM', oracle),
OracleStatusCode.EXECUTE_USER_CODE)
# Correct solution
self.assertEqual(problem.judge(solution, oracle)[0], VeredictCode.AC)
self.assertEqual(
problem.judge(
'SELECT CIF, NOmbre, Sede, Num_Socios FROM "Nombre Club"',
oracle)[0], VeredictCode.AC)
self.assertEqual(
problem.judge(
'SELECT * FROM "Nombre Club" ORDER BY Num_Socios ASC',
oracle)[0], VeredictCode.AC)
# Incorrect solution
self.assertEqual(
problem.judge('SELECT CIF FROM "Nombre Club"', oracle)[0],
VeredictCode.WA)
self.assertEqual(
problem.judge(
'SELECT * FROM "Nombre Club" WHERE Num_Socios < 50000',
oracle)[0], VeredictCode.WA)
def test_achievements_submit(self):
"""Test to show correct message when obtain an achievement"""
client = Client()
collection = create_collection('Colleccion de prueba XYZ')
select_problem = create_select_problem(collection,
'SelectProblem ABC DEF')
user = create_user('5555', 'tamara')
ach_submission = NumSubmissionsProblemsAchievementDefinition(
name={"es": 'Un envio'},
description={"es": 'Envia una solucion para un problema'},
num_problems=1,
num_submissions=1)
ach_submission.save()
ach_submissions = NumSubmissionsProblemsAchievementDefinition(
name={"es": 'Tres envios'},
description={"es": 'Envia tres soluciones de un problema'},
num_problems=1,
num_submissions=3)
ach_submissions.save()
ach_type = NumSolvedTypeAchievementDefinition(
name={"es": 'Es select'},
description={"es": 'Resuelve un problema SELECT'},
num_problems=1,
problem_type=ProblemType.SELECT.name)
client.login(username='******', password='******')
submit_select_url = reverse('judge:submit', args=[select_problem.pk])
# The user submits one solution and obtains the first achievement
response = client.post(submit_select_url, {'code': 'MAL'},
follow=True) # Validation Error, too short
obtained_achieve = ObtainedAchievement.objects.filter(user=user)
self.assertIn(obtained_achieve[0].achievement_definition.name['es'],
response.json()['achievements'])
# The user submits a new solution and does not receive any achievement
response = client.post(submit_select_url, {'code': 'MAL'},
follow=True) # Validation Error, too short
self.assertNotIn('achievements', response.json())
# The user makes another submission and obtain two achievements
ach_type.save()
curr_path = os.path.dirname(__file__)
zip_select_path = os.path.join(curr_path, ParseTest.ZIP_FOLDER,
ParseTest.SELECT_OK)
collection = create_collection('Coleccion 1')
select = SelectProblem(zipfile=zip_select_path, collection=collection)
select.clean()
select.save()
submit_url = reverse('judge:submit', args=[select.pk])
response = client.post(submit_url, {'code': select.solution},
follow=True)
obtained_achieve = ObtainedAchievement.objects.filter(user=user)
self.assertIn(obtained_achieve[1].achievement_definition.name['es'],
response.json()['achievements'])
self.assertIn(obtained_achieve[2].achievement_definition.name['es'],
response.json()['achievements'])
# The user submits a new solution and does not receive any achievement
response = client.post(submit_select_url, {'code': 'MAL'},
follow=True) # Validation Error, too short
self.assertNotIn('achievements', response.json())
