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