[SE-3220] Add support for multiple statements in the XBlock

Description

This PR adds support for multiple statements in the XBlock, and improves error handling for failed queries. It also updates the XBlock version to 0.2

Adding that functionality in `answer_query` was a rather simple change - executing the query with `executescript()` instead of `execute()` (we actually try execute first, due to a caveat mentioned below).

`executescript()` in Python's sqlite library doesn't return any result back, hence any `SELECT` statements which are a part of `executescript()` are in-effect ignored. This does makes sense as multiple queries are often meant to perform updates only (no result returned), whereas multiple `SELECT` statements rarely makes sense.

So, if `verify_query` is being used and it's expecting multiple statements (eg - for testing triggers), then such queries need to be broken up into two parts (as done in this PR):

  • `modification_query` is a newly added parameter - and it will contain all modification statements to be performed after executing `answer_query` or the user's queries

  • `verify_query` will just contain a single SQL query, and returns a result

*JIRA tickets*: (https://openedx.atlassian.net/browse/OSPR-5080), [Opencraft/SE-3218](https://tasks.opencraft.com/browse/SE-3218)

*Sandbox URL*:

Executing SQL queries might seem slow in the resource-limited sandbox, which is a pre-existing condition, due to the use of [codejail](https://github.com/edx/codejail). There has been no significantly increase in execution time as a result of this PR.

*Testing instructions*:

1. Install this XBlock in the LMS container by cloning the repo in `/edx/src`, and running `pip install -e .` in the repo
2. Run `make test` inside the repo
3. Repeat the above two steps in studio to install the XBlock there
4. To enable the block in a course, add "sql_grader" in "Course > Settings > Advanced Settings > Advanced Module List" inside the unit editor in studio
5. Add an exercise by clicking Advanced > SQL Problem at the bottom of the unit in studio
6. Play with different configurations and answers with the block in a course

*Reviewers*

  • [ ] @clemente

  • [ ] edX reviewer[s] TBD

Your pinned fields
Click on the next to a field label to start pinning.

Assignee

sburch

Reporter

Open Source Pull Request Bot

Labels

Contributor Name

Sid Verma

Repo

edx/xblock-sql-grader

Customer

Platform Map Area (Levels 1 & 2)

Educator Experiences - Content Blocks & interactives

Github Lines Added

368

Github Lines Deleted

17