Project 2: CREATE, INSERT, SELECT

Description

You will be creating a python module that emulates the behaviour of the built-in module, "sqlite3". Your module will be able to execute SQL statements corresponding to: creating tables, inserting rows, and selecting rows.

Files in Project2/

  • Test_Suite/: Folder containing test SQL files. Each file contains a number of SQL statements.
  • project.py: This is the one file you are allowed to edit (besides README.txt). It is imported by cli.py.
  • cli.py: This file imports your project.py. It takes 2 required arguments (a test_sql_file and an output_file) and one optional argument (--sqlite). When executed with ./cli.py test.sql output.txt, it loads the test.sql file, passes it to the database (your project.py file) and writes a report to output.txt. If you give it the optional argument (./cli.py --sqlite test.sql output.txt), it passes the SQL statements to sqlite instead. You can use this to see the correct output.
  • run_tests.py: Run this file ("./run_tests.py") to run every test in Test_Suite with cli.py and get your current grade.
  • README.txt: You need to create this file, and it should contain your name, MSU email, feedback, and any sources beyond this course you used.

What you need to do

All of the code you write for this project must be in "project.py", you may not modify any of the existing tests or test runners.

"cli.py" imports "project.py" and expects to find a "connect" function with one parameter. It passes a filename (specifically ":memory:"), which isn't used in this project. The "connect" function should return an object that has two methods "execute" and "close". For each SQL statement in a test file, "cli.py" will pass that SQL statement as a string to the "execute" method. The method should return an empty list, unless a select statement was executed that yielded rows. In that case, a list of tuples with each denoting a row, should be returned. The "close" method doesn't do anything, yet.

There is no need to write any database output to any files. Persistence will be covered in a later project. Each test will be done on a clean, empty database.

Your program is allowed to use any built in modules of python, with the exception of sqlite3. sqlite3 is the reference implementation for this project, your output will be compared against it for correctness. Importing the sqlite3 module in project.py will be considered academic dishonesty for these projects.

You may (and should) write additional functions and classes in "project.py" to perform the needed actions.

SQL statements

All SQL keywords will be in ALL_CAPS. For this project, the SQL keywords you need to handle are as follows:

CREATE TABLE
INSERT INTO VALUES
SELECT FROM ORDER BY

SQL data types (for this project) and their python equivalents are:

SQL Python 3
NULL None
INTEGER int
REAL float
TEXT str

SQL Syntax

CREATE TABLE

Example: CREATE TABLE stocks (symbol TEXT, price REAL, qty INTEGER);

Creates a table named "stocks", with three columns: column "symbol" composed of text strings, column "price" composed of real valued numbers (floating point), and column "qty" composed of integers.

INSERT INTO

Example: INSERT INTO stocks VALUES ('GOOG', 40.4, 7);

Inserts the row ('GOOG', 40.4, 7), into the table named "stocks".

SELECT *

Example: SELECT * FROM stocks ORDER BY price;

Returns all the rows in the table named "stocks". The rows must be ordered by the column "price" ascending.

Given the above examples, the result should be a list of tuples: [('GOOG', 40.4, 7)]

SELECT columns

Example: SELECT qty, symbol FROM stocks ORDER BY price, qty;

Returns all rows, but only showing the qty and symbol values in that order. The rows are sorted by price (and where price is the same, by qty).

Test Guarantees

  • All tests will be legal SQL (no syntax errors, inserting into nonexistent tables or data type violations).
  • All select statements will have "FROM" and "ORDER BY" clauses.
  • In "CREATE TABLE" statements every attribute will have a data type and no constraints.
  • All table and column names will start with a letter (or underscore) and be followed by 0 or more letters or numbers or underscores.
  • No TEXT will contain single quotes.

NULL Values

Some tests (specifically "Test_Suite/test.null.*.sql") have NULL values. You need to represent this fact with the python object, None. When returning NULL values from a select statement, you should yield a python None object.

Example

Example Test File of SQL statements ("Test_Suite/test.tables.02.sql"):

CREATE TABLE student (name TEXT, grade REAL, piazza INTEGER);
CREATE TABLE tests (version INTEGER, total REAL);
INSERT INTO student VALUES ('Josh', 4.0, 1);
INSERT INTO tests VALUES (1, 100.0);
INSERT INTO student VALUES ('Tyler', 4.0, 2);
INSERT INTO tests VALUES (2, 60.0);
INSERT INTO student VALUES ('Grant', 3.2, 2);
SELECT * FROM student ORDER BY piazza, grade;
SELECT * FROM tests ORDER BY version;

Correct output from "cli.py Test_Suite/test.tables.02.sql":

CREATE TABLE student (name TEXT, grade REAL, piazza INTEGER);
CREATE TABLE tests (version INTEGER, total REAL);
INSERT INTO student VALUES ('Josh', 4.0, 1);
INSERT INTO tests VALUES (1, 100.0);
INSERT INTO student VALUES ('Tyler', 4.0, 2);
INSERT INTO tests VALUES (2, 60.0);
INSERT INTO student VALUES ('Grant', 3.2, 2);
SELECT * FROM student ORDER BY piazza, grade;
/*
('Josh', 4.0, 1)
('Grant', 3.2, 2)
('Tyler', 4.0, 2)
*/
SELECT * FROM tests ORDER BY version;
/*
(1, 100.0)
(2, 60.0)
*/

The output from the database implemented in project.py is inserted in the comments.

If you are in doubt as to the correct output, just pass the --sqlite argument to project_cli.py to use sqlite to solve the test (./cli.py Test_Suite/test.tables.02.sql --sqlite).