Exemplo n.º 1
0
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
Exemplo n.º 2
0
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
Exemplo n.º 3
0
    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)