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_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)
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_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_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 = 'select * from dual connect by level <= 1001;'
too_many_cols = 'select 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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_abstract_problem(self):
"""Abstract methods in a Problem raise exceptions"""
collection = Collection()
collection.save()
problem = Problem(title_md='Dates',
text_md='Example with dates',
create_sql=' ',
insert_sql=' ',
collection=collection)
self.assertRaises(NotImplementedError, problem.template)
self.assertRaises(NotImplementedError, problem.judge, '', None)
self.assertRaises(NotImplementedError, problem.problem_type)
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_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 test_load_many_problems(self):
"""Test for loading a ZIP containing several problems"""
curr_path = os.path.dirname(__file__)
zip_path = os.path.join(curr_path, self.ZIP_FOLDER,
self.MANY_PROBLEMS_ZIP_NAME)
collection = Collection(name_md='Colección',
position=8,
description_md='Colección de pruebas',
author=None)
collection.clean()
collection.save()
collection.zipfile = zip_path
collection.clean()
collection.save()
# The new collection contains as many problems as files in the ZIP file
with ZipFile(zip_path) as zfile:
files_in_zip = len(zfile.namelist())
self.assertEqual(
Problem.objects.filter(collection=collection).count(),
files_in_zip)
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_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_trigger(self):
"""Tests for TriggerProblem.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 = """
CREATE OR REPLACE TRIGGER IncrementaNumSocios
BEFORE INSERT OR UPDATE
ON Club FOR EACH ROW
DECLARE
numClubes NUMBER;
BEGIN
SELECT 4000 INTO numClubes FROM DUAL;
:NEW.Num_Socios := :NEW.Num_Socios + numClubes * 100;
END;"""
tests = """
INSERT INTO Club VALUES ('22222222X', 'FALSO', 'Calle', 30000);
UPDATE Club SET Num_Socios = 70000 WHERE CIF = '11111113X';"""
# Time-limit
tle = """
CREATE OR REPLACE TRIGGER IncrementaNumSocios
BEFORE INSERT OR UPDATE
ON Club FOR EACH ROW
DECLARE
numClubes NUMBER;
IN_TIME INT := 11; --num seconds
v_now DATE;
BEGIN
LOOP
EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;
SELECT 4000 INTO numClubes FROM DUAL;
:NEW.Num_Socios := :NEW.Num_Socios + numClubes * 100;
END;"""
# Error in the trigger code
compile_error = """
CREATE OR REPLACE TRIGGER IncrementaNumSocios
BEFORE INSERT OR UPDATE
ON Club FOR EACH ROW
DECLARE
numClubes NUMBER;
BEGIN
SELECT 4000 INTO numClubes FROM DUAL;
:NEW.Num_Socios := :NEW.Num_Socios + numClubes 100; -- missing operator
END;"""
# Division by zero
runtime_error = """
CREATE OR REPLACE TRIGGER IncrementaNumSocios
BEFORE INSERT OR UPDATE
ON Club FOR EACH ROW
DECLARE
numClubes NUMBER;
BEGIN
SELECT 4000 INTO numClubes FROM DUAL;
:NEW.Num_Socios := :NEW.Num_Socios + numClubes / 0;
END;"""
wrong_answer = """
CREATE OR REPLACE TRIGGER IncrementaNumSocios
BEFORE INSERT OR UPDATE
ON Club FOR EACH ROW
DECLARE
numClubes NUMBER;
BEGIN
SELECT 4000 INTO numClubes FROM DUAL;
:NEW.Num_Socios := :NEW.Num_Socios + numClubes * 10000;
END;"""
oracle = OracleExecutor.get()
problem = TriggerProblem(title_md='Test Function',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
author=None,
solution=solution,
tests=tests)
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 compiling user function
self.assert_executor_exception(
lambda: problem.judge(compile_error, oracle),
OracleStatusCode.COMPILATION_ERROR)
# Error when invoking user function
self.assert_executor_exception(
lambda: problem.judge(runtime_error, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
# Correct solution
self.assertEqual(problem.judge(solution, oracle)[0], VeredictCode.AC)
# Incorrect solution
self.assertEqual(
problem.judge(wrong_answer, oracle)[0], VeredictCode.WA)
def test_function(self):
"""Tests for FunctionProblem.judge()"""
collection = Collection()
collection.save()
create = ''
insert = ''
solution = """
CREATE OR REPLACE FUNCTION golesLocal(resultado VARCHAR2) RETURN NUMBER IS
posGuion NUMBER;
golesStr VARCHAR2(3);
BEGIN
posGuion := INSTR(resultado, '-');
golesStr := SUBSTR(resultado, 0, posGuion - 1);
RETURN TO_NUMBER(golesStr);
END;"""
calls = """
golesLocal('2-1')
golesLocal('0-1')
golesLocal('12-13')
golesLocal('117-99')
"""
# Funcion definition is syntactically incorrect
compile_error = """
CREATE OR REPLACE FUNCTION golesLocal(resultado VARCHAR2) RETURN NUMBER IS
posGuion NUMBER;
golesStr VARCHAR2(3);
BEGIN
posGuion := INSTR(resultado, '-');
golesStr := SUBSTR(resultado, 0, posGuion - 1; -- missing bracket
RETURN TO_NUMBER(golesStr);
END;"""
# The execution produces a division by zero
runtime_error1 = """
CREATE OR REPLACE FUNCTION golesLocal(resultado VARCHAR2) RETURN NUMBER IS
posGuion NUMBER;
golesStr VARCHAR2(3);
BEGIN
RETURN 1 / 0;
END;"""
# The function parameter (NUMBER) does not match the actual value (VARCHAR)
runtime_error2 = """
CREATE OR REPLACE FUNCTION golesLocal(resultado NUMBER) RETURN NUMBER IS
BEGIN
RETURN resultado;
END;"""
# Retuns 2x the expected value
wrong_answer = """
CREATE OR REPLACE FUNCTION golesLocal(resultado VARCHAR2) RETURN NUMBER IS
posGuion NUMBER;
golesStr VARCHAR2(3);
BEGIN
posGuion := INSTR(resultado, '-');
golesStr := SUBSTR(resultado, 0, posGuion - 1);
RETURN TO_NUMBER(golesStr) * 2; -- Doubled
END;"""
# Waits for 11 seconds
tle = """
CREATE OR REPLACE FUNCTION golesLocal(resultado VARCHAR2) RETURN NUMBER IS
IN_TIME INT := 11; --num seconds
v_now DATE;
BEGIN
SELECT SYSDATE INTO v_now FROM DUAL;
LOOP
EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;
RETURN 54;
END;"""
bad_name = """
CREATE OR REPLACE FUNCTION golesLocalES(resultado VARCHAR2) RETURN NUMBER IS
posGuion NUMBER;
golesStr VARCHAR2(3);
BEGIN
posGuion := INSTR(resultado, '-');
golesStr := SUBSTR(resultado, 0, posGuion - 1);
RETURN TO_NUMBER(golesStr);
END;"""
oracle = OracleExecutor.get()
problem = FunctionProblem(title_md='Test Function',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
author=None,
solution=solution,
calls=calls)
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 compiling user function
self.assert_executor_exception(
lambda: problem.judge(compile_error, oracle),
OracleStatusCode.COMPILATION_ERROR)
# Error when invoking user function
self.assert_executor_exception(
lambda: problem.judge(runtime_error1, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge(runtime_error2, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(lambda: problem.judge(bad_name, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
# Correct solution
self.assertEqual(problem.judge(solution, oracle)[0], VeredictCode.AC)
# Incorrect solution
self.assertEqual(
problem.judge(wrong_answer, oracle)[0], VeredictCode.WA)
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)
def create_collection(name='Prueba'):
"""Creates and stores a collection"""
collection = Collection(name_md=name, description_md='texto explicativo')
collection.clean()
collection.save()
return collection
def test_proc(self):
"""Tests for ProcProblem.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 = """
CREATE OR REPLACE PROCEDURE inserta(x NUMBER) IS
num_equipos NUMBER;
BEGIN
SELECT COUNT(*) INTO num_equipos FROM Club;
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos + x);
END;"""
call = "inserta(4)"
# Time-limit
tle = """
CREATE OR REPLACE PROCEDURE inserta(x NUMBER) IS
num_equipos NUMBER;
IN_TIME INT := 11; --num seconds
v_now DATE;
BEGIN
SELECT SYSDATE INTO v_now FROM DUAL;
SELECT COUNT(*) INTO num_equipos FROM Club;
LOOP
EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos + x);
END;"""
# Error in the procedure code
compile_error = """
CREATE OR REPLACE PROCEDURE inserta(x NUMBER) IS
num_equipos NUMBER;
BEGIN
SELECT SYSDATE INTO v_now DUAL; -- Falta FROM
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos + x);
END;"""
# Division by zero
runtime_error1 = """
CREATE OR REPLACE PROCEDURE inserta(x NUMBER) IS
num_equipos NUMBER;
BEGIN
SELECT COUNT(*) INTO num_equipos FROM Club;
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos / 0); -- Division by zero
END;"""
# Incompatible type in parameter
runtime_error2 = """
CREATE OR REPLACE PROCEDURE inserta(x DATE) IS -- Incorrect parameter when invoked
num_equipos NUMBER;
BEGIN
SELECT COUNT(*) INTO num_equipos FROM Club;
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos);
END;"""
wrong_answer = """
CREATE OR REPLACE PROCEDURE inserta(x NUMBER) IS
num_equipos NUMBER;
BEGIN
SELECT COUNT(*) INTO num_equipos FROM Club;
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos + x + 2);
END;"""
bad_name = """
CREATE OR REPLACE PROCEDURE insertaR(x NUMBER) IS
num_equipos NUMBER;
BEGIN
SELECT COUNT(*) INTO num_equipos FROM Club;
INSERT INTO CLUB VALUES ('22222222X', 'A', 'B', num_equipos + x);
END;"""
oracle = OracleExecutor.get()
problem = ProcProblem(title_md='Test Function',
text_md='bla bla bla',
create_sql=create,
insert_sql=insert,
collection=collection,
author=None,
solution=solution,
proc_call=call)
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 compiling user function
self.assert_executor_exception(
lambda: problem.judge(compile_error, oracle),
OracleStatusCode.COMPILATION_ERROR)
# Error when invoking user function
self.assert_executor_exception(
lambda: problem.judge(runtime_error1, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(
lambda: problem.judge(runtime_error2, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
self.assert_executor_exception(lambda: problem.judge(bad_name, oracle),
OracleStatusCode.EXECUTE_USER_CODE)
# Correct solution
self.assertEqual(problem.judge(solution, oracle)[0], VeredictCode.AC)
# Incorrect solution
self.assertEqual(
problem.judge(wrong_answer, oracle)[0], VeredictCode.WA)
