ziyuehuang / sqlite-fall2017

Extendible Hash Table, LRU Page Replacement Policy, Buffer Pool Manager, 2PL Lock Manager, Crabbing Protocol Concurrent B+Tree Index, Log Manager, System Recovery

Projects

• Extendible Hash Table, LRU Page Replacement Policy, Buffer Pool Manager

• B+Tree Pages, B+Tree Data Structure, Index Iterator

• 2PL Lock Manager, Crabbing Protocol Concurrent Index (passed test 1000 times)

• Log Manager, System Recovery

15-445 Database Systems

SQLite Project Source Code

Build

mkdir build
cd build
cmake ..
make

Debug mode:

cmake -DCMAKE_BUILD_TYPE=Debug ..
make

Testing

cd build
make check

Run virtual table extension in SQLite

Start SQLite with:

cd build
./bin/sqlite3

In SQLite, load virtual table extension with:

.load ./lib/libvtable.dylib

or load libvtable.so (Linux), libvtable.dll (Windows)

Create virtual table:
1.The first input parameter defines the virtual table schema. Please follow the format of (column_name [space] column_type) seperated by comma. We only support basic data types including INTEGER, BIGINT, SMALLINT, BOOLEAN, DECIMAL and VARCHAR.
2.The second parameter define the index schema. Please follow the format of (index_name [space] indexed_column_names) seperated by comma.

sqlite> CREATE VIRTUAL TABLE foo USING vtable('a int, b varchar(13)','foo_pk a')

After creating virtual table:
Type in any sql statements as you want.

sqlite> INSERT INTO foo values(1,'hello');
sqlite> SELECT * FROM foo ORDER BY a;
a           b         
----------  ----------
1           hello   

See Run-Time Loadable Extensions and CREATE VIRTUAL TABLE for further information.

Virtual table API

https://sqlite.org/vtab.html

TODO

• update: when size exceed that page, table heap returns false and delete/insert tuple (rid will change and need to delete/insert from index)
• delete empty page from table heap when delete tuple
• implement delete table, with empty page bitmap in disk manager (how to persistent?)
• index: unique/dup key, variable key