Table of Contents

Specification

Introduction

This page describes the specification of Fatworm project

Feature Requirements

Baseline

  1. Data Types
    1. Primitive INT, FLOAT, BOOLEAN
    2. Time TIMESTAMP, DATETIME
    3. String CHAR(), VARCHAR()
    4. Other DECIMAL(,)
  2. Components
    1. Storate
      • Buffering
    2. Heap File
    3. SQL Parser and Engine
      1. Create / Drop databases
      2. Create / Drop tables
      3. Insert / Delete / Update tuples in table
      4. Basic SELECT on single or multiple table(s)
      5. More powerful SELECT
        • DISTINCT
        • Alias of tables
        • [NOT] EXISTS
        • ORDER BY can accept more than one column
        • GROUP BY / HAVING clauses
  3. Functionality
    1. JDBC Interface
    2. Standalone Driver

Advance

  1. Components
    1. B+ Tree
      1. Basic B+ Tree Index support
      2. Balanced deleting
      3. Duplicated key support
      4. VARCHAR index
    2. More powerful SQL
      1. Create / Drop indexes (only involved with single column)
      2. Powerful SELECT
        1. Subquery as Scalar Operand
        2. Subquery as Relation Operand (ANY, ALL, IN)
        3. Subquery in FROM clause, see Syntax
      3. More complex INSERT: Insert subquery
    3. Database manager
      1. More than one database in system
      2. Concurrency control (locks on table)
      3. Concurrency control (locks on pages)
  2. Functionality:
    1. Support transaction
    2. Logger and roll back
  3. Supplementary Specifi cation
    1. Syntax
      1. STRING & DATE
        1. Any CHAR, VARCHAR, DATETIME or TIMESTAMP starts and ends with a '.
      2. AUTO_INCREMENT
        1. One table can have one column to be AUTO_INCREMENT, and this column MUST BE the PRIMARY KEY.
        2. AUTO INCREMENT column MUST BE INT.
        3. When inserting a row, value in AUTO_INCREMENT eld CAN be a value given. If no value were given, the value should be (MAX(this column)+1).If the value were not given and no row were in table, the value should be 1.
        4. Of couse, AUTO_INCREMENT column CAN BE updated.
      3. INSERT
        1. If user used INSERT INTO table name VALUES (…), ALL elds should be speci ed.
        2. The Default value will be used, only when user uses INSERT INTO table name(…) VALUES (…).
      4. TIMESTAMP
        1. When inserting or updateing, if the value of this eld were not given,it means current time.

Some Clari cation

  1. It's free for your team to decide which programming language to use, but the test case is totally based on the JDBC interface, so we strongly recommend Java as your programming language.
  2. You should code very thing by yourself. However, taking advantage of some code-generation tool like JFLEX or JavaCup is allowed and encouraged.
  3. It's NOT required to do arithmetic operation on DECIMAL column. In addition, you can set arbitrary restrict on CHAR(), DECIMAL() and VARCHAR(), as long as it's reasonable. For example, the length of VARCHAR can't exceed 4000. You should also write these restrict in document.
  4. You are allowed to keep metadata of table / database (or something like that) in memory (outside bu er), since they are not very big. What you have to load into bu er are records (in heap le) and B+ trees.

Syntax of FwSQL

Note: the syntax of FwSQL(Fatworm-SQL)is a simplified version of MySQL. Please go to http://dev.mysql.com/doc/ for more details.

- Create or Drop Database

- Create or Drop table

-Insert

-Delete

-Update

- Create or Drop index

- Select

Misc

Version 4.1

Date Created 2012-2-25

Authors