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_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_failure_insert_discriminant(self):
"""Test for check if discriminant clean raise ValidationError"""
create = 'CREATE TABLE test_table_1 (x NUMBER, n NUMBER);'
insert = "INSERT INTO test_table_1 VALUES (1997, 1997);\
INSERT INTO test_table_2 VALUES (1994, 1994);"
correct = 'SELECT * FROM test_table_1 ORDER BY n ASC'
incorrect = 'SELECT * FROM test_table_1 ORDER BY x ASC'
collection = create_collection('Colleccion de prueba XYZ')
problem = DiscriminantProblem(title_md='name',
text_md='texto largo',
create_sql=create,
insert_sql=insert,
correct_query=correct,
incorrect_query=incorrect,
check_order=False,
collection=collection)
with self.assertRaises(ValidationError):
problem.clean()
def create_discriminant_problem(important_order, collection, name='Ejemplo'):
"""Creates and stores a Discriminant DB Problem"""
if not important_order:
create = 'CREATE TABLE test_table_1 (n NUMBER);'
insert = "INSERT INTO test_table_1 VALUES (1997);"
incorrect = 'SELECT * FROM test_table_1;'
correct = 'SELECT * FROM test_table_1 WHERE n > 1000;'
else:
create = 'CREATE TABLE test_table_1 (x NUMBER, n NUMBER);'
insert = "INSERT INTO test_table_1 VALUES (1997, 1997);\
INSERT INTO test_table_1 VALUES (1994, 1994);"
correct = 'SELECT * FROM test_table_1 ORDER BY n ASC'
incorrect = 'SELECT * FROM test_table_1 ORDER BY x ASC'
problem = DiscriminantProblem(title_md=name,
text_md='texto largo',
create_sql=create,
insert_sql=insert,
correct_query=correct,
incorrect_query=incorrect,
check_order=important_order,
collection=collection)
problem.clean()
problem.save()
return problem
def test_failure_insert_discriminant(self):
""" Test for check if discriminant clean raise ValidationError because the table test_table_1 does
not exist
"""
create = 'CREATE TABLE test_table_1 (x NUMBER, n NUMBER);'
insert = "INSERT INTO test_table_1 VALUES (1997, 1997);\
INSERT INTO test_table_2 VALUES (1994, 1994);"
correct = 'SELECT * FROM test_table_1 ORDER BY n ASC'
incorrect = 'SELECT * FROM test_table_1 ORDER BY x ASC'
collection = Collection(name_md="nombre",
description_md='texto explicativo')
collection.clean()
collection.save()
problem = DiscriminantProblem(title_md='name',
text_md='texto largo',
create_sql=create,
insert_sql=insert,
correct_query=correct,
incorrect_query=incorrect,
check_order=False,
collection=collection)
with 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_discriminant(self):
"""Tests for DiscriminantProblem.judge(): clubs with more than 1000 followers"""
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', 'RMCF', 'Concha Espina', 70000);
INSERT INTO Club VALUES ('11111112X', 'FCB', 'Aristides Maillol', 80000);
INSERT INTO Club VALUES ('11111113X', 'PSG', 'Rue du Commandant Guilbaud', 1000);"""
correct_query = "SELECT * FROM Club WHERE Num_Socios >= 1000;"
incorrect_query = "SELECT * FROM Club;"
# User solutions
accepted = "INSERT INTO Club VALUES ('11111114X', 'XXX', 'YYY', 300)"
wrong_answer = "INSERT INTO Club VALUES ('11111114X', 'XXX', 'YYY', 3000)"
runtime_error = [
"INSERT INTO Club VALUES ('11111114X', 'XXX', 'YYY', 'AAA')",
"INSERT INTO Club VALUES ('11111114X', 'XXX', 'YYY')",
"INSERT INTO Club VALUES ()",
"INSERT INT Club VALUES ('11111114X', 'XXX', 'YYY', 3000)",
"INSER INTO Club VALUES ('11111114X', 'XXX', 'YYY', 3000)"
]
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;'''
oracle = OracleExecutor.get()
problem = DiscriminantProblem(title_md='Test Discriminant',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
author=None,
incorrect_query=incorrect_query,
correct_query=correct_query)
problem.clean() # Needed to compute extra HTML fields and solutions
problem.save()
# Time-limit
self.assert_executor_exception(lambda: problem.judge(tle, oracle),
OracleStatusCode.TLE_USER_CODE)
# Error when inserting syntactically invalid user rows
for code in runtime_error:
with self.assertRaises(ExecutorException) as ctx:
problem.judge(code, oracle)
self.assertEqual(ctx.exception.error_code,
OracleStatusCode.EXECUTE_USER_CODE)
# Correct solution
self.assertEqual(problem.judge(accepted, oracle)[0], VeredictCode.AC)
# Incorrect solution
self.assertEqual(
problem.judge(wrong_answer, oracle)[0], VeredictCode.WA)