def test_des_dml_messages(self):
""" Check that get_des_messages_solution() returns the correct messages """
problem = DMLProblem(
title_md='Test DML',
text_md='bla bla bla',
create_sql="CREATE TABLE t(name VARCHAR(20) PRIMARY KEY, age INT)",
insert_sql=
"INSERT INTO t VALUES ('pepe', 3); INSERT INTO t VALUES ('ana', 13);",
solution="INSERT INTO t VALUES ('eva', 18)")
code = [
"INSERT INTO t VALUES('pepe', 48)", # Primary key error
"INSERT INTO t VALUES ('eva', 18)", # OK
"UPDATE t SET age = 0 WHERE age = -9", # No tuple met the condition
"DELETE FROM t WHERE age = 10"
] # No tuple met the condition
msgs = problem.get_des_messages_solution(";\n".join(code))
self.assertEqual(len(msgs), 3)
# Message for INSERT (pepe, 48)
self.assertEqual(msgs[0][0], DesMessageType.ERROR)
self.assertIn('Primary key violation', msgs[0][1])
self.assertEqual(code[0], msgs[0][2]) # The statement is the snippet
# Message for UPDATE
self.assertEqual(msgs[1][0], DesMessageType.WARNING)
self.assertIn("No tuple met the 'where' condition for updating",
msgs[1][1])
self.assertEqual(code[2], msgs[1][2]) # The statement is the snippet
# Message for DELETE
self.assertEqual(msgs[2][0], DesMessageType.WARNING)
self.assertIn("No tuple met the 'where' condition for deleting",
msgs[2][1])
self.assertEqual(code[3], msgs[2][2]) # The statement is the snippet
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_des_dml_timeout(self):
""" When DES timeouts DMLProblem.get_des_messages_solution returns [] """
problem = DMLProblem(
title_md='Test DML',
text_md='bla bla bla',
create_sql=self.__CREATE_TIMEOUT,
insert_sql='',
solution=f"INSERT INTO t ({self.__QUERY_TIMEOUT})")
code = f"INSERT INTO t ({self.__QUERY_TIMEOUT})"
messages = problem.get_des_messages_solution(code)
self.assertEqual(messages, [])
def test_validate_dml(self):
""" Checks that validating a DMLProblem with errors returns some DES messages """
problem = DMLProblem(
title_md='Test DML',
text_md='bla bla bla',
create_sql="CREATE TABLE t(name VARCHAR(20) PRIMARY KEY, age INT)",
insert_sql=
"INSERT INTO t VALUES ('pepe', 3); INSERT INTO t VALUES ('pepe', 13);",
solution="INSERT INTO t VALUES ('pepe', 18)")
with self.assertRaises(ValidationError) as ctxt:
problem.validate_des()
exception = str(ctxt.exception)
self.assertIn("Error code: 0", exception)
self.assertIn("Primary key violation", exception)
self.assertIn("t(pepe,13)", exception)
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_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_load_hint(self):
"""Test to check if hints.md is loaded correctly"""
curr_path = os.path.dirname(__file__)
zip_dml_path = os.path.join(curr_path, self.ZIP_FOLDER, self.DML_HINTS)
zip_function_path = os.path.join(curr_path, self.ZIP_FOLDER, self.FUNCTION_HINTS)
zip_proc_path = os.path.join(curr_path, self.ZIP_FOLDER, self.PROC_HINTS)
zip_trigger_path = os.path.join(curr_path, self.ZIP_FOLDER, self.TRIGGER_HINTS)
zip_discriminant_path = os.path.join(curr_path, self.ZIP_FOLDER, self.DISCRIMINANT_HINTS)
collection = create_collection('Coleccion 1')
dml = DMLProblem(zipfile=zip_dml_path, collection=collection)
function = FunctionProblem(zipfile=zip_function_path, collection=collection)
proc = ProcProblem(zipfile=zip_proc_path, collection=collection)
trigger = TriggerProblem(zipfile=zip_trigger_path, collection=collection)
discriminant = DiscriminantProblem(zipfile=zip_discriminant_path, collection=collection)
hints_expected1 = (3, 'descripcion pista 1')
hints_expected2 = (5, 'descripcion pista 2')
hints_expected3 = (10, 'descripcion pista 3')
for problem in [dml, function, proc, trigger, discriminant]:
problem.clean()
problem.save()
hints = Hint.objects.filter(problem=problem).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)
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 create_dml_complete_problem(collection, name='Ejemplo'):
"""Creates and stores a DML Problem with an INSERT, a DELETE, a DROP and CREATE"""
create = 'CREATE TABLE test_table_1 (n NUMBER);\
CREATE TABLE test_table_2 (n NUMBER);\
CREATE TABLE test_table_3 (n NUMBER);'
insert = "INSERT INTO test_table_1 VALUES (1997);\
INSERT INTO test_table_2 VALUES (14);\
INSERT INTO test_table_3 VALUES (17);\
INSERT INTO test_table_3 VALUES (83)"
solution = 'INSERT INTO test_table_1 VALUES (312);\
DELETE FROM test_table_3 WHERE n = 83;\
CREATE TABLE new (n NUMBER);\
DROP TABLE test_table_2;'
problem = DMLProblem(title_md=name,
text_md='texto largo',
create_sql=create,
insert_sql=insert,
collection=collection,
solution=solution,
min_stmt=2,
max_stmt=10)
problem.clean()
problem.save()
return problem
def create_dml_problem(collection, name='Ejemplo'):
"""Creates and stores a DML Problem accepting between 2 and 3 SQL sentences"""
create = 'CREATE TABLE test (n NUMBER);'
insert = "INSERT INTO test VALUES (901)"
solution = 'INSERT INTO test VALUES (25); INSERT INTO test VALUES (50); INSERT INTO test VALUES (75);'
problem = DMLProblem(title_md=name, text_md='texto largo',
create_sql=create, insert_sql=insert, collection=collection,
solution=solution, min_stmt=2, max_stmt=3)
problem.clean()
problem.save()
return problem
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_dml(self):
"""Tests for DMLProblem.judge()"""
collection = Collection()
collection.save()
create = '''CREATE TABLE 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 Club VALUES ('11111111X', 'Real Madrid CF', 'Concha Espina', 70000);
INSERT INTO Club VALUES ('11111112X', 'Futbol Club Barcelona', 'Aristides Maillol', 80000);
INSERT INTO Club VALUES ('11111113X', 'PSG', 'Rue du Commandant Guilbaud', 1000);'''
solution = """INSERT INTO Club VALUES ('11111114X', 'Real Betis Balompié', 'Av. de Heliópolis, s/n', 45000);
INSERT INTO Club VALUES ('11111115X', 'Un otro equipo', 'Calle falsa, 123', 25478);"""
incorrect1 = """INSERT INTO Club VALUES ('11111114X', 'Real Betis Balompié', 'Av. de Heliópolis, s/n', 45001);
INSERT INTO Club VALUES ('11111115X', 'Un otro equipo', 'Calle falsa, 123', 25478);"""
incorrect2 = """INSERT INTO Club VALUES ('11111114X', 'Real Betis Balompié', 'Av. de Heliópolis, s/n', 45000);
INSERT INTO Club VALUES ('11111115Z', 'Un otro equipo', 'Calle falsa, 123', 25478);"""
syntax_err = """INSERT INTO Club VALUES ('11111114X', 'Real Betis Balompié', 'Av. de Heliópolis, s/n', 45000);
INSERT INTO Club VALUE ('11111115X', 'Un otro equipo', 'Calle falsa, 123', 25478);"""
solution_order = """
INSERT INTO Club VALUES ('11111115X', 'Un otro equipo', 'Calle falsa, 123', 25478);
INSERT INTO Club VALUES ('11111114X', 'Real Betis Balompié', 'Av. de Heliópolis, s/n', 45000);
"""
# Time-limit
tle = '''
INSERT INTO Club
SELECT CIF, MIN(Nombre) AS nombre, MAX(Sede) as sede, AVG(Num_socios) as Num_socios
FROM (select '00000000X' AS CIF, 'a' as Nombre from dual connect by level <= 5000)
CROSS JOIN
(select 'b' as Sede, 56789 AS Num_Socios from dual connect by level <= 5000)
GROUP BY CIF;'''
# Creates a table with ORACLE_MAX_ROWS + 1 rows
too_many_rows = f"""
INSERT INTO Club
SELECT level || '3333X', level || 'a', 'b', 45 from dual
connect by level <= {int(os.environ['ORACLE_MAX_ROWS'])+1};
"""
# Create a table with ORACLE_MAX_COLS + 1 columns
too_many_cols = "CREATE TABLE Test( "
for i in range(int(os.environ['ORACLE_MAX_COLS'])):
too_many_cols += f"col{i} NUMBER, "
too_many_cols += "col_end NUMBER);"
# Creates ORACLE_MAX_TABLES + 1 tables
too_many_tables = ""
for i in range(int(os.environ['ORACLE_MAX_TABLES']) + 1):
too_many_tables += f"CREATE TABLE table{i}(n NUMBER);"
oracle = OracleExecutor.get()
problem = DMLProblem(title_md='Test DML',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
min_stmt=2,
max_stmt=2,
author=None,
check_order=False,
solution=solution)
problem2 = DMLProblem(title_md='DML problem',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
min_stmt=0,
max_stmt=100,
author=None,
check_order=False,
solution=solution)
problem.clean() # Needed to compute extra fields and solutions
problem.save()
problem2.clean()
problem.save()
# Validation error (there should be exactly 2 statements)
self.assert_executor_exception(
lambda: problem.judge(
"INSERT INTO Club VALUES ('11111114X', 'R', 'A', 45000)",
oracle), OracleStatusCode.NUMBER_STATEMENTS)
self.assert_executor_exception(
lambda: problem.judge(
"""INSERT INTO Club VALUES ('11111114X', 'R', 'A', 45000);
INSERT INTO Club VALUES ('11111114X', 'R', 'A', 45000);
INSERT INTO Club VALUES ('11111114X', 'R', 'A', 45000)""",
oracle), OracleStatusCode.NUMBER_STATEMENTS)
# Runtime error
self.assert_executor_exception(
lambda: problem.judge(
"""INSERT Club VALUES ('11111114X', 'R', 'A', 45000);
INSERT INTO Club VALUES ('11111114X', 'R', 'A', 45000);""",
oracle), OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge(
"""INSERT INTO Club VALUES ('11111114X', 'R', 'A', 45000);
INSERT Club VALUES ('11111114X', 'R', 'A', 45000);""",
oracle), OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge(syntax_err, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
# Correct solution
self.assertEqual(problem.judge(solution, oracle)[0], VeredictCode.AC)
self.assertEqual(
problem.judge(solution_order, oracle)[0], VeredictCode.AC)
# Incorrect solution
self.assertEqual(problem.judge(incorrect1, oracle)[0], VeredictCode.WA)
self.assertEqual(problem.judge(incorrect2, oracle)[0], VeredictCode.WA)
# Time-limit
self.assert_executor_exception(lambda: problem2.judge(tle, oracle),
OracleStatusCode.TLE_USER_CODE)
self.assert_executor_exception(
lambda: problem2.judge(too_many_rows, oracle),
OracleStatusCode.TLE_USER_CODE)
self.assert_executor_exception(
lambda: problem2.judge(too_many_cols, oracle),
OracleStatusCode.TLE_USER_CODE)
# Too many tables
self.assert_executor_exception(
lambda: problem2.judge(too_many_tables, oracle),
OracleStatusCode.TLE_USER_CODE)
