Chuhan Ouyang, Yolanda Wang, Emerald Liu, Fengyu Li (equal contribution)
In this project, we create a database management system (DBMS) that stores data and supports basic SQL queries in OCaml.
Our system supports users to create and drop tables, insert, and update an entry in the table, read input and create an output.
To be specific, it supports CRUD (Create, Read, Update, Delete) operations such as SELECT…FROM…WHERE, CREATE, DROP, INSERT, UPDATE, and DELETE.
Detailed description of these operations and their syntax can be found in grammar.md. We provide an REPL (Read-eval-print loop) to allow clients to interact with the DBS. It can also easily adapt to provide outside APIs.
Please follow this guide to install OCaml and setup OPAM.
After that, install required dependencies by opam install <package>.
ANSITerminalcsvcsvtool
Our program provides an REPL interface for the user to interact with. Run make start at project root directory to launch it.
Currently, we haven't developed a help page yet. If you're not familiar with SQL grammar, please check the grammar.md file in the same directory as this file. Our program is built primarily upon the grammar defined in that file. If you're not familiar with context-free grammar, you can study this tutorial.
The condition expression following WHERE phase can only use AND, OR to connect expression. All condition expression can be represented via AND and OR. NOT and parentheses is not supported. For example, a valid condition can be "Country" = "Mexico" AND "Population" > 1000 OR "LandSize" > 10000, an invalid condition can be NOT "Country" = "Mexico".
Especially note that all symbols should be separated with spaces, including the ";" at the end. Here is an example for inspiration:
" CREATE Persons (PersonID INT, LastName TEXT, FirstName TEXT, Address TEXT, City TEXT) ; "
CREATE Persons PersonID INTEGER, LastName VARCHAR, FirstName VARCHAR, Address VARCHAR, City VARCHAR ;
INSERT INTO Persons PersonID, LastName, FirstName, Address, City VALUES 9, "Du", "Chuhan", "Risley", "Moon" ; INSERT INTO Persons PersonID, LastName, FirstName VALUES 20, "Li", "Fengyu" ; INSERT INTO Persons PersonID, LastName, FirstName VALUES 23, "Liu", "Emerald" ; INSERT INTO Persons PersonID, LastName, FirstName VALUES 93, "Wang", "Yolanda" ;
SELECT * FROM Persons ;
DELETE FROM Persons WHERE PersonID = 23 ;
DELETE FROM Persons WHERE FirstName = "Chuhan" OR LastName = "Li" ;
DELETE FROM Persons WHERE FirstName = "Zhajjjng" ; //fail
DROP Persons ;
CREATE Persons PersonID INTEGER, LastName VARCHAR, FirstName VARCHAR, Address VARCHAR, City VARCHAR ; INSERT INTO Persons PersonID, LastName, FirstName, Address, City VALUES 9, "Du", "Chuhan", "Risley", "Moon" ; INSERT INTO Persons PersonID, LastName, FirstName VALUES 20, "Li", "Fengyu" ; INSERT INTO Persons PersonID, LastName, FirstName VALUES 23, "Liu", "Emerald" ; INSERT INTO Persons PersonID, LastName, FirstName VALUES 93, "Wang", "Yolanda" ; SELECT * FROM Persons ;
UPDATE Persons SET PersonID = 999, LastName = Unknown WHERE PersonID = 23 AND LastName = "Liu" ; SELECT * FROM Persons ;
SELECT PersonID, FirstName, LastName FROM Persons ;
SELECT PersonID, FirstName, LastName FROM Persons WHERE PersonID = 20 OR FirstName = "Chuhan" ;
SAVE Persons ;
READ Persons ;
INSERT INTO Persons PersonID, LastName, FirstName, Address, City VALUES 100, New, Person, Happy, Hercules ;
UPDATE Persons SET PersonID = 999, LastName = Unknown WHERE PersonID = 23 AND LastName = Emerald ;