This page describes the specification of Fatworm project

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

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 Speci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.

Version 4.1

Date Created 2012-2-25

Authors

• 02-ACMer
  • Li Lei lilei@cs.cmu.edu
  • Yang Linji dragonylj@sjtu.edu.cn
  • Zhang Yaodong zhydong@sjtu.edu.cn
• 03-ACMer
  • Ma Rong marong1204@gmail.com
  • Zhou Muxin muxin.zhou@gmail.com
  • Qian Feng fengqian@umich.edu
  • Qu Wentao thomas.qu@gmail.com
• 04-ACMer
  • Li Mu limu.cn@gmail.com
  • Lian Xiaochen lianxiaochen@gmail.com
  • Sun Xiaorui sunsirius@hotmail.com
• 05-ACMer
  • Han Wei blue066@gmail.com
  • Tian Yuan feldfar@gmail.com
  • Zhang Kang jobo.zh@gmail.com
• 06-ACMer
  • Chen Xiangru cxreinstein@gmail.com
  • Chen Tianqi workcrow@gmail.com
  • Zhu Yihe sjtu.pigoneand@gmail.com
• 07-ACMer
  • Sheng Meilun sjmind2@gmail.com
  • Wu Chenyang shivaw@gmail.com
• 08-ACMer
  • Wu Yangwei wyw.wyw.good@gmail.com
  • Sun Ruihua sun.ruih@gmail.com
  • Pan Ye panye.sjtu@gmail.com

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 DATABASE db-name
• USE db-name
• DROP DATABASE db-name

- Create or Drop table

• CREATE TABLE tbl-name (create-definition, …)
• DROP TABLE tbl-name [,tbl-name]
• create-definition ::= column-definition | PRIMARY KEY (col-name)
• column-definition ::= col-name data-type [[Not]NULL] [DEFAULT const-value][AUTO_INCREMENT]
• data-type ::= INT | FLOAT | CHAR(M) | DATETIME | BOOLEAN | DECIMAL(M[,D]) | TIMESTAMP | VARCHAR(M)

-Insert

• INSERT INTO tbl-name VALUES (value, …)
• INSERT INTO tbl-name(col-name,…)VALUES(value,…)
• INSERT INTO tbl-name(subquery)

-Delete

• DELETE FROM tbl-name [WHERE where-condition]

-Update

• UPDATE tbl-name SET col-name1=value [,col-name2=value…][WHERE where-condition]

- Create or Drop index

• CREATE [UNIQUE] INDEX index-name ON tbl-name (col-name)
• DROP INDEX index-name ON tbl-name

- Select

• SELECT [DISTINCT] select-expr,…
  [FROM tbl-ref[,tbl-ref]…]
  [WHERE where-condition]
  [GROUP BY col-name]
  [HAVING having-condition]
  [ORDERE BY col-name[ ASC | DESC ],…]
• select-expr ::=value [AS alias]|*
• func ::= AVG | COUNT | MIN | MAX | SUM
• col-name ::= [tbl-name.] col-name
• tbl-ref ::=tbl-name[AS alias]
  |(subquery) AS alias
• where-condition::=bool-expr
• bool-expr ::=value cop value
  | bool-expr AND bool-expr
  | bool-expr OR bool-expr
  | [NOT] EXISTS (subquery)
  | value cop ANY (subquery)
  | value IN (subquery)
  | value cop ALL (subquery)
  | (bool-expr)
• cop ::= < | > | = | >= | >= | <>
• value ::= (value)|col-name|const-value|value aop value
  | (subquery with only one elements(or NULL)in the result table)
  | func(col-name)
• aop ::= + | - | * | / | %
• const-value ::= integer | 'string' | float | 'YYYY-MM-DD HH:MM:SS' | TRUE | FALSE | NULL | DEFAULT
• having-condition ::= bool-expr