This project will involve adding more Data Definition Language (DDL) constructs, mostly corresponding to dropping tables and transactions.
All of the code you write for this project must be in "project.py".
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.
In order to ensure that your solution matches the behaviour of python's SQLite3 module, the connect function should accept a total of three parameters:
These two new parameters are used to instruct sqlite3 to not do some of the fancy optimizations that your code won't be sophiticated enough to perform. Example call to connect:
conn = connect("test.db", timeout=0.1, isolation_level=None)
There are times when your code should raise an exception (see https://docs.python.org/3/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.
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.
You can only rollback within a manual transaction, if a connection tries to rollback outside of a transaction, raise an error.