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_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)