Project 4: Data Definition Language


This project will involve adding more Data Definition Language (DDL) constructs, mostly corresponding to dropping tables and transactions.

Files in Project4/

  • You need to supply this file. You should probably copy the file from Project3 or from Project3 solution released by the instructor.

What you need to do

All of the code you write for this project must be in "".

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 will be considered academic dishonesty for these projects.

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

Changes to the connect function

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:

  1. database: the filename of the database
  2. timeout: your code should ignore this parameter
  3. isolation_level: your code should ignore this parameter

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)

Raising Exceptions

There are times when your code should raise an exception (see 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.

Test Categories


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" (example: 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). Examples:

DROP TABLE students;


  • Transactions begin with a BEGIN TRANSACTION statement. Raise an exception if a BEGIN TRANSACTION occurs within an open transaction.
  • Transactions end with COMMIT TRANSACTION (ROLLBACK tests are in a different category). Raise an exception, if a COMMIT TRANSACTION is attempted without a prior BEGIN TRANSACTION.
  • The only statements the tests will happen within transactions are SELECT, INSERT, UPDATE and DELETE (the Data Manipulation Language statements).
  • If a statement is before a BEGIN statement or after a COMMIT statement, that statement is in "autocommit mode" meaning that any locks needed are acquired before the statement runs, and released when the statement is complete. This behavior is disabled after a BEGIN statement (manual transaction mode).
  • A shared lock is needed to read (SELECT). Shared locks block exclusive locks.
  • A reserved lock is needed to write (INSERT, UPDATE, and DELETE). Reserved locks block exclusive locks and other reserved locks.
  • An exclusive lock is needed to commit a write. Specifically, if the transaction has a reserved lock, it must be promoted to an exclusive lock upon commit. Exclusive locks block all other locks.
  • Locks are released upon commit.
  • If a lock can't be granted when requested, raise an exception.
  • Writes are only visible to other connections upon commit (before then the transactions need to keep private copies of their writes).

Remember: sqlite only locks the entire database, not individual tables or rows.

Recommended Implementation

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:

  • DEFERRED = locks are acquired when needed by a statement in the transaction
  • IMMEDIATE = a reserved lock is acquired at start of the transaction and an exclusive lock is acquired when needed
  • EXCLUSIVE = an exclusive lock is acquired at start of 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.