DB SQLite Like Project

Content:

• Specifications.pdf - detailed spesifications to DB binary file structure

• “code” folder:

  • database.py – main module
  • B_tree.py – Class and methods for Bx-Tree
  • inner_node.py – Inner node class of Bx-Tree and its methods
  • leaf_node.py - Leaf node class of Bx-Tree and its methods
  • parsetab.py – Automatically generated by SQL parser
  • sqlparser.py – module to parse SQL commands
  • test_script_UT.sql - test script to create and populate test table
  • data – directory, it contains DB dictionary files and STUDENTS table. Remove the files to re-instantiate DB

• "parser" folder - contains parser to parse SQL syntax

The project is using SQL lexical analyzer library customized/adopted from below code: https://github.com/kmanley/redisql/blob/master/sqlparser.py

To start the DB, run following command: python database.py

Important:

• package “plyr” must be installed before starting DB
• The full command needs to be on the same line, execution starts once “enter” is pressed. Multiple commands can be entered on one line, separated by “;”. Commands will be executed in the order they were entered.
• Tested with Windows 10 “cmd” terminal. Number of lines terminal accepts from clipboard is limited but still large enough top paste multiple lines.

Run-time Environment preparation

Python 3.4 was used. Most likely python 3.5 will work as well. Required package: ply . If doesn’t exists, install from pip:

pip install ply

To start the DB, run following command:

  python database.py

When starting DB for the first time, dictionary files will be created in the "data" folder. To initialize the DB, simply remove all files from “data” folder and restart DB by running:

python database.py

Optional

I recommend to install using virtualenv, this way clean environment is created with only basic Python packages. Important: package “plyr” must be installed before starting DB. The package can be installed by running: pip install plyr Note: if any of the packages fail to install, download “whl” version from following site: http://www.lfd.uci.edu/~gohlke/pythonlibs/ Some packages require correct compiler configuration, alternatively pre-compiled whl files can be used. If don’t have already, install virtualenv: pip install virtualenv pip install virtualenvwrapper-win Next, create virtual environment for the project installation: mkvirtualenv db_project Install packages: pip install ply

Supported commands

DDL

• SHOW TABLES – Displays a list of all tables in DavisBase.
• CREATE TABLE – Creates a new table schema, i.e. a new empty table.
• DROP TABLE – Remove a table schema, and all of its contained data.

DML

• INSERT INTO TABLE – Inserts a single record into a table.
• UPDATE – Modifies one or more records in a table.

VDL

• “SELECT-FROM-WHERE” -style query
• EXIT – Cleanly exits the program and saves all table information in non-volatile files.

Example:

davisql> create table TEST_V10(rowid int,TEST_V0_TEXT text, TEST_VO_INT int, TEST_VO_REAL real); davisql> insert into test_v10 values(1,'first record',25,null); davisql> select * from test_v10;

['rowid', 'test_v0_text', 'test_vo_int', 'test_vo_real']
1        [b'first record', 25, 'NULL']

davisql> quit