This project will involve adding more Data Definition Language (DDL) constructs, mostly corresponding to dropping tables and transactions.
./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.
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.
1: CREATE TABLE students (name TEXT, grade REAL); 2: INSERT INTO students VALUES ('Josh', 2.4); 1: CREATE TABLE colors (r INT, g INT, b INT); 2: INSERT INTO colors VALUES (1, 2, 3); 1: SELECT * FROM colors ORDER BY r; 2: SELECT * FROM students ORDER BY name;
Above is an example test file. Each line is legal SQL, except for the very beginning of each line. The value before the colon is an identifier for a connection. In this case, there are two connections (named "1" and "2"). The rest of the line is executed with the associated connection.
There are times when your code should raise an exception (see https://docs.python.org/3.4/tutorial/errors.html). The type and message of the exception don't matter (and will never matter in any project). But, not raising an exception when expected will fail the test case.
These tests should pass if you completed Project 3. They should pass automatically at the start of the project. If these fail, you've made a regression (you have broken previously functional code).
These tests check that your code accepts multiple connections to the same database (only one database per test in this project). The instructor's Project 3 solution already passes these tests, but your solution may not without some modification. By default, statements autocommit, so no need to handle transaction relevant code yet.
You need to implement the CREATE and DROP table commands. The CREATE TABLE command you have already implemented, but there's one minor addition. Your code should raise an exception if a CREATE TABLE statement attempts to make a table that already exists.
If the CREATE TABLE command includes "IF NOT EXISTS"
CREATE TABLE IF NOT EXISTS students ...),
then nothing should happen if the table already exists.
You also need to implement the DROP TABLE statement
(which has an optional "IF EXISTS" clause to not raise an error
if the table doesn't exist).
DROP TABLE students; DROP TABLE IF EXISTS students;
Remember: sqlite only locks the entire database, not individual tables or rows.
The way we recommend you handle transactions is make a copy of the database's tables when the transactions starts and do your reads and writes to the copy. Upon commit, copy the modified tables to the "real" database. If you implement the locking correctly, no other transaction should generate conflicts.
It may seem inefficient to copy the entire database for each transaction, and it is. The way sqlite actually does it is keeping private copies of all the pages of memory it writes to (and only writing to disk on commit), but that is too difficult to do from Python. So do the inefficient, but easy thing for this project, copy all the tables for each transaction.
Transactions can occur in one of three modes (BEGIN "mode" TRANSACTION:
Transactions that don't specify a mode default to DEFERRED.
Writes are only visible to other connections upon commit (before then the transactions need to keep private copies of their writes).
The second way a transaction can end is with a rollback. All the changes made by the transaction needs to be reversed, and all locks released.
If you implement full isolation between transactions by only modifying a local copy of the database in each transaction, rollback is very simple, just don't copy your modified tables to the "real" database, just get rid of them.